MySQL Performance Tuning Topic 2 (Explain execution plan usage details)

What is Explain execution plan

EXPLAIN performance bottlenecks in your query or structure.
Use the EXPLAIN keyword to simulate the optimizer’s execution of SQL statements to see how MySQL handles your SQL statements.
In our daily work, we sometimes encounter slow-executing SQL. We can use EXPLAIN correlation words to execute SQL. We can check whether the SQL statement uses indexes, whether it is a full table scan, etc. These can be checked with EXPLIN command. We can use the information we see to further refine it.
Add the EXPLAIN keyword before the SELECT statement, MySQL will set an identifier on the query, and when the query is executed, it will return information about the execution plan instead of executing the SQL statement.

Data preparation

Students table

Id = primary key, name = student name, update_time = update time

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# insert data
INSERT INTO `student` (`id`, `name`, `update_time`) VALUES (1,'a'.'the 2017-12-22 15:27:18'), (2,'b'.'the 2017-12-22 15:27:18'), (3,'c'.'the 2017-12-22 15:27:18');
Copy the code

The curriculum

Id = primary key, name = course name (common index)

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# insert data
INSERT INTO `course` (`id`, `name`) VALUES (1,'Java'), (2,'Python'), (3,'JS');
Copy the code

Student and course relationship table

Id = primary key, student_id = primary key of the student table, Course_id = primary key of the course table, remark = Remarks

Student_id and course_id are compound indexes

DROP TABLE IF EXISTS `student_course`;
CREATE TABLE `student_course` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_student_course_id` (`student_id`,`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# insert dataINSERT INTO ` student_course ` (` id `, ` student_id `, ` course_id `) VALUES (1,1,1),,1,2 (2), (3, 2, 1);Copy the code

EXPLAIN output result sets

Now that the tables are ready, let’s briefly use EXPLAIN to see what is available.

explain select * from actor; 
Copy the code

Executing the above statement results in a result set, which we’ll explain in more detail later.

When using EXPLAIN, each table in the query outputs one row of records (as many records as there are SElECT in an SQL).

EXPLAIN comes in two variants:

The explain extended:Additional query optimization information will be provided in addition to explain. Pass aftershow warnings;Command to get the optimized query, you can get the optimized query to see what the optimizer optimized. (This can optimize some simple SQL, slightly more complex or diy ヾ(= · ω · =)o)

explain extended select * from student WHERE id = 1; 
SHOW WARNINGS;
Copy the code

Explain Partitions: An additional partition field than Explain shows which partitions the query will access if the query is based on a partitioned table.

EXPLAIN the columns

Next we will show the information for each column in EXPLAIN.

The id column

The id column is numbered as the sequence number of the SELECT, and the number of ids increases in the order in which the SELECT appears. The larger the ID column is, the higher the execution priority is. If the IDS are the same, the execution is performed from the top down. If the ID is NULL, the execution is performed last.

Simple subquery

EXPLAIN SELECT (SELECT 1 FROM student LIMIT 1) FROM course;
Copy the code

Subquery in the FROM clause

EXPLAIN SELECT id FROM (SELECT id FROM student) AS stu;
Copy the code

Note: the version 5.7 I used is a simple query that returns no temporary tables.

When I executed this statement in my 5.6 test, the result set showed temporary tables.

Prior to 5.7, a temporary table was created that was used for external SELECT queries, as shown in the following figure:

DERIVER

Use merge or implementation to optimize derived table and view references (forgive me, Google translation).

The optimizer can handle derived table references using two strategies (which also apply to view references) :

Merge derived tables into external query blocks
Implement derived tables as internal temporary tables

Example 1: SELECT * FROM (SELECT * FROM t1) AS derived_t1; By merging derived table deriveD_T1, this query is executed like: SELECT * FROM T1; SELECT * FROM T1 JOIN (SELECT t2.f1 FROM T2) AS derived_t2 ON t1.f2=derived_t2.f1 WHERE t1.f1 > 0; SELECT t1.*, t2.f1 FROM T1 JOIN T2 ON T1.f2 =t2.f1 WHERE T1.f1 > 0;Copy the code

In a nutshell: The optimizer handles derived table and view references the same way: it avoids unnecessary implementations as much as possible, which can push conditions from external queries to derived tables and generate more efficient execution plans.

