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

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

引用

CSDN

1.

https://blog.csdn.net/qq_51447436/article/details/145320461

在数据库开发中，分页查询性能问题一直是一个棘手的难题。本文将从MySQL索引机制入手，深入剖析其底层原理（B+树），结合实际场景讲解分页查询优化的技巧，并通过实验数据展示优化效果。通过本文的学习，你将掌握如何将查询时间从几十秒缩短到几百毫秒的优化方法。

MySQL 索引机制

索引是什么？

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

MySQL 的索引类型

1. 聚簇索引（Clustered Index）：

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

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

2. 辅助索引（Secondary Index）：

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

MySQL 缓存机制的变化

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

• 原因：查询缓存频繁失效，影响性能，在高并发写场景下尤为明显。

• 查询缓存的替代：更高效的优化器和InnoDB缓存机制。

• MySQL 的 Buffer Pool：

• 依旧是核心性能优化手段。

• 功能：将数据页、索引页缓存到内存中，减少磁盘I/O。

• 特点：即使查询缓存被删除，Buffer Pool仍然支持高效的索引查询和数据读取。

索引的底层原理

什么是 B+树？

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

B+树的结构

1. 非叶子节点：

• 只存储索引键，起到导航作用。
• 减少了节点大小，提高了节点的分支因子。

2. 叶子节点：

• 存储所有实际数据（聚簇索引）或主键值（辅助索引）。
• 通过链表指针串联，便于范围查询。

B+树的特点

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

为什么 MySQL 使用 B+树？

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

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

问题描述

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

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

存在的问题

1. 大偏移量（OFFSET）：

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

2. 全表扫描的风险：

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

优化思路

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

优化前后对比

优化前 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;

优化前后性能数据

• 优化前：

• 查询耗时：63s

• 原因：扫描大量数据并丢弃前2000000条记录，逻辑开销大。

• 优化后：

• 查询耗时：0.482s

• 原因：子查询通过索引快速定位目标记录范围，主表只查询需要的数据。

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

1. 子查询利用索引

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

2. 减少了无效的数据处理

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

3. 高效利用缓存

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

4. 排序开销显著降低

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