前言
??前段時間應急群有客服反饋，會員管理功能無法按到店時間、到店次數(shù)、消費金額 進行排序。經(jīng)過排查發(fā)現(xiàn)是Sql執(zhí)行效率低，并且索引效率低下。遇到這樣的情況我們該如何處理呢？今天我們聊一聊Mysql大表查詢優(yōu)化。
應急問題
??商戶反饋會員管理功能無法按到店時間、到店次數(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
出現(xiàn)的原因
??經(jīng)過驗證可以按照“到店時間”進行降序排序，但是無法按照升序進行排序主要是查詢太慢了。主要原因是：雖然該查詢使用建立了recently_consume_time索引，但是索引效率低下，需要查詢整個索引樹，導致查詢時間過長。
DESC 查詢大概需要4s，ASC 查詢太慢耗時未知。
為什么降序排序快和而升序慢呢？
??因為是對時間建立了索引，最近的時間一定在最后面，升序查詢，需要查詢更多的數(shù)據(jù)，才能過濾出相應的結果，所以慢。
解決方案
目前生產(chǎn)庫的索引
調(diào)整索引
??需要刪除index_merchant_user_last_time索引，同時將index_merchant_user_merchant_ids單例索引，變?yōu)?merchant_id,recently_consume_time組合索引。
調(diào)整結果（準生產(chǎn)）
調(diào)整前后結果對比（準生產(chǎn)）
?測試數(shù)據(jù)
?merchant_member_info 有902606條記錄。
?member_info 表有775條記錄。
SQL執(zhí)行效率
優(yōu)化前
優(yōu)化后
type由index -> ref
ref由 null -> const
| TOP | 優(yōu)化前 | 優(yōu)化后 |
| ------------- | ------- | ------ |
| 到店時間-降序 | 0.274s | 0.003s |
| 到店時間-升序 | 11.245s | 0.003s |
調(diào)整索引需要執(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`);
經(jīng)詢問，重建索引花了30分鐘。
最終的分頁查詢優(yōu)化
??上面的sql雖然經(jīng)過調(diào)整索引，雖然能達到較高的執(zhí)行效率，但是隨著分頁數(shù)據(jù)的不斷增加，性能會急劇下降。
| 分頁數(shù)據(jù) | 查詢時間 | 優(yōu)化后 |
| ------------- | -------- | ------ |
| limit 0,10 | 0.003s | 0.002s |
| limit 10,10 | 0.005s | 0.002s |
| limit 100,10 | 0.009s | 0.002s |
| limit 1000,10 | 0.044s | 0.004s |
| limit 9000,10 | 0.247s | 0.016s |
最終的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
結尾
??如果覺得對你有幫助，可以多多評論，多多點贊哦，也可以到我的主頁看看，說不定有你喜歡的文章，也可以隨手點個關注哦，謝謝。
??我是不一樣的科技宅，每天進步一點點，體驗不一樣的生活。我們下期見！
