Shulou(Shulou.com)05/31 Report--

This article shows you how to locate and solve the problem of high load and slow execution of MySQL. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

MySQL's explain command statement provides information on how to execute SQL statements, parses the execution plan of SQL statements and shows that explain supports statements such as select, delete, insert, replace and update, as well as parsing of partitioned tables.

Explain is usually used to obtain the execution plan of select statements. Through the information displayed by explain, we can know the order of table queries, the way of table join, etc., and judge the efficiency of select execution according to this information, and decide whether to add indexes or rewrite SQL statements to optimize table join mode to improve execution efficiency. This article refers to the official document: EXPLAIN Output Format explains the content of the explain output, but also summarizes the unclear aspects of its previous use of explain.

The MySQL version used by the editor is the official community version 5.7.24.

Mysql root@localhost: (none) > select version (); +-+ | version () | +-+ | 5.7.24-log | +-+ 1 row in set Time: 0.066s

Main usage

{EXPLAIN | DESCRIBE} [EXTENDED | PARTITIONS | FORMAT= [TRADITIONAL | JSON]] SQL_STATEMENT

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Both EXPLAIN and DESCRIBE (which can be abbreviated to DESC) can be used to view the execution plan of a statement, but EXPLAIN is usually used more

The FORMAT option specifies that the output information of the execution plan is in JSON format and contains some more detailed metrics

The EXTENDED and PARTITIONS options allow you to output a more detailed description of the options, which are syntactically designed to be compatible with lower versions of MySQL and will be discarded in the future. You can use the EXPLAIN command by default.

test data

This document is based on the official MySQL sample database employee:Example Databases. The table used is as follows:

