我的天??！線上千萬級(jí)大表排序，如何優(yōu)化？

前言

今天我們聊一聊Mysql大表查詢優(yōu)化，前段時(shí)間應(yīng)急群有客服反饋，會(huì)員管理功能無法按到店時(shí)間、到店次數(shù)、消費(fèi)金額 進(jìn)行排序。

經(jīng)過排查發(fā)現(xiàn)是Sql執(zhí)行效率低，并且索引效率低下。

應(yīng)急問題

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

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

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

“

不要問我為什么不分表，改動(dòng)太大，無能為力。

問題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??
????????????'會(huì)員'??
????????WHEN?u.nick_name?=?''?THEN??
????????????'會(huì)員'??
????????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?=?'商戶編號(hào)'??
ORDER?BY??
????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í)間過長(zhǎng)。

“

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

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

因?yàn)槭菍?duì)時(shí)間建立了索引，最近的時(shí)間一定在最后面，升序查詢，需要查詢更多的數(shù)據(jù)，才能過濾出相應(yīng)的結(jié)果，所以慢。慢查詢優(yōu)化生產(chǎn)踩到坑，這篇也推薦看下。

解決方案

目前生產(chǎn)庫(kù)的索引

調(diào)整索引

需要?jiǎng)h除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é)果對(duì)比（準(zhǔn)生產(chǎn)）

測(cè)試數(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

#?刪除近期消費(fèi)時(shí)間索引??
ALTER?TABLE?merchant_member_info?DROP?INDEX?index_merchant_user_last_time;??
??
#?刪除商戶編號(hào)索引??
ALTER?TABLE?merchant_member_info?DROP?INDEX?index_merchant_user_merchant_ids;??
??
#?建立商戶編號(hào)和近期消費(fèi)時(shí)間組合索引??
ALTER?TABLE?merchant_member_info?ADD?INDEX?idx_merchant_id_recently_time?(`merchant_id`,`recently_consume_time`);??

“

經(jīng)詢問，重建索引花了30分鐘。關(guān)注公眾號(hào)Java技術(shù)棧可以獲取系列索引教程。

最終的分頁(yè)查詢優(yōu)化

上面的sql雖然經(jīng)過調(diào)整索引，雖然能達(dá)到較高的執(zhí)行效率，但是隨著分頁(yè)數(shù)據(jù)的不斷增加，性能會(huì)急劇下降。

最終的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??
????????????'會(huì)員'??
????????WHEN?u.nick_name?=?''?THEN??
????????????'會(huì)員'??
????????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??

結(jié)尾

如果覺得對(duì)你有幫助，可以多多評(píng)論，多多點(diǎn)贊哦，謝謝。