Shulou(Shulou.com)06/01 Report--

This paper introduces the MySQL statement in the process of MySQL query statement and the basic concepts and optimization of EXPLAIN statement, and gives an example of slow query:

You can see that the above query, which needs to check more than 10, 000 records, and uses temporary tables and filesort sorting, will become a nightmare after the rapid growth in the number of users.

Before optimizing this statement, let's take a look at the basic execution of the SQL query:

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

two。 The permissions are checked and optimized by MySQL optimizer. The parsed and optimized query commands are compiled into a binary form of query plan (query plan) that CPU can run, and can be cached.

3. If there is an index, scan the index first. If the data is overwritten by the index, no additional lookup is required. If not, find and read the corresponding records according to the index.

4. If there is a join query, the query order is to scan the first table to find the records that meet the conditions, scan the second table to find the records that meet the conditions according to the associated key values of the first table and the second table, and cycle in this order.

5. Output the query results and record the binary logs

Obviously, the right index will greatly simplify and speed up the search. If you look at the query statement above, in addition to the conditional query, there are also associated queries and ORDER BY, that is, sort operations.

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

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

Note 1: for information about what is an extraordinary scale, please refer to the MySQL development manual: Consts and Constant Tables

Note 2: what is filesort? this is not literal file sorting. Filesort has two modes:

1. Mode 1: the sorted elements cover the data to be output. The sort result is a series of ordered sequence element groups, which no longer requires additional record reading.

2. Mode 2: the sorting result is a sequence of key-value pairs, and then read the record through these row_ids (random read, inefficient)

Note 3: for information about what is a temporary table, please refer to the MySQL development manual: How MySQL Uses Internal Temporary Tables

The first method is for indexes in the first non-constant table that have columns that ORDER BY depends on, and then you can directly use the ordered index to find the data of the associated table, which has the best performance because no additional sorting actions are required:

In the second method, all the columns that ORDER BY depends on belong to the first query table and have no index, so we can first filesort the records of the first table (schema 1 or mode 2) to get ordered row indexes, and then do associated queries. The results of filesort may be in memory or on the hard disk, depending on the system variable sort_buffer_size (usually about 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 the temporary table, and finally the temporary table is filesort:

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

(1) data of type BLOB,TEXT is used.

(2) the memory table occupies more than the limit of the system variable tmp_table_size/max_heap_table_size (usually about 16m) and 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,Using temporary seriously affects query performance, because if there is a problem with the data type or field design

When there are big data's fields in the tables that need to be queried and the results, and no suitable index is available, it may lead to a large number of IO operations, which is the root cause of slow query performance.

Going back to the query example given at the beginning of the article, it is obviously the third least efficient method, and the optimization methods we need to do and try are:

1. Add an index for users.fl_no and index the fields used by select and where

2. Transfer users.fl_no to or add as redundant fields to table user_profile

3. Excluding fields of TEXT type, TEXT can be replaced with VARCHAR (65535) or VARCHAR (20000) for Chinese.

4. If it is impossible to eliminate Using filesort, increase sort_buffer_size to reduce the burden of IO operation.

5. Try to use the index covered by the first table for sorting, but it is really not possible. You can move the sorting logic from MySQL to the PHP/Java program for execution.

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

Note: write a simple PHP application and test it with siege, the query efficiency is improved by more than 3 times.

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

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.