MySQL 8.0 新特性:Descending Indexes

文若2年前技术文章922


一、前言

MySQL 8.0 之前的索引排序规则之前只允许 ASC 存储,创建时指定 DESC 也会被忽略,

截屏2021-01-14 上午10.59.32.png

截屏2021-01-13 下午5.38.01.png

8.0 版本为我们带来了 Descending Indexes 降序索引 👏👏👏

只能使用 ASC 索引,带来的问题就是使用 DESC 会出现更多的 using filesort 导致更多的性能损耗

二、动手实验 5.7

  1. 5.7 版本用了一张推荐系统的表,简单测试一下:

explain select similarity from new_correlation group by similarity order by similarity asc ;

Snipaste_2021-01-13_17-57-29.png

此时我们是在 5.7 版本里创建了一个 idx_similarity_desc(similarity desc)  发现虽然创建的是倒序索引,但是没有效果,依然是按照 ASC 组织的。下面是使用 DESC 排序下 SQL 的执行计划:

explain select similarity from new_correlation group by similarity order by similarity desc \G

Snipaste_2021-01-13_18-04-51.png

此时是在 5.7 版本下使用倒序 DESC 我们从执行计划里发现比 ASC 多了 Using temporaryfilesort 使用临时表和文件排序,一般我们认为执行计划中有这两个的性能都不咋地,而且执行频率高的话系统 IO 的压力也会非常大,比如羽勒的内站系统数据库,配置只有 2C4G 有一个业务必须使用 DESC 排序,并发上来时 CPU 可以顶得住 IOPS 却已经到极限了。


那么到底是有多大区别呢?请继续往下看


我们使用 MySQL 执行监控计数器看看二者的区别~

下方是使用 ASC 时的计数器 Handler_read_key 较高说明查询及索引均使用正确。

Snipaste_2021-01-13_19-21-35.png

下面是使用 DESC 时计数器,发现多了很多计数 Handler_read_rnd_next 从数据节点读取到下一个数据节点的次数,说明索引使用不正常,消耗的资源也是比 ASC 大许多。

Snipaste_2021-01-13_19-22-21.png

我们可以看到 5.7 即使我们创建 DESC 索引但是依然是按照 ASC 排序的,某些情况下是比较消耗性能的。

Snipaste_2021-01-13_19-29-18.png

三、8.0 新特性改善

我们到 8.0 中重新运行刚才的 SQL 我们添加 DESC 索引

alter table new_correlation add index idx_similarity_desc(similarity desc);

Snipaste_2021-01-13_19-35-44.png

发现 Using temporaryfilesort 已经没有了,使用了 idx_similarity_desc 索引,统计信息如下👇 创建的倒序索引不再失效。

Snipaste_2021-01-13_19-37-39.png

后面我们可以根据业务情况创建合适组织顺序的索引,减少更多不必要的性能损耗。

相关文章

MySQL创建用户授权

创建用户授权一、创建用户1.创建管理用户create user 'test'@'%' identified by 'Test123@'create user 'test'@'localhost' id...

Ranger-usync用户同步-LDAP

Ranger-usync用户同步-LDAP

1、修改配置**cd /opt/ranger-2.3.0-usersync/ **SYNC_SOURCE = ldap SYNC_LDAP_URL = lda...

CDP实操--Ranger开启ldap认证

CDP实操--Ranger开启ldap认证

集群中已经部署了ldap主主模式,并且使用haproxy进行负载均衡,keepalive提供了虚拟ip。页面配置:Ranger进行同步用户:验证:使用ldap用户登录页面:FAQ:1、admin超级管...

大数据组件Apache NiFi

大数据组件Apache NiFi

概述NiFi是美国国家安全局开发并使用了8年的可视化数据集成产品,2014年NAS将其贡献给了Apache社区,2015年成为Apache顶级项目。是一个基于Web图形界面,通过拖拽、连接、配置完成基...

clickhouse集群部署(一)

clickhouse集群部署(一)

1、rpm包下载安装部署完整包地址:https://packages.clickhouse.com/rpm/stable/📎clickhouse-common-static-dbg-22.8.4.7....

Presto临时设置session超时时间

Presto临时设置session超时时间

全局默认超时时间为200s:测试语句:select    "t7"."__fcol_16" "__fcol_22",    date_trunc('month', "t7"."__fcol_21"...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。