在学习SQL优化前，我们要知道SQL优化大部分都是基于查询去做的。

SQL优化要做的其实就是了解SQL的执行顺序，真对性的调整SQL之，使它更符合能提升查询效率的定义。从而减少数据库的查询时间和资源消耗。

在MySQL中，我们可以通过 <font style="color:rgb(28, 25, 23);">Explain</font>关键字，去了解MySQL中是如何处理SQL语句的，包括如何连接表以及什么顺序连接表等。 表的加载顺序 SQL 的查询类型，可能用到哪些索引，哪些索引又被实际使用，表与表之间的引用关系 一个表中有多少行被优化器查询等。 Explain 执行计划包含如下12个字段信息，分别是：
id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra。 SQL优化 | Explain - 图1 其实学习SQL优化很简单，你只要能看懂Explain 执行计划罗列出的12个字段，那么你基本上就可以很轻松定义到慢SQL的根源。从而做出针对性的优化。 下面我们主要，学习下Explain 的字段说明。 + 官网说明：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html 建测试表 学习过程总使用到的表和数据，请自行创建。 sql CREATE DATABASE explain_test; -- 创建表 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; CREATE TABLE `order` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `amount` double DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; CREATE TABLE `goods` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `order_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; -- 添加数据 INSERT INTO explain_test.user VALUES (1, 'Tom', 20); INSERT INTO explain_test.user VALUES (2, 'Jerry', 22); INSERT INTO explain_test.user VALUES (3, 'John', 25); INSERT INTO explain_test.order VALUES (1, 1, 199.5); INSERT INTO explain_test.order VALUES (2, 1, 299.5); INSERT INTO explain_test.order VALUES (3, 3, 599.5); INSERT INTO explain_test.goods VALUES (1, 'Book', 1); INSERT INTO explain_test.goods VALUES (2, 'Phone', 2); INSERT INTO explain_test.goods VALUES (3, 'Computer', 3); ## id 表示查询中执行select子句或者操作表的顺序，id的值越大，代表优先级越高，越先执行。 id排序大致会出现 3种情况： 1. id相同 看到三条记录的id都相同，可以理解成这三个表为一组，具有同样的优先级，执行顺序由上而下，具体顺序由优化器决定。 sql mysql> EXPLAIN SELECT u.*,o.* FROM explain_test.USER u JOIN explain_test.ORDER o ON u.id = o.user_id JOIN explain_test.goods g ON g.order_id = o.id; +----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+ | 1 | SIMPLE | g | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where | | 1 | SIMPLE | o | NULL | eq_ref | PRIMARY | PRIMARY | 4 | explain_test.g.order_id | 1 | 100.00 | Using where | | 1 | SIMPLE | u | NULL | eq_ref | PRIMARY | PRIMARY | 4 | explain_test.o.user_id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+ 2. id不相同 如果SQL中存在子查询，那么id的序号会递增，id值越大优先级越高，越先被执行 。 三个表依次嵌套，发现最里层的子查询id最大，最先执行。 sql mysql> EXPLAIN SELECT * FROM explain_test.goods g WHERE g.order_id = (SELECT id FROM explain_test.order o WHERE o.user_id = (SELECT id FROM explain_test.user u WHERE u.id = 1)); +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | PRIMARY | g | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | | 2 | SUBQUERY | o | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | | 3 | SUBQUERY | u | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 3. 一段SQL中有id相同的和id不相同的 在上方基础上简单做下调整，增加一个子查询，发现id的以上两种同时存在。 相同id划分为一组，这样就有三个组（1、2、3），同组的从上往下顺序执行，不同组 id值越大，优先级越高，越先执行。 sql mysql> EXPLAIN SELECT * FROM explain_test.goods g WHERE g.order_id = (SELECT id FROM explain_test.order o WHERE o.user_id = (SELECT id FROM explain_test.user u WHERE u.id = 1) AND g.id in (SELECT id FROM explain_test.goods gg WHERE gg.id = 1)) ; +----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | PRIMARY | g | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | gg | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | o | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | | 3 | SUBQUERY | u | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ ## select_type 表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等。值域如下 1. SIMPLE：简单select查询，不使用union或 子查询 语句 sql mysql> explain SELECT * FROM explain_test.order; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | order | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 2. PRIMARY：复杂查询中最外层的。查询中包含任何复杂的子部分，最外层的select被标记为PRIMARY 3. SUBQUERY：当 select 或 where 列表中包含了子查询，该子查询被标记为：SUBQUERY 。 sql mysql> explain SELECT * FROM explain_test.order o WHERE o.user_id = (SELECT id FROM explain_test.user u WHERE u.id = 1) ; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | PRIMARY | o | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | | 2 | SUBQUERY | u | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 4. UNION：union 后面的Select语句 5. UNION RESULT：包含union的结果集，在union和union all语句中，因为它不需要参与查询，所以id字段为null sql mysql> explain select id from explain_test.user union select id from explain_test.order; +------+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | user | NULL | index | NULL | PRIMARY | 4 | NULL | 3 | 100.00 | Using index | | 2 | UNION | order | NULL | index | NULL | PRIMARY | 4 | NULL | 3 | 100.00 | Using index | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +------+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ 5. DEPENDENT UNION：一般是子查询中的第二个select语句（取决于外查询，mysql内部也有些优化） sql mysql> explain select * from explain_test.user where id in (select id from explain_test.order union select id from explain_test.goods); +------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | order | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index | | 3 | DEPENDENT UNION | goods | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+ 6. DERIVED：在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中 sql mysql> explain SELECT c.* FROM (SELECT * FROM explain_test.user limit 1) c; +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+ | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 2 | DERIVED | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+ ## table 查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表。 当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id。 ## partitions partitions列显示查询将访问的分区,其主要作用有: 1. 了解查询是走全分区扫描还是部分分区，以便进行分区裁剪优化。 2. 判断是否可利用分区进行并行查询加速。 3. 评估分区扫描对查询性能的影响。 partitions的常见取值包括: + NULL - 表未分区 + p0，p1 - 查询使用了分区裁剪条件,只扫描了部分分区 + ALL - 全表扫描,查询未使用分区裁剪 ## type⭐ 显示的是访问类型，访问类型表示我是以哪种方式访问我们的数据，是在SQL优化中很重要的一个指标，最直接是All，扫描全表，效率最低。访问类型有很多，效率从最好到最坏依次是： sql system -> const -> eq_ref -> ref -> fulltext -> ref_or_null -> index_merge -> unique_subquery -> index_subquery -> range ->index -> All 一般情况下，要保证查询达到range级别，最好达到ref级别
  1. system
  2. const

表示查询时命中 primary key主键或者 unique 唯一索引，或者被连接的部分是一个常量（const）值。

这类扫描效率极高，返回数据量少，速度非常快。

  1. ### 命中主键
  2. mysql> explain select * from explain_test.user where id = 1;
  3. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  4. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
  5. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  6. |  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
  7. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  9. ### 命中不是主键或索引的值
  10. mysql> explain select * from explain_test.user where age = 20;
  11. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  12. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  13. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  14. |  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
  15. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  1. eq_ref

查询时命中主键primary key 或者 unique key索引， type 就是 eq_ref。

  1. mysql> explain select * from explain_test.user u,explain_test.order o where u.id = o.user_id;
  2. +----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
  3. | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                    | rows | filtered | Extra       |
  4. +----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
  5. |  1 | SIMPLE      | o     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                   |    3 |   100.00 | Using where |
  6. |  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | explain_test.o.user_id |    1 |   100.00 | NULL        |
  7. +----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
  1. ref

区别于eq_ref ，ref表示使用非唯一性索引进行数据搜索

  1. mysql> create index idx_3 on user(age);
  2. Query OK, 0 rows affected (0.07 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  5. mysql> explain select id,name from explain_test.user where age = 20;
  6. +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
  7. | id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
  8. +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
  9. |  1 | SIMPLE      | user  | NULL       | ref  | idx_3         | idx_3 | 5       | const |    1 |   100.00 | NULL  |
  10. +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
  1. ref_or_null

类型类似于 ref，区别在于 MySQL会额外搜索包含NULL值的行。

  1. mysql> explain select id,name from explain_test.user where age = 20 or age is null;
  2. +----+-------------+-------+------------+-------------+---------------+-------+---------+-------+------+----------+-----------------------+
  3. | id | select_type | table | partitions | type        | possible_keys | key   | key_len | ref   | rows | filtered | Extra                 |
  4. +----+-------------+-------+------------+-------------+---------------+-------+---------+-------+------+----------+-----------------------+
  5. |  1 | SIMPLE      | user  | NULL       | ref_or_null | idx_3         | idx_3 | 5       | const |    2 |   100.00 | Using index condition |
  6. +----+-------------+-------+------------+-------------+---------------+-------+---------+-------+------+----------+-----------------------+
  1. index_merge（多索引查询）

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

  1. mysql> explain select * from explain_test.user where name = 'Tom' or age =20;
  2. +----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
  3. | id | select_type | table | partitions | type        | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                   |
  4. +----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
  5. |  1 | SIMPLE      | user  | NULL       | index_merge | id_name,idx_3 | id_name,idx_3 | 203,5   | NULL |    2 |   100.00 | Using union(id_name,idx_3); Using where |
  6. +----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
  1. unique_subquery
  2. index_subquery
  3. range

使用普通索引进行查询。简单点说就是针对一个有索引的字段，给定范围检索数据。在where语句中使用 bettween…and、<、>、<=、in 等条件查询 type 都是 range。

  1. mysql> explain select * from explain_test.user where age between 19 and 21; 
  2. +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
  3. | id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                 |
  4. +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
  5. |  1 | SIMPLE      | user  | NULL       | range | idx_3         | idx_3 | 5       | NULL |    1 |   100.00 | Using index condition |
  6. +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
  1. index

Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。

  1. mysql> explain select id from explain_test.user; 
  2. +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
  3. | id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra       |
  4. +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
  5. |  1 | SIMPLE      | user  | NULL       | index | NULL          | idx_3 | 5       | NULL |    3 |   100.00 | Using index |
  6. +----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
  1. all

代表全表扫描，从而匹配出需要的数据，效率最差，当数据量大的时候，就需要进行优化。

  1. mysql> explain select * from explain_test.user; 
  2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
  3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
  4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
  5. |  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
  6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

index，all的区别：都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。

possible_keys

表示在MySQL中通过哪些索引，能让我们在表中找到想要的记录，一旦查询涉及到的某个字段上存在索引，则索引将被列出，但这个索引并不定一会是最终查询数据时所被用到的索引

key⭐

区别于possible_keys，key是查询中实际使用到的索引，若没有使用索引，显示为NULL。

当 type 为 index_merge 时，可能会显示多个索引

key_len

ref

ref：常见的有：const，func，null，字段名。

  • 当使用常量等值查询，显示const
  • 当关联查询时，会显示相应关联表的关联字段
  • 如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为func
    其他情况null

rows

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

这是评估SQL 性能的一个比较重要的数据，mysql需要扫描的行数，很直观的显示 SQL 性能的好坏，一般情况下 rows 值越小越好。

filtered

filtered 这个是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

Extra⭐

Extra字段显示的是MySQL在查询中的一些额外信息。

作用：可以给出MySQL如何执行查询的提示和说明。

通俗来，Extra字段提供了查询执行过程中的一些额外描述信息,以方便我们更好地理解和分析查询的执行计划。

Extra字段常见的可能取值及含义如下：
  1. Using index

表示在select 操作中使用了覆盖索引，通俗一点讲就是查询的列被索引覆盖，使用到覆盖索引查询速度会非常快，SQl优化中理想的状态。

什么又是覆盖索引?

一条 SQL只需要通过索引就可以返回，我们所需要查询的数据（一个或几个字段），而不必通过二级索引，查到主键之后再通过主键查询整行数据（select * ）。

  1. mysql> explain SELECT name FROM explain_test.user u WHERE u.name = 'Tom';
  2. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
  3. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
  4. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
  5. |  1 | SIMPLE      | u     | NULL       | const | id_name       | id_name | 203     | const |    1 |   100.00 | Using index |
  6. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
注意：想要使用到覆盖索引，我们在 select 时只取出需要的字段，不可select *，而且该字段建了索引。 
  1. mysql> explain SELECT * FROM explain_test.user u WHERE u.name = 'Tom';
  2. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  3. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
  4. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  5. |  1 | SIMPLE      | u     | NULL       | const | id_name       | id_name | 203     | const |    1 |   100.00 | NULL  |
  6. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  1. Using where

查询时未找到可用的索引，所以通过where条件过滤获取所需数据，但要注意的是并不是所有带where语句的查询都会显示Using where。
下边示例amount 未用到索引，type 为 ALL，即MySQL通过全表扫描后再按where条件筛选数据。

  1. mysql> explain SELECT * FROM explain_test.order o WHERE o.amount = '199.5';
  2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  5. |  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
  6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  1. Using temporary

表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。

  1. mysql> explain SELECT LEFT(u.name,1),COUNT(1) FROM explain_test.user u GROUP BY LEFT(u.name,1);
  2. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
  3. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                        |
  4. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
  5. |  1 | SIMPLE      | u     | NULL       | index | id_name       | id_name | 203     | NULL |    3 |   100.00 | Using index; Using temporary |
  6. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
  1. Using filesort

表示无法利用索引完成的排序操作，也就是ORDER BY的字段没有索引，通常这样的SQL都是需要优化的。

mysql> explain select * from explain_test.user u order by u.name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+


mysql> explain select id,name from explain_test.user u order by u.name;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | index | NULL          | id_name | 203     | NULL |    3 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

上方两段SQL，分别是用*返回和对应的的列去查询，所执行的情况，会发现，使用select *的语句，无法走所以，最终返回<font style="color:rgb(77, 77, 77);">Using filesort</font>。

如果u.name字段没建立索引，最终的执行结果也是Using filesort。
  1. Using join buffer

在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果。

在上方的order、goods两个表中分别有一个name字段，在不添加索引，进行关联，如下。

发现Extra 列变成 Using join buffer，type均为全表扫描，这也是SQL优化中需要注意的地方。

mysql> explain select * from explain_test.user u,explain_test.goods g where u.name = g.name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                                       |
|  1 | SIMPLE      | g     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+

OK，接着我们给两个表中的name添加索引，再次关联查询如下：

mysql> alter table explain_test.user add unique index id_name (name);
mysql> alter table explain_test.goods add unique index id_name (name);

mysql> explain select u.name from explain_test.user u,explain_test.goods g where u.name = g.name;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra                    |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+--------------------------+
|  1 | SIMPLE      | u     | NULL       | index  | id_name       | id_name | 203     | NULL                |    3 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | g     | NULL       | eq_ref | id_name       | id_name | 203     | explain_test.u.name |    1 |   100.00 | Using index              |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+--------------------------+

此时Extra字段的值标识，已经走索引了。不过需要注意，如果在关联的时候，使用*返回数据行，是无法走索引检索的。

mysql> explain select * from explain_test.user u,explain_test.goods g where u.name = g.name;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | ALL    | id_name       | NULL    | NULL    | NULL                |    3 |   100.00 | Using where |
|  1 | SIMPLE      | g     | NULL       | eq_ref | id_name       | id_name | 203     | explain_test.u.name |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
  1. Impossible where

查询时用了不太正确的where语句，导致没有符合条件的行。

mysql> explain select * from explain_test.order where 1=2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
  1. No tables used

No tables used：我们的查询语句中没有FROM子句，或者有 FROM DUAL子句。

mysql> explain select now();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+