目錄前言案例優(yōu)化小結(jié)前言

后端開(kāi)發(fā)中為了防止一次性加載太多數(shù)據(jù)導(dǎo)致內(nèi)存、磁盤(pán)IO都開(kāi)銷(xiāo)過(guò)大，經(jīng)常需要分頁(yè)展示，這個(gè)時(shí)候就需要用到MySQL的LIMIT關(guān)鍵字。但你以為L(zhǎng)IMIT分頁(yè)就萬(wàn)事大吉了么，Too young,too simple啊，LIMIT在數(shù)據(jù)量大的時(shí)候極可能造成的一個(gè)問(wèn)題就是深度分頁(yè)。

案例

這里我以顯示電商訂單詳情為背景舉個(gè)例子，新建表如下：

CREATE TABLE `cps_user_order_detail` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ’主鍵’, `user_id` varchar(32) NOT NULL DEFAULT ’’ COMMENT ’用戶(hù)ID’, `order_id` bigint(20) DEFAULT NULL COMMENT ’訂單id’, `sku_id` bigint(20) unsigned NOT NULL COMMENT ’商品ID’, `order_time` datetime DEFAULT NULL COMMENT ’下單時(shí)間,格式y(tǒng)yyy-MM-dd HH:mm:ss’, PRIMARY KEY (`id`), KEY `idx_time_user` (`order_time`,`user_id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT=’用戶(hù)訂單詳情’;

然后手動(dòng)向表里插入120W條數(shù)據(jù)?，F(xiàn)在有個(gè)需求：分頁(yè)展示用戶(hù)的訂單詳情，按照下單時(shí)間倒序。表結(jié)構(gòu)精簡(jiǎn)了，需求也簡(jiǎn)單。于是嘩嘩嘩的寫(xiě)完代碼，提測(cè)上線(xiàn)了。早期運(yùn)行一切正常，可隨著訂單量的不斷增大，發(fā)現(xiàn)系統(tǒng)越發(fā)的緩慢，還時(shí)不時(shí)報(bào)出幾個(gè)慢查詢(xún)。這個(gè)時(shí)候你就該想到是LIMIT偏移的問(wèn)題了，沒(méi)錯(cuò)，不是你的SQL不夠優(yōu)美，就是MySQL自身的機(jī)制。這里我就簡(jiǎn)單以?xún)蓷lSQL為例，如下圖，分別是從100和100W的位置偏移分頁(yè)，可以看到時(shí)間相差很大。這還不算其它數(shù)據(jù)運(yùn)算和處理的時(shí)間，單一條SQL的查詢(xún)就耗時(shí)一秒以上，在對(duì)用戶(hù)提供的功能里這是不能容忍的（電商里經(jīng)常要求一個(gè)接口的RT不超過(guò)200ms）。

這里我們?cè)倏聪聢?zhí)行計(jì)劃，如下圖所示：

在此先介紹一下執(zhí)行計(jì)劃Extra列可能出現(xiàn)的值及含義：

Using where：表示優(yōu)化器需要通過(guò)索引回表查詢(xún)數(shù)據(jù)。 Using index：即覆蓋索引，表示直接訪(fǎng)問(wèn)索引就足夠獲取到所需要的數(shù)據(jù)，不需要通過(guò)索引回表，通常是通過(guò)將待查詢(xún)字段建立聯(lián)合索引實(shí)現(xiàn)。 Using index condition：在5.6版本后加入的新特性，即大名鼎鼎的索引下推，是MySQL關(guān)于減少回表次數(shù)的重大優(yōu)化。 Using filesort:文件排序，這個(gè)一般在ORDER BY時(shí)候，數(shù)據(jù)量過(guò)大，MySQL會(huì)將所有數(shù)據(jù)召回內(nèi)存中排序，比較消耗資源。

再看看上圖，同樣的語(yǔ)句，只因?yàn)槠屏坎煌?，就造成了?zhí)行計(jì)劃的千差萬(wàn)別（且容我小小的夸張一下）。第一條語(yǔ)句LIMIT 100,6type列的值是range，表示范圍掃描，性能比ref差一個(gè)級(jí)別，但是也算走了索引，并且還應(yīng)用了索引下推：就是說(shuō)在WHERE之后的下單時(shí)間刪選走了索引，并且之后的ORDER BY也是根據(jù)索引下推優(yōu)化，在執(zhí)行WHERE條件篩選時(shí)同步進(jìn)行的（沒(méi)有回表）。而第二條語(yǔ)句LIMIT 1000000,6壓根就沒(méi)走索引，type列的值是ALL，顯然是全表掃描。并且Extra列字段里的Using where表示發(fā)生了回表，Using filesort表示ORDER BY時(shí)發(fā)生了文件排序。所以這里慢在了兩點(diǎn)：一是文件排序耗時(shí)過(guò)大，二是根據(jù)條件篩選了相關(guān)的數(shù)據(jù)之后，需要根據(jù)偏移量回表獲取全部值。無(wú)論是上面的哪一點(diǎn)，都是LIMIT偏移量過(guò)大導(dǎo)致的，所以實(shí)際開(kāi)發(fā)環(huán)境經(jīng)常遇到非統(tǒng)計(jì)表量級(jí)不得超過(guò)一百萬(wàn)的要求。

優(yōu)化

原因分析完了，那么LIMIT深度分頁(yè)在實(shí)際開(kāi)發(fā)中怎么優(yōu)化呢？這里少俠給兩點(diǎn)方案。一是通過(guò)主鍵索引優(yōu)化。什么意思呢？就是把上面的語(yǔ)句修改成：

SELECT * FROM cps_user_order_detail d WHERE d.id > #{maxId} AND d.order_time>’2020-8-5 00:00:00’ ORDER BY d.order_time LIMIT 6;

如上代碼所示，同樣也是分頁(yè)，但是有個(gè)maxId的限制條件，這個(gè)是什么意思呢，maxId就是上一頁(yè)中的最大主鍵Id。所以采用此方式的前提：1）主鍵必須自增不能是UUID并且前端除了傳基本分頁(yè)參數(shù)pageNo,pageSize外，還必須把每次上一頁(yè)的最大Id帶過(guò)來(lái)，2）該方式不支持隨機(jī)跳頁(yè)，也就是說(shuō)只能上下翻頁(yè)。如下圖所示是某知名電商中的實(shí)際頁(yè)面。

二是通過(guò)Elastic Search搜索引擎優(yōu)化（基于倒排索引），實(shí)際上類(lèi)似于淘寶這樣的電商基本上都是把所有商品放進(jìn)ES搜索引擎里的（那么海量的數(shù)據(jù)，放進(jìn)MySQL是不可能的，放進(jìn)Redis也不現(xiàn)實(shí)）。但即使用了ES搜索引擎，也還是有可能發(fā)生深度分頁(yè)的問(wèn)題的，這時(shí)怎么辦呢？答案是通過(guò)游標(biāo)scroll。關(guān)于此點(diǎn)這里不做深入，感興趣的可以做研究。

小結(jié)

寫(xiě)這篇博客是因?yàn)榍岸螘r(shí)間在開(kāi)發(fā)中真實(shí)經(jīng)歷到了，并且之前在字節(jié)面試中確實(shí)也和面試官探討了一番。知道LIMIT的限制以及優(yōu)化，在面試中能提到是加分項(xiàng)，不能說(shuō)到MySQL優(yōu)化就是建索引，調(diào)整SQL（實(shí)際上在真實(shí)開(kāi)發(fā)中這兩種優(yōu)化方案的成效微乎其微）。畢竟MySQL優(yōu)化那么牛X的話(huà)，就不會(huì)有那么多中間件產(chǎn)生了。

到此這篇關(guān)于MySQL深度分頁(yè)(千萬(wàn)級(jí)數(shù)據(jù)量如何快速分頁(yè))的文章就介紹到這了,更多相關(guān)MySQL 深度分頁(yè)內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！