Mysq索引优化

一、概述

索引是存储引擎用于快速找到某些记录的拥有特定数据结构的文件。本篇讨论的主题就是如何高效的使用索引。在阅读本篇文章前，需要对索引的底层原理有所了解，以便能够深刻的理解文章中提到的观点和策略，关于索引的底层原理可以参考 Mysql索引原理浅析。索引有几种类型，B+Tree索引，哈希索引，全文索引。哈希索引的优缺点在 Mysql索引原理浅析一文中已有讨论。而全文索引，如果确实有做搜索的需要，一般考虑使用更为专业的搜索引擎ElasticSearch来解决。本文对B+Tree索引的高效使用做讨论，这也是在Mysql中使用的最广泛的索引。另外，如果数据量很大，数千万上亿级别，那么单纯的使用mysql索引并不能解决问题，这个场景在如何扩展Mysql有一些讨论，但并不全面。在数十万到数百万数据量的级别，高效的使用索引能带来巨大的性能提升。下文将讨论一些高效使用索引的策略。

二、高效索引策略

首先索引的匹配规则得理解，这是前提，如果查询不符合索引的匹配规则就无法利用索引去查询数据。索引匹配规则在 Mysql索引原理浅析 一文中有介绍，理解了Mysql索引的数据结构自然能够理解它的匹配规则。我们在创建索引时就应当考虑查询场景，在写查询语句时同样要结合索引的匹配规则。在能够使用索引的前提下，我们再来考虑如何更加高效的使用索引。

创建索引

1.选择合适的主键id

主索引根据主键id路由，所有的辅助索引的叶子节点存放的都是主键id，再根据主键id回表查数据。如果主键id太大，会导致一个B+Tree节点路由更少的数据，进而增加整个B+Tree的深度，影响索引的效率。如果主键id是无序的，则插入数据可能引起数据页的合并或分裂。所以一般推荐使用自增主键id。还有业务主键，但是业务主键无序，可能还比较长。如果查询用的是业务主键，那么从查询效率上来讲，我们应该选择业务主键。

2.创建合适的前缀索引

在某些场景中我们需要用某个字段做查询，而这个字段是一个很长的字符串，我们想对其做索引，但是字段太长了。在索引原理中讲过，B+Tree与磁盘预读原理想结合，节点大小是与页相当的，如果索引字段太长，那么意味着一个节点中可以容纳的节点数也就更少，进而导致B+Tree的深度增加，从而影响索引效率。这也是为什么创建索引时，尽可能的选择小一点的key。这种情况下，可以选择该字段一定长度的前缀做索引，以减少key的大小。但是这样做需要考虑选择性的问题，前缀越长，占空间越多，选择性越高；前缀越短，占空间越少，选择性越低。根据实际情况取两者平衡。

SELECT COUNT(DISTINCT LEFT(field,len)) / COUNT(*) AS select_rate;

3.创建合适的组合索引

当查询条件有多个的时候，有必要创建组合索引以满足查询需求，下列建议可以帮助你创建更加高效的组合索引。

a.索引字段顺序

根据区分度，将区分度越大的字段越靠前，这样可以在B+Tree搜索的过程中，提前过滤掉不满足条件的记录。当然这是在单单where条件使用索引的情况下，如果需要用索引分组，排序（下文会讲到），那么就需要结合具体的场景来看了，这条建议就不是必须的。另外非常重要的一点是，如果某一种顺序的索引可以满足更多的查询场景，我们应该选择它，这样相当于用更少的索引满足更多的查询，这样的索引优先选择。

b.注意数据的倾斜

当某个特定值的数据出现的特别多的时候需要特殊考虑，比如某个用户拥有大量的好友，用户id这个索引的选择性就可能因为这一特殊数据而降低，针对这类查询需要在应用层做限制，否则会拖累整个应用。

4.索引的选择性要高

索引的选择性等于该索引的不重复值与记录总数的比值，计算公式如下

SELECT COUNT(DISTINCT field) / COUNT(*) FROM t_user;

选择性反映了一个索引中数据的重复情况。例如，在一张用户表中，用户id做索引，选择性是1，因为id没有重复的；性别做索引，性别在整张表也就两个取值，重复率极高，它的选择性及其小。像这种取值范围固定的字段，随着表增大选择性会越来越低。我们在创建索引时要考虑选择性高的索引，这样在查询时能在存储引擎层面就过滤绝大多数数据。如果走了索引仍然返回很多行记录，那么就会产生大量的随机IO，并且服务层需要做大量的过滤操作，那也就失去索引的意义了。

使用索引

1.不在列上做计算

第一，具体的条件在service层就计算好，而不是推到Mysql中去计算。保持一种良好的SQL编写习惯，将字段放在一侧，另一侧写条件。第二，不能将查询字段作为函数的参数传进去再做过滤。以上两种情况会导致不能走索引。反例如下：

SELECT id, name FROM t_user WHERE age + 1 = 10;
SELECT id, name FROM t_user WHERE format(create_time, 'YYYY-mm-dd') = '2020-09-18';

2.索引覆盖

