操作環(huán)境:

• MySQL: 5.7.26
• Navicat for MySQL

目的: MySQL千萬級(jí)數(shù)據(jù)的優(yōu)化查詢

1. 創(chuàng)建1000w數(shù)據(jù)

1.1 建表

建表SQL語句

CREATE TABLE `big_data` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX idx_name(`name`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

big_data表一共三個(gè)字段id(主鍵),name(創(chuàng)建索引),age,存儲(chǔ)引擎使用的INNODB,INNODB有一個(gè)注意的地方:INNODB引擎數(shù)據(jù)量超過2000W,讀寫性能會(huì)有很大下降。

1.2 插入1000W條數(shù)據(jù)

向MySQL中插入數(shù)據(jù)我能想到的只有2種方式;

• 使用編程語言連接MySQL然后插入1000W數(shù)據(jù)
• 使用MySQL存儲(chǔ)過程
  為了方便我使用存儲(chǔ)過程:

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

CREATE PROCEDURE `insert_data`(IN num INT)
BEGIN
DECLARE n INT DEFAULT 1;
WHILE n <= num DO
INSERT INTO big_data(name,age,email)values(concat('alex',n),rand()*50);
set n=n+1;
end while;
ENd;

執(zhí)行存儲(chǔ)過程插入1000W數(shù)據(jù)

CALL insert_data(10000000);

我使用Navicat這個(gè)MySQL可視化工具,插入1000W數(shù)據(jù)比較耗時(shí)，這里說一下如何通過Navicat工具查看執(zhí)行時(shí)間與插入了多少條數(shù)據(jù)。

1.png

1.png

插入數(shù)據(jù)耗時(shí)很長(zhǎng)，我沒記準(zhǔn)確時(shí)間，但是我通過了一波死神VS火影3.3.

2. 分頁查詢

2.1 普通分頁查詢

上面5條SQL我們逐一對(duì)比：

• 1號(hào)與2號(hào)對(duì)比: 1號(hào)2號(hào)都是用limit a,b 這種方式，MySQL執(zhí)行時(shí)候會(huì)進(jìn)行全表掃描，從開頭掃描，直到掃到300W，然后取后面10條數(shù)據(jù)。2號(hào)耗時(shí)比1號(hào)長(zhǎng)是因?yàn)椋?號(hào)只取了id，而1號(hào)還要取其他所有的字段。兩者時(shí)間差為274ms，雖然減少了，但是相差頁不是很明顯。

• 2號(hào)與3號(hào)對(duì)比: 上面介紹了對(duì)于limit a,b 這種形式會(huì)根據(jù)主鍵id進(jìn)行全表掃描，從1一直查到a，a值越大，耗時(shí)越久。

2.2 主鍵連續(xù)查詢(WHERE優(yōu)化分頁查詢)

1號(hào)SQL通過WHERE可以通過主鍵索引快速定位到第900W條記錄處，然后取10條記錄(注意這里包括第900W條記錄，與 limit a,b不一樣,limit 不包括第a條記錄)。2號(hào)和1號(hào)相同。與普通查詢相比快了很多。

但是這種WHERE分頁查詢只能夠在主鍵id連續(xù)的情況下使用，如果主鍵id不是連續(xù)，那么它的性能會(huì)大大下降(這個(gè)復(fù)現(xiàn)，我手工試了以下把第900W條記錄刪除，在查詢速度沒有減慢，再把第900W條記錄后面的10條刪除了，在查詢速度還是沒有減慢，復(fù)現(xiàn)不出來，此處暫時(shí)擱著)。

// TODO id不連續(xù)使用WHERE查詢

2.3 主鍵不連續(xù)查詢(關(guān)聯(lián)查詢)

在2.1普通分頁查詢種，只查詢id的速度也是可以接收，所以我們可以先把id查詢出來，然后再根據(jù)id查詢數(shù)據(jù)記錄。

SQL 解析：子查詢( SELECT id FROM big_data LIMIT 9000000,10 ) tmp用來查找分頁數(shù)據(jù)id，將結(jié)果集保存到tmp臨時(shí)表種，再通過JOIN ON 連接查詢記錄。可以看到還是有一定優(yōu)化效果，但是如果分頁查詢的數(shù)據(jù)靠后的話(如3號(hào)第900W條開始)，查詢需要的時(shí)間也不是很理想。所以對(duì)于查詢靠后的數(shù)據(jù)通常再業(yè)務(wù)處理，用戶只能查詢前面的數(shù)據(jù)。
比如百度搜索，只顯示前面的76頁數(shù)據(jù)。

1.png