線上千萬級大表排序該如何優(yōu)化珠插？

image

前言

??前段時間應(yīng)急群有客服反饋带到，會員管理功能無法按到店時間昧碉、到店次數(shù)、消費(fèi)金額進(jìn)行排序揽惹。經(jīng)過排查發(fā)現(xiàn)是Sql執(zhí)行效率低被饿，并且索引效率低下。遇到這樣的情況我們該如何處理呢搪搏？今天我們聊一聊Mysql大表查詢優(yōu)化狭握。

應(yīng)急問題

??商戶反饋會員管理功能無法按到店時間、到店次數(shù)疯溺、消費(fèi)金額進(jìn)行排序论颅，一直轉(zhuǎn)圈圈或轉(zhuǎn)完無變化哎垦，商戶要以此數(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)過驗(yàn)證可以按照“到店時間”進(jìn)行降序排序，但是無法按照升序進(jìn)行排序主要是查詢太慢了雁仲。主要原因是：雖然該查詢使用建立了recently_consume_time索引，但是索引效率低下琐脏，需要查詢整個索引樹攒砖，導(dǎo)致查詢時間過長。

DESC 查詢大概需要4s日裙，ASC 查詢太慢耗時未知吹艇。

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

image

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

解決方案

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

image

調(diào)整索引

??需要刪除index_merchant_user_last_time索引联四，同時將index_merchant_user_merchant_ids單例索引撑碴，變?yōu)?merchant_id,recently_consume_time組合索引。

調(diào)整結(jié)果（準(zhǔn)生產(chǎn)）

image

調(diào)整前后結(jié)果對比（準(zhǔn)生產(chǎn)）

?測試數(shù)據(jù)

?merchant_member_info 有902606條記錄朝墩。

?member_info 表有775條記錄醉拓。

SQL執(zhí)行效率

優(yōu)化前

image

優(yōu)化后

image

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ù)量太大裳朋，請在晚上進(jìn)行執(zhí)行污呼，并且需要分開執(zhí)行末早。

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

ALTER TABLE merchant_member_info DROP INDEX index_merchant_user_last_time;

# 刪除商戶編號索引

ALTER TABLE merchant_member_info DROP INDEX index_merchant_user_merchant_ids;

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

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)整索引柄延，雖然能達(dá)到較高的執(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

結(jié)尾

??如果覺得對你有幫助双藕，可以多多評論，多多點(diǎn)贊哦阳仔，也可以到我的主頁看看忧陪，說不定有你喜歡的文章，也可以隨手點(diǎn)個關(guān)注哦近范，謝謝嘶摊。

??我是不一樣的科技宅，每天進(jìn)步一點(diǎn)點(diǎn)评矩，體驗(yàn)不一樣的生活叶堆。我們下期見！

線上千萬級大表排序該如何優(yōu)化芬位？

線上千萬級大表排序該如何優(yōu)化？

前言

應(yīng)急問題

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

問題SQL如下

出現(xiàn)的原因

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

解決方案

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

調(diào)整索引

調(diào)整結(jié)果（準(zhǔn)生產(chǎn)）

調(diào)整前后結(jié)果對比（準(zhǔn)生產(chǎn)）

SQL執(zhí)行效率

調(diào)整索引需要執(zhí)行的SQL

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

最終的sql

結(jié)尾