線上千萬級大表排序該如何優化？

前言

  前段時間應急群有客服回報，會員管理功能無法按到店時間、到店次數、消費金額 進行排序。經過排查發現是Sql執行效率低，并且索引效率低下。遇到這樣的情況我們該如何處理呢？今天我們聊一聊Mysql大表查詢優化。

應急問題

  商戶回報會員管理功能無法按到店時間、到店次數、消費金額 進行排序，一直轉圈圈或轉完無變化，商戶要以此資料來做活動，比較着急，請盡快處理，謝謝。

線上資料量

merchant_member_info

7000W條資料。

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           

出現的原因

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

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

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

  因為是對時間建立了索引，最近的時間一定在最後面，升序查詢，需要查詢更多的資料，才能過濾出相應的結果，是以慢。

解決方案

目前生産庫的索引

調整索引

  需要删除

index_merchant_user_last_time

索引，同時将

index_merchant_user_merchant_ids

單例索引，變為

merchant_id

,

recently_consume_time

組合索引。

調整結果（準生産）

調整前後結果對比（準生産）

 測試資料

 

merchant_member_info

有902606條記錄。

member_info

表有775條記錄。

SQL執行效率

優化前

優化後

type由index -> ref

ref由 null -> const

TOP
到店時間-降序	0.274s	0.003s
到店時間-升序	11.245s

調整索引需要執行的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雖然經過調整索引，雖然能達到較高的執行效率，但是随着分頁資料的不斷增加，性能會急劇下降。

分頁資料	查詢時間
limit 0,10		0.002s
limit 10,10	0.005s
limit 100,10	0.009s
limit 1000,10	0.044s	0.004s
limit 9000,10	0.247s	0.016s

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

結尾

  如果覺得對你有幫助，可以多多評論，多多點贊哦，也可以到我的首頁看看，說不定有你喜歡的文章，也可以随手點個關注哦，謝謝。

  我是不一樣的科技宅，每天進步一點點，體驗不一樣的生活。我們下期見！