前段時間應急群有客服反饋，會員管理功能無法按到店時間、到店次數、消費金額進行排序。經過排查發現是 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
WHENu.nick_nameISNULLTHEN
'會員'
WHENu.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
LEFTJOINmember_info?uONmui.member_id?=?u.id
WHERE
1?=?1
ANDmui.merchant_id?='商戶編號'
ORDERBY
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：
調整索引需要執行的 SQL
執行的注意事項：由于表中的數據量太大，請在晚上進行執行，并且需要分開執行。
#?刪除近期消費時間索引
ALTERTABLEmerchant_member_infoDROPINDEXindex_merchant_user_last_time;
#?刪除商戶編號索引
ALTERTABLEmerchant_member_infoDROPINDEXindex_merchant_user_merchant_ids;
#?建立商戶編號和近期消費時間組合索引
ALTERTABLEmerchant_member_infoADDINDEXidx_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
WHENu.nick_nameISNULLTHEN
'會員'
WHENu.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
INNERJOIN(
SELECT
id
FROM
merchant_member_info
WHERE
merchant_id?=?'商戶ID'
ORDERBY
recently_consume_time?DESC
LIMIT?9000,
10
)?AStmpONtmp.id?=?mui.id
LEFTJOINmember_info?uONmui.member_id?=?u.id
作者：不一樣的科技宅
編輯：陶家龍
出處：juejin.cn/post/6844904053239971854
【編輯推薦】
【責任編輯：武曉燕 TEL：(010)68476606】
點贊 0
