mysql order by的执行原理,以及优化思路,怎么优化order by

2024-08-20 18:04

本文主要是介绍mysql order by的执行原理,以及优化思路,怎么优化order by,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

mysql order by的执行原理,以及优化思路,怎么优化order by

select id, name,age,city,address from table_a t where t.city = '深圳' order by age;

Mysql Order by 排序:

1. 有这样几种方式:

1.1 全字段排序

全字段排序概念:
将最终结果集中所有的字段都放进 sort buffer中,然后在sort buffer中针对排序字段进行快速排序。 

数据太大会使用到临时文件进行辅助排序,使用临时文件排序的时候,用的是归并排序算法 。
sort_buffer_size 进行设置 sort_buffer 的大小。全字段排序只需要一次回表。

1.2 Rowid排序

Rowid排序概念:
只将与排序相关的字段和 rowId 放入 sort buffer,其余结果集需要用到的数据在排序完成后,通过 rowId 回表取得。

rowid 排序就是,只把查询SQL需要用于排序的字段和主键id,到sort_buffer中。Rowid排序需要回表两次。


上面这两种排序如果待排序的数据量大小没有超过sort_buffer_size大小,那么还是在 sort_buffer中排序,速度快。不涉及到外部排序。
 

2. 上面两种排序方式,那mysql order by语句是按照哪种方式进行排序的呢?
max_length_for_sort_data, 它表示MySQL用于排序行数据的长度的一个参数,如果单行的长度超过这个值,MySQL 就认为单行太大,就使用rowid 排序,否则使用全字段排序。


3. 全字段排序与rowid排序对比
全字段排序一次回表,性能高。sort_buffer内存不够的话,就需要用到磁盘临时文件,造成磁盘访问。
rowid排序需要两次回表,可排序的数据多。sort_buffer可以放更多数据,但是需要再回到原表去取数据,比全字段排序多一次回表。
一般情况下,对于InnoDB存储引擎,会优先使用全字段排序。可以发现 max_length_for_sort_data参数设置为1024,这个数比较大的。一般情况下,排序字段不会超过这个值,也就是都会走全字段排序。

但是有的时候,数据量太大,即使只将要排序的字段+主键id放入到 sort_buffer 中仍然存不下,这个时候就需要使用到外部文件了


4. explain 说明, 执行explain语句的时候,可以发现下面的信息:
explain语句的时候,发现 Using filesort 用到了临时文件进行排序
还能看到 Using temporary 用到了临时表,临时表存储引擎为 memory 。

如果当前 MySQL 使用的是内存临时表的话,将会直接使用 rowId 排序,这时的回表(回临时表取数据)只是在内存表中读数据,操作不涉及硬盘IO 。

MySQL 提供了 tmp_table_size 参数限制了内存临时表的大小,默认值是 16M,如果临时表大小超过了tmp_table_size,那么内存临时表就会转成磁盘临时表。这时的回表意味着磁盘随机读,所以会搭配全字段排序方式。


如果数据量大于tmp_table_size, 就不能用内存临时表了,需要用磁盘临时表,这时的回表意味着磁盘随机读,所以会搭配全字段排序方式。
 


5.  order by的一些优化思路
我们如何优化order by语句呢?

A. 联合索引 因为数据是无序的,所以就需要排序。如果数据本身是有序的,那就不用排了。而索引数据本身是有序的,我们通过建立联合索引,优化order by 语句。联合索引 - 就是通过要排序的字段和where条件的字段一起创建联合索引。

B.  覆盖索引: 上面的联合索引虽然不需要排序,但是需要回表查询字段, 就可以考虑覆盖索引 。 select查询的字段和order by排序字段以及where条件字段一起都建一个索引,覆盖索引,就是索引里包含了需要的所有数据(查询数据、排序数据)这样就不需要任何回表了


C. 调整参数优化
C1. sort_buffer 值调大一点。 值太小,数据量大的话,会借助磁盘临时文件排序.  默认是mysql 5.7.5  是256k, mysql 5.7.6是 8M

C2. max_length_for_sort_data值调大一点。值太小的话,order by会走rowid排序,会回表,降低查询性能。默认是1024大小

C3. tmp_table_size 值调大一点。 值太小就不能使用内存临时表,需要用到磁盘临时表,磁盘随机读影响性能。

