MySQL的优化器不是简单基于 规则 或 CBO（基于成本），而混合之用，但是倾向于基于成本优化为主的模式，但是一般都会根据统计信息，以及 SQL语句的成本，甚至服务器的空闲程度 而选择一个合适的计划....

并且mysql对SQL语句会进行改写的，对于执行计划有三个相关的参数：

optimizer_prune_level、optimizer_search_depth、optimizer_switch

另外至于MySQL查询优化器的规则资料，我也没有找到专门的资料，对于你的标题

“mysql索引优化器选择索引的规则是什么”.......这块到是有资料，只要读懂

MySQL手册章节：

7.2. Obtaining Query Execution Plan Information

7.5. Optimization and Indexes

基本上问题就不大了，当然这2个章节告诉你的是索引及SQL优化技术理论基础，还必须结合

实际的表结构、数据量及数据分布率、生产环境的硬件、服务器的压力等，又可能造成不一样

的优化和创建索引策略.....关于索引创建和优化的一篇参考性文章：

http://www.mysqlops.com/2011/05/23/mysql-create-and-optimize-index.html

内容为慕课网的"高并发 高性能 高可用 MySQL 实战"视频的学习笔记内容和个人整理扩展之后的笔记，本节内容讲述的索引优化的内容，另外本部分内容涉及很多优化的内容，所以学习的时候建议翻开《高性能Mysql》第六章进行回顾和了解，对于Mysql数据的开发同学来说大致了解内部工作机制是有必要的。

由于文章内容过长，所以这里拆分为两部分，上下部分的内容均使用sakila-db，也就是mysql的官方案例。第一部分讲述优化的理论和Mysql过去的优化器设计的缺陷，同时会介绍更高的版本中如何修复完善这些问题的（但是从个人看来新版本那些优化根本算不上优化，甚至有的优化还是照抄的Mysql原作者的实现的，发展了这么多年才这么一点成绩还是要归功于Oracle这种极致商业化公司的功劳）。

如果内容比较难，可以跟随《Mysql是怎么样运行》个人读书笔记专栏补补课，个人也在学习和同步更新中。
地址如下：https://juejin.cn/column/7024363476663730207。

• Mysql索引内容的介绍
• 索引的使用策略和使用规则
• 查询优化排查，简单了解Mysql各个组件的职责

sakila-db是什么？国外很火的一个概念，指的是国外的电影租赁市场使用租赁的方式进行电影的观看十分受外国的喜欢。这里介绍是因为后续的内容都用到了这个案例。所以我们需要提前把相关的环境准备好，从如下地址进行下载：

下载地址：https://dev.mysql.com/doc/index-other.html

《高性能Mysql》的SQL 案例也是使用官方的example

work-bench是官方开发的数据库关系图的可视化工具，使用官方案例的具体关系图展示效果如下，通过这些图可以看到Sakila-db之间的大致关系：

work-bench也是开源免费软件，下载地址如下：
https://dev.mysql.com/downloads/workbench/

安装workbench和下载sakila-db的方式这里不做记录，在运行的时候需要注意先建立一个数据库运行sheme文件，然后执行data的sql文件，最终在navicat中查看数据：

首先是索引的特点以及作用：

1. 索引的目的是为了提升数据的效率。
2. 对于ORM框架来说索引的使用至关重要，但是ORM的优化往往难以顾及所有业务情况，后续被逐渐废弃。
3. 不同的索引类型适用于不同的场景。
4. 索引关键在于减少数据需要扫描的量，同时避免服务器内部对内容排序和临时表（因为临时表会索引失效），随机IO转顺序IO等特点

下面介绍Mysql相关的索引类型：

• 哈希索引：哈希索引适合全值匹配和精确查找，查询的速度非常快 在MySQL中只有memory存储引擎显式支持此索引，memory还支持非唯一哈希索引的，是哈希索引设计里面比较特殊的。
• 空间索引：空间索引是myisam表支持，主要用作地理数据存储，这里包含一个叫做GIS的玩意，但是GIS在Postgre中使用比MySQL要出色很多，所以mysql中空间索引是无关紧要的东西。
• 全文索引：全文索引也是myisam独有支持的一种索引类型。适合使用的场景为全值匹配的场景和关键字查询，对于大文本的关键字匹配可以有效处理。
• 聚簇索引：聚簇索引是innodb存储引擎的默认存储引擎。
• 前缀压缩索引：注意这个索引针对的是myisam存储引擎，目的是为了让索引放入内存中排序，，前缀压缩的方法是首先保存索引块的第一个值，然后在保存第二个值，存储第二个值类似（长度,索引值）的形式存放前缀索引。

其他索引类型注意事项：

Archive 在5.1之后才支持单列自增索引。

