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

This article mainly explains "how to master the explain of Mysql". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to master the explain of Mysql.

Database performance optimization is one of the necessary basic skills for every back-end programmer, and explain in Mysql can be called the performance optimization analysis artifact of Mysql. We can use it to analyze how the corresponding execution plan of SQL statements is executed at the bottom of Mysql. It is of great significance for us to evaluate the execution efficiency of SQL and determine the direction of Mysql performance optimization. However, many students are still confused about how to carry out in-depth analysis of the existing SQL according to explain, so this paper describes in detail the performance of the database through explain analysis.

Explain Foundation for each SQL, after it is sent to the Mysql server by the client, it will be analyzed by the optimizer components of Mysql, including some special processing, the change of execution order to ensure the optimal execution efficiency, and finally generate the corresponding execution plan. The so-called execution plan is actually how to obtain data at the storage engine level, whether to obtain data through index or full table scan, whether to return to the table after obtaining data, and so on. Simple understanding is the process of obtaining data by Mysql.

Next, let's take a closer look at who this explain is and why it can guide us in performance optimization. When we execute the following statement:

After explain SELECT * FROM user_info where NAME='mufeng' executes the explain statement, we get the following execution result. The 12 fields similar to the database table are actually a detailed description of how the Mysql executes the execution plan. Let's take a good look at what these 12 fields mean, and only by figuring out what they mean can we know exactly how Mysql performs the data query.

1 、 id

In fact, each select query corresponds to an id, which represents the order in which the SQL executes. The higher the id value, the higher the priority of the corresponding SQL statement execution. In some complex query SQL statements often contain some subqueries, then the id sequence number will be incremented. If there is a nested query, we can find that the innermost query has the largest id, so it is executed first.

As shown in the figure above, in the SQL query statement, the id of the first execution plan is 1, the id of the second execution plan is 2, the execution plan with ID 1 corresponds to the execution plan with table order,id 2, and the table corresponding to the execution plan with order,id 2. Combined with the SQL statement, we know that the subquery select id from user_info is executed first, and then the data query about table order is executed.

2 、 select_type

What is the corresponding query type of the execution plan represented by select_type? the common query types mainly include general query, federated query, subquery and so on. SIMPLE (query statements are simple queries without subqueries), PRIMARY (when subqueries are included, corresponding to the outermost query type), UNION (query types corresponding to select statements that appear after union will be marked with this type), SUBQUERY (subqueries will be marked with this type), DEPENDENT SUBQUERY (depending on the external query)

3 、 table

Table stands for the table name, indicating which table to query. Of course, it is not necessarily the name of the real table, but it may also be the alias or temporary table of the table.

4 、 partitions

Partitions represents the concept of partitioning, which means that when querying, if all the corresponding tables are dead partitioned tables, then the specific partitioning information will be displayed here.

5 、 type

Type is a very core attribute that needs to be mastered. It represents the current way to separate access to the database table.

(1) system

The table has only one row (equivalent to the system table), the amount of data is very small, the query speed is very fast, and system is a special case of the const type.

(2) const

If type is const, it means that primary key or unique index is hit when querying data, and this kind of data query is very fast.

(3) eq_ref

During a data query, if the SQL statement can be scanned based on a clustered index or a unique index with a non-null value in the case of a table join, then the corresponding value of type will be displayed as eq_ref.

(4) ref

When querying data, if the hit index is a secondary index and not a unique index, the test query speed will also be very fast, but type is ref. In addition, if it is a multi-field federated index, then according to the leftmost matching principle, it is also the type of ref to compare fields with consecutive columns starting from the leftmost side of the federated index.

(5) ref_or_null

This connection type is similar to ref, except that MySQL searches for additional rows that contain null values.

(7) unique_subquery

The set of subquery conditions about in in where condition

(8) index_subquery

Unlike unique_subquery, it is used for non-unique indexes and can return duplicate values.

(9) range

Index is used for row data retrieval, and only row data within the specified range is retrieved. In other words, retrieve data in a specified range for an indexed field. Use bettween...and, in the where statement

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.