MySQL百萬級(jí)數(shù)據(jù)，如何做分頁(yè)查詢

隨著業(yè)務(wù)的增長(zhǎng)，數(shù)據(jù)庫(kù)的數(shù)據(jù)也呈指數(shù)級(jí)增長(zhǎng)，拿訂單表為例，之前的訂單表每天只有幾千個(gè)，一個(gè)月下來不超過十萬。

而現(xiàn)在每天的訂單大概就是2w+，目前訂單表的數(shù)據(jù)已經(jīng)達(dá)到了700w。

這帶來了各種各樣的問題，今天我先從一個(gè)小問題開始。

之前所寫的代碼mysql的分頁(yè)都是采用的limit方式進(jìn)行，這種方式固然代碼比較簡(jiǎn)單，但數(shù)據(jù)量大了之后真的是查的慢。

所以此處涉及到mysql大數(shù)據(jù)量后的分頁(yè)查詢方法及其優(yōu)化技巧

方法1：直接使用數(shù)據(jù)庫(kù)提供的SQL語(yǔ)句

語(yǔ)句樣式: MySQL中，可用如下方法：

SELECT * FROM 表名稱 LIMIT M,N

適應(yīng)場(chǎng)景：適用于數(shù)據(jù)量較少的情況(元組百/千級(jí))

原因/缺點(diǎn)：全表掃描,速度會(huì)很慢 且有的數(shù)據(jù)庫(kù)結(jié)果集返回不穩(wěn)定(如某次返回1,2,3,另外的一次返回2,1,3)。

Limit限制的是從結(jié)果集的M位置處取出N條輸出，其余拋棄。

再具體進(jìn)行測(cè)試之前，我們需要先創(chuàng)建100w條測(cè)試數(shù)據(jù)，推薦使用存儲(chǔ)過程進(jìn)行創(chuàng)建

• 創(chuàng)建存儲(chǔ)過程

DROP PROCEDURE IF EXISTS create_user_tel;
create procedure create_user_tel() 
begin 
    declare id int; 
    set id=1;
    while id <=1000000
      do 
        INSERT INTO `user01` VALUES(id,  'test123', 'm');
        set id=id+1;
    end while;
end;

• 執(zhí)行存儲(chǔ)過程

call create_user_tel();

下面開始測(cè)試這種方法

可以看到我表里總共80w條數(shù)據(jù)，我們看看用這種方法來分頁(yè)查詢的耗時(shí)

select * from user01 limit 780000, 20;

像這種分頁(yè)最大的頁(yè)碼頁(yè)顯然這種時(shí)間是無法忍受的，同時(shí)大家可以自行測(cè)試一下limit 100 20 ,limit 1000 20,limit 10000 20等所耗費(fèi)的時(shí)間，不難發(fā)現(xiàn)limit語(yǔ)句的查詢時(shí)間與起始記錄的位置成正比

方法2：建立主鍵或唯一索引, 利用索引(假設(shè)每頁(yè)10條)

語(yǔ)句樣式：MySQL中，可用如下方法：

SELECT id FROM 表名稱 WHERE id > (pageNum*10) LIMIT M

• 適應(yīng)場(chǎng)景：適用于數(shù)據(jù)量多的情況(元組數(shù)上萬)
• 原因：索引掃描,速度會(huì)很快。

我們都知道，利用了索引查詢的語(yǔ)句中如果只包含了那個(gè)索引列（覆蓋索引），那么這種情況會(huì)查詢很快。

因?yàn)槔盟饕檎矣袃?yōu)化算法，且數(shù)據(jù)就在查詢索引上面，不用再去找相關(guān)的數(shù)據(jù)地址了，這樣節(jié)省了很多時(shí)間。另外Mysql中也有相關(guān)的索引緩存，在并發(fā)高的時(shí)候利用緩存就效果更好了。

在我們的例子中，我們知道id字段是主鍵，自然就包含了默認(rèn)的主鍵索引?，F(xiàn)在讓我們看看利用覆蓋索引的查詢效果如何。