MyISAM 支持压缩之后的前缀索引，使得数据结构占用更小。

哈希索引

在Mysql中唯一显式实现哈希索引的存储引擎为Memory，Memory是存在非唯一哈希索引，同时BTree也支持“自适应哈希索引的方式“兼容哈希索引。

下面是哈希索引特点：

• 键存储的是索引哈希值，注意不是索引值本身，而值存储的是指向行的指针
• 注意此哈希索引无法避免行扫描，但是在内存中指针非常快通常可以忽略不计
• 注意只有哈希值按照顺序排序，但是行指针不是按照顺序排序
• 哈希不支持：部分索引覆盖，只支持全索引覆盖，因为使用全部的索引列计算哈希值
• 哈希索引支持等值匹配操作不支持范围查询，比如等于，in子查询，不全等。
• 如果出现哈希冲突，哈希索引将退化为链表顺序查询，同时维护索引的开销也会变大

聚簇索引

聚簇表示数据行的值紧凑存储在一起。而innodb聚簇的值就是主键的值，所以通常使用都是主键上的索引，针对主键索引的选择十分重要。由于本部分着重索引优化，聚簇索引这里就不再讲述了。

MyISam和Innodb的主键索引区别是MyISam的索引很简单，因为数据行只包含行号，所以索引直接存储列值和行号，数据单独存放另一处，类似于一个唯一非空索引，索引和数据不在一处，MyISam的索引设计比InnoDB简单很多，这和MyIsam不需要支持事务也有直接关系，而innodb将索引和行数据放入一个数据结构，将列进行紧凑的存储。

聚簇索引有下面优点

• 紧凑存储数据行，所以可以只扫描少量磁盘就可以获取到数据
• 数据访问的速度非常快，索引和数据放在同一颗BTree中，比非聚簇索引查询快很多
• 覆盖索引可以直接减少回表

当然索引也有下面的缺点：

• 对于非IO密集型应用，聚簇索引的优化无意义。
• 插入速度依赖于插入顺序，但是如果不是自增插入则需要optimize table重新组织表。
• 更新代价非常高，因为BTree要保证顺序排序需要挪动数据页位置和指针。
• 主键数据插入过满数据页存在页分裂问题，行溢出会导致存储压力加大。
• 聚簇索引导致全表扫描变慢，页分裂导致数据问题等。
• 二级索引需要回表查询聚簇索引才能查询数据。
• 二级索引由于需要存储主键开销会更大，至少在InnoDb中维护一个二级索引的开销是挺大的。

压缩索引

压缩索引的特点是使用更少的空间存放尽可能多的内容，但是这样的处理方式仅仅适用于IO密集型的系统，压缩前缀存储形式最大的缺陷是无法使用二分法进行查找，同时如果使用的倒序索引的方式比如order by desc 的方式可能会因为压缩索引的问题存在卡顿的情况。

Bree索引的特点

• 叶子结点存在逻辑页和索引页两种，通常非最底层叶子结点都是索引页，最底层索引页由链表串联。
• Btree索引会根据建表顺序对于索引值进行排序，索引建表时候建议将经常查询的字段往前挪。
• Btree索引适合的查询类型：前缀查询，范围查询，键值查询（哈希索引）。

自适应哈希索引

当innodb发现某些索引列和值使用频繁的时候，BTree会在此基础上自动创建哈希索引辅助优化，但是这个行为是不受外部控制的，完全是内部的优化行为，如果不需要可以考虑关闭。

Btree查询类型

针对Innodb的Btree索引，有下面几种常见的查询方式：

• 全值匹配：等值匹配的方式，全值匹配适合哈希索引进行查询
• 最左匹配原则：二级索引的查询条件放在where最左边
• 前缀匹配：只使用索引的第一列，并且like ‘xxx%’
• 范围匹配：范围匹配索引列到另一列之间的值
• 范围查询和精确匹配结合，一个全值匹配，一个范围匹配
• 覆盖索引查询：覆盖索引也是一种查询方式，

下面是关于建立索引的一些常见策略：

1. 第一件事情需要考虑的是预测那些数据为热点数据或者热点列，按照《高性能Mysql》介绍，对于热点列来说有时候要违背最大选择性的原则，通过建立时常搜索的索引作为最左前缀的默认的设置。同时优化查询需要考虑所有的列，如果一个查询的优化会破坏另一个查询，那么就需要优化索引的结构。
2. 第二件事情是考虑where的条件组合，通过组合多种where条件，需要考虑的是尽可能让查询重用索引而不是大规模的建立新索引。
3. 避免多个范围进行扫描，一方面是范围查询会导致，但是对于多个等值的条件查询，最好的办法是尽量控制搜索范围。

