mysql设置id值为索引值_MySQL 索引

一、索引作用

提供了类似于书中目录的作用,目的是为了优化查询

(一)、索引种类

1、B树索引(Balance Tree)

作用

Btree的设计理念，就是让查询能够快速锁定范围，特别适合于范围查询。

种类

B树

B+树 相邻叶子节点上有双向指针

B*树 相邻非叶子节点上有双向指针

构建过程

1、数据排序

2、排序后的结构，均匀的落在各个leaf节点上

3、下层提取leaf节点的范围+指针构成No-leaf节点

5、下层提取No-leaf节点范围+指针构成root节点

2、Hash索引

3、R树

4、Full text

5、GIS

(二)、BTREE在MySQL中的应用

聚簇索引

1. 前提

a. 建表时有主键，主键选择为聚簇索引

b.没有主键，选唯一

c.都没有，生成6字节隐藏索引

建议：使用数字自增列创建为主键

2. 功能

a录入数据时，按照聚簇索引组织存储数据， 在磁盘上有序存储数据行

在一个区中，数据行从逻辑到物理都是有序的。

b. 加速查询

where id条件

辅助索引回表查询

3. 构建过程

a.Leaf节点：整表数据行所在的数据页

b. No-Leaf节点： 下层叶子节点ID范围+指针

c. ROOT节点：下层No-Leaf节点ID范围+指针

辅助索引

1. 前提

需要按照查询条件创建合理的辅助索引

2. 功能

加速查询：利用辅助索引作为查询条件是才能加速

3. 构建过程

a.Leaf节点：索引键值+主键ID，根据索引键值排序后生成

b. No-Leaf节点： 下层叶子节点键值范围+指针

c. ROOT节点：下层No-Leaf节点键值范围+指针

回表

查询完辅助索引之后，得到主键值，回聚簇索引树查询。

联合索引

联合索引应用要满足最左原则

1、联合索引构建:idx(a,b,c)

叶子结点

1. 提取a,b,c+id

2. 按照a,b,c进行排序

3. 生成叶子节点

枝节点

1.提取最左列的范围+指针

根节点

提取枝节点范围+指针

2、联合索引建立规范

a. 建立联合索引时，选择重复值最少的列作为最左列。

b. 使用联合索引时，查询条件中，必须包含最左列，才有可能应用到联合索引

3、索引应用注意问题

回表

影响：

IO量增多

IO次数增多(IOPS)

随机IO增多

减少方法：

使用联合索引联合索引

精细化查询条件

尽量使用唯一值多的列作为查询条件，并建立索引

优化器：MRR(Multi-Range-Read)

查看所有优化器

select @@optimizer_switch;

设置优化器状态

set global optimizer_switch='mrr=on';

功能：

1. 辅助索引查找后得到ID值，进行自动排序

2. 一次性回表，很有可能受到B+TREE中的双向指针的优化查找。

索引树高度

1、数据行

分表、归档、分布式

2、索引列值长度

前缀索引(字符串列)

3、主键过长

主键最好使