Java-96 深入浅出 MySQL 索引与排序机制详解与优化实践 Filesort

点一下关注吧！！！非常感谢！！持续更新！！！

🚀 AI篇持续更新中！（长期更新）

AI炼丹日志-31- 千呼万唤始出来 GPT-5 发布！“快的模型 + 深度思考模型 + 实时路由”，持续打造实用AI工具指南！📐🤖

💻 Java篇正式开启！（300篇）

目前2025年08月11日更新到：
Java-94 深入浅出 MySQL EXPLAIN详解：索引分析与查询优化详解
MyBatis 已完结，Spring 已完结，Nginx已完结，Tomcat已完结，分布式服务正在更新！深入浅出助你打牢基础！

📊 大数据板块已完成多项干货更新（300篇）：

包括 Hadoop、Hive、Kafka、Flink、ClickHouse、Elasticsearch 等二十余项核心组件，覆盖离线+实时数仓全栈！
大数据-278 Spark MLib - 基础介绍 机器学习算法 梯度提升树 GBDT案例 详解

请添加图片描述

MySQL 索引与排序机制详解

两种排序方式概述

MySQL 查询处理排序时主要支持两种方式：filesort 和 index 排序。这两种方式在性能上有显著差异，理解它们的区别对于数据库优化至关重要。

filesort 排序方式

filesort 是 MySQL 的通用排序算法，当无法使用索引排序时就会采用这种方式。其工作流程如下：

  1. 数据获取阶段：首先执行查询获取满足条件的记录
  2. 排序处理阶段：将结果集放入排序缓冲区(sort buffer)
    • 如果数据量小，完全在内存中完成排序
    • 如果数据量大，会使用临时文件进行多轮归并排序
  3. 结果返回阶段：将排序后的结果返回给客户端

典型的使用 filesort 的场景包括：

  • 对没有索引的列进行排序
  • 使用了 ORDER BY 与 GROUP BY 不同的表达式
  • 排序方向与索引定义方向不一致(如索引是 ASC 但查询要求 DESC)

index 排序方式

index 排序是利用索引本身的有序特性来避免额外的排序操作，其优势包括：

  1. 直接利用索引：按照索引顺序读取数据，天然有序
  2. 无额外开销：省去了排序缓冲区的分配和排序计算过程
  3. 性能优势：特别是对于大型结果集，性能提升显著

使用 index 排序的条件：

  • ORDER BY 子句中的列必须与索引列顺序完全匹配
  • 排序方向(ASC/DESC)必须与索引定义一致
  • 不能跳过索引中的列(遵循最左前缀原则)

性能对比示例

假设有一个包含百万条记录的用户表：

-- 情况1：filesort
SELECT * FROM users WHERE status = 'active' ORDER BY registration_date;-- 情况2：index排序
SELECT * FROM users WHERE status = 'active' ORDER BY id;  -- id是主键

在这个例子中，第一种查询可能需要进行完整的 filesort 操作，而第二种查询可以直接利用主键索引的有序性，性能差异可能达到几个数量级。

优化建议

  1. 为常用排序条件创建合适的索引
  2. 尽量让排序条件与索引定义完全匹配
  3. 监控慢查询日志中的"Using filesort"警告
  4. 适当增大 sort_buffer_size 参数可以减少磁盘临时文件的使用
  5. 考虑使用覆盖索引避免回表操作

算法对比

MySQL 文件排序（filesort）算法详解

双路排序（Two-pass sorting）

双路排序是 MySQL 中的传统排序算法，其工作流程如下：

  1. 第一次磁盘扫描：只读取排序字段（ORDER BY 子句中指定的列）和行指针（row pointer）
  2. 排序阶段：在 sort buffer 中对这些排序键进行排序
  3. 第二次磁盘扫描：根据排序后的行指针回表读取完整的数据行
  4. 结果返回：将排序后的完整数据返回给客户端

适用场景：

  • 当查询的列很多，或者列数据很大时
  • 当 max_length_for_sort_data 参数值设置较小时
  • 特别是当使用 SELECT * 查询大量列时

优点：减少了内存使用，因为只需要缓存排序键而非整行数据