对于索引的策略我们还需要了解下面的细节

• 单行访问很慢，特别是随机访问要比顺序访问要慢更多，一次性加载很多数据页会造成性能的浪费。
• 顺序访问范围数据很快，顺序IO的速度不需要多磁道查找，比随机的访问IO块很多，顺序访问也可以使用group by进行聚合计算。
• 索引覆盖速度很快，如果查询字段包含了索引列，就不需要回表。

索引碎片优化

Innodb的数据结构和特性会导致索引存在数据碎片，对于任何存储结构来说顺序的存储结构是最合适的，并且索引顺序访问要比随机访问快更多，数据存储的碎片比索引本身复杂很多，索引碎片通常包含下面的情况：

• 行碎片：数据行的数据被存储在多个数据页当中，碎片可能会导致性能的下降。
• 行间碎片：逻辑顺序上的页，行在磁盘上不顺序存储，行间数据碎片会导致全表扫描。
• 剩余空间碎片：数据页的间隙有大量的垃圾数据导致的浪费。

对于上面几点，对于myisam 都有可能出现，但是innodb的行碎片不会出现，内部会移动碎片重写到一个片段。

索引碎片的处理方式：在Mysql中可以通过optimize table导入和导出的方式重新整理数据，防止数据碎片问题。

索引规则

• 索引必须按照索引顺序从左到右匹配
• 如果在查询中间出现范围，则范围查询之后的索引失效
• 不能跳过索引列的方式查询（和B+tree索引数据结构设计有关系）

接着是索引顺序问题，由于BTree的结构特性，索引都是按照建立顺序进行查找的，通常不包含排序和分组的情况下，把选择性最高的索引放在最左列是一个普遍正确策略。

如何查看索引基数：show index from sakila.actor，还有一种方式是通过information_schema.statistics 表查询这些信息，可以编写为一个查询给出选择性较低的索引。

当innodb打开某些表的时候会触发索引信息的统计，比如打开information_schema表或者使用show table status和show index的时候，所以如果在系统要运行压力较大的业务时期尽量避开这些操作。

冗余重复索引

Mysql允许同一个列上创建多种类型的索引，有时候会因为建表的特性问题给字段重复建索引造成不必要的性能浪费。冗余索引和重复索引有什么区别？

冗余索引：是符合最左匹配法则的情况下重复对相同列建立索引。

重复索引：是对于不最做的方式创建的索引就有可能是重复创建索引。

比如联合索引：(A,B) 如果在创建 （A）或者（A，B）都是重复索引，但是创建（B）就不是重复索引而是冗余索引。另外某些十分特殊的情况下可能用到冗余索引，但是这会极大的增加索引维护的开销，最为直观的感受是插入、更新、删除的开销变得很大。

多列索引

首先多列索引不是意味着where字段出现的地方就需要加入，其次多列索引虽然在现在主流使用版本中（5.1版本之后）实现了索引内部合并，也就是使用and or或者and和or合并的方式相交使用索引，但是他存在下面几个缺点

• 内部优化器的合并和计算十分耗费CPU的性能，索引反而增加数据查询复杂度，效率也不好
• 往往会存在优化过度的情况，导致运行效果还不如全表扫描
• 出现多列索引合并通常意味着建立索引的方式不对，存在反向优化的嫌疑

文件排序

文件排序遵循Innodb的Btree索引的最基本原则：最左前缀原则，如果索引列的顺序和order by排序一致，并且查询列都和排序列都一样才会用索引替代排序，对于多表查询则排序字段全为第一个表才能进行索引排序。但是有一个特例那就是排序字段的前导列为常量的时候依然可以使用索引排序。

案例：rental 表的联合索引列进行排序

Backward index scan 是 MySQL-8.0.x 针对上面场景的一个专用优化项，它可以从索引的后面往前面读，性能上比加索引提示要好的多

EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' order by inventory_id desc, customer_id asc;
-- 1 SIMPLE rental ref rental_date rental_date 5 const 1 100.00 Using filesort
?
EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' order by inventory_id desc;
-- Backward-index-scan
-- Backward index scan 是 MySQL-8.0.x 针对上面场景的一个专用优化项，它可以从索引的后面往前面读，性能上比加索引提示要好的多
-- 1 SIMPLE rental ref rental_date rental_date 5 const 1 100.00 Backward index scan
?
EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' order by inventory_id, staff_id;
-- 1 SIMPLE rental ref rental_date rental_date 5 const 1 100.00 Using filesort
-- 无法使用索引
EXPLAIN select rental_id,staff_id from rental where rental_date > '2005-05-25' order by inventory_id, customer_id;
-- 1 SIMPLE rental ALL rental_date 16008 50.00 Using where; Using filesort
?
EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' and inventory_id in (1,2) order by customer_id;
-- 1 SIMPLE rental range rental_date,idx_fk_inventory_id rental_date 8 2 100.00 Using index condition; Using filesort
?
explain select actor_id, title from film_actor inner join film using(film_id) order by actor_id;
-- 1 SIMPLE film index PRIMARY idx_title 514 1000 100.00 Using index; Using temporary; Using filesort
-- 1 SIMPLE film_actor ref idx_fk_film_id idx_fk_film_id 2 sakila.film.film_id 5 100.00 Using index

