从63 秒到 0.482 秒：深入剖析 MySQL 分页查询优化

在日常开发中，数据库查询性能问题就像潜伏的“地雷”，总在高并发或数据量庞大的场景下引爆。尤其是当你运行一条简单的分页查询时，结果却让用户苦苦等待，甚至拖垮了系统。这种情况你是否遇到过？

你可能会想：“我的表已经建立了索引，为什么还是这么慢？” 或者：“难道数据库引擎就没办法高效处理大数据分页吗？”

这篇博客将从 MySQL 索引机制入手，深入剖析其底层原理（B+树），结合实际场景讲解分页查询优化的技巧，并通过实验数据展示优化效果。只需稍加调整，就能让查询从 几十秒 缩短到 几百毫秒！

1. MySQL 索引机制

1.1 索引是什么？

索引是一种提高查询速度的数据结构。它的作用类似于书的目录，可以帮助 MySQL 快速找到目标数据，而不是逐页翻找。

1.2 MySQL 的索引类型

1. 聚簇索引（Clustered Index）：

   • InnoDB 存储引擎默认的主键索引。
   • 特点：数据和索引存储在一起，叶子节点存储的是完整行的数据。
   • 每个表只能有一个聚簇索引。
   • 示例：假设一张用户表以 id 为主键，索引结构如下：

     根节点 → 中间节点 → 叶子节点（存储完整行数据）
     

2. 辅助索引（Secondary Index）：

   • 除主键外的其他索引，例如普通索引和唯一索引。
   • 特点：叶子节点存储的是主键值，通过主键值回表查询完整数据。
   • 适用场景：用于加速非主键列的查询。

1.3 MySQL 缓存机制的变化

• MySQL 8.0 删除了查询缓存（Query Cache）：

  • 原因：查询缓存频繁失效，影响性能，在高并发写场景下尤为明显。
  • 查询缓存的替代：更高效的优化器和 InnoDB 缓存机制。

• MySQL 的 Buffer Pool：

  • 依旧是核心性能优化手段。
  • 功能：将数据页、索引页缓存到内存中，减少磁盘 I/O。
  • 特点：即使查询缓存被删除，Buffer Pool 仍然支持高效的索引查询和数据读取。

2. 索引的底层原理

2.1 什么是 B+树？

B+树是一种平衡多路搜索树，广泛应用于数据库和文件系统中，用于存储索引。

2.2 B+树的结构

1. 非叶子节点：
   • 只存储索引键，起到导航作用。
   • 减少了节点大小，提高了节点的分支因子。
2. 叶子节点：
   • 存储所有实际数据（聚簇索引）或主键值（辅助索引）。
   • 通过链表指针串联，便于范围查询。

2.3 B+树的特点

1. 平衡性：所有叶子节点都在同一层，查询效率稳定。
2. 磁盘友好：每个节点存储多个索引键，减少了磁盘 I/O 次数。
3. 范围查询高效：叶子节点的链表结构支持顺序遍历。
   

2.4 为什么 MySQL 使用 B+树？

• 相比 B 树：B+树的非叶子节点存储更多的索引键，更适合大规模数据存储。
• 相比哈希索引：B+树支持范围查询和排序，而哈希索引只支持等值查询。

3. 优化 SQL 排序分页查询的场景

3.1 问题描述

假设我们需要从 content 表中查询最近的第 2000000 条到第 2000010 条数据：

SELECT * FROM content ORDER BY create_time DESC LIMIT 2000000, 10;

3.2 存在的问题

1. 大偏移量（OFFSET）：

   • 数据库需要扫描并丢弃前 2000000 条记录，浪费资源。
   • 即使有索引，MySQL 仍需逐一读取和排序这些记录。

2. 全表扫描的风险：

   • 如果 create_time 没有索引，查询会触发全表扫描。

3.3 优化思路

• 利用子查询限定范围：
  • 子查询通过索引直接定位目标主键范围。
  • 主表查询通过主键精确匹配记录，减少无效扫描。

3.4 优化前后对比

优化前 SQL：

SELECT * FROM content ORDER BY create_time DESC LIMIT 2000000, 10;

优化后 SQL：

SELECT * 
FROM content 
INNER JOIN (SELECT id FROM content ORDER BY create_time DESC LIMIT 2000000, 10
) temp_content 
ON content.id = temp_content.id;

3.5 优化前后性能数据

• 优化前：
  • 查询耗时：63s
  • 原因：扫描大量数据并丢弃前 2000000 条记录，逻辑开销大。
• 优化后：
  • 查询耗时：0.482s
  • 原因：子查询通过索引快速定位目标记录范围，主表只查询需要的数据。

4. 为什么优化后性能提升显著？

4.1 子查询利用索引

• 子查询 SELECT id FROM content ORDER BY create_time DESC LIMIT 2000000, 10 利用了 create_time 索引。
• 索引通过 B+树快速定位到目标范围，减少了全表扫描。

4.2 减少了无效的数据处理

• 优化前：扫描并丢弃了 2000000 条数据。
• 优化后：只查询需要的数据。

4.3 高效利用缓存

• 优化后的查询范围更小，Buffer Pool 的命中率更高。
• 避免了大范围扫描导致的缓存失效问题。

4.4 排序开销显著降低

• 子查询已经完成排序，主查询不需要重复排序，节省了计算资源。