MySQL 高级(2) 索引优化 - 《MySQL》

SQL 性能下降原因
单值索引和复合索引
SQL 执行顺序
七种 join 理论
- 练习
索引介绍
索引性能分析
- 前提知识
- explain
  - explain 表头详解
单表查询索引优化
- 查询优化实战
  - 最左匹配原则
两表查询索引优化
- 查询优化实战
三表查询索引优化
多表优化的结论
索引失效
索引面试题分析
- 总结

索引优化从某种意义上来说其实就是对 where 查询列表中的字段进行的优化

SQL 性能下降原因

导致慢 SQL 的主要原因有两方面

执行时间长
等待时间长

其中根本原因主要有如下几个

查询语句写的不好
索引失效 ( 建了索引，但是没用上 )
关联查询太多 join ( 情况分为：设计缺陷或是不得已的需求 )
服务器调优以及各个参数设置 ( 缓冲，线程池等 )

单值索引和复合索引

通过指定的表中字段数目来创建索引，可以分为单值索引和复合索引

单值索引建立

#索引名的通用命名规则为：idx_表名_字段名
create index 索引名 on 表名(字段名);

通过指定字段来建立索引后，当该字段作为查询条件进行查询时，效率会比不建索引时高很多，因此数据库会在内部对索引字段进行排序

eg：

