Introduction to the use of MySQL database indexes and order by clauses

In some cases,MySQL databaseDirect useIndexTo love oneORDEROr the group by clause, without additional sorting. Although order by is not exactly the same as the index ORDER, the index can still be used because all unnecessary index regions and all additional order by fields are included in the WHERE clause. This article describes how to use indexes and Order By clauses in MySQL databases.

MySQL Order By using Indexes

The following queries use indexes to process order by or group:

 
 

  
  
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 Indexes 　

In other cases, MySQL cannot use an index to favor order by, even though it uses an index to locate registration to match the WHERE clause. These situations are as follows:

Make order by: SELECT * FROM t1 order by key1, key2 for different index keys;

Order by: SELECT * FROM t1 WHERE key2 = constant order by key_part2;

Both ASC and DESC: SELECT * FROM t1 order by key_part1 DESC, key_part2 ASC;

The index keys used for search registration and order by are not uniform: SELECT * FROM t1 WHERE key2 = constant order by key1;

Many tables are connected at the same time, and the fields in order by in the read registration are not all from the first extremely number table, the connection type of the first table in the result of EXPLAIN analysis is not const ).

Different expressions of order by and group by are used.

Registration in Table indexes is not stored in order. For example, even for HASH and HEAP tables. Through explicit SELECT... order by, you can see whether MySQL uses indexes in queries. In case the value of the Extra field is Using filesort, MySQL cannot use the index.

When sorting the harvest, MySQL 4.1 used the following filesort algorithm:

1. Read and register based on the index key. Data Tables may be scanned. Those that cannot match the WHERE clause will be skipped.

2. Each record in the buffer uses a pair to store two value index keys and registration pointers ). The buffer size depends on the value of the system variable sort_buffer_size.

3. When the buffer is slow, run qsort to sort it quickly) and store the harvest in temporary files. Keep the block pointer of the storage. in case all the 'right' values can be kept in the buffer, no temporary files need to be created ).

4. Stick to the monopoly above until all the registrations are read.

5. Merge multiple parts sequentially to save the parts of up to MERGEBUFF7 in another temporary file. Repeat this monopoly until all the blocks in the first file are placed in the second file.

6. Repeat the above monopoly until the number of surplus blocks is smaller than MERGEBUFF2 (15 ).

7. When the final order is multiple, only the last part of the index key of the registered pointer is written to the harvest file.

8. Read the registration pointer in the harvest file in sequence. To optimize this monopoly, MySQL puts the registration pointer read into a large block and uses it to read and register in order and store the registration in the buffer. The buffer size depends on the value of the system variable read_rnd_buffer_size. The code for this link is in the source file 'sqlgexinghua. org/Fisher records. CC. One problem with this close algorithm is that the database reads two registrations: When the WHERE clause is evaluated sequentially, the second is the sorting. Although the first round of winning read registration for example, the sequential full table scan), the second is the random read index key has been sorted, but the registration has not been ).

In MySQL 4.1 and later versions, the filesort optimization algorithm is used to register not only the index key value and registration location, but also the fields required in the query. This avoids the need to read the registration twice. The improved filesort algorithm practices are as follows:

1. Follow the previous steps to read the WHERE clause registration.

2. A corresponding message is registered with each register, including the index key value, registration location, and all required fields in the query.

3. Sort the 'tuples 'messages based on the index key.

4. Read the registration in sequence, but read the registration from the list of 'tuples 'that have been sorted, rather than reading the data from the data table in sequence. Compared with the original filesort algorithm, the 'tuples 'must be longer than the' peer, they rarely match the buffer size in the SORT buffer, which is determined by the sort_buffer_size value ). Therefore, this may require more I/O monopoly, resulting in slower improved algorithms. To avoid slowing down, this optimization is only used to sort the sum of the size of the additional fields in the 'tuples 'beyond the value of the system variable max_length_for_sort_data. The value of this variable is too high, even if the disk load is high and the CPU load is low ).

To increase the order by speed, it depends on whether MySQL can use indexes instead of additional sorting processes. If you cannot use indexes, try to follow the following plan:

Adds the value of sort_buffer_size.

Add the value of read_rnd_buffer_size.

Correct tmpdir and point it to a dedicated File System with plenty of free space.

This section describes how to optimize the Order By index of a MySQL database.