MySQL学习笔记之分析查询语句explain

news/2024/3/29 20:28:22/文章来源:https://blog.csdn.net/qq_37475168/article/details/131735865

文章目录

  • 概述
  • 数据准备
    • 建表
    • 开启允许创建函数
    • 创建函数和存储过程
    • 调用存储过程
  • explain各列说明
    • table
    • id
      • 简单select
      • 多表连接
      • 子查询
      • union和union all
    • select_type
      • simple
      • primary
      • subquery
      • dependent subquery
      • dependent union
      • derived
      • materialized
    • partition
    • type
      • system
      • const
      • eq_ref
      • ref
      • ref_or_null
      • index_merge
      • unique_subquery
      • index_subquery
      • range
      • index
      • all
    • possible_keys和key
    • key_len
      • varchar可空单列索引举例
      • varchar可空联合索引举例
      • int非空举例
      • int可空举例
    • ref
      • 普通索引的等值匹配
      • 多表连接时进行的等值匹配
      • 多表连接时对被连接表的索引列使用函数
    • rows
    • filtered
      • 单表查询
      • 多表连接
    • extra
      • no table used
      • impossible where
      • using where
      • no matching min/max row
      • using index
      • using index condition
      • using join buffer (block nested loop)
      • not exists
      • using interset(...)/union(...)/sort_union(...)
      • zero limit
      • using filesort
      • using temporary
    • 小结
  • 两种输出格式
    • 传统格式
    • JSON格式
  • show warnings

概述

explain语句(或describe语句,等效)用来展示某个SQL的具体执行方式,包括表的读取顺序、数据读取时的类型、哪些索引可以被使用、哪个索引被实际使用、表之间的引用以及每张表有多少行被优化器查询。

官网介绍:https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

基本语法:explain/describe SQL语句,本文以查询语句为例,记录explain语句的学习过程。

输出格式:

列名含义
id一个SQL中,每个select关键字都有一个唯一ID
select_typeselect关键字对应的查询类型
table表名
partitions匹配的分区信息
type针对单表的查询方法
possible_keys可能用到的索引候选
key实际使用的索引
key_len实际使用的索引长度
ref当使用索引列进行等值查询时,与索引列进行等值匹配的对象信息
rows预估要读取的记录条数
filtered某个表经过搜索条件过滤后,剩余记录所占全部记录的百分比
extra额外信息

数据准备

建表

CREATE TABLE s1 (id INT AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY (id),INDEX idx_key1 (key1),UNIQUE INDEX idx_key2 (key2),INDEX idx_key3 (key3),INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;CREATE TABLE s2 (id INT AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY (id),INDEX idx_key1 (key1),UNIQUE INDEX idx_key2 (key2),INDEX idx_key3 (key3),INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

开启允许创建函数

set global log_bin_trust_function_creators=1;

创建函数和存储过程

创建用来产生随机数据的函数和存储过程:

DELIMITER //
CREATE FUNCTION rand_string1(n INT)RETURNS VARCHAR(255) 
BEGINDECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLARE return_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 0;WHILE i < n DOSET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));SET i = i + 1;END WHILE;RETURN return_str;
END //
DELIMITER ;DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO s1 VALUES((min_num + i),rand_string1(6),(min_num + 30 * i + 5),rand_string1(6),rand_string1(10),rand_string1(5),rand_string1(10),rand_string1(10));UNTIL i = max_numEND REPEAT;
COMMIT;
END //
DELIMITER ;DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO s2 VALUES((min_num + i),rand_string1(6),(min_num + 30 * i + 5),rand_string1(6),rand_string1(10),rand_string1(5),rand_string1(10),rand_string1(10));UNTIL i = max_numEND REPEAT;COMMIT;
END //
DELIMITER ;

调用存储过程

调用存储过程来产生数据:

CALL insert_s1(10001, 10000);CALL insert_s2(10001, 10000);

explain各列说明

table

