百万数据的表如何在带排序的情况下进行MySQL分页查询的优化

前言

MySQL是一种广泛使用的关系型数据库管理系统，它可以用于处理各种类型的数据。在实际的应用中，带排序的分页查询是一种非常常见的查询操作，但是如果不加以优化，会导致查询效率低下、性能下降等问题。在本篇博客中，我们将用一张200万的数据表来介绍如何在带排序的情况下进行MySQL分页查询的优化。

前期准备

创建表的脚本

CREATE TABLE `student` (
	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
	`user_no` varchar(50) DEFAULT '' COMMENT '学号',
	`user_name` varchar(50) DEFAULT '' COMMENT '姓名',
	`score` decimal(10,2) DEFAULT NULL COMMENT '分数',
	`create_time` date DEFAULT NULL COMMENT '创建时间',
	`update_time` date DEFAULT NULL COMMENT '更新时间',
	`remark` varchar(200) DEFAULT '' COMMENT '备注',
	PRIMARY KEY (`id`),
	KEY `idx_score` (`score`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

使用存储过程自动生成这两百万数据（生成200万数据的时间有点儿旧，需要耐心等待，一般生成的速度和你的机器配置相关，我用了两分钟）

DROP PROCEDURE IF EXISTS generate_data;
DELIMITER $$
CREATE PROCEDURE generate_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 2011;
    DECLARE user_name VARCHAR(20);
    DECLARE user_no VARCHAR(20);
    DECLARE score INT;
    DECLARE create_time DATETIME DEFAULT NOW();
    DECLARE update_time DATETIME;
    DECLARE remark VARCHAR(50);
		set autocommit=0; -- 关闭自动提交事务，提高插入效率
    
    WHILE i <= 2000000 DO
        SET user_name = CONCAT(
            SUBSTRING('赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水竺苏潘范雷', FLOOR(RAND() * 54) + 1, 1),
            SUBSTRING('安宝彪彬冰博财成程达德东斗政法菲飞丰歌根光国海恒弘鸿宏洪华晖惠建健金景', FLOOR(RAND() * 30) + 1, 1),
            SUBSTRING('静俊凯克莉良亮林玲龙茂梅民敏明娜宁鹏平奇琪全仁荣瑞森帅顺涛韬', FLOOR(RAND() * 30) + 1, 1)
        );
        SET user_no = CONCAT(j, LPAD(i, 7, '0'));
        SET score = FLOOR(RAND() * 101);
        SET remark = CONCAT('remark_', i);
        SET update_time = DATE_ADD(create_time, INTERVAL FLOOR(RAND() * 100) DAY); -- update_time则随机生成在create_time基础上加上一定天数的时间。
        INSERT INTO student(id, user_no, user_name, score, create_time, update_time, remark)
        VALUES(i, user_no, user_name, score, create_time, update_time, remark);
        
				SET create_time = DATE_ADD(create_time, INTERVAL 1 SECOND); -- create_time初始值为当前时间，每生成一行数据就自增1分钟，以保证创建时间的递增。
        SET i = i + 1;
        IF i % 100000 = 0 THEN
            SET j = j + 1;
        END IF;
    END WHILE;
END$$
DELIMITER ;


CALL generate_data();
DROP PROCEDURE IF EXISTS generate_data;

需求

按照成绩降序排列，并查询字段 学号(user_no),姓名(user_name)，分数(score)，做一个带排序的分页查询

示例

现在我们需要查询成绩大于80分的学生信息，并且成绩从高到底进行排序，分页查询，可以使用以下SQL语句：

SELECT id, user_no, user_name, score, create_time, update_time, remark 
FROM student 
WHERE score > 80 
order by score desc
LIMIT 0, 10;

我们可以看到，这条SQL的查询时间需要0.78秒

针对这个查询需要优化的情况，以下是几种可能的优化方式：

1. 添加索引：由于查询操作中，需要根据成绩进行排序，所以可以在score字段上添加一个索引，这样查询时可以通过索引进行快速查找和排序，可以显著提升查询性能。，可以通过以下SQL语句实现：

ALTER TABLE student ADD INDEX score_index (score);

再次查询，可以发现查询时间变短了很多

但是发现一个问题，在深分页的时候，查询时间又变长了，SQL如下：

SELECT id, user_no, user_name, score, create_time, update_time, remark 
FROM student 
WHERE score > 80 
order by score desc
limit 80000, 20;

我们可以看到，这条SQL的查询时间需要2.30秒

我们通过查看查询语句的执行计划（EXPLAIN语句）来判断是否使用了索引。

EXPLAIN SELECT id, user_no, user_name, score, create_time, update_time, remark 
FROM student 
WHERE score > 80 
order by score desc
limit 80000, 20;

我们通过执行计划可以看到type列为range，当查询使用了range类型的索引访问方式时，MySQL将在索引中查找满足查询条件的记录，并将它们作为范围返回。

key 列使用的score_index这个索引列；

通过查看 rows 列看到扫描的行数较多，需要考虑对索引，查询条件这一块进行优化；

通过查看Extra列看到其中的值为Using index condition和Backward index scan，表示MySQL正在使用索引条件和反向索引扫描来优化查询。

2. 减少不必要的查询条件，给order by 和 select字段加上联合索引

ALTER TABLE student ADD index idx_score_name_no (score,user_name, user_no);

再次深分页查询：

select user_no, user_name, score from student order by score desc limit 80000, 20;

我们可以看到深分页查询速度都得到了明显的提升

3. 给排序字段增加索引，并手动回表(需要将方案2的索引删除，减少其他索引的影响)

   删除脚本：

   drop index idx_score_name_no on student;
   

   

   sql如下：

   select user_no, user_name, score from student t1 join
   (select id from student order by score desc limit 80000,20) t2 on t1.id= t2.id; 
   

   

我们可以看到深分页查询速度都得到了明显的提升，但是需要注意的是当子查询结果集过多时不建议采用子查询做join条件

4. 根据我们的score索引结构进行优化；score字段索引是先按照score升序进行排序，再在score相同情况下再按照id的升序进行排列

   分页查询前端传递上一页最后一行数据的id和分数

   SQL如下：

   EXPLAIN
    SELECT
   	id, user_no, user_name, score 
   FROM
   	student 
   WHERE id < 30000000  AND score <= 100.00 
   	ORDER BY score DESC  LIMIT 20
   

   

总结

MySQL的带条件排序的分页查询是非常常见的操作，但是如果数据量很大，查询性能可能会很低。如果遇到相关问题，我们应该

1. 确认问题：首先，需要确认查询很慢的具体原因。可以使用MySQL自带的查询分析器，例如EXPLAIN语句，来分析查询的执行计划和索引使用情况。这样可以确定查询慢的原因，例如是否需要优化查询语句或添加索引等。
2. 优化查询语句：在确认查询慢的原因后，可以考虑优化查询语句。例如，可以考虑修改查询条件、添加索引、使用JOIN语句等。
3. 优化数据库结构：如果查询语句已经优化，但仍然很慢，可以考虑优化数据库结构。例如，可以将表分成多个分区、垂直拆分表、水平拆分表等。
4. 优化硬件和配置：如果查询语句和数据库结构已经优化，但仍然很慢，可以考虑优化硬件和配置。例如，可以升级硬件、调整数据库缓存大小、调整MySQL参数等。
5. 测试和验证：优化完成后，需要进行测试和验证，以确保优化的效果符合预期。可以使用压力测试工具模拟多个并发用户，测试查询响应时间和吞吐量等指标。
6. 监控和维护：优化完成后，需要进行监控和维护，以确保系统的稳定性和可靠性。可以使用MySQL自带的监控工具，例如SHOW STATUS和SHOW VARIABLES等，来监控数据库的性能和状态。

需要注意的是，每个优化流程的实现步骤和优先级都可能不同，应该根据实际情况进行调整和优化。
进行测试和验证，以确保优化的效果符合预期。可以使用压力测试工具模拟多个并发用户，测试查询响应时间和吞吐量等指标。
6. 监控和维护：优化完成后，需要进行监控和维护，以确保系统的稳定性和可靠性。可以使用MySQL自带的监控工具，例如SHOW STATUS和SHOW VARIABLES等，来监控数据库的性能和状态。

需要注意的是，每个优化流程的实现步骤和优先级都可能不同，应该根据实际情况进行调整和优化。