5. MySQL InnoDB的索引与优化

专栏地址：

MySQL系列文章专栏

---

---

1. 索引与表的维护

为什么要重建索引

索引树会因为删除、页分裂等原因，导致数据页有了空洞。重建索引会创建一个新的索引，将数据按顺序插入。使得，索引更加紧凑，数据页利用率更高。

场景

假设表T有索引k和主键索引id，依次使用drop 和add重新创建索引是否合适？

# 重建索引k
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);

这种方式重建索引k是合适的，但是不适用于主键。无论删除主键还是创建主键，都会导致整个表重建，drop和add导致表重建了两次。正确的做法是使用alter table T engine=innoDB直接进行重建表。

表空间的回收

InnoDB对行记录的删除并不是真正的物理删除，而是对删除的行记录进行标记（delete falg），并将其占用空间放入PAGE_FREE列表中，以便后续复用。若整个页的记录都被删除掉了，则整个页都可以复用。

因此删除数据不会并使得表空间缩小，同时还会产生数据页空洞。若要对表空间进行收缩，可以使用alter table命令进行重建表。

2. 使用自增主键

追加插入，无页分裂

自增主键追加的插入模式，不会造成页分裂，也就不需要移动其它记录。

长度短

自增主键一般使用8字节的bigint，较业务主键长度更短:

• 二级索引的叶子节点数据量更短，整体占用空间更少；
• 聚集索引内部更加紧凑。

反例

KV场景，只有一个索引，该索引必须唯一。

3. 使用覆盖索引

3.1 回表

二级索引（辅助索引）的叶子节点保存的是聚簇索引，即主键。利用二级索引进行查询时，若不能获取到所有需要查询的字段，则需要一次额外的回表操作——得到主键之后，再到聚簇索引树中进行查找。

回表操作位于引擎层，Server层和引擎层的交互以行记录为单位。

SELECT * FROM t WHERE k BETWEEN 5 AND 7;需要执行几次树的搜索操作？

对K索引树执行了1次搜索操作，并依次读取了3、5、7三个值，对3、5进行了2次回表，搜索了2次聚集索引树。

3.2 利用覆盖索引减少回表

二级索引已经包含SELECT以及WHERE中所有的列时，称之为覆盖索引，此时不需要在进行回表操作。

覆盖索引能够显著提升性能，是一个常见的优化手段。对于高频的请求上建立覆盖索引，不需要再进行回表操作查询整行记录，减少语句的执行时间。

但是索引的维护也是有代价的，在建立冗余索引来支持覆盖索引时就需要权衡考虑。

可以使用覆盖索引的典型场景有：

1. 查询条件是主键，SELECT列是主键；
2. 查询条件是联合索引，SELECT列是主键或者联合索引的部分列。

3.3 InnoDB对回表的优化

3.3.1 MRR（Mutil-Range Read）

为了减少回表时的随机IO，将其转换成较为顺序的磁盘读取，InnoDB查询二级索引得到主键后，在回表前会对主键先进行排序在进行回表。InnoDB并不是得到所有索引后在回表（减少不必要的额外存储），MRR只能减少回表的随机I