不管select语句多复杂,里面包含了多少张表,到最后也是要对每张表进行查询的。因此explain语句输出的每条记录都对应着某张单表的访问方法,该记录的table字段代表着该表的表名或简称。

id

简单select

mysql> explain select * from s1 where key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

多表连接

mysql> explain select * from s1 inner join s2 on s1.key1 = s2.key1 where s1.common_field = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL                 | 9895 |    10.00 | Using where |
|  1 | SIMPLE      | s2    | NULL       | ref  | idx_key1      | idx_key1 | 303     | review_mysql.s1.key1 |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

可见多表连接里包含了两条语句,若多条语句的id相等,则认为这些语句为同一组,从上往下顺序执行。

子查询

mysql> explain select * from s1 where key1 in (select key2 from s2 where s2.common_field = 'a');
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                  | rows | filtered | Extra                              |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+------------------------------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL                 | 9895 |   100.00 | Using where                        |
|  1 | SIMPLE      | s2    | NULL       | ref  | idx_key2      | idx_key2 | 5       | review_mysql.s1.key1 |    1 |    10.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

union和union all

mysql> explain select * from s1 union select * from s2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | s1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL            |
|  2 | UNION        | s2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)mysql> explain select * from s1 union all select * from s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL  |
|  2 | UNION       | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

如果多个查询语句的ID不同,则ID越大者越先执行。多个查询ID意味着有多趟查询,我们要让查询趟数越少越好。

select_type

描述
simple简单查询,没使用union或子查询
primary最外层的select
unionunion语句中的非第一个select
union resultunion的结果
subquery子查询中的第一个select
dependent subquery子查询中的第一个select,且该select依赖于外部查询
dependent unionunion语句中的非第一个select,且该select依赖于外部查询
derived某个查询存在派生表时,派生表对应的查询即为derived查询
materialized物化子查询
uncacheable subquery查询结果不能被缓存,且必须为外部查询的每一行进行重新计算的子查询
uncacheable unionunion语句中属于uncacheable subquery(不可缓存的子查询)的非第一个select

接下来对其中每一项进行举例,不过后两项不常用,在此略过。

simple

单表查询是典型的simple

mysql> explain select * from s1 where key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

多表连接也算是simple

mysql> explain select * from s1 inner join s2 on s1.key1 = s2.key1 where s1.common_field = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL                 | 9895 |    10.00 | Using where |
|  1 | SIMPLE      | s2    | NULL       | ref  | idx_key1      | idx_key1 | 303     | review_mysql.s1.key1 |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

如果子查询可以被优化器重写为多表连接,则该子查询也算是simple

mysql> explain select * from s1 where key1 in (select key2 from s2 where s2.common_field = 'a');
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                  | rows | filtered | Extra                              |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+------------------------------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL                 | 9895 |   100.00 | Using where                        |
|  1 | SIMPLE      | s2    | NULL       | ref  | idx_key2      | idx_key2 | 5       | review_mysql.s1.key1 |    1 |    10.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

上例中子查询是不相关子查询,因此是simple查询。

primary

典型的场景就是union

mysql> explain select * from s1 union select * from s2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | s1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL            |
|  2 | UNION        | s2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

其中也包含了union类型和union result类型的查询

subquery

如果子查询不能被优化器重写为多表连接,且为不相关子查询,则对应主查询为primary,子查询为subquery

mysql> explain select * from s1 where key1 in (select key1 from s2) or key3 = 'a';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | s1    | NULL       | ALL   | idx_key3      | NULL     | NULL    | NULL | 9895 |   100.00 | Using where |
|  2 | SUBQUERY    | s2    | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9895 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

上例说明select * from s1 where key1 in (select key1 from s2) or key3 = 'a';没有被写成多表连接,因为主查询的where中多了一个or

dependent subquery

由于在子查询的where中将主查询的过滤字段作为子查询的过滤字段(即相关子查询),且不能被重写为多表连接,因此该子查询的类型为dependent subquery

