[MySQL]查询的性能优化分析（三）：索引

说明：

1. 关于索引的一些概念请查看：查询的性能优化分析（一）：概述 中的索引的使用
2. 本文的样例数据表是参照Oracle数据库中Scott用户下的数据表创建的MySQL数据表

---

索引创建案例

单表查询索引创建案例

案例：查询部门编号为30，奖金超过200的员工中工资最高的员工信息，此时SQL语句及查询结果如下：

原表中一共有14条记录，根据要求，我们设计SQL语句执行后得出结果，显然语句并没有设计错误，此时，查看一下这条SQL语句的执行计划：

分析：从上面的图中可以看到作为一个简单查询（select_type为SIMPLE），这条语句在执行的时候进行了全表扫描（type为ALL），而且还使用了文件排序（Extra中出现了Using filesort），这俩点都在指出查询的性能并不乐观¹，所以优化是必须的。

1. 语句是简单的单表查询语句，不存在关联查询，也无书写问题，可以从索引方面入手²；
2. 查看emp表中的索引，可知只有主键empno：
   
3. 可以根据条件（查询条件、排序条件）创建索引：

#ALTER TABLE emp ADD INDEX idx_emp_dcs (deptno,comm,sal);
CREATE INDEX idx_emp_dcs ON emp(deptno,comm,sal);

4. 再次查看emp表中的索引，可以看到会多了一组索引，并且是按照我们创建时候定义的顺序：
   
5. 查看执行计划，type更新成range，并且使用了索引idx_emp_dcs，但是查询排序时依然使用了文件排序（Extra中依然存在Using filesort），也就是索引中sal列上创建的索引无效³：
   
6. 既然索引中的comm做范围查询会导致后面的索引列失效，那么创建仅关于deptno和sal的索引，再次查看执行计划：
   
7. 通过执行计划可以看到，type更新为ref，同时ref值为常量（const），key更新为idx_emp_ds，Extra中也不再包含Using filesort（Backward index scan表示反向扫描，因sal列降序排列出现，8.0版本中新加入的特性），达到我们预期的优化结果；
8. 删除不必要的索引idx_emp_dcs。

多表查询索引创建案例

案例：查询员工的编号、姓名、岗位、最新薪资（工资及奖金），假定薪资表bonus中的数据为最新数据，则SQL语句及查询结果如下：

根据要求，我们设计SQL语句执行后得出结果，显然语句并没有设计错误，此时，查看一下这条SQL语句的执行计划：

分析：从上面的执行计划结果可以看出，这条多表连接查询语句需要优化的地方出现在type值为ALL，而possible_keys和key均为空，显然两张表中缺少索引，而在多表连接查询中，关键就是连接条件的字段，那么分别对左表（emp）的ename字段和右表（bonus）的ename字段创建索引，查看一下执行计划。

• 仅对左表ename字段创建索引idx_emp_ename：
  
• 仅对右表ename字段创建索引idx_bonus_ename（删除索引idx_emp_ename）：
  
• 两表ename字段均创建索引：
  

通过以上三种情况，可以发现左连接情况下，对右表建立索引可以起到优化作用，这是由左连接的特性决定的，左连接情况下，左表的数据是会全部显示的（不额外附加筛选条件），而连接条件是用来确定如何从右表进行数据检索，所以右表是优化的突破点，反之，右连接时，左表需要创建索引⁴。

索引创建小结

• 尽量选择最常用来检索的字段创建索引，如果是创建组合索引，尽量把这样的字段安排在靠前的位置；
• 范围查询的字段不创建索引（条件IS NOT NULL也属于范围查询）；
• JOIN语句中 被驱动表 上JOIN条件字段需要创建索引；
• 当无法保证被驱动表的JOIN条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置。

索引失效案例

组合索引失效案例

准备工作：员工表emp中预先创建好索引idx_emp_enameJobMgr：

案例：将组合索引中涉及的三个字段分别只取一个、取两个和取全部作为WHERE条件，查看执行计划：

• 情境1：只取一个字段作为条件：
  • 情境1-1：取ename作为条件匹配字段
    
  • 情境1-2：取mgr或者job作为条件匹配字段：
    
• 情境2：取其中的两个字段作为条件：
  • 情境2-1：取ename和mgr作为条件匹配字段（无关先后顺序）
    
  • 情境2-2：取ename和job作为条件匹配字段（无关先后顺序）
    
  • 情境2-3：取mgr和job作为条件匹配字段（无关先后顺序）
    
