怎样定位和解决MySQL负载高、执行慢的问题

发布时间：2021-11-30 14:52:23 来源：亿速云阅读：127 作者：柒染栏目：数据库

本篇文章为大家展示了怎样定位和解决MySQL负载高、执行慢的问题，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

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;

鸿蒙官方战略合作共建——HarmonyOS技术社区
EXPLAIN 和 DESCRIBE(可以简写成 DESC)都可以用来查看语句的执行计划，但通常使用 EXPLAIN 较多；
FORMAT 选项可以指定执行计划输出信息为 JSON 格式，而且包含一些更详细的指标说明；
EXTENDED 和 PARTITIONS 选项可以输出更详细选项说明，语法上是为了兼容低版本 MySQL，未来会废弃，默认使用 EXPLAIN 命令即可。

测试数据

本文基于 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 个字段，主要说明如下表：

怎样定位和解决MySQL负载高、执行慢的问题

id 为 select 标识符，语句在执行计划当中的执行顺序。id 值的出现有如下几种情况：

鸿蒙官方战略合作共建——HarmonyOS技术社区
id 值全相同，则按由上到下顺序执行；
id 值全不相同，则按 id 值大小，由大到小顺序执行；
id 值部分相同，部分不相同，则同组 id 值大的优先执行(组内 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 为表查询的类型，根据官方文档总结几种常见类型如下表：

怎样定位和解决MySQL负载高、执行慢的问题

1. SIMPLE：最常见的查询类型，通常情况下没有子查询、union 查询就是 SIMPLE 类型。

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

2. PRIMARY 和 SUBQUERY：在含有子查询的语句中会出现。

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

3. UNION 和 UNION RESULT：在有 union 查询的语句中出现。

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

4. DEPENDENT UNION 和 DEPENDENT SUBQUERY：当语句中子查询和 union 查询依赖外部查询会出现。

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

5. DERIVED：当查询涉及生成临时表时出现。

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

6. table

指执行计划当中当前是从哪张表获取数据，如果为表指定了别名，则显示别名，如果没有涉及对表的数据读取，则显示 NULL，还有如下几种情形：

<unionM,N>：数据来自union查询的id为M和N的结果集；
：数据来自派生表id为N的结果集；
：数据来自子查询id为N的结果集。

7. partitions

指执行计划中当前从分区表哪个表分区获取数据，如果不是分区表，则显示为 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 应该被认为是解读执行计划当中最重要的部分，根据 type 显示的内容可以判断语句总体的查询效率。主要有以下几种类型：

鸿蒙官方战略合作共建——HarmonyOS技术社区
system：表只有一行(系统表)，是 const 的一种特殊情况。

-- 测试表 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

2. const：对于主键或者唯一索引键的等值查询，只返回一行数据。

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

3. eq_ref：对于前表的每一行数据，都只能匹配当前表唯一一行数据。除了 system 与 const 之外这是最好的一种连接查询类型，主键或者是非空唯一索引的所有部分都可以在连接时被使用，通常使用的是'='操作符，比较值可以是一个常量，也可以是一个在该表之前读取该表的字段表达式。

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

4. ref：对于前表的每一行数据，都从当前表读取所有匹配索引值的行。与 eq_ref 相比，连接查询字段不是主键或者唯一索引，又或者是复合索引的部分左前缀，如果连接查询匹配的是少量几行数据，ref 是个不同错的选择，通常使用的运算符是'='、'<='或者'>='等。

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

5. ref_or_null：同ref类型，但是包含了对NULL值的搜索。

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

6. index_merge：使用了索引合并优化进行查询。如果查询指定条件涉及对多个索引的使用时，会将多个索引合并操作。

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

7. range：使用索引扫描条件指定范围内的数据。常用的操作符有 '>'、'<'、'is null'、'between'、'in' 和 'like' 等。

mysql root@localhost:employees> explain select de.* from dept_emp de,departments_1 d where dde.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

8. index：使用索引全扫描。类似于全表扫描，只是扫描对象是索引，出现于以下两种情况：

如果索引是覆盖索引，即索引包含查询所需要的所有表数据，就只扫描索引，并且在 Extra 中出现 Using index。通常情况下扫描索引比打描表要更快，因为索引一般比表来的小；
全表扫描采用索引的顺序来读取数据，本质上还是全表扫描，并且在 Extra 中不会出现 Using index，避免再进行排序消耗性能，因为索引本身就是排序好的。

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

9. all：使用全表扫描。

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

possible_keys

显示了 MySQL 在查找当前表中数据的时候可能使用到的索引，如果该字段值为 NULL，则表明没有相关索引可用。

key

显示了 MySQL 在实际查找数据时决定使用的索引，如果该字段值为 NULL，则表明没有使用索引。

key_len

显示了 MySQL 实际使用索引的键大小，单位字节。可以通过 key_len 的大小判断评估复合索引使用了哪些部分，如果 key 字段值为 NULL，则 key_len 的值也为 NULL。

几种常见字段类型索引长度大小如下，假设字符编码为 UTF8：

字段属性是否允许 NULL，如果允许 NULL，则需要额外增加一个字节；
字符型：

char(n)：3n个字节
varchar(n)：3n+2个字节

数值型：

tinyint：1 个字节
int：4 个字节
bigint：8 个字节

时间型：

1~2位：1 个字节
3~4位：2 个字节
5~6位：3 个字节
date：3 个字节
datetime：5 个字节+秒精度字节
timestamp：4 个字节+秒精度字节
秒精度字节(最大 6 位)：

ref

显示哪些常量或者字段被用于查询索引列键值，以获取表中数据行。

鸿蒙官方战略合作共建——HarmonyOS技术社区
如果是常量等值查询，则显示为 const；
如果是连接查询，则被驱动表的该字段会显示驱动表的所关联字段；
如果条件当中使用函数表达式，或者值导致条件字段发生隐式转换，这里显示为 func。

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

rows

显示预估需要查询的行数。对 InnoDB 表来说这是个预估值，并非是个准确值。

filtered

显示按表条件过滤的表行的估计百分比。

Extra

显示查询时的额外信息。常见的有如下几种：

鸿蒙官方战略合作共建——HarmonyOS技术社区
Using index

仅查询索引树就可以获取到所需要的数据行，而不需要读取表中实际的数据行。通常适用于 select 字段就是查询使用索引的一部分，即使用了覆盖索引。

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

2. Using index condition

显示采用了Index Condition Pushdown (ICP) 特性通过索引去表中获取数据。关于ICP特性可以参考官方文档：Index Condition Pushdown Optimization。简单说法如下：

如果开启 ICP 特性，部分 where 条件部分可以下推到存储引擎通过索引进行过滤，ICP 可以减少存储引擎访问基表的次数；
如果没有开启 ICP 特性，则存储引擎根据索引需要直接访问基表获取数据并返回给 server 层进行 where 条件的过滤。

-- 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

3. Using index for group-by

跟 Using index 访问表的方式类似，显示 MySQL 通过索引就可以完成对 GROUP BY 或 DISTINCT 字段的查询，而无需再访问表中的数据。

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

4. Using where

显示 MySQL 通过索引条件定位之后还需要返回表中获得所需要的数据。

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

5. Impossible WHERE

where 子句的条件永远都不可能为真。

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

6. Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)

