前段時間應急群有客服反饋，會員管理功能無法按到店時間、到店次數、消費金額進行排序。經過排查發現是 SQL 執行效率低，並且索引效率低下。

圖片來自 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 查詢太慢耗時未知。

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

如下圖：

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

解決方案

目前生產庫的索引，如下圖：

①調整索引

②調整結果(準生產)

如下圖：

測試資料：

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

④SQL 執行效率

最佳化前，如下圖：

最佳化後，如下圖：

type 由 index→ref，ref 由 null→const：

調整索引需要執行的 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