vlambda博客
学习文章列表

线上MySQL千万级大表,如何优化?

送 福 利 啦

关注HarmonyOS技术社区,回复【鸿蒙】价值399元的鸿蒙开发板套件(数量不多,先到先得),还可以免费下载鸿蒙入门资料


👇扫码立刻关注👇

线上MySQL千万级大表,如何优化?

专注开源技术,共建鸿蒙生态


前段时间应急群有客服反馈,会员管理功能无法按到店时间、到店次数、消费金额进行排序。经过排查发现是 SQL 执行效率低,并且索引效率低下。


线上MySQL千万级大表,如何优化?

图片来自 Pexels


应急问题


商户反馈会员管理功能无法按到店时间、到店次数、消费金额进行排序,一直转圈圈或转完无变化,商户要以此数据来做活动,比较着急,请尽快处理,谢谢。


线上数据量


merchant_member_info:7000W 条数据。


member_info:3000W。


不要问我为什么不分表,改动太大,无能为力。


问题 SQL


问题 SQL 如下:

 
   
   
 
SELECT  
    mui.id,  
    mui.merchant_id,  
    mui.member_id,  
    DATE_FORMAT(  
        mui.recently_consume_time,  
        '%Y%m%d%H%i%s'  
    ) recently_consume_time,  
    IFNULL(mui.total_consume_num, 0) total_consume_num,  
    IFNULL(mui.total_consume_amount, 0) total_consume_amount,  
    (  
        CASE  
        WHEN u.nick_name IS NULL THEN  
            '会员'  
        WHEN u.nick_name = '' THEN  
            '会员'  
        ELSE  
            u.nick_name  
        END  
    ) AS 'nickname',  
    u.sex,  
    u.head_image_url,  
    u.province,  
    u.city,  
    u.country  
FROM  
    merchant_member_info mui  
LEFT JOIN member_info u ON mui.member_id = u.id  
WHERE  
    1 = 1  
AND mui.merchant_id = '商户编号'  
ORDER BY  
    mui.recently_consume_time DESC / ASC  
LIMIT 0,  
 10 


出现的原因


经过验证可以按照“到店时间”进行降序排序,但是无法按照升序进行排序主要是查询太慢了。


主要原因是:虽然该查询使用建立了 recently_consume_time 索引,但是索引效率低下,需要查询整个索引树,导致查询时间过长。DESC 查询大概需要 4s,ASC 查询太慢耗时未知。


为什么降序排序快和而升序慢呢?


如下图:

线上MySQL千万级大表,如何优化?

因为是对时间建立了索引,最近的时间一定在最后面,升序查询,需要查询更多的数据,才能过滤出相应的结果,所以慢。


解决方案


目前生产库的索引,如下图:

线上MySQL千万级大表,如何优化?

①调整索引


需要删除 index_merchant_user_last_time 索引,同时将 index_merchant_user_merchant_ids 单例索引,变为  merchant_id,recently_consume_time 组合索引。


②调整结果(准生产)


如下图:

线上MySQL千万级大表,如何优化?

③调整前后结果对比(准生产)


测试数据:

  • merchant_member_info 有 902606 条记录。

  • member_info 表有 775 条记录。


④SQL 执行效率


优化前,如下图:

线上MySQL千万级大表,如何优化?

优化后,如下图:

线上MySQL千万级大表,如何优化?

type 由 index→ref,ref 由 null→const:

线上MySQL千万级大表,如何优化?

调整索引需要执行的 SQL


执行的注意事项: 由于表中的数据量太大,请在晚上进行执行,并且需要分开执行。 
# 删除近期消费时间索引  
ALTER TABLE merchant_member_info DROP INDEX index_merchant_user_last_time;  

# 删除商户编号索引  
ALTER TABLE merchant_member_info DROP INDEX index_merchant_user_merchant_ids;  

# 建立商户编号和近期消费时间组合索引  
ALTER TABLE merchant_member_info ADD INDEX idx_merchant_id_recently_time (`merchant_id`,`recently_consume_time`);

经询问,重建索引花了 30 分钟。


最终的分页查询优化


上面的 SQL 虽然经过调整索引,虽然能达到较高的执行效率,但是随着分页数据的不断增加,性能会急剧下降。

最终的 SQL


优化思路: 先走覆盖索引定位到,需要的数据行的主键值,然后 INNER JOIN  回原表,取到其他数据。
SELECT  
    mui.id,  
    mui.merchant_id,  
    mui.member_id,  
    DATE_FORMAT(  
        mui.recently_consume_time,  
        '%Y%m%d%H%i%s'  
    ) recently_consume_time,  
    IFNULL(mui.total_consume_num, 0) total_consume_num,  
    IFNULL(mui.total_consume_amount, 0) total_consume_amount,  
    (  
        CASE  
        WHEN u.nick_name IS NULL THEN  
            '会员'  
        WHEN u.nick_name = '' THEN  
            '会员'  
        ELSE  
            u.nick_name  
        END  
    ) AS 'nickname',  
    u.sex,  
    u.head_image_url,  
    u.province,  
    u.city,  
    u.country  
FROM  
    merchant_member_info mui  
INNER JOIN (  
    SELECT  
        id  
    FROM  
        merchant_member_info  
    WHERE  
        merchant_id = '商户ID'  
    ORDER BY  
        recently_consume_time DESC  
    LIMIT 9000,  
    10  
AS tmp ON tmp.id = mui.id  
LEFT JOIN member_info u ON mui.member_id = u.id 

编辑:陶家龙

出处:juejin.cn/post/6844904053239971854

精彩文章推荐: