尼恩说在前面
mysql分库分表，深度翻页太慢，如何解决？ 分库分表后，分页查询太慢了，如何优化？
最新《尼恩 架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF，请关注本公众号【技术自由圈】获取，后台回复：领电子书
本文目录
- 尼恩说在前面
- 单表场景，limit深度分页存在的严重性能问题
-问题: 为什么 mysql 深度分页会很慢？
- limit在深度翻页场景下变成了： 全表扫描+ 文件排序 filesort
- 单表场景 limit 深度分页 的优化方法
-1）子查询分页方式
-2）join 分页方式
- 索引覆盖（Cover Index）
- 单表场景 limit 深度分页 总结
- 分表场景，limit深度分页存在的严重性能问题
-分表场景下 功能和性能的冲突：从0开始的性能瓶颈
- Sharding-JDBC的性能优化措施
- 当然，流式查询的也是有弊端的
- 分表场景+大表场景，limit严重性能问题 如何解决？
- 优化方案1： 禁止跳页查询法
- 优化方法2：二次查询法
-二次查询法的一个例子
- 优化方案3：使用 ES+HBASE 海量NOSQL架构方案
- 说在最后：有问题找老架构取经
单表场景，limit深度分页存在的严重性能问题
offset）高达20w多。SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
LIMIT 后面传两个参数，一个是偏移量（offset），一个是获取的条数（limit）。offset 很大时，查询速度就会变慢。select * from user where sex = 'm' order by age limit 1, 10 // 32.8ms
select * from user where sex = 'm' order by age limit 10, 10 // 34.2ms
select * from user where sex = 'm' order by age limit 100, 10 // 35.4ms
select * from user where sex = 'm' order by age limit 1000, 10 // 39.6ms
select * from user where sex = 'm' order by age limit 10000, 10 // 5660ms
select * from user where sex = 'm' order by age limit 100000, 10 // 61.4 秒
select * from user where sex = 'm' order by age limit 1000000, 10 // 273 秒
offset）的增加，查询时间变得越长。问题: 为什么 mysql 深度分页会很慢？
预处理器：将查询字段展开（如select * 展开为具体字段）并检查字段是否合法 优化器：指定sql执行计划，如选择合适的索引 执行器：与存储引擎层交互，执行sql语句
limit在深度翻页场景下变成了： 全表扫描+ 文件排序 filesort
select * from user where sex = 'm' order by age limit 1000000, 10 // 273 秒
explain select * from user where sex = 'm' order by age limit 1000000, 10
单表场景 limit 深度分页 的优化方法
SELECT * FROM `tbl_works` WHERE `status`=1 LIMIT 100000, 10 // 78.3 秒
1）子查询分页方式
SELECT * FROM tbl_works
WHERE id >= (SELECT id FROM tbl_works limit 100000, 1)
LIMIT 20 // 54ms
ids，然后再根据 ids 获取内容。 根据直觉将SQL改造如下：SELECT * FROM tbl_works
WHERE id IN (SELECT id FROM tbl_works limit 100000, 10)
// 错误信息：
// This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
limit操作。于是，我们对SQL进行了改造，对子查询包了一层：SELECT t1.* FROM tbl_works t1
WHERE t1.id in (SELECT t2.id from (SELECT id FROM tbl_works limit 100000, 10) as t2) // 53.9ms
join 分页方式，达到相同的优化效果。实际上，两者的原理是相同的。2）join 分页方式
SELECT * FROM tbl_works t1
JOIN (SELECT id from tbl_works WHERE status=1 limit 100000, 10) t2
ON t1.id = t2.id // 53.6 ms
join定位到目标 ids，然后再将数据取出。ids时，由于 SELECT的元素只有主键 ID，且status 存在索引，因此MySQL只需在索引中，就能定位到目标 ids，不用在数据文件上进行查找。基础知识：什么是 索引覆盖（Cover Index）
如果索引包含所有满足查询需要的数据的索引，成为索引覆盖(Covering Index)，也就是平时所说的不需要回表操作。
select name,age,level from user where name = "AAA" and age 17
key `idx_nal` (`name`,`age`,`level`) using btree
索引大小远小于数据行大小。因而，如果只读取索引，则能极大减少对数据访问量。 索引按顺序储存。对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少。 避免对主键索引的二次查询。二级索引的叶子节点包含了主键的值，如果二级索引包含所要查询的值，则能避免二次查询主键索引（聚簇索引，聚簇索引既存储了索引，也储存了值）。
单表场景 limit 深度分页 总结
分表场景，limit深度分页存在的严重性能问题
select * from user where sex = 'm' order by age limit 1000000, 10 // 273 秒
SELECT score FROM t_score ORDER BY score DESC LIMIT 1, 2;
从t_score_0表中获取的是90和80； 从t_score_0表中获取的是85和75。
分表场景下 功能和性能的冲突：从0开始的性能瓶颈
Sharding-JDBC的性能优化措施
当然，流式查询的也是有弊端的
IOPS 飙升，因为需要返回的数据需要写入到临时空间中，存在大量的 IO 读取和写入，此流程可能会引起其它业务的写入抖动 磁盘空间飙升，写入临时空间的数据会在读取完成或客户端发起 ResultSet#close 操作时由 MySQL_Server 回收 客户端 JDBC 发起sql_query，可能会有长时间等待，这段时间为MySQL_Server准备数据阶段。 但是 普通查询等待时间与游标查询等待时间原理上是不一致的: 前者是在读取网络缓冲区的数据，没有响应到业务层面；后者是 MySQL 在准备临时数据空间，没有响应到 JDBC 数据准备完成后，进行到传输数据阶段，网络响应开始飙升，IOPS 由"写"转变为"读"
分表场景+大表场景，limit严重性能问题 如何解决？
优化方案1： 禁止跳页查询法
order by col where col>$max_col limit Y;
优化方法2：二次查询法
order by col offset X/N limit Y;
order by col between col_min and col_i_max;
二次查询法的一个例子
CREATE TABLE `student_time_0` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`name` varchar(200) COLLATE utf8_bin DEFAULT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`create_time` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=674 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
student_time_0,student_time_1,student_time_2,
insert into student_time (`name`, `user_id`, `age`, `create_time`) values (?, ?, ?, ?)
create_time 唯一，比较好说明问题，int i = 0;
try (
Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(insertSql)) {
do {
ps.setString(1, localName + new Random().nextInt(100));
ps.setLong(2, 10086L + (new Random().nextInt(100)));
ps.setInt(3, 18);
ps.setLong(4, new Date().getTime());
int result = ps.executeUpdate();
LOGGER.info("current execute result: {}", result);
Thread.sleep(new Random().nextInt(100));
i++;
} while (i <= 2000);
select * from student_time ORDER BY create_time ASC limit 1000, 5;
student_time 对于我们使用的 sharding-jdbc 来说当然是逻辑表， sharding-jdbc 会改写为select * from student_time ORDER BY create_time ASC limit 0, 1005;
第一个办法禁止跳页，而是只给下一页，那么我们就能把前一次的最大偏移量的 create_time 记录下来，下一页就可以拿着这个偏移量进行查询 第二个办法是二次查询法
t0
334 10158 nick95 18 1641548941767
335 10098 nick11 18 1641548941879
336 10167 nick51 18 1641548942089
337 10167 nick3 18 1641548942119
338 10170 nick57 18 1641548942169
t1
334 10105 nick98 18 1641548939071 最小
335 10174 nick94 18 1641548939377
336 10129 nick85 18 1641548939442
337 10141 nick84 18 1641548939480
338 10096 nick74 18 1641548939668
t2
334 10184 nick11 18 1641548945075
335 10109 nick93 18 1641548945382
336 10181 nick41 18 1641548945583
337 10130 nick80 18 1641548945993
338 10184 nick19 18 1641548946294 最大
order by col between col_min and col_i_max;
t0
322 10161 nick81 18 1641548939284
323 10113 nick16 18 1641548939393
324 10110 nick56 18 1641548939577
325 10116 nick69 18 1641548939588
326 10173 nick51 18 1641548939646
t1
334 10105 nick98 18 1641548939071
335 10174 nick94 18 1641548939377
336 10129 nick85 18 1641548939442
337 10141 nick84 18 1641548939480
338 10096 nick74 18 1641548939668
t2
297 10136 nick28 18 1641548939161
298 10142 nick68 18 1641548939177
299 10124 nick41 18 1641548939237
300 10148 nick87 18 1641548939510
301 10169 nick23 18 1641548939715
322 - 1 + 334-1 + 297 - 1 = 951，1000 - 951 + 5 = 54 条数据，再进行合并排序就可以获得最终正确的结果。优化方案3：使用 ES+HBASE 海量NOSQL架构方案
说在最后：有问题找老架构取经
被裁之后， 空窗1年/空窗2年， 如何 起死回生 ？
案例1：42岁被裁2年，天快塌了，急救1个月，拿到开发经理offer，起死回生
案例2：35岁被裁6个月， 职业绝望，转架构急救上岸，DDD和3高项目太重要了
案例3：失业15个月，学习40天拿offer， 绝境翻盘，如何实现？
被裁之后，100W 年薪 到手， 如何 人生逆袭？
100W案例，100W年薪的底层逻辑是什么？ 如何实现年薪百万？ 如何远离 中年危机？
如何 逆天改命，包含AI、大数据、golang、Java 等
实现职业转型，极速上岸
关注职业救助站公众号，获取每天职业干货
助您实现职业转型、职业升级、极速上岸
---------------------------------
实现架构转型，再无中年危机
关注技术自由圈公众号，获取每天技术千货
一起成为牛逼的未来超级架构师
几十篇架构笔记、5000页面试宝典、20个技术圣经
请加尼恩个人微信 免费拿走
暗号，请在 公众号后台 发送消息：领电子书
如有收获，请点击底部的"在看"和"赞"，谢谢
