Mysql分析指令值explain以及各字段中可能出现的值

1.定义

explain关键字可以模拟Msyql优化器执行sql语句，可以很好的分析sql语句或表结构的性能瓶颈

2.用途

1. 表的读取顺序
2. 数据读操作操作有哪些操作
3. 哪些索引可以被使用
4. 哪些索引实际被使用
5. 表之间是如何引用的
6. 每张表都有多少行被优化器查询

3.explain字段解析

概要描述

explain select *from t_cp_count_url;
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t_cp_count_url | ALL  | NULL          | NULL | NULL    | NULL |    8 | NULL  |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+

字段名	含义
id	sql执行的优先级
select_type	表示查询的类型
table	输出结果集的表
partitions	匹配的分区
type	表示表的连接类型
possible_keys	表示查询时，可以使用的索引
key	表示实际使用的索引
key_len	索引字段的字节数
ref	列与索引的比较
rows	扫描出的行数（估算的行数）
filtered	按表条件过滤的行百分比
extra	执行情况的描述和说明

1.id

id相同时，执行顺序由上至下

explain select t1.*, t2.* from t_pre_game_info t1 ,t_pre_draw_config t2;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                 |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    8 | NULL                                  |
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using join buffer (Block Nested Loop) |

id不同时，执行顺序由大到小

explain select t1.*,t2.* from (select * from t_pre_game_info)t1, t_pre_draw_config t2 where t1.pre_game_id = t2.pre_game_id;
+----+-------------+-----------------+------+---------------+-------------+---------+-------------------------+------+-------------+
| id | select_type | table           | type | possible_keys | key         | key_len | ref                     | rows | Extra       |
+----+-------------+-----------------+------+---------------+-------------+---------+-------------------------+------+-------------+
|  1 | PRIMARY     | t2              | ALL  | index1        | NULL        | NULL    | NULL                    |    8 | NULL        |
|  1 | PRIMARY     | <derived2>      | ref  | <auto_key0>   | <auto_key0> | 4       | prelogin.t2.pre_game_id |    2 | Using where |
|  2 | DERIVED     | t_pre_game_info | ALL  | NULL          | NULL        | NULL    | NULL                    |   10 | NULL        |

2.select_type

表示查询中每个select子句的类型

SIMPLE

简单查询，不使用UNION或者子查询

explain select * from t_pre_game_info;
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t_pre_game_info | ALL  | NULL          | NULL | NULL    | NULL |   10 | NULL  |

PRIMARY

子查询中最外层查询，查询中若包含任何复杂的子部份，最外层的select都会被标记为primary

explain select t1.*,t2.* from (select * from t_pre_game_info)t1, t_pre_draw_config t2 where t1.pre_game_id = t2.pre_game_id;
+----+-------------+-----------------+------+---------------+-------------+---------+-------------------------+------+-------------+
| id | select_type | table           | type | possible_keys | key         | key_len | ref                     | rows | Extra       |
+----+-------------+-----------------+------+---------------+-------------+---------+-------------------------+------+-------------+
|  1 | PRIMARY     | t2              | ALL  | index1        | NULL        | NULL    | NULL                    |    8 | NULL        |
|  1 | PRIMARY     | <derived2>      | ref  | <auto_key0>   | <auto_key0> | 4       | prelogin.t2.pre_game_id |    2 | Using where |
|  2 | DERIVED     | t_pre_game_info | ALL  | NULL          | NULL        | NULL    | NULL                    |   10 | NULL        |

UNION

union中的第二个或后面的select语句，取决于外面的查询

explain select pre_game_id from t_pre_game_info union select pre_game_id from t_pre_draw_config union select pre_game_id from t_pre_user_info;
+----+--------------+-------------------+-------+---------------+---------+---------+------+-------+-----------------+
| id | select_type  | table             | type  | possible_keys | key     | key_len | ref  | rows  | Extra           |
+----+--------------+-------------------+-------+---------------+---------+---------+------+-------+-----------------+
|  1 | PRIMARY      | t_pre_game_info   | index | NULL          | PRIMARY | 4       | NULL |    10 | Using index     |
|  2 | UNION        | t_pre_draw_config | index | NULL          | index1  | 8       | NULL |     8 | Using index     |
|  3 | UNION        | t_pre_user_info   | ALL   | NULL          | NULL    | NULL    | NULL | 22968 | NULL            |
| NULL | UNION RESULT | <union1,2,3>      | ALL   | NULL          | NULL    | NULL    | NULL |  NULL | Using temporary |

DEPENDENT UNION

当union在子查询中时，union中的第二个或后面的select语句

explain select * from t_pre_game_info where pre_game_id in(select pre_game_id from t_pre_draw_config union select pre_game_id from t_pre_user_info);
+----+--------------------+-------------------+------+---------------+--------+---------+------+-------+--------------------------+
| id | select_type        | table             | type | possible_keys | key    | key_len | ref  | rows  | Extra                    |
+----+--------------------+-------------------+------+---------------+--------+---------+------+-------+--------------------------+
|  1 | PRIMARY            | t_pre_game_info   | ALL  | NULL          | NULL   | NULL    | NULL |    10 | Using where              |
|  2 | DEPENDENT SUBQUERY | t_pre_draw_config | ref  | index1        | index1 | 4       | func |     4 | Using where; Using index |
|  3 | DEPENDENT UNION    | t_pre_user_info   | ALL  | NULL          | NULL   | NULL    | NULL | 22968 | Using where              |
| NULL | UNION RESULT       | <union2,3>        | ALL  | NULL          | NULL   | NULL    | NULL |  NULL | Using temporary          |

UNION RESULT

union的结果集

explain select * from t_pre_game_info where pre_game_id in(select pre_game_id from t_pre_draw_config union select pre_game_id from t_pre_user_info);
+----+--------------------+-------------------+------+---------------+--------+---------+------+-------+--------------------------+
| id | select_type        | table             | type | possible_keys | key    | key_len | ref  | rows  | Extra                    |
+----+--------------------+-------------------+------+---------------+--------+---------+------+-------+--------------------------+
|  1 | PRIMARY            | t_pre_game_info   | ALL  | NULL          | NULL   | NULL    | NULL |    10 | Using where              |
|  2 | DEPENDENT SUBQUERY | t_pre_draw_config | ref  | index1        | index1 | 4       | func |     4 | Using where; Using index |
|  3 | DEPENDENT UNION    | t_pre_user_info   | ALL  | NULL          | NULL   | NULL    | NULL | 22968 | Using where              |
| NULL | UNION RESULT       | <union2,3>        |