「MySQL」高性能索引优化策略

栏目: 数据库 · 发布时间: 5年前

内容简介：今天接上一篇在详细总结MySQL的索引优化策略之前，先给大家介绍一个工具，方便在查慢查询的过程，排查大部分的问题：Explain。有关Explain的详细介绍，可以查看官网地址：Explain 结果有好几列，简单说一下常用的列：select_type, type, key, key_len, ref, rows。其余列可以参考官网介绍。

今天接上一篇『MySQL』揭开索引神秘面纱讨论了索引的实现原理，了解了大概的原理，接下来了解一下高性能索引的优化策略，这也是面试中经常会问到的问题。

1. 工具 Explain

在详细总结 MySQL 的索引优化策略之前，先给大家介绍一个工具，方便在查慢查询的过程，排查大部分的问题：Explain。有关Explain的详细介绍，可以查看官网地址： dev.mysql.com/doc/refman/… 。这里再给大家推荐一个学习方法，就是一定要去官网学习第一手资料，如果觉得英语阅读有挑战的朋友，建议还是平时都积累看看英文文章，英语对于程序员来说很重要，先进的技术和理论很多资料都是英文版，而官网也是非常全的，要想成为技术大牛，这是必须需要修炼的。扯淡就到这里，下面我简单描述一下Explain怎么使用。举例：

mysql> explain select * from user where name="xiao" and age=9099 and birthday="1980-08-02";
        +----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
        | id | select_type | table | partitions | type | possible_keys | key        | key_len | ref               | rows | filtered | Extra |
        +----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
        |  1 | SIMPLE      | user  | NULL       | ref  | unique_key    | unique_key | 249     | const,const,const |    1 |   100.00 | NULL  |
        +----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
复制代码

Explain 结果有好几列，简单说一下常用的列：select_type, type, key, key_len, ref, rows。其余列可以参考官网介绍。

select_type，是说查询的类型，是简单的查询还是复杂的查询，如果不是涉及子查询和UNION，select_type就是SIMPLE。其余的复杂查询还有SUBQUERY和UNION等。
type，非常重要，经常查询分析时用到，type有几个值ALL、index、range、ref、const(system)、NULL。ALL代表全表扫描，从头扫到尾；index跟全表扫描一样，只不过MySQL扫描表的时候是按照索引次序进行而不是行；range，范围扫描，即有限制的索引扫描，开始于索引的某一点，返回匹配这个值域的行。ref，索引访问，返回所有匹配某个单个值得行。const，常量，查询的某部分优化转换成一个常量。NULL，一般就是说执行的时候用不着再访问表或者索引。查询速度类型排序：const > ref > range > index=ALL。
key，这个好理解，用到了哪个索引。
key_len。索引里使用的字节数。
ref，表示key列记录的索引中查找所用的列或者常量。

2. 准备Table

