xml地图|网站地图|网站标签 [设为首页] [加入收藏]
当前位置: www8029com > 澳门新葡8522最新网站 > 正文

EXPLAIN命令详解学习,常用的优化步骤

时间:2019-08-26 17:36来源:澳门新葡8522最新网站
在开始博客之前,还是同样的给一个大概的目录结构,实则即为一般MySQL的优化步骤 MySQL EXPLAIN命令详解学习 1、查看SQL的执行频率---------------使用showstatus命令   2、定位哪些需要优化的

在开始博客之前,还是同样的给一个大概的目录结构,实则即为一般MySQL的优化步骤

MySQL EXPLAIN命令详解学习

1、查看SQL的执行频率---------------使用show status命令

 

2、定位哪些需要优化的SQL------------通过慢查询记录 show processlist命令查看当前线程

MySQL的EXPLAIN命令用于SQL语句的查询执行计划(QEP)。这条命令的输出结果能够让我们了解MySQL 优化器是如何执行

3、分析为什么SQL执行效率低------------使用explain/desc命令分析

SQL 语句的。这条命令并没有提供任何调整建议,但它能够提供重要的信息帮助你做出调优决策。

  • 相关列简单解释:type、table、select_type...

1 语法

4、对症下药采取优化措施-----------举例采取index进行优化

MySQL 的EXPLAIN 语法可以运行在SELECT 语句或者特定表上。如果作用在表上,那么此命令等同于DESC 表命令。UPDATE

  • 如何使用索引?
  • 使用索引应该注意的事项
  • 查看索引使用情况

和DELETE 命令也需要进行性能改进,当这些命令不是直接在表的主码上运行时,为了确保最优化的索引使用率,需要把它们改

主要参考资料:《深入浅出MySQL》,

写成SELECT 语句(以便对它们执行EXPLAIN 命令)。请看下面的示例:

 

UPDATE table1


SET col1 = X, col2 = Y

 

WHERE id1 = 9

一、查看SQL执行频率

  使用show [session|gobal] status命令了解SQL执行频率、线程缓存内的线程的数量、当前打开的连接的数量、获得的表的锁的次数等。

比如执行show status like 'Com_%'查看每个语句执行的次数即频率,其中Com_xxx中xxx表示就是语句,比如Com_select:执行select操作的次数。

 1 mysql> use test;
 2 Database changed
 3 mysql> show status like 'Com_%';
 4  ----------------------------- ------- 
 5 | Variable_name               | Value |
 6  ----------------------------- ------- 
 7 | Com_admin_commands          | 0     |
 8 | Com_assign_to_keycache      | 0     |
 9 | Com_alter_db                | 0     |
10 | Com_alter_db_upgrade        | 0     |
11 | Com_alter_event             | 0     |
12 | Com_alter_function          | 0     |
13 | Com_alter_instance          | 0     |
14 | Com_alter_procedure         | 0     |
15 | Com_alter_server            | 0     |
16 | Com_alter_table             | 0     |
17 | Com_alter_tablespace        | 0     |
18 | Com_alter_user              | 0     |
19 | Com_analyze                 | 0     |
20 | Com_begin                   | 0     |
21 | Com_binlog                  | 0     |
22 | Com_call_procedure          | 0     |
23 | Com_change_db               | 2     |
24 | Com_change_master           | 0     |
25 | Com_change_repl_filter      | 0     |
26 | Com_check                   | 0     |
27 | Com_checksum                | 0     |
28 | Com_commit                  | 0     |
29 | Com_create_db               | 0     |
30 | Com_create_event            | 0     |
31 | Com_create_function         | 0     |
32 | Com_create_index            | 0     |
  ..............................

比如执行show status like 'slow_queries'查看慢查询次数(黑人问号??什么是慢查询呢?就是通过设置查询时间阈值long_query_time(0-10s)并打开开关show_query_log(1=OFF/0=ON),当超过这个阈值的查询都称之为慢查询,通常用来划分执行SQL效率)

mysql> show status like 'slow_queries';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| Slow_queries  | 0     |
 --------------- ------- 
1 row in set

比如执行show status like 'uptime'查看服务工作时间(即运行时间):

mysql> show status like 'uptime';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| Uptime        | 21645 |
 --------------- ------- 
1 row in set

比如执行show status like 'connections'查看MySQL连接数:

