Mysql数据库order by实现原理

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: Mysql数据库order by实现原理

业务背景

在应用开发过程中,业务场景可能需要根据某个字段进行排序,并返回指定结果集,就需要用到order by,今天我们来聊聊 order by 的执行流程。

假设你要查询城市是“北京”的所有人的名字,并且按照名字进行排序返回前1000个人的姓名和年龄。建表语句如下:

mysql> create table `user` (
  `id` int(11),
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  PRIMARY KEY(`id`),
  KEY `city` (`city`)
)ENGINE=InnoDB;

SQL语句如下:

mysql> select name, age from user where city = "北京" order by name limit 1000;

全字段排序

为了避免全表扫描,我们需要在city字段上创建索引,用explain命令查看这条语句的执行情况:

mysql> explain select name, age from user where city = "北京" order by name limit 1000;

可以看到 key 为 city,确实走了索引,扫描行数rows为4000,表示city为北京的记录有4000条,Extra字段中的“Using filesort”,表示需要排序,Mysql 会给每个线程分配一段内存用于排序,这段内存称为sort_buffer。

执行流程:

  1. 初始化sort_buffer;
  2. 从city索引找到第一个满足city=“北京”条件的主键Id,也就是途中的ID-x;
  3. 到主键ID-x索引找到这条记录,拿到name、city、age三个字段的值放入sort_buffer;
  4. 从city索引找下一条记录,取到主键id;
  5. 重复步骤3、4 直到city不满足条件为止,也就是到途中的ID-y为止;
  6. 对sort_buffer中的数据按照name做快速排序;
  7. 返回排序结果的前1000条记录;

说明:

步骤6中,按照name字段排序的动作,可能在内存中完成,也可能需要使用外部排序,取决于排序数据所需要的内存和参数sort_buffer_size。sort_buffer_size就是Mysql为排序分配的内存。如果排序的数据量小于sort_buffer_size,排序就在内存中完成,否则就需要利用磁盘的临时文件进行辅助排序。

rowid排序

上面讲到的全字段排序,我们在拿到主键id后,取了结果集的所需全部字段(name、city、age)放入sort_buffer,按照name排序完可以直接返回。这个算法有个问题,就是sort_buffer放入的字段太多,导致内存中放入的行数很少,可能分成很多个临时文件,排序的性能会很差。

rowid排序思路:只把要排序的name字段和主键id放入sort_buffer,也就是尽可能多的放入更多的行。但是,因为sort_buffer中少了city和age字段,不能直接返回了,执行流程如下:

  1. 初始化sort_buffer,确认放入两个字段(name、id);
  2. 从city索引找到第一个city=“北京”的主键id,也就是图中的ID-x;
  3. 根据主键id索引,拿到name、id两个字段放入sort_buffer;
  4. 从city索引找下一条记录,取到主键id;
  5. 重复步骤3、4 直到city不满足条件为止,也就是到途中的ID-y为止;
  6. 对sort_buffer中的数据按照name做快速排序;
  7. 遍历排序结果,取前1000行的id,再回到原表中,拿到city、age、name三个字段,返回结果。

全字段排序 和 rowid排序比较

Msyql的设计思想:如果内存够,尽量使用内存,尽量减少磁盘访问。

如果Mysql认为内存太小,就会使用rowid排序,好处是可以放入更多行的数据,缺点需要再回原表查询数据。

思考:order by是否一定需要排序?

答案是:不一定。可以利用覆盖索引,优化order by语句,比如,可以创建city_name_age的联合索引,该联合索引树的叶子结点的值就已经包含了我们需要的结果,这样的话就不需要回表了哦。

