首页 > 数据库 >聊聊MySQL是如何处理排序的

聊聊MySQL是如何处理排序的

时间:2024-05-16 09:29:54浏览次数:46  
标签:sort buffer 查询 索引 a2 聊聊 MySQL 排序

本文分享自华为云社区《MySQL怎样处理排序⭐️如何优化需要排序的查询?》,作者:菜菜的后端私房菜。

前言

在MySQL的查询中常常会用到 order by 和 group by 这两个关键字

它们的相同点是都会对字段进行排序,那查询语句中的排序是如何实现的呢?

当使用的查询语句需要进行排序时有两种处理情况:

  1. 当前记录本来就是有序的,不需要进行排序
  2. 当前记录未保持顺序,需要排序

使用索引保证有序

对于第一种情况,常常是使用二级索引中索引列的有序来保证结果集有序,从而不需要进行排序

对于表a,为a2建立二级索引,那么在二级索引上a2就是有序的

 CREATE TABLE `a` (
   `a1` int(11) NOT NULL AUTO_INCREMENT,
   `a2` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
   `a3` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`a1`),
   KEY `idx_a2` (`a2`)
 ) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8;

select * from a order by a.a2 limit 10

当优化器选择使用a2索引时,a2列的记录本身就是有序的,因此不需要再使用其他开销进行排序

image.png

当然,优化器也有可能不使用a2索引(当优化器认为使用a2回表开销太大时会使用全表扫描)

image.png

当优化器使用的索引上a2无序时,则会通过其他手段对结果进行排序

filesort

当执行计划的Extra附加信息中出现 Using filesort 时,会使用sort_buffer对结果进行排序

sort_buffer是一块用于排序的内存,sort_buffer可能存放查询需要的所有字段,也可能只存放需要排序的字段和主键

show variables like 'max_length_for_sort_data'

当查询需要的字段长度小于 max_length_for_sort_data 时,则会将查询需要的所有字段放入sort_buffer中,然后对需要排序的列进行排序,最后返回结果

image.png

当查询需要的字段长度大于 max_length_for_sort_data 时,只会将需要排序的字段和主键值放入sort_buffer中,等到排序后再去查询聚簇索引获取需要查询的列(相当于又多了一次回表)

image.png

在sort_buffer中进行排序时,如果内存足够则会在内存中进行排序,如果内存不够则会使用磁盘的临时文件来辅助排序

开启 optimizer_trace 可以查看是否使用临时文件辅助排序

 #开启优化器追踪
 SET optimizer_trace='enabled=on'; 
 ​
 #sql语句
 select * from student order by student_name limit 10000;
 ​
 #查看优化器追踪的信息
 SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G;

排序使用的算法是归并算法,先分割成多个小文件排序再进行合并

其中number_of_tmp_files 为使用到的临时文件数量,sort_buffer_size 为sort_buffer大小

image.png

因此当使用order by、group by等需要排序的关键字时,最好建立合适的索引

如果数据量小可以在sort buffer中排序,如果数据量太大还需要与磁盘交互

总结

当查询语句需要排序时会分为不用排序和需要排序两种情况

当使用的索引有序时则不用再进行排序,通过索引来保证有序

当使用的索引无序时则会使用sort_buffer进行排序,当查询字段的长度未超过限制时,sort_buffer中每条记录会存储需要查询的列

如果超过限制,则sort_buffer只会存储需要排序的列和主键值,排序后再通过主键值进行回表获取需要查询的列

当数据量太大不够在内存中排序完,会使用磁盘页辅助排序,使用归并算法将排序数据分散在多个页再合并

可以通过追踪优化器 optimizer_trace 分析内容查看辅助页的数量等信息

为需要排序的列建立合适的索引,避免使用磁盘页辅助排序

当无法使用索引时可以调整sort buffer 或 max_length_for_sort_data(谨慎)

 

点击关注,第一时间了解华为云新鲜技术~

 

标签:sort,buffer,查询,索引,a2,聊聊,MySQL,排序
From: https://www.cnblogs.com/huaweiyun/p/18195305

相关文章

  • 快速排序代码实现
    代码如下:1#include<stdio.h>2//快速排序函数,形参列表为数组,左指针位置,右指针位置,int*arr等价于intarr[]3voidQkSort(int*arr,intleft,intright){4if(left>right)//左指针位置必须大于右指针位置5{6return;7}8//变......
  • 配置MySQL主从复制和读写分离
    实验环境序号主机名IP地址备注1mysql-master192.168.204.201MySQL主库2mysql-slave192.168.204.202MySQL从库3appserver192.168.204.111应用服务器安装配置MySQL数据库1.使用yum安装mysql和mysql-serveryuminstall-ymariadbmariadb-server2.启......
  • 二维数组排序
    为了更灵活地控制排序字段和排序顺序,可以修改DataSorter类,使其能够通过参数指定排序字段和排序顺序。以下是实现方法:DataSorter类<?phpclassDataSorter{/***按指定字段和顺序排序二维数组**@paramarray$data要排序的二维数组*@param......
  • gorm实现MySQL的INSERT INTO ... ON DUPLICATE KEY UPDATE差异化插入和更新
    比如插入f_create_uid,更新时忽略f_create_uid,只更新f_update_uid。可使用gorm的BeforeCreate和BeforeUpdate钩子,这两个钩子分别在创建和更新记录之前被调用。//BeforeCreate在创建记录之前调用func(dob*MyStruct)BeforeCreate(tx*gorm.DB)(errerror){dob......
  • mysql中explain命令详解
    前言我们可以使用explain命令来查看SQL语句的执行计划,从而帮助我们优化慢查询。使用注意:使用的mysql版本为8.0.28数据准备CREATETABLE`tb_product2`(`id`bigintNOTNULLAUTO_INCREMENTCOMMENT'商品ID',`name`varchar(20)DEFAULTNULLCOMMENT'商品......
  • Linux Debian12 部署MySql 并建立外部连接
    一.下载MySql下载最新软件包。也可以在命令界面下使用下载最新的发行包。wgethttps://repo.mysql.com/mysql-apt-config_0.8.29-1_all.deb下载完成后,使用命令进行安装dpkg-imysql-apt-config_0.8.29-1_all.deb执行完后会跳转到安装MySql配置界面​TAB进行保存,选中OK,然......
  • mysql表名大小写敏感
    通过搜索了解到mysql在win下大小写不明感,在linux下大小写敏感本欲让ai写个脚本转表名,两次回答的结果都不能用后发现修改mysqld.conf中的lower_case_table_names=1可以忽略大小写但修改过后启动失败官方回答是初始化后不能再修改该参数,如需要修改需要重新初始化为了不折腾,......
  • MySQL存储过程中如何使用ROLLBACK
    在MySQL存储过程中,可以使用ROLLBACK来撤销之前执行的所有未提交的更改。当与BEGIN和COMMIT结合使用时,ROLLBACK可以帮助您管理事务并确保数据的完整性。以下是一个示例存储过程,它使用TRY...CATCH块来捕获异常,并在需要时执行ROLLBACK。请注意,MySQL本身并不直接支持TRY...CATCH错误......
  • 排序、过滤、分页
    排序、过滤、分页1.排序1.1使用条件#必须是查询所有接口--》restful规范中:地址栏中带过滤条件 http://127.0.0.1:8008/app01/api/v1/books/?ordering=price#必须是继承GenericAPIView的视图类---》继承APIView是不能这么配置的1.2使用排序#1.引入相关排序相关......
  • Ubuntu20.04安装MySQL8主从
    ......