分頁問題

分頁列表查詢是項(xiàng)目中的熱點(diǎn)需求，這種需求的特點(diǎn)是：字段多、數(shù)據(jù)量大、訪問頻繁、使用率高的特點(diǎn)，這個(gè)功能是給用戶最直觀的展示系統(tǒng)的信息，針對于多、大、頻、熱這幾個(gè)特點(diǎn)，會引申出一個(gè)問題：列表展示的數(shù)據(jù)可能是來自于不同的數(shù)據(jù)維度、需要關(guān)聯(lián)N張表查詢得到，那么，如何讓用戶更快、更準(zhǔn)的獲取到需要的數(shù)據(jù)，便成了程序員在編碼時(shí)需要考慮到并且需要解決的問題，因?yàn)殡S時(shí)間推移，線上系統(tǒng)不乏幾百萬數(shù)據(jù)的表。

準(zhǔn)備

CREATE TABLE `test_temp` (
  `test_id` int NOT NULL AUTO_INCREMENT,
  `field_1` varchar(20) DEFAULT NULL,
  `field_2` varchar(20) DEFAULT NULL,
  `field_3` bigint DEFAULT NULL,
  `create_date` date DEFAULT NULL,
  PRIMARY KEY (`test_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

寫一個(gè)存儲過程生成200萬測試數(shù)據(jù):

drop procedure if exists test_insert;
create procedure test_insert(n int)
begin
    declare v int default 0;
    SET AUTOCOMMIT = 0;
    while v < n
        do
            insert into test_temp(field_1, field_2, field_3, create_date)
            values (concat('testing',v),
                    substring(md5(rand()), 1, 10),
                    floor(rand() * 1000000),
                    adddate('1970-01-01', rand(v) * 10000));
            set v = v + 1;
        end while;
    SET AUTOCOMMIT = 1;
end;

插入數(shù)據(jù):

call test_insert(2000000);

測試數(shù)據(jù)方案來自于:

https://blog.csdn.net/weixin_38924697/article/details/119978916

現(xiàn)象

帶分頁的語句，我們一般使用Limit實(shí)現(xiàn)，那么基于以上數(shù)據(jù)我們寫一個(gè)SQL:

SELECT * from test_temp LIMIT 1,10

執(zhí)行時(shí)間:0.004秒

此時(shí)，我們模擬分頁數(shù)據(jù)特別往后的情況，分頁數(shù)據(jù)越往后越慢。

SELECT SQL_NO_CACHE * from test_temp LIMIT 19999900,10

執(zhí)行時(shí)間：1.348秒，速度慢了二十余倍。

實(shí)際的業(yè)務(wù)場景下，可能會關(guān)聯(lián)N張表，而且線上服務(wù)器的壓力會比單機(jī)開發(fā)環(huán)境更重，因此實(shí)際接口響應(yīng)時(shí)間會更長。

問題原因

1. 回表:查詢頻率高的字段會建立索引，但是并不是所有的查詢字段都會在索引上，無法命中索引的字段則需要回表，回表是IO操作，因?yàn)樾枰鶕?jù)索引查找到數(shù)據(jù)行后，再根據(jù)數(shù)據(jù)行的主鍵或唯一索引去聚簇索引中查找具體的數(shù)據(jù)行。因此在執(zhí)行回表操作時(shí)需要從磁盤讀取數(shù)據(jù)，而磁盤IO是相對較慢的操作。
2. 查詢規(guī)則：limit 19999900，10并不是從第19999900行開始掃描，使用explain查看執(zhí)行計(jì)劃：

解決方案

當(dāng)查詢的字段都被索引覆蓋時(shí)，可無需回表，那么我們可以先查詢出主鍵id，再根據(jù)主鍵id拼接id條件或者作為臨時(shí)表JOIN原表就可以了。因?yàn)橹麈Iid是最快的索引：聚簇索引，通過id就能快速找到指定行。

查詢方案一：

先查詢出id，再根據(jù)id直接查詢數(shù)據(jù)。

查詢出id

SELECT test_id from test_temp LIMIT 1999995,5

執(zhí)行計(jì)劃:

再根據(jù)這些id為條件查詢數(shù)據(jù)

SELECT * from test_temp WHERE test_id in (2952993,2952995,2952996,2952997)；

優(yōu)化后的查詢時(shí)間為:0.002秒。

執(zhí)行計(jì)劃

查詢方案二:

使用子查詢作為臨時(shí)表Inner join主表查詢：

SELECT * from (SELECT test_id from test_temp LIMIT 1999995,5) as temp INNER JOIN test_temp tt on tt.test_id = temp.test_id

整體查詢時(shí)間:0.245秒

執(zhí)行計(jì)劃:

性能對比

在不考慮MySQL執(zhí)行時(shí)校驗(yàn)權(quán)限、建立連接的情況下，兩種方案整體查詢時(shí)間在0.25秒左右，相比于整體查詢的1.34秒具有較大優(yōu)勢。方案一相較于方案二的SQL語句更加簡潔易懂，而方案二只需與MySL建立一次查詢即可。

其他優(yōu)化項(xiàng)

請參考:

百萬查詢注意點(diǎn)文章來源地址http://www.zghlxwxcb.cn/news/detail-605879.html

到了這里，關(guān)于百萬數(shù)據(jù)分頁查詢優(yōu)化方案的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！