使用MySQL進行千萬級別數(shù)據(jù)查詢的技巧分享

更新時間：2024年03月01日 11:25:05 作者：zy_zeros

這篇文章主要介紹了如何使用MySQL進行千萬級別數(shù)據(jù)查詢的技巧,文中通過代碼示例給大家講解的非常詳細,對大家的學習或工作有一定的幫助,需要的朋友可以參考下

一般分頁

在系統(tǒng)中需要進行分頁操作時，我們通常會使用 LIMIT 加上偏移量的方式實現(xiàn)，語法格式如下。

SELECT … FROM … WHERE … ORDER BY … LIMIT …

在有對應索引的情況下，這種方式一般效率還不錯。但它存在一個讓人頭疼的問題，在偏移量非常大的時候，也就是翻頁到很靠后的頁面時，查詢速度會變得越來越慢。

我們來演示一下。

先創(chuàng)建一個訂單表 t_order。

CREATE TABLE t_order (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘自增主鍵',
order_no varchar(32) NOT NULL COMMENT ‘訂單號',
user_id varchar(20) NOT NULL COMMENT ‘用戶ID',
amount decimal(18,2) NOT NULL COMMENT ‘訂單金額',
order_status tinyint(4) NOT NULL COMMENT ‘訂單狀態(tài)：0新建 1處理中 2成功 3失敗',
create_time datetime NOT NULL COMMENT ‘創(chuàng)建時間',
PRIMARY KEY (id),
UNIQUE KEY uniq_order_no (order_no) USING BTREE COMMENT ‘訂單號唯一索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

往表中插入1100w 條數(shù)據(jù)。（ t1 是一個有100條數(shù)據(jù)的表，這里我利用笛卡爾乘積的方式插入1100w條數(shù)據(jù)）

set @N=0;
INSERT INTO t_order(order_no,user_id,amount,order_status,create_time)
select
CONCAT(“APP”, @N:=@N+1),
CONCAT(“USER_ID_”, @N+1),
@N%10000,
@N%4,
NOW()
from t1 a, t1 b, t1 c, t1 d
LIMIT 11000000;

我們看下，如下這些查詢花費的時間。

select * from t_order order by id limit 0, 10;
select * from t_order order by id limit 10000, 10;
select * from t_order order by id limit 100000, 10;
select * from t_order order by id limit 1000000, 10;
select * from t_order order by id limit 10000000, 10;

執(zhí)行時間如下：

– 0.002
– 0.045
– 0.069
– 0.517
– 4.134

同樣是只查詢10條數(shù)據(jù)，最開始的時候查詢花費 0.002s，而到最后，查詢花費了 4.134s。

這是什么原因呢？

這是因為查詢時 MySQL 并不是跳過 OFFSET 行，而是取 OFFSET+N 行，然后放棄前 OFFSET 行，最后返回 N 行，當 OFFSET 特別大的時候，效率就非常的低下。

拿 limit 10000, 10 這條語句來說明一下， MySQL在執(zhí)行這條查詢的時候，需要查詢 10010 (10000 + 10) 條記錄，然后只返回最后 10 條，并將前面的 10000 條記錄拋棄，這樣當翻頁越靠后時，代價就變得越來越高。

知道問題所在了，那有什么辦法可以優(yōu)化，解決這個問題呢？

1優(yōu)化一：記錄位置，避免使用 OFFSET

首先獲取第一頁的結果：

select * from t_order limit 10;

假如上邊返回的是 id 為1 ~ 10的記錄，我們將 10 這個值記住，下一頁查詢就可以直接從 10 這個值開始。

select * from t_order where id > 10 limit 10;

這樣做，無論翻頁到多少頁，性能都會很好：

select * from t_order limit 10;
select * from t_order where id > 10000 limit 10;
select * from t_order where id > 100000 limit 10;
select * from t_order where id > 1000000 limit 10;
select * from t_order where id > 10000000 limit 10;

執(zhí)行時間如下：

– 0.003
– 0.005
– 0.002
– 0.002
– 0.002

而如果我們當前記錄的 id 值為 10000，我們想查上一頁怎么辦呢？返回去查一下即可：

select * from t_order where id <= 10000 order by id desc limit 10,10;

這種優(yōu)化方式，可以實現(xiàn)上一頁、下一頁這種的分頁。但如果想要實現(xiàn)跳轉(zhuǎn)到指定頁碼的話，就需要保證 id 連續(xù)不中斷，再通過計算找到準確的位置。

2優(yōu)化二：計算邊界值，轉(zhuǎn)換為已知位置的查詢

如果 id 連續(xù)不中斷，我們就可以計算出每一頁的邊界值，讓 MySQL 根據(jù)邊界值進行范圍掃描，查出數(shù)據(jù)。

select * from t_order where id between 0 and 10;
select * from t_order where id between 10000 and 10010;
select * from t_order where id between 100000 and 100010;
select * from t_order where id between 1000000 and 1000010;
select * from t_order where id between 10000000 and 10000010;

執(zhí)行時間如下：

– 0.001
– 0.002
– 0.002
– 0.001
– 0.001

3優(yōu)化三：使用索引覆蓋+子查詢優(yōu)化

先在索引樹中找到開始位置的 id 值，再根據(jù)找到的 id 值查詢行數(shù)據(jù)。

select * from t_order where id >= (select id from t_order order by id limit 0, 1) order by id limit 10;
select * from t_order where id >= (select id from t_order order by id limit 10000, 1) order by id limit 10;
select * from t_order where id >= (select id from t_order order by id limit 100000, 1) order by id limit 10;
select * from t_order where id >= (select id from t_order order by id limit 1000000, 1) order by id limit 10;
select * from t_order where id >= (select id from t_order order by id limit 10000000, 1) order by id limit 10;

執(zhí)行時間如下：

– 0.007
– 0.009
– 0.047
– 0.332
– 2.822

可以看到，這種優(yōu)化方式也可以提升查詢速度。這其實是利用了索引覆蓋的如下好處：

索引文件不包含行數(shù)據(jù)的所有信息，故其大小遠小于數(shù)據(jù)文件，因此可以減少大量的IO操作。

索引覆蓋只需要掃描一次索引樹，不需要回表掃描行數(shù)據(jù)，所以性能比回表查詢要高。

4優(yōu)化四：使用索引覆蓋+連接查詢優(yōu)化

這種優(yōu)化方式跟優(yōu)化三原理一樣。也是先在索引上進行分頁查詢，當找到 id 后，再統(tǒng)一通過 JOIN 關聯(lián)查詢得到最終需要的數(shù)據(jù)詳情。

select * from t_order a Join (select id from t_order order by id limit 0, 10) b ON a.id = b.id;
select * from t_order a Join (select id from t_order order by id limit 10000, 10) b ON a.id = b.id;
select * from t_order a Join (select id from t_order order by id limit 100000, 10) b ON a.id = b.id;
select * from t_order a Join (select id from t_order order by id limit 1000000, 10) b ON a.id = b.id;
select * from t_order a Join (select id from t_order order by id limit 10000000, 10) b ON a.id = b.id;

執(zhí)行時間如下：