当前位置：首页 > article >正文

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

article 2025/2/28 12:29:09

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

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

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

1. MySQL 索引机制

1.1 索引是什么？

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

1.2 MySQL 的索引类型

聚簇索引（Clustered Index）：
- InnoDB 存储引擎默认的主键索引。
- 特点：数据和索引存储在一起，叶子节点存储的是完整行的数据。
- 每个表只能有一个聚簇索引。
- 示例：假设一张用户表以 id 为主键，索引结构如下：
```
根节点 → 中间节点 → 叶子节点（存储完整行数据）
```
辅助索引（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+树的结构

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

2.3 B+树的特点

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

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 存在的问题

大偏移量（OFFSET）：
- 数据库需要扫描并丢弃前 2000000 条记录，浪费资源。
- 即使有索引，MySQL 仍需逐一读取和排序这些记录。
全表扫描的风险：
- 如果 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;