mysql explain 各字段的理解

本文结合 http://blog.csdn.net/mijinhuandu/article/details/50818278 [mysql explain用法和结果的含义] 阅读

一、根据 select_type 的值，我们可以认为 select 语句可以划分为三种：只有UNION查询、各种情况的子查询以及非以上两种情况的查询。
以下是各类型出现的情况：

1. SIMPLE：简单SELECT(不使用UNION或子查询)；
2. PRIMARY：在使用UNION、子查询的情况下，最外面的SELECT
3. 在单独使用 UNION、单独使用子查询以及混合使用时会各自出现的类型有
   • UNION：UNION中的第二个或后面的SELECT语句
   • DEPENDENT UNION：UNION中的第二个或后面的SELECT语句,取决于外面的查询
   • UNION RESULT：UNION 的结果
   • SUBQUERY：子查询中的第一个SELECT
   • DEPENDENT SUBQUERY：子查询中的第一个SELECT,取决于外面的查询
   • DERIVED：导出表的SELECT(FROM子句的子查询)

在记忆这些类型时可以从只有UNION查询、各种情况的子查询以及非以上两种情况的查询上着手SIMPLE、PRIMARY、UNION、UNION RESULT、DERIVED 是随便写写就能得到的类型理解起来也比较容易，其他三种不经常写吧

#非 union 查询和子查询 SIMPLE #
mysql> explain select * from t1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 7525 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
 
  

#只有 union 查询 PRIMARY、UNION、UNION RESULT# 包含 union 和子查询任意一种情况都会出现 PRIMARY #
mysql> explain select * from t1 union select * from t1;
+----+--------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+--------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY      | t1         | ALL  | NULL          | NULL | NULL    | NULL | 7525 |       |
|  2 | UNION        | t1         | ALL  | NULL          | NULL | NULL    | NULL | 7525 |       |
|NULL| UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL |       |
+----+--------------+------------+------+---------------+------+---------+------+------+-------+

#当子查询是from子句时#
mysql> explain select * from (select * from t1) as t;
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 7273 |       |
|  2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 7525 |       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+

#当子查询是where子句且子句返回单行单列，where查询是等号比较时#
mysql> explain select * from t1 where uid=(select uid from t1 where uid=1);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | t1    | const | PRIMARY       | PRIMARY | 4       | const |    1 |             |
|  2 | SUBQUERY    | t1    | const | PRIMARY       | PRIMARY | 4       |       |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

#当子查询为union查询时，第一个union的select_type是DEPENDENT SUBQUERY，第二个union的select_type是DEPENDENT UNION。#
mysql> explain select * from t1 where uid in (select uid from t1 union select uid from t1);
+----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+
| id | select_type        | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | t1         | ALL    | NULL          | NULL    | NULL    | NULL | 7054 | Using where |
|  2 | DEPENDENT SUBQUERY | t1         | eq_ref | PRIMARY       | PRIMARY | 4       | func |    1 | Using index |
|  3 | DEPENDENT UNION    | t1         | eq_ref | PRIMARY       | PRIMARY | 4       | func |    1 | Using index |
|NULL| UNION RESULT       | <union2,3> | ALL    | NULL          | NULL    | NULL    | NULL | NULL |             |
+----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+

二、联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序：

1. 关于 system 和 const 测试了下

mysql> explain select * from t1 where uid = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

mysql> explain select * from (select * from t1 where uid = 1) as a;
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | t1         | const  | PRIMARY       | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+

其实不太理解他们两个，但既然已经是最好了，应该不会说有“再弄好点”的需求了吧，多半是“啊 是这两个类型 那优化就到此为止吧”或者“啊 这句字是这两个类型的 优化的很好嘛”的感叹