Shulou(Shulou.com)06/01 Report--

This article is to share with you the content of a case of Explain execution plan in MySQL. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

1. Brief introduction of Explain

The Explain statement can see how MySQL executes the SQL statement, including index usage, the number of rows scanned, and so on. This information is very important for SQL tuning, so you must first understand the execution plan.

Mysql > explain select * from user where name='one' +- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -- + | 1 | SIMPLE | user | NULL | ref | a | a | 13 | const | 1 | 100.00 | Using index | + -+-+ 1 row in set 1 warning (0.00 sec) copy code

The above is the execution plan of a simple query statement, this table has a total of 12 fields, each representing different meanings, which are described below.

Id: indicates the order in which SQL is executed. The higher the value, the higher the priority. If the values are the same, the order of execution is determined by the optimizer. Select_type: indicates the type of select query statement table: the table name (or alias of the table) queried by the SQL statement, or the table partitions that does not exist such as a temporary table: the partition information type: association type (access type) involved in the query statement determines how MySQL looks up the rows in the table. The order of performance from worst to best is ALL, index, range, index_merge, ref, eq_ref, const, system. NULLpossible_keys: shows all the indexes that can be used by the query statement key: shows the index name decided by the optimizer key_len: shows the number of bytes of index length used by MySQL: the column or constant used to find values in the index recorded by the key column rows: the estimate of the number of scan rows filtered: the percentage of the total rows returned by the storage engine that finally satisfies the number of query statements Extra: other execution information

The above is just an explanation of the nouns of the fields of the execution schedule, and then I will use practical examples to help you (myself) better understand the important fields such as select_type, type, key_len, rows, and Extra.

2. Explain details 2.1 sample table structure

First, introduce the sample table structure and data rows that will be used in this article:

