MYSQL ORDER by optimization

ORDER by Optimization

In some cases, MySQL can use the index sort to avoid extra sorting.

Even if the ORDER BY statement column does not match index exactly, as long as there are no columns in index (not in the order by), the where statement appears in constant form. (leftmost prefix )

SELECT *  fromT1ORDER  byKey_part1,key_part2,...;SELECT *  fromT1WHEREKey_part1=constantORDER  byKey_part2;SELECT *  fromT1ORDER  byKey_part1DESC, Key_part2DESC;SELECT *  fromT1WHEREKey_part1= 1  ORDER  byKey_part1DESC, Key_part2DESC;SELECT *  fromT1WHEREKey_part1>constantORDER  byKey_part1ASC;SELECT *  fromT1WHEREKey_part1<constantORDER  byKey_part1DESC;SELECT *  fromT1WHEREKey_part1=Constant1 andKey_part2>Constant2ORDER  byKey_part2;

In some cases, index is still used to find the row that matches the WHERE clause, but MySQL does not use index to resolve the order by:

the 1:order by clause uses different indexes:

SELECT *  from ORDER  by Key1, Key2;

2: Use discontinuous index part (non-leftmost prefix of union key)

SELECT *  from WHERE Key2=ORDER by Key_part2;

3: Mix with ASC and DESC:

SELECT *  from ORDER  by DESC ASC;

4: The index of the obtained data row (in the WHERE clause) is not the same as that used in order by :

SELECT *  from WHERE Key2=ORDER by Key1;

an expression using index in the 5:order by clause :

SELECT *  from ORDER  by ABS (key); SELECT *  from ORDER  by - key;

6: when a join operation, the column of the ORDER BY clause is not the first non-const table;

7: Different order By,group by expressions:

8: Index is only added to the prefix of the ORDER BY clause column, which in this case does not resolve the sort. The E.g:order by column contains a char (20) type, but only the top 10bytes plus index;

9:table index unordered, the index of the hash (memory table);

Whether an index is sorted can be affected by the alias of the column, table T1 column A is indexed:

You can use Index to sort:

SELECT  from ORDER  by A;

No:

SELECT ABS  as  from ORDER  by A;

in the statement, order by refers to column A, and the column name of the SELECT clause is also a, but he is an alias, and the reference is ABS (a);

In the following statement, the order by reference column name is not the same as the column name in the select list, but select uses the A,index sort to be used (the result of the statement is completely different from the sort of abs (a))

SELECT ABS  as  from ORDER  by A;

By default, MySQL sorts all the groups Col1,col2 (group by Col1,col2), and if a query contains group by but wants to avoid the sort payload, you can suppress the sort by ordering by null.

INSERT  into Foo SELECT COUNT (*fromGROUPbyORDERbyNULL;

The dependent implicit group by sort is discarded in the mysql5.6. It is preferable to use an accurate ORDER BY clause.

MySQL has two filesort algorithms to get results. The original method uses only the column list in order by. The overridden method uses not only the columns in the ORDER BY clause, but the columns used in the query.

Which filesort algorithm does the optimizer choose? Under normal circumstances, using the second (outside the large object column, such as BLOB text ), both algorithms use the SORT_BUFFER_SIZE system variable:

The original Filesort algorithm works:

1: Reads all rows that meet the criteria according to the key value or the scan all table (where condition), skipping rows that do not satisfy the WHERE clause.

2: For each row, store (key value, row ID) pair in the sort buffer.

3: If all of the above pairs are in sort buffer, the temporary file will not be created, otherwise, when the sort buffer is full, the quicksort is executed in memory and the result is written into the temporary file, and a pointer is saved to execute the sorted block.

4: Repeat the above procedure until all the rows are read.

5: Perform a multi-merge sort to transfer the block of the first file to another temporary file. Repeat until the contents of the first file are all in the second file.

6: Merge buffer until 2 blocks are left

7: Last merge, write only rowid to result table

8. Read the data according to the order of ROWID in the sorting results. (An optimization scheme is also mentioned in the manual, but I do not think it can be optimized).

There are two reads in the Filesort, the first one is judged in the WHERE clause, and the other is after the value pairs is taken. However, even if the first access is sequential (e.g. scan all the table), but the second time they are random access (although the key row is ordered, but the line position does not ~!) );

Second Filesort algorithm: (avoid two reads, not record ROWID, but record reference columns used by the query)

1: Read all rows that satisfy the WHERE clause

2: For each row, the tuple records key value and the column referenced by the query

3: Sort and write to temporary files when buffer is full

4:merge sort all temporary files, retrieve ordered row data, read the required columns directly from the sorted tuple instead of accessing the base table two times

The modified method is longer than the original method. There is a good chance that a lot of IO will be generated, making the sorting slow. To avoid this problem, the optimizer will select the modified algorithm only if the length of all read columns is less than the MAX_LENGTH_FOR_SORT_DATA system variable.

When the Filesort is complete, the extra in the explain output will have a using Filesort, and the filesort_summary block in the optimizer trace output:

"Filesort_summary": {    0  ,   25192,  "Sort_mode": "<sort_key, additional_fields> "}

Where sort mode is the way to decide:

<sort_key,rowid> represents the original algorithm

<sort_key,addtitional_filed> represents a modified algorithm

To improve the sorting speed, you can check whether the index can be used if you cannot use:

1. Increase the size of the sort_buffer_size

2. Increase the size of the Read_rnd_buffer_size

3. Reduce space consumption by table design

4. Modify the Tmpdir directory to point to a dedicated file system

If an order by does not use an index, but has a limit clause, the optimizer may avoid merging temporary files and sorting them directly in memory

MYSQL ORDER by optimization