An introduction to the

Performance optimization is an indispensable part of the work of a developer or DBA. This article introduces a tool to help us check whether the SQL execution plan is optimized. By explaining the results, we can determine whether the SQL is using the correct index.

The second is introduced

2.1 Test Environment

MySQL 5.7

create table a(  id   bigint(20)  NOT NULL AUTO_INCREMENT,  name varchar(50) NOT NULL DEFAULT ' ', age INT(11) DEFAULT 0, primary key (id), key idx_name (name)) engine = innodb default charset= utf8; insert into a (name, age) values('yy'11); insert into a (name, age) values('xx', 25); insert into a (name, age) values('yz', 23); insert into a (name, age) values('zhangcan', 32); insert into a (name, age) values('lisi', 18); insert into a (name, age) values('boshi', 62); insert into a (name, age) values('taisen'52); insert into a (name, age) values('liuxiang', 32); insert into a (name, age) values('malong', 23); insert into a (name, age) values('jingtian', 28); create table b( id bigint(20) NOT NULL AUTO_INCREMENT, sid int not null default 0, name varchar(50) NOT NULL DEFAULT' ', score INT(11) DEFAULT 0, primary key (id), key idx_sid(sid), key idx_name (name)) engine = innodb default charset= utf8; insert into b (sid,name, score) values(1,'yy', 99); insert into b (sid,name, score) values(1,'yy', 99); insert into b (sid,name, score) values(1,'yy', 99); insert into b (sid,name, score) values(2,'xx', 95); insert into b (sid,name, score) values(2,'xx', 95); insert into b (sid,name, score) values(3,'yz', 93); insert into b (sid,name, score) values(3,'yz', 93); insert into b (sid,name, score) values(4,'zhangcan', 90); insert into b (sid,name, score) values(5,'lisi', 88); insert into b (sid,name, score) values(5,'lisi', 80); insert into b (sid,name, score) values(5,'lisi', 78); insert into b (sid,name, score) values(6,'boshi', 83); insert into b (sid,name, score) values(6,'boshi', 80); insert into b (sid,name, score) values(6,'boshi', 92); insert into b (sid,name, score) values(7,'taisen', 85); insert into b (sid,name, score) values(8,'liuxiang', 81); insert into b (sid,name, score) values(9,'malong', 92); insert into b (sid,name, score) values(10,'jingtian', 78); insert into b (sid,name, score) values(10,'jingtian', 90); insert into b (sid,name, score) values(10,'jingtian', 88); insert into b (sid,name, score) values(10,'jingtian', 93);Copy the code

2.2 Result Introduction

The results of executing explian are as follows:

test >explain select * from  a where id=3 \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: a   partitions: NULL         type: constpossible_keys: PRIMARY          key: PRIMARY      key_len: 8          ref: const         rows: 1     filtered: 100.00        Extra: NULLCopy the code

As you can see from the above output, possiblekeys are ID, Type, tabl, selectType, key, key_len, ref, rows, and Extra. This article focuses on the EXPLian output using the SELECT statement as an example.

Three reading

3.1 id

The serial number or identifier of a query statement. Each query statement including subqueries is assigned an ID, which indicates the order in which the select clause or operation table is executed in the query

1 The ID values are the same

The same ID value generally occurs in the scenario where multiple tables are associated, and the table access sequence is from top to bottom.

Both ids 1 access table B first and then table A.

2 The ID value is different

If the id value is different, from large to small, the larger the value, the earlier the execution or access.

From the results, the subquery with id 2 is executed first. Select * from a where id=1;

A. sid (select sid from b where id=10); What would be the result of explai?

3 ID contains the same and different cases.

In this case, two tables or subqueries join the table and then associate the query with a third table. Such as

EXPLAIN SELECT t2.* FROM(SELECT t3.id FROM t3 WHERE t3.other_column = ' ') s1,t2 WHERE s1.id = t2.id; +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+| id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | || 1 | PRIMARY | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 | || 2 | DERIVED | t3 | ALL | NULL | NULL | NULL | NULL | 5 | Usingwhere |+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+Copy the code

As can be seen from the analysis result, the statement with the largest ID 2 is passed first, that is, the statement in the parentheses is passed first. Alter table t3; alter table t3; alter table t3; alter table t3; alter table t3; Table T2 is the last query performed.

The 5.7 optimizer does a lot of optimizations for subqueries. I didn’t simulate scenario 3 myself, so I used the examples from the web.

3.2 select_Type (Type of data read operation)

SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION, and UNION RESULT are common queries, combined queries, subqueries, and other complex queries.

SIMPLE: The simplest query that does not contain subqueries or unions.

PRIMARY: If the query contains any complex subqueries, the outermost query is marked as PRIMARY, the last statement to be executed.

SUBQUERY: Contains subqueries in a SELECT FROM or WHERE list