• 情境3：取索引涉及的全部字段作为匹配条件：
  

分析：

1. 通过对情境1-2和情境2-3这两种情况的执行计划，根据B-Tree索引的工作原理分析可以得出，当检索条件中不包含创建索引时的第一个字段，索引会失效（type值为ALL，possible_keys和key均为空）；
2. 通过对情境1-1和情境2-1这两种情况的执行计划，根据B-Tree索引的工作原理分析可以得出，检索条件包含部分索引字段时，索引字段是按照顺序起作用的，如果跳过某个索引字段，后续索引字段将全部失效（type值为ref，而ref值仅为一个const），这也解释了分析1中索引全部失效的原因。

操作索引字段导致失效案例

案例：

• 情境1：查询员工表emp中员工姓名为“SCOTT”的员工信息：
  
• 情境2：查询员工表emp中员工姓名以“SCOTT”开头的员工信息：
  

分析：如果不考虑需求，只看查询结果的话，想要得到一条这样的记录，我们可以通过不同的SQL语句来实现，但查看这两个语句的执行计划却有很大差别。

• 情境1执行计划：
  
• 情境2执行计划：
  

从上面的对比不难看出，情境2中虽然也是使用了索引字段ename作为匹配条件，但是由于增加了对索引字段的操作导致索引失效，查询进行了全表扫描。

使用SELECT * 查询结果

案例：查询员工表emp中员工的姓名、岗位、主管编号（这三个字段均已创建索引）

• 使用SELCT * 查询结果可以实现需求，无非是多了其他无关紧要的列，查看执行计划如下：
  
• 使用索引字段查询，查看执行计划如下：
  

分析：通过两个执行计划的结果对比不难看出，当要查询的列均为索引列时，数据检索类型就从全表扫描（type值为ALL）变成索引扫描（type值为index），性能上得到了提升。

使用模糊查询

案例：生产中我们经常需要使用模糊查询，比如基于对名字、地址之类的列进行LIKE查询，而且通常与通配符配合使用（%和_在执行计划中影响一致，仅以使用最多的%为例）。查询员工表emp中员工姓名包含“LL”的员工编号、员工姓名，执行计划如下：

分析：先看一下emp表中当前包含的索引：

从两张图中可以分析得出，创建的索引字段ename并未起到作用，而模糊查询中通配符的使用除了两端都加以外还有两种情况，如下：

• 左侧加通配符
  
• 右侧加通配符
  

三种情况对比可以看出，以通配符起始的模糊查询会导致索引字段失效，而实际生产中，除了要查以固定字符串开头的字段内容外，绝对部分时候我们进行的模糊查询都是以通配符起始的，此时为了使索引能够起到相应的作用，建议尽可能的使用覆盖索引，使查询列和索引列保持一致：

• 两侧都加通配符
  
• 左侧加通配符
  

索引失效小结

• 查询是按照索引创建时的字段顺序依次进行，不能跳过索引中的某个字段；
• 在索引字段上做任何操作（计算、函数、类型转换⁵）都会导致索引失效；
• 存储引擎不能使用索引中范围条件右边的列，也就是索引创建小结中提到的“范围查询的字段不创建索引”的原因；
• 尽量使用覆盖索引，使查询列和索引列一致，减少使用“ SELECT * ”，尤其是对索引字段使用模糊查询时；
• MySQL中索引字段在使用不等于（!=或者<>）时会失效。

---

1. 关于使用执行计划对语句性能进行分析请参看 查询的性能优化分析（二）：Explain 中的“Explain的使用”部分。 ↩︎

2. 分析判断依据请查看 查询的性能优化分析（一）：概述 中的“分析原因”部分。 ↩︎

3. 根据B-Tree索引的工作原理，查询时会先对deptno判断，若遇到相同的deptno（值为30）则再对comm判断，依此类推，判断完comm后判断sal，但是comm>200是一个范围检索条件（执行计划中type更新未range的原因），无法实现等值判断，MySQL无法利用索引再对后面的部分（sal列）进行检索，从而产生索引无效，即range类型查询索引后面的索引都会失效。 ↩︎

4. 内连接INNER JOIN时，通过分情况查看执行计划，分析结果建议索引键在右表上。 ↩︎

5. 字符串不加单引号，语句可以执行的情况下，一般都是MySQL自身对数据做了隐式转换。 ↩︎