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

Mysql的explain关键字用于模拟优化器执行SQL,分析表读取顺序、数据操作和性能瓶颈。通过分析id、select_type、table、type、possible_keys、key、key_len、ref、rows和Extra字段,可以理解查询执行过程并优化性能。常见的性能优化包括避免全表扫描、合理使用索引、限制in操作的数量等。
摘要由CSDN通过智能技术生成

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>        | 
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值