MySQL 索引

1. 索引的分类

MySQL中的分类按照不同的角度如下：

• 数据结构：B+树索引、Hash索引、Full-Text索引
• 物理存储：聚簇索引（主键索引）、二级索引（辅助索引）
• 字段特性：主键索引、唯一索引、普通索引、前缀索引
• 字段个数：单列索引、联合索引

1.1 数据结构分类

类型	InnoDB	MyISAM	Memory
B+树	支持	支持	支持
Hash	不支持（自适应Hash索引）	不支持	支持
Full-Text	支持	支持	不支持

在5.5版本后，InnoDB成为了MySQL的默认存储引擎，B+树也称为了默认的索引类型，创建表时，InnoDB会根据不同场景创建聚簇索引：

• 存在主键：主键作为聚簇索引
• 不存在主键：选择第一个不包含NULL的唯一列作为聚簇索引
• 以上条件都不满足：隐式创建自增id的列作为聚簇索引

1.2 物理存储分类

1. 聚簇索引（主键索引）：叶子节点存储的是实际的数据

2. 二级索引（辅助索引）：叶子节点存储的是主键，查询其他数据需要根据主键进行回表

1.3 字段特性分类

1. 主键索引：一个表最多只有一个主键索引，且不能为空
2. 唯一索引：建立在UNIQUE字段上的索引，一张表可以有多个唯一索引，索引值可以为空
3. 普通索引：不要求唯一，可以为空
4. 前缀索引：使用字符类型字段的前几个字符（char、varchar、binary、varbinary）建立的索引，不需要使用全部的字段

1.4 字段个数分类

1. 单列索引：即普通的单独一列作为索引
2. 联合索引：多个字段组合成索引，联合索引的非叶子节点存储了多个值，并根据最左匹配原则进行比较

比如创建(a, b, c)索引，在查询的时候：

• where a = x;
• where a = x and b = y and c = z; 此时索引就会生效，同时因为有查询优化器，所以where b = y and a = x;也是可以生效的

但是如果跳过了前面的索引，查询就不会使用到联合索引的优化，比如：

• where b = y and c = z;

1.5 联合索引的范围查询

在使用范围查询的时候，联合索引并不是都会生效，对于(a, b, c)索引：

1. 范围：>

select * from t_table where a > 1 and b = 2;

在a > 1的情况下，b是无序的，所以只有a的索引会生效（5.6之前）

在MySQL 5.6之后，引入了索引下推优化，比如a>1的第一个id是3，5.6之前会在3之后开始回表；而5.6之后则会继续找到b=2的位置，并从之后进行查找

2. 范围：>=

select * from t_table where a >= 1 and b = 2;

当a=1这一情况下，b的索引是相对有序的，所以会从a = 1 and b = 2这条记录开始扫描，此时a、b索引都生效

3. Between And

select * from t_table where a between 1 and 3 and b = 2;

同理，MySQL的Between And相当于 >= 和 <= ，此时a、b索引都生效

4. Like

select * from t_table where a like xxx% and b = 2;

因为此处like满足了前缀匹配，此时a、b索引都生效

2. 索引优化

2.1 索引使用条件

1. 适合使用索引的场景

• 唯一性的字段，如身份证、订单号等
• 经常使用Where的字段，如果多个字段可以建立联合索引
• 经常使用Group By和Order By的字段，根据索引的性质，其在B+树中已排好序，所以可以节省一次排序的时间

2. 不适合使用索引的场景

• Where、Group By和Order By用不到的字段，使用索引只会增加物理空间的占用
• 数据重复率高的字段，比如性别、月份等，MySQL存在查询优化器，当发现值在行中比例过高（默认30%）的时候就会忽略索引，进行全表扫描
• 表数据太少的时候，不需要创建索引
• 经常更新的字段不需要创建索引，频繁的修改会触发B+树的重建，影响性能

2.2 索引的优化方法

1. 前缀索引

前缀索引是为了减小在大字段上的索引大小，但因为没有整个字段的值，也存在一定的局限性，如：不能在order by上发挥效果，不能覆盖索引

2. 覆盖索引

覆盖索引是查找语句中所有的字段都在索引中，这样不会触发回表查询。比如有：(商品id，名称，价格）表，可以对三者建立联合索引

3. 主键索引自增

使用自增的唯一不为null的索引当做主键，这样新的数据会顺序的追加到当前的页面。如果使用非自增的主键的话，新的数据很有可能会插入到数据页的中间位置，导致页分裂，产生大量的内存碎片，从而影响查询效率。

4. 索引不为null

设置为null会导致优化器查询、统计更复杂，比如Count会忽略null的行；null尽管没有意义，但也会在行格式中占用至少1字节的空间