Query process and optimization method of (JOIN/ORDER BY) statement in MySQL

The EXPLAIN statement is introduced in MySQL query statement process and basic concepts and optimization of EXPLAIN statement, and an example of slow query is given:

It can be seen that the above query needs to check more than 10,000 records and uses a temporary table and filesort. Such a query will become a nightmare when the number of users grows rapidly.

Before optimizing this statement, let's first understand the basic execution process of SQL queries:

1. The application sends the query command to the MySQL server through the MySQL API, and then it is parsed

2. Check permissions and optimize with MySQL optimizer. After parsing and optimization, the query command is compiled into a binary query plan that can be run by the CPU and can be cached.

3. If there is an index, scan the index first. If the data is covered by the index, no additional search is required. If not, find and read the corresponding record based on the index.

4. If there is an associated query, the query order is to scan the first table to find the records that meet the conditions, and then scan the second table to find the records that meet the conditions according to the associated key value of the first table and the second table, and loop in this order

5. Output query results and record binary logs

Obviously, a suitable index will greatly simplify and speed up the search. Let's look at the query statement above. In addition to conditional queries, there are also associated queries and ORDER BY, i.e. sorting operations.

So let's take a closer look at how joins and ORDER BY work. MySQL has three ways to handle joins and data sorting:

The first method is based on the index, the second is to filesort (quicksort) the first non-constant table, and the third is to put the results of the joint query into a temporary table and then perform filesort.

Note 1: For more information about what a constant table is, please refer to the MySQL Developer's Manual: Consts and Constant Tables.
Note 2: What is filesort? This is not the literal file sorting. Filesort has two modes:
1. Mode 1: The sorted elements cover the data to be output. The sorting result is a string of ordered sequence elements, and no additional record reading is required;
2. Mode 2: The sorting result is a sequence of key-value pairs <sort_key, row_id>, and records are read through these row_ids (random reading, inefficient);
Note 3: For more information about temporary tables, please refer to the MySQL Development Manual: How MySQL Uses Internal Temporary Tables

The first method is used when the index of the column that the ORDER BY depends on exists in the first non-constant table. In this case, the already ordered index can be used directly to find the data of the associated table. This method has the best performance because no additional sorting action is required:

The second method is used when all the columns that ORDER BY depends on belong to the first query table and there is no index. In this case, we can first perform filesort on the records of the first table (the mode may be mode 1 or mode 2) to obtain the ordered row index, and then perform the associated query. The filesort result may be in memory or on the hard disk, depending on the system variable sort_buffer_size (usually around 2M):

The third method is used when the element of ORDER BY does not belong to the first table. The result of the associated query needs to be put into a temporary table, and then filesort is performed on the temporary table:

The temporary table in the third method may be in-memory table or on the hard disk. Generally, the hard disk (on-disk table) is used in the following two situations:

(1) Using BLOB and TEXT data types

(2) The memory table occupancy exceeds the limit of the system variable tmp_table_size/max_heap_table_size (usually around 16M), so it can only be placed on the hard disk

From the above query execution process and method, we should be able to clearly understand why Using filesort and Using temporary will seriously affect query performance, because if there is a problem with the data type or field design,

When there are large data fields in the table to be queried and the results, and no suitable index is available, a large number of IO operations may be generated, which is the root cause of slow query performance.

Back to the query example at the beginning of the article, it obviously uses the third method, which is the least efficient. The optimization methods we need to do and try are:

1. Add an index for users.fl_no and create indexes for the fields used in select and where

2. Transfer users.fl_no to or add it as a redundant field to the user_profile table

3. Remove the TEXT type field. TEXT can be replaced with VARCHAR (65535) or VARCHAR (20000) for Chinese.

4. If you cannot eliminate Using filesort, increase sort_buffer_size to reduce the IO operation burden.

5. Try to use the index covered by the first table for sorting. If that doesn't work, move the sorting logic from MySQL to the PHP/Java program for execution.

After implementing optimization methods 1, 2, and 3, the EXPLAIN results are as follows:

Note: Write a simple PHP application and use siege to test, the query efficiency is improved by >3 times.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.