单路排序（Single-pass sorting）

单路排序是 MySQL 优化的排序算法，其工作流程如下：

  1. 单次磁盘扫描：一次性读取查询需要的所有列（包括排序字段和其他字段）
  2. 内存排序：在 sort buffer 中对这些数据进行排序
  3. 结果返回：直接返回已排序的结果集

潜在问题：

  • 如果查询数据超出 sort buffer 大小（由 sort_buffer_size 参数控制）
  • 会导致多次磁盘读取操作
  • 可能需要创建临时表
  • 最终产生多次 I/O 操作，反而降低性能

优化建议：

  1. 避免使用 SELECT *，只查询必要的列
  2. 适当增加 sort_buffer_size 参数值
  3. 调整 max_length_for_sort_data 参数值（控制单行数据最大长度）

示例场景：

-- 不推荐的写法（可能导致单路排序性能问题）
SELECT * FROM large_table ORDER BY create_time DESC;-- 推荐的写法（减少数据传输量）
SELECT id, name, create_time FROM large_table ORDER BY create_time DESC;

参数调整示例：

-- 增加排序缓冲区大小（默认通常为256KB）
SET sort_buffer_size = 4 * 1024 * 1024;  -- 设置为4MB-- 调整单行排序数据最大长度（默认1024字节）
SET max_length_for_sort_data = 8192;  -- 设置为8KB

EXPLAIN

如果我们使用 EXPLAIN 命令分析 SQL 查询的执行计划时：

在结果集的 Extra 列中，如果出现"Using filesort"的提示，这表示 MySQL 在执行查询时使用了文件排序（filesort）操作。filesort 是一种成本较高的排序方式，当不能使用索引排序时，MySQL 会将结果集放入临时表并进行排序。这种情况下，我们应该考虑优化查询或添加适当的索引来提高性能。

优化 filesort 的常见方法包括：

  1. 为 ORDER BY 子句中的列创建合适的索引
  2. 确保 WHERE 条件中的列和 ORDER BY 列使用相同的索引
  3. 减少查询返回的数据量

相反，如果 Extra 列显示"Using Index"，这表示查询使用了覆盖索引（Covering Index），即查询所需的所有数据都可以从索引中获取，而不需要回表查询数据行。这种情况是最理想的：

  1. 查询性能最优，因为完全避免了访问数据表
  2. 可以使用 index 排序方式，效率远高于 filesort
  3. 减少了 I/O 操作，降低了内存使用

在实际开发中，我们应尽量设计查询使其能够使用覆盖索引，具体方法包括：

  1. 创建包含所有查询字段的复合索引
  2. 避免 SELECT * 查询，只选择必要的列
  3. 确保 WHERE、ORDER BY 和 GROUP BY 子句中的列被索引覆盖

例如，对于查询：

SELECT id, name FROM users WHERE status = 1 ORDER BY create_time;

创建索引 (status, create_time, id, name) 就能实现覆盖索引，避免 filesort 操作。

index方式

当我们使用 order by 子句索引组合满足索引最左前列的时候：

explain select id from wzk_user order by id;

执行结果如下所示：
在这里插入图片描述
当我们使用 where 子句 + order by子句 索引组合列满足索引最左前列的时候：

explain select id from user_info where age > 18 order by username;

对应的结果如下所示：

filesort方式

对索引列同时使用了 ASC 和 DESC：

explain select id from user_info order by age asc, username desc;

对应的结果如下所示：
在这里插入图片描述
where 子句和order by子句满足最左前缀，但where 子句使用了范围查询：

explain select id from user_info where age > 10 order by username;

对应的结果如下所示：
在这里插入图片描述

order by 或者 where + order by 索引没有满足索引最左前列：

explain select id from user_info order by username;

执行结果如下所示：
在这里插入图片描述
使用了不同的索引，MySQL每次只采用一个索引，order by涉及了两个索引：

explain select id from user_info order by username, age;

对应的结果如下所示：
在这里插入图片描述
where 子句与order by子句，使用了不同的索引：

explain select id from user_info order by abs(age);

对应的结果如下所示：
在这里插入图片描述

