2024年大数据量性能优化之分页查询(1)，金九银十

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取

针对这种查询方式，下面测试查询记录量对时间的影响：

select * from orders_history where type=8 limit 10000,1;

select * from orders_history where type=8 limit 10000,10;

select * from orders_history where type=8 limit 10000,100;

select * from orders_history where type=8 limit 10000,1000;

select * from orders_history where type=8 limit 10000,10000;

三次查询时间：

查询1条记录：3072ms 3092ms 3002ms

查询10条记录：3081ms 3077ms 3032ms

查询100条记录：3118ms 3200ms 3128ms

查询1000条记录：3412ms 3468ms 3394ms

查询10000条记录：3749ms 3802ms 3696ms

在查询记录量低于100时，查询时间基本无差距，随查询记录量越来越大，消耗时间越多。

针对查询偏移量的测试：

select * from orders_history where type=8 limit 100,100;

select * from orders_history where type=8 limit 1000,100;

select * from orders_history where type=8 limit 10000,100;

select * from orders_history where type=8 limit 100000,100;

select * from orders_history where type=8 limit 1000000,100;

三次查询时间如下：

查询100偏移：25ms 24ms 24ms

查询1000偏移：78ms 76ms 77ms

查询10000偏移：3092ms 3212ms 3128ms

查询100000偏移：3878ms 3812ms 3798ms

查询1000000偏移：14608ms 14062ms 14700ms

随着查询偏移的增大，尤其查询偏移大于10万以后，查询时间急剧增加。

这种分页查询方式会从DB的第一条记录开始扫描，所以越往后，查询速度越慢，而且查询数据越多，也会拖慢总查询速度。

优化

=================================================================

• 前端加缓存、搜索，减少落到库的查询操作

比如海量商品可以放到搜索里面，使用瀑布流的方式展现数据

• 优化SQL 访问数据的方式

直接快速定位到要访问的数据行。推荐使用"延迟关联"的方法来优化排序操作，何谓"延迟关联" ：通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。

• 使用书签方式 ，记录上次查询最新/大的id值，向后追溯 M行记录

延迟关联

---

优化前

explain SELECt id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type =‘0’ AND end_time >=‘2014-05-29’ ORDER BY id asc LIMIT 149420 ,20;

±—±------------±------------±------±--------------±------------±--------±-----±-------±----+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

±—±------------±------------±------±--------------±------------±--------±-----±-------±----+

| 1 | SIMPLE | relation | range | ind_endtime | ind_endtime | 9 | NULL | 349622 | Using where; Using filesort |

±—±------------±------------±------±--------------±------------±--------±-----±-------±----+

执行时间：

20 rows in set (1.05 sec)

优化后:

explain SELECT a.* FROM relation a, (select id from relation where biz_type =‘0’ AND end_time >=‘2014-05-29’ ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id;

±—±------------±------------±-------±--------------±--------±--------±-----±-------±------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

±—±------------±------------±-------±--------------±--------±--------±-----±-------±------+

| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 20 | |

| 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 8 | b.id | 1 | |

| 2 | DERIVED | relation | index | ind_endtime | PRIMARY | 8 | NULL | 733552 | |

±—±------------±------------±-------±--------------±--------±--------±-----±-------±------+

执行时间:

20 rows in set (0.36 sec)

优化后 执行时间 为原来的1/3 。

书签

---

首先获取符合条件的记录的最大 id和最小id(默认id是主键)

select max(id) as maxid ,min(id) as minid

from t where kid=2333 and type=1;

根据id 大于最小值或者小于最大值进行遍历。

select xx,xx from t where kid=2333 and type=1

and id >=min_id order by id asc limit 100;

select xx,xx from t where kid=2333 and type=1

and id <=max_id order by id desc limit 100;

案例

---

当遇到延迟关联也不能满足查询速度的要求时

SELECt a.id as id, client_id, admin_id, kdt_id, type, token, created_time, update_time, is_valid, version FROM t1 a, (SELECt id FROM t1 WHERe 1 and client_id = ‘xxx’ and is_valid = ‘1’ order by kdt_id asc limit 267100,100 ) b WHERe a.id = b.id;

100 rows in set (0.51 sec)

使用延迟关联查询数据510ms ，使用基于书签模式的解决方法减少到10ms以内 绝对是一个质的飞跃。

SELECt * FROM t1 where client_id=‘xxxxx’ and is_valid=1 and id<47399727 order by id desc LIMIT 100;

100 rows in set (0.00 sec)

小结

---

根据主键定位数据的方式直接定位到主键起始位点，然后过滤所需要的数据。

相对比延迟关联的速度更快，查找数据时少了二级索引扫描。但优化方法没有银弹，比如：

order by id desc 和 order by asc 的结果相差70ms ，生产上的案例有limit 100 相差1.3s ，这是为啥？

还有其他优化方式，比如在使用不到组合索引的全部索引列进行覆盖索引扫描的时候使用 ICP 的方式 也能够加快大分页查询。

子查询优化

====================================================================

先定位偏移位置的 id，然后往后查询，适于 id 递增场景：

select * from orders_history where type=8 limit 100000,1;

select id from orders_history where type=8 limit 100000,1;

select * from orders_history where type=8 and

id>=(select id from orders_history where type=8 limit 100000,1)

limit 100;

select * from orders_history where type=8 limit 100000,100;

4条语句的查询时间如下：

第1条语句：3674ms

第2条语句：1315ms

第3条语句：1327ms

第4条语句：3710ms

• 1 V.S 2：select id 代替 select *，速度快3倍

• 2 V.S 3：速度相差不大

• 3 V.S 4：得益于 select id 速度增加，3的查询速度快了3倍

这种方式相较于原始一般的查询方法，将会增快数倍。

使用 id 限定优化

=========================================================================

假设数据表的id是连续递增，则根据查询的页数和查询的记录数可以算出查询的id的范围，可使用 id between and：

select *

from order_history

where c = 2

and id between 1000000 and 1000100

limit 100;

查询时间：

15ms

12ms

9ms

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取

and id between 1000000 and 1000100

limit 100;

查询时间：

15ms

12ms

9ms

[外链图片转存中…(img-SwwDb4jT-1714890350778)] [外链图片转存中…(img-fnBmphJd-1714890350779)] [外链图片转存中…(img-wlvLsrC7-1714890350779)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新