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

This article introduces the relevant knowledge of "the optimization of Order By index in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In some cases, MySQL can directly use an index to satisfy an ORDER BY or GROUP BY clause without additional sorting. Although the ORDER BY does not exactly match the order of the index, the index can be used as long as the unused part of the index and all additional ORDER BY fields are included in the WHERE clause.

MySQL Order By that uses indexes

The following queries use indexes to resolve the ORDER BY or GROUP BY parts:

SELECT * FROM T1 ORDER BY key_part1,key_part2,...

SELECT * FROM T1 WHERE key_part1=constant ORDER BY key_part2

SELECT * FROM T1 WHERE key_part1=constant GROUP BY key_part2

SELECT * FROM T1 ORDER BY key_part1 DESC, key_part2 DESC

SELECT * FROM T1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC

MySQL Order By without indexing

In other cases, MySQL cannot use an index to satisfy ORDER BY, although it uses an index to find records to match the WHERE clause. These conditions are as follows:

* ORDER BY different index keys:

SELECT * FROM T1 ORDER BY key1, key2

* ORDER BY on non-contiguous index keys:

SELECT * FROM T1 WHERE key2=constant ORDER BY key_part2

* both ASC and DESC are used:

SELECT * FROM T1 ORDER BY key_part1 DESC, key_part2 ASC

* the index key used to search records is not the same as that used for ORDER BY:

SELECT * FROM T1 WHERE key2=constant ORDER BY key1

* there are many tables to join together, and not all the fields in the ORDER BY in the records read come from the first non-constant table (that is, the join type of the first table in the result of EXPLAIN analysis is not const).

* different ORDER BY and GROUP BY expressions are used.

* the records in the table index are not stored in order. For example, the HASH and HEAP tables are like this.

By executing EXPLAIN SELECT... ORDER BY, you will know whether MySQL uses the index in the query. If the value of the Extra field is Using filesort, MySQL cannot use the index. For more information, see "7.2.1 EXPLAIN Syntax (Get Information About a SELECT)". Before MySQL 4.1, it used the following filesort algorithm when the results had to be sorted:

1. Read the record according to the index key, or scan the data table. Records that do not match WHERE clauses are skipped.

two。 Each record stores two values (index key and record pointer) with a 'pair' in the buffer. The size of the buffer depends on the value of the system variable sort_buffer_size.

3. When the buffer is slow, run qsort (quick sort) and store the results in a temporary file. Save the stored block pointer (if all 'pair' values can be saved in the buffer, there is no need to create a temporary file).

4. Do the above until all the records are read.

5. Do a multiple merge to save blocks of up to MERGEBUFF (7) regions in another temporary file. Repeat this until all the blocks in the first file are placed in the second file.

6. Repeat until the number of remaining blocks is less than MERGEBUFF2 (15).

7. In the last multiple merge, only the pointer to the record (the last part of the sort index key) is written to the result file.

8. The records are read sequentially by reading the record pointer in the result file. To optimize this operation, MySQL puts the record pointer reading into a large block and uses it to read the records sequentially and put the records in the buffer. The size of the buffer is determined by the value of the system variable read_rnd_buffer_size. The code for this step is in the source file `sql/records.cc'.

One problem with this approximation algorithm is that records are read twice: once when estimating WHERE clauses and the second when sorting. Although the records were successfully read the first time (for example, a full table scan was done), the second was a random read (the index keys were sorted, but the records were not). In MySQL 4.1 and later, the filesort optimization algorithm is used to include not only the index key value and the location of the record, but also the fields required in the query. This avoids the need to read the record twice. The improved filesort algorithm is roughly as follows:

1. As before, read records that match WHERE clauses.

two。 Relative to each record, a corresponding tuple information is recorded, including the index key value, the record location, and all the fields required in the query.

3. Sort the 'tuple' information according to the index key.

4. Read records in order, but read records from a list of 'tuples' that have already been sorted, rather than reading them again from the data table.

Using the improved filesort algorithm, tuples take up more space than pairs, and they rarely fit right into the sort buffer (the size of the buffer is determined by the value of sort_buffer_size). Therefore, this may require more Imax O operations, causing the improved algorithm to be slower. To avoid slowing it down, this optimization method is only used to sort cases where the sum of the extra fields in the 'tuple' exceeds the system variable max_length_for_sort_data (one sign that the value of this variable is set too high is high disk load and low CPU load). To improve the speed of ORDER BY, the first thing to see is whether MySQL can use indexes instead of additional sorting procedures. If you cannot use an index, try following the following strategy:

* increase the value of sort_buffer_size.

* increase the value of read_rnd_buffer_size.

* modify the tmpdir to point to a dedicated file system with a lot of free space.

If you use MySQL 4.1or newer, this option allows multiple paths to be in a circular format. The paths are separated by a colon (':') on Unix and a semicolon (';') on Windows,NetWare and OS/2. You can use this feature to spread the load evenly among several directories. Note: these paths must be directories distributed on different physical disks, not different directories on the same physical disk

This is the end of the content of "Optimization of Order By Index in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.