MySQL使用EXPLAIN分析sql语句

MySQL使用EXPLAIN分析sql语句

关于EXPLAIN

EXPLAIN简介

EXPLAIN关键字用于提供MySQL是怎样执行sql语句的,只要在SELECT、DELETE、INSERT、REPLACE、UPDATE这些语句前面加上一个EXPLAIN关键字再运行,就可以输出相关的执行信息。最常见的是用它来分析优化select语句。

DESCRIBE和EXPLAIN的区别

首先说答案:这两个关键字的功能是完全一样的,可以相互替换使用。

打开13.8.2 EXPLAIN Statement,往下滚动一下,能找到以下句子

The DESCRIBE and EXPLAIN statements are synonyms. In practice, the DESCRIBE keyword is more often used to obtain information about table structure, whereas EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query).

翻译:DESCRIBE和EXPLAIN语句是同义词。在实际使用中,DESCRIBE更常用于获取有关表结构的信息,而 EXPLAIN用于获取查询执行计划(即MySQL如何执行查询的说明)。

可以看到,DESCRIBE(平时使用可简写成DESC)和EXPLAIN是同义词,使用的时候功能是一样的,虽然功能完全一样,虽然可以相互替换使用,但在实际使用中,DESCRIBE更常用于获取有关表结构的信息(格式:desc <表名>),而EXPLAIN更常在sql优化和索引优化中使用(用来分析select语句的执行情况,以便知识如何优化)。

构建测试数据库

导入数据库

要分析sql语句,首先要有一个数据库,MySQL官方有很多个Example Databases,在这里搜索“Example Databases”即可搜索到,我们就用它的第一个,叫“employee data”,这个数据库最大,有30多M,并且是在github提供下载:test_db,虽然官网的可能不至于删除,但我还是自己fork了一份到自己的github:test_db。另外这里也有一个在线的可以构建测试数据库的网站,有需要可以去看看。

以下是从下载到导入数据库的所有

# 下载(也可以在浏览器里下载,如果以后有新版本,请自己把以下链接替换为新版本链接即可)
wget https://github.com/datacharmer/test_db/releases/download/v1.0.7/test_db-1.0.7.tar.gz

# 解压(当然你也可以双击或右击解压)
tar -zxf test_db-1.0.7.tar.gz

# 进入test_db文件夹(一定要进入该文件夹后再执行以下语句,否则导入会报错,原因看后面的解释)
cd test_db

# 导入方式一:直接使用mysql命令导入(如果默认数据库)
mysql -u<用户名> -p < employees.sql

# 导入方式二:先登录进数据库
mysql -u<用户名> -p

# 登录后设置一下默认数据库引擎为InnoDB(当然这一步在大多数情况下可以不做,因为8.0以上版本默认引擎就是InnoDB)
set storage_engine = InnoDB;

# 然后再使用source导入
source employees.sql
  • 1、虽然这个包叫test_db,但是它创建的数据库名是“employees”(意思是“员工数据库”);
  • 2、虽然test_db文件夹内有很多sql文件,但我们只需要导入employees.sql即可,因为在employees.sql文件底部有很多source语句,它会自动source其它sql文件,这也是为什么你需要先cd进test_db文件夹的原因。

数据库表结构分析

mysql> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)

可以看到,前面导入的employees库有8张表,不过实际上current_dept_emp和dept_emp_latest_date是视图,所以实际的表有6张:

  • 1、employees: 员工表
  • 2、titles: 员工职称表(与员工表关联)
  • 3、salaries: 员工薪资表(与员工关联)
  • 4、departments: 部门表
  • 5、dept_emp: 员工-部门关联表
  • 6、dept_manager: 部门经理表(与部门关联)

EXPLAIN输出结果详解

官方文档:8.8.2 EXPLAIN Output Format

输出结果

执行以下sql语句,得到一个表格,这个表格就是explain后面那个select语句的执行信息

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

如果explain后面加个format=json,可以以json格式输出sql执行信息

explain format=json select * from employees;

json格式输出explain信息

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "30833.08"
    },
    "table": {
      "table_name": "employees",
      "access_type": "ALL",
      "rows_examined_per_scan": 299379,
      "rows_produced_per_join": 299379,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "895.18",
        "eval_cost": "29937.90",
        "prefix_cost": "30833.08",
        "data_read_per_join": "38M"
      },
      "used_columns": [
        "emp_no",
        "birth_date",
        "first_name",
        "last_name",
        "gender",
        "hire_date"
      ]
    }
  }
}

注意:json格式并不是单纯的把表格格式转换为json格式,而是显示了更多的信息,但也有些表格中的信息没有在json中显示(比如select_type和Extra就不在json信息中显示)。

输出结果详解

json格式信息我们暂时不看,我们先看表格信息,从以上表格信息中我们可以看到,explain共输出12个字段