CREATE TABLE `user` (`id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'key', `name` varchar (36) DEFAULT NULL COMMENT 'name', `age`int (11) NULL DEFAULT NULL COMMENT 'age', `email` varchar (36) DEFAULT NULL COMMENT 'mailbox', PRIMARY KEY (`id`) USING BTREE, INDEX `idx_age_ name` (`age`, `name`) ENGINE = InnoDB AUTO_INCREMENT = 1; copy code

Insert 1000000 pieces of test data into the table through the function.

CREATE DEFINER= `root` @ `localhost` PROCEDURE `idata` () begin declare i int; set iTunes 1; while (i explain select * from user where id=1 +-+ | id | | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+ -+ | 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +- -+ 1 row in set 1 warning (0.00 sec) copy code 2.2.1 PRIMARY

If the query contains any complex subsections, the outermost part is marked PRIMARY, such as:

Mysql > explain select * from user where id= (select id from user where id=1) +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | PRIMARY | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | 2 | SUBQUERY | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | .00 | Using index | + -+ 2 rows in set 1 warning (0.00 sec) copy code

In the execution plan of this SQL statement, the first SQL executed is select * from yser where id = (...) It is marked as PRIMARY.

2.2.2 SUBQUERY

Subqueries contained in select or where content are marked as SUBQUERY, as in the second statement in the execution plan of the previous example SQL, that is, the select_type of select id from user where id=1 is marked as SUBQUERY.

2.2.3 DERIVED

Subqueries that are included after the FROM keyword (that is, treat the results of subqueries as "tables"), subqueries that are considered "tables" will be marked as DERIVED, and their results will be stored in temporary tables, such as:

Mysql > explain select * from (select id,name,count (*) from user where id=1) as user_1 where id=1 +- +-+ | 1 | PRIMARY | | NULL | system | NULL | 1 | 100.00 | NULL | 2 | DERIVED | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | .00 | NULL | +-- +- +-+ 2 rows in set 1 warning (0.00 sec) copy code

As you can see from the execution plan, the query type of the second executed SQL, select id,name,count (*) from user where id=1, is DERIVED.

There are 12 query types in select_type. For more information, please see the official document-explain_select_type.

2.3 type in Explain

The type field is a very important basis for measuring SQL in the execution plan. It shows the association type (access type) of the SQL statement and determines how MySQL looks up the rows in the table.

The value performance of type field from the worst to the best is ALL, index, range, index_merge, ref, eq_ref, const, system.

In order to better understand the meaning of each type, I have given examples for each of the above types.

It is not all listed. The complete explanation can be found in the official document-EXPLAIN Join Types.

2.3.1 ALL

ALL stands for full table scan, which means that the storage engine does not index when looking for records, so it is one of the worst-performing types of access, such as

Mysql > explain select * from user where age+2=20 +- -+ | 1 | SIMPLE | user | NULL | ALL | NULL | 1002301 | 100.00 | Using where | + -+-+ 1 row in set 1 warning (0.00 sec) copy code

You can see that the value of the rows row is 1002301, that is, all the data of the whole table has been scanned (the value of the number of scanned rows is actually estimated). If there is such a SQL in the production environment, it should definitely be optimized.

We know that in where query conditions, you should not use functions or expressions on query fields (should be written to the right of the equal sign). Those who don't know this can take a look at my last blog, the MySQL (2) index as I understand it.

After optimization, the query statement should be: select * from user where age=18. Remove the expression on the left side of the equal sign. The optimized execution plan is as follows:

Mysql > explain select * from user where age=18 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | SIMPLE | user | NULL | ref | idx_age_name | idx_age_name | 5 | const | 39360 | 100.00 | NULL | +- -+ 1 row in set 1 warning (0.00 sec) copy code 2.3.2 index

Index means full index tree scan, which is better than full table scan in the form of ALL because it scans index tree.

At the same time, because the index tree itself is ordered, sorting can be avoided.

Mysql > explain select id,age from user where name='name1' +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | SIMPLE | user | NULL | index | NULL | idx_age_name | | NULL | 1002301 | 10.00 | Using where | Using index | +-- + -- + 1 row in set 1 warning (0.00 sec) copy code

As mentioned above, when the query condition exists in the federated index idx_age_name but cannot be used directly (due to the leftmost prefix principle), and the query column id,age also exists in the federated index, the access type type column in the execution plan will be index without having to retrieve it from the back table.

2.3.3 range

Range means range scan, which is exactly based on the range scan of the index tree, which scans part of the index tree, so the performance is slightly better than index.

It is important to note that range scanning can also be used when using in or or.

Mysql > explain select * from user where age > 18 and age explain select * from user where age=18 or age=20 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+-- + | 1 | SIMPLE | user | NULL | range | idx_age_name | idx_age_name | 5 | NULL | 78720 | 100. 00 | Using index condition | +-- +- +-+ 1 row in set 1 warning (0.00 sec) copy code 2.3.4 index_merge

Index_merge is index merging, which means that MySQL uses multiple indexes when querying.

MySQL has multiple query conditions in the where statement, and there are multiple fields in which you can use multiple different indexes, in which case MySQL can scan multiple index trees at the same time, and finally merge their results, such as:

Mysql > explain select * from user where id=1 or age=18 +- -+-+-- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | +- -+-+ | 1 | SIMPLE | user | NULL | index_merge | PRIMARY Idx_age_name | idx_age_name,PRIMARY | 5P4 | NULL | 39361 | 100.00 | Using sort_union (idx_age_name,PRIMARY) Using where | +-- + -+-+-- + 1 row in set 1 warning (0.00 sec) copy code

The id=1 and age=18 in the above query use the PRIMARY primary key index and the idx_age_name federated index, respectively, and finally merge the records that meet these two conditions.

2.3.5 ref

Ref stands for index access (index lookup), which occurs when queries are queried with the constant value of a non-clustered index column.

For example, in the introduction of full table scan, the access type of optimized SQL is ref.

2.3.6 eq_ref

The access type of eq_ref, which returns at most one qualified record, occurs when a join query is joined through a clustered index. If the clustered index of the table is a federated index, all index columns must be equivalent queries, such as:

Mysql > explain select * from user user1 inner join user user2 where user1.id=user2.id limit 10 +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -- + | 1 | SIMPLE | user1 | NULL | ALL | PRIMARY | NULL | 1002301 | 100.00 | NULL | | 1 | SIMPLE | user2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | all_in_one.user1.id | 1 | 100.00 | NULL | +-+ -+-+ 2 rows in set 1 warning (0.00 sec) copy code 2.3.7 const

The access type of const can occur in the case of constant equivalent queries through clustered indexes, such as:

Mysql > explain select * from user where id=1 +-+ | id | | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+ -+ | 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +- -+ 1 row in set 1 warning (0.00 sec) copy code 2.4 key_len in Explain

The calculation of index length has been mentioned in the last blog post, which I understand in part 5.2 of the MySQL (2) index matching, so let's sum it up here.

2.4.1 character type

If a field of character type is used as an index column, its index length = field defines the length character length + whether the default NULL + is a variable length field *, where:

Field definition length means that the numeric character length in parentheses after the field type is constant when defining the table structure. Whether utf8=3, gbk=2 and latin1=1 default NULL is also constant. If the default value of the field is NULL, the value is 1, because NULL needs an extra byte to represent it. Otherwise, whether the value 0 is a variable length field is also constant, if the field is a variable length field, the value is 2; otherwise, the value is 0.

The so-called variable length field is varchar, which occupies the length of the actual content of the field rather than the length when the field is defined. The fixed-length field, that is, the char type, takes up space that is the length of the custom field, and if it exceeds it, it will be intercepted.

For example, add an index of a character type field to the above example table.

Alter table user add index idx_name (`name`); copy the code

Then make a query through the name field to see the execution plan.

Mysql > explain select * from user where name='name1' +-+ | id | | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+ -+ | 1 | SIMPLE | user | NULL | ref | idx_name | idx_name | 111 | const | 2 | 100.00 | NULL | + -+ 1 row in set 1 warning (0.01sec) copy code

As you can see, the value of the key_len column in the execution plan is 111.

According to the above formula for calculating the index length, the name column field defines a length of 36, the character set type is the default utf8, this field allows NULL by default, and this field is a variable length field varchar.

So the index length of the idx_name index = 36 "3" 1 "2" 111, just like the value shown in the execution plan.

2.4.2 other types of fixed length

For a field of a fixed length type, its index length is the same as its data type length.

Data type length int4bigint8date3datetime8timestamp4float4double8

It is important to note that if this field allows the default value to be NULL, like the character type, the index length needs to be added by 1.

Mysql > explain select * from user where age=1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | SIMPLE | user | NULL | ref | idx_age_name | idx_age_name | 5 | const | 39366 | 100.00 | NULL | +- -+ 1 row in set 1 warning (0.00 sec) copy code

As in the above example (in this example, the index only uses the age field), the age field is of type int, and its index length should have been 4, but because the age field is allowed to be NULL by default, its index length becomes 5.

2.5 rows in Explain

The number of scan rows is actually an estimate in the execution plan. MySQL selects N different index data pages, calculates the average to get the index base of a single page, and then multiplies it by the number of index pages to get the estimate of the number of scan rows.

The number of scan rows is one of the factors that the optimizer considers index execution efficiency. Generally speaking, the fewer scan rows, the higher the execution efficiency.

2.6 Extra in Explain

Common types of Extra fields in an execution plan are:

Using index: override index is used to avoid returning to the table Using index condition: index push-down is used Specifically, you can take a look at my last blog, the MySQL (2) index Using where: it means that MySQL will filter records through where conditions: scan the index tree with this table field as search criteria in where: where contains fields other than index fields as search criteria Using temporary: MySQL uses temporary table Using filesort: external index sorting (file sorting) when sorting query results Sort in memory when there is less index data, and sort on disk when there is more data. Try to avoid this information appearing in the execution plan. Thank you for reading! This is the case about the implementation plan of Explain in MySQL. I hope the above content can be helpful to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

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.