#假设有表如下
CREATE TABLE `students` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `class_id` bigint(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `gender` varchar(1) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

#在对该表进行查询时，假如经常通过 name 来查询记录，则可以通过对 name 建立索引，来优化基于 name 的查询语句
select * from where name = "xxx";

create index idx_students_name on students(name);

复合索引建立

#索引名的通用命名规则为：idx_表名_字段名1_字段名2
create index 索引名 on 表名(字段名1, 字段名2...);

通过指定多个字段来建立索引后，这多个字段作为查询条件时的查询语句效率会有所提高

eg：

#假设有表如下
CREATE TABLE `students` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `class_id` bigint(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `gender` varchar(1) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

#在对该表进行查询时，假如经常通过 name 和 gender 来查询记录，则可以通过对 name 和 gender 建立复合索引，来优化基于 name 和 gender 的查询语句
select * from where name = "xxx";

create index idx_students_name_gender on students(name, gender);

SQL 执行顺序

一段常见的查询 SQL 的大致语法如下

select 查询列表
from 表1 别名
连接类型 join 表2 别名
on 连接条件 [连接类型 join 表3 别名 ...]
where 分组前筛选
group by 分组列表
having 分组后筛选
order by 排序列表
limit 分页起始索引, 分页大小;

但是在数据库系统中，执行顺序是这样的

from 表1 别名
连接类型 join 表2 别名
on 连接条件 [连接类型 join 表3 别名 ...]
where 分组前筛选
group by 分组列表
having 分组后筛选
select 查询列表
order by 排序列表
limit 分页起始索引, 分页大小

MySQL 高级(2) 索引优化 - 图1

七种 join 理论

常见的 join 连接有：左外连接，右外连接，全外连接，内连接，左连接，右连接，全连接

MySQL 高级(2) 索引优化 - 图2

MySQL 不支持 full outer join

练习

先创建两张表并插入数据

create table tb_dept(
    id int(11) not null auto_increment,
    deptName varchar(30) default null,
    locAdd varchar(40) default null,
    primary key(id)
)engine=innodb auto_increment=1 default charset=utf8;

create table tbl_emp(
    id int(11) not null auto_increment,
    name varchar(20) default null,
    deptId int(11) default null,
    primary key(id),
    key fk_dept_id(deptId)
)engine=innodb auto_increment=1 default charset=utf8;

INSERT INTO tb_dept(deptName, locAdd) VALUES
("RD", 11),("HR", 12),("MK", 13),("MIS", 14),("FD", 15);

INSERT INTO tbl_emp(NAME, deptId) VALUES
("z3", 1),("z4", 1),("z5", 1),("w5", 2),("w6", 2), ("s7", 3), ("s8", 4), ("s9", 51);

#查询有部门的员工的信息
SELECT te.*
FROM tb_dept td INNER JOIN tbl_emp te
ON te.`deptId` = td.`id`;

MySQL 高级(2) 索引优化 - 图3

#查询所有部门及其员工信息
SELECT td.*, te.*
FROM tb_dept td left JOIN tbl_emp te
ON te.`deptId` = td.`id`;

MySQL 高级(2) 索引优化 - 图4

#查询所有员工及其部门信息
SELECT td.*, te.*
FROM tb_dept td right JOIN tbl_emp te
ON te.`deptId` = td.`id`;

MySQL 高级(2) 索引优化 - 图5

#查询没有部门的员工信息
SELECT te.*
FROM tbl_emp te LEFT JOIN tb_dept td
ON te.`deptId` = td.id
WHERE td.`id` IS NULL;

MySQL 高级(2) 索引优化 - 图6

#查询没有员工的部门信息
select td.*
from tbl_emp te right join tb_dept td
on te.`deptId` = td.id
where te.`id` is null;

MySQL 高级(2) 索引优化 - 图7

全连接其实就是左连接结果+右连接结果，中间部分会重复一次，去重即可

MySQL 不支持全连接，可以 union 来如下实现 ( union 自带去重 )

select *
from tbl_emp te right join tb_dept td
on te.`deptId` = td.`id` 
union
select * from tbl_emp te left join tb_dept td
on te.`deptId` = td.`id`;

MySQL 高级(2) 索引优化 - 图8

MySQL 也不支持全外连接，但是用 union 也可以解决，全外的本质就是左外结果+右外结果

SELECT *
FROM tbl_emp te RIGHT JOIN tb_dept td
ON te.`deptId` = td.`id`
WHERE te.`id` IS NULL
UNION
SELECT *
FROM tbl_emp te LEFT JOIN tb_dept td
ON te.`deptId` = td.`id`
WHERE td.`id` IS NULL;

索引介绍

索引是什么

MySQL 官方对索引的定义为：索引 ( Index ) 是帮助 MySQL 高效获取数据的数据结构，因此索引本质上是一种数据结构。索引的目的在于提高查找效率，因此索引会让被索引字段排序，以提高查找效率，所以索引可以简单理解为是一种排好序的，可以用来快速查找数据的一种数据结构

索引的两大功能是排序和提高查找效率，因此索引会影响 where 的查找效率和 order by 的排序效率

快速查找原理

在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上上进行快速查找，这些数据结构就是索引。以下是一种可能的索引方式

MySQL 高级(2) 索引优化 - 图9

为了加快 Col2 的查找速度，可以维护一个右边所示的二叉排序树，每个结点分别包含索引键值和一个指向对应数据物理地址的指针，这样就大大提高了查找数据的效率

一些 tips

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘中
平时所说的索引，如果没有特别指明，都是指 B Tree 结构 ( 多路搜索树 )组织的索引 ( 次要索引，覆盖索引，聚集索引，复合索引，前缀索引，唯一索引默认都是使用 B+ 树索引，统称索引 )。除了 B Tree 结构的索引外，还有哈希索引等
在公司中，一般不会从物理上删除一条数据，而是进行逻辑删除，一是为了做数据分析，二是为了不破坏索引结构，避免索引失效
但是在经过大量的增删改后，索引失效是必然的，这时就需要重建索引
使用索引可以增加对字段的查询效率，但是会降低对字段进行增删改的效率 ( 因为同时需要修改索引 )，因此频繁被增删改的字段上不应该建立索引

索引的优劣

建立合适的索引的优点

可以提高数据检索效率，降低数据库的 IO 次数
索引列会对数据进行排序，降低数据排序时的成本，降低 CPU 开销

但同时索引也有以下的缺点

索引实际上也是一张表，该表保存了主键与索引字段，并指向实体表的记录，索引索引列也是要占用空间的
索引增加了查询速度的同时，会降低更新表的速度，因为更新表时，MySQL 不仅要保存数据，还要保存每次更新后添加了的索引列的字段到索引文件中，或者调整因为更新带来的键值变化后的索引信息
索引并不是固定的，而是需要根据业务变更，用户分析等多方面因素来持续更新和优化的

索引分类和基本语法

参考博客：https://www.cnblogs.com/jiawen010/p/11805241.html

索引大致分为如下几类

单值索引：即一个索引只包含单个字段，一个表可以有多个单值索引
唯一索引：索引字段的值在表中必须唯一，但允许有空值 ( UNIQUE )
复合索引：一个索引包含多个字段
聚集索引：聚集索引并不是一种单独的索引类型，而是一种数据存储方式，具体细节依赖于其实现方式。在 InnoDB 中，聚集索引是按照每张表的主键构造的一棵 B+ 树，同时叶子结点中存放的就是整张表的每条行记录，也将聚集索引的叶子结点称为数据页
非聚集索引：在聚集索引之上创建的索引称之为非聚集索引，非聚集索引访问数据总是需要二次查找。非聚集索引中叶子结点存放的有键值，聚集索引键，通过非聚集索引查找数据需要先找到聚集索引键后再根据该键找到数据行的数据页，最后找到数据

总的来说

InnoDB 中，表数据文件本身就是按 B+ Tree 组织的一个索引结构，聚集索引就是按照每张表的主键构造一颗 B+ 树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分；
一般建表会用一个自增主键做聚集索引，没有的话 MySQL 会默认创建，但是这个主键的更改代价较高，故建表时要考虑自增 ID 不能频繁 update 这点
根据实际情况自行添加的索引都是非聚集索引，非聚集索引就是一个为了需找主键索引的二级索引，需要先找到主键索引再通过主键索引找数据

tips：一般情况下复合索引优于单值索引

创建

create [unique] index 索引名 on 表名(字段名 ...);

alter 表名 add [unique] index [索引名] on (字段名 ...);

删除

drop index [索引名] on 表名;

查看

show index from 表名;

tips：除了自定义索引外，还有主键，外键，唯一，全文等 MySQL 本身就支持的索引

#主键索引
alter table 表名 modify column 字段名 类型 primary key;
alter table 表名 add primary key(字段名);

#唯一索引
alter table 表名 modify column 字段名 类型 unique;
alter table 表名 add unique [索引名](字段名);

#外键索引
alter table 表名 add foreign key(字段名) references 主表名(被引用字段名);

#全文索引
alter table 表名 add fulltext [索引名](字段名);

#自定义索引
alter table 表名 add index [索引名](字段名);

索引结构和搜索原理

MySQL 的索引结构一般有如下几种

B Tree 索引
Hash 索引
full-text 索引
R Tree 索引

其中 B Tree 索引是和后端关系最密切的

假设有如下一种索引结构

MySQL 高级(2) 索引优化 - 图10

浅蓝色的部分为一个磁盘块，深蓝色为数据项，黄色为指针

如：磁盘块 1 就包含数据项 17 和 35，以及指针 P1, P2, P3，并且具有如下规则

指针 P1 指向的子结点中，所有的数据都小于父结点中的最小值
指针 P2 指向的子结点中，所有的数据都大于父结点中的最小值，小于父结点中的最大值
指针 P3 指向的子结点中，所有的数据都大于父结点中的最大值
上述规则可向下类推

其实在数据库中的基本单位是页，页再存储在磁盘块上

而真实的数据存在于叶子节点上，非叶子结点不存储真实数据，只存储指引搜索方向 (所谓的指引指的是与真实数据项作比较后告知该去哪个指针所指的磁盘块，也可以称为键值) 的数据项。如：3,5,9,10 等都是真实存在于数据表中的，而 8, 12, 26, 30 等并不存在于数据表中

假设要查找值为 28 的数据项，则一次查找过程如下

在磁盘块 1 (根结点) 开始进行查找，发现 28 大于最小值，小于最大值，因此去指针 P2 所指向的磁盘块 3。这里由于加载磁盘块 1 到内存中，发生了第一次 IO
在磁盘块 3 中进行查找，发现 28 依然大于磁盘块 3 中的最小值，小于最大值，因此继续去往磁盘块 3 的 P2 指针所指向的磁盘块 8。这里由于加载磁盘块 3 到内存中，发生了第二次 IO
在磁盘块 8 中进行搜索，找到目标数据项。这里由于加载磁盘块 8 到内存中，发生了第三次 IO

3 层的 B+ Tree 可以表示上千万的数据 ( InnoDB )，如果上千万的数据查找都只需要三次 IO，那么对性能的提高是巨大的。如果没有索引，则每个数据项都要发生一次 IO，而千万次的 IO 显然是难以接受的

补充

参考博客

http://blog.itpub.net/31545820/viewspace-2654702/

计算机在存储数据时，都有最小存储单元 ( 磁盘的最小单元是扇区，文件系统的最小存储单元是块 )，数据库系统也不例外，但是因为存储引擎的不同，最小存储单元可能会不同，而 InnoDB 的最小存储单元为 "页"，一个页的大小为 16k

可以通过命令查看

SHOW VARIABLES LIKE 'innodb_page_size';

MySQL 高级(2) 索引优化 - 图11

数据表中的数据都是存储在页中的，假设 1 行数据的大小为 1k，则一页就可以存放 16 行数据

但是如果数据库按照这样的方式存储数据，那么如何知道数据在哪一页就成了问题，所以人们想到了使用 B+ Tree 的方式来组织这些数据 (也就是前面介绍的方式)

假设有一棵 B+ Tree 的高度为 2，即存在 1 个根结点和若干个叶子结点，则这棵树的存放总记录数为：根结点指针数*单个叶子结点记录行数。我们假设 1 行数据大小为 1k，则单个叶子结点中的记录数为 16k / 1k = 16 行

计算非叶子结点能存放多少指针时，假设主键类型为 bigint，长度为 8 字节，而指针大小在 InnoDB 下为 6 字节，一共 14 字节代表一个键值对单元 ( 非叶子结点不存储数据项，故不算上 1k )， 1 页中能存放下多少个这种单元就代表可以存放下多少个指针，16k = 16384 字节，16384 / 14 = 1170

最后 1170*16 = 18720，因此一个两层 B+ Tree 能存放 18720 条数据记录

同理，一个三层 B+ Tree 则可以存放 1170*1170*16 = 20902400 条记录

总结

InnoDB 存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值+指针，在 B+ 树中叶子节点存放数据，非叶子节点存放键值和指针。
索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中查找到需要的数据

索引的建与不建

什么时候应该建

主键应该建立唯一索引
频繁作为查询条件的字段应该建立索引
查询中与其他表关联的字段，可以建立外键索引
查询中排序所依据的字段应该建索引，如果该排序字段通过索引去访问将大大提高排序效率 ( 即：如果排序字段上有索引的话，则根据该字段进行的排序其速度会大幅提升 )
查询中分组所依据的字段应该创建索引，并且也会影响分组函数的效率

什么时候不建

表记录太少时，不应该建索引
经常增删改的表上不应该建索引
查询中很少使用或参考的字段不应该建立索引
数据重复且平均分布的表字段不应该建立索引，因为这样没有太大的左右

索引性能分析

前提知识

mysql query optimizer

MySQL 中专门负责 select 语句的优化器模块，主要功能为：为客户端请求的查询提供优化器认为最优的执行策略 ( 优化器觉得最优的执行策略未必 DBA 觉得最优 )

查询优化器是这样进行工作的

当客户端向 MySQL 请求一条查询，命令解析器模块完成请求分类
如果是 select 语句，则转发给查询优化器
查询优化器对整条查询进行优化，处理掉一些常量表达式的运算，直接换算成常量值
完成对查询中查询条件的简化和转化，如去掉一些无用或者显而易见的条件，结构调整等
分析查询中的 Hint 信息，看显示的 Hint 信息是否可以完全确定该查询的执行计划
如果没有 Hint 信息或者 Hint 信息不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据查询进行相应的计算分析，最后得出执行计划

MySQL 常见瓶颈

CPU：CPU 的瓶颈一般是因为饱和时将数据装入内存或从磁盘上读取数据
IO：磁盘 IO 瓶颈发生在装入数据远远大于内存容量的时候
服务器硬件的性能瓶颈：top，free，iostat，vmstat 来查看系统的性能状态

覆盖索引

覆盖索引有三种理解方式

select 的列表只用从索引中就能够获取，而不用读取数据行，MySQL 可以利用索引返回 select 列表中的字段，而不必根据索引再次读取数据文件，即查询列被所建的索引覆盖(假如建了 column1，column2 的符合索引，而查的也是 column1 和 column2，则发生了索引覆盖)
索引是高效查找记录的一种方法，但是一般数据库也能使用索引查找到一个列的值，因此其不必读取所有行。当能通过索引就可以得到想要的数据，那么就不需要读取行了。一个或多个索引包含或者覆盖了所有满足查询结果的数据就叫做索引覆盖
是非聚集复合索引的一种形式，包括查询中的 select，join，和 where 子句用到的所有列 ( 即建立了索引的字段正好是查询中 select 子句和 where 子句所涉及的字段，也即索引包含了查询正在查找的所有数据 )

如果使用覆盖索引，则 select 列表中最好只取出需要的列，而不能 select *。但也不能将所有字段一起做成复合索引，如果将所有字段一起做索引则会导致索引文件过大，查询性能下降

explain

使用 explain 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或是表结构的性能瓶颈

在 MySQL 5.6.3 之前只能 explain select，5.6.3 之后可以 explain update，delete，select

explain 的使用：explain + SQL

explain select * from tbl_emp;

MySQL 高级(2) 索引优化 - 图13

explain 能够显示以下信息

表的读取顺序（ id ）
数据读取操作的操作类型（ type ）
哪些索引可以被使用（ possible_keys ）
哪些索引被实际使用（ key ）
表之间的引用
每张表有多少行被优化器查询（ rows ）

explain 表头详解

使用 explain 对 SQL 进行分析获得其执行计划，必定包含以下列

id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extr

每一行就是 MySQL 获取某数据的执行计划，通过分析这些计划中的参数可以进行 SQL 效率的分析

id

select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作的顺序

id 有三种情况

id 相同

id 相同，代表表的读取执行顺序是由上至下的

EXPLAIN SELECT * 
FROM departments d INNER JOIN employees e
ON d.`department_id` = e.`department_id`;

MySQL 高级(2) 索引优化 - 图14

id 相同的情况下，执行顺序由上至下，说明先加载了别名为 d 的表

下面这个例子中

EXPLAIN SELECT * 
FROM departments d INNER JOIN employees e
ON d.`department_id` = e.`department_id`
inner join jobs j on e.`job_id` = j.`job_id`;

MySQL 高级(2) 索引优化 - 图15

则表明了加载顺序为 j -> e -> d

id 不同

先看一下下面这段三重嵌套子查询的 explain 信息是什么样的

EXPLAIN 
SELECT 
  * 
FROM
  departments
WHERE department_id = 
  (SELECT 
    department_id
  FROM
    employees 
  WHERE job_id = 
    (SELECT 
      job_id
    FROM
      jobs
    WHERE job_title LIKE "Public Accountant")) ;

MySQL 高级(2) 索引优化 - 图16

结果非常 amazing 啊！在子查询中 id 居然开始递增了，并且 id 值越大的优先级越高，越先被执行

SUBQUERY 代表子查询，PRIMARY 代表主查询，越外面的查询越往后被执行

id 有相同有不同

id 越大的越先执行， id 相同的按顺序执行

EXPLAIN
SELECT 
  grade,
  ag.s 平均工资 
FROM
  (SELECT 
    AVG(salary) s 
  FROM
    employees 
  GROUP BY department_id) ag 
  INNER JOIN sal_grade s 
    ON ag.s BETWEEN s.`min_salary` 
    AND s.`max_salary` ;

MySQL 高级(2) 索引优化 - 图17

derived 意思为衍生，derive+x 代表该表为虚表 (衍生表)，x 代表这张表是从 id 为 x 的记录所对应的 table 字段中的表衍生来的

select_type

该字段主要用于区别对某一张表进行的查询操作是普通查询，联合查询还是子查询等

常见的值有 6 种

SIMPLE：简单的 select 查询，查询中不包含子查询或者 UNION
PRIMARY：查询中如果包含任何子查询，则最外层查询被标记为该值
SUBQUERY：在 select 或者 where 中包含的子查询，被标记为该值
DERIVED：在 from 列表中包含的子查询被标记为该值，MySQL 会递归执行这类子查询，把结果放在临时表中
UNION：出现在 union 后的 select 查询标记为该值。若 union 包含在 from 子句的子查询中，外层 select 将被标记为 derived
UNION RESULT：从 union 表获取结果的 select 标记为该值```mysql

EXPLAIN SELECT FROM tb_dept td LEFT JOIN tbl_emp te ON td.id = te.deptId UNION SELECT FROM tb_dept td RIGHT JOIN tbl_emp te ON td.id = te.deptId;

<br />![](https://gitee.com/antigenmhc/picture/raw/master/img/20201003193508.png#align=left&display=inline&height=141&margin=%5Bobject%20Object%5D&originHeight=141&originWidth=367&status=done&style=none&width=367)

<a name="table"></a>
##### table

表示该行的数据是关于哪张表的

tips： 表示这张表是 explain 信息表中 id 为 x 的记录，其对应的表的衍生表

<a name="partitions"></a>
##### partitions

在 MySQL 5.7 之后为默认选项，显示分区表命中的分区情况，若为非分区表则该字段为 null

<a name="type"></a>
##### type

type 表示访问类型，即 **表示 MySQL 在表中找到所需行的方式** ( 由筛选条件中所用的字段是否涉及索引，具体条件等因素决定 )，其常见的值中，从最好到最差依次为

`system > const > eq_ref > ref > range > index > all`

<a name="ea229056"></a>
###### system，const

当 MySQL 能对查询的某部分进行优化，将其转化成一个常量时就会使用这些访问类型

其中 system 表示查询的表只有一行记录，是 const 的特例，实际业务中很少出现

const 表示该数据通过索引一次就可以找到，一般通过 primary key 或者 unique 索引查找到的数据属于该访问类型。因为只匹配一行数据，所以很快，MySQL 能将该查询转换为一个常量

```mysql
explain
select *
from employees
where employee_id = 100;

MySQL 高级(2) 索引优化 - 图18

eq_ref

唯一性索引，如果被标识为了这种访问类型，说明 MySQL 知道最多只返回一条符合条件的记录，一般通过 primary key 或者 unique 索引进行查找时会看到

explain
select *
from employees e inner join departments d
on e.`employee_id` = d.`department_id`;

MySQL 高级(2) 索引优化 - 图19

根据连接条件不难得知，e 表中必然只会有一条记录会和 d 表中的某一条记录连接

ref

返回匹配某个单值的所有行，可能会找到多个符合条件的行，此访问类型大部分情况下只有通过外键索引进行查找时才会标记

EXPLAIN
SELECT *
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`;

MySQL 高级(2) 索引优化 - 图20

根据条件不难得知，e 表中必然会有多条记录与 d 表中的一条记录连接

range

当检索条件来自于一个给定的范围时，访问类型为 range

一般就是在 where 后使用 between and，>，<，in 等条件查询

explain
select *
from employees
where job_id in ("AD", "AB");

MySQL 高级(2) 索引优化 - 图21

index

full index scan，index 与 all 的区别在于 index 的访问类型只遍历索引树，这通常比 all 要快，因为索引文件通常比数据文件要小 ( InnoDB 的数据文件和索引文件在一个文件中 )

虽然 all 和 index 都是扫描全表，但是 index 是从索引中读取，而 all 则是从硬盘中读取

EXPLAIN
SELECT employee_id
FROM employees;

MySQL 高级(2) 索引优化 - 图22

当查找的字段为索引字段时，访问类型就是 index，因为相当于只想查找所有的索引字段的值

#department_id 为外键索引
explain
select department_id
from employees;

MySQL 高级(2) 索引优化 - 图23

all

全表扫描找打匹配行，最慢的嗷

possible_keys，key

这两个字段用于

判断是否使用到了索引，即可以用于判断索引是否失效
在多个索引竞争的情况下，MySQL 到底使用到了哪个索引

possible_keys

显示可能应用在这张表中的索引，一个或多个

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引，如果为 null，则表示没有使用索引或者索引失效没有用上

explain
select *
from employees e inner join departments d
on e.`employee_id` = d.`department_id`
inner join jobs j on e.`employee_id` = j.`job_id`
where e.job_id  = "";

MySQL 高级(2) 索引优化 - 图24

查询中若使用了覆盖索引，则该索引仅出现在 key 列表中

覆盖索引就是 select 的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说即查询列要被所使用的索引覆盖

EXPLAIN
SELECT employee_id, job_id
FROM employees;

MySQL 高级(2) 索引优化 - 图25

可因发现，可能使用的索引为 null，但是实际使用中还是用到了索引

key_len

表示查询中使用的索引的字节数，可通过该列计算查询中所使用的索引的长度。在不损失精确性的情况下，长度越短越好

key_len 显示的值为索引字段的最大可能长度，而并非实际使用长度，key_len 是根据表定义计算而得的，不是通过表内检索出的

但是往往精确度和 ken_len 并不是正相关的，为了更高的精确度往往需要更大的 ken_len

EXPLAIN
SELECT *
FROM employees
WHERE job_id = "AC";

MySQL 高级(2) 索引优化 - 图26

key_len的长度计算公式：

varchr(10) 可变长字段且允许NULL = 10 * ( character set：utf8=3, gbk=2, latin1=1)+1(NULL)+2(变长字段)
varchr(10) 可变长字段且不允许NULL = 10 *( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10) 固定字段且允许NULL = 10 * ( character set：utf8=3, gbk=2, latin1=1)+1(NULL)
char(10) 固定字段且不允许NULL = 10 * ( character set：utf8=3, gbk=2, latin1=1)

ref

显示与 key 中索引匹配的是哪些字段或常量 (即哪些字段或常量被作为了查询条件)

当使用索引列作为等值匹配的条件去执行查询时，ref 列展示的就是与索引列作等值匹配的条件，如常数或者某个列

explain
select *
from employees e inner join departments d
on e.`employee_id` = d.`department_id`
inner join jobs j on e.`employee_id` = j.`job_id`
where e.job_id  = "";

MySQL 高级(2) 索引优化 - 图27

不难分析，由于我们用到的 job_id 是一个外键索引，而且查询条件为一个常量 “”，索引数据将会根据 job_id 和该常量值来进行筛选

rows

根据表统计信息及索引选用情况，大致估算出为了找到所需要的记录而需要读取的行数，数字越小越好

explain
select *
from employees e inner join departments d
on e.`employee_id` = d.`department_id`
where e.`job_id` = "";

MySQL 高级(2) 索引优化 - 图28 MySQL 高级(2) 索引优化 - 图29

不难看出，因为 id 都为 1，所以按照顺序执行，大概需要读取两行就可以得出所需要的记录 ( 代表有多少行被优化器查询过 )

filtered

表示存储引擎返回的数据在 Server 层过滤后，剩下多少满足查询的记录与总数量的比例。该字段不重要

Extra

该列用于显示不适合在其它列中显示但又十分重要的信息

该列可能有出现以下值 (前三个较为重要)

Using filesort

说明 MySQL 对数据使用了一个外部的索引排序，而不是按照表中的索引顺序进行排序 ( 没用上索引或者索引失效 )

MySQL 无法只利用表中索引顺序完成排序，从而需要借助的排序操作称为文件排序，Using filesort 出现时，说明 MySQL 就需要额外的排序操作，不能通过索引顺序达到排序效果，增加了 CPU 的开销，需要进行优化 (如果可以优化)

#使用 MySQL 客户端命令行
explain
select *
from employees e inner join departments d
on e.`employee_id` = d.`department_id`
order by e.job_id\G

MySQL 高级(2) 索引优化 - 图30

Using temporary

表示查询中出现了临时表用于保存中间结果，一般出现于排序，分组和多表 join 的情况，查询效率不高，建议优化

Using Index

表示相应的 select 操作中使用了覆盖索引，没有访问表的数据行，效率高

如果同时出现 using where，表示索引被用来执行索引键值的查找
如果没有同时出现 using where，表示索引用来读取数据而非执行索引键值的查找

Using where

表示使用了 where 进行筛选，并且查询的列未被索引覆盖

using join buffer

使用了连接缓存

impossible where

表示 where 子句的值总是 false，不能用来获取任何元组

select tables optimized away

在没有 group by 子句的情况下，基于索引优化 MIN / MAX 分组函数，或者在 MyISAM 存储引擎下优化 COUNT(*) 等操作不必等到执行阶段再进行优化，而是在执行计划生成的阶段完成优化

distinct

表示使用了 distinct，并且优化了 distinct，在找到第一行匹配的记录后就停止查找相同的记录

no table used

查询中使用了 from 虚拟表或不包含 from 子句

NULL

查询的列未被索引覆盖，并且 where 的筛选条件是索引的前导列

Using Index condition

mysql 5.6 版本的新特性

mysql 利用索引（二级索引）元组和筛选字段在索引中的where 条件从表中提取数据记录的一种优化操作

非常的 amazing 啊！在后面的测试中，如果一张表中有五个字段，其中一个字段为主键，另外三个字段为复合索引，则即使是对最后一个没有索引的字段进行查询时 ( 即没有发生覆盖索引 )，也不会变为全表扫描 ( 前提是复合索引中的三个字段都用到了查询列表中 )

单表查询索引优化

首先创建表并插入数据

create table if not exists article(
    id int(10) unsigned not null primary key auto_increment,
    author_id int(10) unsigned not null,
    category_id int(10) unsigned not null,
    views int(10) unsigned not null,
    comments int(10) unsigned not null,
    title varbinary(255) not null,
    content text not null
);

insert into article(author_id, category_id, views, comments, title, content)
values
(1, 1, 1, 1, "1", "1"),
(2, 2, 2, 2, "2", "2"),
(1, 1, 3, 3, "3", "3");

查询优化实战

#查询 category_id 为 1 且 comments 大于 1 的情况下，views 最多的 id，并查看其执行计划
explain
select id
from article
where category_id = 1 and comments > 1
order by views desc
limit 1\G

MySQL 高级(2) 索引优化 - 图31

发现是全表扫描，并且使用了文件排序，查询效率是比较低的

不难知道该表中只有 id 上有主键索引，因此这时可以建立索引来优化

因为我们用到了三个字段，因此可以尝试通过使用这三个字段来建立复合索引从而提高查询效率

CREATE INDEX idx_article_ccv ON article(category_id, comments, views);

然后再次查询

MySQL 高级(2) 索引优化 - 图32

发现访问类型变了，不再是全表扫描，extra 增加了 using index，但是依然没有解决文件排序的问题

在进行更进一步的优化之前，先了解一下什么是最左匹配原则

最左匹配原则

以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询 ( <，>，between and，like ) 就会停止匹配

比如有一个复合索引的创建顺序为 (a，b，c，d)，而查询条件为 a=1 and b=2 and c>3 and d=4，这时 d 是用不到索引的，因为 c 是一个范围查询，其后面的字段会停止匹配，造成索引失效

最左匹配原则只有针对复合索引有效，无论什么样的索引，底层都是一棵 B+ 树，而复合索引的键值数量不是一个，是多个。构建一棵 B+ 树只能根据一个值来构建，因此数据库依据复合索引最左的字段来构建 B+ 树

MySQL 高级(2) 索引优化 - 图33

比如这棵 B+ 树，可以看到 a 的值是有顺序的 112233，而 b 值则是无序的 121412，因此直接使用 b = 2 这种查询条件是没有办法利用索引的，因为复合索引首先按照 a 排序，b 直接使用的话是无序的

但是也可以发现，当 a 值相等的情况下，b 值是按顺序排列的，但是这种顺序是相对的。假设有 a>1 and b=2 这个查询条件，a 字段是可以匹配上索引的，而 b 则不可以，因为 a 是一个范围，在这个范围中 b 字段可能是无序的。这就是为什么在遇到范围查询后，后面的索引都会失效的原因

在有了这一点知识后，我们不难分析出，如果我们将 comments > 1 这个条件，放在了排序索引字段 views 前，则会造成排序依据的索引字段失效，从而无法按照 views 这个索引的索引顺序进行排序，进而需要文件排序造成额外开销。因此我们需要在创建索引时，将 views 放在前面 comments 前面，避免最左原则造成的索引失效，从而避免 using filesort

#删除并新建索引，保证排序依据的索引字段在范围查询字段之前
DROP INDEX idx_article_ccv ON article;
CREATE INDEX idx_article_ccv ON article(category_id, views, comments);

再次查询

explain
select id
from article
where category_id = 1 and comments > 1
order by views desc
limit 1\G

MySQL 高级(2) 索引优化 - 图34

可以发现，文件排序已然消失，访问类型也变成了 ref，效率有了很大的提升

两表查询索引优化

先说结论：索引建在从表上效率更高。当为内连接时，索引无论加在哪张表上优化程度都是一样的

先创建两张表并插入数据

CREATE TABLE IF NOT EXISTS class(
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    card INT(10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS book(
    bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    card INT(10) UNSIGNED NOT NULL
);

INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));

INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));

各插入 20 条数据，自行完成即可

查询优化实战

Q：为了优化两张表的多表查询的效率而需要建立索引，那索引应该建在哪张表上？

explain
select *
from class s left join book b
on s.`card` = b.`card`\G

MySQL 高级(2) 索引优化 - 图35

可以发现都是全表扫描

由于我们不知道给左表还是右表添加索引能够提高效率，但是可以两边都尝试一遍

先给右表 ( left join 中的从表 )添加索引后，再次查看执行计划

create index idx_book on book(card);

explain
select *
from class s left join book b
on s.`card` = b.`card`\G

MySQL 高级(2) 索引优化 - 图36

发现只有右表的访问类型变为了 ref，虽然左表依然没有改变，但是较之前两个全表扫描有较为明显的优化

删除刚才建的索引，尝试在左表 ( left join 中的主表 ) 上建立索引

drop index idx_book on book;
CREATE INDEX idx_class ON class(card);

explain
select *
from class s left join book b
on s.`card` = b.`card`\G

MySQL 高级(2) 索引优化 - 图37

结果非常的 amazing 啊！发现只有左表的访问类型变为了 index，右表访问类型依然没有变。但是如果与将索引加在右表上相比，索引加在右表 (从表) 上的效率显然要更高

A：在左连接中，造成索引加在左表和加在右表上效率显著不同原因是左连接的特性，因为左连接保证了左表数据一定是都有的，而只用确定如何从右表开始搜索，因此在右表上建立索引能够更大程度的提升查询效率

同理，右连接查询中应该建在左表上才能更大程度的提升效率

但是，内连接下加在任意一张表上的优化程度都一样

三表查询索引优化

在两表查询索引优化案例的两张表的基础上，新建一张表并插入 20 条数据

CREATE TABLE IF NOT EXISTS phone(
    phoneid INT (10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    card INT(10) UNSIGNED NOT NULL
);

INSERT INTO phone(card) VALUES(FLOOR(1+(RAND())*20));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND())*20));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND())*20));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND())*20));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND())*20));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND())*20));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND())*20));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND())*20));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND())*20));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND())*20));

先查看前面两张中是否有除了主键以外的索引，有的话将其删干净

先来直接看三表连接的执行计划

explain 
select *
from class c left join book b
on c.`card` = b.`card`
left join phone p
on b.`card` = p.`card`\G

MySQL 高级(2) 索引优化 - 图38

不出意外的，访问类型全是 all

其实所谓的三表，以及更多的 n 表连接都可以拆解为两表连接，从 SQL 中不难看出，是先将 class 和 book 进行左连接的，因此可以在 book 上建索引，而后又将 class 与 book 的左连接结果与 phone 进行左连接，因此又可以在 phone 上建索引

建立索引后再次查看

create index idx_book_card on book(card);

create index idx_phone_card on phone(card);

explain 
select *
from class c left join book b
on c.`card` = b.`card`
left join phone p
on b.`card` = p.`card`\G

MySQL 高级(2) 索引优化 - 图39

可以看到，有两个查询优化成了 ref

多表优化的结论

尽可能减少 join 语句中嵌套循环的循环总次数，永远用小的结果集驱动大的结果集
优先优化嵌套循环中的内层循环
保证 join 语句中，被驱动表上作为条件连接的字段已经被索引
当无法保证被驱动表的 join 条件字段被索引且内存资源充足的情况下，不要太吝啬 join buffer 的大小设置

关于上述的结论中提及的驱动表，嵌套循环等概念，后面会再有提及

索引失效

首先创建一张表并插入数据

CREATE TABLE IF NOT EXISTS staffs(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(24) NOT NULL DEFAULT "",
    age INT NOT NULL DEFAULT 0,
    pos VARCHAR(20) NOT NULL DEFAULT "",
    add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO staffs(NAME, age, pos, add_time) 
VALUES
("z3", 22, "manager", NOW()),
("july", 23, "dev", NOW()),
("2000", 23, "dev", NOW());

CREATE INDEX idx_staffs_nameAgePos ON staffs(NAME, age, pos);

索引失效的原因有很多，下面列出十条常见的失效原因

tips：索引失效是针对于查询条件而言的，当查询条件不合适时才会发生索引失效，而与查询列表无关

不遵循最左匹配

explain
select * from staffs where name = "july"\G

MySQL 高级(2) 索引优化 - 图40

explain
select * from staffs where name = "july" and age = 25\G

MySQL 高级(2) 索引优化 - 图41

EXPLAIN
SELECT * FROM staffs WHERE NAME = "july" AND age=25 AND pos="dev"\G

MySQL 高级(2) 索引优化 - 图42

在上面三种情况下，都没有发生索引失效，这是因为我们使用的查询条件是按照我们所建索引的最左匹配来的，如果没有按照最左匹配原则来写查询条件 ( 从第一个查询条件开始，依次可以与所建的复合索引中的字段顺序对应上 )，则会发生索引失效

#不将 name 作为查询条件，将 name 这个最左索引字段排除在外，只用后面的索引进行查看
explain select * from staffs where age = 23 and pos="dev"\G

MySQL 高级(2) 索引优化 - 图43

可以发现，索引失效了

最左匹配原则简单来说，就是，如果索引了多个字段，就要遵守的一个法则，其有以下两个要求

指查询条件的第一个字段要和复合索引中最左边的字段对应（带头大哥不能丢）
不跳过索引中间部分的字段进行查询 ( 中间兄弟不能断 )

eg：查询条件为 name = “july” and pos = “dev” ( 条件没有 age )，虽然不会发生索引失效，但是只会有部分索引匹配，因为查询条件的字段顺序与索引的字段顺序并不是连续一致的

操作索引列

在索引列上做任何操作 ( 计算，函数，类型转换 ( 隐式或者显式 ) )，会导致索引失效而转为全表扫描

EXPLAIN SELECT * FROM staffs WHERE LEFT(NAME, 4) = "july"\G

MySQL 高级(2) 索引优化 - 图44

范围条件右边的列

由于复合索引在 B+ 树下的特性，范围条件 ( <、>、between and 等 ) 之后的索引将会失效（最左匹配原则的原理）

explain select * from staffs where name = "july" and age>20 and pos="dev"\G

MySQL 高级(2) 索引优化 - 图45

不难通过 ken_len 的大小看出，pos 索引失效了

计算过程如下，name 列类型为 varchar(24) 且不允许为 null，所以单单 name 索引的大小为 243+2 = 74。age 列类型为 int(11)，int 字节数为 4，所以 name + age 的 key_len = 78。所以 pos 索引失效了，如果没有实效，则应该是 78+( 203+2 ) = 140

select *

尽量使用覆盖索引 ( 查询列与索引列一致，或查询列包含部分索引列 )，减少使用 select *

当使用 select * 时

explain select * from staffs where name = "july" and age=23 and pos="dev"\G

MySQL 高级(2) 索引优化 - 图46

可以看见 Extra 为 NULL

当使用覆盖索引时

explain select name, age, pos from staffs where name = "july" and age=23 and pos="dev"\G

MySQL 高级(2) 索引优化 - 图47

Extra 就成了 Using index，比使用 select * 效率有所提升

使用不等于

MySQL 在使用不等于 ( != 或者 <> ) 的时候无法使用索引，从而导致索引失效从而全表扫描

EXPLAIN SELECT * FROM staffs WHERE NAME != "july"\G

MySQL 高级(2) 索引优化 - 图48

is null 和 is not null

is null 或者 is not null 也无法使用索引，从而导致索引失效

like

索引字段使用 like ，并且以通配符开头 ( “%abc…” 或者 “_abc…” ) 会导致索引失效，进而变为全表扫描

explain select * from staffs where name like "%uly"\G

MySQL 高级(2) 索引优化 - 图49

当通配符写在右边时，可以避免索引失效，或者刻意写成覆盖索引，也可以避免索引失效

explain select name from staffs where name like "%uly"\G

MySQL 高级(2) 索引优化 - 图50

Q：如何解决 like 中通配符写在左边导致索引失效的情况？

A：首先，新建一个表进行后续测试

create table if not exists tbl_user(
    id int(11) not null auto_increment primary key,
    name varchar(20) default null,
    age int(11) default null,
    email varchar(20) default null
);

insert into tbl_user(name, age, email)
values
("1aa1", 21, "b@163.com"),
("2aa2", 222, "a@163.com"),
("3aa3", 265, "c@163.com"),
("4aa4", 21, "d@163.com");

通常使用覆盖索引解决 like 中左边通配符问题

在只有主键索引的情况下，执行

EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE "%aa" and age = 21\G

不出意外的，是全表扫描![](https://gitee.com/antigenmhc/picture/raw/master/img/20201005195037.png#align=left&display=inline&height=262&margin=%5Bobject%20Object%5D&originHeight=262&originWidth=665&status=done&style=none&width=665)

然后，我们创建一个 name 字段 + age 字段的复合索引

CREATE INDEX idx_user_nameAge ON tbl_user(NAME, age);

再次执行上面查询

EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE "%aa" and age = 21\G

这时就相当于发生了覆盖索引![](https://gitee.com/antigenmhc/picture/raw/master/img/20201005195219.png#align=left&display=inline&height=262&margin=%5Bobject%20Object%5D&originHeight=262&originWidth=647&status=done&style=none&width=647)

总结：当 SQL 只需要通过索引就可以返回查询所需的数据时，就会发生覆盖索引。但是建议查询列表与条件列表中的字段数目，顺序都一致

tips：虽然 like 本身也是一种范围条件，但是当通配符在 like 的右边时，like 及其之后的索引并不会失效；相反的，只要当通配符在 like 左边时，使用 like 的字段上的索引以及后面的索引必定失效 (%xxx 或者 %xxx%)

口诀：like 常量来开头，like 及后都能用

字符串不加单引号

作为条件查询的字符串不加单引号 (或双引号) 会导致该字段上的索引失效

首先，staffs 表中有一行记录的 name 列为 2000

MySQL 高级(2) 索引优化 - 图51

select * from staffs where name = 2000;
select * from staffs where name = "2000";

分别运行上面两条 SQL，结果非常的 amazing 啊！居然都能查出来，具体原因会在后面说

并且 name 字段上是有索引的

MySQL 高级(2) 索引优化 - 图52

尝试运行以下语句

explain select * from staffs where name = 2000\G

MySQL 高级(2) 索引优化 - 图53

可以看见，name 字段的索引失效，发生了全表扫描

将 2000 改回为 “2000”

explain select * from staffs where name = "2000"\G

MySQL 高级(2) 索引优化 - 图54

可以看见，name 字段索引生效

在这个例子中，无论使用 2000 还是 “2000” ，都可以查出想要的数据。name 作为 varchar 类型，虽然我们的查询条件类型为整数，但是依然查出来了，这是因为 MySQL 底层做了隐式的转换，将 2000 这个整型做了一个 toString 的操作，将其转换为了字符串后再作为了查询条件，这就犯了索引失效的第二个原因：操作索引列中的类型转换。从而导致了不加 “” 或者 ‘’ 时的索引失效问题

or

少用 or，使用 or 连接会导致索引失效

EXPLAIN SELECT * FROM staffs WHERE NAME = "july" OR NAME = "z3"\G

MySQL 高级(2) 索引优化 - 图55

小总结

MySQL 高级(2) 索引优化 - 图56

全值匹配我最爱，最左优先要遵守

带头大哥不能丢，中间兄弟不能走

索引列上少计算，范围之后全没有

like 百分写最右，覆盖索引别写 *

不等判空还有or，导致失效要少用

varchar 引号不能丢，隐式转换坑别踩

索引面试题分析

有一张表及其数据如下

create table if not exists test03(
    id int primary key not null auto_increment,
    c1 char(10),
    c2 char(10),
    c3 char(10),
    c4 char(10),
    c5 char(10)
);

insert into test03(c1,c2,c3,c4,c5)
values 
("a1", "a2", "a3", "a4", "a5"),("b1", "b2", "b3", "b4", "b5"),
("c1", "c2", "c3", "c4", "c5"),("d1", "d2", "d3", "d4", "d5"),
("e1", "e2", "e3", "e4", "e5");

有一索引如下

CREATE INDEX idx_test03_c1234 ON test03(c1, c2, c3, c4);

根据以下 SQL 分析索引使用情况

explain select * from test03 where c1="a1";

在这条 SQL 中，使用到了索引 c1

explain select * where c1="a1" and c2="a2"

在这条 SQL 中，使用到了索引 c1 和 c2

explain select * from test03 where c1="a1" and c2 = "a2" and c3 = "a3";

使用到了 c1，c2 和 c3 三个索引

explain select * from test03 where c1="a1" and c2 = "a2" and c3="a3" and c4="a4";

使用到了 c1，c2， c3 和 c4 四个索引

explain select * from test03 
where c1="a1" and c2 = "a2" and c4="a4" and c3="a3";

我们将 c4 和 c3 的顺序调换了一下，结果非常的 amazing 啊！四个索引虽然没有按照建立索引时的顺序来写，但是居然都用到了

MySQL 高级(2) 索引优化 - 图57

无论我们如何打乱查询顺序，四个索引都依然会生效，这是因为 MySQL 底层自带的查询优化器会对查询进行自动调整，以达到最佳的查询效果。虽然查询顺序与索引顺序并不一样，但是在不影响查询结果的前提下，MySQL 会自己调节来让顺序一致

EXPLAIN SELECT * FROM test03 WHERE c1="a1" AND c2 = "a2" AND c4>"a4" AND c3="a3";

和上个例子类似，虽然使用到了范围查询，但是因为查询顺序与索引顺序并不一致，因此 MySQL 底层进行优化后的语句中，查询列表中的 c3 或者 c4 交换位置，导致最后 4 个索引都生效了

MySQL 高级(2) 索引优化 - 图58

EXPLAIN SELECT * FROM test03
WHERE c1="a1" AND c2="a2" AND c4="a4" ORDER BY c3;

在这个 SQL 中，c4 和 c3 的顺序颠倒了，但是因为 c3 作为的是排序索引，而 c4 作为的是查询索引，因此 MySQL 底层并不会去优化顺序，而 explain 信息中，也不会包含排序索引信息，因此最后只有两条索引被统计

MySQL 高级(2) 索引优化 - 图59

explain select * from test03
where c1="a1" and c2="a2" order by c3;

在这个 SQL 中，依然只有两个索引被统计，c3 作为排序索引虽然生效但是没有被统计

explain select * from test03
where c1="a1" and c2="a2" order by c4;

这个 SQL 的结果非常的 amazing 啊！

MySQL 高级(2) 索引优化 - 图60

可以清楚地看见发生了文件排序 (即使用到了索引)，这是因为排序索引的使用与前面的断开了，没有使用到 c3 ，导致 c1 和 c2 之后的索引失效，排序时使用 c4 进行排序索引无效，最后产生了文件排序

EXPLAIN SELECT * FROM test03 
WHERE c1="a1" AND c5 = "a5" ORDER BY c2, c3;

由于中间断开了，因此查找条件用到了 1 个索引，排序用到了 2 个索引，但是 explain 中只统计查找条件的索引

EXPLAIN SELECT * FROM test03 
WHERE c1="a1" AND c5 = "a5" ORDER BY c3, c2;

MySQL 高级(2) 索引优化 - 图61

这段 SQL 与上面那段 SQL 的区别只在于排序索引的顺序，在这段 SQL 中由于排序索引并没有按照索引创建顺序来写，因此失效了，产生了文件排序

EXPLAIN SELECT * FROM test03 
WHERE c1="a1" AND c2 = "a2" AND c5="a5" ORDER BY c3, c2;

MySQL 高级(2) 索引优化 - 图62

结果非常的 amazing 啊！居然没有用到文件排序

这段 SQL 与上一段 SQL 的区别仅仅在于查询条件索引中多了 c2，结果居然就没有用到了文件排序

首先，在正常情况下，如果排序索引顺序与索引创建顺序不一样时，都会出现文件排序。在排序索引有两个，且与索引创建顺序不一样的情况下，如果有排序索引同时作为了查询条件索引，并且与常量进行等值判断时，就不会出现文件排序 ( 因为这时进行等值判断的索引作为定值，在排序中退化为了排序无关索引 )

同理可推，三个顺序与索引顺序相逆的索引，则需要有两个作为条件索引 ( 并且顺序需要和索引顺序一致 ( 满足最左匹配原则等 )，不能出现失效 )

总结

使用 order by 需要格外注意排序索引与索引创建顺序是否一致，否则可能出现文件排序，如果当条件索引是排序索引的子集，且只比排序索引少一个索引时，无论排序索引是否与索引创建顺序一致，都不会出现文件排序

使用 group by 前，MySQL 底层会先排序，所以索引的原则与 order by 基本一致。但是由于 MySQL 版本的改进，一般在使用 group by 之后，查询列表就只能查询分组字段了，因此不会出现文件排序 (可以设置，但没必要)

对于单值索引，尽量选择针对当前查询过滤性更好的索引

在选择复合索引时，尽可能选择当前查询中过滤性最好的字段在索引顺序中的位置靠左的复合索引 ( 原因参考复合索引下 B+ 树底层的最左匹配原则 )

比如：name 是过滤性最好的字段，并且有一条 SQL 使用 name 作为条件进行查询
则：index(name, age, email) 和 index(age, name, email) 这两个索引，前者优于后者

在选择复合索引时，尽可能选择能够包含当前查询中的条件索引中更多字段的复合索引

尽可能通过分析执行计划和调整查询的写法来达到选择合适索引的目的