ASC DESC

  • ASC：升序（Ascending），从小到大。
  • DESC：降序（Descending），从大到小。
  • SQL 中默认是 ASC，显式写 DESC 会反转结果顺序。
SELECT * FROM users ORDER BY age ASC;   -- 年龄小的在前
SELECT * FROM users ORDER BY age DESC;  -- 年龄大的在前

聚簇索引（Clustered Index）与辅助索引（Secondary Index）

  1. 索引结构
  • InnoDB 存储引擎采用 B+ 树作为索引结构
  • 聚簇索引：
    • 叶子节点存储完整的数据记录（数据即索引）
    • 每个表只能有一个聚簇索引，通常建立在主键上
    • 物理存储顺序与索引顺序一致
  • 辅助索引（二级索引）：
    • 叶子节点只存储主键值，不包含完整数据
    • 通过回表操作获取完整数据
    • 一个表可以有多个辅助索引
  1. 遍历方式
  • 升序遍历：
    • 从 B+ 树最左叶子节点开始向右顺序扫描
    • 示例：SELECT * FROM table ORDER BY id ASC
  • 降序遍历：
    • 从 B+ 树最右叶子节点开始向左顺序扫描
    • 示例：SELECT * FROM table ORDER BY id DESC
  1. 性能特点
  • 聚簇索引优势：
    • 范围查询效率高（数据物理连续）
    • 主键查找只需一次IO
  • 辅助索引特点：
    • 需要两次查找（先查辅助索引，再查聚簇索引）
    • 覆盖索引可避免回表（查询字段都在索引中）
  1. 应用场景
  • 聚簇索引：
    • 主键查询
    • 范围查询（如 BETWEEN, >, <）
    • 排序操作
  • 辅助索引：
    • 非主键字段查询
    • 多条件查询（可建立复合索引）
    • 频繁查询但更新少的字段聚簇索引（Clustered Index）与辅助索引（Secondary Index）
  1. 索引结构
  • InnoDB 存储引擎采用 B+ 树作为索引结构
  • 聚簇索引：
    • 叶子节点存储完整的数据记录（数据即索引）
    • 每个表只能有一个聚簇索引，通常建立在主键上
    • 物理存储顺序与索引顺序一致
  • 辅助索引（二级索引）：
    • 叶子节点只存储主键值，不包含完整数据
    • 通过回表操作获取完整数据
    • 一个表可以有多个辅助索引
  1. 遍历方式
  • 升序遍历：
    • 从 B+ 树最左叶子节点开始向右顺序扫描
    • 示例：SELECT * FROM table ORDER BY id ASC
  • 降序遍历：
    • 从 B+ 树最右叶子节点开始向左顺序扫描
    • 示例：SELECT * FROM table ORDER BY id DESC
  1. 性能特点
  • 聚簇索引优势：
    • 范围查询效率高（数据物理连续）
    • 主键查找只需一次IO
  • 辅助索引特点：
    • 需要两次查找（先查辅助索引，再查聚簇索引）
    • 覆盖索引可避免回表（查询字段都在索引中）
  1. 应用场景
  • 聚簇索引：
    • 主键查询
    • 范围查询（如 BETWEEN, >, <）
    • 排序操作
  • 辅助索引：
    • 非主键字段查询
    • 多条件查询（可建立复合索引）
    • 频繁查询但更新少的字段

索引与排序的关系

索引排序的基本原理

当 ORDER BY 子句的字段顺序与索引顺序完全一致且排序方向相同时（都是 ASC 或都是 DESC），MySQL 优化器可以利用索引的有序特性直接返回已排序的结果集，这种优化称为"索引排序"（Index Order By）。这种情况下，执行计划中不会出现"Using filesort"的额外操作。

示例：

-- 假设有索引 idx_name_age (name, age)
-- 可以直接使用索引排序的情况
SELECT * FROM users ORDER BY name ASC, age ASC;

