Introduction to mysqlfilesort

In MySQL, order by has two sorting methods:

1. Use ordered indexes to obtain ordered data

2. File Sorting

When you use explain to analyze and query, you can use ordered indexes to obtain ordered data and display Using indexes. The File Sorting displays Using filesort.

1. Use ordered indexes to obtain ordered data

Retrieve the fields that meet the filtering conditions as sorting conditions, and the row pointer information that can be directly located to the row data, and perform the actual sorting operation in the Sort Buffer, the sorted data is then returned to the table based on the row pointer information to obtain the data of other fields requested by the client, and then returned to the client.

In this way, the Using index is displayed when you use explain to analyze the query. The File Sorting displays Using filesort.

Note: MySQL can only use one index during query. Therefore, if the WHERE condition already occupies an index, the index will not be used in sorting.

1.1 sort the results by index: order by using the index is conditional

1) return the selected field, that is, only the selected column is included (the field after select), not necessarily ):

Create table 'test '(

'Id' int (11) NOT NULLAUTO_INCREMENT,

'Rdate' datetime not null,

'Partition tid' int (11) not null,

'Mermerid' int (11) not null,

'Staffid' int (11) not null,

'Data' varchar (20) not null,

Primary key ('id '),

Unique key 'rdate' ('rdate', 'regiontid', 'mermerid '),

KEY 'partition tid' ('partition tid '),

KEY 'mermerid' ('mermerid '),

KEY 'staffid' ('staffid ')

) ENGINE = InnoDB AUTO_INCREMENT = 27 default charset = latin1

Mysql>

Explain select distinct TID from test where rdate = '2017-12-2011: 00: 00' order by partition tid, customerid;

+ ---- + ------------- + ------- + ------ + --------------- + ------- + --------- + ------- + ------ + ---------------------------- +

| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+ ---- + ------------- + ------- + ------ + --------------- + ------- + --------- + ------- + ------ + ---------------------------- +

| 1 | SIMPLE | test | ref | rdate | 8 | const | 10 | Using where; Using index |

+ ---- + ------------- + ------- + ------ + --------------- + ------- + --------- + ------- + ------ + ---------------------------- +

1 row in set (0.00 sec)

The columns selected by the Select statement use indexes instead of the following:

Mysql> explain select * from test where rdate = '2017-12-14 00:00:00 'order by partition tid, customerid;

+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------------------------- +

| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------------------------- +

| 1 | SIMPLE | test | ALL | rdate | NULL | 13 | Using where; Using filesort |

+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------------------------- +

2) only when all columns of order by must be included in the same index, and the order of the index is exactly the same as that of the ORDER by clause, all columns are sorted in the same direction (ascending or descending). (If both ASC and DESC modes are used, no index is used)

Mysql>

Xplain select partition TID from test order byrdate, partition tid;

+ ---- + ------------- + ------- + --------------- + ------- + --------- + ------ + ------------- +

| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+ ---- + ------------- + ------- + --------------- + ------- + --------- + ------ + ------------- +

| 1 | SIMPLE | test | index | NULL | rdate | 16 | NULL | 13 | Using index |

+ ---- + ------------- + ------- + --------------- + ------- + --------- + ------ + ------------- +

1 row in set (0.00 sec)

Mysql>

Explain select partition TID from test where rdate = "2011-12-16" order by partition tid, staffid;

+ ---- + ------------- + ------- + ------ + --------------- + ------- + --------- + ------- + ------ + --------------------------

| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+ ---- + ------------- + ------- + ------ + --------------- + ------- + --------- + ------- + ------ + --------------------------

| 1 | SIMPLE | test | ref | rdate | 8 | const | 1 | Using where; Using filesort

+ ---- + ------------- + ------- + ------ + --------------- + ------- + --------- + ------- + ------ + --------------------------

1 row in set (0.00 sec)

Because rdate and regiontid use the same index. The index is used for sorting. This also satisfies the prefix index. However, order by distinct tid and staffid; does not use an index because staffid and clustered TID are not the same index.

3) the combination of the where statement and the order by statement satisfies the leftmost Prefix:

Mysql>

Explain select partition TID from test whererdate = "2011-12-16" order by partition tid;

+ ---- + ------------- + ------- + ------ + --------------- + ------- + --------- + ------- + ------ + ---------------------------- +

| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+ ---- + ------------- + ------- + ------ + --------------- + ------- + --------- + ------- + ------ + ---------------------------- +

| 1 | SIMPLE | test | ref | rdate | 8 | const | 1 | Using where; Using index |