mysql> show status like 'connections';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| Connections   | 6     |
 --------------- ------- 
1 row in set

  通过show [session|gobal] status命令很清楚地看到哪些SQL执行效率不如人意,但是具体是怎么个不如意法,还得继续往下看,使用EXPLAIN命令分析具体的SQL语句

AND dt >= '2010-01-01';

 二、定位效率低的SQL

  上面也提到过慢查询这个概念主要是用来划分效率低的SQL,但是慢查询是在整个查询结束后才记录的,所以光是靠慢查询日志是跟踪不了效率低的SQL。一般有两种方式定位效率低的SQL:

  1、通过慢查询日志查看效率低的SQL语句,慢查询日志是通过show_query_log_file指定存储路径的,里面记录所有超过long_query_time的SQL语句(关于日志的查看,日后再一步研究学习),但是需要慢查询日志的产生是在查询结束后才有的。

  2、通过show processlist命令查看当前MySQL进行的线程,可以看到线程的状态信息

mysql> show processlist;
 ---- ------ ----------------- ------ --------- ------ ---------- ------------------ 
| Id | User | Host            | db   | Command | Time | State    | Info             |
 ---- ------ ----------------- ------ --------- ------ ---------- ------------------ 
|  2 | root | localhost:58377 | NULL | Sleep   | 2091 |          | NULL             |
|  3 | root | localhost:58382 | test | Sleep   | 2083 |          | NULL             |
|  4 | root | localhost:58386 | test | Sleep   | 2082 |          | NULL             |
|  5 | root | localhost:59092 | test | Query   |    0 | starting | show processlist |
 ---- ------ ----------------- ------ --------- ------ ---------- ------------------ 
4 rows in set

  其中主要的是state字段,表示当前SQL语句线程的状态,如Sleeping 表示正在等待客户端发送新请求,Sending data把查询到的data结果发送给客户端等等,具体请看

这个UPDATE语句可以被重写成为下面这样的SELECT语句:

三、 查看分析效率低的SQL

  MYSQL 5.6.3以前只能EXPLAIN SELECT; MYSQL5.6.3以后就可以EXPLAIN SELECT,UPDATE,DELETE,现在我们先创建一个user_table的表,之后分析select* from user where name=''语句

mysql> create table user(id int, name varchar(10),password varchar(32),primary key(id))engine=InnoDB;
Query OK, 0 rows affected

之后插入三条数据:

mysql> insert into user values(1,'Zhangsan',replace(UUID(),'-','')),(2,'Lisi',replace(UUID(),'-','')),(3,'Wangwu',replace(UUID(),'-',''));
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0
mysql> select* from user;
 ---- ---------- ---------------------------------- 
| id | name     | password                         |
 ---- ---------- ---------------------------------- 
|  1 | Zhangsan | 2d7284808e5111e8af74201a060059ce |
|  2 | Lisi     | 2d73641c8e5111e8af74201a060059ce |
|  3 | Wangwu   | 2d73670c8e5111e8af74201a060059ce |
 ---- ---------- ---------------------------------- 
3 rows in set

下面以分析select*from user where name='Lisi'语句为例:

mysql> explain select*from user where name='Lisi';
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
1 row in set

 

下面讲解select_type等常见列的含义的:

(1)select_type:表示SELECT的类型,主要有:

  • SIMPLE:简单表,没有表连接或者子查询
  • PRIMARY:主查询,即最外城的查询
  • UNION:UNION中的第二个或者后面的语句
  • SUBQUERY:子查询中的第一个SELECT

(2)table:结果输出的表

(3)type:表示表的连接类型,性能由好到差为:

  • system:常量表
  • const:单表中最多有一行匹配,比如primary key,unique index
  • eq_ref:多表连接中使用primary key,unique index
  • ref:使用普通索引
  • ref_or_null:与ref类似,但是包含了NULL查询
  • index_merge:索引合并优化
  • unique_subquery:in后面是一个查询主键字段的子查询
  • index_subquery:in后面是非唯一索引字段的子查询
  • range:单表中范围查看,使用like模糊查询
  • index:对于后面每一行都通过查询索引得到数据
  • all:表示全表查询

(3)possible_key:查询时可能使用的索引

(4)key:表示实际使用的索引

(5)key_len:索引字段的长度

