mysql explain 工具，字段解析

explain 个字段意义

id列 ：查询语句在分析时所属的编号

select_type ：显示是简单的select还是复杂的select

table ：表名

type ：访问类型

possible_key ：可以使用的键

key ：使用的索引
key_len ：索引列中可能的最大长度

ref ：在key列记录的索引值中查找所用的列或常量
row ：估计为了找到所需行而需要读取的行数
extra ：其他信息

id列是查询语句在分析时所属的编号，如果语句中包含子查询，则会有多个id
mysql> explain select * from user where uid<(select count(*) as c from user);
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     | user | range | PRIMARY       | PRIMARY | 4       | NULL | 4853 | Using where |
|  2 | SUBQUERY    | user | index | NULL          | city_id | 4       | NULL | 9706 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+------+——————+

select_type 显示是简单的select还是复杂的select
     subquery 代表子查询
     derived 结果放在临时表中，一般在from中的子查询会有此影响
     union 
     union result

type 访问类型

     all 全表扫描
mysql> explain select * from user;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user | ALL  | NULL          | NULL | NULL    | NULL | 9706 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+

     index 使用索引次序扫描，主要优点避免了排序
     尽量不要使用select *，select *会随机访问行，造成较大开销
     如果分析中extra列显示Using index，这证明只扫描索引列
mysql> explain select uid from user;
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | user | index | NULL          | city_id | 4       | NULL | 9706 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
    
     range 范围扫描 只扫描索引部分数据
mysql> explain select * from user where pdate>199 and city_id>10 and uid>100;
+----+-------------+---------+-------+-----------------------+---------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys         | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+-----------------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | user | range | PRIMARY,city_id,pdate | PRIMARY | 4       | NULL | 4853 | Using where |
+----+-------------+---------+-------+-----------------------+---------+---------+------+------+——————+

     ref 索引查找
     eq_ref 索引查找，只返回1条
     注意当条件不为唯一键是才会触发，也就是如果使用主键或唯一键不会触发（唯一键会触发更高效的const类型查询）
mysql> explain select * from user where pdate=2;
+----+-------------+---------+------+---------------+-------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys | key   | key_len | ref   | rows | Extra |
+----+-------------+---------+------+---------------+-------+---------+-------+------+-------+
|  1 | SIMPLE      | user | ref  | pdate         | pdate | 4       | const |    1 | NULL  |
+----+-------------+---------+------+---------------+-------+---------+-------+------+-------+

      const，system 能对查询的某部分优化并产生一个常量是会使用这种访问类型，比如主键作为条件
mysql> explain select * from user where uid=2;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | user | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
   
     null mysql在优化阶段分解语句，执行阶段不需要访问表
mysql> explain select max(uid) from user;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+