快捷導(dǎo)航

MySQL百萬(wàn)數(shù)據(jù)深度分頁(yè)優(yōu)化思路解析

更新時(shí)間：2023年05月10日 14:31:58 作者：一個(gè)程序員的成長(zhǎng)

這篇文章主要為大家介紹了MySQL百萬(wàn)數(shù)據(jù)深度分頁(yè)優(yōu)化思路分析，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

業(yè)務(wù)場(chǎng)景

一般在項(xiàng)目開(kāi)發(fā)中會(huì)有很多的統(tǒng)計(jì)數(shù)據(jù)需要進(jìn)行上報(bào)分析，一般在分析過(guò)后會(huì)在后臺(tái)展示出來(lái)給運(yùn)營(yíng)和產(chǎn)品進(jìn)行分頁(yè)查看，最常見(jiàn)的一種就是根據(jù)日期進(jìn)行篩選。這種統(tǒng)計(jì)數(shù)據(jù)隨著時(shí)間的推移數(shù)據(jù)量會(huì)慢慢的變大，達(dá)到百萬(wàn)、千萬(wàn)條數(shù)據(jù)只是時(shí)間問(wèn)題。

瓶頸再現(xiàn)

創(chuàng)建了一張user表，給create_time字段添加了索引。并在該表中添加了100w條數(shù)據(jù)。

我們這里使用limit分頁(yè)的方式查詢下前5條數(shù)據(jù)和后5條數(shù)據(jù)在查詢時(shí)間上有什么區(qū)別。

查詢前10條基本上不消耗什么時(shí)間

我們從第50w+開(kāi)始取數(shù)據(jù)的時(shí)候，查詢耗時(shí)1秒。

SQL_NO_CACHE
這個(gè)關(guān)鍵詞是為了不讓SQL查詢走緩存。

同樣的SQL語(yǔ)句，不同的分頁(yè)條件，兩者的性能差距如此之大，那么隨著數(shù)據(jù)量的增長(zhǎng)，往后頁(yè)的查詢所耗時(shí)間按理會(huì)越來(lái)越大。

問(wèn)題分析

回表

我們一般對(duì)于查詢頻率比較高的字段會(huì)建立索引。索引會(huì)提高我們的查詢效率。我們上面的語(yǔ)句使用了SELECT * FROM user，但是我們并不是所有的字段都建立了索引。當(dāng)從索引文件中查詢到符合條件的數(shù)據(jù)后，還需要從數(shù)據(jù)文件中查詢到?jīng)]有建立索引的字段。那么這個(gè)過(guò)程稱之為回表。

覆蓋索引

如果查詢的字段正好創(chuàng)建了索引了，比如 SELECT create_time FROM user，我們查詢的字段是我們創(chuàng)建的索引，那么這個(gè)時(shí)候就不需要再去數(shù)據(jù)文件里面查詢，也就不需要回表。這種情況我們稱之為覆蓋索引。

IO

回表操作通常是IO操作，因?yàn)樾枰鶕?jù)索引查找到數(shù)據(jù)行后，再根據(jù)數(shù)據(jù)行的主鍵或唯一索引去聚簇索引中查找具體的數(shù)據(jù)行。聚簇索引一般是存儲(chǔ)在磁盤上的數(shù)據(jù)文件，因此在執(zhí)行回表操作時(shí)需要從磁盤讀取數(shù)據(jù)，而磁盤IO是相對(duì)較慢的操作。

LIMTI 2000,10 ？

你有木有想過(guò)LIMIT 2000,10會(huì)不會(huì)掃描1-2000行，你之前有沒(méi)有跟我一樣，覺(jué)得數(shù)據(jù)是直接從2000行開(kāi)始取的，前面的根本沒(méi)掃描或者不回表。其實(shí)這樣的寫法，一個(gè)完整的流程是查詢數(shù)據(jù)，如果不能覆蓋索引，那么也是要回表查詢數(shù)據(jù)的。

現(xiàn)在你知道為什么越到后面查詢?cè)铰税桑?/p>

問(wèn)題總結(jié)

我們現(xiàn)在知道了LIMIT 遇到后面查詢的性能越差，性能差的原因是因?yàn)橐乇恚热灰呀?jīng)找到了問(wèn)題那么我們只需要減少回表的次數(shù)就可以提升查詢性能了。

解決方案

既然覆蓋索引可以防止數(shù)據(jù)回表，那么我們可以先查出來(lái)主鍵id（主鍵索引），然后將查出來(lái)的數(shù)據(jù)作為臨時(shí)表然后 JOIN 原表就可以了，這樣只需要對(duì)查詢出來(lái)的5條結(jié)果進(jìn)行數(shù)據(jù)回表，大幅減少了IO操作。