Orderby 排序优化

简介: 在日常的业务开发中,order by 排序是少不了的。但要写出高效的排序SQL,需要先花点精力和时间来了解排序的底层原理,这样才能找到优化排序的好策略。

orderBy排序优化.png

排序的方式

index(索引排序,性能最佳)

尽可能使用索引字段来排序
AI 代码解读

filesort(文件排序)

2.1 双路排序

MySQL4.1 之前的版本,通过两次扫描磁盘,最终得到数据
先从磁盘中读取行指针和 order by 列,并对它们进行排序,然后扫描已经排好序的列表,按照列表中的值重新从列表中读出(再一次从磁盘中读),要对磁盘进行两次扫描,I\O是很耗时的。
AI 代码解读

2.2 单路排序

MySQL4.1 之后,增加的更优排序算法
从磁盘读取查询需要的所有列,按照order by列在buffer(缓冲区)对它们进行排序,然后扫描排序后的列表进行输出
它的效率要更快一些,避免了第二次读取数据(从磁盘读)并且把随机I\O变成了顺序I\O
但是它会使用过多空间,因为它把每一行都保存在内存中了
AI 代码解读
不足:
在sort_buffer中,单路算法比双路算法要多占用很多空间
因为单路算法是把所有字段都取出,所以有可能取出的数据总大小超出了 sort_bufferMySQL会给每个线程分配一块内存用于排序) 的容量,导致每次只能取 sort_buffer 容量大小的数据,进行排序(创建tmp文件,多路合并),排完再取出
sort_buffer容量太小,再排......从而多次I\O操作
本想着省一次I\O操作,反而导致了大量的I\O操作,反而得不偿失。
AI 代码解读
使用单路排序满足的条件:
1. 查询语句所取出的字段类型大小总和要小于 max_length_for_sort_data
2. 排序字段中不包含text和blob类型
AI 代码解读

优化策略

order by时,只query需要的字段

1. 当query的字段大小总和小于max_length_for_sort_data,而且排序字段不是TEXT|BLOB类型,会使用单路排序算法,否则使用多路排序算法
2. 两种算法的数据都有可能超出sort_buffer的容量,超出之后,创建tmp文件进行合并排序,导致多次的I\O,但是使用单路排序的风险更大,所以要提高sort_buffer_size
AI 代码解读

尝试提高sort_buffer_size

不管使用哪种算法,提高这个参数都会提高效率
要根据系统的自身能力去提高,因为这个参数是针对每个进程的
AI 代码解读

尝试提高max_length_for_sort_data

提高这个参数,会增加用改进算法的概率。
但如果设置得太高,数据总容量超出sort_buffer_size的概率会增大,明显症状是高的磁盘I\O活动和低的处理器使用率
AI 代码解读

实例

数据表

*************************** ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `age` int(10) NOT NULL DEFAULT '0',
  `city` varchar(20) NOT NULL,
  `addr` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age_city` (`name`,`age`,`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
AI 代码解读

order by能使用索引最左前缀

* select id,name,age,city from user order by name;
* select id,name,age,city from user order by name,age,city;
* explain select id,name,age,city from user order by name desc,age desc,city desc;
AI 代码解读

Orderby 排序优化

Orderby 排序优化

Orderby 排序优化

如果where使用索引的最左前缀定义为常量,则order by 能使用索引

* select * from user where name = 'zhangsan' order by age,city;
* select * from user where name = 'zhangsan' and age = 20 order by city;
* select * from user where name = 'zhangsan' and age > 20 order by age,city;
AI 代码解读

Orderby 排序优化

Orderby 排序优化

Orderby 排序优化

不能使用索引进行排序

select * from user order by name,age,city;//query*字段
select * from user order by addr;//非索引字段排序
select * from user order by name,addr;//含有非索引字段
select * from user where age = 20 order by city;//跳过了name字段,违反最左前缀法则
select * from user where name = 'zhangsan' order by city;//跳过了age字段,违反最左前缀法则
select * from user where name = 'zhangsan' order by age,addr;//含有非索引字段
AI 代码解读

Orderby 排序优化

Orderby 排序优化

Orderby 排序优化

Orderby 排序优化

Orderby 排序优化

目录
打赏
0
0
0
0
23
分享
相关文章
MaxCompute常见错误汇总(更新ing)
从今天开始,小编会为大家陆续解读MaxCompute常见问题,帮助大家快速上手MaxCompute,玩转大数据计算平台。
24118 7
体验AnalyticDB与通义千问搭建AI智能客服,赢取马克杯/卫衣等好礼!
体验AnalyticDB与通义千问搭建AI智能客服,赢取马克杯/卫衣等好礼!
AnalyticDB 实时数仓架构解析
AnalyticDB 是阿里云自研的 OLAP 数据库,广泛应用于行为分析、数据报表、金融风控等应用场景,可支持 100 trillion 行记录、10PB 量级的数据规模,亚秒级完成交互式分析查询。本文是对 《 AnalyticDB: Real-time OLAP Database System at Alibaba Cloud 》的学习总结。
193 1
聊聊DataWorks——这个一站式智能大数据开发治理平台
聊聊DataWorks——这个一站式智能大数据开发治理平台
553 2
优化AnalyticDB性能:查询优化与资源管理
【10月更文挑战第25天】在大数据时代,实时分析和处理海量数据的能力成为了企业竞争力的重要组成部分。阿里云的AnalyticDB(ADB)是一款完全托管的实时数据仓库服务,支持PB级数据的秒级查询响应。作为一名已经有一定AnalyticDB使用经验的开发者,我发现通过合理的查询优化和资源管理可以显著提升ADB的性能。本文将从个人角度出发,分享我在实践中积累的经验,帮助读者更好地利用ADB的强大功能。
218 0
深入解析:AnalyticDB中的高级查询优化与性能调优
【10月更文挑战第22天】 AnalyticDB(ADB)是阿里云推出的一款实时OLAP数据库服务,它能够处理大规模的数据分析任务,提供亚秒级的查询响应时间。对于已经熟悉AnalyticDB基本操作的用户来说,如何通过查询优化和性能调优来提高数据处理效率,是进一步提升系统性能的关键。本文将从个人的角度出发,结合实际经验,深入探讨AnalyticDB中的高级查询优化与性能调优技巧。
434 4
拥抱Data+AI|破解电商7大挑战,DMS+AnalyticDB助力企业智能决策
本文为数据库「拥抱Data+AI」系列连载第1篇,该系列是阿里云瑶池数据库面向各行业Data+AI应用场景,基于真实客户案例&最佳实践,展示Data+AI行业解决方案的连载文章。本篇内容针对电商行业痛点,将深入探讨如何利用数据与AI技术以及数据分析方法论,为电商行业注入新的活力与效能。
拥抱Data+AI|破解电商7大挑战,DMS+AnalyticDB助力企业智能决策
MaxCompute产品使用合集之如何使用SDK获取ODPS上的资源文件
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
122 1
MaxCompute操作报错合集之报错ODPS-0010000:System internal error - kWorkerOutOfMemory表明什么意思
MaxCompute是阿里云提供的大规模离线数据处理服务,用于大数据分析、挖掘和报表生成等场景。在使用MaxCompute进行数据处理时,可能会遇到各种操作报错。以下是一些常见的MaxCompute操作报错及其可能的原因与解决措施的合集。
254 7
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等

登录插画

登录以查看您的控制台资源

管理云资源
状态一览
快捷访问