MySQL8中降序索引的示例分析
发布时间：2022-02-08 15:24:57 所属栏目：MySql教程 来源：互联网
导读：这篇文章将为大家详细讲解有关MySQL8中降序索引的示例分析，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。 前言 MySQL 8.0终于支持降序索引了。其实，从语法上，MySQL 4就支持了，但正如官方文档所言，they are pars
|
这篇文章将为大家详细讲解有关MySQL8中降序索引的示例分析，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。 前言 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.t1G *************************** 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 row in set (0.00 sec) 虽然c2列指定了desc，但在实际的建表语句中还是将其忽略了。再来看看MySQL 8.0的结果。 mysql> show create table slowtech.t1G *************************** 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 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 | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+ 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 | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+ 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 | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+ 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 | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+ 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> 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 | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+ 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 | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+ 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 | +------+ 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 | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 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 | +------+ 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 | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ row in set, 1 warning (0.01 sec) 不仅结果没有升序输出，执行计划中也没有“Using filesort”。 可见，MySQL 8.0对于group by操作确实不再进行隐式排序。 从5.7升级到8.0，依赖group by隐式排序的业务可要小心咯。 关于“MySQL8中降序索引的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。 （编辑：云计算网_泰州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
热点阅读
浙公网安备 33038102330476号