数据库系列：MySQL索引优化总结（综合版）

1 背景

作为一个常年在一线带组的Owner以及老面试官，我们面试的目标基本都是一线的开发人员。从服务端这个技术栈出发，问题的范围主要还是围绕开发语言（Java、Go）等核心知识点、数据库技术、缓存技术、消息中间件、微服务框架的使用等几个方面来提问。

MySQL作为大厂的主流数据存储配置，当然是被问的最多的，而其中重点区域就是索引的使用和优化。

2 索引的优化步骤

2.1 高效索引的原则

1. 正确理解和计算索引字段的区分度，下面是计算规则，区分度高的索引，可以快速得定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。我们创建索引的时候，尽量选择区分度高的列作为索引。

selecttivity = count(distinct c_name)/count(*)

1. 正确理解和计算前缀索引的字段长度，下面是判断规则，合适的长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态，才是保证高效率的索引。下买呢长度为6的时候是最佳状态。

select count(distinct left(c_name , calcul_len)) / count(*) from t_name;

mysql>  SELECT
     count(DISTINCT LEFT(empname, 3)) / count(*) AS sel3,
     count(DISTINCT LEFT(empname, 4)) / count(*) AS sel4,
     count(DISTINCT LEFT(empname, 5)) / count(*) AS sel5,
     count(DISTINCT LEFT(empname, 6)) / count(*) AS sel6,
     count(DISTINCT LEFT(empname, 7)) / count(*) AS sel7
 FROM
     emp;
+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   |
+--------+--------+--------+--------+--------+
| 0.0012 | 0.0076 | 0.0400 | 0.1713 | 0.1713 |
+--------+--------+--------+--------+--------+
1 row in set

1. 联合索引注意最左匹配原则：按照从左到右的顺序匹配，MySQL会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。如 depno=1 and empname>'' and job=1 ，如果建立(depno,empname,job)顺序的索引，empname 和 job是用不到索引的。
2. 应需而取策略，查询记录的时候，不要一上来就使用*，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。
3. 正确判断是否使用联合索引（策略篇 联合索引的使用那一小节有说明判断规则），也可以进一步分析到索引下推（IPC），减少回表操作，提升效率。
4. 避免索引失效的原则：禁止对索引字段使用函数、运算符操作，会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。
5. 避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。
6. 模糊查询'%value%'会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是'value%'是可以有效利用索引。
7. 索引覆盖排序字段，这样可以减少排序步骤，提升查询效率
8. 尽量的扩展索引，非必要不新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
9. 无需强制索引顺序，比如 建立(depno,empno,jobno)顺序的索引，你可以是 empno = 1 and jobno = 2 and depno = 8。因为MySQL的查询优化器会根据实际索引情况进行顺序优化，所以这边不强制顺序一致性。但是同等条件下还是按照顺序进行排列，比较清晰，并且节省查询优化器的处理。

2.2 查询优化分析器 - explain

explain命令大家应该很熟悉，具体用法和字段含义可以参考官网 explain-output ，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快，因为扫描的内容基数小。

所以优化语句基本上都是在优化降低rows值。

2.2.1 Explain输出的字段

Column	JSON Name	Meaning
id select_id	The SELECT	identifier
select_type	None	The SELECT type
table table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

2.2.2 select_type 枚举

注意几个核心关键参数：possible_keys、key、rows、select_type,对于优化指导很有意义。

• select_type：表示查询中每个select子句的类型（Simple、Primary、Depend SubQuery）
• possible_keys :指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
• key：key列显示MySQL实际决定使用的键（索引），未走索引是null
• rows：表示MySQL根据表统计信息及索引选用情况，估算所需要扫描的行数

慢查询优化基本步骤

1. 先运行查看实际耗时，判断是否真的很慢（注意设置SQL_NO_CACHE）。
2. 高