mysql> explain select * from s1 where key1 in (select key1 from s2 where s1.key2 = s2.key2) or key3 = 'a';
+----+--------------------+-------+------------+------+-------------------+----------+---------+----------------------+------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys     | key      | key_len | ref                  | rows | filtered | Extra       |
+----+--------------------+-------+------------+------+-------------------+----------+---------+----------------------+------+----------+-------------+
|  1 | PRIMARY            | s1    | NULL       | ALL  | idx_key3          | NULL     | NULL    | NULL                 | 9895 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | s2    | NULL       | ref  | idx_key2,idx_key1 | idx_key2 | 5       | review_mysql.s1.key2 |    1 |    10.00 | Using where |
+----+--------------------+-------+------------+------+-------------------+----------+---------+----------------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

dependent union

如果子查询里的union中非第一个select语句的where,将主查询的where过滤字段作为自己的过滤字段,则该子查询uniondependent union

mysql> explain select * from s1 where key1 in (select key1 from s2 where s2.key1 = 'a' union select key1 from s1 where s1.key1 = 'b');
+----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
| id | select_type        | table      | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra                    |
+----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY            | s1         | NULL       | ALL  | NULL          | NULL     | NULL    | NULL  | 9895 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | s2         | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | Using where; Using index |
|  3 | DEPENDENT UNION    | s1         | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | Using where; Using index |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL  | NULL          | NULL     | NULL    | NULL  | NULL |     NULL | Using temporary          |
+----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
4 rows in set, 1 warning (0.00 sec)

这里子查询里面的两部分都是相关的(相关子查询、相关联合),是因为优化器将我们的SQL重构成了相关子查询。

derived

如果需要将子查询的结果固定成一张表,则该子查询就是派生表:

mysql> explain select * from (select key1, count(*) as c from s1 group by key1) as derived_s1;
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL     | NULL    | NULL | 9895 |   100.00 | NULL        |
|  2 | DERIVED     | s1         | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9895 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

materialized

如果某子查询经过优化后成为一张只包含子查询select字段的表,则该子查询就是物化子查询:

mysql> explain select * from s1 where key1 in (select key1 from s2);
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref                  | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE       | s1          | NULL       | ALL    | idx_key1      | NULL       | NULL    | NULL                 | 9895 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 303     | review_mysql.s1.key1 |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | s2          | NULL       | index  | idx_key1      | idx_key1   | 303     | NULL                 | 9895 |   100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

partition

表示分区表的分区命中情况,非分区表的partition字段均为null

type

场景
system表中只有一条记录,且该表使用的存储引擎是精确统计数据
const根据主键或唯一(unique)索引列和常数进行等值匹配
eq_ref表连接时,被连接表是通过主键或唯一索引进行连接的
ref普通索引和常量进行等值匹配,注意不能有类型转换
fulltext全文索引
ref_or_null普通索引和常量进行等值匹配,并且where子句中有null判断,注意不能有类型转换
index_merge需要将两个普通单列索引合并成一个索引时
unique_subquery针对一些in子查询,优化器决定将in子查询转换成exist子查询,并且子查询中用到了主键进行等值匹配
index_subquery一般在update或delete中显示,且子查询中使用到了普通索引
range使用索引进行范围查询
index索引覆盖+全表扫描,特别是使用联合索引时,没有按照定义联合索引时规定的顺序定义where子句时
all经过优化后,相当于没有索引时的全表遍历的查询

上表中,越往下的类型查询效率越低。

system

这是性能最高的查询:

mysql> create table test_system_type(id int) engine = myisam;
Query OK, 0 rows affected (0.01 sec)mysql> insert into test_system_type (id) values(1);
Query OK, 1 row affected (0.00 sec)mysql> explain select * from test_system_type;
+----+-------------+------------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table            | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test_system_type | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+------------------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

const

mysql> explain select * from s1 where id = 10005;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql> explain select * from s1 where key2 = 10066;
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | const | idx_key2      | idx_key2 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

eq_ref

mysql> explain select * from s1 inner join s2 on s1.id = s2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL               | 9895 |   100.00 | NULL  |
|  1 | SIMPLE      | s2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | review_mysql.s1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

