MySQL查询计划学习
以select_type为线索：
1） SIMPLE：简单的SELECT，不实用UNION或者子查询
mysql> explain select * from t2; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 100 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)
2） PRIMARY：最外层SELECT。
mysql> explain select * from (select * from t2 where id2=2) b; +----+-------------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 100 | NULL | | 2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where | +----+-------------+------------+------+---------------+------+---------+------+------+-------------+ 2 rows in set (0.00 sec)
其中using where表示使用了where子句，过滤了元组
3） UNION：第二层，在SELECT之后使用了UNION。
mysql> explain select * from t1 union select * from t2; +----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10208 | NULL | | 2 | UNION | t2 | ALL | NULL | NULL | NULL | NULL | 100 | NULL | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+ 3 rows in set (0.00 sec)
其中using temporary表示使用了临时表保存中间结果。
4） DEPENDENT UNION：UNION语句中的第二个SELECT，依赖于外部子查询。
mysql> explain select * from t1 where id1 in (select id2 from t2 where id2<10 union select id3 from t3 where id3<5);
+----+--------------------+------------+------+---------------+------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+------+---------------+------+---------+------+-------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10208 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
| 3 | DEPENDENT UNION | t3 | ref | id3 | id3 | 5 | func | 1 | Using where; Using index |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------------+------------+------+---------------+------+---------+------+-------+--------------------------+
4 rows in set (0.00 sec)
其中possible_keys表示备选索引为id3，key表示经过优化器选定的索引为id3，type为ref表示使用单表t3扫描。
5） UNION RESULT：UNION的结果。
mysql> explain select * from t1 union select * from t2; +----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10208 | NULL | | 2 | UNION | t2 | ALL | NULL | NULL | NULL | NULL | 100 | NULL | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+ 3 rows in set (0.00 sec)
6） SUBQUERY：子查询中的第一个SELECT。
mysql> explain select * from t1 where id1=(select id2 from t2 where id2=2); +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | PRIMARY | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | | 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ 2 rows in set (0.00 sec)
其中type为const表示访问常量表，where条件筛选后至多有一条元组匹配。
key为PRIMARY表示主键为选定的索引
type: 表示表的连接类型，性能由好到坏，依次为，
system，表中只有一行，常量表。
const，单表中最多有一行匹配，如pramary key 或者 unique index
eq_ref，对于前面的每一行，此表中只查询一条记录，比如多表连接中，使用primary key 或 unique index
ref，与eq_ref 类似，区别在于不是primary key 或qunique index, 而是普通索引。
ref_or_null，与ref 类似，区别在于查询中包含对null的查询。
index_merge，索引合并优化
unique_subquery，in的后面是一个查询主键字段的子查询。
index_subquery，与unique_subquery类似，区别在于是查询非唯一索引字段的子查询。
range，单表中的范围查询。
index，对于前面的每一行，都通过查询索引来得到数据。
all，全表扫描。
7） DERIVED：被驱动的SELECT子查询
mysql> explain select * from (select * from t2 where id2=2) b; +----+-------------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 100 | NULL | | 2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where | +----+-------------+------------+------+---------------+------+---------+------+------+-------------+ 2 rows in set (0.00 sec)
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