在排序字段无索引的情况下,mysql使用优先队列进行排序(即堆排序)对 order by + limit n 排序语句进行优化。
过程:
    在所有待排序的数据,取数量为 LIMIT 的数据,构建一个堆。
    不断的取下一行数据,更新堆节点。
    当所有行的扫描完,得到最终的排序结果

这篇关于mysql order by的执行原理,以及优化思路,怎么优化order by的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1090791

相关文章

MySQL的JDBC编程详解

《MySQL的JDBC编程详解》:本文主要介绍MySQL的JDBC编程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言一、前置知识1. 引入依赖2. 认识 url二、JDBC 操作流程1. JDBC 的写操作2. JDBC 的读操作总结前言本文介绍了mysq

java.sql.SQLTransientConnectionException连接超时异常原因及解决方案

《java.sql.SQLTransientConnectionException连接超时异常原因及解决方案》:本文主要介绍java.sql.SQLTransientConnectionExcep... 目录一、引言二、异常信息分析三、可能的原因3.1 连接池配置不合理3.2 数据库负载过高3.3 连接泄漏

Linux下MySQL数据库定时备份脚本与Crontab配置教学

《Linux下MySQL数据库定时备份脚本与Crontab配置教学》在生产环境中,数据库是核心资产之一,定期备份数据库可以有效防止意外数据丢失,本文将分享一份MySQL定时备份脚本,并讲解如何通过cr... 目录备份脚本详解脚本功能说明授权与可执行权限使用 Crontab 定时执行编辑 Crontab添加定

C++统计函数执行时间的最佳实践

《C++统计函数执行时间的最佳实践》在软件开发过程中,性能分析是优化程序的重要环节,了解函数的执行时间分布对于识别性能瓶颈至关重要,本文将分享一个C++函数执行时间统计工具,希望对大家有所帮助... 目录前言工具特性核心设计1. 数据结构设计2. 单例模式管理器3. RAII自动计时使用方法基本用法高级用法

Java实现远程执行Shell指令

《Java实现远程执行Shell指令》文章介绍使用JSch在SpringBoot项目中实现远程Shell操作,涵盖环境配置、依赖引入及工具类编写,详解分号和双与号执行多指令的区别... 目录软硬件环境说明编写执行Shell指令的工具类总结jsch(Java Secure Channel)是SSH2的一个纯J

ShardingProxy读写分离之原理、配置与实践过程

《ShardingProxy读写分离之原理、配置与实践过程》ShardingProxy是ApacheShardingSphere的数据库中间件,通过三层架构实现读写分离,解决高并发场景下数据库性能瓶... 目录一、ShardingProxy技术定位与读写分离核心价值1.1 技术定位1.2 读写分离核心价值二

深度解析Python中递归下降解析器的原理与实现

《深度解析Python中递归下降解析器的原理与实现》在编译器设计、配置文件处理和数据转换领域,递归下降解析器是最常用且最直观的解析技术,本文将详细介绍递归下降解析器的原理与实现,感兴趣的小伙伴可以跟随... 目录引言:解析器的核心价值一、递归下降解析器基础1.1 核心概念解析1.2 基本架构二、简单算术表达

深入浅出Spring中的@Autowired自动注入的工作原理及实践应用

《深入浅出Spring中的@Autowired自动注入的工作原理及实践应用》在Spring框架的学习旅程中,@Autowired无疑是一个高频出现却又让初学者头疼的注解,它看似简单,却蕴含着Sprin... 目录深入浅出Spring中的@Autowired:自动注入的奥秘什么是依赖注入?@Autowired

MySQL中On duplicate key update的实现示例

《MySQL中Onduplicatekeyupdate的实现示例》ONDUPLICATEKEYUPDATE是一种MySQL的语法,它在插入新数据时,如果遇到唯一键冲突,则会执行更新操作,而不是抛... 目录1/ ON DUPLICATE KEY UPDATE的简介2/ ON DUPLICATE KEY UP

MySQL分库分表的实践示例

《MySQL分库分表的实践示例》MySQL分库分表适用于数据量大或并发压力高的场景,核心技术包括水平/垂直分片和分库,需应对分布式事务、跨库查询等挑战,通过中间件和解决方案实现,最佳实践为合理策略、备... 目录一、分库分表的触发条件1.1 数据量阈值1.2 并发压力二、分库分表的核心技术模块2.1 水平分