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

执行语句

explain + SQL 语句
  

表头信息

字段	描述
id	Select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序。 id 相同：执行顺序由上而下 id 不同：如果是子查询，id 序号会递增，id 越大优先级越高，越先被执行 id 相同不同同时存在：id 如果相同，可以认为是一组，由上往下执行；在所有组里 id 越大，优先级越高，越先执行
select_type	主要用于区别普通查询、联合查询、子查询等的复杂程度。 SIMPLE：简单的 select 查询，查询中不包含子查询或者 UNION PRIMARY：查询中若包含任何复杂的自查询，最外层查询为 PRIMARY SUBQUERY：在 SELECT 或 WHERE 中包含子查询 UNCACHEABLESUBQUERY：一个子查询的结果不能被缓存，必须重新评估外链接的第一行 DERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED（衍生）MySQL 会递归执行这些子查询，把结果放进临时表 UNION：若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION，若 UNION 包含在 FROM 子句的子查询，则外层 SELECT 将被标记为 DERIVED UNIONRESULT：从 UNION 表中获取结果的 SELECT
table	显示这行数据是关于那张表
type	要记住以下 10 个状态，（从左往右，越靠左边的越优秀） NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL MySQL 能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。 存在这样一种情况，大家都知道索引是将数据在 B+Tree 中进行排序了，所以你的查询速率才这么高，那么 B+ 树的最边上的叶子节点是不是要么是最大值要么是最小值啊？既然你都知道了，那 MySQL 比你更知道啊！当你要查询最大值或者最小值时，MySQL 会直接到你的索引得分叶子节点上直接拿，所以不用访问表或者索引。 表只有一行记录（等于系统表），这是 const 类型的特例，平时不大会出现，可以忽略。 表示通过索引一次就能找到，const 用于比较 primary 和 unique 索引。因为只匹配一行数据，所以很快。简单来说，const 是直接按主键或唯一键读取。 用于联表查询的情况，按联表的主键或唯一键联合查询。多表 join 时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了 system 和 const 之外最好的类型。 ref 可以用于单表扫描或者连接。如果是连接的话，驱动表的一条记录能够在被驱动表中通过非唯一（主键）属性所在索引中匹配多行数据，或者是在单表查询的时候通过非唯一（主键）属性所在索引中查到一行数据。 ref_or_null 类似 ref，但是可以搜索值为 NULL 的行。 index_merge 表示查询使用了两个以上的索引，最后取交集或者并集，常见 and，or 的条件使用了不同的索引，官方排序这个在 ref_or_null 之后，但是实际上由于要读取
possible_keys	MySQL 认为可以利用的索引
key	实际使用的索引
key_len	使用索引的长度
rows	预计要读取的行数
Extra	其他信息，比如是否使用了临时表、是否使用覆盖索引等

示例

EXPLAIN SELECT  FROM employees WHERE last_name = 'Smith';
  

输出结果可能如下所示：

id	select_type	table	type	possible_keys	key	key_len	rows	Extra
1	SIMPLE	employees	ref	last_name_index	last_name_index	255	10	Using index

在这个示例中，EXPLAIN 输出结果表明： 该查询是一个简单的 SELECT 查询（select_type = SIMPLE） 该查询正在读取 employees 表（table = employees） MySQL 使用了 last_name_index 索引（type = ref，key = last_name_index） 该查询预计将读取 10 行（rows = 10）

使用 EXPLAIN 优化查询

EXPLAIN 可以帮助你找出查询性能瓶颈。以下是一些使用 EXPLAIN 优化查询的技巧： 查看 id 值。id 值较高的子查询或表将优先执行。 查看 type 值。const、eq_ref 和 ref 类型索引比 index 类型更有效率。 查看 rows 值。rows 值较高的查询可能需要优化。 查看 Extra 值。Extra 值可以提供有关查询如何执行的其他信息。 通过理解 EXPLAIN 输出结果，你可以识别查询中的瓶颈并采取措施优化它们。这可以显著提高 MySQL 查询的性能。

---

mysql数据库的一个表字段为explain,一查询这个字段就报错！

使用引号把字段名引起来就行了，`explain`,``为Tab键上面的一个键，不是单引号另外，可以在SQL中加表名如select from a

对order by的理解

前言

日常开发中，我们经常会使用到order by，亲爱的小伙伴，你是否知道order by 的工作原理呢？order by的优化思路是怎样的呢？使用order by有哪些注意的问题呢？本文将跟大家一起来学习，攻克order by~

一个使用order by 的简单例子

假设用一张员工表，表结构如下：

表数据如下：

我们现在有这么一个需求：查询前10个，来自深圳员工的姓名、年龄、城市，并且按照年龄小到大排序。对应的 SQL 语句就可以这么写：

这条语句的逻辑很清楚，但是它的底层执行流程是怎样的呢？

order by 工作原理

explain 执行计划

我们先用Explain关键字查看一下执行计划

我们可以发现，这条SQL使用到了索引，并且也用到排序。那么它是怎么排序的呢？

全字段排序

MySQL 会给每个查询线程分配一块小内存，用于排序的，称为 sort_buffer。什么时候把字段放进去排序呢，其实是通过idx_city索引找到对应的数据，才把数据放进去啦。

我们回顾下索引是怎么找到匹配的数据的，现在先把索引树画出来吧，idx_city索引树如下：

idx_city索引树，叶子节点存储的是主键id。还有一棵id主键聚族索引树，我们再画出聚族索引树图吧：

我们的查询语句是怎么找到匹配数据的呢？先通过idx_city索引树，找到对应的主键id，然后再通过拿到的主键id，搜索id主键索引树，找到对应的行数据。

加上order by之后，整体的执行流程就是：