The union query

EXPLAIN SELECT 1 UNION ALL SELECT 1;
Copy the code

Note: As with the subquery in the FROM clause, versions after 5.7 have been optimized.

Select_type column

Select_type indicates whether the corresponding row is a simple or complex query, and if it is a complex query, which of the above three complex queries.

simple

A simple query

EXPLAIN SELECT * FROM student WHERE id = 2;
Copy the code

primary

The outermost select in a complex query

subquery

Subqueries included in select (not in the FROM clause)

derived

MySQL stores the results in a temporary table, also known as a derived table

Use this example to understand the primary and subQuery types

EXPLAIN SELECT (SELECT 1 FROM student WHERE id = 1) FROM (SELECT * FROM course WHERE id = 1) c;
Copy the code

union

The second and subsequent select in the union

union result

Select to retrieve results from the UNION temporary table

The table column

This column indicates which table is being accessed by the EXPLAIN row.

Type column (important)

This column represents the association type or access type, which is the approximate range of data row records that MySQL determines how to find rows in the table.

The order from best to worst is: system > const > eq_ref > ref > range > index > ALL. Generally speaking, it is necessary to ensure that the query reaches the range level, and ref is the best

NULL

MySQL can decompose queries during the optimization phase without having to access tables or indexes during the execution phase.

For example, if you select a minimum value in the index column, you can do this by looking up the index separately without accessing the table during execution.

EXPLAIN SELECT MIN(id) FROM student;
Copy the code

Const, system

MySQL can optimize part of a query and convert it to a constant (see the result of show Warnings).

Select * from primary key; select * from constant; select * from primary key;
System is a special case of const; it is system if only one tuple in the table matches

EXPLAIN extended SELECT * FROM (SELECT * FROM course WHERE id = 1) tmp;
Copy the code

show warnings; 
Copy the code

eq_ref

All parts of the primary key or unique key index are joined, and at most one qualifying record is returned. This is probably the best type of join besides const, and it does not appear in simple SELECT queries.

EXPLAIN SELECT * FROM student_course LEFT JOIN course ON student_course.course_id = course.id;
Copy the code

ref

Instead of using a unique index, eq_ref uses a normal index or a partial prefix of a unique index, which is compared to a value and may find multiple rows that match the condition.

Simple SELECT query, name is normal index (not unique index)

EXPLAIN SELECT * FROM course WHERE name = "Java";
Copy the code

Idx_student_course_id is the joint index of student_id and course_id. The student_id part of the left prefix of the student_course is used.

EXPLAIN SELECT student_id FROM student LEFT JOIN student_course ON student.id = student_course.student_id;
Copy the code

range

Range scanning usually occurs IN(), BETWEEN, >, <, >=, and so on. Use an index to retrieve rows in a given range.

EXPLAIN SELECT * FROM student WHERE id > 1;
Copy the code

index

Scan a full table index, which is usually faster than ALL. (Index is read from the index, while all is read from the hard disk)

EXPLAIN SELECT * FROM course;
Copy the code

ALL

Full table scan, which means MySQL has to look for rows from beginning to end. Normally this would need to be optimized by adding an index.

EXPLAIN SELECT * FROM student;
Copy the code

Possible_keys column

This column shows which indexes the query might use to look up.

When using EXPLAIN, possible_keys may have columns and key may be NULL. In this case, it is because there is not much data in the table. MySQL thinks that the index is not helpful for the query and selects the full table query.
If the column is NULL, there is no associated index. In this case, you can improve query performance by examining the WHERE clause to see if you can create an appropriate index, and then using EXPLAIN to see the effect.

The key column

This column shows which index MySQL actually uses to optimize access to the table.

If no index is used, the column is NULL.
If you want to force MySQL to use or ignore indexes in the possible_keys column, use force index and ignore index in the query.

Key_len column

This column shows the number of bytes used by MySQL in the index. This value can be used to figure out which columns are used in the index.

Key_len is computed as follows:

string

type The length of the

char(n) N Byte length

varchar(n) 2 bytes The value is a string of 3n + 2 bytes if it is UTF-8
Numeric types

type The length of the

tinyint 1 byte

tinyint 2 –

int 4 bytes

