从一个破分页查询聊到千万级数据查询性能优化

前言

• 分页查询是Web应用中常见的需求，当对百万、千万级别数据进行分页查询时，我们可能会遇到性能问题。本文将探讨常规分页查询为什么慢，并介绍一些常用优化技巧提高查询性能，以及聊聊百万级、千万级数据查询性能优化。

数据准备

• 创建 student 测试表

CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(12) DEFAULT NULL,
  `class_no` varchar(50) DEFAULT NULL COMMENT '班级号',
  `sex` bigint(20) NOT NULL COMMENT '1 - 男 2 -女',
  PRIMARY KEY (`id`),
  KEY `sex_indx` (`sex`) USING BTREE,
  KEY `age_index` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• 使用存储过程插入数据

DELIMITER //  # 将结束标志符临时从 ; 改为 //

CREATE PROCEDURE InsertStudentData()
BEGIN
  DECLARE i INT DEFAULT 1;

  WHILE i <= 5000000 DO
    INSERT INTO student (name, age, class_no, sex)
    VALUES (
      CONCAT('Student', i),
      FLOOR(RAND() * 30) + 10, -- 生成随机年龄，假设年龄在10到40之间
      CONCAT('Class', FLOOR(RAND() * 10) + 1), -- 生成随机班级号，假设班级号在Class1到Class10之间
      IF(i % 2 = 0, 2, 1) -- 偶数行为女性，奇数行为男性
    );

    SET i = i + 1;
  END WHILE;
END //

DELIMITER ; // 将结束标志符改回 ;

// 调用存储过程插入数据
CALL InsertStudentData();

常规分页查询

• 在常规分页查询中，我们一般使用 LIMIT 进行分页，下面是两种常用写法：

// 效果相同 都表示从 4000001 开始取 10 条数据
SELECT * FROM student ORDER BY id LIMIT 1000 OFFSET 4000000;
SELECT * FROM student ORDER BY id LIMIT 4000000,1000;

性能分析

• 使用常规分页查询，当数据量比较小并不存在性能问题；但分页数据不断后移，达到百万、或千万级别，我们下面来看看会发生什么：

SELECT * FROM student ORDER BY id LIMIT 1000,10
> OK
> 查询时间: 0.004s

SELECT * FROM student ORDER BY id LIMIT 10000,10
> OK
> 查询时间: 0.014s

SELECT * FROM student ORDER BY id LIMIT 100000,10
> OK
> 查询时间: 0.102s

SELECT * FROM student ORDER BY id LIMIT 1000000,10
> OK
> 查询时间: 1.279s

SELECT * FROM student ORDER BY id LIMIT 2000000,10
> OK
> 查询时间: 1.671s

SELECT * FROM student ORDER BY id LIMIT 4000000,10
> OK
> 查询时间: 2.649s

• 在上面的案例中我们可以看到，当分页数据后移到百万级别时，我们所需要的时间已经达到秒级，且随着分页数据后移越大，性能会越来越差。

常规查询下数据量增加性能下降的原因探究

• 以下面的 SQL 为例：

SELECT * FROM student ORDER BY id LIMIT 4900000,10

// 执行流程
先统计到 4900000 行，然后再往后取 10 行，这里的统计其实和我们的 count 函数的逻辑基本一致，有索引使用索引，无索引进行全表扫描

SELECT * FROM student ORDER BY id LIMIT 4900000,10
> OK
> 查询时间: 3.469s

使用 EXPLAIN 分析

• LIMIT 1000,10

• LIMIT 10000,10

• LIMIT 100000,10

• 从上面的 EXPLAIN 分析结果可以看出，随着分页数据不断后移，我们查询需要扫描的行数不断增多，查询所需时间也不断增大。

查询性能优化

索引优化

游标分页

• 通过记录上一页查询的最后一条记录的位置，然后在下一页查询时使用这个位置信息，可以有效地获取下一页的数据。

原理

