【4. MySQL 性能优化】

最新推荐文章于 2022-09-27 09:47:05 发布

岁月玲珑

最新推荐文章于 2022-09-27 09:47:05 发布

阅读量1k

点赞数 1

分类专栏： mysql 文章标签：性能优化 mysql 服务器

本文链接：https://blog.csdn.net/yin18827152962/article/details/121888692

版权

mysql 专栏收录该内容

18 篇文章 1 订阅

订阅专栏

MySQL 性能优化

总论

MySQL 性能优化其实是个很大的课题，在优化上存在着一个调优金字塔的说法：
在这里插入图片描述

很明显从图上可以看出，越往上走，难度越来越高，收益却是越来越小的。比如硬件和 OS 调优，需要对硬件和 OS 有着非常深刻的了解，仅仅就磁盘一项来说，一般非 DBA 能想到的调整就是 SSD 盘比用机械硬盘更好，但其实它至少包括了，使用什么样的磁盘阵列（RAID）级别、是否可以分散磁盘 IO、是否使用裸设备存放数据，使用哪种文件系统（目前比较推荐的是 XFS），操作系统的磁盘调度算法（目前比较推荐 deadline，对机械硬盘和 SSD 都比较合适。从内核2.5 开始，默认的 I/O 调度算法是 Deadline，之后默认 I/O 调度算法为 Anticipatory，直到内核2.6.17 为止，从内核2.6.18 开始，CFQ 成为默认的 IO 调度算法，但 CFQ 并不推荐作为数据库服务器的磁盘调度算法。）选择，是否需要调整操作系统文件管理方面比如 atime 属性等等。

TIPS：裸设备(raw device)，也叫裸分区（原始分区），是一种没有经过格式化，不被 Unix 通过文件系统来读取的特殊块设备文件。由应用程序负责对它进行读写操作。不经过文件系统的缓冲。它是不被操作系统直接管理的设备。这种设备少了操作系统这一层，I/O 效率更高。

TIPS：小演示，查看磁盘调度算法：

# dmesg |grep -i scheduler 
# df -m 
# more /sys/block/vda/queue/scheduler

永久地修改 IO 调度算法,需要修改内核引导参数

[root@localhost ~]# dmesg |grep -i scheduler
[ 0.001000] rcu: RCU calculated value of scheduler-enlistment delay is 100 jiffies.
[ 2.698654] io scheduler mq-deadline registered
[ 2.698660] io scheduler kyber registered
[ 2.698718] io scheduler bfq registered
[root@localhost ~]# df -m
Filesystem 1M-blocks  Used Available Use% Mounted on
devtmpfs 877 0 877 0%/dev
tmpfs 896 0 896 0%/dev/shm
tmpfs 896 18 878 2%/run
tmpfs 896 0 896 0%/sys/fs/cgroup
/dev/nvme0n1p3 18121 6690 11432 37%/
/dev/nvme0n1p1 295 174 122 59%/boot
tmpfs 180 2 178 1%/run/user/42
tmpfs 180 5 175 3%/run/user/0
[root@localhost ~]# more /sys/block/
nvme0n1/ sr0/
[root@localhost ~]# more /sys/block/nvme0n1/queue/scheduler 
[none] mq-deadline kyber bfq

所以在进行优化时，首先需要关注和优化的应该是架构，如果架构不合理，即使是 DBA 能做的事情其实是也是比较有限的。

对于架构调优，在系统设计时首先需要充分考虑业务的实际情况，**是否可以把不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去做；**然后考虑写的并发量有多大，是否需要采用分布式；最后考虑读的压力是否很大，是否需要读写分离。对于核心应用或者金融类的应用，需要额外考虑数据安全因素，数据是否不允许丢失。

作为金字塔的底部的架构调优，采用更适合业务场景的架构能最大程度地提升系统的扩展性和可用性。在设计中进行垂直拆分能尽量解耦应用的依赖，对读压力比较大的业务进行读写分离能保证读性能线性扩展，而对于读写并发压力比较大的业务在 MySQL 上也有采用读写分离的大量案例。

作为金字塔的底部，在底层硬件系统、SQL 语句和参数都基本定型的情况下，单个 MySQL 数据库能提供的性能、扩展性等就基本定型了。但是通过架构设计和优化，却能承载几倍、几十倍甚至百倍于单个 MySQL 数据库能力的业务请求能力。

对于 MySQL 调优，需要确认业务表结构设计是否合理，SQL 语句优化是否足够，该添加的索引是否都添加了，是否可以剔除多余的索引等等。

最后确定系统、硬件有哪些地方需要优化，系统瓶颈在哪里，哪些系统参数需要调整优化，进程资源限制是否提到足够高；在硬件方面是否需要更换为具有更高 I/O 性能的存储硬件，是否需要升级内存、CPU、网络等。

如果在设计之初架构就不合理，比如没有进行读写分离，那么后期的 MySQL 和硬件、系统优化的成本就会很高，并且还不一定能最终解决问题。如果业务性能的瓶颈是由于索引等 MySQL 层的优化不够导致的，那么即使配置再高性能的 I/O 存储硬件或者 CPU 也无法支撑业务的全表扫描。

所以本章我们重点关注 MySQL 方面的调优，特别是索引。SQL/索引调优要求对业务和数据流非常清楚。在阿里巴巴内部，有三分之二的 DBA 是业务 DBA，从业务需求讨论到表结构审核、SQL 语句审核、上线、索引更新、版本迭代升级，甚至哪些数据应该放到非关系型数据库中，哪些数据放到数据仓库、搜索引擎或者缓存中，都需要这些 DBA 跟踪和复审。他们甚至可以称为数据架构师（Data Architecher）。

查询性能优化

前面的章节我们知道如何设计最优的库表结构、如何建立最好的索引，这些对于高性能来说是必不可少的。但这些还不够—还需要合理的设计查询。如果查询写得很糟糕，即使库表结构再合理、索引再合适，也无法实现高性能。

什么是慢查询

慢查询日志，顾名思义，就是查询花费大量时间的日志，是指 mysql 记录所有执行超过 long_query_time 参数设定的时间阈值的 SQL 语句的日志。该日志能为 SQL 语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。如何开启，我们稍后再说。

慢查询基础-优化数据访问

查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，一般通过下面两个步骤来分析总是很有效:

1．确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。 2．确认 MySQL 服务器层是否在分析大量超过需要的数据行。

请求了不需要的数据？

有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给 MySQL 服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的 CPU 和内存资源。比如:

查询不需要的记录

一个常见的错误是常常会误以为 MySQL 会只返回需要的数据，实际上 MySQL 却是先返回全部结果集再进行计算。我们经常会看到一些了解其他数据库系统的人会设计出这类应用程序。这些开发者习惯使用这样的技术，先使用 SELECT 语句查询大量的结果，然后获取前面的 N 行后关闭结果集（例如在新闻网站中取出100 条记录，但是只是在页面上显示前面10 条)。他们认为 MySQL 会执行查询，并只返回他们需要的10 条数据，然后停止查询。实际情况是 MySQL 会查询出全部的结果集，客户端的应用程序会接收全部的结果集数据，然后抛弃其中大部分数据。最简单有效的解决方法就是在这样的查询后面加上LIMIT。

总是取出全部列

每次看到 SELECT *的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的列？很可能不是必需的。取出全部列，会让优化器无法完成索引覆盖扫描这类优化,还会为服务器带来额外的I/O、内存和 CPU 的消耗。因此，一些 DBA 是严格禁止 SELECT *的写法的，这样做有时候还能避免某些列被修改带来的问题。[磁盘/内存/cpu]

什么时候应该允许查询返回超过需要的数据？如果这种有点浪费数据库资源的方式可以简化开发，因为能提高相同代码片段的复用性，如果清楚这样做的性能影响，那么这种做法也是值得考虑的。如果应用程序使用了某种缓存机制，或者有其他考虑，获取超过需要的数据也可能有其好处，但不要忘记这样做的代价是什么。获取并缓存所有的列的查询，相比多个独立的只获取部分列的查询可能就更有好处。

重复查询相同的数据

不断地重复执行相同的查询，然后每次都返回完全相同的数据。比较好的方案是，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。

是否在扫描额外的记录

在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多的数据。对于 MySQL，最简单的衡量查询开销的三个指标如下:

响应时间、扫描的行数、返回的行数

没有哪个指标能够完美地衡量查询的开销，但它们大致反映了 MySQL 在内部执行查询时需要访问多少数据，并可以大概推算出查询运行的时间。这三个指标都会记录到 MySQL 的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。

响应时间

响应时间是两个部分之和:服务时间和排队时间。

排队时间是指服务器因为等待某些资源而没有真正执行查询的时间—-可能是等 I/O 操作完成，也可能是等待行锁，等等。

当你看到一个查询的响应时间的时候，首先需要问问自己，这个响应时间是否是一个合理的值。概括地说，了解这个查询需要哪些索引以及它的执行计划是什么,然后计算大概需要多少个顺序和随机 I/O(一次机械磁盘的IO耗时大概9ms)，再用其乘以在具体硬件条件下一次 I/O 的消耗时间。最后把这些消耗都加起来，就可以获得一个大概参考值来判断当前响应时间是不是一个合理的值。

扫描的行数和返回的行数

分析查询时，查看该查询扫描的行数是非常有帮助的。这在一定程度上能够说明该查询找到需要的数据的效率高不高。

