前段時(shí)間應(yīng)急群有客服反饋，會員管理功能無法按到店時(shí)間、到店次數(shù)、消費(fèi)金額進(jìn)行排序。經(jīng)過排查發(fā)現(xiàn)是 SQL 執(zhí)行效率低，并且索引效率低下。

圖片來自 Pexels

應(yīng)急問題

商戶反饋會員管理功能無法按到店時(shí)間、到店次數(shù)、消費(fèi)金額進(jìn)行排序，一直轉(zhuǎn)圈圈或轉(zhuǎn)完無變化，商戶要以此數(shù)據(jù)來做活動，比較著急，請盡快處理，謝謝。

線上數(shù)據(jù)量

merchant_member_info：7000W 條數(shù)據(jù)。

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

出現(xiàn)的原因

經(jīng)過驗(yàn)證可以按照“到店時(shí)間”進(jìn)行降序排序，但是無法按照升序進(jìn)行排序主要是查詢太慢了。

主要原因是：雖然該查詢使用建立了 recently_consume_time 索引，但是索引效率低下，需要查詢整個(gè)索引樹，導(dǎo)致查詢時(shí)間過長。DESC 查詢大概需要 4s，ASC 查詢太慢耗時(shí)未知。

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

如下圖：

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

解決方案

目前生產(chǎn)庫的索引，如下圖：

①調(diào)整索引

需要刪除 index_merchant_user_last_time 索引，同時(shí)將 index_merchant_user_merchant_ids 單例索引，變?yōu)?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í)行的注意事項(xiàng)：由于表中的數(shù)據(jù)量太大，請?jiān)谕砩线M(jìn)行執(zhí)行，并且需要分開執(zhí)行。

#?刪除近期消費(fèi)時(shí)間索引

ALTERTABLEmerchant_member_infoDROPINDEXindex_merchant_user_last_time;

#?刪除商戶編號索引

ALTERTABLEmerchant_member_infoDROPINDEXindex_merchant_user_merchant_ids;

#?建立商戶編號和近期消費(fèi)時(shí)間組合索引

ALTERTABLEmerchant_member_infoADDINDEXidx_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

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

【編輯推薦】

【責(zé)任編輯：武曉燕 TEL：(010)68476606】

點(diǎn)贊 0

總結(jié)

以上是生活随笔為你收集整理的MySQL线上优化_线上MySQL千万级大表，如何优化？的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。