无法使用索引排序的情况

  1. 排序方向不一致：

    • 当索引字段的排序方向与 ORDER BY 指定的方向不一致时
    • 示例：ORDER BY name ASC, age DESC（索引是 ASC,ASC）

  2. 字段顺序不匹配：

    • ORDER BY 字段的顺序与索引定义的顺序不同
    • 示例：ORDER BY age, name（索引是 name, age）

  3. 混合使用 ASC 和 DESC：

    • 即使字段顺序匹配，但排序方向混合时
    • 示例：ORDER BY name DESC, age ASC

  4. 包含非索引字段：

    • ORDER BY 包含不在索引中的字段
    • 示例：ORDER BY name, email（email 不在索引中）

Filesort 操作

当无法使用索引排序时，MySQL 必须执行额外的排序操作（Filesort）：

  • 数据会被收集到排序缓冲区
  • 使用快速排序算法在内存中排序
  • 如果数据量太大，会使用临时文件进行外部排序
  • 在 EXPLAIN 结果中会显示"Using filesort"

最佳实践建议

  1. 设计匹配查询的索引：

    -- 为常见排序查询创建专用索引
CREATE INDEX idx_users_sort ON users(last_name ASC, first_name ASC, hire_date DESC);

  2. 使用覆盖索引：

    • 当查询只需要索引列时，可以完全避免访问表数据
    • 示例：SELECT user_id FROM users ORDER BY name（user_id 是主键）

  3. **避免 SELECT ***：

    • 只查询需要的列，增加使用覆盖索引的可能性

  4. 注意多列索引的顺序：

    • 确保索引列顺序与常用 ORDER BY 子句一致

  5. 考虑使用 DESC 索引（MySQL 8.0+）：

    CREATE INDEX idx_desc ON table_name (column_name DESC);

特殊情况说明

  1. LIMIT 优化：

    • 即使需要 Filesort，带有 LIMIT 的查询可能只需要排序部分数据

  2. 索引跳跃扫描（MySQL 8.0+）：

    • 在某些情况下，即使 ORDER BY 不是索引的最左前缀，也可能使用索引

  3. 分区表排序：

    • 在分区表上排序可能会有不同的性能特征# 索引与排序的关系

索引排序的基本原理

当 ORDER BY 子句的字段顺序与索引顺序完全一致且排序方向相同时（都是 ASC 或都是 DESC），MySQL 优化器可以利用索引的有序特性直接返回已排序的结果集，这种优化称为"索引排序"（Index Order By）。这种情况下，执行计划中不会出现"Using filesort"的额外操作。

示例：

-- 假设有索引 idx_name_age (name, age)
-- 可以直接使用索引排序的情况
SELECT * FROM users ORDER BY name ASC, age ASC;

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.pswp.cn/news/918580.shtml
繁体地址，请注明出处：http://hk.pswp.cn/news/918580.shtml

如若内容造成侵权/违法违规/事实不符，请联系多彩编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

MLAG双活网络妙招：BGP + 静态VRRP实现智能负载均衡

MLAG双活网络妙招：BGP + 静态VRRP实现智能负载均衡

引言 在现代数据中心和企业网络架构中&#xff0c;高可用性和负载均衡是核心需求。MLAG&#xff08;Multi-Chassis Link Aggregation&#xff09;技术结合BGP和静态VRRP的解决方案&#xff0c;为网络工程师提供了一种高效实现双活网络负载均衡的妙招。本文将深入探讨这一技术组…
阅读更多...
如何构建PHP表单页面及验证相关原理（PHP基础）

如何构建PHP表单页面及验证相关原理（PHP基础）

文章目录PHP表单 - 必需字段PHP - 必需字段PHP - 显示错误信息总结PHP表单 - 验证邮件和URLPHP - 验证名称PHP - 验证邮件验证URLPHP 完整表单实例 PHP表单 - 必需字段 该章内容将介绍如何设置表单必需字段及错误信息 PHP - 必需字段 我们首先给出一个表的验证规则&#xff0c;…
阅读更多...
API如何集成Web搜索功能：原理、实践与最佳选型

API如何集成Web搜索功能：原理、实践与最佳选型

API如何集成Web搜索功能&#xff1a;原理、实践与最佳选型 在现代智能应用开发中&#xff0c;模型生成结果往往需要融合最新的互联网信息。通过集成Web搜索工具&#xff0c;模型可以在生成响应前主动检索网络&#xff0c;获取实时数据。这一能力极大提升了智能系统的准确性和时…
阅读更多...
Spring Boot项目中调用第三方接口