CREATE TABLE `user` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `gender` varchar(16) DEFAULT NULL,
 `name` varchar(64) DEFAULT NULL,
 `birthday` varchar(16) NOT NULL,
 `age` int(11) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `unique_key` (`name`,`age`,`birthday`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码

往表里插入了一些数据，方便下面问题的分析

3. B-Tree索引场景和相关限制:

B-Tree索引，按上一篇原理分析知道是按顺序存储数据的，所以并不是只要查询语句中用了索引就能起作用的，下面来看看具体的场景和限制

全值匹配 。全值匹配指的是和索引中的所有列进行匹配，例如：

mysql> explain select * from user where name="xiao" and age=9099 and birthday="1980-08-02";
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | unique_key    | unique_key | 249     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
复制代码

全值匹配，即按照索引的所有列均精确匹配，从ref和key_len看出，从语句用到了三个索引。理论上索引对顺序是比较敏感的，但实际上执行下面语句可以看看结果：

explain select * from user where age=9099 and birthday="1980-08-02" and name="xiao";
复制代码

结果答案是一样的，因为MySQL查询优化器会自动调整where子句的条件顺序，从而匹配最适合的索引。

匹配最左前缀 。如果想查找name=xiao的所有人，即只使用索引的第一列。

mysql> explain select * from user where name="xiao";
+----+-------------+-------+------------+------+---------------+------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | unique_key    | unique_key | 195     | const | 15170 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+-------+----------+-------+
复制代码

可以看到用到了name这个索引。如果没有匹配最左前缀，结果是怎么样了：

mysql> explain select * from user where birthday="1980-08-02";
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 30340 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
复制代码

可以看到如果没有用name查询索引，则变成了全表查询。

匹配列前缀 。也就是说可以只匹配某一列的值的开头部分，例如想匹配name=xiao-1开头的数据

mysql> explain select * from user where name like "xiao-1%";
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | unique_key    | unique_key | 195     | NULL | 1111 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
复制代码

可以看到类型是range, 使用key=unique_key的联合索引。如果是name like "%xiao-1%" 则就不能使用索引了，其中原因可以根据B-Tree的特性想一下。

匹配范围值 。例如，想查找查找name在[xiao-1, xiao-200]之间的数据。

mysql> explain select * from user where name >  "xiao-1" and name <= "xiao-200";
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | unique_key    | unique_key | 195     | NULL | 1113 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
复制代码

可以看出type=range，用到了unique_key索引。

精确匹配某一列并范围匹配另外一列 。比如想查 name="xiao"， age在[1,100]之间的数据。

mysql> explain select * from user where name="xiao" and age > 1 and age < 100;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | unique_key    | unique_key | 199     | NULL |   98 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
复制代码

可以从key_len的长度参考上一条范围匹配，发现key_len的长度变长了，实际就是用到了name和age2个索引，name是精准匹配，age是范围匹配。思考：如果 sql 语句变成:

select * from user where name="xiao" and age > 1 and age < 100 and birthday="2000-08-02";
复制代码

birthday的索引会用到吗？

刚才上面也提到了B-Tree索引有一些限制，现在总结一下：

最左前缀原理，如果不是按照索引的最左列开始查找，则无法使用索引。
不能跳过索引中的列，比如索引是(name, age, birthday)，那么如果只提供name, birthday两列，则birthday的索引是无法使用的。
如果查询中的某个列范围查询，则其右边所有列都无法使用索引。从第5点最后的思考题就是说的这一点限制。
如果查询语句有函数或者表达式，都是没法使用索引的，比如age-1=18，或者left(name, 3) = xia 。
匹配列前缀，也就是上面提到的第三点，如果like表达式是“%xiao-1%”，则也是没法使用索引的。

4. 索引策略

先总结一下索引的优点：

索引大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机I/O变成顺序I/O

说了三大优点，是不是觉得只要是个表，是个列就全部加上索引就好了？

这样显示是不对的，虽然索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。只要当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是比较有效的。

那是否有什么办法知道什么时候该用索引，什么时候不该用了？

表记录比较少，简单的全表扫描更高效。少的界定的话一般也是靠经验，没有明确多少行算少，个人觉得2000行以内就ok的，实际业务中很多配置表，明显觉得不会有2000行的都可以。

索引选择性。高性能MySQL（第三版）对索引选择性的定义是：不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，下面举例看一下如何计算选择性：

mysql> select count( distinct name) / count(1) from user;
+----------------------------------+
| count( distinct name) / count(1) |
+----------------------------------+
|                           0.6632 |
+----------------------------------+

mysql> select count( distinct birthday) / count(1) from user;
+--------------------------------------+
| count( distinct birthday) / count(1) |
+--------------------------------------+
|                               0.0002 |
+--------------------------------------+

mysql> select count( distinct id) / count(1) from user;
+--------------------------------+
| count( distinct id) / count(1) |
+--------------------------------+
|                         1.0000 |
+--------------------------------+
复制代码

上面可以看出，user表里，name索引的选择性还蛮高，id自增主键选择性就是1，birthday的选择性很低，其实没有必要做索引了。说白了就是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度非常低)。

