1.簡(jiǎn)介

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

2.分頁(yè)插件使用

1.pom文件

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

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

@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;
    }
}

• 測(cè)試代碼：

    @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.重寫(xiě)sql分析

debug 后可以查看它是通過(guò)重寫(xiě)sql來(lái)實(shí)現(xiàn)分頁(yè)功能； 重寫(xiě)后的sql語(yǔ)句為"SELECT * FROM amj_devinfo order by id desc limit ?, ?";
limit a, b；// 跳過(guò)前a條數(shù)據(jù)，取b條數(shù)據(jù)；
所以，其實(shí)現(xiàn)在問(wèn)題就是回到了，執(zhí)行這條sql語(yǔ)句所需要花費(fèi)多少的問(wèn)題了；

3.sql測(cè)試與分析

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é)論：如果說(shuō)，是小的數(shù)據(jù)量的話，使用該分頁(yè)完全沒(méi)問(wèn)題；當(dāng)數(shù)據(jù)量到達(dá)兩百萬(wàn)的時(shí)候，執(zhí)行時(shí)間就得為6.729s了，對(duì)于用戶來(lái)說(shuō)，這是不可接受的；

3.1 limit現(xiàn)象分析

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

針對(duì)，select * from amj_devinfo order by id limit 2000, 20來(lái)說(shuō)：

可以看到，使用的是基于索引樹(shù) + 回表的方法來(lái)獲取數(shù)據(jù)的，順序IO查詢列數(shù)為：2000020； 首先，根據(jù)阿里Java開(kāi)發(fā)手冊(cè)，type為index 就已經(jīng)不可接受了；最低標(biāo)準(zhǔn)為range；而且，它是order by id 能夠使用上主鍵索引，要是order by '其他列（無(wú)索引）如devaddress' 這個(gè)時(shí)候，就是全表掃描 + filesort，效率更慢；
備注：

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

這條語(yǔ)句是 方案一 ：先通過(guò)id找到2000000，然后，剩下的20條再全表掃描；還是，方案二： 通過(guò)id回表直接找到2000020條，然后，放棄前2000000條；理論上剩下20條進(jìn)行全表掃描肯定是快很多的；但是，有點(diǎn)尷尬。Mysql選擇的其實(shí)是方案二；

3.2 解決之道

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

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

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

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

3.2.1 對(duì)devcho建立索引

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

執(zhí)行sql語(yǔ)句：

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

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

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

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

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

回表是sql瓶頸驗(yàn)證:

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

3.2.3 解決之道

我們剛剛發(fā)現(xiàn)，因?yàn)閘imit比較笨。select * from amj_devinfo where devcho = "77" limit 20000, 10;需要回表20010次；但是，我們只需要它回表10次啊。所以，我們可以先把符合條件的id找出來(lái)；再根據(jù)id使用inner join 去進(jìn)行回表；
sql語(yǔ)句如下：

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

查詢時(shí)間：0.025s

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

4 測(cè)試時(shí)走過(guò)的坑

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

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

4.1 百萬(wàn)數(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

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

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

4.2 寫(xiě)sql時(shí)，把"77"寫(xiě)成了77；

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

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

這里 第一條語(yǔ)句因?yàn)樽侄伪鹊诙l語(yǔ)句中少；所以，放入sort_buffer中的數(shù)據(jù)是不同的；
問(wèn)題回顧：我之前就在想，為什么我基于索引列查詢id會(huì)這么慢？我當(dāng)時(shí)沒(méi)想到索引失效問(wèn)題；后來(lái)，我是怎么發(fā)現(xiàn)這個(gè)問(wèn)題的呢？因?yàn)?，基于索引列查詢的時(shí)候，Mysql要掃描的字段也就是20010條數(shù)據(jù)即可；而我查看Handler_read_next（此選項(xiàng)表明在進(jìn)行索引掃描時(shí)，按照索引從數(shù)據(jù)文件里取數(shù)據(jù)的次數(shù)）時(shí)，

Handler_read_next 4274160 

explain分析結(jié)果：

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

4.2.1 為什么會(huì)索引失效？

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

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

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

大掃描行數(shù) VS 隨機(jī)IO

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

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

總結(jié)

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

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

最新評(píng)論