如果一个索引包含所有的查询字段，我们就称之为索引覆盖。比如学生表有班级姓名的索引，我们查询某个班是否存在某个姓名的人，根据班级+姓名去查询就满足索引覆盖，因为在辅助索引中就可以直接查询到我们需要信息，不需要再根据主键去主索引再查一遍，这样的效率很高。

3.索引排序

Mysql有两种方式来生成有序的结果：通过排序，或者按索引顺序扫描。如果EXPLAIN出来的type为index，则说明使用了索引排序。索引排序分下列三种情况

a.无WHERE条件

SELECT * FROM t_table ORDER BY field1, field2 ; 这种情况只需要ORDER BY 后面的字段有索引(field1, field2)，就可以使用索引排序了。

b.WHERE等值条件+ORDER BY

例一
SELECT * FROM t_table WHERE field1… AND field2… ORDER BY field1 field2; 需要有（field1,field2…）的索引。
例二
SELECT * FROM t_table WHERE field1…AND field2 ORDER BY field3 field4；需要有(field1,field2,field3,field4…)的索引。

c.WHERE范围条件+ORDER BY

SELECT * FROM t_table WHERE field1(范围) ORDER BY field1,field2
如果Where条件是范围查询，那么排序字段必须以Where的过滤字段为索引字段前缀，如上面例子，范围查询为field1，排序字段必须以field1,field2开头，需要index(field1,field2…)。
从上面三种情况看得出来，索引排序的规则类似前缀匹配，根据B+Tree的结构，再结合查询条件，不难分析出这个查询是否可以走索引排序。当前最好还是EXPLAIN一下，看看是否真正可以用到索引排序。

三、索引内置优化

1.索引下推

索引下推 index condition pushdown(ICP)，我们都知道索引的最左匹配规则，例如index（a,b,c），a是等值匹配，b是范围匹配，c是范围匹配，那么索引只能用到a，b两个字段 。在5.6之前，使用EXPLAIN将看到extra列会有using where，也就是说服务层需要进行过滤。在5.7之后，支持索引下推，存储引擎层会根据c字段的条件进行过滤，extra列会显示Using index condition。这样就可以减少回表的数据量，进而减少随机IO，服务层也不需要再执行过滤。索引下推算是对最左匹配原则的一种增强和支持，能够更多的利用索引资源。

2.索引合并

假设使用有两个单列索引，index(a)，index(b)，那么下面的SQL是可以利用到这两个索引的。

SELECT * FROM t_table WHERE a = xxx OR b = xxx;

四、索引注意事项

1.索引的个数

索引的个数越多，查询速度会提升，但是插入和更新的速度会降低。不要过度创建索引。在满足现有查询的情况下，索引越少越好。

2.不必要的索引

不要创建重复的索引。删除一些低频的索引，或重复索引，提高插入效率。通过查询INFROMATION SCHEMA, INDEX_STATISTICS可以看到每个索引的使用频率。InnoDB的唯一约束是通过索引实现的，也许一个索引不是用来查询，仅仅用于数据约束也是有可能的，这种就不能随意删除。

3.索引失效

1.隐式类型转换
比如存的手机号为varchar，查询却用的数字。
2.索引列参与计算
索引列应当独立放在一边，另外一遍放查询条件。
3.索引区分度低
如果查询的数据量比较多或者一个索引的过滤情况很差，即使能够走索引，mysql也不会选择使用索引，而是全表扫描。
通过索引取数据会涉及随机IO，先到辅助索引中查找，再回表取数据，并且在索引中查找需要遍历B+Tree。而全表扫描只需要从主索引第一行记录开始，以链表的形式向后遍历，是顺序IO。
第一、顺序操作的执行速度比随机操作更快，无论是在内存还是在磁盘。在顺序操作的情况下，内存和磁盘的速度差距相对更小；在随机操作下，内存与磁盘的速度差距相对更大。
第二、随机读取通常是查找特定的行，却要读取一整页的数据，其中大部分是不需要的，浪费了一些工作。而顺序读取，通常是需要页上的所有数据。要解决随机IO的问题，可以使用更多的内存，让他们缓存在内存中。

4.索引与锁

1.InnoDB基于索引实现行锁的，如果索引无法过滤掉无效的行，就需要InnoDB检索到数据返回给服务层，服务层应用Where后再释放行锁。所以使用索引也可能需要锁住一些不需要的数据，如果在不能使用索引的情况下更糟糕，Mysql会做全表扫描并锁定所有行，并返回到服务层应用Where做过滤。
2.InnoDB在二级索引上使用共享锁，在主键索引上使用排它锁。先逐行锁二级索引，再锁对应的主键索引。如果有两个不同的二级索引，在批量更新时，走不同二级索引的两个查询可能需要更新相同的几个记录，这样有可能导致死锁。

5.重建索引

如果我们选择的不是自增主键，在进行插入的过程中很可能导致页分裂和页空洞。这样利用页的利用率下降，被缓存命中的概率也下降。通过重建索引可以让数据更加紧凑，提高页利用率进而提高查询效率。可以通过命令重建主键索引。如果是普通索引，可以先执行删除，再添加。主键索引则采用下面这种方式，更加节省时间。因为删除主键索引会导致表重建（Innodb基于主键id来建立二级索引），添加索引时也会重建。没有必要重复两次，用下面的语句一次重建即可。

alter table T engine=InnoDB