MySQL - 索引

索引是在存储引擎层实现的，不同引擎实现方式不同，下面讲的是InnoDB。

InnoDB索引模型

表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。这个索引组织表的结构是B+树，是一颗N叉树。

有一张表两个字段ID和k，ID是主键，k则是普通列+索引，这张表的存储形式如下图：

• 索引中的数据都是有序存储的。根据索引字段的顺序比如(a, b)就等于order by a, b
• 主键索引中存放的是ID的值+行。这种索引称为clustered index。
• k索引中存放的则是k的值+ID。这种索引称为secondary index。

索引维护

索引以数据页的形式存在磁盘上，可以认为每页存放N个B+树的结点。

• 数据页有很多
• 数据页的大小是固定的（可参数调整）
• 数据页中的数据是按照索引字段排序的，前面的图里可以看到
• 在磁盘上是段连续的空间，因为机械磁盘顺序读写的速度快，随机读写的速度超级慢。

页分裂：插入一条数据需要写数据页，如果数据页满了，且插入位置在中间，那么存储引擎要申请一个新的数据页，把部分数据移动过去。数据插在尾部没有这个问题。

页合并：删除一条数据，当页的利用率很低之后，就会合并页。

页空洞：页分裂过程会产生**“页空洞”**，因为两个页都不满（分裂后只用50%），但是占用磁盘空间，页空洞是占用大量磁盘空间的一个很常见的原因。

用什么作主键

自增ID做主键

用NOT NULL PRIMARY KEY AUTO_INCREMENT创建一个自增ID主键索引。两个好处：

1. 节省空间：自增ID是数字，占用4个字节（int）或8个字节（bigint）。回想前面的图中普通索引存的是索引字段+主键。如果你有多个普通索引，那么这里省下的空间是比较可观的。
2. 没有“页空洞”：因为是自增的，所以每次插入都插在数据页尾部，所以没有“页分裂”，所以就没有“页空洞”

业务主键做主键

比如拿身份证号做主键，和自增ID比，存在浪费空间，会产生页空洞的问题。但也有合适的时候：

• 只有一个索引
• 该索引必须是唯一索引

回表问题

什么是“回表”：

• select * from T where id=1，执行器只需到主键索引中查就行了
• select * from T where k=5，执行器要先到K索引中查到ID，然后再拿ID到主键索引中查。这个就是回表。如果查到的ID是N个，那么就要N次回表。

所以避免回表是提升执行效率有效手段，方法有：

• 查询只用到主键索引，可以避免回表
• 查询字段在索引覆盖的范围内，比如select ID,k from T where k=5
• 覆盖索引：联合索引比如(k, t)，那么这种也能避免回表：select id, k, t from T where k=5

覆盖索引

覆盖索引可以避免回表问题，但也会占用空间。使用时需要权衡利弊。

最左前缀原则

你可以建一个联合索引(a, b, c)来覆盖到以下查询：

• where a=1
• where a=1 and b=1
• where a=1 and b=1 and c=1
• order by a
• order by a, b
• order by a, b, c
• where a=1 order by b

对于字符类型字段，则like 'xyz%'这种形式也符合最左前缀原则。

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

所以：

1. 索引的创建要和业务查询相匹配
2. 如果通过改变顺序能够少维护一个索引，那么就优先考虑这种方案
3. 如果(a, b), (b)和(b, a), (a)都能满足要求，那么看哪种占用空间小就用哪个。其实就是看a和b哪个占用空间小。

索引下推

1

select * from tuser where name like '张%' and age=10 and ismale=1;

如果name和age都有索引，但是ismale没有，在MySQL 5.6之前是先根据name找到记录，然后就开始回表判断了。5.6之后则会先拿索引字段name和age来过滤，然后再回表。

重建索引

为何要重建索引？为了解决数据页空洞，节省磁盘空间。

重建主键索引会将其他索引一并重建。事实上无论是删除还是创建主键都会将整个表的索引都搞一遍。

可以使用alter table T engine=InnoDB来重建。当然下面也是可以的：

1
2
3
4
5

alter table T drop index k;
alter table T add index(k);

alter table T drop primary key;
alter table T add primary key(id);