查询优化的排查意味着我们需要先了解Mysql的各个组件在各步骤中做了哪些事情，下面这张图来自于《高性能Mysql》，对于一次客户端的请求，大致分为下面的流程：

1. 客户端发送请求
2. 服务器查询执行缓存
   

• 不重要，8.0之后已经删除

1. 服务端进行SQL解析和预处理
   

• 权限检查
• 词法解析
• 语法树

1. 优化器生成执行计划
   

• 优化器存在的问题？
• 优化器如何工作？

1. 根据执行计划调用存储引擎的APi接口执行查询
2. 结果返回客户端

对于关系型的数据库来说，核心部分在于查询优化器和执行计划的部分，因为不管我们如何编写SQL语句，如果没有强大的优化器和执行计划那么一切都是空谈，所以本部分的重点也会围绕优化器进行讲解，在此之前我们先看看其他组件的工作：

首先查询缓存不需要过多解释，他的作用是当用户重复执行一个查询的时候会内部对于结果进行缓存，但是一旦用户修改查询条件，缓存就失效了，在早期的互联网环境中这种处理很不错，可以减少磁盘IO和CPU的压力，但是到了现在的环境下显然不适合，所以8.0删除也是可以理解的。

接着是解析器，解析器这部分主要工作是通过解析语法形成解析树对于语句进行预处理，预处理可以类看作我们编译器把我们写的编程语句“翻译”为机器代码的过程，让下一步的优化器可以认识这颗解析树去进行解析，

如果想要了解SQL解析优化的底层过程，可以从这篇文章入手：

SQL解析在美团的应用 - 美团技术团队 (meituan.com)

在上面的博客中提到了一个DBA必须掌握的工具pt-query-digest，分析慢查询日志，下面这个文章中提供了一个实际的案例来排查和优化，案例较为简单适合刚接触这个工具的人进行学习和思考，这里一并列出来了。

使用 pt-query-digest 分析 RDS MySQL 慢查询日志 | 亚马逊AWS官方博客 (amazon.com)

SQL解析部分笔记：
词法分析：核心代码在sql/sql_lex.c文件中的，MySQLLex→lex_one_Token
MySQL语法分析树生成过程：全部的源码在sql/sql_yacc.yy中，在MySQL5.6中有17K行左右代码
最核心的结构是SELECT_LEX，其定义在sql/sql_lex.h中

下面我们来深入看看优化器的部分工作内容以及Mysql优化历史：

由于讲述优化器的内容较少，这里直接总结《高性能Mysql》的内容，优化器也不需要研究和记忆，因为随着版本的迭代不断更新优化器会不断调整，一切要以真实实验为准：

1. 子查询关联：

下面的查询在通常情况下我们会认为先进行子查询，然后通过for循环扫描film表进行匹配操作，然后从explain的结果中可以看到这里的查询线进行了全表扫描，然后通过关联索引进行第二层的for循环查询，这样的写法类似exists。

explain select * from sakila.film where film_id in (select film_id from film_actor where actor_id)
-- 1  SIMPLE  film    ALL PRIMARY       1000  100.00  
-- 1  SIMPLE  film_actor    ref idx_fk_film_id  idx_fk_film_id  2 sakila.film.film_id 5 90.00 Using where; Using index; FirstMatch(film)

优化这个子查询的方式使用关联查询替代子查询，但是需要注意这里存在where条件才会走索引，否则和上面的结果没有区别：

explain select film.* from sakila.film film  join film_actor actor using (film_id) where actor.actor_id = 1

另一种是使用exists的方式进行关联匹配。

explain select * from film where exists (select * from film_actor actor where actor.film_id =  film.film_id and actor.actor_id = 1);

可以看到哪怕到了5.8的版本，Mysql的子查询优化既然没有特别大的改进，所以通常情况下如果不确定in查询的内容大小，建议用exists或者join进行查询，另外也不要相信什么in查询就一定慢点说法，在不同的mysql优化器版本中可能会有不同的效果。

2. union查询