這次我們之間查詢最后一頁(yè)的數(shù)據(jù)（利用覆蓋索引，只包含id列），如下：

select id from user01 limit 780000, 20;

上面這個(gè)語(yǔ)句只能查詢id，如果我們也要查詢所有列，有兩種方法：

• 一種是id>=的形式
• 另一種就是利用join

SELECT * FROM user01 
WHERE id > =(
select id from user01 limit 780000, 1) 
limit 20

另一種寫法

SELECT * FROM user01 a JOIN 
(select id from user01 limit 780000, 20) b 
ON a.id = b.id

自己可以操作一下就會(huì)發(fā)現(xiàn)效率會(huì)大幅度提升，如果你發(fā)現(xiàn)這些語(yǔ)句速度差別不大的話，性能的限制還有可能是你服務(wù)器或自己的電腦性能不足導(dǎo)致的

通過主鍵或者索引的方式去查詢可能會(huì)出現(xiàn)一個(gè)致命的問題就是數(shù)據(jù)查詢出來并不是按照主鍵或者索引排序的，所以會(huì)有漏掉數(shù)據(jù)的情況

這種情況可以通過方法三來解決

方法3：基于索引再排序

語(yǔ)句樣式：MySQL中，可用如下方法：

SELECT * FROM 表名稱 
WHERE id_pk > (pageNum*10) 
ORDER BY id_pk ASC LIMIT M

• 適應(yīng)場(chǎng)景：適用于數(shù)據(jù)量多的情況(元組數(shù)上萬). 最好ORDER BY后的列對(duì)象是主鍵或唯一索引,使得ORDERBY操作能利用索引被消除但結(jié)果集是穩(wěn)定的(穩(wěn)定的含義,參見方法1)
• 原因：索引掃描,速度會(huì)很快.

SELECT * FROM user01 
WHERE id >= 780000
ORDER BY id ASC LIMIT 20

這種方式會(huì)讓我們的查詢效率得到更大的提升

方法4：基于索引使用prepare

語(yǔ)句樣式：MySQL中，可用如下方法：

PREPARE stmt_name FROM SELECT * FROM 表名稱 WHERE id_pk > (？* ？) ORDER BY id_pk ASC LIMIT M

第一個(gè)問號(hào)表示pageNum，第二個(gè)問號(hào)表示每頁(yè)元組數(shù)。

• 適應(yīng)場(chǎng)景：大數(shù)據(jù)量
• 原因：索引掃描,速度會(huì)很快。

prepare語(yǔ)句又比一般的查詢語(yǔ)句快一點(diǎn)。

方法5：利用MySQL支持ORDER操作可以利用索引快速定位部分元組，避免全表掃描。

比如：讀第1000到1019行數(shù)據(jù)

SELECT * FROM your_table 
WHERE id>=780000 
ORDER BY id ASC 
LIMIT 0,20

可以發(fā)現(xiàn)這種效率和上面方法的效率差不多，因?yàn)樾实奶嵘脑蚨际亲遡d主鍵索引

方法6：利用"子查詢/連接+索引"快速定位元組的位置,然后再讀取元組

SELECT * FROM your_table 
WHERE id <= 
(SELECT id FROM your_table 
ORDER BY id desc 
LIMIT ($page-1)*$pagesize 
ORDER BY id desc 
LIMIT $pagesize

利用連接示例：

SELECT * FROM your_table AS t1 
JOIN (
SELECT id FROM your_table 
ORDER BY id desc LIMIT ($page-1)*$pagesize 
) AS t2 
WHERE t1.id <= t2.id 
ORDER BY t1.id desc LIMIT $pagesize;

我個(gè)人實(shí)驗(yàn)之后發(fā)現(xiàn)效率極其低下

綜上：

如果對(duì)于有where 條件，又想走索引用limit的，必須設(shè)計(jì)一個(gè)索引，將where 放第一位，limit用到的主鍵放第2位，而且只能select 主鍵！

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。