理想情况下扫描的行数和返回的行数应该是相同的。但实际情况中这种“美事”并不多。例如在做一个关联查询时，服务器必须要扫描多行才能生成结果集中的一行。扫描的行数对返回的行数的比率通常很小，一般在1:1 和10:1 之间，不过有时候这个值也可能非常非常大。

扫描的行数和访问类型

在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。 MySQL 有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无须扫描就能返回结果。

在 EXPLAIN 语句中的 type 列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度是从慢到快，扫描的行数也是从小到大。你不需要记住这些访问类型，但需要明白扫描表、扫描索引、范围访问和单值访问的概念。

如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引，为什么索引对于查询优化如此重要了。索引让 MySQL 以最高效、扫描行数最少的方式找到需要的记录。

一般 MySQL 能够使用如下三种方式应用 WHERE 条件，从好到坏依次为:

1、在索引中使用 WHERE 条件来过滤不匹配的记录。这是在存储引擎层完成的。

2、**使用索引覆盖扫描（在 Extra 列中出现了 Using index）**来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在 MySQL 服务器层完成的，但无须再回表查询记录。

3、从数据表中返回数据，然后过滤不满足条件的记录（在 Extra 列中出现 Using Where)。这在 MySQL 服务器层完成，MySQL 需要先从数据表读出记录然后过滤。

好的索引可以让查询使用合适的访问类型，尽可能地只扫描需要的数据行。

如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它:

1、使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了（在前面的章节中我们已经讨论过了)。

2、改变库表结构。例如使用单独的汇总表。

3、重写这个复杂的查询，让 MySQL 优化器能够以更优化的方式执行这个查询。

重构查询的方式

在优化有问题的查询时，目标应该是找到一个更优的方法获得实际需要的结果——而不一定总是需要从 MySQL 获取一模一样的结果集。有时候，可以将查询转换一种写法让其返回一样的结果,但是性能更好。但也可以通过修改应用代码,用另一种方式完成查询，最终达到一样的目的。

一个复杂查询还是多个简单查询

设计查询的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。在传统实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。但是这样的想法对于 MySQL 并不适用，MySQL 从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。现代的网络速度比以前要快很多，无论是带宽还是延迟。在某些版本的 MySQL 上，即使在一个通用服务器上，也能够运行每秒超过10 万的查询，即使是一个千兆网卡也能轻松满足每秒超过2000 次的查询。所以运行多个小查询现在已经不是大问题了。

MySQL 内部每秒能够扫描内存中上百万行数据，相比之下，MySQL 响应数据给客户端就慢得多了。在其他条件都相同的时候,使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询是很有必要的。

不过，在应用设计的时候，如果一个查询能够胜任时还写成多个独立查询是不明智的。例如，应用对一个数据表做10 次独立的查询来返回10 行数据，每个查询返回一条结果，查询10 次。

切分查询

有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的 DELETE 语句切分成多个较小的查询可以尽可能小地影响 MySQL 性能，同时还可以减少 MySQL 复制的延迟。

一次删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法。同时，需要注意的是，如果每次删除数据后，都暂停一会儿再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。[1w是个经验值]

分解关联查询

很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。到底为什么要这样做?乍一看，这样做并没有什么好处，原本一条查询，这里却变成多条查询，返回的结果又是一模一样的。事实上，用分解关联查询的方式重构查询有如下的优势:

让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。将查询分解后，执行单个查询可以减少锁的竞争。

在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。查询本身效率也可能会有所提升。

可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。

更进一步，这样做相当于在应用中实现了哈希关联，而不是使用 MySQL 的嵌套循环关联。某些场景哈希关联的效率要高很多。

在很多场景下，通过重构查询将关联放到应用程序中将会更加高效，这样的场景有很多，比如:当应用能够方便地缓存单个查询的结果的时候、当可以将数据分布到不同的 MySQL 服务器上的时候、当能够使用 IN()的方式代替关联查询的时候、当查询中使用同一个数据表的时候。(一般来说嵌套子查询比关联查询效率高一个数量级)

慢查询配置

我们已经知道慢查询日志可以帮助定位可能存在问题的 SQL 语句，从而进行 SQL 语句层面的优化。但是默认值为关闭的，需要我们手动开启。

mysql> show VARIABLES like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.35 sec)
## 开启
mysql> set @@global.slow_query_log=1;
Query OK, 0 rows affected (0.77 sec)
## 已开启
mysql> show VARIABLES like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.00 sec)

但是多慢算慢？MySQL 中可以设定一个阈值，将运行时间超过该值的所有 SQL 语句都记录到慢查询日志中。long_query_time 参数就是这个阈值。默认值为 10，代表 10 秒。

show VARIABLES like '%long_query_time%';

mysql> show VARIABLES like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

set global long_query_time=0; —默认 10 秒，这里为了演示方便设置为 0

mysql> set global long_query_time=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show VARIABLES like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
## 居然没有生效,什么情况, 断开连接重新查询就是真实的值(可能有缓存)
mysql> exit;
Bye
## 重新连接
[root@localhost ~]# mysql -uroot -p 
Enter password: 
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
## 生效了

同时对于没有运行的 SQL 语句没有使用索引，则 MySQL 数据库也可以将这条 SQL 语句记录到慢查询日志文件，控制参数是：

show VARIABLES like '%log_queries_not_using_indexes%';

mysql> show VARIABLES like '%log_queries_not_using_indexes%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

对于产生的慢查询日志，可以指定输出的位置，通过参数 log_output 来控制，可以输出到[TABLE][FILE][FILE,TABLE]。比如

set global log_output=‘FILE,TABLE’，缺省是输出到文件，我们的配置把慢查询输出到表，不过一般不推荐输出到表。

mysql> show VARIABLES like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

小结

slow_query_log 启动停止慢查询日志
slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
long_query_time 指定记录慢查询日志 SQL 执行时间得伐值（单位：秒，默认 10 秒）
log_queries_not_using_indexes 是否记录未使用索引的 SQL
log_output 日志存放的地方可以是[TABLE][FILE][FILE,TABLE]

慢查询解读分析

日志格式

开启慢查询功能以后，会根据我们的配置产生慢查询日志

mysql> show variables like 'slow_query_log_file';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/localhost-slow.log | ## 位置
+---------------------+-----------------------------------+
1 row in set (0.00 sec)

root@localhost ~]# cd /var/lib/mysql/
total 193220
-rw-r-----. 1 mysql mysql       56 Dec  7 06:11  auto.cnf
-rw-r-----. 1 mysql mysql  4446509 Dec 10 19:55  binlog.000001
-rw-r-----. 1 mysql mysql       16 Dec  7 06:11  binlog.index
...
drwxr-x---. 2 mysql mysql      135 Dec 10 19:55  len
-rw-r-----. 1 mysql mysql     1760 Dec 11 01:47  localhost-slow.log ## here
....

从慢查询日志里面摘选一条慢查询日志，数据组成如下

Time                 Id Command    Argument
# Time: 2021-12-11T09:41:17.546180Z 查询执行时间
# User@Host: root[root] @ localhost []  Id:    43  用户名 、用户的 IP 信 息、线程 ID 号
# Query_time: 0.001168 执行花费的时长【单位：毫秒】 Lock_time: 0.000000 执行花费的时长【单位：毫秒】 Rows_sent: 1 获得的结果行数 Rows_examined: 1  扫描的数据行数
SET timestamp=1639215677; 这 SQL 执行的具体时间
select @@version_comment limit 1; 执行的 SQL 语句

慢查询分析

慢查询的日志记录非常多，要从里面找寻一条查询慢的日志并不是很容易的事情，一般来说都需要一些工具辅助才能快速定位到需要优化的 SQL 语句，下面介绍两个慢查询辅助工具

mysqldumpslow

常用的慢查询日志分析工具，汇总除查询条件外其他完全相同的 SQL，并将分析结果按照参数中所指定的顺序输出。当然它的参数不少，我们常用的也就是那么几个。

mysqldumpslow -s r -t 10 slow-mysql.log -s order (c,t,l,r,at,al,ar)

c:总次数

t:总时间

l:锁的时间

r:获得的结果行数

at,al,ar :指 t,l,r 平均数【例如：at = 总时间/总次数】

-s 对结果进行排序，怎么排，根据后面所带的 (c,t,l,r,at,al,ar)，缺省为 at

-t NUM just show the top n queries：仅显示前 n 条查询

-g PATTERN grep: only consider stmts that include this string：通过 grep 来筛选语句。

[root@localhost ~]# whereis mysqldumpslow
mysqldumpslow: /usr/bin/mysqldumpslow /usr/share/man/man1/mysqldumpslow.1.gz
[root@localhost bin]# cd /usr/bin/
[root@localhost bin]# pwd
/usr/bin
## 执行慢查询分析命令
[root@localhost bin]# ./mysqldumpslow -s t -t 10 /var/lib/mysql/localhost-slow.log

Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
Count: 3  Time=0.06s (0s)  Lock=0.05s (0s)  Rows=0.7 (2), root[root]@localhost
  show variables like 'S'

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost
  show VARIABLES like 'S'

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  show variables 'S'

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select @@version_comment limit N

Died at ./mysqldumpslow line 162, <> chunk 7.

## 带grep过滤条件的分析
[root@localhost bin]# ./mysqldumpslow -s t -t 10 /var/lib/mysql/localhost-slow.log -g select

Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select @@version_comment limit N

