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

135次閱讀

共計(jì) 39369 個(gè)字符，預(yù)計(jì)需要花費(fèi) 99 分鐘才能閱讀完成。

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

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

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

丸趣 TV 小編使用的 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;

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

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

FORMAT 選項(xiàng)可以指定執(zhí)行計(jì)劃輸出信息為 JSON 格式，而且包含一些更詳細(xì)的指標(biāo)說明；

EXTENDED 和 PARTITIONS 選項(xiàng)可以輸出更詳細(xì)選項(xiàng)說明，語法上是為了兼容低版本 MySQL，未來會(huì)廢棄，默認(rèn)使用 EXPLAIN 命令即可。

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

本文基于 MySQL 官方示例數(shù)據(jù)庫 employee：Example Databases 進(jìn)行解析說明，使用到的表如下：

-- 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 個(gè)字段，主要說明如下表：

id

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

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

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

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

id 值部分相同，部分不相同，則同組 id 值大的優(yōu)先執(zhí)行 (組內(nèi) 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ù)官方文檔總結(jié)幾種常見類型如下表：

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：在含有子查詢的語句中會(huì)出現(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：當(dāng)語句中子查詢和 union 查詢依賴外部查詢會(huì)出現(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：當(dāng)查詢涉及生成臨時(shí)表時(shí)出現(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í)行計(jì)劃當(dāng)中當(dāng)前是從哪張表獲取數(shù)據(jù)，如果為表指定了別名，則顯示別名，如果沒有涉及對(duì)表的數(shù)據(jù)讀取，則顯示 NULL，還有如下幾種情形：

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

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

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

7. partitions

指執(zhí)行計(jì)劃中當(dāng)前從分區(qū)表哪個(gè)表分區(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 應(yīng)該被認(rèn)為是解讀執(zhí)行計(jì)劃當(dāng)中最重要的部分，根據(jù) type 顯示的內(nèi)容可以判斷語句總體的查詢效率。主要有以下幾種類型：

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

system：表只有一行 (系統(tǒng)表)，是 const 的一種特殊情況。

--  測(cè)試表  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：對(duì)于主鍵或者唯一索引鍵的等值查詢，只返回一行數(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：對(duì)于前表的每一行數(shù)據(jù)，都只能匹配當(dāng)前表唯一一行數(shù)據(jù)。除了 system 與 const 之外這是最好的一種連接查詢類型，主鍵或者是非空唯一索引的所有部分都可以在連接時(shí)被使用，通常使用的是 = 操作符，比較值可以是一個(gè)常量，也可以是一個(gè)在該表之前讀取該表的字段表達(dá)式。

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：對(duì)于前表的每一行數(shù)據(jù)，都從當(dāng)前表讀取所有匹配索引值的行。與 eq_ref 相比，連接查詢字段不是主鍵或者唯一索引，又或者是復(fù)合索引的部分左前綴，如果連接查詢匹配的是少量幾行數(shù)據(jù)，ref 是個(gè)不同錯(cuò)的選擇，通常使用的運(yùn)算符是 =、= 或者 = 等。

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 類型，但是包含了對(duì) 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)化進(jìn)行查詢。如果查詢指定條件涉及對(duì)多個(gè)索引的使用時(shí)，會(huì)將多個(gè)索引合并操作。

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：使用索引掃描條件指定范圍內(nèi)的數(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：使用索引全掃描。類似于全表掃描，只是掃描對(duì)象是索引，出現(xiàn)于以下兩種情況：

如果索引是覆蓋索引，即索引包含查詢所需要的所有表數(shù)據(jù)，就只掃描索引，并且在 Extra 中出現(xiàn) Using index。通常情況下掃描索引比打描表要更快，因?yàn)樗饕话惚缺韥淼男。?/p>

全表掃描采用索引的順序來讀取數(shù)據(jù)，本質(zhì)上還是全表掃描，并且在 Extra 中不會(huì)出現(xiàn) Using index，避免再進(jìn)行排序消耗性能，因?yàn)樗饕旧砭褪桥判蚝玫摹?nbsp;

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

通過以上各種主要類型的分析，可以總結(jié)出各個(gè)類型性能排序 (從左到右性能從高到低)：

system   const   eq_ref   ref   range   index   all

possible_keys

顯示了 MySQL 在查找當(dāng)前表中數(shù)據(jù)的時(shí)候可能使用到的索引，如果該字段值為 NULL，則表明沒有相關(guān)索引可用。

key

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

key_len

顯示了 MySQL 實(shí)際使用索引的鍵大小，單位字節(jié)。可以通過 key_len 的大小判斷評(píng)估復(fù)合索引使用了哪些部分，如果 key 字段值為 NULL，則 key_len 的值也為 NULL。

幾種常見字段類型索引長(zhǎng)度大小如下，假設(shè)字符編碼為 UTF8：

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

字符型：

char(n)：3n 個(gè)字節(jié)

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

數(shù)值型：

tinyint：1 個(gè)字節(jié)

int：4 個(gè)字節(jié)

bigint：8 個(gè)字節(jié)

時(shí)間型：

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

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

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

date：3 個(gè)字節(jié)

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

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

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

ref

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

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

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

如果是連接查詢，則被驅(qū)動(dòng)表的該字段會(huì)顯示驅(qū)動(dòng)表的所關(guān)聯(lián)字段；

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

顯示預(yù)估需要查詢的行數(shù)。對(duì) InnoDB 表來說這是個(gè)預(yù)估值，并非是個(gè)準(zhǔn)確值。

filtered

顯示按表?xiàng)l件過濾的表行的估計(jì)百分比。

Extra

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

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

Using index

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

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

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

-- employees 表創(chuàng)建復(fù)合索引 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 通過索引就可以完成對(duì) 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 子句的條件永遠(yuǎn)都不可能為真。

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)接過程當(dāng)中，將先前表的部分?jǐn)?shù)據(jù)讀取到 join buffer 緩沖區(qū)中，然后從緩沖區(qū)中讀取數(shù)據(jù)與當(dāng)前表進(jìn)行連接。

