MySQL query statement process and basic concepts of EXPLAIN statement and its optimization

The performance of your website or service depends largely on the design of your database (assuming you choose the right language development framework) and how you query the data.

We know the performance optimization methods of MySQL, which generally include creating indexes, avoiding complex joint queries, setting redundant fields, creating intermediate tables, query cache, etc. We also know how to use EXPLAIN to view the execution plan.

However, little is known about the execution process and internal mechanism of complex MySQL query statements, the optimizations made by MySQL Optimizer itself, and the impact of query statement adjustments on performance and their causes.

This article attempts to make a more in-depth discussion of some key concepts such as execution process and index usage, so as to understand the whys and whys.

This can avoid blindly switching to NoSQL storage or investing in infrastructure upgrades when good results can be achieved through simple MySQL optimization.

If you want to do your work well, you must first sharpen your tools. Here we first introduce the MySQL query statement performance analysis tool.

MySQL's EXPLAIN command is a tool for analyzing query performance. Each row of EXPLAIN's output corresponds to the execution plan description of a table in the query statement. The meanings of the output columns are as follows:

The type column in the above table is the table association type. Common types are as follows (arranged from high to low in terms of association query efficiency):

const (constant connection), such as SELECT * FROM user WHERE id=1;
eq_ref (equal value reference), such as SELECT * FROM user,card WHERE user.id=card.userid;
ref (reference), used for non-unique indexes, such as SELECT * FROM user,card WHERE user.last_name='test';
range, such as SELECT * FROM tbl_name WHERE key_column > 10;
index: read data according to the index. If the index already contains the query data, only the index tree needs to be scanned. Otherwise, a full table scan is performed, which is similar to All.
ALL (all), full table scan

The key column represents the index, and rows represents the estimated number of rows to be scanned.

Extra indicates additional information. Common ones are as follows (also arranged in descending order of query efficiency):

Using index: indicates the use of an index. If Using where appears at the same time, it means that the index is used to search and read records. If Using where is not found, it means that the index contains the query data and no additional search is required.
Using where: indicates a conditional query. If the type column is ALL or index and this information does not appear, you may be executing an incorrect query: returning all data.
Using filesort: Not what "using file index" means! Filesort is a sorting strategy implemented by MySQL. This message usually appears when the sorting statement ORDER BY is used.
Using temporary: In order to obtain the result, a temporary table is used. This usually occurs when multiple tables are joined and the results are sorted.

If the following two messages (Using filesort, Using temporary) appear in EXPLAIN, and the rows are relatively large, it usually means that you need to adjust the query statement or add an index. In short, you need to try to eliminate these two messages.

The following is an example of EXPLAIN results (finding nicknames and genders from the user profile table, sorting by the number of user followers in the user table):

The query statement above is a typical problem case. The specific meaning of Using filesort and Using temporary and how to optimize the above statement will be discussed in the next article in conjunction with the query process and principles.

The above is the full content of this article. I hope it will be helpful to you. If you have any other questions, you can leave a message to communicate. Please continue to pay attention to 123WORDPRESS.COM!