Died at ./mysqldumpslow line 162, <> chunk 7.

Explain 执行计划

什么是执行计划

有了慢查询语句后，就要对语句进行分析。一条查询语句在经过 MySQL 查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。EXPLAIN 语句来帮助我们查看某个查询语句的具体执行计划，我们需要搞懂 EPLATNEXPLAIN 的各个输出项都是干嘛使的，从而可以有针对性的提升我们查询语句的性能。

通过使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析查询语句或是表结构的性能瓶颈，总的来说通过 EXPLAIN 我们可以：

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

执行计划的语法

执行计划的语法其实非常简单：在 SQL 查询的前面加上 EXPLAIN 关键字就行。比如：EXPLAIN select * from table1

重点的就是 EXPLAIN 后面你要分析的 SQL 语句

除了以 SELECT 开头的查询语句，其余的 DELETE、INSERT、REPLACE 以及 UPOATE 语句前边都可以加上 EXPLAIN，用来查看这些语句的执行计划，不过我们这里对 SELECT 语句更感兴趣，所以后边只会以 SELECT 语句为例来描述 explain语句的用法。

执行计划详解

为了让大家先有一个感性的认识，我们把 EXPLAIN 语句输出的各个列的作用先大致罗列一下:

mysql> explain select * from order_exp\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10612
     filtered: 100.00 ## 没有过滤掉数据
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

id：在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id
select_type： SELECT 关键字对应的那个查询的类型
table：表名
partitions：匹配的分区信息
type：针对单表的访问方法
possible_keys：可能用到的索引
key：实际上使用的索引
key_len：实际使用到的索引长度
ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows：预估的需要读取的记录条数
filtered：某个表经过搜索条件过滤后剩余记录条数的百分比
Extra：—些额外的信息

看到这里，是不是一脸懵逼，这是正常的，这里把它们都列出来只是为了描述一个轮廓，随着我们课程的进行，我们会仔细讲解每个列的含义，显示值的含义。

使用范例表 order_exp：

mysql> show create table order_exp \G
*************************** 1. row ***************************
       Table: order_exp
Create Table: CREATE TABLE `order_exp` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的编号',
  `order_note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的说明',
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10819 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

这个表在库中有个三个派生表 s1，s2，order_exp_cut，表结构基本一致，有少许差别：

mysql> show create table order_exp_cut \G
*************************** 1. row ***************************
       Table: order_exp_cut
Create Table: CREATE TABLE `order_exp_cut` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '订单的编号',
  `order_note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的说明',
  `insert_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10819 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)


mysql> show create table s1\G
*************************** 1. row ***************************
       Table: s1
Create Table: CREATE TABLE `s1` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的编号',
  `order_note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '订单的说明',
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_insert_time` (`insert_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10814 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)


mysql> show create table s2\G
*************************** 1. row ***************************
       Table: s2