笔记参考于极客时间《MySQL实战45讲》


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
存储 关系型数据库 MySQL
MySQL主从复制原理和使用
本文介绍了MySQL主从复制的基本概念、原理及其实现方法,详细讲解了一主两从的架构设计,以及三种常见的复制模式(全同步、异步、半同步)的特点与适用场景。此外,文章还提供了Spring Boot环境下配置主从复制的具体代码示例,包括数据源配置、上下文切换、路由实现及切面编程等内容,帮助读者理解如何在实际项目中实现数据库的读写分离。
MySQL主从复制原理和使用
|
2月前
|
缓存 算法 关系型数据库
Mysql(3)—数据库相关概念及工作原理
数据库是一个以某种有组织的方式存储的数据集合。它通常包括一个或多个不同的主题领域或用途的数据表。
65 5
Mysql(3)—数据库相关概念及工作原理
|
28天前
|
存储 Java 关系型数据库
在Java开发中,数据库连接是应用与数据交互的关键环节。本文通过案例分析,深入探讨Java连接池的原理与最佳实践
在Java开发中,数据库连接是应用与数据交互的关键环节。本文通过案例分析,深入探讨Java连接池的原理与最佳实践,包括连接创建、分配、复用和释放等操作,并通过电商应用实例展示了如何选择合适的连接池库(如HikariCP)和配置参数,实现高效、稳定的数据库连接管理。
52 2
|
2月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1650 14
|
2月前
|
SQL 关系型数据库 MySQL
Mysql中搭建主从复制原理和配置
主从复制在数据库管理中广泛应用,主要优点包括提高性能、实现高可用性、数据备份及灾难恢复。通过读写分离、从服务器接管、实时备份和地理分布等机制,有效增强系统的稳定性和数据安全性。主从复制涉及I/O线程和SQL线程,前者负责日志传输,后者负责日志应用,确保数据同步。配置过程中需开启二进制日志、设置唯一服务器ID,并创建复制用户,通过CHANGE MASTER TO命令配置从服务器连接主服务器,实现数据同步。实验部分展示了如何在两台CentOS 7服务器上配置MySQL 5.7主从复制,包括关闭防火墙、配置静态IP、设置域名解析、配置主从服务器、启动复制及验证同步效果。
Mysql中搭建主从复制原理和配置
|
2月前
|
SQL 关系型数据库 MySQL
阿里面试:MYSQL 事务ACID,底层原理是什么? 具体是如何实现的?
尼恩,一位40岁的资深架构师,通过其丰富的经验和深厚的技術功底,为众多读者提供了宝贵的面试指导和技术分享。在他的读者交流群中,许多小伙伴获得了来自一线互联网企业的面试机会,并成功应对了诸如事务ACID特性实现、MVCC等相关面试题。尼恩特别整理了这些常见面试题的系统化解答,形成了《MVCC 学习圣经:一次穿透MYSQL MVCC》PDF文档,旨在帮助大家在面试中展示出扎实的技术功底,提高面试成功率。此外,他还编写了《尼恩Java面试宝典》等资料,涵盖了大量面试题和答案,帮助读者全面提升技术面试的表现。这些资料不仅内容详实,而且持续更新,是求职者备战技术面试的宝贵资源。
阿里面试:MYSQL 事务ACID,底层原理是什么? 具体是如何实现的?
|
2月前
|
SQL 关系型数据库 数据库
SQL数据库:核心原理与应用实践
随着信息技术的飞速发展,数据库管理系统已成为各类组织和企业中不可或缺的核心组件。在众多数据库管理系统中,SQL(结构化查询语言)数据库以其强大的数据管理能力和灵活性,广泛应用于各类业务场景。本文将深入探讨SQL数据库的基本原理、核心特性以及实际应用。一、SQL数据库概述SQL数据库是一种关系型数据库
74 5
|
2月前
|
SQL 搜索推荐 关系型数据库
MySQL 如何实现 ORDER BY 排序?
本文详细解析了MySQL中`ORDER BY`的实现原理及优化方法。通过解析与优化、执行及多种优化技术,如索引利用、内存排序、外部排序等,帮助你提升排序性能。了解其背后的机制,可显著优化查询效率。
47 4
|
2月前
|
SQL 缓存 关系型数据库
MySQL Limit实现原理
本文详细探讨了MySQL中`LIMIT`子句的实现原理及其在不同场景下的应用。`LIMIT`用于控制查询结果的行数,结合`OFFSET`可实现分页查询。其内部实现涉及解析器、优化器和执行器三部分,通过索引利用、子查询优化等提升性能。文章还提供了性能优化策略,如索引优化、覆盖索引及延迟关联等,并给出实践建议。
79 3
|
2月前
|
SQL 搜索推荐 关系型数据库
MySQL 如何实现 ORDER BY 排序?
在实际开发中,我们经常会使用 MySQL 的 `ORDER BY`进行排序,那么,`ORDER BY`是如何实现的排序的?我们该如何优化 `ORDER BY`的排序性能?这篇文章,我们来聊一聊。
33 3