怎樣定位和解決MySQL負載高、執(zhí)行慢的問題

本篇文章為大家展示了怎樣定位和解決MySQL負載高、執(zhí)行慢的問題，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

MySQL的 explain 命令語句提供了如何執(zhí)行 SQL 語句的信息，解析 SQL 語句的執(zhí)行計劃并展示，explain 支持 select、delete、insert、replace 和 update 等語句，也支持對分區(qū)表的解析。

通常 explain 用來獲取 select 語句的執(zhí)行計劃，通過 explain 展示的信息我們可以了解到表查詢的順序，表連接的方式等，并根據(jù)這些信息判斷 select 執(zhí)行效率，決定是否添加索引或改寫 SQL 語句優(yōu)化表連接方式以提高執(zhí)行效率。本文參考官方文檔：EXPLAIN Output Format 對 explain 輸出的內容進行說明，同時也對自己之前使用 explain 不清晰的方面進行總結。

小編使用的 MySQL 版本為官方社區(qū)版 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. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術社區(qū)

2.  EXPLAIN 和 DESCRIBE(可以簡寫成 DESC)都可以用來查看語句的執(zhí)行計劃，但通常使用 EXPLAIN 較多；

3.  FORMAT 選項可以指定執(zhí)行計劃輸出信息為 JSON 格式，而且包含一些更詳細的指標說明；

4.  EXTENDED 和 PARTITIONS 選項可以輸出更詳細選項說明，語法上是為了兼容低版本 MySQL，未來會廢棄，默認使用 EXPLAIN 命令即可。

測試數(shù)據(jù)

本文基于 MySQL 官方示例數(shù)據(jù)庫 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 個字段，主要說明如下表：

id

id 為 select 標識符，語句在執(zhí)行計劃當中的執(zhí)行順序。id 值的出現(xiàn)有如下幾種情況：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術社區(qū)

2.  id 值全相同，則按由上到下順序執(zhí)行；

3.  id 值全不相同，則按 id 值大小，由大到小順序執(zhí)行；

4.  id 值部分相同，部分不相同，則同組 id 值大的優(yōu)先執(zhí)行(組內 id 值相同的順序執(zhí)行)。 

-- 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 為表查詢的類型，根據(jù)官方文檔總結幾種常見類型如下表：

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：在含有子查詢的語句中會出現(xiàn)。

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 查詢的語句中出現(xiàn)。

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 查詢依賴外部查詢會出現(xiàn)。

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：當查詢涉及生成臨時表時出現(xiàn)。

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

指執(zhí)行計劃當中當前是從哪張表獲取數(shù)據(jù)，如果為表指定了別名，則顯示別名，如果沒有涉及對表的數(shù)據(jù)讀取，則顯示 NULL，還有如下幾種情形：

•  <unionM,N>：數(shù)據(jù)來自union查詢的id為M和N的結果集；

•  ：數(shù)據(jù)來自派生表id為N的結果集；

•  ：數(shù)據(jù)來自子查詢id為N的結果集。

 7. partitions

指執(zhí)行計劃中當前從分區(qū)表哪個表分區(qū)獲取數(shù)據(jù)，如果不是分區(qū)表，則顯示為 NULL。

-- 示例數(shù)據(jù)庫 employees 的分區(qū)表 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 應該被認為是解讀執(zhí)行計劃當中最重要的部分，根據(jù) type 顯示的內容可以判斷語句總體的查詢效率。主要有以下幾種類型：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術社區(qū)

2.  system：表只有一行(系統(tǒng)表)，是 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 -- 系統(tǒng)表：  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：對于主鍵或者唯一索引鍵的等值查詢，只返回一行數(shù)據(jù)。

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：對于前表的每一行數(shù)據(jù)，都只能匹配當前表唯一一行數(shù)據(jù)。除了 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：對于前表的每一行數(shù)據(jù)，都從當前表讀取所有匹配索引值的行。與 eq_ref 相比，連接查詢字段不是主鍵或者唯一索引，又或者是復合索引的部分左前綴，如果連接查詢匹配的是少量幾行數(shù)據(jù)，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：使用了索引合并優(yōu)化進行查詢。如果查詢指定條件涉及對多個索引的使用時，會將多個索引合并操作。

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：使用索引掃描條件指定范圍內的數(shù)據(jù)。常用的操作符有 '>'、'<'、'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：使用索引全掃描。類似于全表掃描，只是掃描對象是索引，出現(xiàn)于以下兩種情況：

•  如果索引是覆蓋索引，即索引包含查詢所需要的所有表數(shù)據(jù)，就只掃描索引，并且在 Extra 中出現(xiàn) Using index。通常情況下掃描索引比打描表要更快，因為索引一般比表來的?。?/p>

•  全表掃描采用索引的順序來讀取數(shù)據(jù)，本質上還是全表掃描，并且在 Extra 中不會出現(xiàn) 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 在查找當前表中數(shù)據(jù)的時候可能使用到的索引，如果該字段值為 NULL，則表明沒有相關索引可用。

key

顯示了 MySQL 在實際查找數(shù)據(jù)時決定使用的索引，如果該字段值為 NULL，則表明沒有使用索引。

key_len

顯示了 MySQL 實際使用索引的鍵大小，單位字節(jié)?？梢酝ㄟ^ key_len 的大小判斷評估復合索引使用了哪些部分，如果 key 字段值為 NULL，則 key_len 的值也為 NULL。