-- 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, `KEY 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_emp_ibfk_ 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` (`departments` (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

Output description

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 | | index | | PRIMARY | 4 | | 299512 | 100.0 | Using index | +- -+ 1 row in set Time: 0.026s

From the above example statement, we can see that there are 12 fields in the explain output, which are mainly described in the following table:

Id

Id is the select identifier, the order in which statements are executed in the execution plan. The occurrence of id values can be as follows:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

If the id values are all the same, they are executed in the order from top to bottom

If the id values are all different, they will be executed according to the size of the id value, in the order from the highest to the lowest.

If the id value is the same in part and different in part, then the high id value in the same group takes precedence (the order in which the id value is the same within the group).

-- id all the same mysql root@localhost:employees > explain select * from employees eCom deptabilemp dJEI 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 | | const | dept_name | dept_name | 122 | const | | 1 | 100.0 | Using index | | 1 | SIMPLE | e | | ALL | PRIMARY | 299512 | 100.0 | | 1 | SIMPLE | d | ref | PRIMARY | PRIMARY | 4 | employees.e.emp_no | 1 | 100.0 | | +-- | +- -- +-+ 3 rows in set Time: 0.018s-- id is all different 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.dept_ 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 | | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.0 | | | | 2 | SUBQUERY | d | | const | PRIMARY | Dept_no | PRIMARY | 16 | const Const | 1 | 100.0 | Using index | | 3 | SUBQUERY | de | | const | dept_name | dept_name | 122 | const | 1 | 100.0 | Using index | + -+ 3 rows in set Time: 0.027s-- id part is the same Partial differences mysql root@localhost:employees > explain select * from ^ where ^ Ie.emp _ no in (select d.emp_no from dept_emp d where d.dept_no = (select de.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 | | ref | PRIMARY Dept_no | dept_no | 12 | const | 33212 | 100.0 | Using index | 1 | PRIMARY | e | eq_ref | PRIMARY | PRIMARY | 4 | employees.d.emp_no | 1 | 100.0 | | 3 | SUBQUERY | de | const | dept_name | dept_name | 122 | const | | 1 | 100.0 | Using index | +-+- -+ 3 rows in set Time: 0.020s

Select_type

Select_type is the type of table query. According to the official documents, several common types are summarized as follows:

1. SIMPLE: the most common type of query, usually there is no subquery, union query is the SIMPLE type.

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 | | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.0 | | + -+ 1 row in set Time: 0.019s

2. PRIMARY and SUBQUERY: appear in statements that contain subqueries.

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 | Extra | +-+-- -+-+ | 1 | PRIMARY | d | | ref | dept_no | dept_no | 12 | const | 148054 | 100.0 | Using where | | 2 | SUBQUERY | de | | const | dept_name | | dept_name | 122 | const | 1 | 100.0 | Using index | +-- + | -+ 2 rows in set Time: 0.021s

3. UNION and UNION RESULT: appear in statements with union queries.

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 | | const | PRIMARY | PRIMARY | 12 | const | | 1 | 100.0 | | 2 | UNION | departments | | const | PRIMARY | PRIMARY | 12 | const | 1 | 100.0 | UNION RESULT | | ALL | | Using temporary | +-| -+ 3 rows in set Time: 0.020s

4. DEPENDENT UNION and DEPENDENT SUBQUERY: occurs when statement subqueries and union queries rely on external queries.

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 | | | ALL | | 299512 | 100.0 | Using where | | 2 | DEPENDENT SUBQUERY | d | | ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where | | 3 | DEPENDENT UNION | d | ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where | | | UNION RESULT | | ALL | Using temporary | +-+-| -+ 4 rows in set Time: 0.022s

5. DERIVED: occurs when a query involves generating temporary tables.

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 | ALL | | 5 | 100.0 | 2 | DERIVED | departments | | Index | | dept_name | 122 | | 9 | 100.0 | Using index | +-+ -+ 2 rows in set Time: 0.012s

6. Table

Refers to the table from which the data is currently obtained in the execution plan. If an alias is specified for the table, the alias is displayed, and if the data reading to the table is not involved, the NULL is displayed. There are also the following situations:

The data comes from the result set whose id is M and N in the union query

The data comes from the result set where the derived table id is N.

The data comes from the result set where the subquery id is N.

7. Partitions

Refers to the table from which the partition table currently obtains data in the execution plan, and if it is not a partition table, it is displayed as NULL.

-- the partition table salaries mysql root@localhost:employees > show create table salaries of the sample database employees +-+ | Table | Create Table | +- -- +-+ | salaries | CREATE TABLE `salaries` (| `emp_ no` int (11) NOT NULL) | `salary` int (11) NOT NULL, | `salary` 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 | | | | | 384341 | 11.11 | Using where | +----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+ 1 row in set Time: 0.023s type type 应该被认为是解读执行计划当中最重要的部分，根据 type 显示的内容可以判断语句总体的查询效率。主要有以下几种类型： 鸿蒙官方战略合作共建--HarmonyOS技术社区 system：表只有一行(系统表)，是 const 的一种特殊情况。 -- 测试表 departments_1 生成： mysql root@localhost:employees>

Create table departments_1 as select * from departments where dept_no='d005'; Query OK, 1 row affected Time: 0.107s mysql root@localhost:employees > alter table departments_1 add primary key (dept_no); Query OK, 0 rows affected mysql root@localhost:employees > create index idx_dept_name on departments_1 (dept_name); Query OK, 0 rows affected mysql root@localhost:employees > show create table departments_1\ G * * [1. Row] * * Table | departments_1 Create Table | CREATE TABLE `departments_ 1` (`dept_ no` char (4) NOT NULL, `dept_ name` varchar (40) DEFAULT NULL, PRIMARY KEY (`dept_ no`) KEY `idx_dept_ name` (`dept_ name`) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set Time: 0.010s-system table: mysql root@localhost:employees > explain select * from mysql.proxies_priv +-- + -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -+-+ | 1 | SIMPLE | proxies_priv | | system | 1 | 100.0 | | + -- + 1 row in set Time: 0.023s-- ordinary table: 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 | system | 1 | 100.0 | 2 | DERIVED | departments_1 | | const | PRIMARY | PRIMARY | 12 | | const | 1 | 100.0 | | + -+ 2 rows in set Time: 0.015s

2. Const: for an equivalent query with a primary key or a unique index key, only one row of data is returned.

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 | | const | PRIMARY | PRIMARY | 12 | const | 1 | 100.0 | +-+-- -+ 1 row in set Time: 0.018s

3. Eq_ref: for each row of data in the previous table, only one row of data in the current table can be matched. This is the best type of join query except for system and const. All parts of a primary key or a non-empty unique index can be used when joining, usually using the'= 'operator, and the comparison value can be a constant or a field expression that reads the table before the table.

Explain select * from departments dint departments1d1 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 | | index | PRIMARY | | idx_dept_name | 1 | 100.0 | Using index | | 1 | SIMPLE | d | | eq_ref | PRIMARY | PRIMARY | 12 | employees.d1.dept_no | 1 | 100.0 | | +-- +-| -+-+ 2 rows in set Time: 0.037s

4. Ref: for each row of data in the previous table, all rows that match index values are read from the current table. Compared with eq_ref, the join query field is not the primary key or unique index, or part of the left prefix of the composite index. If the join query matches a few rows of data, ref is a different wrong choice, and the operators commonly used are'=','=', and so on.

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 | | ref | dept_no | dept_no | 12 | const | 148054 | 100.0 | | + -+ 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 | | ALL | 1 | 100.0 | 1 | SIMPLE | | d | | ref | dept_no | dept_no | 12 | employees.d1.dept_no | 41392 | 100.0 | | +-- +-| -+-- + 2 rows in set Time: 0.012s

5. Ref_or_null: same type as ref, but includes a search for null values.

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 | | 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: index merge optimization is used for query. If the query specifies a condition that involves the use of multiple indexes, multiple indexes are merged.

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 | | index_merge | PRIMARY Dept_no | PRIMARY,dept_no | 4Page12 | | 148055 | 100.0 | Using union (PRIMARY,dept_no) Using where | | 2 | SUBQUERY | departments_1 | | index | | idx_dept_name | 123 | 1 | 100.0 | Using index | + -+- -+ 2 rows in set Time: 0.014s

7. Range: use the index scan condition to specify the data within the range. The commonly used operators are'>','.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.