bigint 8 bytes
Time to type

type The length of the

date 3 bytes

timestamp 4 bytes

timestamp 8 bytes
If the field is allowed NULL, 1 byte is required to record whether the field is NULL

type	The length of the
char(n)	N Byte length
varchar(n)	2 bytes The value is a string of 3n + 2 bytes if it is UTF-8

type	The length of the
tinyint	1 byte
tinyint	2 –
int	4 bytes
bigint	8 bytes

type	The length of the
date	3 bytes
timestamp	4 bytes
timestamp	8 bytes

The maximum length of an index is 768 bytes. If the string is too long, MySQL extracts the first half of the string from the left prefix index.

Ref column

This column displays the columns or constants used by the table to find values in the index of the key column. Common examples are const (constant), field names (e.g. Student.id).

Rows column

This column is the number of rows that mysql expects to read and detect, note that this is not the number of rows in the result set.

Extra column (important)

This column presents additional information. Common important values are as follows:

Using index

The columns of the query are overwritten by the index, and the WHERE filter condition is the leading column of the index, which is a high performance performance. Typically, an overwrite index is used (the index contains all the fields of the query). For InnoDB, secondary indexes can improve performance.

EXPLAIN SELECT student_id FROM student_course WHERE student_id = 1;
Copy the code

Using where

The column in the query is not covered by the index, and WHERE filters the leading column that is not the index

EXPLAIN SELECT * FROM student WHERE NAME = 'Python';
Copy the code

Using where Using index

The columns of the query are overwritten by the index, and the WHERE filter condition is one of the index columns but not the leading column of the index, meaning that the data that matches the condition cannot be queried directly through the index lookup

EXPLAIN SELECT course_id FROM student_course WHERE course_id = 1;
Copy the code

NULL

The columns in the query are not covered by the index, and the WHERE filter condition is the leading column of the index, meaning that the index is used, but some fields are not covered by the index, and must be “back to the table”, not only using the index, but also not completely missing the index

EXPLAIN SELECT * FROM student_course WHERE student_id = 1;
Copy the code

Using index condition

Similar to Using WHERE, the columns of the query are not completely covered by the index; the WHERE condition is a range of leading columns

EXPLAIN SELECT * FROM student_course WHERE student_id > 1;
Copy the code

Using temporary

MySQL needs to create a temporary table to process the query. This situation is generally to optimize, the first is to think of using indexes to optimize.

Student. Name has no index and a temporary table is created to be DISTINCT

EXPLAIN SELECT DISTINCT name FROM student; 
Copy the code

Name = idx_name; extra = Using index;

EXPLAIN SELECT DISTINCT name FROM course;
Copy the code

Using filesort

MySQL uses an external index sort on the results, rather than reading rows from the table in index order. At this point, MySQL will browse all eligible records based on the join type, save the sort key and row pointer, then sort the key and retrieve the row information in order. Consider using indexes for optimization in this case as well.

Student. Name does not create index, browse student entire table, save sort keyword name and corresponding ID, then sort name and retrieve row records

EXPLAIN SELECT * FROM student ORDER BY name;
Copy the code

Name = idx_name; extra = Using index;

EXPLAIN SELECT * FROM course ORDER BY name;
Copy the code

Filtered column

Filtered is a half-scale value, and rows * filtered / 100 estimates the number of rows that will join the previous table in EXPLAIN (the previous table in EXPLAIN has an ID value smaller than the current table ID value).

Strike while the iron is hot

Prepare tables and data

DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT ' ' COMMENT 'name',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT 'age',
  `position` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'job',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Entry Time',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='Staff Record';