DERIVED: Subqueries contained in the FROM list are labeled as DERIVED, and MySQL performs these subqueries recursively, putting the results in temporary tables.

UNION: If the second SELECT appears after the UNION, it is marked as UNION; If UNION is included in the subquery of the FROM clause, the outer SELECT is marked as DERIVED

UNION RESULT: A combination of two UNION statements.

DEPENDENT SUBQUERY: The first SELECT in a SUBQUERY, DEPENDENT on the external query. That is, subqueries depend on the results of outer queries. Be careful when this value is present; you may need to optimize the subquery using join.

3.3 table (tables involved in the query or derived tables)

The value is the name of the table or the alias of the table, indicating which table to access,

When there are subqueries in from, the table name is of the form derivedN, where N points to the subquery, which is the next column in the explain result

When there is a union result, the table name is of the form union, 1,2, etc. 1,2 represents the query id participating in the union

Note that MySQL treats these tables just like normal tables, but these temporary tables do not have any indexes. Performance problems can occur with large amounts of data.

3.4 Type (How to access the table)

The results, from best to worst, are as follows:

system > const > eq_ref > ref > range > index > ALL

System: indicates that the result set has only one row. This isa special case of the const join type, and the table must be either myISAM or a Memory engine. For an InnoDB storage engine, type is displayed as const.

Const: Indicates that a primary key or unique key lookup matches at most one row of data.

eq_ref: This type is most commonly used in multi-table JOIN scenarios. The primary key or unique key is used to access a table.

For each row in front table B, only one row in back table A can be matched. The comparison operation is usually =, indicating high query efficiency.

Ref: This type usually occurs in queries where SQL uses non-unique or non-primary key indexes, or where the left-most prefix rule indexes are used. For example, in this example, a query of type ref is used:

Range: indicates the WHERE condition. The index range query is used to obtain some data records in the table based on the index field range. This type IS often appear IN the < >, >, > =, <, < =, IS NULL, the < = >, BETWEEN, IN () operation. When type is range, the ref field is NULL.

Index: full index scan, similar to ALL except that ALL scans ALL table records, while index scans ALL index records instead of data.

The index type is usually present in an overwrite index, so the data to be queried can be accessed directly from the index, without having to go back to the table to scan the data. The Extra field displays Using Index.

Another option is to access data in index order during a full table scan. Using index is not displayed in Extra.

ALL: indicates that the execution plan selects a full table scan, unless the data volume is very small, such as 100 (do not ask ‘101 ok ‘, encounter too high count 1000 rows blocked database). When the execution plan type is ALL, we try to modify the index to make use of the index.

3.5 possible_keys

Possible_keys specifies the possible_keys index that can be used by MySQL when querying. Note that even though some indexes appear in Possible_keys, it doesn’t mean that the index will actually be used by MySQL. The key field determines which indexes are used in the MySQL query.

3.6 the key

This field is the actual index used by MySQL in the current query.

3.7 key_len

Key_len indicates the number of bytes of index length selected by the execution plan, which is usually used to determine how many columns are selected for the federated index

Here the key_len size is computed as follows:

Generally, key_len is equal to the length of the index column type in bytes, for example, int is 4 bytes and bigint is 8 bytes.

If the value is a string, consider the character set. For example, CHAR(30) UTF8. Key_len must be at least 90 bytes.

If the column type definition allows NULL, its key_len needs an additional bytes.

If the column type is variable length, such as VARCHAR (TEXT\BLOB does not allow indexes to be created for whole columns; creating partial indexes is also considered a dynamic column type), its key_len needs an additional 2 bytes.

Id bigint is 8 bytes so key_len=8

Table A character set UTF8, name=’lisi’ name= vARCHar (50) key_len=50*3+2=152

3.8 rows

Rows is also an important field. Based on the statistics, the MySQL query optimizer estimates the number of rows of data that the SQL needs to scan and read to find the result set. In principle, as few rows as possible. Remember that this is not an exact value.

3.9 extra

As the name suggests, this column prompts additional information about the optimization execution plan, including the following:

Using index

When Using index appears in extra, it means that the SQL uses an overwrite index scan, that is, the required data can be obtained from only accessing the index without returning to the table.

Using where

When Using WHERE appears in extra, it indicates that the SQL is retrieving data from the table. What is a return table? Access to the index alone is not enough to get the required data, you need to access the page of the table.

If present with Using index, the WHERE condition locates the data through the index, then returns to the table, and then filters the required data.

Using filesort

Using filesort indicates that an additional sort occurred without using an index, and may be accompanied by using temporary. Using filesort also uses temporary table sorting.

Using MRR, Using index condition, Using index for group-by, etc.

Four summarizes

This article explains how to understand explain execution results based on case studies. It is hoped that you need to evaluate the SQL execution plan of friends.