Spring Boot项目中调用第三方接口

目录 步骤1: 添加依赖 步骤2: 配置HTTP客户端 配置RestTemplate 配置WebClient 步骤3: 在Service层调用接口 使用RestTemplate示例 使用WebClient示例 步骤4: 在Controller层调用Service 注意事项 总结 Spring Boot项目中调用第三方接口 在Spring Boot项目中调用第三…
阅读更多...
关系型数据库：原理、演进与生态全景——从理论基石到云原生的深度巡礼

关系型数据库：原理、演进与生态全景——从理论基石到云原生的深度巡礼

目录 一、引言&#xff1a;当“表”成为世界的通用语言 二、理论基石&#xff1a;关系模型与 ACID 三、引擎架构&#xff1a;一条 SQL 的奇幻漂流 四、存储机制&#xff1a;页、缓冲池与 WAL 五、并发控制&#xff1a;锁、MVCC 与隔离级别 六、SQL&#xff1a;声明式语言…
阅读更多...
【软考架构】计算机网络中的IP地址表示和子网划分

【软考架构】计算机网络中的IP地址表示和子网划分

在计算机网络中&#xff0c;IP地址用于唯一标识网络中的设备。IP地址的表示方式有两种&#xff1a;IPv4和IPv6。IPv4是当前使用最广泛的地址格式&#xff0c;而IPv6是为了解决IPv4地址耗尽问题而设计的。 1. IPv4地址 IPv4地址是一个32位的数字&#xff0c;通常用四个十进制数表…
阅读更多...
【后端】Spring @Resource和@Autowired的用法和区别

【后端】Spring @Resource和@Autowired的用法和区别

以下是关于 Resource 和 Autowired 两个依赖注入注解的详细对比说明&#xff0c;重点关注它们的区别和使用场景&#xff1a;&#x1f4cc; 核心区别总结特性Autowired (Spring)Resource (JSR-250 标准)来源Spring 框架原生注解Java 标准 (javax.annotation)默认注入方式按类型 …
阅读更多...
php+apache+nginx 更换域名

php+apache+nginx 更换域名

phpapachenginx 更换域名✅ 第 1 步&#xff1a;确认到底是谁在监听 80/443✅ 第 2 步&#xff1a;按监听者修改配置&#x1f539; 场景 A&#xff1a;Apache 直接监听 80/443&#x1f539; 场景 B&#xff1a;Nginx 监听 80/443&#xff0c;反向代理到 Apache✅ 第 3 步&#…
阅读更多...
AI 视频卫士：AI 无人机巡检，适配多元河道场景的治理利器

AI 视频卫士：AI 无人机巡检，适配多元河道场景的治理利器

河道治理&#xff0c;场景各异&#xff0c;难题不同。城市内河的生活垃圾、景区河道的景观破坏、工业园区河道的工业废料&#xff0c;每一种场景都对巡检工作有着独特的要求。AI 视频卫士&#xff0c;凭借强大的 AI 技术&#xff0c;针对不同河道应用场景&#xff0c;打造专属巡…
阅读更多...
累加和校验原理与FPGA实现

累加和校验原理与FPGA实现

累加和校验原理与FPGA实现写在前面一、基础原理二、举个例子2.1 进位累加2.2 回卷累加三、FPGA实现3.1 发送端&#xff08;产生校验和&#xff09;3.2 接收端&#xff08;累加和校验&#xff09;3.3 仿真结果写在后面写在前面 在上文《奇偶校验原理与FPGA实现》中&#xff0c;讲…
阅读更多...
深入解析Go设计模式：命令模式实战

深入解析Go设计模式：命令模式实战

什么是命令模式？ 命令模式(Command Pattern)是一种行为型设计模式，它将请求封装为独立对象，从而允许客户端通过不同的请求对象进行参数化配置。该模式支持请求的排队执行、操作记录以及撤销等功能。 命令模式UML类图如下所示： 命令模式包含五个核心角色，具体说明如下： …
阅读更多...
Pytest项目_day11（fixture、conftest）

