How MySQL optimizes ORDER

I have been studying mysql database optimization over the past few days. I checked the information on the Internet and stayed here. I will continue my new research experience later. Below are some online materials

In some cases, MySQL can directly use indexes to satisfy an order by or group by clause without additional sorting.

Although order by does not match the ORDER of the index, the index can still be used, as long as the unused index part and all the additional order by fields are included in the WHERE clause.

The following queries use indexes to solve 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;

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

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

2> order by on the non-consecutive index key:
SELECT * FROM t1 WHERE key2 = constant order by key_part2;

3> both ASC and DESC are used:
SELECT * FROM t1 order by key_part1 DESC, key_part2 ASC;

4> the index key used for searching records and the order by statement are not the same:
SELECT * FROM t1 WHERE key2 = constant order by key1;

5> records in Table indexes are not stored in order. For example, the HASH and HEAP tables are like this.

BY executing explain select... order by, you can see whether MySQL uses an index in the query. If the value of the Extra field is Using filesort, MySQL cannot use the index.

View Indexes

The base number is the number of different values contained in the Data column. For example, if a data column contains values 1, 3, 7, 4, 7, and 3, the base number is 4. When the base of an index is higher than the number of rows in a data table (that is, a column contains many different values with few repeated values), it works best. If a data column contains many different ages, the index will quickly identify the data rows. If a data column is used to record gender (only "M" and "F" values), the index is of little use. If the probability of a value appearing is almost equal, half of the data rows may be obtained no matter which value is searched. In these cases, it is best not to use indexes at all, because when the query optimizer finds that a value has a high percentage in the table's data rows, it generally ignores the index, scan the entire table. The usual percentage line is "30% ".