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

This article mainly explains the basic concepts and optimization of MySQL query statement process and EXPLAIN sentence. The content of the explanation in this article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn the basic concepts and optimization of MySQL query statement process and EXPLAIN sentence.

The key to the performance of a website or service lies largely in the design of the database (assuming you choose the appropriate language development framework) and how to query data.

We know the performance optimization methods of MySQL, such as establishing indexes, avoiding complex federated queries, setting redundant fields, setting up intermediate tables, query caching, etc., and we also know how to use EXPLAIN to view the execution plan.

However, little is known about the execution process and internal mechanism of MySQL complex query statements, the optimization made by MySQL Optimizer itself and the impact of query statement adjustment on performance and its reasons.

This paper attempts to make an in-depth discussion on some of the key concepts, such as execution process, index use and so on.

This can avoid blindly following the trend of NoSQL storage or investing money to upgrade infrastructure when good results can be achieved through simple optimization of MySQL.

If you want to do a good job, you must first sharpen its tools. Here, we first introduce the MySQL query performance analysis tool.

The EXPLAIN command of MySQL is a tool for analyzing query performance. Each row of the output of EXPLAIN corresponds to the execution plan description of a table in the query statement. The meaning of the output column is shown in the following table:

The type column in the above table is a table association type, and the common types are as follows (sorted by association query efficiency from high to low):

Const (constant connection), such as SELECT * FROM user WHERE id=1

Eq_ref (equivalent reference), such as SELECT * FROM user,card WHERE user.id=card.userid

Ref (reference) for non-unique indexes, such as SELECT * FROM user,card WHERE user.last_name='test'

Range (range), such as SELECT * FROM tbl_name WHERE key_column > 10

Index (index), which reads data according to the index. If the index already contains query data, just scan the index tree, otherwise perform a full table scan similar to All.

ALL (all), full table scan

The key column represents the index (index), and rows indicates how many rows are estimated to be scanned

Extra stands for additional information, and the common ones are as follows (also sorted by query efficiency from high to low):

Using index: it means to use the index. If Using where appears at the same time, it means to use the index to find the read record. If there is no Using where, it means that the index contains the query data and no additional lookup is required.

Using where: represents a conditional query. If the type column is ALL or index and the message does not appear, you may be executing the wrong query: returning all the data

Using filesort: not the meaning of "using file indexing"! Filesort is a sort strategy implemented by MySQL, which usually appears when the sort statement ORDER BY is used.

Using temporary: indicates that temporary tables are used to get results, which usually occurs in the case of multi-table federated queries and sorting of results

If the last two messages appear in EXPLAIN (Using filesort,Using temporary), and the rows is large, it usually means that you need to adjust the query statement, or need to add an index, in short, you need to eliminate these two messages as much as possible.

The following is an example of the EXPLAIN result (find the nickname and gender from the user profile table and sort by the number of user followers in the user table):

Thank you for your reading, the above is the content of "the basic concepts and optimization of MySQL query statement process and EXPLAIN statement". After the study of this article, I believe you have a deeper understanding of the basic concept and optimization of MySQL query statement process and EXPLAIN sentence. 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.