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

This article mainly explains "how can you say you are proficient in MySQL query optimization when you can't even use explain?" The content of the explanation in this article is simple and clear, and it is easy to learn and understand. now please follow the editor's train of thought to study and learn "explain can not be used, how can you say that you are proficient in MySQL query optimization?" Come on!

Introduction to Explain

The Explain keyword is a common "keyword" for sql optimization in Mysql. Explain is usually used to "view the execution plan of sql instead of sql" to quickly find out what is wrong with sql.

Before explaining Explain, you first create the required user table user, role table role, and user role relationship table role_user as testing tables:

/ / user table DROP TABLE IF EXISTS `user`; CREATE TABLE `user` (`id` int (11) NOT NULL, `name` varchar (25) DEFAULT NULL, `age` int (11) NOT NULL DEFAULT 0, `update_ time` datetime DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 INSERT INTO `user` (`id`, `name`, `age`, `update_ time`) VALUES (1pi 'Zhang San', 23Meno '2012-22 1514 2714 18'), (2Mi' Li Si', 24Med '2020-06-21 1527Ranger 18'), (3pm' Wang Wu', 25PM'07-20 15VALUES 2718'); DROP TABLE IF EXISTS `role` CREATE TABLE `role` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_ name` (`name`) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `role` (`id`, `name`) VALUES (1 'Product Manager'), (3 'Technical Manager'), (3 'role_' Project Director'); DROP TABLE IF EXISTS `role` CREATE TABLE `role_ User` (`id` int (11) NOT NULL, `role_ id` int (11) NOT NULL, `user_ id` int (11) NOT NULL, PRIMARY KEY (`id`), KEY `index_role_user_ id` (`role_ id`, `user_ id`) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `role_ user` (`id`, `role_ id`, `user_ id`) VALUES (1Query 2), (3meme 3)

First, we execute a sql:explain select * from user where id = 2th. After execution, we can see the following result:

You can see that there are 12 fields here and all have corresponding values. This is the explain execution plan. If you can understand this execution plan, you will not be far away from being proficient in sql optimization. Here is a detailed description of what these 12 fields mean.

Id field

Id represents the sequence number of the execution of the select query statement, which is the identification of the order in which the sql is executed, the sql is executed according to the id from the largest to the smallest, and the same id is executed as a group, from top to bottom.

What do you mean? For example, execute this sql:explain select * from user where id in (select user_id from role_user)

+- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | | ref | rows | filtered | Extra | +-+ -+- -+ | 1 | SIMPLE | user | NULL | ALL | PRIMARY | NULL | 3 | 100.00 | NULL | | 1 | SIMPLE | role_user | NULL | index | NULL | index_role_user_id | 8 | NULL | 3 | 33.33 | Using where Using index; FirstMatch (user) Using join buffer (Block Nested Loop) | + -- +-+

If both id are the same, it means that the execution of sql is executed from top to bottom, the first record corresponds to the user table, and then the second record corresponds to the role_ user table, which is the same as id.

If the id is different, for example, execute the following sql:explain select (select 1 from user limit 1) from role;:

+- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -+-+ | 1 | PRIMARY | role | NULL | index | NULL | index_name | 33 | NULL | 3 | 100.00 | Using index | 2 | SUBQUERY | user | NULL | index | NULL | PRIMARY | 4 | | NULL | 3 | 100.00 | Using index | + -+

You will see that there are two records, and the id of the two records will be different. The larger the id, the first to execute. You can see that the id=2 executes the user table, that is, the subquery part, and finally executes the outermost part.

Conclusion: "this is the order in which id marks the execution of sql. Generally, there are multiple records in a complex query, a simple query has only one record, and the same id in a complex query is a group, the order of execution is from top to bottom, and the larger the id, the first to execute; in Mysql 8, subqueries are optimized, so sometimes even complex queries have only one record.

Select_type field

Select_type represents the type of query, that is, whether it corresponds to a simple query or a complex query, and if a complex query contains: "simple subquery, subquery of the from clause, union query". Let's take a look at all the query types in select_type.

Simple

Simple stands for simple queries and does not contain any complex queries.

PRIMARY

In a complex query, "the query type of the outermost select statement is PRIMARY", for example, execute the following sql:explain select * from role where id = (select id from role_user where role_id = (select id from user where id = 2))

The outermost select, namely select * from role where id =? Will be marked as PRIMARY type.

SUBQUERY

Subqueries contained in "select or where" are represented as SUBQUERY types, for example, two subqueries in the sql executed in the previous sentence are SUBQUERY.

DERIVED

"DERIVED means derived or derived tables, which are represented as DERIVED types in subqueries contained in from." Mysql executes these subqueries recursively and puts the results in temporary tables. Execute sql:explain select * from (select name from user union select name from role) a where a.name = 'Zhang San'

It has been optimized in Mysql version 5.7 and added derived_merge (derived merge), which can speed up the query efficiency.

UNION

In the UNION query statement, the query statement of the second select is represented as UNION:

UNION RESULT

"the result of the UNION query statement is marked UNION RESULT", such as sql:explain select * from (select name from user union select name from role) a where a.name = 'Zhang San' executed above

As you can see from the table field in the fourth row of records, the records in the fourth row come from the second and third rows, so the result of an UNION query statement is marked as UNION RESULT

Other

The above seven select_type are all common, and there are some less common ones, which is good for understanding:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

DEPENDENT UNION: also means the second or subsequent statement in the UNION query statement, but it depends on the external query.

DEPENDENT SUBQUERY: the first select statement in a subquery, which also depends on an external query.

UNCACHEABLE SUBQUERY: the results of the subquery cannot be cached and the first row of the outer join must be reevaluated.

Table field

It is easy to see that "the table field represents which table to query", and one is an existing table, such as the user and role above, which are created by ourselves, or derivative tables.

For example, the table field of UNION RESULT is expressed as, that is, the result records of the second and third rows are queried.

Type field

The type of sql associated, or the type of access, represented by the type field. From this field, we can determine what the approximate scope of the search record is when the sql looks up the database table, which directly reflects the efficiency of sql.

There are also many types of type fields, and the main ones that are commonly mastered are as follows: system, const, eq_ref, ref, range, index, ALL. Its performance is from high to low, that is, system > const > eq_ref > ref > range > index > ALL. Let's talk about this attribute in detail below.

System

System is a special case of const, which "indicates that there is only one row in the table", which is rarely seen as an understanding.

Const

Const means that the data is found at one time through the index, and const usually appears in the "unique index or primary key index using an equivalent query" because there is only one data match in the table, so the lookup is very fast. Example: explain select * from user where id = 2

Eq_ref

Eq_ref means that a unique index or primary key index scan is used as a table link matching condition, and for each index key, only one record in the table matches it. For example: explain select * from user left join role_user on user.id = role_user.user_id left join role on role_user.role_id=role.id

Ref

The performance of ref is worse than that of eq_ref, which also indicates the link matching condition of the table, that is, which table fields are used as the values on the query index column. The difference between ref and eq_ref is that eq_ref uses a unique index or primary key index.

The result of a ref scan may find multiple qualified rows of data, which is essentially an index access that returns matching rows. For example: explain select * from user where name = 'Zhang San'

Range

"range uses an index to retrieve a given range of row data. Generally, range appears when between, in and other query statements are used after where.": explain select * from user where id > 2

Index

Index means traversing the index tree. It is faster for index to avoid ALL, but the appearance of index indicates that you need to check whether your index is used correctly: explain select id from user

ALL

"the difference between ALL and index is that ALL is read from the hard disk, while index is read from the index file." ALL full table scan means that Mysql scans from beginning to end of the table, which means that indexes are usually needed for optimization, or that indexes are not used as conditions in the query: explain select * from user

Possible_keys field

Possible_keys indicates that the indexes that may be used in this column of query statements are only possible, and the indexes listed are not necessarily used.

When the index is NULL, it means that you need to increase the index to optimize the query. If there is less data in the table, the database thinks that the full table scan is faster, or it may be NULL.

Key field

The difference between the key field and possible_keys is that it represents the index that is actually used, that is, the possible_keys contains the value of key.

If you want Mysql to use or ignore indexes in possible_keys, you can use FORCE INDEX, USE INDEX, or IGNORE INDEX.

Key_len field

Represents the number of bytes used by the index in the sql query statement, which is not the actual length, but is calculated by calculating the length of the index used in the query, showing the maximum possible length of the index field.

Generally speaking, without losing precision, the smaller the key_len, the better. For example, the id of the above test table is int, and the int type is composed of 4 bytes: explain select * from user where id = 2.

Key_len has its own calculation rules for different types. The specific calculation rules are as follows:

Bytes occupied by data type numeric string char (n): n byte length

Varchar (n): 2 bytes store string length, if utf-8, length 3n + 2 numeric type tinyint: 1 byte

Smallint:2 byte

Int:4 byte

Bigint:8 byte time type date: 3 bytes

Timestamp:4 byte

Datetime:8 byte

If the index is a string type, the actual stored string is very long and has exceeded the maximum storage length of the string type (768 bytes), mysql will use a similar left prefix index to deal with.

Ref field

Ref represents the comparison of the column to the index, the matching condition of the table join, and which columns or constants are used to query the values on the index column.

Rows field

Rows indicates the estimated number of rows to be scanned. Generally, Mysql will estimate the number of rows to be scanned for finding records based on statistical table information and index selection. Note that this is not the actual number of rows in the result set.

Partitions, filtered field

Partitions represents the matching partition; filtered represents the percentage of query table rows in the table.

Extra field

This field shows the additional information of the sql query, mainly in the following situations:

Using index

Indicates that the column of the query is overwritten by the index, which reflects the high query performance, that is, the information to be queried can be found in the index without going back to the table. The index is used correctly: explain select id from user where id = 2

If using where appears at the same time, it indicates that the index is used to perform the lookup of the index key value; if there is no using where, the index is used to read the data, not the action of executing the query.

Using where

This attribute is contrary to Using index, the query column is not overwritten by the index, the where condition is followed by a non-indexed leading column, it only uses the where condition: explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id

Using temporary

"Using temporary means that temporary tables are used to store intermediate results, and temporary tables are generally used when sorting results", such as sorting order by and grouping query group by. Example: explain select * from (select name from user union select name from role) a where a.name = 'Zhang San'

Using filesort

Using filesort stands for file sorting, indicating that Mysql uses an external index to sort the data, not an index in the table: explain select * from user order by name

Using join buffer

Using join buffer means to use connection caching: explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id

It emphasizes that when obtaining the connection condition, the index is not used, but the connection buffer is used to store the intermediate results. if this value appears, it generally indicates that an index needs to be added to optimize it.

Impossible where

Impossible where will appear when the condition after where is always false, which can be ignored and rarely occurs: explain select * from user where name = 'hah' and name =' sfsd'

Select tables optimized away

Indicates that the select statement returns data without traversing the table or index, such as explain select min (id) from user

There are other properties in the Extra field, but almost never seen, do not appear, so explain which, interested can learn for themselves, here is only listed these common.

Having said so many theories, it always needs to be put into practice, so let's take the user test table as an example for testing practice.

Practice

(1) No index is created in the name field by querying explain select * from user where name = 'Zhang San'.

We can solve this by creating a federated index index_name_age_time:

Alter table user add index index_name_age_time (name,age,update_time)

When querying again, the index is used:

(2) the use of federated indexes should follow the "leftmost prefix rule". About the use of the leftmost prefix rule, I have written a detailed article, please refer to [].

(3) when using an index to query, do not do any function operations, otherwise the index will fail: example: EXPLAIN SELECT * FROM user WHERE name = 'Zhang San'

But when you use the left () function, such as: EXPLAIN SELECT * FROM employees WHERE left (name,2) = 'Zhang San';, it will cause the index to fail.

(4) do not use (! = or) and is null,is not null, like keywords starting with%, otherwise the index will also be invalidated:

Thank you for reading, the above is "explain can not be used, you have the nerve to say proficient in MySQL query optimization?" After the study of this article, I believe everyone can not use explain, how can you say that you are proficient in MySQL query optimization? This problem has a deeper understanding, the specific use of the situation still needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.