基于mysql大数据量limit查询优化

刺骨的言语ヽ痛彻心扉 2022-12-29 09:43 189阅读 0赞

今天一个业务场景，定时任务同步全量的历史数据。Java中用一个list去接收sql查询返回的数据。想到list的存储及sql性能瓶颈，于是就需要对数据进行分页查询。

使用得mysql数据，在查询数据库中40w数据时，发现耗时将近4min。其中230s是用在网络传输。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDU0MA_size_16_color_FFFFFF_t_70][]

那么此时就需要分页查询。分页查询逻辑也有效率高低，比较好的方法

1.  通过id进行between查询。这种局限于id为自增的表。并且它的每条数据内容都是固定的。如果中间有其实id不是连续的，那么中间这个基数就很难定义。比如我数据库最小的id是1.第二条为70000.后面的id才连续+1.如果一次查询1w。那这样的基数从第一条开始到第二条就会要查70次。

Select \* from coupon\_order\_details where id BETWEEN 340591（起始id） and 7155105 （结束id）

1.  通过id进行limit查询。这种也会有一个问题。就是如果你扫1w条数据。这1w条数据都没有查到更新结果。第二次再查又会查到这批数据。一直只查这批数据。并且如下性能会很差。

select id,coupon\_details from coupon\_order\_details LIMIT 1 ，50000  但这种很明显的测试性能偏低。因为他每次遍历都需要计算偏移量。越往后分页，LIMIT语句的偏移量就会越大，

大概100s的样子。

结合以上两种，同时工作可解决我的业务问题。

1.  先查询该表中最小的id。Select min（id） from coupon\_order\_details
2.  通过大于最小id的查询的5w条数据。并且记录此次查询的max（id）和数量

select max(id)，count（1） FROM (select id,coupon\_details from coupon\_order\_details where id > 340591 LIMIT 50000) t

1.  如果数量小于5w就不需要继续查询了，如果数量等于5w的话。那么拿着最大id再去查询下一次数据

于是改了条件，这种相当于先到了这行以后的数据的50000条。这次查询只用了60s。

select id,coupon\_details from coupon\_order\_details where id > 340591 LIMIT 50000

下面是执行效率![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDU0MA_size_16_color_FFFFFF_t_70 1][]

然而在以上的基础上，还可以进行优化。以上我查了两列，但有一列的字段值为text类型、这条的字节长度非常大。所以这就是之前提的网络传输占用时间过长。我们可能需要某些特定字段的数据。可以在sql中直接解析拿取。如下SQL,这次发现查询5w条数据只用了10s。

从240s到10s的缩减。已经是极致提升。

SELECT id,

coupon\_details ->> '$.outTxnType' AS outTxnType,

coupon\_details ->> '$.productNo' AS productNo,

CASE

WHEN coupon\_details ->> '$.outTxnType'='WB0000' THEN coupon\_details ->> '$.voucherInfoBO\[0\].voucherNo'

WHEN coupon\_details ->> '$.outTxnType'='WB0003' THEN coupon\_details ->> '$.couponInfoBO\[0\].couponNo'

END AS codeNo

FROM coupon\_order\_details

LIMIT 50000

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDU0MA_size_16_color_FFFFFF_t_70 2][]

大概去比对了底层性能。发现网络传输的send数据发送的相差比较。所以在sql中强调不要用select \* ，count（\*） 这种语法，要精确到某一具体的列是可以提升很大性能的。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDU0MA_size_16_color_FFFFFF_t_70 3][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDU0MA_size_16_color_FFFFFF_t_70 4][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDU0MA_size_16_color_FFFFFF_t_70]: /images/20221120/2b13af85dd574bd3bfc2b4ab9e4a7629.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDU0MA_size_16_color_FFFFFF_t_70 1]: /images/20221120/008553ffeedd47588a455e9d307fd860.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDU0MA_size_16_color_FFFFFF_t_70 2]: /images/20221120/7264a2835ca34d4fa88dfa8d4c8c4398.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDU0MA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201221223734260.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDU0MA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDU0MA_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/2020122122374128.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDU0MA==,size_16,color_FFFFFF,t_70