快捷導(dǎo)航

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

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

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

一般分頁

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

SELECT … FROM … WHERE … ORDER BY … LIMIT …

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

我們來演示一下。

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

CREATE TABLE t_order (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘自增主鍵',
order_no varchar(32) NOT NULL COMMENT ‘訂單號(hào)',
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)建時(shí)間',
PRIMARY KEY (id),
UNIQUE KEY uniq_order_no (order_no) USING BTREE COMMENT ‘訂單號(hào)唯一索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

往表中插入1100w 條數(shù)據(jù)。（ t1 是一個(gè)有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;

我們看下，如下這些查詢花費(fèi)的時(shí)間。

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í)行時(shí)間如下：

– 0.002
– 0.045
– 0.069
– 0.517
– 4.134

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

這是什么原因呢？

這是因?yàn)椴樵儠r(shí) MySQL 并不是跳過 OFFSET 行，而是取 OFFSET+N 行，然后放棄前 OFFSET 行，最后返回 N 行，當(dāng) OFFSET 特別大的時(shí)候，效率就非常的低下。

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

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

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

首先獲取第一頁的結(jié)果：

select * from t_order limit 10;

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

select * from t_order where id > 10 limit 10;

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

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í)行時(shí)間如下：

– 0.003
– 0.005
– 0.002
– 0.002
– 0.002

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

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

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

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

如果 id 連續(xù)不中斷，我們就可以計(jì)算出每一頁的邊界值，讓 MySQL 根據(jù)邊界值進(jìn)行范圍掃描，查出數(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í)行時(shí)間如下：

– 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í)行時(shí)間如下：

– 0.007
– 0.009
– 0.047
– 0.332
– 2.822

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

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

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

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

這種優(yōu)化方式跟優(yōu)化三原理一樣。也是先在索引上進(jìn)行分頁查詢，當(dāng)找到 id 后，再統(tǒng)一通過 JOIN 關(guān)聯(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í)行時(shí)間如下：