MySQL 8.0 终于支持降序索引了. 其实, 从语法上, MySQL 4 就支持了, 但正如官方文档所言,"they are parsed but ignored", 实际创建的还是升序索引.
无图无真相, 同一个建表语句, 看看 MySQL 5.7 和 8.0 的区别.
- create table slowtech.t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
- MySQL 5.7
mysql> show create table slowtech.t1\G
- *************************** 1. row ***************************
- Table: t1
- Create Table: CREATE TABLE `t1` (
- `c1` int(11) DEFAULT NULL,
- `c2` int(11) DEFAULT NULL,
- KEY `idx_c1_c2` (`c1`,`c2`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
- 1 row in set (0.00 sec)
虽然 c2 列指定了 desc, 但在实际的建表语句中还是将其忽略了. 再来看看 MySQL 8.0 的结果.
mysql> show create table slowtech.t1\G
- *************************** 1. row ***************************
- Table: t1
- Create Table: CREATE TABLE `t1` (
- `c1` int(11) DEFAULT NULL,
- `c2` int(11) DEFAULT NULL,
- KEY `idx_c1_c2` (`c1`,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
c2 列还是保留了 desc 子句.
降序索引的意义
如果一个查询, 需要对多个列进行排序, 且顺序要求不一致. 在这种场景下, 要想避免数据库额外的排序 -"filesort", 只能使用降序索引. 还是上面这张表, 来看看有降序索引和没有的区别.
- MySQL 5.7
- mysql> explain select * from slowtech.t1 order by c1,c2 desc;
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
- | 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index; Using filesort |
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
- 1 row in set, 1 warning (0.00 sec)
- MySQL 8.0
- mysql> explain select * from slowtech.t1 order by c1,c2 desc;
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
- | 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
- 1 row in set, 1 warning (0.00 sec)
两者的对比可以看出, MySQL 8.0 因为降序索引的存在, 避免了 "filesort".
这其实是降序索引的主要应用场景. 如果只对单个列进行排序, 降序索引的意义不是太大, 无论是升序还是降序, 升序索引完全可以应付. 还是同样的表, 看看下面的查询.
- MySQL 5.7
- mysql> explain select * from slowtech.t1 order by c1;
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
- | 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
- 1 row in set, 1 warning (0.00 sec)
- mysql> explain select * from slowtech.t1 order by c1 desc;
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
- | 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
- 1 row in set, 1 warning (0.00 sec)
虽然 c1 是升序索引, 但在第二个查询中, 对其进行降序排列时, 并没有进行额外的排序, 使用的还是索引. 在这里, 大家容易产生误区, 以为升序索引就不能用于降序排列, 实际上, 对于索引, MySQL 不仅支持正向扫描, 还可以反向扫描. 反向扫描的性能同样不差. 以下是官方对于降序索引的压测结果, 测试表也只有两列 (a,b), 建了一个联合索引 (a desc,b asc), 感兴趣的童鞋可以看看, http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/
而在 8.0 中, 对于反向扫描, 有一个专门的词进行描述 "Backward index scan".
- MySQL 8.0
- mysql> explain select * from slowtech.t1 order by c1;
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
- | 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
- 1 row in set, 1 warning (0.00 sec)
- mysql> explain select * from slowtech.t1 order by c1 desc;
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
- | 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Backward index scan; Using index |
- +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
- 1 row in set, 1 warning (0.00 sec)
终于不再对 group by 进行隐式排序
由于降序索引的引入, MySQL 8.0 再也不会对 group by 操作进行隐式排序.
下面看看 MySQL 5.7 和 8 中的测试情况
- create table slowtech.t1(id int);
- insert into slowtech.t1 values(2);
- insert into slowtech.t1 values(3);
- insert into slowtech.t1 values(1);
- MySQL 5.7
- mysql> select * from slowtech.t1 group by id;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- | 3 |
- +------+
- 3 rows in set (0.00 sec)
- mysql> explain select * from slowtech.t1 group by id;
- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
- | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary; Using filesort |
- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
- 1 row in set, 1 warning (0.00 sec)
"Using filesort", 代表查询中有排序操作, 从结果上看, id 列确实也是升序输出.
- MySQL 8.0
- mysql> select * from slowtech.t1 group by id;
- +------+
- | id |
- +------+
- | 2 |
- | 3 |
- | 1 |
- +------+
- 3 rows in set (0.00 sec)
- mysql> explain select * from slowtech.t1 group by id;
- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
- | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary |
- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
- 1 row in set, 1 warning (0.01 sec)
不仅结果没有升序输出, 执行计划中也没有 "Using filesort".
可见, MySQL 8.0 对于 group by 操作确实不再进行隐式排序.
从 5.7 升级到 8.0, 依赖 group by 隐式排序的业务可要小心咯.
参考文档
http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/
来源: http://www.linuxidc.com/Linux/2018-05/152499.htm