(6)rows:查询时实际扫描的行数

(7)Extra:执行情况的说明和描述

(8)partitions:分区数目

(9)filtered:查询过滤的表占的百分比,比如这里查询的记录是name=Lisi的记录,占三条记录的33.3%

SELECT col1, col2

四、 关于索引的优化

FROM table1

1、使用索引优化的举例

  上个例子我们看到到执行explain select*from user where name='Lisi',扫描了3行(全部行数)使用了全表搜索all。如果实际业务中name是经常用到查询的字段(是指经常跟在where后的字段,不是select后的字段)并且数据量很大的情况呢?这时候就需要索引了(索引经常用到where后面的字段比select后面的字段效果更好,或者说就是要使用在where后面的字段上)

增加name前缀索引(这里只是举例,并没有选择最合适的前缀):

mysql> create index index_name on user(name(2));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

执行explain分析

mysql> explain select*from user where name = 'Lisi';
 ---- ------------- ------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
|  1 | SIMPLE      | user  | NULL       | ref  | index_name    | index_name | 9       | const |    1 |      100 | Using where |
 ---- ------------- ------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
1 row in set

  可以看到type变为ref、rows降为1(实际上只要使用了索引都是1),filtered过滤百分比为100%,实际用到的索引为index_name。如果数据量很大的话使用索引就是很好的优化措施,对于如何选择索引,什么时候用索引,我做出了如下总结:

WHERE id1 = 9

2、如何高效使用索引?

  (1) 创建多列索引时,**只要查询条件中用到最左边的列,索引一般都会被用到**

  我们创建一张没有索引的表user_1:

mysql> show create table 
user_1;
 -------- -------------------------------------------------------------------------------------------------------------------------- 
| Table  | Create Table                                                                                                             |
 -------- -------------------------------------------------------------------------------------------------------------------------- 