虽然多数情况下我们会用union替换or，但是更多的情况是应该尽量避免使用union，因为union查询会产生临时表和中间结果集容易导致优化索引失效，需要注意的是 union会触发内部的排序动作，也就是说union会等价于order by的排序，如果数据不是强烈要求不能重复，那么更建议使用union all，对于优化器来说这样工作更加简单，直接把两个结果集凑在一起就行，也不会进行排序。

union查询能不用就不用，除非是用来代替or查询的时候酌情考虑是否有必要使用。

最后注意union的产生排序不受控制的，可能会出现意料之外的结果。

3. 并行查询优化

并行查询优化在8.0中终于有了实现，可以根据参数：innodb_parallel_read_threads=并行数来验证。

由于个人是M1的CPU，读者可以根据自己的实际情况进行实验。

set local innodb_parallel_read_threads = 1;
select count(*) from payment;
set local innodb_parallel_read_threads = 6;
select count(*) from payment;

从执行结果可以看到仅仅是1万多条数据的count(*)查询就有明显直观的差距：

4. 哈希关联

官方文档的介绍地址：Mysql官方文档哈希关联

在MySQL 8.0.18中Mysql终于增加了哈希关联的功能。在此之前的版本中，Mysql的优化器通常只支持for循环嵌套关联，曲线救国的方法是建立一个哈希索引或者使用Memory存储引擎，而新版本提供的哈希关联则提供了一种新的对关联方式，哈希关联的方式如下：

把一张小表数据存储到内存中的哈希表里，通过匹配大表中的数据计算哈希值，并把符合条件的数据从内存中返回客户端。

对于Mysql的哈希关联，我们直接使用官方的例子：

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);
?
EXPLAIN
     SELECT * FROM t1
         JOIN t2 ON t1.c1=t2.c1;
-- Using where; Using join buffer (hash join)

除开等值查询以外，Mysql的8.0.20之后提供了更多的支持，比如在 MySQL 8.0.20 及更高版本中，连接不再需要包含至少一个等连接条件才能使用哈希连接，除此之外它还包括下面的内容：

-- 8.0.20 支持范围查询哈希关联
EXPLAIN  SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1;
-- 8.0.20 支持 in关联
EXPLAIN  SELECT * FROM t1 
        WHERE t1.c1 IN (SELECT t2.c2 FROM t2);
-- 8.0.20 支持 not exists 关联
EXPLAIN  SELECT * FROM t2 
         WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c1 = t2.c2);
-- 8.0.20 支持 左右外部连接
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1;

注意8.0.18版本的哈希关联仅仅支持join查询，对于可能会带来笛卡尔积的左连和右连接查询是不支持的。但是在后续的版本中提供了更多查询条件支持
另外，8.0.20版本之前想要查看是否使用hash join，需要结合 format=tree 选项。

最终Mysql在8.0.18版本中曾经提供过开关哈希索引和设置优化器提示optimizer_switch等参数来判定是否给予hash join的提示，真是闲的蛋疼（官方自己也这么认为）所以在8.0.19立马就把这些参数给废弃。

注意哈希连接不是没有限制的，了解哈希关联的流程就会发现如果哈希表过大，会导致整个哈希关联过程在磁盘中完成其速度可想而知，所以官方提供了下面的建议：

• 增加join_buffer_size，也就是增加哈希关联的哈希表缓存大小，防止进入磁盘关联。
• 增加open_files_limit数量，这个参数什么意思这里就不介绍了，意义是增加这个参数可以增加关联的时候关联次数。

吐槽：说句心里话自Mysql被Oracle收购之后，越来越商业化的同时进步也越来越小，in查询优化这一点其实在很多开源库甚至Mysql的原作者给解决了，但是Mysql到了8.0依然和多年前的《高性能Mysql》结果没有差别。哎。。。。。
Mysql数据库的发展也告诉我们时刻保持开放的心态，吸取教训正视不足和改进，才不会被时代逐渐淘汰。

5. 松散索引

松散索引在Mysql5.6之后已经支持，松散索引简单理解就是在进行多列索引扫描的时候，即使次索引不是有序的，但是跳过索引是有序的，也可以走索引来快速匹配数据。

松散索引的优化细节放到了下半部分的文章，这里简单讲述一下大致的工作原理。

1. 查询同时更新数据

在Postgresql中，支持下面的语法：

update tbl_info
set name = tmp.name
from 
(select name from tbl_user where name ='xxx')
tmp
[where ....]
?
-- 比如下面的写法：
UPDATE `sakila`.`actor` SET `first_name` = 'PENELOPE'
from 
(select address,address_id from address where address_id = 1) tmp
 WHERE `actor_id` = 1 and actor.actor_id = tmp.address_id;

但是很可惜这种语法在Mysql是没有办法实现也是不支持的，哪怕到了8.0.26依然没有支持，这和Mysql的优化器设计有着本质的关系。