Create Table: CREATE TABLE `s2` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的编号',
  `order_note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的说明',
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_insert_time` (`insert_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10814 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

注意：为了方便讲述，我们可能会适当调整对列的讲解顺序，不会完全按照 EXPLAIN 语句输出列顺序来讲解。

table

不论我们的查询语句有多复杂，里边包含了多少个表，到最后也是需要对每个表进行单表访问的，MySQL 规定 EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法，该条记录的 table 列代表着该表的表名。

只涉及对 s1 表的单表查询，所以 EXPLAIN 输出中只有一条记录，其中的 table 列的值是 s1，而连接查询的执行计划中有两条记录，这两条记录的 table 列分别是 s1 和 s2.

对于在同一个 SELECT 关键字中的表来说，它们的 id 值是相同的

我们知道我们写的查询语句一般都以 SELECT 关键字开头，比较简单的查询语句里只有一个 SELECT 关键字，

稍微复杂一点的连接查询中也只有一个 SELECT 关键字，比如:

select * from s1 inner join s2 on s1.id = s2.id;
SELECT * FROM s1 INNER J0IN s2 ON s1.id = s2.id WHERE s1.order_status = 0; ## 这个sql有个错误你发现了没
## J0IN 是个0,不是O, 测试时候不小心搞错了, 懵逼了半天, 0-O, 1-l, 这些容易错的字符时刻都要小心不要被自己坑了
select database(); ## 查看当前的数据库
mysql> explain SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id WHERE s1.order_status = 0 \G
*************************** 1. row ***************************
           id: 1 ## id是1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1 ## id是1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: len.s1.id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)
## 这个虽然只有一个select,但是还是会生成两id

但是下边两种情况下在一条查询语句中会出现多个 SELECT 关键字:

1、查询中包含子查询的情况

SELECT* FROM s1 WHERE id IN ( SELECT * FROM s2);

2、查询中包含 UNION 语句的情况

SELECT * FROM s1 UNION SELECT * FROM s2 ;

查询语句中每出现一个 SELECT 关键字，MySQL 就会为它分配一个唯一的 id 值。这个 id 值就是 EXPLAIN 语句的第一个列。

单 SELECT 关键字

比如下边这个查询中只有一个 SELECT 关键字，所以 EXPLAIN 的结果中也就只有一条 id 列为 1 的记录∶

EXPLAIN SELECT * FROM s1 WHERE order_no = 'a';

连接查询

对于连接查询来说，一个 SELEOT 关键字后边的 FROM 子句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的 id 值都是相同的，比如:

EXPLAIN SELECT * FROM s1 INNER JOIN s2;

包含子查询

EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2) OR order_no = 'a';
mysql> EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2) OR order_no = 'a'\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: s1
   partitions: NULL
         type: ALL
possible_keys: idx_order_no
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2 ## id不同,说明没有优化成关联查询
  select_type: DEPENDENT SUBQUERY
        table: s2
   partitions: NULL
         type: unique_subquery
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: func
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

但是这里大家需要特别注意，查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写，直接查看执行计划就好了，比如说:

mysql> EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 WHERE order_no = 'a')\G
*************************** 1. row ***************************
           id: 1 ## id是1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1 ## id是1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,idx_order_no
          key: PRIMARY
      key_len: 8
          ref: len.s1.id
         rows: 1
     filtered: 100.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

可以看到，虽然我们的查询语句是一个子查询，但是执行计划中 s1 和 s2 表对应的记录的 id 值全部是 1，这就表明了查询优化器将子查询转换为了连接查询,

包含 UNION 子句

对于包含 UNION 子句的查询语句来说，每个 SELECT 关键字对应一个 id 值也是没错的，不过还是有点儿特别的东西，比方说下边这个查询:

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY ## 主表
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: UNION ## 驱动表, 联合
        table: s2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT ## 它是union result
        table: <union1,2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using temporary
3 rows in set, 1 warning (0.00 sec)

这个语句的执行计划的第三条记录为什么这样？UNION 子句会把多个查询的结果集合并起来并对结果集中的记录进行去重，怎么去重呢? MySQL 使用的是内部的临时表。正如上边的查询计划中所示，UNION 子句是为了把 id 为 1 的查询和id为2的查询的结果集合并起来并去重，所以在内部创建了一个名为<union1， 2>的临时表（就是执行计划第三条记录的 table 列的名称)，id 为 NULL 表明这个临时表是为了合并两个查询的结果集而创建的。

跟 UNION 对比起来，UNION ALL 就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户，所以也就不需要使用临时表。所以在包含 UNION ALL 子句的查询的执行计划中，就没有那个 id 为 NULL 的记录，如下所示:

mysql> EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: s2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

select_type 列

通过上边的内容我们知道，一条大的查询语句里边可以包含若干个 SELECT 关键字，每个 SELECT 关键字代表着一个小的查询语句，而每个 SELECT 关键字的 From 子句中都可以包含若干张表（这些表用来做连接查询)，每一张表都对应着执行计划输出中的一条记录，对于在同一个 SELECT 关键字中的表来说，它们的 id 值是相同的。

MySQL 为每一个 SELECT 关键字代表的小查询都定义了一个称之为 select_type 的属性，意思是我们只要知道了某个小查询的 select_type 属性，就知道了这个小查询在整个大查询中扮演了一个什么角色，select_type 取值如下：

SIMPLE：简单的select查询,不使用union及子查询,但是可能使用关联查询
PRIMARY：最外层的select查询
UNION：UNION中的第二个或随后的select查询,不依赖于外部查询的结果集
UNIONRESULT：UNION结果集
SUBQUERY：子查询中的第一个select查询,不依赖于外部查询的结果集
DEPENDENTUNION：UNION中的第二个或随后的select查询,依赖于外部查询的结果集
DEPENDENTSUBQUERY：子查询中的第一个select查询,依赖于外部查询的结果集
DERIVED：用于from子句里有子查询的情况。MySQL会递归执行这些子查询,把结果放在临时表里。 /dɪ'raɪvd/ adj. 导出的；衍生的，派生的
MATERIALIZED：物化子查询UNCACHEABLE
SUBQUERY：结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估，出现极少。
UNCACHEABLEUNION：UNION中的第二个或随后的select查询,属于不可缓存的子查询，出现极少。

SIMPLE

EXPLAIN SELECT * FROM s1 WHERE order_no = ‘a’;

简单的 select 查询，查询中不包含子查询或者 UNION

连接查询也算是 SIMPLE 类型

EXPLAIN SELECT * FROM s1 INNER JOIN s2;

PRIMARY

对于包含 UNION、UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的 select_type 值就是 PRIMARY

UNION

对于包含 UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的查询的 select_type 值就是 UNION

UNION RESULT

MySQL 选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION RESULT，例子上边有。

SUBQUERY

如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 SUBQUERY，比如下边这个查询:

EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2) OR order_no = ‘a’;

mysql> EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2) OR order_no = 'a'\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: s1
   partitions: NULL
         type: ALL
possible_keys: idx_order_no
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: s2
   partitions: NULL
         type: unique_subquery
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: func
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

可以看到，外层查询的 select_type 就是 PRTIMARY，子查询的 select_type 就是 SUBOUERY。需要大家注意的是，由于 select_type 为 SUBQUERY 的子查询由于会被物化，所以只需要执行一遍。

TIPS

上面的说明里出现了几个新名字，这里稍微解释下，以后会有详解讲解。

semi-join：半连接优化技术，本质上是把子查询上拉到父查询中，与父查询的表做 join 操作。关键词是“上拉”。对于子查询，其子查询部分相对于父表的每个符合条件的元组，都要把子查询执行一轮。效率低下。用半连接操作优化子查询，是把子查询上拉到父查询中，这样子查询的表和父查询中的表是并列关系，父表的每个符合条件的元组，只需要在子表中找符合条件的元组即可。简单来说，就是通过将子查询上拉对父查询中的数据进行筛选，以使获取到最少量的足以对父查询记录进行筛选的信息就足够了。

子查询物化：子查询的结果通常缓存在内存或临时表中。

关联/相关子查询：子查询的执行依赖于外部查询。多数情况下是子查询的 WHERE 子句中引用了外部查询的表。自然“非关联/相关子查询”的执行则不依赖与外部的查询。

DEPENDENT UNION、DEPENDENT SUBQUERY

在包含 UNION 或者 UNION ALL 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的 select_type 的值就是 DEPENDENT UNION。比方说下边这个查询：

EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 WHERE id = 716 UNION SELECT id FROM s1 WHERE id = 718);

mysql> EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 WHERE id = 716 UNION SELECT id FROM s1 WHERE id = 718)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: no matching row in const table
*************************** 3. row ***************************
           id: 3
  select_type: DEPENDENT UNION
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: no matching row in const table
*************************** 4. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using temporary
4 rows in set, 1 warning (0.00 sec)

这个查询比较复杂，大查询里包含了一个子查询，子查询里又是由 UNION 连起来的两个小查询。从执行计划中可以看出来，SELECT id FROM s2 WHERE id = 716 这个小查询由于是子查询中第一个查询，所以它的 select_type 是 OEPENDENT SUBOUERY，而 SELECT id FROM s1 WHERE id = 718 这个查询的 select_type 就是 DEPENDENT UNION。

是不是很奇怪这条语句并没有依赖外部的查询？MySQL 优化器对 IN 操作符的优化会将 IN 中的非关联子查询优化成一个关联子查询。我们可以在执行上面那个执行计划后，马上执行 show warnings\G，可以看到 MySQL 对 SQL 语句的大致改写情况：

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `len`.`s1`.`id` AS `id`,`len`.`s1`.`order_no` AS `order_no`,`len`.`s1`.`order_note` AS `order_note`,`len`.`s1`.`insert_time` AS `insert_time`,`len`.`s1`.`expire_duration` AS `expire_duration`,`len`.`s1`.`expire_time` AS `expire_time`,`len`.`s1`.`order_status` AS `order_status` from `len`.`s1` where <in_optimizer>(`len`.`s1`.`id`,<exists>(/* select#2 */ select NULL from `len`.`s2` where ((<cache>(`len`.`s1`.`id`) = 716) and multiple equal(716, NULL)) union /* select#3 */ select NULL from `len`.`s1` where ((<cache>(`len`.`s1`.`id`) = 718) and multiple equal(718, NULL))))
1 row in set (0.00 sec)
## 大致是将s2改写成了一个嵌套关联的子查询, 因此也没有对子查询的物化表了

DERIVED

对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的 select_type 就是 DERIVED。

EXPLAIN SELECT * FROM (SELECT id, count(*) as c FROM s1 GROUP BY id) AS derived_s1 where c >1;

mysql> EXPLAIN SELECT * FROM (SELECT id, count(*) as c FROM s1 GROUP BY id) AS derived_s1 where c >1 \G 
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED ## 子查询被物化了, 没有任何关联的子查询是不能被物化的
        table: s1
   partitions: NULL
         type: index
possible_keys: PRIMARY,u_idx_day_status,idx_order_no,idx_insert_time
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

从执行计划中可以看出， id 为 2 的记录就代表子查询的执行方式，它的 select_type 是 DERIVED ，说明该子查询是以物化的方式执行的。id 为 1 的记录代表外层查询，大家注意看它的 table 列显示的是，表示该查询是针对将派生表物化之后的表进行查询的。

MATERIALIZED

当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的 select_type 属性就是 MATERIALIZED，比如下边这个查询︰(比物化根进一步了,物化了还进行了数据连接)

EXPLAIN SELECT * FROM s1 WHERE order_no IN (SELECT order_no FROM s2);

mysql> EXPLAIN SELECT * FROM s1 WHERE order_no IN (SELECT order_no FROM s2)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: idx_order_no
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: ref
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 152
          ref: len.s1.order_no
         rows: 1
     filtered: 100.00
        Extra: Using index; FirstMatch(s1)
2 rows in set, 1 warning (0.00 sec)

物化子查询UNCACHEABLE和物化区别在于,物化使用在from后面, MATERIALIZED是出现在where条件中的

UNCACHEABLE SUBQUERY、UNCACHEABLE UNION

出现极少，不做深入讲解，比如

explain select * from s1 where id = ( select id from s2 where order_no=@@sql_log_bin);

partitions

和分区表有关，一般情况下我们的查询语句的执行计划的 partitions 列的值都是 NULL。

type

我们前边说过执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法/访问类型，其中的 type 列就表明了这个访问方法/访问类型是个什么东西，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

出现比较多的是 system>const>eq_ref>ref>range>index>ALL

system[驱动表,唯一]

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory，那么对该表的访问方法就是 system。

explain select * from test_myisam;

当然，如果改成使用 InnoDB 存储引擎，试试看执行计划的 type 列的值是什么? innodb使用的估值法,默认值是6(好像是),所以不会出现system

const [驱动表, 唯一,等值]

就是当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是 const。因为只匹配一行数据，所以很快。

EXPLAIN SELECT * FROM s1 WHERE id = 716;

mysql> EXPLAIN SELECT * FROM s1 WHERE id = 01 \G 命中数据
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM s1 WHERE id = 716 \G 没有可命中的数据(返回null)
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: no matching row in const table
1 row in set, 1 warning (0.00 sec)

不过这种 const 访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，组成索引的每一个列都是与常数进行等值比较时，这个 const 访问方法才有效

对于唯一二级索引来说，查询该列为 NULL 值的情况比较特殊，因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录，也就是说 is null 不可以使用 const 访问方法来执行。

eq_ref [被驱动, 唯一, 等值]

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的〈如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较)，则对该被驱动表的访问方法就是 eq_ref。

TIPS

*驱动表与被驱动表：*A 表和 B 表 join 连接查询，如果通过 A 表(左连接一定是A,内连接使用哪一个执行引擎要计算成本决定)的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到 B 表中查询数据，然后合并结果。那么我们称 A 表为驱动表，B 表为被驱动表

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

ref[驱动表, 等值]

当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref。

EXPLAIN SELECT * FROM s1 WHERE order_no = ‘a’;

二级索引列值为 NULL 的情况

不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含 NULL 值的数量并不限制，所以我们采用 key IS NULL 这种形式的搜索条件最多只能使用 ref 的访问方法，而不是 const 的访问方法。

对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用 ref 的访问方法，比方说下边这几个查询：

explain SELECT * FROM order_exp WHERE insert_time = ‘2021-03-22 18:28:23’;

explain SELECT * FROM order_exp WHERE insert_time = ‘2021-03-22 18:28:23’ AND order_status = 0;

但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为 ref 了，比方说这样：

explain SELECT * FROM order_exp WHERE insert_time = ‘2021-03-22 18:28:23’ AND order_status > -1; ## range

fulltext

全文索引

ref_or_null

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为 NULL 的记录也找出来，就像下边这个查询：

mysql>  show create table order_exp_cut \G
*************************** 1. row ***************************
       Table: order_exp_cut
Create Table: CREATE TABLE `order_exp_cut` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '订单的编号',
  `order_note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的说明',
  `insert_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10819 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

注意，上面的表改为了 order_exp_cut，order_exp_cut 相对于 order_exp 就是把一些列改为了允许 null，其他的无变化。

explain SELECT * FROM order_exp_cut WHERE order_no= ‘abc’ OR order_no IS NULL; ## 不是所有的null都会导致索引失效

mysql> explain SELECT * FROM order_exp_cut WHERE order_no= 'abc' OR order_no IS NULL \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp_cut
   partitions: NULL
         type: ref_or_null
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 153
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.10 sec)

这个查询相当于先分别从 order_exp_cut 表的 idx_order_no 索引对应的 B+树中找出 order_no IS NULL 和 order_no= 'abc’的两个连续的记录范围，然后根据这些二级索引记录中的 id 值再回表查找完整的用户记录。

index_merge [使用两个普通索引的方式]

一般情况下对于某个表的查询只能使用到一个索引，在某些场景下可以使用索引合并的方式来执行查询:

EXPLAIN SELECT * FROM s1 WHERE order_no = ‘a’ OR insert_time = ‘2021-03-22 18:36:47’;

unique_subquery [in 子查询使用的是唯一索引]

类似于两表连接中被驱动表的 eq_ref 访问方法，unique _subquery 是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery，比如下边的这个查询语句:

EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 where s1.insert_time = s2.insert_time) OR order_no = ‘a’;

index_subquery [in子查询使用的是普通索引]

index_subquery 与 unique_subquery 类似，只不过访问⼦查询中的表时使⽤的是普通的索引：

EXPLAIN SELECT * FROM s1 WHERE order_no IN (SELECT order_no FROM s2 where s1.insert_time = s2.insert_time) OR order_no = ‘a’;

range [可以是普通索引,也可以是聚簇索引]

如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法，一般就是在你的 where 语句中出现了 between、<、>、in 等的查询。

这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引

EXPLAIN SELECT * FROM s1 WHERE order_no IN (‘a’, ‘b’, ‘c’);

EXPLAIN SELECT * FROM s1 WHERE order_no > ‘a’ AND order_no < ‘b’;

这种利用索引进行范围匹配的访问方法称之为：range。

此处所说的使用索引进行范围匹配中的 索引 可以是聚簇索引，也可以是二级索引。

index

当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index。

EXPLAIN SELECT insert_time FROM s1 WHERE expire_time = ‘2021-03-22 18:36:47’;

all

最熟悉的全表扫描，将遍历全表以找到匹配的行

EXPLAIN SELECT * FROM s1;

possible_keys 与 key

在EXPLAIN 语句输出的执行计划中,possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key 列表示实际用到的索引有哪些，如果为 NULL，则没有使用索引。比方说下边这个查询:。

另外需要注意的一点是，possible keys 列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

key_len

key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，计算方式是这样的：

对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是 VARCHAR(100)，使用的字符集是 utf8，那么该列实际占用的最大存储空间就是 100 x 3 = 300 个字节。

如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多 1 个字节。

对于变长字段来说，都会有 2 个字节的空间来存储该变长列的实际长度。

EXPLAIN SELECT * FROM s1 WHERE id = 718;

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

注意：char 和 varchar 跟字符编码也有密切的联系，比如 latin1 占用 1 个字节， gbk 占用 2 个字节，utf8 占用 3 个字节。

ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const、 eg_ref、ref、ref_or_null、unique_sutbquery、index_subopery 其中之一时,ref 列展示的就是与索引列作等值匹配的是谁，比如只是一个常数或者是某个列。比如

EXPLAIN SELECT * FROM s1 WHERE order_no = ‘a’;

mysql> EXPLAIN SELECT * FROM s1 WHERE order_no = 'a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ref
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 152
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

可以看到 ref 列的值是 const，表明在使用 idx_order_no 索引执行查询时，与 order_no 列作等值匹配的对象是一个常数，当然有时候更复杂一点：

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: len.s1.id ## 做等值匹配的列是来自表
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

可以看到对被驱动表 s2 的访问方法是 eq_ref，而对应的 ref 列的值是 len.s2.id，这说明在对被驱动表进行访问时会用到 PRIMARY 索引，也就是聚簇索引与一个列进行等值匹配的条件，与 s2 表的 id 作等值匹配的对象就是 len.s2.id 列(注意这里把数据库名也写出来了)。

有的时候与索引列进行等值匹配的对象是一个函数，比如

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.order_no= UPPER(s1.order_no);

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.order_no= UPPER(s1.order_no)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: ref
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 152
          ref: func ## 函数
         rows: 1
     filtered: 100.00
        Extra: Using index condition
2 rows in set, 1 warning (0.09 sec)

可以看到在查询计划的 ref 列⾥输出的是 func。

rows

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数。比如下边两个个查询:

EXPLAIN SELECT * FROM s1 WHERE order_no > ‘z’;

EXPLAIN SELECT * FROM s1 WHERE order_no > ‘a’;

mysql> EXPLAIN SELECT * FROM s1 WHERE order_no > 'z' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: range
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 152
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM s1 WHERE order_no > 'a' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: idx_order_no
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10646
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

filtered

查询优化器预测有多少条记录满⾜其余的搜索条件，什么意思呢？看具体的语句：

EXPLAIN SELECT * FROM s1 WHERE id > 5890 AND order_note = ‘a’;

从执行计划的 key 列中可以看出来，该查询使用 PRIMARY 索引来执行查询，从 rows 列可以看出满足 id > 5890 的记录有 5286 条。执行计划的 filtered 列就代表查询优化器预测在这 5286 条记录中，有多少条记录满足其余的搜索条件，也就是 order_note = 'a’这个条件的百分比。此处 filtered 列的值是 10.0，说明查询优化器预测在 5286 条记录中有 10.00%的记录满足 order_note = 'a’这个条件。

对于单表查询来说，这个 filtered 列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的 filtered 值，比方说下边这个查询:

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.order_no = s2.order_no WHERE s1.order_note > ‘你好，李焕英’;

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.order_no = s2.order_no WHERE s1.order_note > '你好，李焕英'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: idx_order_no
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10646
     filtered: 33.33
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: ref
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 152
          ref: len.s1.order_no
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

从执行计划中可以看出来，查询优化器打算把 s 1 当作驱动表，s2 当作被驱动表。我们可以看到驱动表 s1 表的执行计划的 rows 列为 10646，filtered 列为 33.33 ，这意味着驱动表 s1 的扇出值就是 10573 x 33.33 % = 3524.3，这说明还要对被驱动表执行大约 3524 次查询。

Extra

顾名思义，Extra 列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解 MySQL 到底将如何执行给定的查询语句。MySQL 提供的额外信息很多，几十个，无法一一介绍，挑一些平时常见的或者比较重要的额外信息讲讲，同时因为数据的关系，很难实际演示出来，所以本小节的相关内容不会提供全部实际的 SQL 语句和结果画面

No tables used

当查询语句的没有 FROM 子句时将会提示该额外信息。

mysql> explain select database()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
1 row in set, 1 warning (0.00 sec)

mysql> explain select sleep(1)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
1 row in set, 1 warning (0.00 sec)
## 一般函数的查询都是没有from字句的

Impossible WHERE

查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息。

mysql> explain select * from s1 where 1=2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Impossible WHERE
1 row in set, 1 warning (0.00 sec)

No matching min/max row

当查询列表处有 MIN 或者 MAX 聚集函数，但是并没有符合 WHERE 子句中的搜索条件的记录时，将会提示该额外信息。

Using index [索引覆盖]

当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在 Extra 列将会提示该额外信息。比方说下边这个查询中只需要用到 idx_order_no 而不需要回表操作：

mysql> EXPLAIN SELECT expire_time FROM s1 WHERE insert_time = '2021-03-22 18:36:47' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ref
possible_keys: u_idx_day_status
          key: u_idx_day_status
      key_len: 5
          ref: const
         rows: 14
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Using index condition [索引条件下推]

有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如下边这个查询：

SELECT * FROM s1 WHERE order_no > ‘z’ AND order_no LIKE ‘%a’;

其中的 order_no> 'z’可以使用到索引，但是 order_no LIKE '%a’却无法使用到索引，在以前版本的 MySQL (5.6以前)中，是按照下边步骤来执行这个查询的：

1、先根据 order_no> 'z’这个条件，从二级索引 idx_order_no 中获取到对应的二级索引记录。

2、根据上一步骤得到的二级索引记录中的主键值进行回表(因为是 select *)，找到完整的用户记录再检测该记录是否符合 key1 LIKE '%a’这个条件，将符合条件的记录加入到最后的结果集。

但是虽然 order_no LIKE '%a’不能组成范围区间参与 range 访问方法的执行，但这个条件毕竟只涉及到了 order_no 列，MySQL 把上边的步骤改进了一下。(这才是符合人们正常思维的检索方式)

索引条件下推 :只对满足索引条件的数据进行下推回表

1、先根据 order_no> 'z’这个条件，定位到二级索引 idx_order_no 中对应的二级索引记录。

2、对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足 order_noLIKE '%a’这个条件，如果这个条件不满足，则该二级索引记录压根儿就没必要回表。(检测索引列中条件是否已经不满足部分条件或全部条件)

3、对于满足 order_no LIKE '%a’这个条件的二级索引记录执行回表操作。

我们说回表操作其实是一个随机 IO(9ms)，比较耗时，所以上述修改可以省去很多回表操作的成本。这个改进称之为索引条件下推（英文名：ICP ，Index Condition Pushdown）。

如果在查询语句的执行过程中将要使用索引条件下推这个特性，在 Extra 列中将会显示 Using index condition，比如这样：

mysql> explain SELECT * FROM s1 WHERE order_no > 'z' AND order_no LIKE '%a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: range
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 152
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition ## 索引条件下推
1 row in set, 1 warning (0.00 sec)

Using where

当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时，在 Extra 列中会提示上述额外信息。

mysql> EXPLAIN SELECT * FROM s1 WHERE order_note = 'a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10646
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

当使用索引访问来执行对某个表的查询，并且该语句的 WHERE 子句中有除了该索引包含的列之外的其他搜索条件时，在 Extra 列中也会提示上述信息。

比如下边这个查询虽然使用 idx_order_no 索引执行查询，但是搜索条件中除了包含 order_no 的搜索条件 order_no = ‘a’，还有包含 order_note 的搜索条件，此时需要回表检索记录然后进行条件判断，所以 Extra 列会显示 Using where 的提示：

mysql> EXPLAIN SELECT * FROM s1 WHERE order_no = 'a' AND order_note = 'a'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ref
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 152
          ref: const
         rows: 1
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

但是大家注意：出现了 Using where，只是表示在 server 层根据 where 条件进行了过滤，和是否全表扫描或读取了索引文件没有关系，网上有不少文章把 Using where 和是否读取索引进行关联，是不正确的，也有文章把 Using where 和回表进行了关联，这也是不对的。按照 MySQL 官方的说明：

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_extra

Using where (JSON property: attached_condition)

A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.

Using where has no direct counterpart in JSON-formatted output; the attached_condition property contains any WHERE condition used.

意思是：Extra 列中出现了 Using where 代表 WHERE 子句用于限制要与下一个表匹配或发送给客户端的行。

很明显，Using where 只是表示 MySQL 使用 where 子句中的条件对记录进行了过滤。

Using join buffer (Block Nested Loop)

在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度， MySQL 一般会为其分配一块名叫 join buffer 的内存块来加快查询速度：

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.order_note = s2.order_note;

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.order_note = s2.order_note\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10642
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10646
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join)
2 rows in set, 1 warning (0.06 sec)

我们看到，在对 s1 表的执行计划的 Extra 列显示了两个提示：

Using join buffer (Block Nested Loop)：这是因为对表 s1 的访问不能有效利用索引，只好退而求其次，使用 join buffer 来减少对 s1 表的访问次数，从而提高性能。

Using where：可以看到查询语句中有一个 s1.order_note = s2.order_note 条件，因为 s2 是驱动表，s1 是被驱动表，所以在访问 s1 表时，s1.order_note 的值已经确定下来了，所以实际上查询 s1 表的条件就是 s1.order_note = 一个常数，所以提示了 Using where 额外信息。

Not exists

当我们使用左（外）连接时，如果 WHERE 子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且那个列又是不允许存储 NULL 值的，那么在该表的执行计划的 Extra 列就会提示 Not exists 额外信息，比如这样：

mysql> EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.order_no = s2.order_no WHERE s2.id IS NULL \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10646
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s2
   partitions: NULL
         type: ref
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 152
          ref: len.s1.order_no
         rows: 1
     filtered: 10.00
        Extra: Using where; Not exists
2 rows in set, 1 warning (0.00 sec)

上述查询中 s1 表是驱动表，s2 表是被驱动表，s2.id 列是主键而且不允许存储 NULL 值的，而 WHERE 子句中又包含 s2.id IS NULL 的搜索条件。

Using intersect(…)、Using union(…)和 Using sort_union(…)

如果执行计划的 Extra 列出现了 Using intersect(…)提示，说明准备使用 Intersect 索引合并的方式执行查询，括号中的…表示需要进行索引合并的索引名称；如果出现了 Using union(…)提示，说明准备使用 Union 索引合并的方式执行查询；出现了 Using sort_union(…)提示，说明准备使用 Sort-Union 索引合并的方式执行查询。什么是索引合并，我们后面会单独讲。

Zero limit

当我们的 LIMIT 子句的参数为 0 时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息。

mysql> explain select *from s1 limit 0,0 \G ## limit 0 == limit 0,0
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Zero limit
1 row in set, 1 warning (0.00 sec)

select *from s1 limit 0,1; 和
select *from s1 limit 1; 都是获取第一条记录

Using filesort

有一些情况下对结果集中的记录进行排序是可以使用到索引的，比如下边这个查询：

mysql> EXPLAIN SELECT * FROM s1 ORDER BY order_no LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_order_no
      key_len: 152
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.02 sec)

这个查询语句可以利用idx_order_no索引直接取出order_no列的10条记录(默认按照索引的排序方式)，然后再进行回表操作就好了。但是很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL 把这种在内存中或者磁盘上进行排序的方式统称为文件排序。如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的 Extra 列中显示 Using filesort 提示：

mysql> EXPLAIN SELECT * FROM s1 ORDER BY order_note LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10646
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

需要注意的是，如果查询中需要使用 filesort 的方式进行排序的记录非常多，那么这个过程是很耗费性能的，我们最好想办法将使用文件排序的执行方式改为使用索引进行排序。

Using temporary

在许多查询的执行过程中，MySQL 可能会借助临时表来完成一些功能，比如 去重、排序之类的，比如我们在执行许多包含 DISTINCT、GROUP BY、UNION 等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL 很有可能寻求通 过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的 Extra 列将会显示 Using temporary 提示：

mysql> EXPLAIN SELECT DISTINCT order_note FROM s1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10646
     filtered: 100.00
        Extra: Using temporary
1 row in set, 1 warning (0.02 sec)

再比如：

mysql> EXPLAIN SELECT order_note, COUNT(*) AS amount FROM s1 GROUP BY order_note order by order_note\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10646
     filtered: 100.00
        Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.01 sec)
## mysql8 默认对group不进行排序,但是mysql5.7 会进行排序

上述执行计划的 Extra 列不仅仅包含 Using temporary 提示，还包含 Using filesort 提示，如果我们不写ORDER BY 子句,就不会进行排序：

EXPLAIN SELECT order_note, COUNT() AS amount FROM s1 GROUP BY order_note order by order_note; 和mysql5.7中的默认排序等价*

EXPLAIN SELECT order_note, COUNT() AS amount FROM s1 GROUP BY order_note order by null; 和mysql8中的默认不排序等价*

很明显，执行计划中出现 Using temporary 并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以我们最好能使用索引来替代掉使用临时表，比方说下边这个包含 GROUP BY 子句的查询就不需要使用临时表：

mysql> EXPLAIN SELECT order_no, COUNT(*) AS amount FROM s1 GROUP BY order_no\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: NULL
         type: index
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 152
          ref: NULL
         rows: 10646
     filtered: 100.00
        Extra: Using index ##  索引覆盖
1 row in set, 1 warning (0.00 sec)

总的来说，发现在执行计划里面有using filesort或者Using temporary的时候，特别需要注意，这往往存在着很大优化的余地，最好进行改进，变为使用 Using index 会更好

Start temporary, End temporary

有子查询时，查询优化器会优先尝试将 IN 子查询转换成 semi-join(半连接优化技术，本质上是把子查询上拉到父查询中，与父查询的表做 join 操作)，而 semi-join 又有好多种执行策略，当执行策略为 DuplicateWeedout 时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询执行计划的 Extra 列将显示 Start temporary 提示，被驱动表查询执行计划的 Extra 列将显示 End temporary 提示。

LooseScan

在将 In 子查询转为 semi-join 时，如果采用的是 LooseScan 执行策略，则在驱动表执行计划的 Extra 列就是显示 LooseScan 提示。

FirstMatch(tbl_name)

在将 In 子查询转为 semi-join 时，如果采用的是 FirstMatch 执行策略，则在被驱动表执行计划的 Extra 列就是显示 FirstMatch(tbl_name)提示。

MRR(Disk-Sweep Multi-Range Read: 磁盘扫描多范围读取)

从上文可以看出，每次从二级索引中读取到一条记录后，就会根据该记录的主键值执行回表操作。而在某个扫描区间中的二级索引记录的主键值是无序的， 也就是说这些二级索引记录对应的聚簇索引记录所在的页面的页号是无序的。

每次执行回表操作时都相当于要随机读取一个聚簇索引页面，而这些随机 IO 带来的性能开销比较大。MySQL 中提出了一个名为 Disk-Sweep Multi-Range Read (MRR，多范围读取)的优化措施，即先读取一部分二级索引记录，将它们的主键值排好序之后再统一执行回表操作。

相对于每读取一条二级索引记录就立即执行回表操作，这样会节省一些 IO 开销。使用这个 MRR 优化措施的条件比较苛刻，所以我们直接认为每读取一条二级索引记录就立即执行回表操作。MRR 的详细信息，可以查询官方文档。

高性能的索引使用策略

不在索引列上做任何操作

我们通常会看到一些查询不当地使用索引，或者使得 MySQL 无法使用已有的索引。如果查询中的列不是独立的，则 MySQL 就不会使用索引。“独立的列” 是指索引列不能是表达式的一部分，也不能是函数的参数。

mysql>SELECT * FROM order_exp WHERE order_status + 1 = 1;

凭肉眼很容易看出 WHERE 中的表达式其实等价于 order_status = 0，但是 MySQL 无法自动解析这个方程式。这完全是用户行为。我们应该养成简化 WHERE 条件的习惯，始终将索引列单独放在比较符号的一侧。

下面是另一个常见的错误:

mysql>SELECT ... WHERE TO_DAYS(insert_time) - TO_DAYS(expire_time) <= 10;

在索引列上使用函数，也是无法利用索引的。

尽量全值匹配

建立了联合索引列后，如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配，比方说下边这个查找语句：

select * from order_exp where insert_time='2021-03-22 18:34:55' and order_status=0 and expire_time='2021-03-22 18:35:14';

我们建立的u_idx_day_statusr索引包含的3个列在这个查询语句中都展现出来了，联合索引中的三个列都可能被用到。

***所以，当建立了联合索引列后，能在 where 条件中使用索引的尽量使用。***(尽可能多的给出你的条件)

最佳左前缀法则

建立了联合索引列，如果搜索条件不够全值匹配怎么办？在我们的搜索语句中也可以不用包含全部联合索引中的列，但要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

select * from order_exp where insert_time='2021-03-22 18:23:42' and order_status=1；

范围条件放最后

这一点，也是针对联合索引来说的，前面我们反复强调过，所有记录都是按照索引列的值从小到大的顺序排好序的，而联合索引则是按创建索引时的顺序进行分组排序。

对于一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找：

mysql> explain select * from order_exp_cut where insert_time='2021-03-22 18:34:55' and order_status=0 and expire_time>'2021-03-22 18:23:57' and expire_time<'2021-03-22 18:35:00'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp_cut
   partitions: NULL
         type: range
possible_keys: u_idx_day_status,idx_expire_time
          key: u_idx_day_status
      key_len: 12
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.10 sec)

中间有范围查询会导致后面的列全部失效，无法充分利用这个联合索引

覆盖索引尽量用

覆盖索引是非常有用的工具，能够极大地提高性能，三星索引里最重要的那颗星就是宽索引星。考虑一下如果查询只需要扫描索引而无须回表，会带来多少好处:

索引条目通常远小于数据行大小，所以如果只需要读取索引

因为索引是按照列值顺序存储的，所以对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多

由于 InnoDB 的聚簇索引，覆盖索引对 InnoDB 表特别有用。InnoDB 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

**尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，不是必要的情况下减少 select ***，除非是需要将表中的全部列检索后，进行缓存。(很多时候我们需要的数据是被索引覆盖的,但是由于使用了select * 导致索引覆盖失效)

不等于要慎用

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

mysql> explain SELECT * FROM order_exp WHERE order_no <> 'DD00_6S'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp
   partitions: NULL
         type: ALL
possible_keys: idx_order_no
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10612
     filtered: 55.77
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

为什么？道理是明显的，这种情况下，扫描区间是[第一条记录,‘DD00_6S’] 和[‘DD00_6S’,最后一条记录]，加上回表，还不如直接进行全部扫描。

Null/Not 有影响

需要注意 null/not null 对索引的可能影响

mysql> explain SELECT * FROM order_exp WHERE order_no is null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Impossible WHERE
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT * FROM order_exp WHERE order_no is not null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10612
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

order_no 为索引列，同时不允许为 null，

可以看见，order_no is null 的情况下，MySQL 直接表示 Impossible WHERE，对于 is not null 直接走的全表扫描。

当 order_no 允许为 null 时：

mysql> show create table order_exp_cut \G
*************************** 1. row ***************************
       Table: order_exp_cut
Create Table: CREATE TABLE `order_exp_cut` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的编号',
  `order_note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的说明',
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10819 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

mysql> explain SELECT * FROM order_exp_cut WHERE order_no is null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: ref
possible_keys: idx_ordre_no
          key: idx_ordre_no
      key_len: 153
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT * FROM order_exp_cut WHERE order_no is not null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp_cut
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10651
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

is null 会走 ref 类型的索引访问，is not null;依然是全表扫描。所以总结起来：

is not null 容易导致索引失效，is null 则会区分被检索的列是否为 null，如果是 null 则会走 ref 类型的索引访问，如果不为 null，也是全表扫描。

但是当联合索引上使用时覆盖索引时，情况又不同了：

mysql> explain SELECT order_status,expire_time FROM order_exp WHERE insert_time is null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp
   partitions: NULL
         type: ref
possible_keys: u_idx_day_status
          key: u_idx_day_status
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT order_status,expire_time FROM order_exp WHERE insert_time is not null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp
   partitions: NULL
         type: index
possible_keys: NULL
          key: u_idx_day_status
      key_len: 12
          ref: NULL
         rows: 10612
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT order_status,expire_time FROM order_exp_cut WHERE insert_time is null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp_cut
   partitions: NULL
         type: ref
possible_keys: u_idx_day_status
          key: u_idx_day_status
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT order_status,expire_time FROM order_exp_cut WHERE insert_time is not null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp_cut
   partitions: NULL
         type: index
possible_keys: NULL
          key: u_idx_day_status
      key_len: 12
          ref: NULL
         rows: 10651
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

所以总的来说，在设计表时列尽可能的不要声明为 null

Like 查询要当心

like 以通配符开头(’%abc…’)，mysql 索引失效会变成全表扫描的操作

mysql> explain SELECT * FROM order_exp WHERE order_no like '%_6S'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10612
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

此时如果使用覆盖索引可以改善这个问题

mysql> explain SELECT order_status,expire_time FROM order_exp_cut WHERE insert_time like '%18:35:09'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp_cut
   partitions: NULL
         type: index
possible_keys: NULL
          key: u_idx_day_status
      key_len: 12
          ref: NULL
         rows: 10651
     filtered: 11.11
        Extra: Using where; Using index ## 索引覆盖
1 row in set, 1 warning (0.00 sec)

字符类型加引号

字符串不加单引号索引失效

mysql> explain SELECT * FROM order_exp WHERE order_no = 6 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp
   partitions: NULL
         type: ALL
possible_keys: idx_order_no
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10612
     filtered: 10.00
        Extra: Using where
1 row in set, 3 warnings (0.03 sec)

## 添加单引号
mysql> explain SELECT * FROM order_exp WHERE order_no = '6' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp
   partitions: NULL
         type: ref
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 152
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

MySQL 的查询优化器，会自动的进行类型转换，比如上个语句里会尝试将 order_no 转换为数字后和 6 进行比较，自然造成索引失效。(做法和我们认为的不太一样,我们理解的可能是将6转换成’6’)

使用 or 关键字时要注意

explain SELECT * FROM order_exp WHERE order_no = ‘DD00_6S’ OR order_no = ‘DD00_9S’;

mysql> explain SELECT * FROM order_exp WHERE order_no = 'DD00_6S' OR order_no = 'DD00_9S'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp
   partitions: NULL
         type: range ## 范围扫描
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 152
          ref: NULL
         rows: 36
     filtered: 100.00
        Extra: Using index condition ## 索引条件下推
1 row in set, 1 warning (0.00 sec)

explain SELECT * FROM order_exp WHERE expire_time= ‘2021-03-22 18:35:09’ OR order_note = ‘abc’;

mysql> explain SELECT * FROM order_exp WHERE expire_time= '2021-03-22 18:35:09' OR order_note = 'abc'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp
   partitions: NULL
         type: ALL
possible_keys: idx_expire_time
          key: NULL ## 没有使用索引
      key_len: NULL
          ref: NULL
         rows: 10612
     filtered: 10.01
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

## 回顾下表结构
mysql> show create table order_exp_cut\G
*************************** 1. row ***************************
Table: order_exp_cut
Create Table: CREATE TABLE `order_exp_cut` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的编号',
  `order_note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的说明',
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10819 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC
1 row in set (0.08 sec)
## 唯一索引
UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,

为什么会失效?

对于条件expire_time可以使用索引,但是由于使用or连接,对于order_no不能使用任何索引,必须进行全表扫描或者是全索引扫描,索引当我们使用索引覆盖时候还是可以提高性能的,因为可以使用索引覆盖,通过扫面全索引避免了扫面全表,因为索引空间更小,扫面的磁盘地址更小,io和cpu耗时更小

当然如果两个条件都是索引列，情况会有变化：

mysql> explain SELECT * FROM order_exp WHERE expire_time= '2021-03-22 18:35:09' OR order_no = 'DD00_6S'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp
   partitions: NULL
         type: index_merge
possible_keys: idx_order_no,idx_expire_time
          key: idx_expire_time,idx_order_no ##使用了索引
      key_len: 5,152
          ref: NULL
         rows: 25
     filtered: 100.00
        Extra: Using union(idx_expire_time,idx_order_no); Using where
1 row in set, 1 warning (0.01 sec)

这也给了我们提示，如果我们将 SELECT * FROM order_exp WHERE expire_time= ‘2021-03-22 18:35:09’ OR order_note = ‘abc’;改为：(虽然order_note='abc’不能使用索引,但是我们可以通过union避免可以使用索引的一部分失效)

union使得集合的行数增加, 关联使得表的列数和行数都增加(无条件的情况)

explain SELECT * FROM order_exp WHERE expire_time= '2021-03-22 18:35:09' union all SELECT * FROM order_exp WHERE order_note = 'abc';

mysql> explain SELECT * FROM order_exp WHERE expire_time= '2021-03-22 18:35:09' union all SELECT * FROM order_exp WHERE order_note = 'abc'\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: order_exp
   partitions: NULL
         type: ref ## 使用了索引
possible_keys: idx_expire_time
          key: idx_expire_time
      key_len: 5
          ref: const
         rows: 6
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: order_exp
   partitions: NULL
         type: ALL ##无法使用索引
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10612
     filtered: 10.00
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

可以改善语句的表现。

当然使用覆盖扫描也可以改善这个问题：

explain SELECT order_status,id FROM order_exp_cut WHERE insert_time=‘2021-03-22 18:34:55’ or expire_time=‘2021-03-22 18:28:28’;

mysql> explain SELECT order_status,id FROM order_exp_cut WHERE insert_time='2021-03-22 18:34:55' or expire_time='2021-03-22 18:28:28'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp_cut
   partitions: NULL
         type: index_merge
possible_keys: u_idx_day_status,idx_expire_time
          key: u_idx_day_status,idx_expire_time
      key_len: 5,5
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using sort_union(u_idx_day_status,idx_expire_time); Using where
1 row in set, 1 warning (0.00 sec)

使用索引扫描来做排序和分组

MySQL 有两种方式可以生成有序的结果﹔通过排序操作﹔或者按索引顺序扫描施﹔如果 EXPLAIN 出来的 type 列的值为“index”，则说明 MySQL 使用了索引扫描来做排序（不要和 Extra 列的“Using index”搞混淆了)。

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机 I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在 IO 密集型的工作负载时。

MySQL 可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL 才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当 0RDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。

排序要当心

ASC、DESC 别混用

对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是 ASC 规则排序，要么都是 DESC 规则排序。

排序列包含非同一个索引的列

用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序

explain SELECT * FROM order_exp order by order_no,insert_time;

mysql> explain SELECT * FROM order_exp order by order_no,insert_time\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_exp
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10612
     filtered: 100.00
        Extra: Using filesort ## 使用了文件排序
1 row in set, 1 warning (0.00 sec)

尽可能按主键顺序插入行

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于 I/O 密集型的应用。例如，从性能的角度考虑，使用 UUID 来作为聚簇索引则会很糟糕，它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

最简单的方法是使用 AUTO_INCREMENT 自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。

注意到向 UUID 主键插入行不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长﹔另一方面毫无疑问是由于页分裂和碎片导致的

因为主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时(InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改)，下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载,主键页就会近似于被顺序的记录填满,这也正是所期望的结果。

如果新行的主键值不一定比之前插入的大，所以 InnoDB 无法简单地总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置-—通常是已有数据的中间位置——并且分配空间。这会增加很多的额外工作，并导致数据分布不够优化。下面是总结的一些缺点:

写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB 在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机 IO。
因为写入是乱序的，InnoDB 不得不频繁地做页分裂(B+树的结构变化)操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。

所以使用 InnoDB 时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。

优化 Count 查询

首先要注意，COUNT()是一个特殊的函数，有两种非常不同的作用:它可以统计某个列值的数量，也可以统计行数。

在统计列值时要求列值是非空的（不统计 NULL)。

COUNT()的另一个作用是统计结果集的行数。常用的就是就是当我们使用 COUNT(*)。实际上，它会忽略所有的列而直接统计所有的行数。

通常来说，COUNT()都需要扫描大量的行（意味着要访问大量数据）才能获得精确的结果，因此是很难优化的。在 MySQL 层面能做的基本只有索引覆盖扫描了。如果这还不够,就需要考虑修改应用的架构，可以用估算值取代精确值，可以增加汇总表，或者增加类似 Redis 这样的外部缓存系统。

关于 Null 的特别说明

对于 Null 到底算什么，存在着分歧：

1、有的认为 NULL 值代表一个未确定的值，MySQL 认为任何和 NULL 值做比较的表达式的值都为 NULL，包括 select null=null 和 select null!=null;

mysql> select null!=null;
+------------+
| null!=null |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)

mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

所以每一个 NULL 值都是独一无二的

2、有的认为其实 NULL 值在业务上就是代表没有，所有的 NULL 值和起来算一份；

3、有的认为这 NULL 完全没有意义，所以在统计数量时压根儿不能把它们算进来。select count(columnName) from tb

在对统计索引列不重复值的数量时如何对待 NULL 值，MySQL 专门提供了一个 innodb_stats_method 的系统变量，

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_stats_method

这个系统变量有三个候选值：

nulls_equal：认为所有 NULL 值都是相等的。这个值也是 innodb_stats_method 的默认值。

如果某个索引列中 NULL 值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。
nulls_unequal：认为所有 NULL 值都是不相等的。

如果某个索引列中 NULL 值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别少，所以倾向于使用索引进行访问。
nulls_ignored：直接把 NULL 值忽略掉。

而且有迹象表明，在 MySQL5.7.22 以后的版本，对这个 innodb_stats_method 的修改不起作用，MySQL 把这个值在代码里写死为 nulls_equal。也就是说 MySQL 在进行索引列的数据统计行为又把 null 视为第二种情况（NULL 值在业务上就是代表没有，所有的 NULL 值合起来算一份），看起来，MySQL 中对 Null 值的处理也很分裂。所以总的来说，对于列的声明尽可能的不要允许为 null。

优化 limit 分页

在系统中需要进行分页操作的时候，我们通常会使用 LIMIT 加上偏移量的办法实现，同时加上合适的 ORDER BY 子句。

一个非常常见又令人头疼的问题就是，在偏移量非常大的时候，例如可能是 select * from order_exp limit 10000,10;

这样的查询，这时 MySQL 需要查询 10010 条记录然后只返回最后 10 条，前面 10 000 条记录都将被抛弃，这样的代价非常高。

优化此类分页查询的一个最简单的办法是

SELECT * FROM (select id from order_exp limit 10000,10) b,order_exp a where a.id = b.id;

mysql> explain SELECT * FROM (select id from order_exp limit 10000,10) b,order_exp a where a.id = b.id\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>  ## 被物化的表b
   partitions: NULL
         type: ALL ## 对索引进行全表扫描
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10010
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
   partitions: NULL
         type: eq_ref ##走唯一索引关联
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: b.id ## 关联关系
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED ## 被物化
        table: order_exp
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_expire_time
      key_len: 5
          ref: NULL
         rows: 10612
     filtered: 100.00
        Extra: Using index
3 rows in set, 1 warning (0.00 sec)

## 或者这样优化
mysql> SELECT * FROM order_exp where id in (select id from order_exp limit 10000,10);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
## 但是不支持limit在in的子查询中

## 试试这样优化
SELECT * FROM (select id from order_exp limit 10000,10); ## 但缺少需要的字段, 还是依靠关联获取

会先查询翻页中需要的 N 条数据的主键值，然后根据主键值回表查询所需要的 N 条数据(如果使用*回返回索引列的数据)，在此过程中查询 N 条数据的主键 id 在索引中完成，所以效率会高一些。

从执行计划中可以看出，首先执行子查询中的 order_exp 表，根据主键做索引全表扫描，然后与 a 表通过 id 做主键关联查询，相比传统写法中的全表扫描效率会高一些。

从两种写法上能看出性能有一定的差距，虽然并不明显，但是随着数据量的增大，两者执行的效率便会体现出来。

上面的写法虽然可以达到一定程度的优化，但还是存在性能问题。最佳的方式是在业务上进行配合修改为以下语句：

select * from order_exp where id > 67 order by id limit 10;

采用这种写法，需要前端通过点击 More 来获得更多数据，而不是纯粹的翻页，因此，每次查询只需要使用上次查询出的数据中的 id 来获取接下来的数据即可，但这种写法需要业务配合。

记忆总结

全职匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE 百分写最右，覆盖索引不写*；
不等空值还有 OR，索引影响要注意；
VAR 引号不可丢， SQL 优化有诀窍。

分区表

简介

分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言，逻辑上只有一个表或一个索引,但是实际上这个表可能由数 10 个物理分区对象组成，每个分区都是一个独立的对象以独自处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。

分区表的原理

如前所述，分区表由多个相关的底层表实现，这些底层表也是由句柄对象（Handlerobject)表示，所以我们也可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎)，分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。分区表上的操作按照下面的操作逻辑进行:

虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁，例如 InnoDB，则会在分区层释放对应表锁。这个加锁和解锁过程与普通 InnoDB 上的查询类似。

分区表的类型

RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。

LIST 分区：类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择。(在集合层做了一次映射)

HASH 分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式。

KEY 分区：类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

复合分区/子分区：目前只支持 RANGE 和 LIST 的子分区，且子分区的类型只能为 HASH 和 KEY。

分区的基本语法如下

CREATE TABLE test ( 

order_date DATETIME NOT NULL, 

）ENGINE=InnoDB 

PARTITION BY RANGE(YEAR(order_date))( 

PARTITION p_0 VALUES LESS THAN (2010) , 

PARTITION p_1 VALUES LESS THAN (2011), 

PARTITION p_2 VALUES LESS THAN (2012), 

PARTITION p_other VALUES LESS THAN MAXVALUE );

不建议 mysql 分区表

但在实际的互联网中，MySQL 分区表用的极少，更多的是自己分库分表。

分库分表除了支持 MySQL 分区表的水平切分以外，还支持垂直切分，把一个很大的库（表）的数据分到几个库（表）中，每个库（表）的结构都相同，但他们可能分布在不同的 mysql 实例，甚至不同的物理机器上，以达到降低单库（表）数据量，提高访问性能的目的

两者对比起来

1）分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁

2）一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难

3）自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个 sql，都不确定 mysql 是怎么操作的，不太可控

4）分区表无论怎么分，都是在一台机器上，天然就有性能的上限。

岁月玲珑

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
【4. MySQL 性能优化】

MySQL 性能优化总论 MySQL 性能优化其实是个很大的课题，在优化上存在着一个调优金字塔的说法：很明显从图上可以看出，越往上走，难度越来越高，收益却是越来越小的。比如硬件和 OS 调优，需要对硬件和 OS 有着非常深刻的了解，仅仅就磁盘一项来说，一般非 DBA 能想到的调整就是 SSD 盘比用机械硬盘更好，但其实它至少包括了，使用什么样的磁盘阵列（RAID）级别、是否可以分散磁盘 IO、是否使用裸设备存放数据，使用哪种文件系统（目前比较推荐的是 XFS），操作系统的磁盘调度算法（目前
复制链接

扫一扫