MySQL笔记-索引篇-第二话

这是 MySQL 笔记索引部分的第二篇，聚焦于如何更好的使用索引。第一篇聚焦于介绍什么是索引，索引的一些数据模型，以及 MySQL 中与索引相关的操作语句。

覆盖索引

MySQL 在利用索引进行查询时，可能会发生回表行为，不可避免的会影响到查询的效率。为了进一步的提高查询效率，可以利用一些手段避免回表。

之所以会发生回表，是因为二级索引树上没有存储需要查询的字段，只能查询到该记录所在行的主键，必须回到主索引树去查询相应的字段。

如果在二级索引上可以直接获得相应的值，那么就不会产生回表了，例如：

/*ID 是主键，k 是索引*/
SELECT ID FROM table_test WHERE k='key';

这被称为 覆盖索引，因为索引 k “覆盖” 了查询需求，利用 k 可以直接获得想要的结果。

可以看到，使用覆盖索引之后，整个查询过程中对树的搜索次数被明显减少了，由此可以带来显著的性能提升。

通过联合索引完成覆盖索引

使用场景

当存在一个高频的需求，需要根据某个索引查找另一个字段数据，在这种情况下，就可以建立联合索引，在这个请求上应用到覆盖索引，减少了语句的运行时间。

例如，在如下的一张表：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_cid_INX` (`name`,`cid`),
)

根据 name 查询 cid 的时候，就不需要经历回表了，可以直接通过所在的索引树获取到结果。

建立这样的索引在提升查询效率的同时，也会因为冗余问题而付出代价，在具体应用时需要权衡一番。

使用最左前缀原则

在 B+ 树中，索引项会根据索引的最左前缀来进行排序，可以是联合索引的定义中出现最左 M 个字段，也可以是字符串索引的最左 N 的字符。

只要符合索引的最左前缀，就可以利用该索引来进行搜索了。

例如，上一段中的 (‘name’, ‘id’) 索引，其索引项的排序就会如下所示：

|（‘Apple','12'）| ('Banana', 13)  | ('Banana', 13)  | ('Banana', 14)|
----------------------------------------------------------------------
|    ID_1       |       ID_2      |    ID_3         |    ID_5        |

----->

查找 ‘Banana’ 时，就会定位到 ID_2, 然后向后查找；同样当查找 ‘Ba%’ 时，也可以利用该索引定位到 ID_2， 然后向后查找。

因此，当需要建立联合索引时，如果可以通过索引的位置顺序调整，来减少一个索引，那么就可以采用这种顺序来建立联合索引。

但是，当存在 name, cid 的联合查询，以及分别基于 name 和 cid 的单独查询时，需要维护两个索引，分别是 （’name’, ‘cid’) 以及 （’cid’)。

总结

当建立索引时，需要考虑到会出现的高频需求，各个需求之间能否合并建立联合索引来减少索引项。综合考虑到各种情况后，建立最合适的索引。