MySQL的查询性能分析神器：explain命令的使用详解

一口十个猪腰子
70 阅读5分钟 
create table `user` 
( `id` bigint(20) unsigned not null auto_increment, 
`name` varchar(64) default null, 
`age` bigint(20) unsigned default '0', 
`param` varchar(32) default null, 
`a` int(11) default '0', 
`b` int(11) default '0', 
`c` int(11) default '0', 
primary key (`id`), 
key `idx_a_b_c_d` (`a`, `b`, `c`), 
key `idx_age` (`age`), 
key `idx_name` (`name`) 
); 

create table `order_info` 
( 
`id` bigint(16) unsigned not null auto_increment, 
`ref_user_id` bigint(20) unsigned not null, 
`serial_no` varchar(32) not null, 
primary key (`id`), 
key `uk_user_id` (`ref_user_id`) 
); 

INSERT INTO hlj_sql.user (id, name, age, param, a, b, c) VALUES (1, 'xiaoming', 11, 'a', 1, 2, 3); 
INSERT INTO hlj_sql.user (id, name, age, param, a, b, c) VALUES (2, 'xiaohong', 23, 'b', 21, 21, 21); 
INSERT INTO hlj_sql.user (id, name, age, param, a, b, c) VALUES (3, 'liuqiangdong', 45, 'c', 56, 23, 23); 
INSERT INTO hlj_sql.user (id, name, age, param, a, b, c) VALUES (4, 'mayun', 123, 'd', 45, 12, 3); 
INSERT INTO hlj_sql.user (id, name, age, param, a, b, c) VALUES (5, 'leijun', 5, 'e', 12, 322, 1); 
INSERT INTO hlj_sql.order_info (id, ref_user_id, serial_no) VALUES (1, 1, '2'); 
INSERT INTO hlj_sql.order_info (id, ref_user_id, serial_no) VALUES (2, 2, '3'); 
INSERT INTO hlj_sql.order_info (id, ref_user_id, serial_no) VALUES (3, 3, '5'); 
INSERT INTO hlj_sql.order_info (id, ref_user_id, serial_no) VALUES (4, 2, '3'); 
INSERT INTO hlj_sql.order_info (id, ref_user_id, serial_no) VALUES (5, 4, '3'); 
INSERT INTO hlj_sql.order_info (id, ref_user_id, serial_no) VALUES (6, 5, '6'); 
INSERT INTO hlj_sql.order_info (id, ref_user_id, serial_no) VALUES (7, 4, '3');

image.png

image.png

image.png

image.png

image.png

image.png

image.png

image.png

image.png

image.png

image.png

explain + sql：

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| 5    |       | 
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
  1. id
  • 标志这个sql语句被分为几个(行数)独立的sql执行，执行顺序依照(1)从大到小(2)从上到下 依次排列执行 
  1. select_type

查询类型

+----+-------------+ 
| id | select_type | 
+----+-------------+ 
| 1 | SIMPLE       | 
+----+-------------+ 
| 2 | PRIMARY      | 
+----+-------------+ 
| 3 | SUBQUERY     | 
+----+-------------+ 
| 4 | DERIVED      | 
+----+-------------+ 
| 5 | UNION        | 
+----+-------------+ 
| 6 | UNION RESULT | 
+----+-------------+

SIMPLE    　 简单的select查询,查询中不包含子查询或者UNION

  • 　　PRIMARY            查询中包含子查询，其中最外层查询为PRIMARY
  • 　　SUBQUERY        别的查询语句（select或where）中的子查询
  •        DERIVED            在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表 中 ，，，，临时表！
  •        UNION                若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
  •        UNION RESULT UNION的结果

3.table 

  • 表示当前行的子查询设计的表

4.type

　表示查询用到了那种索引类型

| ALL | index | range | ref | eq_ref | const | system  | NULL |

从最好到最差依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system 　　表中只有一条记录，一般来说只在系统表里出现。

  • const 表示通过一次索引查询就查询到了，一般对应索引列为primarykey 或者unique where语句中 指定 一个常量，因为只匹配一行数据，MYSQL能把这个查询优化为一个常量，所以非常快。
  • eq_ref 唯一性索引扫描。此类型通常出现在多表的 join 查询，对于每一个从前面的表连接的对应列，当前表的对应列具有唯一性索引，最多只有一行数据与之匹配。
  • ref 非唯一性索引扫描。同上，但当前表的对应列不具有唯一性索引，可能有多行数据匹配。此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 
  • 最左前缀
  •  规则索引的查询. 
  • range 索引的范围查询。查询索引关键字某个范围的值。
  • index 全文索引扫描。与all基本相同，扫描了全文，但查询的字段被索引包含，故不需要读取表中数据，只需要读取索引树中的字段。
  • all 全文扫描。未使用索引，效率最低。

5.key 和 possible key 与 key_len

  • possible key 表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key字段决定.
  • key 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
  • key_len 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。www.cnblogs.com/zhoujinyi/p…
    • 覆盖索引：指在查询中，要查询的字段被某个索引的所有字段包含，(type一般为index),那么这个索引只出现在key中，不出现在possible key中。

6.ref

　　显示索引的哪一列被使用了。

  •  ref 显示使用哪个列或常数与key一起从表中选择行。在eq_ref 与ref中才不为null。
  •  如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

7.rows

MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数 这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好

8.extra

额外信息

  •  Using Filesort 表示对结果排序时无法使用索引，使用了一个临时索引对结果排序。
  •  Using Temporary 在查询中使用了临时表保存中间结果。
  •  Using Index 表示使用了覆盖索引(见5.) 如果同时出现了Using Where 表示索引被同时用来查找对应的键，若未出现，则仅仅用来读取数据。
  •  Using Join Buffer 表示在查询的时候 Join使用的非常多，使用了连接缓存，可以在配置文件中把缓冲区的join buffer调大一点。
  •  Using where 使用了where
  •  Impossible where where后的值总是false 比如 (...where i=1 and i=2)
  •  distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

转载

avatar
文章
阅读
粉丝