前言

圖片

image.gif

?

今天我們聊一聊Mysql大表查詢優(yōu)化，前段時間應急群有客服反饋，會員管理功能無法按到店時間、到店次數(shù)、消費金額 進行排序。

經(jīng)過排查發(fā)現(xiàn)是Sql執(zhí)行效率低，并且索引效率低下。

應急問題

商戶反饋會員管理功能無法按到店時間、到店次數(shù)、消費金額 進行排序，一直轉圈圈或轉完無變化，商戶要以此數(shù)據(jù)來做活動，比較著急，請盡快處理，謝謝。

線上數(shù)據(jù)量

merchant_member_info 7000W條數(shù)據(jù)。
member_info 3000W。

“

不要問我為什么不分表，改動太大，無能為力。

問題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  

image.gif

出現(xiàn)的原因

經(jīng)過驗證可以按照“到店時間”進行降序排序，但是無法按照升序進行排序主要是查詢太慢了。

主要原因是：雖然該查詢使用建立了recently_consume_time索引，但是索引效率低下，需要查詢整個索引樹，導致查詢時間過長。

“

DESC 查詢大概需要4s，ASC 查詢太慢耗時未知。

為什么降序排序快和而升序慢呢？

圖片

image.gif

?

因為是對時間建立了索引，最近的時間一定在最后面，升序查詢，需要查詢更多的數(shù)據(jù)，才能過濾出相應的結果，所以慢。慢查詢優(yōu)化生產(chǎn)踩到坑，這篇也推薦看下。

解決方案

目前生產(chǎn)庫的索引

圖片

image.gif

?

調整索引

需要刪除index_merchant_user_last_time索引，同時將index_merchant_user_merchant_ids單例索引，變?yōu)?code>merchant_id,recently_consume_time組合索引。

調整結果（準生產(chǎn)）

圖片

image.gif

?

調整前后結果對比（準生產(chǎn)）

測試數(shù)據(jù)

merchant_member_info 有902606條記錄。
member_info 表有775條記錄。

SQL執(zhí)行效率

優(yōu)化前

圖片

image.gif

?

優(yōu)化后

圖片

image.gif

?

type由index -> ref

ref由 null -> const

圖片

image.gif

?

調整索引需要執(zhí)行的SQL

執(zhí)行的注意事項：

由于表中的數(shù)據(jù)量太大，請在晚上進行執(zhí)行，并且需要分開執(zhí)行。

# 刪除近期消費時間索引  
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`);  

image.gif

“

經(jīng)詢問，重建索引花了30分鐘。關注公眾號Java技術?？梢垣@取系列索引教程。

最終的分頁查詢優(yōu)化

上面的sql雖然經(jīng)過調整索引，雖然能達到較高的執(zhí)行效率，但是隨著分頁數(shù)據(jù)的不斷增加，性能會急劇下降。

圖片

image.gif

?

最終的sql

優(yōu)化思路：先走覆蓋索引定位到，需要的數(shù)據(jù)行的主鍵值，然后INNER JOIN 回原表，取到其他數(shù)據(jù)。

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  

image.gif

結尾

如果覺得對你有幫助，可以多多評論，多多點贊哦，謝謝。