orderBy 排序优化

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

orderBy排序优化.png

排序的方式

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

尽可能使用索引字段来排序

filesort(文件排序)

2.1 双路排序

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

2.2 单路排序

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

优化策略

order by时,只query需要的字段

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

尝试提高sort_buffer_size

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

尝试提高max_length_for_sort_data

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

实例

数据表

*************************** ***************************
       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

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;

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;

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;//含有非索引字段

Orderby 排序优化

Orderby 排序优化

Orderby 排序优化

Orderby 排序优化

Orderby 排序优化

Image placeholder
吴进15506142982
未设置
  24人点赞

没有讨论,发表一下自己的看法吧

推荐文章
Carbon —— PHP 中日期 / 时间处理,你只需要这个扩展包就够了

在PHP中使用日期和时间并不是容易或清晰的任务。我们必须处理strtotime,格式化问题,大量计算等等。 这个漂亮的包叫做Carbon可以帮助在PHP开发中处理日期/时间变得更加简单、更语义化,从

基于 Laravel6.x 构建的博客应用,支持 Markdown,支持 RBAC 权限管理

基于Laravel6.x构建的博客应用,支持Markdown,支持图片拖拽上传,基于RBAC权限管理系统首页基于RBAC的权限管理后台,Dashboard页面统计了用户总数、文章发布总数、评论率、评论

第 10 节:复合类型 1.4 冒泡排序与数组去重

04冒泡排序packagemain import"fmt" funcmain(){ vararr[10]int=[10]int{9,1,5,6,8,2,10,7,4,3} //外层执行一次内层执

如果我问你:排序算法的「稳定性」有何意义?你怎么回答?

虽然我们在工作不一定经常去写排序算法,但是排序算法却是充斥着我们的程序生活,比如你不经意间调用了SDK中的某个sort算法,其背后无非是什么快排、归并等算法。而且在我们面试的过程中也会经常被问及,如果

mysql 查询按照中文进行排序

在mysql中我们使用orderby来实现查询排序,如:SELECT\*FROMmemberORDERBYidASC//查询用户表并按照id正序排序 SELECT\*FROMmemberORDERBY

小知识-SQL 自定义排序

问题场景在一次写业务的过程中,发现在使用sql查询数据的时候,不是按照我希望的顺序进行排序的,而是根据系统顺序进行排序的.o(╥﹏╥)oSELECT*FROMtable_nameWHEREidin(3

阿里提出针对多目标优化的全新算法框架,同时提升电商推荐场景 GMV 和 CTR

在推荐系统中,多目标优化一直是热门话题,阿里巴巴的XiaoLin、HongjieChen等人针对推荐中的多目标优化问题提出了一种基于帕累托效率的优化算法框架,并应用在电商推荐场景中,对GMV和CTR

PHP 性能优化 - php.ini 配置

内存 默认设置 memory_limit=128M 单个进程可使用的内存最大值,这个值的设定可以从以下几点考虑: 应用的类型。如果是内存集中型应用,可增加该值; 单个PHP进程平均消耗的内存,该值

MySQL 优化笔记

优化方向 SQL优化 sql优化分析 索引优化 优化数据库对象 优化表的数据类型 表拆分(水平、垂直) 反范式 使用中间表 优化mysqlserver mysql内存管理优化 log机制及优化

MySQL 优化笔记

优化方向 SQL优化 sql优化分析 索引优化 优化数据库对象 优化表的数据类型 表拆分(水平、垂直) 反范式 使用中间表 优化mysqlserver mysql内存管理优化 log机制及优化

Laravel-自定义全局函数-ChinaCircle 优化版

看过很多关于helper辅助文件的教程我进行个进一步优化 很多教程都会说,你在composer.json这个文件中通过添加一个自动加载的文件,就可以实现这个需求。但我认为这不是一个好的方式,当你在he

为高性能优化 PHP-FPM

PHP是无处不在的,可以说是互联网Web应用上使用最广泛的语言。 然而,它的高性能并不为人所知,尤其是在涉及到高并发系统时。这就是为什么对于这样特殊的用例,正在被Node(是的,我知道,它不是一种语

上线清单 —— 20 个 Laravel 应用性能优化项

让我们开始吧!假若你的laravel应用已经投入生产环境中。 从第一个用户,到第十,第一百,直到成千上万的用户!慢慢地,随着用户越多,你的网站会越来越慢 那我们应该如何做?细节决定成败 经过一番搜索

TPC-C解析系列03_TPC-C基准测试之SQL优化

TPC-C是一个非常严苛的基准测试模型,考验的是一个完备的关系数据库系统全链路的能力。这也是为什么在TPC-C的榜单前列,出现的永远只是大家熟知的那几家在业界有着几十年积累、从关系数据库理论开始发展就

TPC-C解析系列05_TPC-C基准测试之存储优化

TPC-C规范要求被测数据库的性能(tpmC)与数据量成正比。TPC-C的基本数据单元是仓库(warehouse),每个仓库的数据量通常在70MB左右(与具体实现有关)。TPC-C规定每个仓库所获得的

SACC 2019:云闪付APP架构优化实践之路

中国银联科技事业部架构师 程朝程朝2011年加入中国银联,拥有三年应用开发设计经验,三年MySQL与Redis内核开发设计经验,三年应用架构设计经验;擅长分布式系统设计,有丰富的系统设计与调优经验,现

Hadoop YARN:调度性能优化实践

背景YARN作为Hadoop的资源管理系统,负责Hadoop集群上计算资源的管理和作业调度。美团的YARN以社区2.7.1版本为基础构建分支。目前在YARN上支撑离线业务、实时业务以及机器学习业务。离

陆天炜: GoldenDB事务一致性处理机制优化历程

前言:GoldenDB是中兴通讯推出的一款自研的金融级交易型分布式数据。针对金融行业关注的数据库事务一致性问题,中兴通讯GoldenDB分布式数据库架构师陆天炜,在DTCC2019数据库大会上做了干货

etcd 在超大规模数据场景下的性能优化

作者|阿里云智能事业部高级开发工程师 陈星宇(宇慕)划重点etcd优化背景问题分析优化方案展示实际优化效果本文被收录在5月9日cncf.io官方blog中,链接:https://www.cncf.io

MySQL 百万级数据量分页查询方法及其优化

作者|大神养成记原文|  http://t.cn/RnvCJnm方法1:直接使用数据库提供的SQL语句语句样式: MySQL中,可用如下方法:SELECT*FROM表名称LIMITM,N适应场景: 适

Elasticsearch 亿级数据检索性能优化案例实战!

一、前言数据平台已迭代三个版本,从头开始遇到很多常见的难题,终于有片段时间整理一些已完善的文档,在此分享以供所需朋友实现参考,少走些弯路,在此篇幅中偏重于ES的优化,关于HBase,Hadoop的设计

MySQL 性能优化:8 种常见 SQL 错误用法!

1、LIMIT语句分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般DBA想到的办法是在type,name,create_time字段上加组合索引。这样条件排序都

广域网优化:企业如何使用SD-WAN

最近,NetworkComputing与EMA共同发起了一项题为“广域网优化:企业如何使用SD-WAN”的调查,以检研究企业广域网的变化,以及SD-WAN技术在其中所起的作用。调查参与者包括305名企

MySQL优化之覆盖索引的使用

查看测试表结构:mysql>showcreatetableim_message\G ***************************1.row**************************