MySQL Order By index optimization method
- 2020-05-13 03:39:27
- OfStack
Although 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 the additional ORDER BY fields are included in the WHERE clause.
Use the MySQL Order By index
Several of the following queries will use the index to resolve the ORDER BY or GROUP BY sections:
No index MySQL Order By is used
In other cases, MySQL cannot use an index to satisfy ORDER BY, although it will use the index to find records that match the WHERE clause. These are as follows:
* do ORDER BY for different index keys:
SELECT * FROM t1 ORDER BY key1, key2;
* do ORDER BY on the discontinuous index key section:
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 to do ORDER BY:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
* there are many table 1 joins, and none of the fields in ORDER BY in the records read are all from the first nonfrequent table (that is, the first table in the results of EXPLAIN analysis has an const join type).
* different ORDER BY and GROUP BY expressions are used.
* records in table indexes are not stored in order. For example, the HASH and HEAP tables are like this.
By executing EXPLAIN SELECT... ORDER BY, you know if MySQL is using an index in the query. If the value of the Extra field is Using filesort, then MySQL cannot use the index. For details, see "7.2.1 EXPLAIN Syntax (Get Information About a SELECT)". When the results must be sorted, MySQL 4.1 previously used the following filesort algorithm:
One problem with this approximation algorithm is that the database reads the record twice: once when the WHERE clause is estimated, and the second time when it is sorted. Although the records were read successfully the first time (for example, a full table scan was performed), the second time 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 values and record locations, but also the fields required in the query. This avoids having to read the record twice. The approach of the improved filesort algorithm is roughly as follows:
1. Read the record matching the WHERE clause as before.
2. Relative to each record, 1 corresponding; 'tuple' information information, including index key values, record locations, and all fields required in the query.
Sort 'tuple' information by index key.
4. Read records sequentially, but from a list of 'tuples' that have already been sorted, instead of reading them again from a table.
With the improved filesort algorithm, 'tuples' take up more space than' pairs' than the original, and they rarely fit neatly into the sort buffer (the size of the buffer is determined by the value of sort_buffer_size). Therefore, this may require more I/O operations, resulting in a slower improved algorithm. To avoid slowing it down, this optimization method is only used if the sum of the size of the additional fields in the sort 'tuple' exceeds the size of the system variable max_length_for_sort_data (a sign that the value of this variable is set too high is the high disk load and the low CPU load). To speed up ORDER BY, the first step is to see if MySQL can use indexes instead of an extra sort process. If you can't use an index, try following these strategies:
* increase the value of sort_buffer_size.
* increase the value of read_rnd_buffer_size.
* modify tmpdir to point to a dedicated file system with a lot of spare space.
If you are using MySQL 4.1 or later, this option allows you to have multiple paths in the format of a loop. The paths are separated on Unix by a colon (':') and on Windows, NetWare, and OS/2 by a semicolon ('; '). You can use this feature to spread the load evenly over several directories. Note: these paths must be directories distributed on different physical disks, not different directories on the same physical disk.
Use the MySQL Order By index
Several of the following queries will use the index to resolve the ORDER BY or GROUP BY sections:
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;
No index MySQL Order By is used
In other cases, MySQL cannot use an index to satisfy ORDER BY, although it will use the index to find records that match the WHERE clause. These are as follows:
* do ORDER BY for different index keys:
SELECT * FROM t1 ORDER BY key1, key2;
* do ORDER BY on the discontinuous index key section:
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 to do ORDER BY:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
* there are many table 1 joins, and none of the fields in ORDER BY in the records read are all from the first nonfrequent table (that is, the first table in the results of EXPLAIN analysis has an const join type).
* different ORDER BY and GROUP BY expressions are used.
* records in table indexes are not stored in order. For example, the HASH and HEAP tables are like this.
By executing EXPLAIN SELECT... ORDER BY, you know if MySQL is using an index in the query. If the value of the Extra field is Using filesort, then MySQL cannot use the index. For details, see "7.2.1 EXPLAIN Syntax (Get Information About a SELECT)". When the results must be sorted, MySQL 4.1 previously used the following filesort algorithm:
1. Read the record by the index key, or scan the data table. Those don't match WHERE The record of clauses will be skipped.
2. Each record is used in the buffer 1 'for ' Store the 2 Value (index key and record pointer). The size of the buffer depends on system variables sort_buffer_size The value of.
3. When the buffer is slow, it runs qsort (quicksort) and store the results in a temporary file. Save the stored block pointer (if all 'pairs' are correct) ' The values can be stored in the buffer, eliminating the need to create a temporary file.
4. Do this until all the records are read.
5. do 1 The next multiple merge will be as many as MERGEBUFF ( 7 ) the block of the region is saved in another 1 In a temporary file. Repeat this operation until all are in the first 1 All the blocks of the file are put in the first 2 A file.
6. Repeat until the number of remaining blocks is less than MERGEBUFF2 (15) .
7. In the final 1 In a multiple merge, only the pointer to the record (the last part of the sorted index key) is written to the result file.
8. Reads the records in order by reading the record pointer in the result file. To optimize this operation, MySQL Put the record pointer read into 1 And use it to read the records in order and place them in the buffer. The size of the buffer is determined by system variables read_rnd_buffer_size The value of. The code for this step is in the source file `sql/records.cc' In the.
One problem with this approximation algorithm is that the database reads the record twice: once when the WHERE clause is estimated, and the second time when it is sorted. Although the records were read successfully the first time (for example, a full table scan was performed), the second time 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 values and record locations, but also the fields required in the query. This avoids having to read the record twice. The approach of the improved filesort algorithm is roughly as follows:
1. Read the record matching the WHERE clause as before.
2. Relative to each record, 1 corresponding; 'tuple' information information, including index key values, record locations, and all fields required in the query.
Sort 'tuple' information by index key.
4. Read records sequentially, but from a list of 'tuples' that have already been sorted, instead of reading them again from a table.
With the improved filesort algorithm, 'tuples' take up more space than' pairs' than the original, and they rarely fit neatly into the sort buffer (the size of the buffer is determined by the value of sort_buffer_size). Therefore, this may require more I/O operations, resulting in a slower improved algorithm. To avoid slowing it down, this optimization method is only used if the sum of the size of the additional fields in the sort 'tuple' exceeds the size of the system variable max_length_for_sort_data (a sign that the value of this variable is set too high is the high disk load and the low CPU load). To speed up ORDER BY, the first step is to see if MySQL can use indexes instead of an extra sort process. If you can't use an index, try following these strategies:
* increase the value of sort_buffer_size.
* increase the value of read_rnd_buffer_size.
* modify tmpdir to point to a dedicated file system with a lot of spare space.
If you are using MySQL 4.1 or later, this option allows you to have multiple paths in the format of a loop. The paths are separated on Unix by a colon (':') and on Windows, NetWare, and OS/2 by a semicolon ('; '). You can use this feature to spread the load evenly over several directories. Note: these paths must be directories distributed on different physical disks, not different directories on the same physical disk.