1.簡介

之前，面阿里的時候，有個面試官問我有沒有使用過分頁查詢，我說有，他說分頁查詢是有問題的，怎么解決；后來這個問題我沒有回答出來；本著學(xué)習(xí)的態(tài)度，今天來解決一下這個問題；

2.分頁插件使用

1.pom文件

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

2.創(chuàng)建分頁配置器

@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來實現(xiàn)分頁功能； 重寫后的sql語句為"SELECT * FROM amj_devinfo order by id desc limit ?, ?";
limit a, b；// 跳過前a條數(shù)據(jù)，取b條數(shù)據(jù)；
所以，其實現(xiàn)在問題就是回到了，執(zhí)行這條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；

結(jié)論：如果說，是小的數(shù)據(jù)量的話，使用該分頁完全沒問題；當(dāng)數(shù)據(jù)量到達兩百萬的時候，執(zhí)行時間就得為6.729s了，對于用戶來說，這是不可接受的；

3.1 limit現(xiàn)象分析

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

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

可以看到，使用的是基于索引樹 + 回表的方法來獲取數(shù)據(jù)的，順序IO查詢列數(shù)為：2000020； 首先，根據(jù)阿里Java開發(fā)手冊，type為index 就已經(jīng)不可接受了；最低標(biāo)準(zhǔn)為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 解決之道

很顯然，現(xiàn)在已經(jīng)是發(fā)現(xiàn)了問題所在，我們需要對其進行解決；我們對下面的sql語句來進行升級；

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

有需要測試的同學(xué)，可以按照我表設(shè)計來模擬測試；

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

3.2.1 對devcho建立索引

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

執(zhí)行sql語句：

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

3.2.2 sql執(zhí)行時間長分析

經(jīng)過多次測試，發(fā)現(xiàn)時間都是很久，那么，就不會是Mysql 刷臟頁，而且，數(shù)據(jù)庫空閑，沒有別的sql與其競爭磁盤IO 而且，通過MVCC查找數(shù)據(jù)也不存在鎖相關(guān)問題；所以，問題肯定是出現(xiàn)在sql語句上；
那么，為什么會出現(xiàn)這個問題呢？ -- 答案是回表這條sql語句是怎么執(zhí)行的呢？

• 先基于devcho的索引列，找到devcho='77'的這一行；
• 在通過devcho中存的主鍵id,然后，回表找所有的數(shù)據(jù)；找20010條數(shù)據(jù)；

這時候，問題就出現(xiàn)了，這個回表的過程是隨機IO；這個隨機IO效率是很低的；所以，undo log要把隨機IO變成順序IO。這里，就是最大的瓶頸所在；
掃描條數(shù)驗證： Handler_read_next: 該選項表明在進行索引掃描時，按照索引從文件數(shù)據(jù)里取數(shù)據(jù)的次數(shù)；

回表是sql瓶頸驗證:

查找主鍵id,不需要回表，發(fā)現(xiàn)0.01s就可以搞定；證明了sql導(dǎo)致的回表就是瓶頸所在；

3.2.3 解決之道

我們剛剛發(fā)現(xiàn)，因為limit比較笨。select * from amj_devinfo where devcho = "77" limit 20000, 10;需要回表20010次；但是，我們只需要它回表10次啊。所以，我們可以先把符合條件的id找出來；再根據(jù)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

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

4 測試時走過的坑

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

• 插入百萬條數(shù)據(jù)數(shù)據(jù)內(nèi)容相同；
• 在執(zhí)行sql時，格式?jīng)]有對應(yīng)上，導(dǎo)致索引失效select * from amj_devinfo where devcho = 77 limit 20000, 10; 77是字符，我輸入為整型；

4.1 百萬數(shù)據(jù)內(nèi)容都一樣

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

還是上面的語句，只是數(shù)據(jù)內(nèi)容是一樣的；為什么兩者時間是一個級別？
為什么會產(chǎn)生這種現(xiàn)象呢？

• 因為數(shù)據(jù)都一樣的devcho 索引其實是沒有用的；成為鏈表了；
• 第一條語句，找出20010條語句就找到內(nèi)容了，因為，都存在一起 都在一個或者幾個頁表中，隨機IO升級為順序IO，是有回表，但是，順序IO的回表也是很快的。 所以，效率很高；即，第一條語句和第二條語句花的時間是差不多的；

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

現(xiàn)象再現(xiàn)：

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中的數(shù)據(jù)是不同的；
問題回顧：我之前就在想，為什么我基于索引列查詢id會這么慢？我當(dāng)時沒想到索引失效問題；后來，我是怎么發(fā)現(xiàn)這個問題的呢？因為，基于索引列查詢的時候，Mysql要掃描的字段也就是20010條數(shù)據(jù)即可；而我查看Handler_read_next（此選項表明在進行索引掃描時，按照索引從數(shù)據(jù)文件里取數(shù)據(jù)的次數(shù)）時，

Handler_read_next 4274160 

explain分析結(jié)果：

如果，掃描這么多行，需要這么多時間是可以理解的，那么，為什么需要掃描這么多行呢？ 我那時候，重新看了一下表的設(shè)計，發(fā)現(xiàn)原來devcho字段的類型是varchar；這個時候，就想到了索引失效這個問題；

4.2.1 為什么會索引失效？

既然，發(fā)現(xiàn)了類型不同導(dǎo)致索引失效，那么就分析一下，為什么會導(dǎo)致索引失效？這條sql又將如何執(zhí)行？ 因為，他是基于索引列找的。但是，由于77 != '77'所以，這就導(dǎo)致了索引實現(xiàn)；但是，最終它還是找到了數(shù)據(jù)，這個時候，結(jié)合了掃描行數(shù)，我個人感覺應(yīng)該是采用了全表掃描，然后，通過，強制類型轉(zhuǎn)換,cpu進行判斷，查詢所得；
當(dāng)改成 select id from amj_devinfo where devcho = "77" limit 20000, 10;就沒有這個問題了；掃描的行數(shù)為20009行； 所以，在寫sql語句的過程中還是要注意??；
字段為varchar 傳入 int 會索引失效，那么，字段為bigint 傳入 "String" 會失效嗎？經(jīng)過測試：不會失效；

所以，在Mybatis中，可以放心使用#{}占位符了；

4.3 一個有趣的現(xiàn)象

大掃描行數(shù) 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掃描的行數(shù)是500多萬行； 但是，由于每個行都需要讀入內(nèi)存中，使用的是順序IO 第二個sql掃描的行數(shù)是20010行，但是，需要訪問隨機IO 20010次；其實，基本上也就把所有的頁表都找了一次；
小總結(jié)：隨機IO，查詢次數(shù)都要避免；

總結(jié)

本文，主要是模擬了分頁查詢中，往后數(shù)據(jù)查詢較慢的現(xiàn)象，以及分析了速度較慢的原因；limit導(dǎo)致隨機回表數(shù)增多。并提供了解決方法，先找到符合條件的id；然后，根據(jù)id做內(nèi)聯(lián)查詢，減少隨機IO的次數(shù)；并且，總結(jié)了一下自己出現(xiàn)的問題以及原因；如果，有一些個人見解不一定正確的話，希望大家多多指正；

到此這篇關(guān)于淺談Mysql大數(shù)據(jù)分頁查詢解決方案的文章就介紹到這了,更多相關(guān)Mysql大數(shù)據(jù)分頁查詢 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！