本文结合 http://blog.csdn.net/mijinhuandu/article/details/50818278 [mysql explain用法和结果的含义] 阅读
一、根据 select_type 的值,我们可以认为 select 语句可以划分为三种:只有UNION查询、各种情况的子查询以及非以上两种情况的查询。
以下是各类型出现的情况:
- SIMPLE:简单SELECT(不使用UNION或子查询);
- PRIMARY:在使用UNION、子查询的情况下,最外面的SELECT
- 在单独使用 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 | |
+----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+
二、联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
- 关于 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 | |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
其实不太理解他们两个,但既然已经是最好了,应该不会说有“再弄好点”的需求了吧,多半是“啊 是这两个类型 那优化就到此为止吧”或者“啊 这句字是这两个类型的 优化的很好嘛”的感叹