ref

mysql> explain select * from s1 where key3 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key3      | idx_key3 | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

ref_or_null

mysql> explain select * from s1 where key3 = 'a' or key3 = null;
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | s1    | NULL       | ref_or_null | idx_key3      | idx_key3 | 303     | const |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index_merge

mysql> explain select * from s1 where key3 = 'a' or key1 = 'b';
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                       |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | s1    | NULL       | index_merge | idx_key1,idx_key3 | idx_key3,idx_key1 | 303,303 | NULL |    2 |   100.00 | Using union(idx_key3,idx_key1); Using where |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)

ref_or_null要求多个单列索引在where中的关系为并集关系。

unique_subquery

mysql> explain select * from s1 where key2 in (select id from s2 where s1.key1 = s2.key1) or key3 = 'a';
+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type            | possible_keys    | key     | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | s1    | NULL       | ALL             | idx_key3         | NULL    | NULL    | NULL | 9895 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | s2    | NULL       | unique_subquery | PRIMARY,idx_key1 | PRIMARY | 4       | func |    1 |    10.00 | Using where |
+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

index_subquery

mysql> explain update s1 set key1 = 'a' where key1 in (select key2 from s2);
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type        | table | partitions | type           | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | UPDATE             | s1    | NULL       | index          | NULL          | PRIMARY  | 4       | NULL | 9895 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | s2    | NULL       | index_subquery | idx_key2      | idx_key2 | 5       | func |    1 |   100.00 | Using where; Using index |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+--------------------------+
2 rows in set (0.00 sec)mysql> explain delete from s1 where key1 in (select key2 from s2);
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type        | table | partitions | type           | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | DELETE             | s1    | NULL       | ALL            | NULL          | NULL     | NULL    | NULL | 9895 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | s2    | NULL       | index_subquery | idx_key2      | idx_key2 | 5       | func |    1 |   100.00 | Using where; Using index |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+------+----------+--------------------------+
2 rows in set (0.00 sec)

range

mysql> explain select * from s1 where key1 in ('a', 'b');
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)mysql> explain select * from s1 where key1 > 'a' and key1 < 'e';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL | 1565 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index

mysql> explain select key_part1 from s1 where key_part3 = 'a';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | s1    | NULL       | index | NULL          | idx_key_part | 909     | NULL | 9895 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

all

mysql> explain select * from s1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

possible_keys和key

前者表示执行该查询可能用到的索引候选集合,后者表示执行该查询时实际用的索引(没用到即为NULL,且不一定为候选集合的子集),前者越少越好,因为涉及候选项的成本计算。

key_len

key_len表示实际用到的索引长度(byte)。对于同一个索引,值越大越好。针对字符串类型char和varchar,单列索引的计算公式如下:

类型是否非空计算公式
varchar(n)n * len_charset + 1 + 2
varchar(n)n * len_charset + 2
char(n)n * len_charset + 1
char(n)n * len_charset

len_charset取决于使用的字符集:utf8、gbk和latin1分别对应的值为3、2、1。
如果使用的是联合索引,则key_len为联合索引中每个单列字段的key_len之和。

varchar可空单列索引举例

mysql> explain select * from s1 where key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

其中,key_len = 100 * 3 + 1 + 2 = 303。

varchar可空联合索引举例

mysql> explain select * from s1 where key_part1 = 'a' and key_part2 = 'b';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key_part  | idx_key_part | 606     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

key_len = (100 * 3 + 1 + 2) + (100 * 3 + 1 + 2) = 606

int非空举例

mysql> explain select * from s1 where id = 10005;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

key_len为4,是因为key1是主键,所以是非空的,因此索引长度就是int的长度。

int可空举例

mysql> explain select * from s1 where key2 = 11326;
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | const | idx_key2      | idx_key2 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

多出来的1就是因为key2是可空的。

ref