执行示意图如下：

将查询所需的字段全部读取到sort_buffer中，就是全字段排序。这里面，有些小伙伴可能会有个疑问,把查询的所有字段都放到sort_buffer，而sort_buffer是一块内存来的，如果数据量太大，sort_buffer放不下怎么办呢？

磁盘临时文件辅助排序

实际上，sort_buffer的大小是由一个参数控制的：sort_buffer_size。如果要排序的数据小于sort_buffer_size，排序在sort_buffer 内存中完成，如果要排序的数据大于sort_buffer_size，则借助磁盘文件来进行排序

如何确定是否使用了磁盘文件来进行排序呢？可以使用以下这几个 命令

可以从 number_of_tmp_files 中看出，是否使用了临时文件。

number_of_tmp_files 表示使用来排序的磁盘临时文件数。如果number_of_tmp_files>0，则表示使用了磁盘文件来进行排序。

使用了磁盘临时文件，整个排序过程又是怎样的呢？

TPS: 借助磁盘临时小文件排序，实际上使用的是归并排序算法。

小伙伴们可能会有个疑问，既然sort_buffer放不下，就需要用到临时磁盘文件，这会影响排序效率。那为什么还要把排序不相关的字段（name，city）放到sort_buffer中呢？只放排序相关的age字段，它不香吗？可以了解下rowid 排序。

rowid 排序

rowid 排序就是，只把查询SQL需要用于排序的字段和主键id，放到sort_buffer中。那怎么确定走的是全字段排序还是rowid 排序排序呢？

实际上有个参数控制的。这个参数就是max_length_for_sort_data，它表示MySQL用于排序行数据的长度的一个参数，如果单行的长度超过这个值，MySQL 就认为单行太大，就换rowid 排序。我们可以通过 命令 看下这个参数取值。

max_length_for_sort_data 默认值是1024。因为本文示例中name,age,city长度=64+4+64 =132 < 1024, 所以走的是全字段排序。我们来改下这个参数，改小一点.

使用rowid 排序的话，整个SQL执行流程又是怎样的呢？

执行示意图如下：

对比一下全字段排序的流程，rowid 排序多了一次回表。

什么是回表？拿到主键再回到主键索引查询的过程，就叫做回表”

我们通过optimizer_trace，可以看到是否使用了rowid排序的：

全字段排序与rowid排序对比

一般情况下，对于InnoDB存储引擎，会优先使用全字段排序。可以发现 max_length_for_sort_data 参数设置为1024，这个数比较大的。一般情况下，排序字段不会超过这个值，也就是都会走全字段排序。

order by的一些优化思路

我们如何优化order by语句呢？

联合索引优化

再回顾下示例SQL的查询计划

我们给查询条件city和排序字段age，加个联合索引idx_city_age。再去查看执行计划：

可以发现，加上idx_city_age联合索引，就不需要Using filesort排序了。为什么呢？因为索引本身是有序的，我们可以看下idx_city_age联合索引示意图，如下：

整个SQL执行流程变成酱紫：

流程示意图如下：

从示意图看来，还是有一次回表操作。针对本次示例，有没有更高效的方案呢？有的，可以使用覆盖索引：

覆盖索引：在查询的数据列里面，不需要回表去查，直接从索引列就能取到想要的结果。换句话说，你SQL用到的索引列数据，覆盖了查询结果的列，就算上覆盖索引了。”

我们给city，name，age 组成一个联合索引，即可用到了覆盖索引，这时候SQL执行时，连回表操作都可以省去啦。

调整参数优化

我们还可以通过调整参数，去优化order by的执行。比如可以调整sort_buffer_size的值。因为sort_buffer值太小，数据量大的话，会借助磁盘临时文件排序。如果MySQL服务器配置高的话，可以使用稍微调整大点。

我们还可以调整max_length_for_sort_data的值，这个值太小的话，order by会走rowid排序，会回表，降低查询性能。所以max_length_for_sort_data可以适当大一点。

当然，很多时候，这些MySQL参数值，我们直接采用默认值就可以了。

使用order by 的一些注意点:没有where条件，order by字段需要加索引吗

日常开发过程中，我们可能会遇到没有where条件的order by，那么，这时候order by后面的字段是否需要加索引呢。如有这么一个SQL，create_time是否需要加索引：

无条件查询的话，即使create_time上有索引,也不会使用到。因为MySQL优化器认为走普通二级索引，再去回表成本比全表扫描排序更高。所以选择走全表扫描,然后根据全字段排序或者rowid排序来进行。

如果查询SQL修改一下：

分页limit过大时，会导致大量排序怎么办?

假设SQL如下：

索引存储顺序与order by不一致，如何优化？

假设有联合索引 idx_age_name, 我们需求修改为这样：查询前10个员工的姓名、年龄，并且按照年龄小到大排序，如果年龄相同，则按姓名降序排。对应的 SQL 语句就可以这么写：

我们看下执行计划，发现使用到Using filesort

这是因为，idx_age_name索引树中，age从小到大排序，如果age相同，再按name从小到大排序。而order by 中，是按age从小到大排序，如果age相同，再按name从大到小排序。也就是说，索引存储顺序与order by不一致。

我们怎么优化呢？如果MySQL是8.0版本，支持Descending Indexes，可以这样修改索引：

使用了in条件多个属性时，SQL执行是否有排序过程

如果我们有联合索引idx_city_name，执行这个SQL的话，是不会走排序过程的，如下：

但是，如果使用in条件，并且有多个条件时，就会有排序过程。

这是因为:in有两个条件，在满足深圳时，age是排好序的，但是把满足上海的age也加进来，就不能保证满足所有的age都是排好序的。因此需要Using filesort。