在表联接过程当中，将先前表的部分数据读取到 join buffer 缓冲区中，然后从缓冲区中读取数据与当前表进行连接。

主要有两种算法：Block Nested Loop和Batched Key Access，关于这两种算法说明可以参考官方文档：Block Nested-Loop and Batched Key Access Joins，也可以参考另一篇博文说明：MySQL 查询优化之 Block Nested-Loop 与 Batched Key Access Joins。

-- 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

7. Using MRR

读取数据采用多范围读 (Multi-Range Read) 的优化策略。关于MRR特性也可以参考官方文档：Multi-Range Read Optimization

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

8. Range checked for each record (index map: N)

MySQL 在获取数据时发现在没有索引可用，但当获取部分先前表字段值时发现可以采用当前表某些索引来获取数据。index map展示的是一个掩码值，如 index map:0x19，对应二进制值为 11001，表示当前表索引编号为 1、4 和 5 号索引可能被用来获取数据，索引编号通过 SHOW INDEX 语句获得。

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

9. Select tables optimized away

MySQL 优化器能够确定以下两点：

最多只有一行记录被返回；
为了获取这一行数据，有一定的结果集需要获取。

当语句在优化器阶段过程中可以获取查询结果(如获取行数，只需要读取相应索引数据)，而无需再返回表中查询数据，可能会出现 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

10. Using temporary

MySQL 需要创建临时表来存放查询结果集。通常发生在有 GROUP BY 或 ORDER BY 子句的语句当中。

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

11. Using filesort

MySQL 需要对获取的数据进行额外的一次排序操作，无法通过索引的排序完成。通常发生在有 ORDER BY 子句的语句当中。

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 命令输出内容当中比较重要的是：

type：展示了表的查询/连接类型，体现查询效率；
key/key_len：实际使用了什么索引，使用了哪些部分索引；
Extra：对执行计划步骤额外的说明，采用了哪些查询特性。

上述内容就是怎样定位和解决MySQL负载高、执行慢的问题，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。

向AI问一下细节

怎样定位和解决MySQL负载高、执行慢的问题

猜你喜欢

最新资讯

相关推荐

相关标签