当使用索引列进行等值查询时,与索引列进行等值匹配的对象信息。如果等值查询匹配的是常数,则索引列的类型为ref;如果等值匹配用到了多表连接,则被连接表的索引列类型为eq_ref

普通索引的等值匹配

mysql> explain select * from s1 where key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

多表连接时进行的等值匹配

mysql> explain select * from s1 join s2 on s1.id = s2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL               | 9895 |   100.00 | NULL  |
|  1 | SIMPLE      | s2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | review_mysql.s1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

可见被连接表的索引列对应的类型就是eq_ref

多表连接时对被连接表的索引列使用函数

mysql> explain select * from s1 join s2 on upper(s1.id) = s2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 9895 |   100.00 | NULL        |
|  1 | SIMPLE      | s2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | func |    1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

对被连接表的索引列使用函数后,除了被连接表的索引列类型时eq_ref外,引用源的ref也变成了func

rows

要读取的行数预计值,越小越好。

举例:

mysql> explain select * from s1 where key1 > 'v';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | idx_key1      | NULL | NULL    | NULL | 9895 |    19.01 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

filtered

预计过滤后,剩余数据占所有数据百分比。

单表查询

mysql> explain select * from s1 where key1 > 'v' and common_field = 'a';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | idx_key1      | NULL | NULL    | NULL | 9895 |     1.90 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

结合rows字段,filtered的含义是9895条数据中,符合where条件的数据占比为1.9%

多表连接

filtered更多的关注点在多表连接,连接表的filtered * rows决定了被连接表要连接多少次:

mysql> explain select * from s1 join s2 on s1.id = s2.id where s1.common_field = 'a';
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL               | 9895 |    10.00 | Using where |
|  1 | SIMPLE      | s2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | review_mysql.s1.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

上例中,连接表的rows * filtered = 989.5,即被连接表预计要被连接989次。

extra

extra字段提供了一些不太适合放到其他字段中显示,但却对执行SQL非常重要的信息,常见的字段值如下表所示:

说明
no table used没有使用表
impossible wherewhere条件不可能成立
using where使用了where语句,且没有使用索引
no matching min/max row使用了最小/最大聚合函数,但没有匹配where条件的数据
using index使用了覆盖索引,即select的字段可以被where中的索引字段包含,即不需要回表了
using index conditionwhere中出现了索引列,且需要进行索引条件下推优化
using join buffer (block nested loop)多表连接中,被连接表不能通过索引加速检索时,MySQL会为其分配一块叫做join buffer的缓存加快查询速率
not exists左外连接时,where语句要求被连接表中某个非空列的列值为空
using interset(…)/union(…)/sort_union(…)准备使用interset/union/sort union的方式合并索引进行查询,括号中的…即要合并的索引名
zero limit使用了limit子句,且限制条数为0
using filesort对varchar类型字段进行排序,且该这种排序用不到索引
using temporary使用了临时表
其他忽略

no table used

mysql> explain select 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

impossible where

mysql> explain select * from s1 where 1 > 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)

using where

mysql> explain select * from s1 where id > 1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 4947 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

no matching min/max row

mysql> explain select min(id) from s1 where id < 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                   |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No matching min/max row |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
1 row in set, 1 warning (0.00 sec)

using index

mysql> explain select key1 from s1 where key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)mysql> explain select key_part1, key_part2, id from s1 where key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key_part  | idx_key_part | 909     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

加上主键id也属于覆盖索引,因为主键本身就存储于非聚簇索引B+树的叶子结点中。

using index condition

mysql> explain select * from s1 where key1 > 'z' and key1 like '%a';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |  367 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

using join buffer (block nested loop)

mysql> explain select * from s1 join s2 on s1.common_field = s1.common_field;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |    10.00 | Using where                           |
|  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)

not exists

mysql> explain select * from s1 left join s2 on s1.id = s1.id where s2.id is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | NULL                                                           |
|  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |    10.00 | Using where; Not exists; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

using interset(…)/union(…)/sort_union(…)