+ ---- + ------------- + ------- + ------ + --------------- + ------- + --------- + ------- + ------ + ---------------------------- +

1 row in set (0.00 sec)

4) if the query is connected to multiple tables, only the columns in the first table referenced by all columns in the order by clause can be used.

5) in other cases, mysql uses File Sorting, for example:

1) The where statement and order by statement use different indexes.

2) Check for too many rows without overwriting Indexes

3) columns in order by are not included in the same index, that is, different indexes are used.

4) both ASC and DESC are used for index columns.

5) The index column in The where or order by statement uses an expression, including a function expression.

6) The combination of the where statement and the order by statement satisfies the leftmost prefix, but the where statement uses conditional query. For details, see the 10th sentence. Although the where clause and order by clause constitute a condition with the leftmost fix of the index, the where clause uses conditional query.

Mysql> explain select partition TID from test where rdate> "2011-12-16" order by partition tid;

+ ---- + ------------- + ------- + --------------- + ------- + --------- + ------ + ----------------

| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

+ ---- + ------------- + ------- + --------------- + ------- + --------- + ------ + ----------------

| 1 | SIMPLE | test | range | rdate | 8 | NULL | 1 | Using where; Using index; Usingfilesort |

+ ---- + ------------- + ------- + --------------- + ------- + --------- + ------ + ----------------

1 row in set (0.00sec)

7) When left join is used, table fields on the right are sorted.

2. File Sorting

This filesort does not mean to sort disk files, but just tells us to perform a sorting operation. It is called filesort in the execution plan provided by MySQL Query Optimizer (viewed by the EXPLAIN command)

File Sorting uses the corresponding Sorting Algorithm to sort the obtained data in the memory: MySQL needs to sort the data in the memory, the memory used is the sorting area we set through the sort_buffer_size system variable. This sorting area is exclusive to each Thread, So multiple sort buffer memory areas may exist in MySQL at the same time.

In MySQL, there are actually two filesort implementation algorithms:

Dual-path sorting: first, obtain the corresponding sorting fields and row pointer information that can directly locate the row data based on the corresponding conditions, and then sort the data in sort buffer.

Single-path sorting: Retrieves all fields that meet the condition row at a time and sorts them in sort buffer.

Before MySQL, only the first sorting algorithm is used for dual-path sorting. The second algorithm is an improved algorithm starting with MySQL, the main purpose is to reduce the I/O operations required to access table data twice in the first algorithm, and change the two operations to one, but it also consumes more sortbuffer space. Of course, All Versions later than MySQL4.1 also support the first algorithm,

MySQL mainly compares the size of the system parameter max_length_for_sort_data and the sum of the Field Types retrieved by the Query statement to determine which sort algorithm to use. If max_length_for_sort_data is larger, the second optimized algorithm is used. Otherwise, the first algorithm is used. Therefore, if you want the order by operation to be as efficient as possible, you must set the max_length_for_sort_data parameter. A large number of sort waits have occurred in a colleague's database, resulting in a high system load and a long response time. Finally, it was discovered that MySQL used the traditional first sort algorithm, after the value of the max_length_for_sort_data parameter is increased, the system load is quickly mitigated and the response is much faster.

Optimize Filesort

How can we optimize sorting operations that cannot be avoided? Obviously, MySQL should try to use the second single-path algorithm for sorting. This can reduce a large number of random IO operations and greatly improve the efficiency of sorting.

1. Increase the settings of the max_length_for_sort_data parameter.

In MySQL, it is determined whether to use the old-fashioned or simplified Sorting Algorithm by using the max_length_for _ sort_data parameter. When the maximum length of all returned fields is smaller than this parameter value, MySQL selects the improved sorting algorithm. Otherwise, it selects the old algorithm. Therefore, if there is sufficient memory for MySQL to store non-sorted fields that need to be returned, you can increase the value of this parameter so that MySQL can choose to use the sort algorithm of the latest version.

2. Remove unnecessary return Fields

When the memory is not abundant, you cannot force MySQL to use the sort algorithm by adding the preceding parameters. Otherwise, MySQL may have to divide the data into multiple segments, then sort the data, which may be worth the candle. In this case, you need to remove unnecessary return fields to adapt the length of the returned results to the constraints of the max_length_for_sort_data parameter.

3. Increase the sort_buffer_size parameter settings

Increasing sort_buffer_size is not to allow MySQL to select the sort algorithm, but to minimize the number of data segments to be sorted in the sorting process, because segmentation will cause MySQL to have to use temporary tables for exchange sorting.