Descending Indexes【降序索引】(8.0.1)

12月 11, 2021 |

降序索引为order by f1 desc, f2, asc;提供避免文件排序可能
alter table t1 add index idx(f1 desc, f2 asc);

show index from t1\G
*************************** 1. row ***************************
Key_name: idx
Seq_in_index: 1
Column_name: f1
Collation: D
Cardinality: 2
*************************** 2. row ***************************
Non_unique: 1
Key_name: idx
Seq_in_index: 2
Column_name: f2
Collation: A
Cardinality: 160

可以看到f1列是降序索引,f2列是升序索引。
mysql> explain select f1,f2 from t1 order by f1 desc, f2 asc;

+----+------------+-------+-----+--------+------+----------+-------------+
| id | select_type| type |key | key_len| rows | filtered | Extra |
+----+------------+-------+-----+--------+------+----------+-------------+
| 1 | SIMPLE | index |idx | 8 | 160 | 100.00 | Using index |
+----+------------+-------+-----+--------+------+----------+-------------+

mysql> explain select f1,f2 from t1 order by f1 asc, f2 desc;

+----+------------+------+------+--------+------+----------+----------------------------------+
| id | select_type| type | key | key_len| rows | filtered | Extra |
+----+------------+------+------+--------+------+----------+----------------------------------+
| 1 | SIMPLE | index| idx | 8 | 160 | 100.00 | Backward index scan; Using index |
+----+------------+------+------+--------+------+----------+----------------------------------+

mysql> explain select f1,f2 from t1 order by f1 desc, f2 desc;

+----+------------+------+------+--------+------+----------+-----------------------------+
| id | select_type| type | key | key_len| rows | filtered | Extra |
+----+------------+------+------+--------+------+----------+-----------------------------+
| 1 | SIMPLE | index| idx | 8 | 160 | 100.00 | Using index; Using filesort |
+----+------------+------+------+--------+------+----------+-----------------------------+

mysql> explain select f1,f2 from t1 order by f1 asc, f2 asc;

+----+------------+------+-----+--------+------+----------+-----------------------------+
| id | select_type|type |key | key_len| rows | filtered | Extra |
+----+------------+------+-----+--------+------+----------+-----------------------------+
| 1 | SIMPLE |index |idx | 8 | 160 | 100.00 | Using index; Using filesort |
+----+------------+------+-----+--------+------+----------+-----------------------------+

可以看到对于1,2中情况都能避免文件排序

参考文档

https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html

Posted in: IT人生

Comments are closed.