mysql> explain select * from s1 where key1 = 'a' or key2 = 'b';
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                       |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | s1    | NULL       | index_merge | idx_key2,idx_key1 | idx_key1,idx_key2 | 303,5   | NULL |    2 |   100.00 | Using union(idx_key1,idx_key2); Using where |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)

zero limit

mysql> explain select * from s1 limit 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Zero limit |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
1 row in set, 1 warning (0.00 sec)

using filesort

mysql> explain select * from s1 order by key1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

对非字符串型字段排序,或可以使用索引字段的排序,就不会有using filesort

mysql> explain select * from s1 order by id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | index | NULL          | PRIMARY | 4       | NULL | 9895 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

using temporary

特别是distinctgroup by等用到的字段是非索引列时,extra中会有using temporary

mysql> explain select distinct common_field from s1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)mysql> explain select count(*) from s1 group by common_field;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9895 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

如果分组、去重等操作可以用到索引,就无需临时表:

mysql> explain select common_field from s1 group by key1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9895 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

因为创建、维护临时表需要开销,所以还是尽量使用索引进行相关操作。

小结

explain不考虑任何cache,不能显示优化过程,不会显示触发器、存储过程的信息,或用户自定义函数对查询的影响,而且其部分统计信息是估算的,不是精确值。

两种输出格式

传统格式

即默认格式,输出形式为一张表。

JSON格式

explain后面指定format = json即可:

mysql> explain format = json select * from s1 where key1 = 'a';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"query_block": {"select_id": 1,"cost_info": {"query_cost": "1.20" # 查询成本},"table": {"table_name": "s1","access_type": "ref", # 传统格式下的type"possible_keys": ["idx_key1"],"key": "idx_key1","used_key_parts": ["key1"],"key_length": "303", # 传统格式下d的key_len"ref": ["const"],"rows_examined_per_scan": 1, # 即传统模式下的rows"rows_produced_per_join": 1, # 传统模式下rows * filtered的向下取整"filtered": "100.00","cost_info": {"read_cost": "1.00","eval_cost": "0.20","prefix_cost": "1.20","data_read_per_join": "1K"},"used_columns": ["id","key1","key2","key3","key_part1","key_part2","key_part3","common_field"]}}
} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

其中cost_info信息如下:

      "cost_info": {"read_cost": "1.00", "eval_cost": "0.20","prefix_cost": "1.20","data_read_per_join": "1K"}

read_cost包括两部分:1)、IO成本;2)、检测到rows * (1 - filtered)条记录的CPU成本
eval_cost:检测rows * filtered条记录的成本;
prefix_cost:单独查询s1标的成本,即read_cost + eval_cost
data_read_per_join:此次查询中需要读取的数据量。

show warnings

该语句用于显示上一条语句执行后,产生的警告信息:

mysql> explain select * from s1 where key1 = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql> show warnings\G
*************************** 1. row ***************************Level: NoteCode: 1003
Message: /* select#1 */ select `review_mysql`.`s1`.`id` AS `id`,`review_mysql`.`s1`.`key1` AS `key1`,`review_mysql`.`s1`.`key2` AS `key2`,`review_mysql`.`s1`.`key3` AS `key3`,`review_mysql`.`s1`.`key_part1` AS `key_part1`,`review_mysql`.`s1`.`key_part2` AS `key_part2`,`review_mysql`.`s1`.`key_part3` AS `key_part3`,`review_mysql`.`s1`.`common_field` AS `common_field` from `review_mysql`.`s1` where (`review_mysql`.`s1`.`key1` = 'a')
1 row in set (0.00 sec)

上例中,show warnings显示的警告信息中的Message字段,输出了我们的SQL经过优化后的大体格式。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.luyixian.cn/news_show_331241.aspx

如若内容造成侵权/违法违规/事实不符,请联系dt猫网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

解决打开excel时报错 “不能使用对象链接和嵌入”