| user_1 | CREATE TABLE `user_1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
 -------- -------------------------------------------------------------------------------------------------------------------------- 
 1 row in set

 之后同样插入数据:

mysql> select *from user_1;
 ---- ---------- 
| id | name     |
 ---- ---------- 
|  1 | Zhangsan |
|  2 | Lisi     |
 ---- ---------- 
2 rows in set

 创建多列索引index_id_name

mysql> create index index_id_name on user_1(id,name);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

 实验查询explain分析name与id

mysql> explain select * from user_1 where id=1;
 ---- ------------- -------- ------------ ------ --------------- --------------- --------- ------- ------ ---------- ------------- 
| id | select_type | table  | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
 ---- ------------- -------- ------------ ------ --------------- --------------- --------- ------- ------ ---------- ------------- 
|  1 | SIMPLE      | user_1 | NULL       | ref  | index_id_name | index_id_name | 5       | const |    1 |      100 | Using index |
 ---- ------------- -------- ------------ ------ --------------- --------------- --------- ------- ------ ---------- ------------- 
1 row in set

mysql> explain select * from user_1 where name='Lisi';
 ---- ------------- -------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- -------------------------- 
| id | select_type | table  | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
 ---- ------------- -------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- -------------------------- 
|  1 | SIMPLE      | user_1 | NULL       | index | NULL          | index_id_name | 38      | NULL |    2 |       50 | Using where; Using index |
 ---- ------------- -------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- -------------------------- 
1 row in set

  可以看到使用最左列id的时候,rows为1,并且Extra明确使用了index,key的值为id_name_index,type的值为ref,而where不用到id,而是name的话,rows的值为2。filtered为50%,虽然key是index_id_name,但是表明是索引(个人理解,应该不太准确)

  (2) 使用like的查询,只有%不是第一个字符并且%后面是常量的情况下,索引才可能会被使用。

   执行explain select *from user where name like ‘%Li’后type为ALL且key的值为NULL,执行explain select *from user where name like ‘Li%’后key值不为空为index_name。

mysql> explain select*from user where name like '%Li';
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
1 row in set
mysql> explain select*from user where name like 'Li%';
 ---- ------------- ------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- ------------- 
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | user  | NULL       | range | index_name    | index_name | 9       | NULL |    1 |      100 | Using where |
 ---- ------------- ------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- ------------- 
1 row in set

  (3) 如果对打的文本进行搜索,使用全文索引而不是用like ‘%...%’(只有MyISAM支持全文索引)。

  (4) 如果列名是索引,使用column_name is null将使用索引。

mysql> explain select*from user where name is null;
 ---- ------------- ------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
|  1 | SIMPLE      | user  | NULL       | ref  | index_name    | index_name | 9       | const |    1 |      100 | Using where |
 ---- ------------- ------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
1 row in set

mysql> explain select*from user where password
 is null;
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
1 row in set

AND dt >= '2010-01-01';

3、哪些情况下即使有索引也用不到?

  (1) MySQL使用MEMORY/HEAP引擎(使用的HASH索引),并且WHERE条件中不会使用”=”,in等进行索引列,那么不会用到索引(这是关于引擎部分特点,之后会介绍)。

  (2) 用OR分隔开的条件,如果OR前面的条件中的列有索引,而后面的列没有索引,那么涉及到的列索引不会被使用。

  执行命令show index from user可以看出password字段并没有使用任何索引,而id使用了两个索引,但是where id=1 or password='2d7284808e5111e8af74201a060059ce' 导致没有使用id列的primary索引与id_name_index索引

mysql> show index from user;
 ------- ------------ --------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 ------- ------------ --------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
| user  |          0 | PRIMARY       |            1 | id          | A         |           3 | NULL     | NULL   |      | BTREE      |         |               |
| user  |          1 | index_name    |            1 | name        | A         |           3 |        2 | NULL   | YES  | BTREE      |         |               |
| user  |          1 | id_name_index |            1 | id          | A         |           3 | NULL     | NULL   |      | BTREE      |         |               |
| user  |          1 | id_name_index |            2 | name        | A         |           3 | NULL     | NULL   | YES  | BTREE      |         |               |
 ------- ------------ --------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
4 rows in set

mysql> explain select*from user where id=1 or password='2d7284808e5111e8af74201a060059ce';
 ---- ------------- ------- ------------ ------ ----------------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------ ----------------------- ------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | user  | NULL       | ALL  | PRIMARY,id_name_index | NULL | NULL    | NULL |    3 |    55.56 | Using where |
 ---- ------------- ------- ------------ ------ ----------------------- ------ --------- ------ ------ ---------- ------------- 
1 row in set

  (3) 不是用到复合索引中的第一列即最左边的列的话,索引就不起作用(上面已经介绍)。

  (4) 如果like是以%开头的(上面已经介绍)

  (5) 如果列类型是字符串,那么where条件中字符常量值不用’’引号引起来的话,那就不会失去索引效果,这是因为MySQL会把输入的常量值进行转换再使用索引。

  select * from user_1 where name =250,其中name的索引为name_index,并且是varchar字符串类型,但是并没有将250用引号变成’250’,那么explain之后的ref仍然为NULL,rows为3

mysql> show index from user_1;
 -------- ------------ --------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
| Table  | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 -------- ------------ --------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
| user_1 |          1 | index_id_name |            1 | id          | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               |
| user_1 |          1 | index_id_name |            2 | name        | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               |
| user_1 |          1 | name_index    |            1 | name        | A         |           3 |        5 | NULL   | YES  | BTREE      |         |               |
 -------- ------------ --------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
3 rows in set

mysql> explain select*from user_1 where name=250;
 ---- ------------- -------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- -------------------------- 
| id | select_type | table  | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
 ---- ------------- -------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- -------------------------- 
|  1 | SIMPLE      | user_1 | NULL       | index | name_index    | index_id_name | 38      | NULL |    3 |    33.33 | Using where; Using index |
 ---- ------------- -------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- -------------------------- 
1 row in set

mysql> explain select*from user_1 where name='250';
 ---- ------------- -------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
 ---- ------------- -------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
|  1 | SIMPLE      | user_1 | NULL       | ref  | name_index    | name_index | 18      | const |    1 |      100 | Using where |
 ---- ------------- -------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
1 row in set

 

在5.6.10版本里面,是可以直接对dml语句进行explain分析操作的.

4、查看索引的使用情况

执行show status like ‘Handler_read%’可以看到一个值Handler_read_key,它代表一行被索引值读的次数,如果值很低说明增加索引得到的性能改善不高,因为索引并不经常使用。

mysql> show status like 'Handler_read%' ;
 ----------------------- ------- 
| Variable_name         | Value |
 ----------------------- ------- 
| Handler_read_first    | 3     |
| Handler_read_key      | 5     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 20    |
 ----------------------- ------- 
7 rows in set

(1)Handler_read_first:索引中第一条被读的次数。如果较高,它表示服务器正执行大量全索引扫描;

(2)Handler_read_key:如果索引正在工作,这个值代表一个行被索引值读的次数,如果值越低,表示索引得到的性能改善不高,因为索引不经常使用。

(3)Handler_read_next :按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列,该值增加。

(4)Handler_read_prev:按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。

(5)Handler_read_rnd :根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高,意味着运行效率低,应该建立索引来补救。

(6)Handler_read_rnd_next:在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

   注:以上6点来自于网络总结,其中比较重要的两个参数是Handler_read_key与Handler_read_rnd_next。

MySQL 优化器是基于开销来工作的,它并不提供任何的QEP的位置。这意味着QEP 是在每条SQL 语句执行的时候动态地计

算出来的。在MySQL 存储过程中的SQL 语句也是在每次执行时计算QEP 的。存储过程缓存仅仅解析查询树。

2 各列详解

MySQL EXPLAIN命令能够为SQL语句中的每个表生成以下信息:

mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176G;

  ********************* 1. row ***********************

  id: 1

  select_type: SIMPLE

  table: inventory

  type: ALL

  possible_keys: NULL

  key: NULL

  key_len: NULL

  ref: NULL

  rows: 787338

  Extra: Using where

  

这个QEP 显示没有使用任何索引(也就是全表扫描)并且处理了大量的行来满足查询。对同样一条SELECT 语句,一个优化过的QEP 如下所示:

  ********************* 1. row ***********************

  id: 1

  select_type: SIMPLE

  table: inventory

  type: ref

  possible_keys: item_id

  key: item_id

  key_len: 4

  ref: const

  rows: 1

  Extra:

在这个QEP 中,我们看到使用了一个索引,且估计只有一行数据将被获取。

QEP 中每个行的所有列表如下所示:

 id

 select_type

 table

 partitions(这一列只有在EXPLAIN PARTITIONS 语法中才会出现)

 possible_keys

 key

 key_len

 ref

 rows

 filtered(这一列只有在EXPLAINED EXTENDED 语法中才会出现)

 Extra

这些列展示了SELECT 语句对每一个表的QEP。一个表可能和一个物理模式表或者在SQL 执行时生成的内部临时表(例如从子查询或者合并操作会产生内部临时表)相关联。

 

2.1 key

 key 列指出优化器选择使用的索引。一般来说SQL 查询中的每个表都仅使用一个索引。也存在索引合并的少数例外情况,如给定表上用到了两个或者更多索引。

 下面是QEP 中key 列的示例:

 key: item_id

 key: NULL

 key: first, last

 SHOW CREATE TABLE <table>命令是最简单的查看表和索引列细节的方式。和key 列相关的列还包括possible_keys、rows 以及key_len。

2.2 ROWS

 rows 列提供了试图分析所有存在于累计结果集中的行数目的MySQL 优化器估计值。QEP 很容易描述这个很困难的统计量。

 查询中总的读操作数量是基于合并之前行的每一行的rows 值的连续积累而得出的。这是一种嵌套行算法。

 

 以连接两个表的QEP 为例。通过id=1 这个条件找到的第一行的rows 值为1,这等于对第一个表做了一次读操作。第二行是

 通过id=2 找到的,rows 的值为5。这等于有5 次读操作符合当前1 的积累量。参考两个表,读操作的总数目是6。在另一个QEP

 中,第一rows 的值是5,第二rows 的值是1。这等于第一个表有5 次读操作,对5个积累量中每个都有一个读操作。因此两个表

 总的读操作的次数是10(5 5)次。

 

 最好的估计值是1,一般来说这种情况发生在当寻找的行在表中可以通过主键或者唯一键找到的时候。

 在下面的QEP 中,外面的嵌套循环可以通过id=1 来找到,其估计的物理行数是1。第二个循环处理了10行。

 

 

 ********************* 1. row ***********************

 id: 1

 select_type: SIMPLE

 table: p

 type: const

 possible_keys: PRIMARY

 key: PRIMARY

 key_len: 4

 ref: const

 rows: 1

 Extra:

 ********************* 2. row ***********************

 id: 1

 select_type: SIMPLE

 table: c

 type: ref

 possible_keys: parent_id

 key: parent_id

 key_len: 4

 ref: const

 rows: 10

 Extra:

 

 可以使用SHOW STATUS 命令来查看实际的行操作。这个命令可以提供最佳的确认物理行操作的方式。请看下面的示例:

 mysql> SHOW SESSION STATUS LIKE 'Handler_read%';

  ----------------------- -------

  | Variable_name         | Value |

  ----------------------- -------

  | Handler_read_first    | 0     |

  | Handler_read_key      | 0     | 

  | Handler_read_last     | 0     |

  | Handler_read_next     | 0     |

  | Handler_read_prev     | 0     |

  | Handler_read_rnd      | 0     |

  | Handler_read_rnd_next | 11    |

  ----------------------- -------

  7 rows in set (0.00 sec)

  

 在下一个QEP 中,通过id=1 找到的外层嵌套循环估计有160行。第二个循环估计有1 行。

 ********************* 1. row ***********************

  id: 1

  select_type: SIMPLE

  table: p

  type: ALL

  possible_keys: NULL

  key: NULL

  key_len: NULL

  ref: NULL

  rows: 160

  Extra:

 ********************* 2. row ***********************

  id: 1

  select type: SIMPLE

  table: c

  type: ref

  possible_keys: PRIMARY,parent_id

  key: parent_id

  key_len: 4

  ref: test.p.parent_id

  rows: 1

  Extra: Using where

 

 通过SHOW STATUS 命令可以查看实际的行操作,该命令表明物理读操作数量大幅增加。请看下面的示例:

 mysql> SHOW SESSION STATUS LIKE 'Handler_read%';

  -------------------------------------- ---------

 | Variable_name | Value |

  -------------------------------------- ---------

 | Handler_read_first | 1 |

 | Handler_read_key | 164 |

 | Handler_read_last | 0 |

 | Handler_read_next | 107 |

 | Handler_read_prev | 0 |

 | Handler_read_rnd | 0 |

 | Handler_read_rnd_next | 161 |

  -------------------------------------- ---------

 相关的QEP 列还包括key列。

 

 2.3 possible_keys

 possible_keys 列指出优化器为查询选定的索引。

 一个会列出大量可能的索引(例如多于3 个)的QEP 意味着备选索引数量太多了,同时也可能提示存在一个无效的单列索引。

 可以用第2 章详细介绍过的SHOW INDEXES 命令来检查索引是否有效且是否具有合适的基数。

 为查询确定QEP 的速度也会影响到查询的性能。如果发现有大量的可能的索引,则意味着这些索引没有被使用到。

 相关的QEP 列还包括key 列。

 

 2.4 key_len

 key_len 列定义了用于SQL 语句的连接条件的键的长度。此列值对于确认索引的有效性以及多列索引中用到的列的数目很重要。

 此列的一些示例值如下所示:

 

 此列的一些示例值如下所示:

 key_len: 4 // INT NOT NULL

 key_len: 5 // INT NULL

 key_len: 30 // CHAR(30) NOT NULL

 key_len: 32 // VARCHAR(30) NOT NULL

 key_len: 92 // VARCHAR(30) NULL CHARSET=utf8

 

 从这些示例中可以看出,是否可以为空、可变长度的列以及key_len 列的值只和用在连接和WHERE 条件中的索引的列

 有关。索引中的其他列会在ORDER BY 或者GROUP BY 语句中被用到。下面这个来自于著名的开源博客软件WordPress 的表展示了

 如何以最佳方式使用带有定义好的表索引的SQL 语句:

 CREATE TABLE `wp_posts` (

  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `post_status` varchar(20) NOT NULL DEFAULT 'publish' ,

  `post_type` varchar(20) NOT NULL DEFAULT 'post',

  PRIMARY KEY (`ID`),

  KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)

 ) DEFAULT CHARSET=utf8

 

  CREATE TABLE `wp_posts` (

  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `post_status` varchar(20) NOT NULL DEFAULT 'publish' ,

  `post_type` varchar(20) NOT NULL DEFAULT 'post',

  PRIMARY KEY (`ID`),

  KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)

 ) DEFAULT CHARSET=utf8

 

 这个表的索引包括post_type、post_status、post_date 以及ID列。下面是一个演示索引列用法的SQL 查询:

 EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type='post' AND post_date > '2010-06-01';

 

 这个查询的QEP 返回的key_len 是62。这说明只有post_type列上的索引用到了(因为(20×3) 2=62)。尽管查询在WHERE 语句

 中使用了post_type 和post_date 列,但只有post_type 部分被用到了。其他索引没有被使用的原因是MySQL 只能使用定义索引的

 最左边部分。为了更好地利用这个索引,可以修改这个查询来调整索引的列。请看下面的示例:

 mysql> EXPLAIN SELECT ID, post_title

 -> FROM wp_posts

 -> WHERE post_type='post'

 -> AND post_status='publish'

 -> AND post_date > '2010-06-01';

 

 在SELECT查询的添加一个post_status 列的限制条件后,QEP显示key_len 的值为132,这意味着post_type、post_status、post_date

 三列(62 62 8,(20×3) 2,(20×3) 2,8)都被用到了。此外,这个索引的主码列ID 的定义是使用MyISAM 存储索

 引的遗留痕迹。当使用InnoDB 存储引擎时,在非主码索引中包含主码列是多余的,这可以从key_len 的用法看出来。

 相关的QEP 列还包括带有Using index 值的Extra 列。

 

 2.5 table

 table 列是EXPLAIN 命令输出结果中的一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生临时表

 的标识符,如派生表、子查询或集合。下面是QEP 中table 列的一些示例:

 table: item

 table: <derivedN>

 table: <unionN,M>

 表中N 和M 的值参考了另一个符合id 列值的table 行。相关的QEP 列还有select_type

 2.6 select_type

 select_type 列提供了各种表示table 列引用的使用方式的类型。最常见的值包括SIMPLE、PRIMARY、DERIVED 和UNION。其他可能

 的值还有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION 以及UNCACHEABLE QUERY。

 1. SIMPLE

 对于不包含子查询和其他复杂语法的简单查询,这是一个常 见的类型。

 2. PRIMARY

 这是为更复杂的查询而创建的首要表(也就是最外层的表)。这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。

 

 3. DERIVED

 当一个表不是一个物理表时,那么就被叫做DERIVED。下面的SQL 语句给出了一个QEP 中DERIVED select-type 类型的

 示例:

 mysql> EXPLAIN SELECT MAX(id)

 -> FROM (SELECT id FROM users WHERE first = 'west') c;

 4. DEPENDENT SUBQUERY

 这个select-type 值是为使用子查询而定义的。下面的SQL语句提供了这个值:

 mysql> EXPLAIN SELECT p.*

 -> FROM parent p

 -> WHERE p.id NOT IN (SELECT c.parent_id FROM child c);

 

 5. UNION

 这是UNION 语句其中的一个SQL 元素。

 6. UNION RESULT

 这是一系列定义在UNION 语句中的表的返回结果。当select_type 为这个值时,经常可以看到table 的值是<unionN,M>,

 这说明匹配的id 行是这个集合的一部分。下面的SQL产生了一个UNION和UNION RESULT select-type:

 mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.val

 LIKE 'a%'

 -> UNION

 -> SELECT p.* FROM parent p WHERE p.id > 5;

 2.7  partitions

  partitions 列代表给定表所使用的分区。这一列只会在EXPLAIN

  PARTITIONS 语句中出现。

 2.8 Extra

 Extra 列提供了有关不同种类的MySQL 优化器路径的一系列

 额外信息。Extra 列可以包含多个值,可以有很多不同的取值,并

 且这些值还在随着MySQL 新版本的发布而进一步增加。下面给

 出常用值的列表。你可以从下面的地址找到更全面的值的列表:

 

 1. Using where

 这个值表示查询使用了where 语句来处理结果——例如执行

 全表扫描。如果也用到了索引,那么行的限制条件是通过获取必

 要的数据之后处理读缓冲区来实现的。

 2. Using temporary

 这个值表示使用了内部临时(基于内存的)表。一个查询可能

 用到多个临时表。有很多原因都会导致MySQL 在执行查询期间

 创建临时表。两个常见的原因是在来自不同表的列上使用了

 DISTINCT,或者使用了不同的ORDER BY 和GROUP BY 列。

 

 可以强制指定一个临时表使用基于磁盘的MyISAM 存储引

 擎。这样做的原因主要有两个:

  内部临时表占用的空间超过min(tmp_table_size,max_

 heap_table_size)系统变量的限制

  使用了TEXT/BLOB 列

 3. Using filesort

 这是ORDER BY 语句的结果。这可能是一个CPU 密集型的过程。

 可以通过选择合适的索引来改进性能,用索引来为查询结果排序。详细过程请参考第4 章。

 4. Using index

 这个值重点强调了只需要使用索引就可以满足查询表的要求,不需要直接访问表数据。请参考第5 章的详细示例来理解这

 个值。

 5. Using join buffer

 这个值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。

 如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进性能。

 6. Impossible where

 这个值强调了where 语句会导致没有符合条件的行。请看下面的示例:

 mysql> EXPLAIN SELECT * FROM user WHERE 1=2;

 7. Select tables optimized away

 这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行。请看下面的示例:

 8. Distinct

 这个值意味着MySQL 在找到第一个匹配的行之后就会停止搜索其他行。

 9. Index merges

 当MySQL 决定要在一个给定的表上使用超过一个索引的时候,就会出现以下格式中的一个,详细说明使用的索引以及合并的类型。

  Using sort_union(...)

  Using union(...)

  Using intersect(...)

 2.9 id

 id 列是在QEP 中展示的表的连续引用。

 2.10 ref

 ref 列可以被用来标识那些用来进行索引比较的列或者常量。

 2.11 filtered

 filtered 列给出了一个百分比的值,这个百分比值和rows 列的值一起使用,可以估计出那些将要和QEP 中的前一个表进行连

 接的行的数目。前一个表就是指id 列的值比当前表的id 小的表。这一列只有在EXPLAIN EXTENDED 语句中才会出现。

 2.12 type

 type 列代表QEP 中指定的表使用的连接方式。下面是最常用的几种连接方式:

  const 当这个表最多只有一行匹配的行时出现system 这是const 的特例,当表只有一个row 时会出现

  eq_ref 这个值表示有一行是为了每个之前确定的表而读取的

  ref 这个值表示所有具有匹配的索引值的行都被用到

  range 这个值表示所有符合一个给定范围值的索引行都被用到

  ALL 这个值表示需要一次全表扫描其他类型的值还有fulltext 、ref_or_null 、index_merge 、unique_subquery、index_subquery 以及index。

 

 3 解释EXPLAIN 输出结果

 理解你的应用程序(包括技术和实现可能性)和优化SQL 语句同等重要。下面给出一个从父子关系中获取孤立的父辈记录的商

 业需求的例子。这个查询可以用三种不同的方式构造。尽管会产生相同的结果,但QEP 会显示三种不同的路径。

 mysql> EXPLAIN SELECT p.*

 -> FROM parent p

 -> WHERE p.id NOT IN (SELECT c.parent_id FROM child

 c)G

 ********************* 1. row ***********************

 id: 1

 select type: PRIMARY

 table: p

 type: ALL

 possible_keys: NULL

 key: NULL

 key_len: NULL

 ref: NULL

 rows: 160

 Extra: Using where

 ********************* 2. row ***********************

 id: 2

 select_type: DEPENDENT SUBQUERY

 table: c

 type: index_subquery

 possible_keys: parent_id

 key: parent_id

 key_len: 4

 ref: func

 rows: 1

 Extra: Using index

 2 rows in set (0.00 sec)

 

 EXPLAIN SELECT p.* FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE c.child_id IS NULLG

 ********************* 1. row ***********************

 id: 1

 select_type: SIMPLE

 table: p

 type: ALL

 possible_keys: NULL

 key: NULL

 key_len: NULL

 ref: NULL

 rows: 160

 Extra:

 ********************* 2. row ***********************

 id: 1

 select_type: SIMPLE

 table: c

 type: ref

 possible_keys: parent_id

 key: parent_id

 key_len: 4

 ref: test.p.id

 rows: 1

 Extra: Using where; Using index; Not exists

 2 rows in set (0.00 sec)

 

EXPLAIN命令详解学习 MySQL的EXPLAIN命令用于SQL语句的查询执行计划(QEP)。这条命令的输出结果能够让我们了解MySQL 优化器是如何执行 SQL 语...

编辑:澳门新葡8522最新网站 本文来源:EXPLAIN命令详解学习,常用的优化步骤

关键词: www8029com