This article will help you quickly understand MySQL execution plan

Usually when querying the slow query SQL statement, you will use the EXPLAIN command to view the execution plan of the SQL statement, through The returned information can help us understand how the Mysql optimizer executes the SQL statement, and analysis can help us provide optimization ideas.

1. Explain function The explain command is mainly used to view the execution plan of SQL statements. This command can simulate the optimizer's execution of SQL query statements and can help us write and Optimize SQL. So what specific information can explain provide to help us optimize SQl?

Table reading sequence

Operation type of data reading operation

Which indexes Can be used

Which indexes are actually used

References between tables

Each table How many rows in the table are queried by the optimizer

2. Explain how to use Usage: explain the sql to be executed

Explain will return a list of execution plans for the SQL to be executed. The list contains 12 fields. The fields together describe the method that the SQL will take in the execution plan. implement. The following list describes the field meanings of the execution plan table in detail:

Field name Description id The sequence number of the select statement query determines the reading order of the table select_type The type of query, that is Operation type of data read operation table query table name partitions table Partition type Access type possible_keys Indices that can be used. If there is an index on the field involved in the query, the index will be listed, but it may not be actually used by the query. If this field is null but the field key is not null, this situation means that there is no secondary index tree that can be used when searching, but the secondary index contains the fields that need to be queried, so the clustered index (clustered index) is no longer searched. The cluster index is relatively large), and the secondary index tree is scanned instead (the secondary index tree is relatively small), and at this time the general access type is index, and the entire index tree is scanned. key The index used for the actual scan. If it is null, the index is not used; if a covering index is used in the query, the index only appears in the key list; key_len used in the index Number of bytes. The length of the index used in the query can be calculated through this column. Without losing accuracy, the shorter the length, the better; the value displayed by key_len is the maximum possible length of the index field, not the actual length used, that is, key_len is based on the table The definition is calculated, not retrieved from the table; ref shows which column of the index is used. If possible, it is a constant, which columns or constants are used to find the value on the index column; rows According to table statistics and index selection, Roughly estimate the number of rows that need to be read to find the required records; filtered The percentage of remaining data after filtering by search conditions. Extra Contains important extra information that does not fit in other columns # #3. Key field analysis (1)id The sequence number of the select statement query, including a set of numbers, indicating that the select is executed in the query The order of clauses or operation tables, it has three situations:

Type name Description ##The id is the same The execution order is from top to bottom The id is different If it is a subquery, the serial number of the id will be incremented. The larger the id value, the higher the priority and will be executed first. #The id is the same but different and exists at the same time. If the id is the same, it can be considered as a group, from top to bottom Execute in the following order. In all groups, the larger the id value, the higher the priority and the earlier it is executed (2) select_type is the operation type of data reading operation. It has the following types:

Type name Description simple Simple select query, the query does not contain subqueries or unions; primary If the query contains any complex subqueries, the outermost query will be marked; subquery The subquery is included in the select or where list; dependent subquery The first SELECT in the subquery depends on the outer query. That is, the subquery depends on the results of the outer query. derived The subqueries contained in the from list are marked as DERIVED (derived tables). MySQL will recursively execute these subqueries and put the results in a temporary table. ; union If the second select appears after union, it is marked as union. If union is included in the subquery of the from clause, the outer layer select will be marked as DERIVED; union result Get the result of the select query from the union table (that is, the union merged result set); meterized Materialized table, when a subquery is associated with a query, the subquery results are stored in the materialized temporary table, and then matched with the main table based on the data in the temporary table. dependent union The second or subsequent query statement in UNION depends on the outer query (3) table The name of the query table displayed. If the query uses an alias, then the alias is displayed here. If it does not involve operations on the data table, then this is displayed as null, or one of the following:

Type name Description N> indicates that this is a temporary table, and the following N is the id in the execution plan, indicating that the results are generated from this query. ## M , N > N &gt ;Similarly, it is also a temporary table, indicating that this result comes from the result set with IDs M and N of the union query. N > This row refers to the value N of the result id of the row with the materialized subquery. (4)partitions The query will match the partitions of the records. The value NULL is used for non-partitioned tables.

(5)type from best to worst:

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

In addition to all , all other type can use indexes, except index_merge In addition, other type can only use one index.

Let’s create a series of tables ourselves to experiment:

SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------- -- Table structure for goods -- ---------------------------- DROP TABLE IF EXISTS `goods`; CREATE TABLE `goods` ( `id` int(11) NOT NULL, `sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ---------------------------- -- Records of goods -- ---------------------------- INSERT INTO `goods` VALUES (1, 'sn123456', '衣服');

-- ---------------------------- -- Table structure for sku -- ---------------------------- DROP TABLE IF EXISTS `sku`; CREATE TABLE `sku` ( `id` int(11) NOT NULL, `goods_id` int(11) NOT NULL, `status` int(11) NOT NULL, `deleted` int(11) NOT NULL, `barcode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `index_2`(`name`) USING BTREE, INDEX `index_1`(`goods_id`, `status`, `deleted`, `barcode`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ---------------------------- -- Records of sku -- ---------------------------- INSERT INTO `sku` VALUES (1, 1, 1, 0, 'kt123456', '黑色');

SET FOREIGN_KEY_CHECKS = 1; system The table has only one row of records (equal to the system table). This is a special case of the const type. It does not usually appear and this can be ignored;

const means it is found through the index once, const is used to compare the primary key or unique index. Because only one row of records is matched, it is very fast. If you place the primary key in the where list, mysql can convert the query into a constant;

EXPLAIN SELECT * FROM sku WHERE id=1;复制代码

eq_ref unique Sexual index scan, for each index key, only one record in the table matches it, often used for primary key or unique index scan; this type usually appears in join equivalent queries of multiple tables, which means that for each result of the previous table, Only one row of results in the latter table can be matched, and the query efficiency is high.

EXPLAIN SELECT * FROM sku,goods WHERE sku.goods_id=goods.id;

ref Non-unique index scan returns all rows matching a single value, which is essentially an index access, it returns all rows that match a single value, however, it may find multiple matching rows, so it should be a hybrid of a find and a scan;

EXPLAIN SELECT * FROM sku WHERE goods_id=1;

ref_or_null Secondary index equal value comparison also limits is null.

EXPLAIN SELECT * FROM sku WHERE name='123456' or name IS NULL;

range Retrieve only rows in a given range, using an index to select the rows, the key column shows which index is used , usually queries such as between, , in, etc. appear in your where statement; this range index scan is better than a full table scan, because it only needs to start at a certain point in the index and end at another point. One point, no need to scan all indexes;

EXPLAIN SELECT * FROM sku WHERE id BETWEEN 1 and 10;

index The difference between index and all is that the index type only traverses the index tree. This Usually faster than all because index files are usually smaller than data files; that is to say, although all and index both read and write tables, index reads from the index, while all reads from the hard disk;

EXPLAIN SELECT barcode FROM sku WHERE deleted=0;

##all is a full table scan; EXPLAIN SELECT * FROM sku WHERE deleted=0;

(6) Indexes that may be used in possible_keys queries will be listed here.

(7) key queries the index actually used. When select_type is index_merge , there may be two errors here. For more than one index, only one of the other select_type will appear here.

(8)key_len key_len indicates the length of the index used in the column calculation query. For example: SELECT * FROM table where age = 1 and name like 'xx' , assuming that age is of int type and cannot be null; name is of varchar(20) type and can be null, and the encoding is utf8. If these two fields are used as index queries, then the value of key_len is 4 3 * 20 2 1 = 67 . The specific calculation rules are shown in the following table:

Value type Value name Description String CHAR(n) n Byte length ##VARCHAR(n) If it is utf8 encoding, it is 3 n 2 bytes; if it is utf8mb4 encoding, it is 4 n 2 bytes. Numeric type TINYINT 1 byte SMALLINT 2 bytes MEDIUMINT 3 bytes INT 4 bytes ##BIGINT 8 bytes Time type DATE 3 bytes TIMESTAMP 4 bytes ##DATETIME 8 bytes Field attributes The NULL attribute occupies one byte. If a field is NOT NULL, it is not occupied. (9)ref If it is a constant equivalent query, const will be displayed here. If it is a connection query, the execution plan of the driven table The associated fields of the driver table will be displayed here. If the condition uses an expression or function, or the condition column undergoes internal implicit conversion, it may be displayed as func .

(10)rows This is the estimated number of scan rows in the execution plan, not an exact value.

（11）filtered This column will appear when using explain extended , it will be available by default in versions after 5.7 There is no need to use explain extended for this field. This field indicates the proportion of the remaining records that satisfy the query after the data returned by the storage engine is filtered at the server layer. Note that it is a percentage, not a specific number of records.

(12)Extra This column can display a lot of information, there are dozens of them, the commonly used ones are:

1 , distinct : The distinct keyword is used in the select part

2, no tables used : without from query or From dual query. Use the not in() form subquery or the not exists() operator to join the query. This is called an anti-join. That is, a general join query queries the inner table first and then the outer table, while an anti-join query queries the outer table first and then the inner table.

3. using filesort : Description mysql will sort the data using an external index instead of reading it in the order of the index in the table. The sorting operation that cannot be completed using indexes in mysql is called "file sorting". This occurs when the index cannot be used during sorting. Commonly seen in the order by statement, it needs to be optimized as soon as possible

4. using index : There is no need to return to the table when querying, and the query data can be obtained directly through the index .

5. using join buffer (block nested loop), using join buffer (batched key accss) : 5.6.x and later versions optimize related queries BNL , BKA features. The main purpose is to reduce the number of loops in the internal table and scan the query sequentially.

6. using sort_union, using_union, using intersect, using sort_intersection:

##using intersect: indicates the use of each index of and When a condition is used, this information indicates that the intersection is obtained from the processing results. using union: It indicates that or is used to connect the conditions using indexes. This information indicates that the union is obtained from the processing results. using sort_union and using sort_intersection: Similar to the previous two, except that they appear when using and and or to query a large amount of information, first query the primary key, and then proceed Only after sorting and merging can the records be read and returned. 7. using temporary : Indicates that a temporary table is used to store intermediate results. The temporary table can be a memory temporary table or a disk temporary table. It cannot be seen in the execution plan. You need to check the status variable, used_tmp_table , used_tmp_disk_table to see it. Commonly seen in order by and group query group by . group by Be sure to follow the order and number of indexes created. Need to optimize as soon as possible

8. using where : Indicates that not all records returned by the storage engine meet the query conditions and need to be filtered at the server layer. Query conditions are divided into restriction conditions and inspection conditions. Before 5.6 , the storage engine could only scan data and return it according to the restriction conditions, and then the server layer would filter according to the inspection conditions and return the data that actually meets the requirements. Query data. After 5.6.x , the ICP feature (index condition pushdown, index pushdown) is supported. The check conditions can also be pushed down to the storage engine layer. Data that does not meet the check conditions and restrictions will be Do not read directly, which greatly reduces the number of records scanned by the storage engine. extra Column display using index condition

9, firstmatch(tb_name) : Optimizer introduced starting from 5.6.x One of the new features of query is common in where clauses containing subqueries of in() type. If the amount of data in the internal table is relatively large, this may occur

10, loosescan(m..n) : The optimization subquery introduced after 5.6.x One of the new features is that in a subquery of type in() , when the subquery returns duplicate records, this may occur.

4. Explain Main Concerns In general, we only need to pay attention to a few columns in the results:

Column name Remarks ##type This query table Connection type, from here you can see the approximate efficiency of this query key Finally selected index, if there is no index, the efficiency of this query is usually very poor key_len The actual length of the index used for result filtering in this query rows Estimated The number of records that need to be scanned, the estimated number of records that need to be scanned The smaller the better Extra Extra Extra additional information, mainly to confirm whether it appears Using filesort , Using temporary In these two cases ##Let’s take a look at the Extra column that needs to be noted. Several situations:

Keywords Remarks Using filesort Will use external sorting instead of arranging the results in index order. When the data is small, it will be sorted from the memory. Otherwise, the sorting needs to be completed on the disk, which is very expensive. Need to add a suitable index Using temporary Need to create a temporary table to store the results, this usually occurs when doing GROUP BY on a column without an index, or Not all columns in ORDER BY are in the index, needs to add a suitable index Using index means MySQL Use a covering index to avoid a full table scan, and there is no need to search for data twice in the table. This is one of the better results. Be careful not to confuse it with the index type in type Using where is usually a full table/full index After scanning, use the WHERE clause to complete the result filtering. Need to add the appropriate index Impossible WHERE Yes Where The result of clause judgment is always false and no data can be selected, for example where 1=0 , no need to pay too much attention Select tables optimized away When using some aggregate functions to access a field that has an index, the optimizer will directly locate the required data rows through the index at once to complete the entire query, such as MIN()\MAX () , this is also one of the better results [Related recommendations: mysql video tutorial ]

This article will help you quickly understand MySQL execution plan

Related articles