问题截图 打开excel文件或者插入对象时&#xff0c;直接弹出不能使用对象链接和嵌入报错信息。 解决方法 按 winr 组合快捷键&#xff0c;打开运行&#xff0c;输入 dcomcnfg.exe 按回车确定 此时进入到组件服务管理界面&#xff0c;依次选择 组件服务-计算机-我的电脑-DOCM…

LaTex 1【字体、符号、表格】

​&#xff08;上一期已经安装完软件&#xff0c;但是突然出现了一点子问题不会解决&#xff0c;先用overleaf来学习吧&#xff0c;网站还是很给力的&#xff09; 关键字部分&#xff1a; \quad:代表空格【无论你打多少个空格都不是空格&#xff0c;要输入“\quad”】 字体部分…

ECMAScript 6 之二

目录 2.6 Symbol 2.7 Map 和 Set 2.8 迭代器和生成器 2.9 Promise对象 2.10 Proxy对象 2.11 async的用法 2.22 类class 2.23 模块化实现 2.6 Symbol 原始数据类型&#xff0c;它表示是独一无二的值。它属于 JavaScript 语言的原生数据类型之一&#xff0c;其他数据类型…

6.2.8 网络基本服务----万维网(www)

6.2.8 网络基本服务----万维网&#xff08;www&#xff09; 万维网即www&#xff08;World Wide Web&#xff09;是开源的信息空间&#xff0c;使用URL也就是统一资源标识符标识文档和Web资源&#xff0c;使用超文本链接互相连接资源&#xff0c;万维网并非某种特殊的计算机网…

Linux下Lua和C++交互

前言 lua&#xff08;wiki 中文 官方社区&#xff1a;lua-users&#xff09;是一门开源、简明、可扩展且高效的弱类型解释型脚本语言。 由于其实现遵循C标准&#xff0c;它几乎能在所有的平台&#xff08;windows、linux、MacOS、Android、iOS、PlayStation、XBox、wii等&…

【大数据之Hive】二十三、HQL语法优化之数据倾斜

1 数据倾斜概述 数据倾斜指参与计算的数据分布不均&#xff0c;即某个key或者某些key的数据量远超其他key&#xff0c;导致在shuffle阶段&#xff0c;大量相同key的数据被发往同一个Reduce&#xff0c;导致该Reduce所需的时间远超其他Reduce&#xff0c;成为整个任务的瓶颈。  …

数据结构与算法——什么是线性表(线性存储结构)

我们知道&#xff0c;具有“一对一”逻辑关系的数据&#xff0c;最佳的存储方式是使用线性表。那么&#xff0c;什么是线性表呢&#xff1f; 线性表&#xff0c;全名为线性存储结构。使用线性表存储数据的方式可以这样理解&#xff0c;即“把所有数据用一根线儿串起来&#xf…

海岸带地物分类步骤

1.读取图像 使用 Envi 打开 imageKSC.tif 影像。在 Toolbox 工具栏中选择 Spectral->Build 3D Cube。在 3D Cube File 对话框中选择高光谱数据集&#xff0c;显示信息为 614*512*176 的高光谱影像&#xff0c;单击 OK 按钮。 图1 原始影像 2.选择波段 当打开 3D Cube RGB…

【数学建模】统计分析方法

文章目录 1.回归分析2. 逻辑回归3. 聚类分析4. 判别分析5. 主成分分析6. 因子分析7. 对应分析 1.回归分析 数据量要多&#xff0c;样本总量n越大越好——>保证拟合效果更好&#xff0c;预测效果越好 一般n>40/45较好 方法 建立回归模型 yiβ0β1i……βkxkiεi 所估计的…

运输层(TCP运输协议相关)

运输层 1. 运输层概述2. 端口号3. 运输层复用和分用4. 应用层常见协议使用的运输层熟知端口号5. TCP协议对比UDP协议6. TCP的流量控制7. TCP的拥塞控制7.1 慢开始算法、拥塞避免算法7.2 快重传算法7.3 快恢复算法 8. TCP超时重传时间的选择8.1 超时重传时间计算 9. TCP可靠传输…

win10查看、关闭和开启多个mysql服务