Pytest项目_day11（fixture、conftest）

Fixture fixture是一种类似于setup、teardown&#xff0c;用于测试前后进行预备、清理工作的代码处理机制 相比于setup、teardown来说&#xff0c;fixture命名更灵活&#xff0c;局限性更少使用conftest.py配置里面可以实现数据共享&#xff0c;不需要import就能自动找到一些配…
阅读更多...
DAY 43 复习日

DAY 43 复习日

作业&#xff1a; kaggle找到一个图像数据集&#xff0c;用cnn网络进行训练并且用grad-cam做可视化 划分数据集 import torch import torch.nn as nn import torch.optim as optim from torchvision import datasets, transforms from torch.utils.data import DataLoader impo…
阅读更多...
Flink运行时的实现细节

Flink运行时的实现细节

一、Flink集群中各角色运行架构先说Flink集群中的角色吧&#xff0c;有三个分别是客户端(Client)、JobManager、TaskManager。客户端负责接收作业任务并进行解析&#xff0c;将解析后的二进制数据发送给JobManager&#xff1b;JobManager是作业调度中心&#xff0c;负责对所有作…
阅读更多...
思科、华为、华三如何切换三层端口？

思科、华为、华三如何切换三层端口？

三层交换机融合了二层交换技术与三层转发技术&#xff0c;具备强大的网络功能。主流厂商&#xff08;思科、H3C、华为&#xff09;的三层交换机均支持二层端口与三层端口的相互切换&#xff0c;但具体命令存在差异。本文将详细介绍三大厂商设备的端口切换方法及相关知识。一、各…
阅读更多...
springboot的基础要点

springboot的基础要点

Spring Boot 的核心设计理念是 ​​"约定优于配置"​​&#xff08;Convention Over Configuration&#xff09;&#xff0c;旨在简化 Spring 应用的初始搭建和开发过程。以下是需要掌握的核心基础要点&#xff1a;​一、核心机制​​自动配置 (Auto-Configuration)​…
阅读更多...
lesson36：MySQL从入门到精通：全面掌握数据库操作与核心原理

lesson36：MySQL从入门到精通：全面掌握数据库操作与核心原理

目录 一、引言&#xff1a;为什么选择MySQL&#xff1f; 二、MySQL安装与登录配置 2.1 环境准备 2.2 登录指令详解 三、数据库核心操作 3.1 数据库生命周期管理 3.2 数据库存储引擎选择 四、数据表设计与操作 4.1 表结构创建&#xff08;含数据类型详解&#xff09; …
阅读更多...
Spring源码解析 - SpringApplication run流程-prepareContext源码分析

Spring源码解析 - SpringApplication run流程-prepareContext源码分析

prepareContext源码分析 private void prepareContext(DefaultBootstrapContext bootstrapContext, ConfigurableApplicationContext context,ConfigurableEnvironment environment, SpringApplicationRunListeners listeners,ApplicationArguments applicationArguments, Bann…
阅读更多...
HIS系统：医院信息化建设的核心，采用Angular+Java技术栈，集成MySQL、Redis等技术，实现医院全业务流程管理。

HIS系统：医院信息化建设的核心，采用Angular+Java技术栈，集成MySQL、Redis等技术，实现医院全业务流程管理。

HIS系统在医院信息化建设中扮演着核心的角色。它是一个综合性的信息系统&#xff0c;旨在管理和运营医院的各种业务&#xff0c;包括门诊、住院、财务、物资、科研等。技术细节&#xff1a;前端&#xff1a;AngularNginx后台&#xff1a;JavaSpring&#xff0c;SpringBoot&…
阅读更多...
深度学习-卷积神经网络-LeNet

深度学习-卷积神经网络-LeNet

卷积神经网络是一种专门用于处理具有网格结构数据&#xff08;如图像、音频等&#xff09;的深度学习模型。它通过卷积层自动提取数据中的特征&#xff0c;利用局部连接和参数共享的特性减少了模型的参数数量&#xff0c;降低了过拟合的风险&#xff0c;同时能够有效地捕捉数据…
阅读更多...
最新文章

Copyright @ 2022~2023