1. 优化器提示设置

优化器提示没有多少意义，这里直接略过了。

1. 最大值和最小值优化

从实际的情况来看Mysql最大值和最小值这两个函数使用并不是很多所以不再进行介绍了，另外无论什么样的数据库都不是很建议频繁使用函数，而是改用业务+简单SQL实现高效索引优化。

其他慢查询优化

对于慢查询的优化我们需要清楚优化是分为几种类别的，在Mysql中优化策略分为动态优化和静态优化：静态优化主要为优化更好的写法，比如常数的排序和一些固定的优化策略等，这些动作通常在一次优化过程中就可以完成。而动态优化策略要复杂很多，可能会在执行的过程中优化，有可能在执行过后重新评估执行计划。

静态优化是受优化器影响的，不同版本有不同情况，所以这里讲述动态优化的情况，而动态优化主要包含下面的内容：

• 关联表顺序，有时候关联表顺序和查询顺序不一定相同。
• 重写外连接为内连接：如果一个外连接关联是没有必要的就优化掉外连接关联。
• 等价替换，比如 a>5 and a=5被优化为a >=5 ，类似数学的逻辑公式简化
• 优化count()、max()、min()等函数：有时候找最大和最小值只需要找最大和最小的索引记录，这时候由于不需要遍历，可以认为直接为哈希的获取记录的方式，所以在查询分析的 extra 里面进行体现（Select tables optimized away），比如：explain select max(actor_id) from actor;
• 预估和转化常数：以连接查询为例，如果在查询条件中可以实现预估关联的记录条数，那么对于一个关联查询来说就有可能被优化器作为常数进行优化，因为事先取出记录的条数被优化器知晓。所以优化起来十分简单。
• 子查询优化：子查询虽然有可能被索引优化但是需要尽量避免使用。
• 覆盖索引扫描：让索引和查询列一致，是非常高效的优化和执行方式
• 提前终止查询：提前终止查询指的是当遇到一些查询条件会让查询提前完成，优化器会提前判断加快数据的匹配和搜索速度
• 等值传递，如果范围查询可以根据关联表查询优化，那么无需 显式的提示则可以直接搜索数据。

这里汇总了文章中出现的一些参考资料：

• Mysql官方文档哈希关联
• SQL解析在美团的应用 - 美团技术团队 (meituan.com)
• 使用 pt-query-digest 分析 RDS MySQL 慢查询日志 | 亚马逊AWS官方博客 (amazon.com)

上半部分以理论为主，下半部分将会着重实战内容进行介绍。

• 使用 explain 关键字可以模拟优化器执行SQL语句，分析查询语句或结构的性能瓶颈
• 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL
• 注意：
• 如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中

