文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2024-04-02 19:55

关注

本篇文章为大家展示了怎样定位和解决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;
  1. 鸿蒙官方战略合作共建——HarmonyOS技术社区

  2.  EXPLAIN 和 DESCRIBE(可以简写成 DESC)都可以用来查看语句的执行计划,但通常使用 EXPLAIN 较多;

  3.  FORMAT 选项可以指定执行计划输出信息为 JSON 格式,而且包含一些更详细的指标说明;

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

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

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

  2.  id 值全相同,则按由上到下顺序执行;

  3.  id 值全不相同,则按 id 值大小,由大到小顺序执行;

  4.  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,还有如下几种情形:

 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                            |  |          |   |  +----------+-----------------------------------------------------------------+  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 显示的内容可以判断语句总体的查询效率。主要有以下几种类型:

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

  2.  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:使用索引全扫描。类似于全表扫描,只是扫描对象是索引,出现于以下两种情况:

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:

ref

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

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

  2.  如果是常量等值查询,则显示为 const;

  3.  如果是连接查询,则被驱动表的该字段会显示驱动表的所关联字段;

  4.  如果条件当中使用函数表达式,或者值导致条件字段发生隐式转换,这里显示为 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

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

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

  2.  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。简单说法如下:

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

  1.  type:展示了表的查询/连接类型,体现查询效率;

  2.  key/key_len:实际使用了什么索引,使用了哪些部分索引;

  3.  Extra:对执行计划步骤额外的说明,采用了哪些查询特性。 

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

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