主要有兩種算法：Block Nested Loop 和 Batched Key Access，關(guān)于這兩種算法說明可以參考官方文檔：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)化策略。關(guān)于 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ù)時(shí)發(fā)現(xiàn)在沒有索引可用，但當(dāng)獲取部分先前表字段值時(shí)發(fā)現(xiàn)可以采用當(dāng)前表某些索引來獲取數(shù)據(jù)。index map 展示的是一個(gè)掩碼值，如 index map:0x19，對(duì)應(yīng)二進(jìn)制值為 11001，表示當(dāng)前表索引編號(hào)為 1、4 和 5 號(hào)索引可能被用來獲取數(shù)據(jù)，索引編號(hào)通過 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)化器能夠確定以下兩點(diǎn)：

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

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

當(dāng)語句在優(yōu)化器階段過程中可以獲取查詢結(jié)果 (如獲取行數(shù)，只需要讀取相應(yīng)索引數(shù)據(jù))，而無需再返回表中查詢數(shù)據(jù)，可能會(huì)出現(xiàn) Select tables optimized away。例如針對(duì) MyISAM 引擎的表，使用 select count(*) 獲取表的總行數(shù)，而且又沒有 where 子句或者條件總是為真，也沒有 GROUP BY 子句時(shí)，其實(shí)就包含了以上的條件且隱式含有 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í)行計(jì)劃  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 需要?jiǎng)?chuàng)建臨時(shí)表來存放查詢結(jié)果集。通常發(fā)生在有 GROUP BY 或 ORDER BY 子句的語句當(dāng)中。

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 需要對(duì)獲取的數(shù)據(jù)進(jìn)行額外的一次排序操作，無法通過索引的排序完成。通常發(fā)生在有 ORDER BY 子句的語句當(dāng)中。

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

以上內(nèi)容總結(jié)了 MySQL 獲取執(zhí)行計(jì)劃 explain 命令執(zhí)行時(shí)輸出的主要字段說明，還有許多未仔細(xì)說明的參數(shù)和選項(xiàng)，以后還需多多實(shí)踐總結(jié)。可以看出 explain 命令輸出內(nèi)容當(dāng)中比較重要的是：

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

key/key_len：實(shí)際使用了什么索引，使用了哪些部分索引；

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

上述內(nèi)容就是怎樣定位和解決 MySQL 負(fù)載高、執(zhí)行慢的問題，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道。

正文完