淺談Mysql巨量資料分頁查詢解決方案

2022-02-18 13:05:09

1.簡介

之前，面阿里的時候，有個面試官問我有沒有使用過分頁查詢，我說有，他說分頁查詢是有問題的，怎麼解決；後來這個問題我沒有回答出來；本著學習的態度，今天來解決一下這個問題；

2.分頁外掛使用

1.pom檔案

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>4.1.6</version>
        </dependency>

2.建立分頁設定器

@Configuration
public class PageHelperConfig {
    @Bean
    public PageHelper pageHelper() {
        PageHelper pageHelper = new PageHelper();
        Properties p = new Properties();
        p.setProperty("offsetAsPageNum", "true");
        p.setProperty("rowBoundsWithCount", "true");
        p.setProperty("reasonable", "true");
        pageHelper.setProperties(p);
        return pageHelper;
    }
}

測試程式碼：

    @Test
    void test() {
        PageHelper.startPage(400000,10,"id desc");
        List<UploadData> users = userMapper.queryAll();
        System.out.println(users.size());
        System.out.println(users);
        for (UploadData uploadData: users) {
            System.out.println(uploadData);
        }
    }

4.重寫sql分析

debug 後可以檢視它是通過重寫sql來實現分頁功能；重寫後的sql語句為"SELECT * FROM amj_devinfo order by id desc limit ?, ?";
limit a, b；// 跳過前a條資料，取b條資料；
所以，其實現在問題就是回到了，執行這條sql語句所需要花費多少的問題了；

3.sql測試與分析

select * from amj_devinfo order by id limit 2000, 20;     // 0.027s
select * from amj_devinfo order by id limit 20000, 20;    // 0.035s
select * from amj_devinfo order by id limit 200000, 20;   // 0.136s
select * from amj_devinfo order by id limit 2000000, 20;  // 1.484s

select * from amj_devinfo order by devaddress limit 2000000, 20; // 7.356 全表掃描 + filesort；

結論：如果說，是小的資料量的話，使用該分頁完全沒問題；當資料量到達兩百萬的時候，執行時間就得為6.729s了，對於使用者來說，這是不可接受的；

3.1 limit現象分析

使用explain對sql先來分析一波；感興趣的同學可以看看我的另一篇文章 MySQL結合explain分析結果如下：

針對，select * from amj_devinfo order by id limit 2000, 20來說：

可以看到，使用的是基於索引樹 + 回表的方法來獲取資料的，順序IO查詢列數為：2000020；首先，根據阿里Java開發手冊，type為index 就已經不可接受了；最低標準為range；而且，它是order by id 能夠使用上主鍵索引，要是order by '其他列（無索引）如devaddress' 這個時候，就是全表掃描 + filesort，效率更慢；
備註：

select * from amj_devinfo order by id limit 2000000, 20;

這條語句是方案一：先通過id找到2000000，然後，剩下的20條再全表掃描；還是，方案二：通過id回表直接找到2000020條，然後，放棄前2000000條；理論上剩下20條進行全表掃描肯定是快很多的；但是，有點尷尬。Mysql選擇的其實是方案二；

3.2 解決之道

很顯然，現在已經是發現了問題所在，我們需要對其進行解決；我們對下面的sql語句來進行升級；

測試背景：
1.mysql 資料表中有5695594 (五百萬)條資料，在devcho中資料相對離散。
2.表的設計如下：

有需要測試的同學，可以按照我表設計來模擬測試；

select * from amj_devinfo where devcho = "77" limit 20000, 10;

3.2.1 對devcho建立索引

很顯然，通過sql來查詢的話，對devcho建立索引的話，可以把全表掃描升級為基於索引列的掃描；能提升一個量級；
索引建立結果如下：

執行sql語句：

執行時間8.415s 這個時間是不可以接收的；

3.2.2 sql執行時間長分析

經過多次測試，發現時間都是很久，那麼，就不會是Mysql 刷髒頁，而且，資料庫空閒，沒有別的sql與其競爭磁碟IO 而且，通過MVCC查詢資料也不存在鎖相關問題；所以，問題肯定是出現在sql語句上；
那麼，為什麼會出現這個問題呢？ -- 答案是回表這條sql語句是怎麼執行的呢？

先基於devcho的索引列，找到devcho='77'的這一行；
在通過devcho中存的主鍵id,然後，回表找所有的資料；找20010條資料；

這時候，問題就出現了，這個回表的過程是隨機IO；這個隨機IO效率是很低的；所以，undo log要把隨機IO變成順序IO。這裡，就是最大的瓶頸所在；
掃描條數驗證： Handler_read_next: 該選項表明在進行索引掃描時，按照索引從檔案資料裡取資料的次數；

