Order BY Optimization-mysql

Original address: http://leihuang.org/2015/05/15/order-by-mysql/

The previous blog spoke about MySQL's paging optimization, which includes an optimization of the order BY keyword, which is analyzed today.

First look at the MySQL official document, how to talk about the order by keyword optimization. 8.2.1.15 ORDER by optimization

The following statements can be sorted using an index. Key_part1,key_part2 represents two combined two miniature indexes.

 SELECT *  from t1 ORDER  by Key_part1,key_part2,...; SELECT *  from t1 WHERE key_part1 = constant ORDER  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; SELECT *  from t1 WHERE key_part1 > Constant ORDER  by Key_part1 as C; SELECT *  from t1 WHERE Key_part1 < constant ORDER  by Key_part1 DESC; SELECT *  from t1 WHERE key_part1 = constant1  and Key_part2 > constant 2 ORDER  by Key_part2;

The following statements are not indexed.

• Sort two indexes that are not related to each other

SELECT * from T1 ORDER by Key1, Key2;

• Do an ORDER by on the non-contiguous index key section

SELECT * from T1 WHERE key2=constant ORDER by Key_part2;

• ASC and DESC are also used:

SELECT * from T1 ORDER by Key_part1 DESC, Key_part2 ASC;

• The index key used to search for records is not the same as the ORDER by:

SELECT * from T1 WHERE key2=constant ORDER by Key1;

Remember the most critical sentence: One SQL cannot use two indexes at the same time, and if necessary, you need to set up a federated index

To increase the speed of an ORDER by, it is first to see if MySQL can use the index rather than the extra ordering process. If you cannot use an index, you can try to follow these strategies:

Increase the value of the sort_buffer_size.

Increase the value of the read_rnd_buffer_size.

Take a look at the situation mentioned in the previous article, as follows

select * from a order by subject_code   //用不上索引select id from a order by subject_code  //能用上索引select subject_code from a order by subject_code    //能用上索引select * from a where subject_code = XX order by subject_code   //能用上索引

Why the first statement does not use the index is due to a two-level index problem:

InnoDB Level Two index is the current column+ corresponding to the primary key, query with the primary key value to the primary key index to query the corresponding row.

In InnoDB, each record in a secondary index contains the primary key
Columns for the row, as well as the columns specified for the
Secondary index. InnoDB uses this primary key value to search for the
Row in the clustered index.

SELECT * from a ORDER by Subject_code

This statement if the index on the subject_code to sort, then the Subject_code index in order to go to the primary key index check, but rather than do filesort to come fast.

So there's this optimization.

SELECT *FROM `XXX` AS `x`INNER JOIN(    SELECT `id`    FROM `XXX`    ORDER BY `id` DESC    LIMIT M,N) AS `t`USING(`id`)

The subquery uses an overlay index, so you do not need to scan the disk to find the desired row ID, and then you can go directly to the disk to fetch the required data.

Order BY Optimization-mysql