• explain分析示例参考官方文档：
• https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
 `id` int(11) NOT NULL,
 `name` varchar(45) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 INSERT INTO `actor`(`id`, `name`, `update_time`) VALUES (1, 'a', '2022-05-14 22:37:14'), (2, 'b', '2022-05-14 22:38:19'),(3, 'c', '2022-05-14 22:38:30');

 DROP TABLE IF EXISTS `film`;
 CREATE TABLE `film` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_name` (`name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

 DROP TABLE IF EXISTS `film_actor`;
 CREATE TABLE `film_actor` (
 `id` int(11) NOT NULL,
 `film_id` int(11) NOT NULL,
 `actor_id` int(11) NOT NULL,
 `remark` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_film_actor_id` (`film_id`,`actor_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

• 会在 explain 的基础上额外提供一些查询优化的信息
• 紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么
• 额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的

表）

explain extended select * from film where id=1;

• 结果：

• 引：

 show warnings;

• 相比 explain 多了 partitions 字段
• 如果查询是基于分区表的话，会显示查询将访问的分区

explain partitions select * from film where id=1;

• 展示 explain 中每个列的信息，需要结合B+树理解
• select_type\ ype\\key等几列更重要
• 另外，注意掌握覆盖索引

• id列的编号是 select 的序列号
• 有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的
• id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行

• select_type 表示对应行是简单还是复杂的查询
• 常见类型：

1. simple：简单查询。查询不包含子查询和union

 explain select * from film where id=2;

1. primary：复杂查询中最外层的 select
2. subquery：包含在 select 中的子查询（不在 from 子句中）
3. derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

primary、subquery、derived综合示例

set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合并优化

explain select (select 1 from actor where id=1) from (select * from film where id=1) der;

1. union：在 union 中的第二个和随后的 select

explain select 1 union all select 1;

• 这一列表示 explain 的一行正在访问哪个表
• 当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询
• 当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id

• 这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围
• 依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
• 一般来说，得保证查询达到range级别，最好达到ref

类型说明

• NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引
• 例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

explain select min(id) from film;

• const：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）
• 用于primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快
• system：是const的特例，表里只有一条元组匹配时为system

const、system示例

explain extended select * from (select * from film where id=1) tmp;

• eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录
• 这可能是在const 之外最好的联接类型，简单的 select 查询不会出现这种 type

explain select * from film_actor left join film on film_actor.film_id=film.id;

• ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行：

1. 简单 select 查询，name是普通索引（非唯一索引）

 explain select * from film where name='film1';

1. 关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分

explain select film_id from film left join film_actor on film.id=film_actor.film_id;

• range：走索引的范围查询/扫描，范围扫描通常出现在 in(), between ,> ,<, >=等操作中。使用一个索引来检索给定范围的行

explain select * from actor where id > 1;

• index：扫描全索引就能拿到结果，一般是扫描某个二级索引。
• 这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的
• 这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些

explain select * from film;

• ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点
• 通常情况下这需要增加索引来进行优化

explain select * from actor;

mysql选用索引原则：

查找结果集如果在主键索引和辅助索引中都有，则选辅助索引（因为辅助索引小，效率更高）

• 这一列显示查询可能使用哪些索引来查找
• explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询
• 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果

• 这一列显示mysql实际采用哪个索引来优化对该表的访问
• 如果没有使用索引，则该列是 NULL
• 如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index

• 这一列显示了mysql在索引里使用的字节数
• 通过这个值可以算出具体使用了索引中的哪些列
• 举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找

 explain select * from film_actor where film_id=2;

• key_len计算规则如下：
• 字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
• char(n)：如果存汉字长度就是 3n 字节
• varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
• 数值类型
• tinyint：1字节
• smallint：2字节
• int：4字节
• bigint：8字节
• 时间类型
• date：3字节
• timestamp：4字节
• datetime：8字节
• 如果字段允许为 NULL，需要1字节记录是否为 NULL
• 索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

• 这一列显示了在key列记录的索引中，表查找值所用到的列或常量
• 常见的有：const（常量），字段名（例：film.id）

• 这一列是mysql估计要读取并检测的行数
• 注意：
• 这个不是结果集里的行数

• 这一列展示的是额外信息
• 常见的重要值如下：

1. Using index：使用覆盖索引

• 覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index
• 覆盖索引查找效率也很高：覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

 explain select film_id from film_actor where film_id=1;

1. Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖

explain select * from actor where name='a';

1. Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

explain select * from film_actor where film_id > 1;

1. Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化
1. actor.name没有索引，此时创建了张临时表来distinct

explain select distinct name from actor;

1. film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表

explain select distinct name from film;

1. Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化
1. actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录

explain select * from actor order by name;

1. film.name建立了idx_name索引,此时查询时extra是using index

explain select * from film order by name;

1. Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时

explain select min(id) from film;

CREATE TABLE `employees` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
 PRIMARY KEY (`id`),
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

 INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei',23,'dev',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

EXPLAIN SELECT * FROM employees WHERE name='LiLei';

EXPLAIN SELECT * FROM employees WHERE name='LiLei' AND age=22;

EXPLAIN SELECT * FROM employees WHERE name='LiLei' AND age=22 AND position='manager';

• 如果索引了多列，要遵守最左前缀法则
• 指的是查询从索引的最左前列开始并且不跳过索引中的列

EXPLAIN SELECT * FROM employees WHERE name='Bill' and age=31;
EXPLAIN SELECT * FROM employees WHERE age=30 AND position='dev';
EXPLAIN SELECT * FROM employees WHERE position='manager';

EXPLAIN SELECT * FROM employees WHERE name='LiLei';
EXPLAIN SELECT * FROM employees WHERE left(name,3)='LiLei';

• 给hire_time增加一个普通索引：

ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
EXPLAIN select * from employees where date(hire_time)='2018‐09‐30';

• 转化为日期范围查询，有可能会走索引：

EXPLAIN select * from employees where hire_time >='2018‐09‐30 00:00:00' and hire_time <='2018‐09‐30 23:59:59';

• 还原最初索引状态：

ALTER TABLE `employees` DROP INDEX `idx_hire_time`;

EXPLAIN SELECT * FROM employees WHERE name='LiLei' AND age=22 AND position='manager';
EXPLAIN SELECT * FROM employees WHERE name='LiLei' AND age > 22 AND position='manager';

EXPLAIN SELECT name,age FROM employees WHERE name='LiLei' AND age=23 AND position='manager';

EXPLAIN SELECT * FROM employees WHERE name='LiLei' AND age=23 AND position='manager';

< 小于、 > 大于、 <=、>=这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

EXPLAIN SELECT * FROM employees WHERE name !='LiLei';

EXPLAIN SELECT * FROM employees WHERE name is null;

EXPLAIN SELECT * FROM employees WHERE name like '%Lei';

1. 使用覆盖索引，查询字段必须是建立覆盖索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';

1. 如果不能使用覆盖索引则可能需要借助搜索引擎

EXPLAIN SELECT * FROM employees WHERE name='1000';
EXPLAIN SELECT * FROM employees WHERE name=1000;

用它们查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

EXPLAIN SELECT * FROM employees WHERE name='LiLei' or name='HanMeimei';

• e.g：给年龄添加单值索引

ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE;
explain select * from employees where age >=1 and age <=2000;

• 没走索引原因：
• mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
• 比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引
• 优化方法：
• 可以将大的范围拆分成多个小范围

explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000;

• 还原最初索引状态：

ALTER TABLE `employees` DROP INDEX `idx_age`;

• 假设index(a,b,c)

说明：

• like KK%相当于=常量，%KK和%KK% 相当于范围

‐‐ mysql5.7关闭ONLY_FULL_GROUP_BY报错
select version(), @@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

对 MySQL 来讲，一条 SQL 语句从客户端发出，到数据库端返回结果，一般会经历几个阶段：词法解析、语法解析、语义解析、逻辑优化、物理优化、最终执行并返回结果。

整个过程，SQL执行得快不快很大程度上依赖于索引，而通常一张表上会有多个索引，那么 MySQL 优化器是如何选择的呢？

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。

这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。

而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。

也就是说，这个基数越大，索引的区分度越好。我们可以使用 show index 方法，看到一个索引的基数。

[root@127.0.0.1@3306][test][11:13:46]> show index from test;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| test  |          0 | PRIMARY  |            1 | ID          | A         |      842914 |     NULL | NULL   |      | BTREE      |         |               |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 rows in set (0.00 sec)

● Table：索引所在的表名。

● Non_unique：0表示该索引是唯一索引，1表示该索引不是唯一索引。

● Key_name：索引的名称。

● Seq_in_index：索引列在索引中的位置，从1开始。对于组合索引来说，这个字段很重要。

● Column_name：索引列的名称。

● Collation：索引列的值以什么方式存储在索引中。在MySQL中，A 表示有排序，B+树索引使用该方式；NULL 表示无序的，Heap索引使用该方式。

● Cardinality：索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。索引列所有值去重后的数量，该值除以该表的行数越接近1越好，如果非常小，则需要考虑是否可以删除该索引。

● Sub_part：数值 N 表示只对该列的前 N 个字符进行索引；NULL 表示索引整个列的值。

● Packed：表示关键字是否被压缩，NULL 表示没有压缩。

● Null：索引列是否可以为空。

● Index_type：索引类型，BTREE 表示B+树索引。一共有四种（BTREE, FULLTEXT, HASH, RTREE）。

● Comment：注释。

● Index_comment：注释。

采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent 的值来选择：

设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是20，M 是 10。

设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是8，M 是 16。

1、区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）；

2、尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）；

3、使用最频繁的列放到联合索引的左侧（这样可较少的建立一些索引）。

1、对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决。

2、而对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引。

select * from t force index(idx_num) where num between 10000 and 20000;

3、也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

1、索引要建在使用查询比较多的字段上。

2、尽量不要在相同值比较多的列建立索引，比如性别、年龄等字段。

3、对于经常进行数据存取的列不要建立索引。

4、对于有外键引用的表，在主键和外键上建立索引。

5、限制每张表上的索引数量，建议单张表索引不超过5个索引。如索引很有必要，也可酌情增加。

6. 避免重复的索引，如：index(a,b,c)，index(a,b)，index(a)，重复的和冗余的索引会降低查询效率，因为MySQL查询优化器会不知道该使用哪个索引。

1、索引一般在<，>，<=，>=，between，in以及右%下的like等操作符上才能使用。

2、如果使用like，则%或_不能位于开头。

3、如果使用多列索引，则第一列必须包含匹配的条件。

4、如果在列上使用函数则不能使用索引。

5、目前mysql5.7 innodb存储引擎中索引允许的最大长度是3072 bytes，其中unqiue key最大长度是1000 bytes。

6、创建唯一索引的字段，都不能允许为null，否则mysql的唯一性约束可能会失效。

7、创建前缀索引，虽然节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。

1、索引的原理是拿额外的存储空间换取查询时间，增加了写入数据的开销，但使读取数据的时间复杂度一般从O(n)降低到O(logn)甚至O(1)。

2、创建索引会占用磁盘空间，尤其是表很大，且创建索引的字段值比较多，内容比较长的话，更是如此。

3、索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。

4、优化器在选择索引时，会根据统计信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。