回表是sql瓶頸驗證:

查詢主鍵id,不需要回表，發現0.01s就可以搞定；證明了sql導致的回表就是瓶頸所在；

3.2.3 解決之道

我們剛剛發現，因為limit比較笨。select * from amj_devinfo where devcho = "77" limit 20000, 10;需要回表20010次；但是，我們只需要它回表10次啊。所以，我們可以先把符合條件的id找出來；再根據id使用inner join 去進行回表；
sql語句如下：

select * from amj_devinfo a INNER JOIN (select id from amj_devinfo where devcho = "77" limit 20000, 10) b on a.id = b.id;

查詢時間：0.025s

這個時候，就可以達到我們的要求了；這個聯結是會產生笛卡爾積的。檢索出來行的數目是第一個表中的行數乘以第二個表中的行數，以前，感覺挺慢的，這也證明，如果沒有檔案排序或者臨時表的話，效率其實還可以；

4 測試時走過的坑

在測試的時候，其實我犯了兩個錯，卡了自己好幾個小時，證明測試都不對；特此記錄一下，給想復現現象的同學提個醒；

插入百萬條資料資料內容相同；
在執行sql時，格式沒有對應上，導致索引失效select * from amj_devinfo where devcho = 77 limit 20000, 10; 77是字元，我輸入為整型；

4.1 百萬資料內容都一樣

select * from amj_devinfo where devcho = "2212" limit 20000, 10; // 0.042s
select id from amj_devinfo where devcho = "2212" limit 20000, 10; // 0.026s

還是上面的語句，只是資料內容是一樣的；為什麼兩者時間是一個級別？
為什麼會產生這種現象呢？

因為資料都一樣的devcho 索引其實是沒有用的；成為連結串列了；
第一條語句，找出20010條語句就找到內容了，因為，都存在一起都在一個或者幾個頁表中，隨機IO升級為順序IO，是有回表，但是，順序IO的回表也是很快的。所以，效率很高；即，第一條語句和第二條語句花的時間是差不多的；

4.2 寫sql時，把"77"寫成了77；

現象再現：

select id from amj_devinfo where devcho = 77 limit 20000, 10; // 查詢時間2.064s
select * from amj_devinfo where devcho = 77 limit 20000, 10; // 查詢時間3.716s

這裡第一條語句因為欄位比第二條語句中少；所以，放入sort_buffer中的資料是不同的；
問題回顧：我之前就在想，為什麼我基於索引列查詢id會這麼慢？我當時沒想到索引失效問題；後來，我是怎麼發現這個問題的呢？因為，基於索引列查詢的時候，Mysql要掃描的欄位也就是20010條資料即可；而我檢視Handler_read_next（此選項表明在進行索引掃描時，按照索引從資料檔案裡取資料的次數）時，

Handler_read_next 4274160

explain分析結果：

如果，掃描這麼多行，需要這麼多時間是可以理解的，那麼，為什麼需要掃描這麼多行呢？我那時候，重新看了一下表的設計，發現原來devcho欄位的型別是varchar；這個時候，就想到了索引失效這個問題；

4.2.1 為什麼會索引失效？

既然，發現了型別不同導致索引失效，那麼就分析一下，為什麼會導致索引失效？這條sql又將如何執行？因為，他是基於索引列找的。但是，由於77 != '77'所以，這就導致了索引實現；但是，最終它還是找到了資料，這個時候，結合了掃描行數，我個人感覺應該是採用了全表掃描，然後，通過，強制型別轉換,cpu進行判斷，查詢所得；
當改成 select id from amj_devinfo where devcho = "77" limit 20000, 10;就沒有這個問題了；掃描的行數為20009行；所以，在寫sql語句的過程中還是要注意啊；
欄位為varchar 傳入 int 會索引失效，那麼，欄位為bigint 傳入 "String" 會失效嗎？經過測試：不會失效；

所以，在Mybatis中，可以放心使用#{}預留位置了；

4.3 一個有趣的現象

大掃描行數 VS 隨機IO

select * from amj_devinfo  where devcho  = 77 limit 20000, 10; 查詢時間 3.311s
select * from amj_devinfo  where devcho  = "77" limit 20000, 10; 查詢時間 3.188s

第一個sql掃描的行數是500多萬行；但是，由於每個行都需要讀入記憶體中，使用的是順序IO 第二個sql掃描的行數是20010行，但是，需要存取隨機IO 20010次；其實，基本上也就把所有的頁表都找了一次；
小總結：隨機IO，查詢次數都要避免；