Introduction to mysqlfilesort

There are two sorting methods for ORDERBY in MySQL: 1. Get ordered data using ordered indexes 2. Get ordered data using ordered indexes when using explain to analyze and Query files. The File Sorting displays Usingfilesort. 1. Use ordered indexes to obtain ordered data and retrieve words that meet filtering conditions as sorting Conditions

In MySQL, order by has two sorting methods: 1. Using an ordered index to obtain ordered data 2. Using the explain statement to analyze and Query files, 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 and retrieve words that meet filtering conditions as sorting Conditions

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)