如何将过亿订单数据实现毫秒级分页查询

待我称王封你为后i 2022-02-27 02:30 699阅读 0赞

**注：以下sql及数据库截图均为测试数据，无实际用途！**

分页查询？？？  
难道不是 limit pageId,pageSize ???  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2ODIzNA_size_16_color_FFFFFF_t_70]

**新建测试订单表lp\_bank\_order，以create\_day分天记录，  
数据量分别为十万级、百万级、亿级测试，  
分析如下：**

Mysql 在使用limit pageId,pageSize时，**随着数据量的增大，性能会急剧下降**

select * from lp_bank_order t where t.create_day=20181014 limit 0,10;   -- 0.026
    select * from lp_bank_order t where t.create_day=20181014 limit 1000,10; -- 0.032
    select * from lp_bank_order t where t.create_day=20181014 limit 5000,10; -- 0.058
    select * from lp_bank_order t where t.create_day=20181014 limit 10000,10; -- 0.085
    select * from lp_bank_order t where t.create_day=20181014 limit 50000,10; -- 0.248
    select * from lp_bank_order t where t.create_day=20181014 limit 100000,10; -- 0.451

**那么问题来了，必然随着数据增多，瓶颈会越来越大？  
如何实现过亿数据的分页查询，就成了一个问题！！！**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2ODIzNA_size_16_color_FFFFFF_t_70 1]

**常规思想行不通  
转换思路！！！**

*  解决思路1  
    Limit pageId, pageSize有瓶颈  
    但是  
    Limit pageSize是OK的
 *  解决思路2  
    Limit pageSize是OK的，但是要取到每一段，还需要一个条件控制  
    推荐如下：  
    id  
    create\_time

所以我们诞生了如下sql:

select * from lp_bank_order t 
    where 
    t.create_day=20181014
    and t.id > 254671932
    limit 10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2ODIzNA_size_16_color_FFFFFF_t_70 2]

OK，大功告成，收工走人？？？

细心的人已经发现…

id是无序的  
![在这里插入图片描述][20190326162136375.png]

发现问题，开启排序操作：

select * from lp_bank_order t 
    where 
    t.create_day=20181014
    and t.id > 254671932
    order by t.id ASC
    limit 10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2ODIzNA_size_16_color_FFFFFF_t_70 3]

走到这里，往下翻页基本搞定…

处理往上翻页~~  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2ODIzNA_size_16_color_FFFFFF_t_70 4]

处理往上分页：

select * from lp_bank_order t 
    where 
    t.create_day=20181014
    and t.id < 254671933
    order by t.id DESC
    limit 10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2ODIzNA_size_16_color_FFFFFF_t_70 5]

有没有发现幺蛾子…

往上翻页因为倒序排过，返回的列表是倒序的…  
![在这里插入图片描述][20190326162637913.png]

继续改进：

select * from 
    (
    select * from lp_bank_order t 
    where 
    t.create_day=20181014
    and t.id < 254671933
    order by t.id DESC
    limit 10
    ) m
    ORDER BY m.id;

此时此刻，这个sql已经可以支持表内数据几百万了，查询速度刚刚滴。。。  
![在这里插入图片描述][20190326162846206.png]  
新幺蛾子又出现了…

**表内达到上亿数据的时候，就运行不出来了。。。**  
分析原因 -->  
![在这里插入图片描述][20190326163026723.png]

**通过运行sql的使用索引发现，这个sql只用了主键id的索引，并没有用上create\_day索引**  
**造成了order by id的时候，是全表排序的，才会导致很慢**

![!\[在这里插入图片描述\](https://img-blog.csdni][https_img-blog.csdni]

获取并缓存ID  
每次查询最大最小ID需要耗时：1-2秒 所以需要**缓存起来**！！！  
![在这里插入图片描述][20190326163314846.png]  
![在这里插入图片描述][20190326163336456.png]

截止此时，分页查询的核心脚本已经OK…  
![在这里插入图片描述][20190326163407706.png]  
**我们还可以继续优化的事项：**

*  部分缓存数据现在都是首次查询的时候缓存的，可以使用脚本去提前缓存，这样首次查询一样可以享受快速查询
 *  订单查询的缓存时间虽然缩短至10秒，但是方案有待进一步去优化  
    … …

最后送大家一句话：

**世上没有绝望的处境，只有对处境绝望的人。 —— 费洛姆**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2ODIzNA_size_16_color_FFFFFF_t_70]: /images/20220227/3c0d17790ca44d4bbbff6b36fcfc5680.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2ODIzNA_size_16_color_FFFFFF_t_70 1]: /images/20220227/afeeb3257e3d4076ada95cc8bdbfd660.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2ODIzNA_size_16_color_FFFFFF_t_70 2]: /images/20220227/cf9b1774b7bd4b6c86f19150a53927e1.png
[20190326162136375.png]: /images/20220227/ae7ab68c64074ce9894df91aced626e8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2ODIzNA_size_16_color_FFFFFF_t_70 3]: /images/20220227/06b83cfd283c4853b5ae4ae9414cb6f9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2ODIzNA_size_16_color_FFFFFF_t_70 4]: /images/20220227/4afa7ceb59f14f1bb635f4a57c5a251b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2ODIzNA_size_16_color_FFFFFF_t_70 5]: /images/20220227/1e09786f0c6a4792a7ec49d4afb39636.png
[20190326162637913.png]: /images/20220227/82d7634efec04f0a8f1d55f17f3f20ed.png
[20190326162846206.png]: /images/20220227/5b620785a1264fe29ba0e0832e64b3d6.png
[20190326163026723.png]: /images/20220227/112fac7106314be7911d4a82326bb583.png
[https_img-blog.csdni]: /images/20220227/02d6fc0d96b3457caae4b225bf34bce0.png
[20190326163314846.png]: /images/20220227/7b62750dd2264717b0c7a3b7b6e7cf2a.png
[20190326163336456.png]: /images/20220227/6464a67b72a94e7e8781b318d3da6085.png
[20190326163407706.png]: /images/20220227/46a397097fe4449f8385a1c1e9e1755b.png