我的之前安装了2个MySQL版本&#xff0c;一个是MySQL8.0.17&#xff0c;一个是MySQL5.7.19 为什么要查看怎么关闭MySQL服务?如果是个人电脑&#xff0c;我觉得开启一个服务相当于开启一个进程&#xff0c;可能会占用部分内存。如果自己是游戏摆烂状态&#xff08;非学习状态&…

【Ubuntu】安装docker-compose

要在Ubuntu上安装Docker Compose&#xff0c;可以按照以下步骤进行操作&#xff1a; 下载 Docker Compose 二进制文件&#xff1a; sudo curl -L "https://github.com/docker/compose/releases/latest/download/docker-compose-$(uname -s)-$(uname -m)" -o /usr/loc…

我爱学QT--qt的网络编程

学习地址&#xff1a; QT网络编程之TCP通信_哔哩哔哩_bilibili QT网络编程有TCP和UDP。 TCP编程需要用到两个类&#xff1a;QTcpServer和QTcpSocket 本节课目标&#xff1a; 完成一个服务器和一个客户端 首先是经典的几步 先设计ui再设计逻辑实现 多看看写的文件理解吧

基于linux下的高并发服务器开发(第一章)- GDB调试(3)1.15

04 / GDB命令&#xff1a;断点操作 其中num代表断点编号&#xff0c;Type&#xff08;类型&#xff09;为断点&#xff08;breakpoint&#xff09;&#xff0c;Disp为断点状态&#xff0c;Enb是yes代表为有效断点&#xff0c;adress为断点地址&#xff0c;What说明断点的在那个…

2023年上半年总结

2023年上半年总结 引言问答CSDN 竞赛技能树博客原力值粉丝数贡献墙个人能力图新星计划总结 引言 老顾是一个懒癌晚期患者&#xff0c;通常情况下&#xff0c;是一条不折不扣的咸鱼&#xff0c;在工作中&#xff0c;也大多数时间都用来摸鱼了。 摸鱼时间太长&#xff0c;也就有…

ES(1)简介和安装

文章目录 简介倒排索引 安装 简介 ES是面向文档型数据库&#xff0c;一条数据在这里就是一个文档。 和关系型数据库大致关系如下: ES7.x中废除掉Type&#xff08;表&#xff09;的概念 倒排索引 要知道什么是倒排索引&#xff0c;就要先知道什么是正排索引 idcontent100…

JAVA中的Socket编程、通信协议、传输协议

JAVA中的Socket编程 一、Socket概述 Socket&#xff0c;建立起客户端和服务器之间的连接&#xff0c;实现数据的传输和交互&#xff0c;它既可以发送请求&#xff0c;也可以接受请求&#xff0c;一个Socket由一个IP地址和一个端口号唯一确定&#xff0c;利用Socket能比较方便的…

AI智能助手的未来:与人类互动的下一代人工智能技术

自我介绍⛵ &#x1f4e3;我是秋说&#xff0c;研究人工智能、大数据等前沿技术&#xff0c;传递Java、Python等语言知识。 &#x1f649;主页链接&#xff1a;秋说的博客 &#x1f4c6; 学习专栏推荐&#xff1a;人工智能&#xff1a;创新无限、MySQL进阶之路、C刷题集、网络安…

flask实现get和post请求

1、实现get请求 在项目根目录创建app.py 代码如下&#xff1a; from flask import Flask,render_template,requestapp Flask(__name__)app.route("/regist/user/", methods[GET]) def regist():return render_template("regist.html") #默认去templat…

玩转数据可视化之R语言ggplot2:(十四)层级布局(一层一层增加你的绘图元素,使绘图更灵活)

【R语言数据科学可视化篇】 🌸个人主页:JOJO数据科学📝个人介绍:统计学top3高校统计学硕士在读💌如果文章对你有帮助,欢迎✌关注、👍点赞、✌收藏、👍订阅专栏✨本文收录于【R语言数据科学】本系列主要介绍R语言在数据科学领域的应用包括: R语言编程基础、R语言可…