接下来总结一下常见的索引策略：

独立的列 ：独立的列是指索引的列不能使表达式的一部分，也不能是函数的参数。这个上面也有提到到，就不再重复。
前缀索引 ：有时候需要索引很长的字符列，比如name，名字这种字符一般比较长，如果作为索引，会将整个索引文件变得很大，也会导致查询速度慢下来。一种方法只索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。当然，这种优化也会降低索引的选择性，举例如下：

mysql> select count( distinct left(name, 8)) / count(1) from user;
+-------------------------------------------+
| count( distinct left(name, 8)) / count(1) |
+-------------------------------------------+
|                                    0.3648 |
+-------------------------------------------+

mysql> select count( distinct left(name, 9)) / count(1) from user;
+-------------------------------------------+
| count( distinct left(name, 9)) / count(1) |
+-------------------------------------------+
|                                    0.6630 |
+-------------------------------------------+
复制代码

可以看到当采用name，前缀8个字符时，选择性还比较低，当变成9个字符时，选择性就高了很多，修改索引为left(name, 9)，看一下索引的长度降低了多少了。将索引改为(name(9), age, birthday)

mysql> explain select * from user where name="xiao" and age=9099 and birthday="1980-08-02";
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | unique_key2   | unique_key2 | 84      | const,const,const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------------+
复制代码

可以对比全值匹配中的explain语句，key_len从249缩小到了84，缩小了三倍，大大减少了索引文件的大小，提高了效率。但是也有缺点，前缀索引对ORDER BY or GROUP BY操作无效。

选择合适的索引列顺序，从场景分析中看，对于B-Tree索引是按顺序存储数据，所以选择一个最合适的顺序索引列对查询非常有帮助，但这个也没有比较直观的方法，一般考虑选择性和业务需求的特性。比如上面的例子，name的选择性>age>birthday，且通常业务中按某个用户的name查询的场景会居多，所以索引的顺序就是(name, age, birthday)。说白了就是较频繁作为查询条件的字段才去创建索引。
聚簇索引的特性，从上一篇索引的原理分析，InnoDB引擎使用的B-Tree索引就是聚簇索引，这类索引有什么特性了，上一篇也提到过，InnoDB数据是按主键聚集，如果表没有显示定义主键，则InnoDB会优先选择一个唯一的非空索引代替，如果找不到这样的索引，会隐式定义一个主键来聚簇索引。所以在选择主键的时候，建议参考以下：
- 占的字符尽量的小
- 使用自增ID作为主键
- 更新频繁的列最好不要作为索引
有些人觉得使用业务中的唯一字段作为主键即可，没必要选一个跟业务无关的自增id作为主键，但我个人建议最好使用跟业务无关的自增ID作为主键。原因如下：
- InnoDB数据按主键顺序聚集存储，数据记录本身被存于主索引的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如果用业务的唯一主键，可能非自增主键（如身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。可见插入的消耗是巨大的。
- 为什么主键的字符要小了，因为二级索引是根据主键来检索数据，则叶子节点存储了主键列，也就是说二级索引的访问需要访问二次主键索引，如果主键索引很大，二级索引的可能比想象的要大很多，从而影响性能。
- 更新频繁的列最好不要作为索引，如果更新频繁的列作为索引，每次更新，为了保持有顺，需要调整整个索引B-Tree树，这样的消耗也是挺大的。
冗余和重复索引MySQL允许相同列上创建多个索引，有时候看到建了一个UNIQUE KEY (name, age)，然后还建了个 KEY (name)，这样name这个索引就重复了，发现则需要删除单独的索引，可以减少很多开销。索引越多，会导致插入数据变慢。
未使用的索引在设计表的时候，刚开始需求可能需要用到某个字段的去查询，就将此字段增加了索引，可能最后需求变更的时候，这个字段基本很少有场景去查，这时候经常会忘记去删除此索引，导致没必要的开销。所以没必要的索引，最好是删除。