幾種常見字段類型索引長度大小如下，假設字符編碼為 UTF8：

•  字段屬性是否允許 NULL，如果允許 NULL，則需要額外增加一個字節(jié)；

•  字符型：
  

•   char(n)：3n個字節(jié)

•   varchar(n)：3n+2個字節(jié)

•  數(shù)值型：
  

•   tinyint：1 個字節(jié)

•   int：4 個字節(jié)

•   bigint：8 個字節(jié)

•  時間型：
  

•   1~2位：1 個字節(jié)

•   3~4位：2 個字節(jié)

•   5~6位：3 個字節(jié)

•   date：3 個字節(jié)

•   datetime：5 個字節(jié)+秒精度字節(jié)

•   timestamp：4 個字節(jié)+秒精度字節(jié)

•   秒精度字節(jié)(最大 6 位)：

ref

顯示哪些常量或者字段被用于查詢索引列鍵值，以獲取表中數(shù)據(jù)行。

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術社區(qū)

2.  如果是常量等值查詢，則顯示為 const；

3.  如果是連接查詢，則被驅動表的該字段會顯示驅動表的所關聯(lián)字段；

4.  如果條件當中使用函數(shù)表達式，或者值導致條件字段發(fā)生隱式轉換，這里顯示為 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

顯示預估需要查詢的行數(shù)。對 InnoDB 表來說這是個預估值，并非是個準確值。

filtered

顯示按表條件過濾的表行的估計百分比。

Extra

顯示查詢時的額外信息。常見的有如下幾種：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術社區(qū)

2.  Using index

僅查詢索引樹就可以獲取到所需要的數(shù)據(jù)行，而不需要讀取表中實際的數(shù)據(jù)行。通常適用于 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) 特性通過索引去表中獲取數(shù)據(jù)。關于ICP特性可以參考官方文檔：Index Condition Pushdown Optimization。簡單說法如下：

•  如果開啟 ICP 特性，部分 where 條件部分可以下推到存儲引擎通過索引進行過濾，ICP 可以減少存儲引擎訪問基表的次數(shù)；

•  如果沒有開啟 ICP 特性，則存儲引擎根據(jù)索引需要直接訪問基表獲取數(shù)據(jù)并返回給 server 層進行 where 條件的過濾。 

-- employees表創(chuàng)建復合索引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 字段的查詢，而無需再訪問表中的數(shù)據(jù)。

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 通過索引條件定位之后還需要返回表中獲得所需要的數(shù)據(jù)。

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)

在表聯(lián)接過程當中，將先前表的部分數(shù)據(jù)讀取到 join buffer 緩沖區(qū)中，然后從緩沖區(qū)中讀取數(shù)據(jù)與當前表進行連接。

主要有兩種算法：Block Nested Loop和Batched Key Access，關于這兩種算法說明可以參考官方文檔：Block Nested-Loop and Batched Key Access Joins，也可以參考另一篇博文說明：MySQL 查詢優(yōu)化之 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

讀取數(shù)據(jù)采用多范圍讀 (Multi-Range Read) 的優(yōu)化策略。關于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 在獲取數(shù)據(jù)時發(fā)現(xiàn)在沒有索引可用，但當獲取部分先前表字段值時發(fā)現(xiàn)可以采用當前表某些索引來獲取數(shù)據(jù)。index map展示的是一個掩碼值，如 index map:0x19，對應二進制值為 11001，表示當前表索引編號為 1、4 和 5 號索引可能被用來獲取數(shù)據(jù)，索引編號通過 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 優(yōu)化器能夠確定以下兩點：

•  最多只有一行記錄被返回；

•  為了獲取這一行數(shù)據(jù)，有一定的結果集需要獲取。

當語句在優(yōu)化器階段過程中可以獲取查詢結果(如獲取行數(shù)，只需要讀取相應索引數(shù)據(jù))，而無需再返回表中查詢數(shù)據(jù)，可能會出現(xiàn) Select tables optimized away。例如針對 MyISAM 引擎的表，使用 select count(*) 獲取表的總行數(shù)，而且又沒有 where 子句或者條件總是為真，也沒有 GROUP BY 子句時，其實就包含了以上的條件且隱式含有 GROUP BY 分組的效果。

-- 創(chuàng)建 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  -- 獲取執(zhí)行 count(*) 查詢行數(shù)執(zhí)行計劃  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 需要創(chuàng)建臨時表來存放查詢結果集。通常發(fā)生在有 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 需要對獲取的數(shù)據(jù)進行額外的一次排序操作，無法通過索引的排序完成。通常發(fā)生在有 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 獲取執(zhí)行計劃 explain 命令執(zhí)行時輸出的主要字段說明，還有許多未仔細說明的參數(shù)和選項，以后還需多多實踐總結?？梢钥闯?explain 命令輸出內容當中比較重要的是：

1.  type：展示了表的查詢/連接類型，體現(xiàn)查詢效率；

2.  key/key_len：實際使用了什么索引，使用了哪些部分索引；

3.  Extra：對執(zhí)行計劃步驟額外的說明，采用了哪些查詢特性。 

上述內容就是怎樣定位和解決MySQL負載高、執(zhí)行慢的問題，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業(yè)資訊頻道。