序号 说明
1 id MySQL查询优化器选定的执行计划中查询的序列号。表示查询中执行select子句或操作表的顺序,id值越大优先级越高,越先被执行。id相同,执行顺序由上至下。
2 子序号 select_type select类型,如下所示,从最好到最差依次排列
1 SIMPLE 简单的select查询,不使用union及子查询
2 PRIMARY 当有子查询时,最外层的select有可能是PRIMARY类型,但也未必,因为PRIMARY类型比较少见
3 UNION UNION中的第二个或随后的select查询,不依赖外部查询的结果集。
4 DEPENDENT UNION UNION中的第二个或随后的select查询,依赖于外部查询的结果集。
5 UNION RESULT UNION 查询的结果集
6 SUBQUERY 子查询中的第一个select查询,不依赖于外部查询的结果集。
7 DEPENDENT SUBQUERY 子查询中的第一个select查询,依赖于外部查询的结果集。
8 DERIVED 用于from子句里有子查询的情况。MySQL会递归执行这些子查询,把结果放在临时表里,不依赖外部结果集。
9 DEPENDENT DERIVED 用于from子句里有子查询的情况。MySQL会递归执行这些子查询,把结果放在临时表里,依赖于外部结果集。
10 MATERIALIZED 物化,其实就是创建临时表
11 UNCACHEABLE SUBQUERY 结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估。
12 UNCACHEABLE UNION UNION中的第二个或随后的select查询,属于不可缓存的子查询。
3 table 输出表所引用的行
4 partitions 查询将会从哪个分区匹配记录。
5 子序号 type 以下类型从好到坏依次排列,全文索引一般可以忽略,因为全文索引一般都是用专用的全文索引,而不用mysql本身的。
1 system 表仅有一行(=系统表)。这是const连接类型的一个特例。
2 const 该表最多有一个匹配行,在查询开始时被读取。 因为只有一行,所以这一行中字段的值可以被优化器的其余部分视为常量。 const表非常快,因为它们只被读取一次。这种类型一般出现在“where 字段=常量”,且字段是主键或有唯一索引时。
3 eq_ref 对于先前表中的每个行组合,从该表中读取一行。 除了system和const类型之外,这是最好的连接类型。当连接使用索引的所有部分并且索引是PRIMARY KEY或UNIQUE NOT NULL索引时使用它。
4 ref 连接不能基于关键字选择单个行,可能查找到多个符合条件的行。叫做ref是因为索引要跟某个参考值相比较。这个参考值或者是一个常数,或者是来自一个表里的多表查询的结果值。
5 fulltext 使用全文索引执行连接,这种一般用的比较少,因为对中文支持不好。
6 ref_or_null 与ref类似,但是MySQL必须在初次查找的结果里找出null条目,然后进行二次查找。
7 index_merge 说明索引合并优化被使用了。例如:SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
8 unique_subquery 在某些IN查询中使用此种类型,而不是常规的ref,如:value IN (SELECT primary_key FROM single_table WHERE some_expr)
9 index_subquery 在某些IN查询中使用此种类型,与unique_subquery类似,但是查询的是非唯一性索引:value IN (SELECT key_column FROM single_table WHERE some_expr)
10 range 只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。当关键字列使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、IN这些操作符与常量比较时,将会使用range类型。
11 index 全表扫描,只是扫描表的时候按照索引次序进行而不是行。此时Extra会显示:using index,主要优点就是避免了排序,但是开销仍然非常大。
12 all 最坏的情况,从头到尾全表扫描。一般可通过给适当的列添加索引以避免该类型。
6 possible_keys 可能使用到的索引,用于指出MySQL能在该表中使用哪些索引有助于查询。如果为空,说明没有可用的索引。
7 key MySQL实际从possible_key选择使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引。
8 key_len 使用的索引的长度。在不损失精确性的情况下,长度越短越好
9 ref 显示索引的哪一列被使用了
10 rows MYSQL认为必须检查的用来返回请求数据的行数
11 filtered 输出表所引用的行
10 子序号 extra 额外的信息。这列可能显示的信息非常多,这里我只列几个常见的,其它的请参见:
EXPLAIN Extra Information
1 Using where WHERE子句用于限制与下一个表匹配或发送到客户端的行。
2 Using filesort 表示MySQL会对结果使用一个外部索排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行序。MySQL中无法利用索引完成的排序操作称为“文件排序”。
3 Using temporary 表示MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
4 Using index 当type=index时,Extra会显示Using index,因为type=index代表全表扫描,虽然情况比type=all好一点,但仍需尽量避免。

以上为explain分析select语句的输出结果详解,对于select_type和type,都是从好到坏排列,所以尽量优化为好的类型就可以。

打赏

订阅评论
提醒
guest

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据

0 评论
内联反馈
查看所有评论
0
希望看到您的想法,请您发表评论x
()
x

扫码在手机查看
iPhone请用自带相机扫
安卓用UC/QQ浏览器扫

MySQL使用EXPLAIN分析sql语句