正文
mysql的explain命令语句提供了如何执行sql语句的信息,解析sql语句的执行计划并展示,explain支持select、delete、insert、replace和update等语句,也支持对分区表的解析。通常explain用来获取select语句的执行计划,通过explain展示的信息我们可以了解到表查询的顺序,表连接的方式等,并根据这些信息判断select执行效率,决定是否添加索引或改写sql语句优化表连接方式以提高执行效率。本文参考官方文档:explain output format对explain输出的内容进行说明,同时也对自己之前使用explain不清晰的方面进行总结。
本文使用的mysql版本为官方社区版 5.7.24
。
mysql root@localhost:(none)> select version(); +------------+ | version() | +------------+ | 5.7.24-log | +------------+ 1 row in set time: 0.066s
{ explain | describe } [extended | partitions | format=[traditional | json]] sql_statement;
本文基于mysql官方示例数据库employee:example databases进行解析说明,使用到的表如下:
-- employees: mysql root@localhost:employees> show create table employees\g; ***************************[ 1. row ]*************************** table | employees create table | create table `employees` ( `emp_no` int(11) not null, `birth_date` date not null, `first_name` varchar(14) not null, `last_name` varchar(16) not null, `gender` enum('m','f') not null, `hire_date` date not null, primary key (`emp_no`), key `idx_first_last` (`first_name`,`last_name`), key `idx_birth_hire` (`birth_date`,`hire_date`) ) engine=innodb default charset=utf8 1 row in set time: 0.008s -- dept_emp: mysql root@localhost:employees> show create table dept_emp\g; ***************************[ 1. row ]*************************** table | dept_emp create table | create table `dept_emp` ( `emp_no` int(11) not null, `dept_no` char(4) not null, `from_date` date not null, `to_date` date not null, primary key (`emp_no`,`dept_no`), key `dept_no` (`dept_no`), constraint `dept_emp_ibfk_1` foreign key (`emp_no`) references `employees` (`emp_no`) on delete cascade, constraint `dept_emp_ibfk_2` foreign key (`dept_no`) references `departments` (`dept_no`) on delete cascade ) engine=innodb default charset=utf8 1 row in set time: 0.010s -- departments: mysql root@localhost:employees> show create table departments\g; ***************************[ 1. row ]*************************** table | departments create table | create table `departments` ( `dept_no` char(4) not null, `dept_name` varchar(40) not null, primary key (`dept_no`), unique key `dept_name` (`dept_name`) ) engine=innodb default charset=utf8 1 row in set time: 0.012s
mysql root@localhost:employees> explain select count(*) from employees; +----+-------------+-----------+------------+-------+---------------+---------+---------+--------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+--------+--------+----------+-------------+ | 1 | simple | employees | <null> | index | <null> | primary | 4 | <null> | 299512 | 100.0 | using index | +----+-------------+-----------+------------+-------+---------------+---------+---------+--------+--------+----------+-------------+ 1 row in set time: 0.026s
通过以上示例语句得出explain输出有12个字段,主要说明如下表:
字段(column) | json名称(json name) | 含义(meaning) |
---|---|---|
id | select_id | 标识符,语句涉及表的执行顺序 |
select_type | none | 表查询类型 |
table | table_name | 表名称 |
partitions | partitions | 涉及表哪个分区 |
type | access_type | 表的查询(连接)类型 |
possible_keys | possible_keys | 表可能使用到的索引 |
key | key | 表实际使用到的索引 |
key_len | key_length | 表实际使用索引的长度,单位:字节 |
ref | ref | 表哪些字段或者常量用于连接查找索引上的值 |
rows | rows | 查询预估返回表的行数 |
filtered | filtered | 表经过条件过滤之后与总数的百分比 |
extra | none | 额外的说明信息 |
id为select标识符,语句在执行计划当中的执行顺序。id值的出现有如下几种情况:
-- id全相同 mysql root@localhost:employees> explain select * from employees e,dept_emp d,departments de where e.emp_no = d.emp_no and de.dept_name = 'human resources'; +----+-------------+-------+------------+-------+---------------+-----------+---------+--------------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+--------------------+--------+----------+-------------+ | 1 | simple | de | <null> | const | dept_name | dept_name | 122 | const | 1 | 100.0 | using index | | 1 | simple | e | <null> | all | primary | <null> | <null> | <null> | 299512 | 100.0 | <null> | | 1 | simple | d | <null> | ref | primary | primary | 4 | employees.e.emp_no | 1 | 100.0 | <null> | +----+-------------+-------+------------+-------+---------------+-----------+---------+--------------------+--------+----------+-------------+ 3 rows in set time: 0.018s -- id全不相同 mysql root@localhost:employees> explain select * from employees e where e.emp_no = (select d.emp_no from dept_emp d where d.dept_no = (select de.d ept_no from departments de where de.dept_name = 'development') and d.emp_no = 10023); +----+-------------+-------+------------+-------+-----------------+-----------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------+------------+-------+-----------------+-----------+---------+-------------+------+----------+-------------+ | 1 | primary | e | <null> | const | primary | primary | 4 | const | 1 | 100.0 | <null> | | 2 | subquery | d | <null> | const | primary,dept_no | primary | 16 | const,const | 1 | 100.0 | using index | | 3 | subquery | de | <null> | const | dept_name | dept_name | 122 | const | 1 | 100.0 | using index | +----+-------------+-------+------------+-------+-----------------+-----------+---------+-------------+------+----------+-------------+ 3 rows in set time: 0.027s -- id部分相同,部分不相同 mysql root@localhost:employees> explain select * from^iemployees e where^ie.emp_no in (select d.emp_no from dept_emp d where d.dept_no = (select d e.dept_no from departments de where de.dept_name = 'human resources')); +----+-------------+-------+------------+--------+-----------------+-----------+---------+--------------------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------+------------+--------+-----------------+-----------+---------+--------------------+-------+----------+-------------+ | 1 | primary | d | <null> | ref | primary,dept_no | dept_no | 12 | const | 33212 | 100.0 | using index | | 1 | primary | e | <null> | eq_ref | primary | primary | 4 | employees.d.emp_no | 1 | 100.0 | <null> | | 3 | subquery | de | <null> | const | dept_name | dept_name | 122 | const | 1 | 100.0 | using index | +----+-------------+-------+------------+--------+-----------------+-----------+---------+--------------------+-------+----------+-------------+ 3 rows in set time: 0.020s
select_type为表查询的类型,根据官方文档总结几种常见类型如下表:
select_type值(value) | json名称(json name) | 含义(meaning) |
---|---|---|
simple | none | 简单查询,不包含unino查询或子查询 |
primary | none | 位于最外层的查询 |
union | none | 当出现union查询时第二个或之后的查询 |
dependent union | dependent(true) | 当出现union查询时第二个或之后的查询,取决于外部查询 |
union result | union_result | union查询的结果 |
subquery | none | 子查询当中第一个select查询 |
dependent subquery | dependent(true) | 子查询当中第一个select查询,取决于外部的查询 |
derived | none | 派生表,from子句中出现的子查询 |
mysql root@localhost:employees> explain select * from employees where emp_no = 10001; +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+ | 1 | simple | employees | <null> | const | primary | primary | 4 | const | 1 | 100.0 | <null> | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+ 1 row in set time: 0.019s
mysql root@localhost:employees> explain select * from dept_emp d where d.dept_no = (select de.dept_no from departments de where de.dept_name = 'de velopment'); +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+ | 1 | primary | d | <null> | ref | dept_no | dept_no | 12 | const | 148054 | 100.0 | using where | | 2 | subquery | de | <null> | const | dept_name | dept_name | 122 | const | 1 | 100.0 | using index | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+ 2 rows in set time: 0.021s
mysql root@localhost:employees> explain select * from departments where dept_no = 'd005' union select * from departments where dept_no = 'd004'; +--------+--------------+-------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +--------+--------------+-------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+ | 1 | primary | departments | <null> | const | primary | primary | 12 | const | 1 | 100.0 | <null> | | 2 | union | departments | <null> | const | primary | primary | 12 | const | 1 | 100.0 | <null> | | <null> | union result | <union1,2> | <null> | all | <null> | <null> | <null> | <null> | <null> | <null> | using temporary | +--------+--------------+-------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+ 3 rows in set time: 0.020s
mysql root@localhost:employees> explain select * from employees e where e.emp_no in (select d.emp_no from dept_emp d where d.from_date = '1986-06- 26' union select d.emp_no from dept_emp d where d.from_date = '1996-08-03'); +--------+--------------------+------------+------------+------+---------------+---------+---------+--------+--------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +--------+--------------------+------------+------------+------+---------------+---------+---------+--------+--------+----------+-----------------+ | 1 | primary | e | <null> | all | <null> | <null> | <null> | <null> | 299512 | 100.0 | using where | | 2 | dependent subquery | d | <null> | ref | primary | primary | 4 | func | 1 | 10.0 | using where | | 3 | dependent union | d | <null> | ref | primary | primary | 4 | func | 1 | 10.0 | using where | | <null> | union result | <union2,3> | <null> | all | <null> | <null> | <null> | <null> | <null> | <null> | using temporary| +--------+--------------------+------------+------------+------+---------------+---------+---------+--------+--------+----------+-----------------+ 4 rows in set time: 0.022s
mysql root@localhost:employees> explain select * from (select * from departments limit 5) de; +----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+ | 1 | primary | <derived2> | <null> | all | <null> | <null> | <null> | <null> | 5 | 100.0 | <null> | | 2 | derived | departments | <null> | index | <null> | dept_name | 122 | <null> | 9 | 100.0 | using index | +----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+ 2 rows in set time: 0.012s
指执行计划当中当前是从哪张表获取数据,如果为表指定了别名,则显示别名,如果没有涉及对表的数据读取,则显示null,还有如下几种情形:
指执行计划中当前从分区表哪个表分区获取数据,如果不是分区表,则显示为null。
-- 示例数据库employees的分区表salaries mysql root@localhost:employees> show create table salaries; +----------+-----------------------------------------------------------------+ | table | create table | +----------+-----------------------------------------------------------------+ | salaries | create table `salaries` ( | | | `emp_no` int(11) not null, | | | `salary` int(11) not null, | | | `from_date` date not null, | | | `to_date` date not null, | | | primary key (`emp_no`,`from_date`) | | | ) engine=innodb default charset=utf8 | | | /*!50500 partition by range columns(from_date) | | | (partition p01 values less than ('1985-12-31') engine = innodb, | | | partition p02 values less than ('1986-12-31') engine = innodb, | | | partition p03 values less than ('1987-12-31') engine = innodb, | | | partition p04 values less than ('1988-12-31') engine = innodb, | | | partition p05 values less than ('1989-12-31') engine = innodb, | | | partition p06 values less than ('1990-12-31') engine = innodb, | | | partition p07 values less than ('1991-12-31') engine = innodb, | | | partition p08 values less than ('1992-12-31') engine = innodb, | | | partition p09 values less than ('1993-12-31') engine = innodb, | | | partition p10 values less than ('1994-12-31') engine = innodb, | | | partition p11 values less than ('1995-12-31') engine = innodb, | | | partition p12 values less than ('1996-12-31') engine = innodb, | | | partition p13 values less than ('1997-12-31') engine = innodb, | | | partition p14 values less than ('1998-12-31') engine = innodb, | | | partition p15 values less than ('1999-12-31') engine = innodb, | | | partition p16 values less than ('2000-12-31') engine = innodb, | | | partition p17 values less than ('2001-12-31') engine = innodb, | | | partition p18 values less than ('2002-12-31') engine = innodb, | | | partition p19 values less than (maxvalue) engine = innodb) */ | +----------+-----------------------------------------------------------------+ 1 row in set time: 0.018s mysql root@localhost:employees> explain select * from salaries where from_date > '1985-12-31' and from_date < '1990-12-31'; +----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+ | 1 | simple | salaries | p02,p03,p04,p05,p06 | all | <null> | <null> | <null> | <null> | 384341 | 11.11 | using where | +----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+ 1 row in set time: 0.023s
type应该被认为是解读执行计划当中最重要的部分,根据type显示的内容可以判断语句总体的查询效率。主要有以下几种类型:
-- 测试表departments_1生成: mysql root@localhost:employees> create table departments_1 as select * from departments where dept_no='d005'; query ok, 1 row affected time: 0.107s mysql root@localhost:employees> alter table departments_1 add primary key(dept_no); query ok, 0 rows affected mysql root@localhost:employees> create index idx_dept_name on departments_1(dept_name); query ok, 0 rows affected mysql root@localhost:employees> show create table departments_1\g; ***************************[ 1. row ]*************************** table | departments_1 create table | create table `departments_1` ( `dept_no` char(4) not null, `dept_name` varchar(40) default null, primary key (`dept_no`), key `idx_dept_name` (`dept_name`) ) engine=innodb default charset=utf8 1 row in set time: 0.010s -- 系统表: mysql root@localhost:employees> explain select * from mysql.proxies_priv; +----+-------------+--------------+------------+--------+---------------+--------+---------+--------+------+----------+--------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+--------------+------------+--------+---------------+--------+---------+--------+------+----------+--------+ | 1 | simple | proxies_priv | <null> | system | <null> | <null> | <null> | <null> | 1 | 100.0 | <null> | +----+-------------+--------------+------------+--------+---------------+--------+---------+--------+------+----------+--------+ 1 row in set time: 0.023s -- 普通表: mysql root@localhost:employees> explain select * from (select * from departments_1 where dept_no = 'd005' limit 1) de; +----+-------------+---------------+------------+--------+---------------+---------+---------+--------+------+----------+--------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+---------------+------------+--------+---------------+---------+---------+--------+------+----------+--------+ | 1 | primary | <derived2> | <null> | system | <null> | <null> | <null> | <null> | 1 | 100.0 | <null> | | 2 | derived | departments_1 | <null> | const | primary | primary | 12 | const | 1 | 100.0 | <null> | +----+-------------+---------------+------------+--------+---------------+---------+---------+--------+------+----------+--------+ 2 rows in set time: 0.015s
mysql root@localhost:employees> explain select * from departments_1 where dept_no = 'd005'; +----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+ | 1 | simple | departments_1 | <null> | const | primary | primary | 12 | const | 1 | 100.0 | <null> | +----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+ 1 row in set time: 0.018s
explain select * from departments d,departments_1 d1 where d.dept_no = d1.dept_no; +----+-------------+-------+------------+--------+---------------+---------------+---------+----------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------+------------+--------+---------------+---------------+---------+----------------------+------+----------+-------------+ | 1 | simple | d1 | <null> | index | primary | idx_dept_name | 123 | <null> | 1 | 100.0 | using index| | 1 | simple | d | <null> | eq_ref | primary | primary | 12 | employees.d1.dept_no | 1 | 100.0 | <null> | +----+-------------+-------+------------+--------+---------------+---------------+---------+----------------------+------+----------+-------------+ 2 rows in set time: 0.037s
mysql root@localhost:employees> explain select * from dept_emp where dept_no ='d005'; +----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+--------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+--------+ | 1 | simple | dept_emp | <null> | ref | dept_no | dept_no | 12 | const | 148054 | 100.0 | <null> | +----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+--------+ 1 row in set time: 0.059s mysql root@localhost:employees> explain select * from dept_emp d,departments_1 d1 where d.dept_no = d1.dept_no; +----+-------------+-------+------------+------+---------------+---------+---------+----------------------+-------+----------+--------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------+------------+------+---------------+---------+---------+----------------------+-------+----------+--------+ | 1 | simple | d1 | <null> | all | <null> | <null> | <null> | <null> | 1 | 100.0 | <null> | | 1 | simple | d | <null> | ref | dept_no | dept_no | 12 | employees.d1.dept_no | 41392 | 100.0 | <null> | +----+-------------+-------+------------+------+---------------+---------+---------+----------------------+-------+----------+--------+ 2 rows in set time: 0.012s
mysql root@localhost:employees> explain select dept_name from departments_1 where dept_name = 'd005' or dept_name is null; +----+-------------+---------------+------------+-------------+---------------+---------------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+---------------+------------+-------------+---------------+---------------+---------+-------+------+----------+--------------------------+ | 1 | simple | departments_1 | <null> | ref_or_null | idx_dept_name | idx_dept_name | 123 | const | 2 | 100.0 | using where; using index | +----+-------------+---------------+------------+-------------+---------------+---------------+---------+-------+------+----------+--------------------------+ 1 row in set time: 0.011s
mysql root@localhost:employees> explain select * from dept_emp where emp_no = 10001 or dept_no = (select dept_no from departments_1); +----+-------------+---------------+------------+-------------+-----------------+-----------------+---------+--------+--------+----------+-------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+---------------+------------+-------------+-----------------+-----------------+---------+--------+--------+----------+-------------------------------------------+ | 1 | primary | dept_emp | <null> | index_merge | primary,dept_no | primary,dept_no | 4,12 | <null> | 148055 | 100.0 | using union(primary,dept_no); using where | | 2 | subquery | departments_1 | <null> | index | <null> | idx_dept_name | 123 | <null> | 1 | 100.0 | using index | +----+-------------+---------------+------------+-------------+-----------------+-----------------+---------+--------+--------+----------+-------------------------------------------+ 2 rows in set time: 0.014s
mysql root@localhost:employees> explain select de.* from dept_emp de,departments_1 d where de.dept_no = d.dept_no and de.emp_no < 10010; +----+-------------+-------+------------+-------+-----------------+---------------+---------+--------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------+------------+-------+-----------------+---------------+---------+--------+------+----------+----------------------------------------------------+ | 1 | simple | d | <null> | index | primary | idx_dept_name | 123 | <null> | 1 | 100.0 | using index | | 1 | simple | de | <null> | range | primary,dept_no | primary | 4 | <null> | 9 | 12.5 | using where; using join buffer (block nested loop) | +----+-------------+-------+------------+-------+-----------------+---------------+---------+--------+------+----------+----------------------------------------------------+ 2 rows in set time: 0.019s
mysql root@localhost:employees> explain select dept_name from departments_1; +----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+ | 1 | simple | departments_1 | <null> | index | <null> | idx_dept_name | 123 | <null> | 1 | 100.0 | using index | +----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+ 1 row in set time: 0.020s
mysql root@localhost:employees> drop index idx_dept_name on departments_1; query ok, 0 rows affected time: 0.052s mysql root@localhost:employees> explain select * from departments_1; +----+-------------+---------------+------------+------+---------------+--------+---------+--------+------+----------+--------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+---------------+------------+------+---------------+--------+---------+--------+------+----------+--------+ | 1 | simple | departments_1 | <null> | all | <null> | <null> | <null> | <null> | 1 | 100.0 | <null> | +----+-------------+---------------+------------+------+---------------+--------+---------+--------+------+----------+--------+ 1 row in set time: 0.018s
通过以上各种主要类型的分析,可以总结出各个类型性能排序(从左到右性能从高到低):
system > const > eq_ref > ref > range > index > all
显示了mysql在查找当前表中数据的时候可能使用到的索引,如果该字段值为null,则表明没有相关索引可用。
显示了mysql在实际查找数据时决定使用的索引,如果该字段值为null,则表明没有使用索引。
显示了mysql实际使用索引的键大小,单位字节。可以通过key_len的大小判断评估复合索引使用了哪些部分,如果key字段值为null,则key_len的值也为null。
几种常见字段类型索引长度大小如下,假设字符编码为utf8:
显示哪些常量或者字段被用于查询索引列键值,以获取表中数据行。
mysql root@localhost:employees> explain select * from departments d,departments_1 d1 where d.dept_no = d1.dept_no; +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------+ | 1 | simple | d1 | <null> | all | primary | <null> | <null> | <null> | 1 | 100.0 | <null> | | 1 | simple | d | <null> | eq_ref | primary | primary | 12 | employees.d1.dept_no | 1 | 100.0 | <null> | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------+ 2 rows in set time: 0.038s
显示预估需要查询的行数。对innodb表来说这是个预估值,并非是个准确值。
显示按表条件过滤的表行的估计百分比。
显示查询时的额外信息。常见的有如下几种:
mysql root@localhost:employees> explain select dept_name from departments_1; +----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+ | 1 | simple | departments_1 | <null> | index | <null> | idx_dept_name | 123 | <null> | 1 | 100.0 | using index | +----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+ 1 row in set time: 0.015s
-- employees表创建复合索引idx_birth_hire mysql root@localhost:employees> create index idx_birth_hire on employees(birth_date,hire_date); query ok, 0 rows affected time: 0.768s mysql root@localhost:employees> explain select * from employees where birth_date = '1960-01-01' and hire_date > '1980-01-01'; +----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+-----------------------+ | 1 | simple | employees | <null> | range | idx_birth_hire | idx_birth_hire | 6 | <null> | 63 | 100.0 | using index condition | +----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+-----------------------+ 1 row in set time: 0.016s
mysql root@localhost:employees> explain select distinct dept_no from dept_emp; +----+-------------+----------+------------+-------+-----------------+---------+---------+--------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+----------+------------+-------+-----------------+---------+---------+--------+------+----------+--------------------------+ | 1 | simple | dept_emp | <null> | range | primary,dept_no | dept_no | 12 | <null> | 9 | 100.0 | using index for group-by | +----+-------------+----------+------------+-------+-----------------+---------+---------+--------+------+----------+--------------------------+ 1 row in set time: 0.020s
mysql root@localhost:employees> explain select * from employees where birth_date < '1970-01-01'; +----+-------------+-----------+------------+------+----------------+--------+---------+--------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-----------+------------+------+----------------+--------+---------+--------+--------+----------+-------------+ | 1 | simple | employees | <null> | all | idx_birth_hire | <null> | <null> | <null> | 299512 | 50.0 | using where | +----+-------------+-----------+------------+------+----------------+--------+---------+--------+--------+----------+-------------+ 1 row in set time: 0.016s
mysql root@localhost:employees> explain select * from employees where 1 = 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> | impossible where | +----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------+ 1 row in set time: 0.015s
-- block nested loop mysql root@localhost:employees> explain select * from employees e,dept_emp d where e.emp_no > 10001 and e.emp_no <> d.emp_no; +----+-------------+-------+------------+-------+---------------+---------+---------+--------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------+------------+-------+---------------+---------+---------+--------+--------+----------+----------------------------------------------------+ | 1 | simple | e | <null> | range | primary | primary | 4 | <null> | 149756 | 100.0 | using where | | 1 | simple | d | <null> | all | <null> | <null> | <null> | <null> | 331143 | 90.0 | using where; using join buffer(block nested loop) | +----+-------------+-------+------------+-------+---------------+---------+---------+--------+--------+----------+----------------------------------------------------+ 2 rows in set time: 0.020s -- batched key access mysql root@localhost:employees> explain select /*+ bka(a)*/ a.gender, b.dept_no from employees a, dept_emp b where a.birth_date = b.from_date; +----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+ | 1 | simple | b | <null> | all | <null> | <null> | <null> | <null> | 331143 | 100.0 | <null> | | 1 | simple | a | <null> | ref | idx_birth_hire | idx_birth_hire | 3 | employees.b.from_date | 63 | 100.0 | using join buffer (batched key access) | +----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+ 2 rows in set time: 0.014s
mysql root@localhost:employees> set optimizer_switch='mrr=on,mrr_cost_based=off'; query ok, 0 rows affected time: 0.001s mysql root@localhost:employees> explain select * from employees where birth_date = '1970-01-01' and hire_date > '1990-01-01'; +----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+----------------------------------+ | 1 | simple | employees | <null> | range | idx_birth_hire | idx_birth_hire | 6 | <null> | 1 | 100.0 | using index condition; using mrr | +----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+----------------------------------+ 1 row in set time: 0.014s
mysql root@localhost:employees> explain select * from employees e,dept_emp d where e.emp_no > d.emp_no; +----+-------------+-------+------------+------+---------------+--------+---------+--------+--------+----------+------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------+------------+------+---------------+--------+---------+--------+--------+----------+------------------------------------------------+ | 1 | simple | d | <null> | all | primary | <null> | <null> | <null> | 331143 | 100.0 | <null> | | 1 | simple | e | <null> | all | primary | <null> | <null> | <null> | 299512 | 33.33 | range checked for each record (index map: 0x1) | +----+-------------+-------+------------+------+---------------+--------+---------+--------+--------+----------+------------------------------------------------+ 2 rows in set time: 0.038s
当语句在优化器阶段过程中可以获取查询结果(如获取行数,只需要读取相应索引数据),而无需再返回表中查询数据,可能会出现select tables optimized away。例如针对myisam引擎的表,使用select count(*)获取表的总行数,而且又没有where子句或者条件总是为真,也没有group by子句时,其实就包含了以上的条件且隐式含有group by分组的效果。
-- 创建myisam引擎的employees表 mysql root@localhost:employees> create table employees_myisam like employees; query ok, 0 rows affected time: 0.040s mysql root@localhost:employees> insert into employees_myisam select * from employees; query ok, 300024 rows affected time: 5.023s mysql root@localhost:employees> alter table employees_myisam engine=myisam; query ok, 300024 rows affected time: 1.515s -- 获取执行count(*)查询行数执行计划 mysql root@localhost:employees> explain select count(*) from employees_myisam; +----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------------------+ | 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> | select tables optimized away | +----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------------------+ 1 row in set time: 0.024s
mysql root@localhost:employees> explain select hire_date from employees group by hire_date; +----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+--------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+--------+----------+----------------------------------------------+ | 1 | simple | employees | <null> | index | idx_birth_hire | idx_birth_hire | 6 | <null> | 299512 | 100.0 | using index; using temporary; using filesort | +----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+--------+----------+----------------------------------------------+ 1 row in set time: 0.018s
mysql root@localhost:employees> explain select * from employees order by hire_date; +----+-------------+-----------+------------+------+---------------+--------+---------+--------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-----------+------------+------+---------------+--------+---------+--------+--------+----------+----------------+ | 1 | simple | employees | <null> | all | <null> | <null> | <null> | <null> | 299512 | 100.0 | using filesort | +----+-------------+-----------+------------+------+---------------+--------+---------+--------+--------+----------+----------------+ 1 row in set time: 0.015s
以上内容总结了mysql获取执行计划explain命令执行时输出的主要字段说明,还有许多未仔细说明的参数和选项,以后还需多多实践总结。可以看出explain命令输出内容当中比较重要的是:
☆〖本人水平有限,文中如有错误还请留言批评指正!〗☆
如对本文有疑问, 点击进行留言回复!!
MySQL-关系代数-并、交、差、等值连接、自然连接、左连接。。。
网友评论