mysql 两百万数据 深分页性能优化

-- ddl
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`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='学生表';
-- 生成数据
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 ();

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

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

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

# 4ms 浅分页
SELECT id, user_no, user_name, score, create_time, update_time, remark
FROM student
WHERE score > 80
order by score desc
LIMIT 0, 10;
select count(*) from student s
# 4.392s 深分页
SELECT id, user_no, user_name, score, create_time, update_time, remark
FROM student
WHERE score > 80
order by score desc
limit 80000, 20;
# 1 SIMPLE student range idx_score idx_score 6 661530 100.0 Using index condition
EXPLAIN SELECT id, user_no, user_name, score, create_time, update_time, remark
FROM student
-- WHERE score > 80 (all)
order by score desc
limit 80000, 20;
# order by score desc all order不走索引 17s 偏移量大 回表成本高 where 有索引字段还是会走
# 1 SIMPLE student ALL 1993166 100.0 Using filesort
EXPLAIN
SELECT id, user_no, user_name, score, create_time, update_time, remark
FROM student
-- WHERE score > 80 (all)
order by score desc
limit 80000, 20;
# 强制索引 # 9.42s
SELECT id, user_no, user_name, score, create_time, update_time, remark
FROM student force index(idx_score)
-- WHERE score > 80 (all)
order by score desc
limit 80000, 20;
我们通过执行计划可以看到type列为range，当查询使用了range类型的索引访问方式时，MySQL将在索引中查找满足查询条件的记录，并将它们作为范围返回。
key 列使用的score_index这个索引列；
通过查看 rows 列看到扫描的行数较多，需要考虑对索引，查询条件这一块进行优化；
通过查看Extra列看到其中的值为Using index condition和Backward index scan，表示MySQL正在使用索引条件和反向索引扫描来优化查询。
减少不必要的查询条件，给order by 和 select字段加上联合索引

------------------
2 减少不必要的查询条件，给order by 和 select字段加上联合索引
# 17s
ALTER TABLE student ADD index idx_score_name_no (score,user_name, user_no);
再次深分页查询：
# 37ms
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;
# 79ms
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和分数
desc student
EXPLAIN
SELECT
id, user_no, user_name, score
FROM
student
WHERE id < 30000000 AND score <= 100.00
ORDER BY score DESC LIMIT 20

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

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

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