INSERT INTO employees(name,age,position,hire_time) VALUES('Ming', 22,'JAVA',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('li lei', 23,'Python',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Han Meimei', 23,'JS',NOW());
Copy the code

As shown above, name, age, and position are compound indexes

All values match

EXPLAIN SELECT * FROM employees WHERE NAME= 'Ming';
Copy the code

EXPLAIN SELECT * FROM employees WHERE name= 'Ming' AND age = 22;
Copy the code

EXPLAIN SELECT * FROM employees WHERE NAME= 'Ming' AND age = 22 AND POSITION ='JAVA';
Copy the code

As above, all three SQL statements use indexes

Optimal left prefix rule

EXPLAIN SELECT * FROM employees WHERE NAME = '李磊';
Copy the code

In the figure, you can see that the index is used.

EXPLAIN SELECT * FROM employees WHERE POSITION = 'Python';
Copy the code

In the figure, you can see that there is no index.

EXPLAIN SELECT * FROM employees WHERE age = 23 AND POSITION ='Python';
Copy the code

As you can see, there is no index.

Bottom line: If you index multiple columns, follow the leftmost prefix rule. Indicates that the query starts at the left-most front of the index and does not skip columns in the index.

Do nothing on the index column

Such as calculations, functions, (automatic or manual) type conversions, etc., can cause indexes to fail and move to full table scans

EXPLAIN SELECT * FROM employees WHERE left(NAME,1) = 'Korea';
Copy the code

In the figure, you can see that there is no index.

Columns to the right of range conditions in indexes cannot be used

EXPLAIN SELECT * FROM employees WHERE name= 'Ming' AND age = 22 AND position ='JAVA'; From the above experience, we know that this statement uses an indexCopy the code

EXPLAIN SELECT * FROM employees WHERE name= 'Ming' AND age > 22 AND position ='JAVA';
Copy the code

We can calculate the key_len length and see that the index uses only name and age. Position does not use the index, so if the range condition is used in the composite index, the column to the right of the index will be invalidated.

Using overwrite indexes

Queries that access only indexes (index columns contain query columns), reducing SELECT * statements

EXPLAIN SELECT * FROM employees WHERE name= 'Ming' AND age = 22 AND POSITION ='Java';
Copy the code

EXPLAIN SELECT NAME, age, POSITION FROM employees WHERE NAME= 'Ming' AND age = 22 AND POSITION ='Java';
Copy the code

As you can see, NULL has changed to Using index

Use does not equal (! Index invalid when = or <>)

EXPLAIN SELECT * FROM employees WHERE NAME ! ='Ming'
Copy the code

IS NULL, IS NOT NULL also cannot use the index

EXPLAIN SELECT * FROM employees WHERE name IS NOT NULL
Copy the code

Indexes that start with a wildcard character are invalidated

EXPLAIN SELECT * FROM employees WHERE name LIKE '% l'
Copy the code

Wildcard ending indexes are not invalidated

EXPLAIN SELECT * FROM employees WHERE name LIKE 'l %'
Copy the code

Or to use an overwrite index, the query field must be an overwrite index field

EXPLAIN SELECT NAME, age, position FROM employees WHERE name LIKE '% l'
Copy the code

Note: The overwrite index will be invalid if it points to a field vARCHAR (380) or above.

The index of a string without single quotation marks is invalid

EXPLAIN SELECT * FROM employees WHERE NAME = 1000;
Copy the code

Use or sparingly, as it will break indexes in many cases

EXPLAIN SELECT * FROM employees WHERE name = '李磊' OR NAME = 'Han Meimei';
Copy the code

conclusion

Suppose a, B, and C are composite indexes

WHERE clause	Index usage
WHERE a = ‘xiaoming’	To use a
WHERE a = ‘xiao Ming’ AND b = ‘Li Lei’	Apply to a and B
WHERE a = ‘xiao Ming’ AND b = ‘Li Lei’ AND C = ‘Han Mei mei’	Use a, B, and C
WHERE b = ‘li lei’ AND C = ‘han Meimei’ WHERE C = ‘han Meimei’	Don’t use
WHERE a = ‘xiao Ming’ AND c = ‘Han Mei mei’	A is used, c is not used, because B is interrupted
WHERE a = ‘xiao Ming’ AND b > ‘Li Li’ AND c = ‘han Mei mei’	A, b, C
WHERE a = ‘xiao Ming’ AND b = ‘li Lei’ AND c = ‘han Mei mei’	Use a, B, and C
WHERE a = ‘xiaoming’ AND b = ‘% lilei ‘AND c =’ han Meimei ‘	Only to a
WHERE a = ‘xiao Ming’ AND b = ‘% li Lei ‘AND c =’ han Mei mei ‘	Only to a
WHERE a = ‘xiao Ming’ AND b = ‘li Lei’ AND c = ‘han Mei mei’	Use a, B, and C