騰訊云：線上千萬級大表排序該如何優(yōu)化？

前言

前段時間應(yīng)急群有客服反饋，會員管理功能無法按到店時間、到店次數(shù)、消費金額進(jìn)行排序。經(jīng)過排查發(fā)現(xiàn)是Sql執(zhí)行效率低，并且索引效率低下。遇到這樣的情況我們該如何處理呢？今天我們聊一聊Mysql大表查詢優(yōu)化。

應(yīng)急問題

商戶反饋會員管理功能無法按到店時間、到店次數(shù)、消費金額進(jìn)行排序，一直轉(zhuǎn)圈圈或轉(zhuǎn)完無變化，商戶要以此數(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

出現(xiàn)的原因

經(jīng)過驗證可以按照“到店時間”進(jìn)行降序排序，但是無法按照升序進(jìn)行排序主要是查詢太慢了。主要原因是：雖然該查詢使用建立了recently_consume_time索引，但是索引效率低下，需要查詢整個索引樹，導(dǎo)致查詢時間過長。

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

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

因為是對時間建立了索引，最近的時間一定在最后面，升序查詢，需要查詢更多的數(shù)據(jù)，才能過濾出相應(yīng)的結(jié)果，所以慢。

解決方案

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

調(diào)整索引

需要刪除index_merchant_user_last_time索引，同時將index_merchant_user_merchant_ids單例索引，變?yōu)?span style="font-family: 微軟雅黑, "Microsoft YaHei"; background-color: rgb(242, 242, 242);">merchant_id,recently_consume_time組合索引。

調(diào)整結(jié)果（準(zhǔn)生產(chǎn)）

調(diào)整前后結(jié)果對比（準(zhǔn)生產(chǎn)）

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

merchant_member_info有902606條記錄。

member_info表有775條記錄。

SQL執(zhí)行效率

優(yōu)化前

優(yōu)化后

type由index->ref

ref由null->const

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

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

由于表中的數(shù)據(jù)量太大，請在晚上進(jìn)行執(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`);

經(jīng)詢問，重建索引花了30分鐘。

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

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

最終的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