MySQL - 索引优化技巧

2021-04-15 本文已影响0人 kyo1992

本文介绍几种最常用的几种索引优化手段

在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引，联合索引使用不仅可以节省空间，还可以更容易的使用到索引覆盖。

索引的字段越多，更容易满足查询需要返回的数据。例如联合索引(a_b_c)，等价于有了三个索引：a，a_b，a_b_c，索引树的叶节点数据没变，索引data字段数据是省数据了。

在创建联合索引的时候，应该把频繁使用的列，区分度高的列放在前面，区分度高代表筛选粒度大，也可以在常需要作为查询返回的字段上增加到联合索引中，如果在联合索引上增加一个字段而使用到了覆盖索引(索引下推)，避免回表，就建议使用联合索引。

最左前缀原则和联合索引的索引存储结构和检索方式有关。

联合索引底层存储结构

如上图，在联合索引树上，最底层的叶子节点按照第一列name从左到右递增排列，但是age和position是无序的，age只有在name值相等的情况下，小范围内递增有序，而position列只有在name和age两列相等的情况下小范围内递增有序。

select * from abc_innodb where name = 'haha' and age = 30 and position = 'dev';

像这个查询，B+树会比较name列来确定下一步搜索方向，往左还是右，如果name列相同再比较age列。如果查询条件没有name列，B+树就不知道第一步应该从哪个节点查起。

select * from abc_innodb where name  like 'Bi';

如果你要查的是所有名字以'Bi'为前缀的人，也能够用上这个索引，查找到第一个符合条件的记录，然后向后遍历，直到不满足条件为止。

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

例如对于BLOG、TEXT和VARCHAR类型的列，必须使用前缀索引，只索引开始的部分字符。

基于上面对最左前缀索引的说明，讨论一个问题：在建立联合索引的时候，如何安排索引内的字段顺序。

评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

要为高频请求创建 (身份证号，姓名）这个联合索引，并用这个索引支持“根据身份证号查询地址”的需求。

那么，如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候不得不维护另外一个索引，也就是说需要同时维护 (a,b)、(b) 这两个索引。

定义：索引包含所有需要查询的字段的值，避免回表。
覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。

但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。

对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

两者性能区别微乎其微。
因为InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。即使普通索引需要继续往后查找，能在同一页内停止搜索概率还是很大的。

当更新操作涉及唯一索引时，因为唯一性约束，当数据不在内存页时，不能使用change buffer直接更新，必须将数据从磁盘中加载。而普通索引则没有这个限制。
所以，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，尽量选择普通索引。