MySQL性能优化(二)优化排序操作

俊达3年前技术文章757

排序是数据库的基本功能。


一个例子

SELECT *
FROM audit_log 
WHERE user_id = xxx
AND log_type = ‘xxxx’
ORDER BY gmt_create DESC


执行计划

2-1.png


看这里的执行计划:

key: idx_userid_logtype。使用了idx_userid_logtype索引。

key_len: 8。user_id为bigint

ref: const。

Extra: Using where; Using filesort。因为SQL中用到了order by,这里使用了filesort。


排序是一个耗资源的操作,需要消耗CPU和内存资源。如果需要排序的数据量超过了排序内存,需要将数据写入磁盘文件,进行多趟排序。

在上面这个例子中,我们可以利用索引的有序性来避免排序。这也是优化排序SQL的一个常用的方法。


使用索引消除排序

alter table audit_log
   drop key idx_userid_logtype,
   add KEY `idx_userid_logtype` (`user_id`,`log_type`,`gmt_create`)


我们在索引中加入gmt_create字段,再查看执行计划:


2-2.png


可以看到,Extra中没有了filesort相关内容。


前提条件

使用索引消除排序有几个前提条件:

1、排序字段前面的字段(这里是user_id, log_type),都需要以等值条件传入到where条件中(user_id=xx and log_type=xx)。

2、如果根据多个字段排序( order by col_1, col_2),则索引中相关字段也要以同样的顺序( index idx(col_1, col_2)



对于上述索引(user_id, log_type, gmt_create),下面的SQL就无法使用索引来消除排序。还是需要filesort。

SELECT *
FROM audit_log 
WHERE user_id = xxx
ORDER BY gmt_create DESC



相关文章

Redis 运维规范_运维管理规范

三、运维管理规范1、密码认证 云上 Redis 的权限控制:账号管理+白名单设置+阿里云子账号权限。对于线下 Redis 可以通过设置密码和 bind 参数文件控制访问。2、合理设置备份策略 Redi...

Elasticsearch数据生命周期如何规划

Elasticsearch中的open状态的索引都会占用堆内存来存储倒排索引,过多的索引会导致集群整体内存使用率多大,甚至引起内存溢出。所以需要根据自身业务管理历史数据的生命周期,如近3个月的数据op...

静默安装oracle11g单实例

环境: CentOS 7.8 11.2.0.4.0 orclp:172.16.104.31一、准备1、依赖包检查pdksh 在 redhat 上叫 ksh检查是否有安装root# rpm -q bin...

arm环境安装达梦数据库

arm环境安装达梦数据库

一、安装前准备1、创建用户和用户组groupadd dinstall useradd -g dinstall -m -d /home/dm...

数据湖技术之iceberg(七)Spark管理iceberg表

数据湖技术之iceberg(七)Spark管理iceberg表

1.SparkSQL设置catalog配置以下操作主要是SparkSQL操作Iceberg,同样Spark中支持两种Catalog的设置:hive和hadoop,Hive Catalog就是icebe...

lvm介绍和常用命令

lvm介绍和常用命令

在分区的时候,每个分区应该分多大是令人头疼的,而且随着长时间的运行,分区不管你分多大,都会被数据给占满。当遇到某个分区不够用时管理员可能甚至要备份整个系统、清除硬盘、重新对硬盘分区,然后恢复数据到新分...

发表评论    

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