• 通过游标的方式减少数据扫描行数，从而提高查询效率

适用条件

• 查询语句使用游标列索引，并可以拿到上一页查询最后一条记录的位置
• 索引满足自增

SELECT * FROM student ORDER BY id LIMIT 4900000,10
> OK
> 查询时间: 5.341s


// 使用主键索引 id 且 上一次查询 id 为 4900000
SELECT * FROM student WHERE id > 4900000 ORDER BY id LIMIT 10
> OK
> 查询时间: 0s

BETWEEN … AND（基本无实际场景）

• 原理和游标分页一样，只是条件更加苛刻，主键自增不能出现缺失（比如 4900002 缺失，导致返回的数据只有9条）

SELECT * FROM student WHERE id BETWEEN 4900000 AND 4900010
> OK
> 查询时间: 0.004s

缓存分页

• 缓存分页是在应用层面对查询结果进行缓存，当用户请求下一页时，直接从缓存中获取数据，而不需要再次访问数据库。这适用于一些静态或者不经常更新的数据。
• 需要注意的是，缓存分页可能会导致数据一致性的问题，因此在使用缓存分页时需要考虑数据更新的频率和敏感度。

物理分页（分库分表）

• 通过将查询结果集的数据进行物理拆分存储，每个物理分页存储一部分数据。在查询时，直接定位到所需的物理分页，避免扫描整个表。也就是我们我们常说的分库分表。
• 实际业务中，一般会根据业务数据的分布情况进行分库分表，比如常见的按时间拆分、按地点拆分、或者更加复杂的混合拆分。

# 数据按月进行物理分页

+-------------------------------------+   
|      Physical Tabel 1  202301       |
+-------------------------------------+
|   Record 1   |   Record 2   |  ...  |
+-------------------------------------+
|   Record 11  |   Record 12  |  ...  |
+-------------------------------------+
|              ...                  |
+-------------------------------------+
|   Record 20  |   Record 21  |  ...  |
+-------------------------------------+

+-------------------------------------+
|      Physical Tabel 2 202302       |
+-------------------------------------+
|   Record 21  |   Record 22  |  ...  |
+-------------------------------------+
|   Record 31  |   Record 32  |  ...  |
+-------------------------------------+
|              ...                  |
+-------------------------------------+
|   Record 40  |   Record 41  |  ...  |
+-------------------------------------+

选择合适的存储引擎

• 不同的数据库引擎有不同的特性，选择适合业务需求的存储引擎。

总结

• 当数据库数据量逐渐增多，查询性能会不断下降，本文从分页查询入手，介绍了一些常见的优化方案，方案的核心都是减少数据库扫描行数，从而提高查询效率。在实际的业务场景中，大家可以根据实际业务需求，选择合适的方案。

个人简介

👋 你好，我是 Lorin 洛林，一位 Java 后端技术开发者！座右铭：Technology has the power to make the world a better place.

🚀 我对技术的热情是我不断学习和分享的动力。我的博客是一个关于Java生态系统、后端开发和最新技术趋势的地方。

🧠 作为一个 Java 后端技术爱好者，我不仅热衷于探索语言的新特性和技术的深度，还热衷于分享我的见解和最佳实践。我相信知识的分享和社区合作可以帮助我们共同成长。

💡 在我的博客上，你将找到关于Java核心概念、JVM 底层技术、常用框架如Spring和Mybatis 、MySQL等数据库管理、RabbitMQ、Rocketmq等消息中间件、性能优化等内容的深入文章。我也将分享一些编程技巧和解决问题的方法，以帮助你更好地掌握Java编程。

🌐 我鼓励互动和建立社区，因此请留下你的问题、建议或主题请求，让我知道你感兴趣的内容。此外，我将分享最新的互联网和技术资讯，以确保你与技术世界的最新发展保持联系。我期待与你一起在技术之路上前进，一起探讨技术世界的无限可能性。

📖 保持关注我的博客，让我们共同追求技术卓越。