MySQL調(diào)優(yōu)之SQL查詢(xún)深度分頁(yè)問(wèn)題怎么解決

這篇文章主要講解了“MySQL調(diào)優(yōu)之SQL查詢(xún)深度分頁(yè)問(wèn)題怎么解決”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“MySQL調(diào)優(yōu)之SQL查詢(xún)深度分頁(yè)問(wèn)題怎么解決”吧！

一、問(wèn)題引入

例如當(dāng)前存在一張表test_user，然后往這個(gè)表里面插入3百萬(wàn)的數(shù)據(jù)：

CREATE TABLE `test_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
  `user_id` varchar(36) NOT NULL COMMENT '用戶(hù)id',
  `user_name` varchar(30) NOT NULL COMMENT '用戶(hù)名稱(chēng)',
  `phone` varchar(20) NOT NULL COMMENT '手機(jī)號(hào)碼',
  `lan_id` int(9) NOT NULL COMMENT '本地網(wǎng)',
  `region_id` int(9) NOT NULL COMMENT '區(qū)域',
  `create_time` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT;

在數(shù)據(jù)庫(kù)開(kāi)發(fā)過(guò)程中我們經(jīng)常會(huì)使用分頁(yè)，核心技術(shù)是使用用 limit start, count 分頁(yè)語(yǔ)句進(jìn)行數(shù)據(jù)的讀取。 

我們分別看下從0、10000、100000、500000、1000000、1800000開(kāi)始分頁(yè)的執(zhí)行時(shí)長(zhǎng)（每頁(yè)取100條）。

SELECT * FROM test_user LIMIT 0,100;         # 0.031
SELECT * FROM test_user LIMIT 10000,100;     # 0.047
SELECT * FROM test_user LIMIT 100000,100;    # 0.109
SELECT * FROM test_user LIMIT 500000,100;    # 0.219
SELECT * FROM test_user LIMIT 1000000,100;   # 0.547s
SELECT * FROM test_user LIMIT 1800000,100;   # 1.625s

我們已經(jīng)看出隨著起始記錄的增加，時(shí)間也隨著增大。這說(shuō)明分頁(yè)語(yǔ)句limit跟起始頁(yè)碼是有很大關(guān)系的，那么我們把起始記錄改為290w看下：

SELECT * FROM test_user LIMIT 2900000,100; # 3.062s

我們驚訝的發(fā)現(xiàn)MySQL在數(shù)據(jù)量大的情況下分頁(yè)起點(diǎn)越大，查詢(xún)速度越慢！ 

那么為什么會(huì)出現(xiàn)上述這種情況呢？

答案： 因?yàn)?nbsp;limit 2900000,100 的語(yǔ)法實(shí)際上是mysql掃描到前2900100條數(shù)據(jù)，之后丟棄前面的3000000行，這個(gè)步驟其實(shí)是浪費(fèi)掉的。

從中我們也能總結(jié)出以下兩件事情：

limit語(yǔ)句的查詢(xún)時(shí)間與起始記錄的位置成正比。

mysql的limit語(yǔ)句是很方便，但是對(duì)記錄很多的表并不適合直接使用。

二、MySQL中的limit用法

limit子句可以被用于強(qiáng)制select語(yǔ)句返回指定的記錄數(shù)，其語(yǔ)法格式如下：

SELECT * FROM 表名 limit m,n;
SELECT * FROM table LIMIT [offset,] rows;

limit接受一個(gè)或兩個(gè)數(shù)字參數(shù)，參數(shù)必須是一個(gè)整數(shù)常量，如果給定兩個(gè)參數(shù)：

第一個(gè)參數(shù)指定第一個(gè)返回記錄行的偏移量
第二個(gè)參數(shù)指定返回記錄行的最大數(shù)目

2.1 m代表從m+1條記錄行開(kāi)始檢索，n代表取出n條數(shù)據(jù)。(m可設(shè)為0) 

SELECT * FROM 表名 limit 6,5;

上述SQL表示從第7條記錄行開(kāi)始算，取出5條數(shù)據(jù) 

2.2 值得注意的是，n可以被設(shè)置為-1，當(dāng)n為-1時(shí)，表示從m+1行開(kāi)始檢索，直到取出最后一條數(shù)據(jù)

SELECT * FROM 表名 limit 6,-1;

上述SQL表示取出第6條記錄行以后的所有數(shù)據(jù)

2.3 若只給出m，則表示從第1條記錄行開(kāi)始算一共取出m條

SELECT * FROM 表名 limit 6;

2.4 以年齡倒序后取出前3行

select * from student order by age desc limit 3;

2.5 跳過(guò)前3行后再2取行

select * from student order by age desc limit 3,2;

三、深度分頁(yè)優(yōu)化策略

方法一：用主鍵id或者唯一索引優(yōu)化

即先找到上次分頁(yè)的最大id，然后利用id上的索引來(lái)查詢(xún)：

SELECT * FROM test_user WHERE id>1000000 LIMIT 100; # 0.047秒

使用此優(yōu)化SQL相比于前面的查詢(xún)速度已經(jīng)快了11倍。除了主鍵ID，也可以利用唯一索引快速定位部分?jǐn)?shù)據(jù)，避免全表掃描。例如讀取第1000到1019行數(shù)據(jù)（pk是唯一鍵），則相對(duì)應(yīng)的優(yōu)化SQL如下：

SELECT * FROM 表名稱(chēng) WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

原因：索引掃描，速度會(huì)很快。

適用場(chǎng)景：如果數(shù)據(jù)查詢(xún)出來(lái)是按照pk或者id進(jìn)行排序，并且全部數(shù)據(jù)沒(méi)有缺失的話(huà)則可以這樣優(yōu)化，否則分頁(yè)操作會(huì)漏數(shù)據(jù)。

方法二：利用索引覆蓋優(yōu)化

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

為什么索引覆蓋查詢(xún)會(huì)很快呢？

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

在我們的測(cè)試表test_user中，id字段是主鍵，自然就包含了默認(rèn)的主鍵索引。現(xiàn)在讓我們看看利用覆蓋索引的查詢(xún)效果如何。

這次我們查詢(xún)第1000001到1000100行的數(shù)據(jù)（利用覆蓋索引，只包含id列）：

SELECT id FROM test_user LIMIT 1000000,100; # 0.843秒

從這個(gè)結(jié)果中發(fā)現(xiàn)查詢(xún)速度比全表掃描速度還要慢（當(dāng)然在重復(fù)執(zhí)行這條SQL，多次查詢(xún)之后速度還是變快了很多，幾乎省了一半時(shí)間，這是由于緩存的原因）， 接著使用explain命令來(lái)查看該SQL的執(zhí)行計(jì)劃，發(fā)現(xiàn)該SQL執(zhí)行采用的普通索引 idx_user_id：

EXPLAIN SELECT id FROM test_user LIMIT 1000000,100;

如果我們把普通索引給刪除的話(huà)，就會(huì)發(fā)現(xiàn)執(zhí)行上述SQL其采用的會(huì)是主鍵索引。那如果不刪除普通索引的話(huà)，針對(duì)這種情況，我們要讓上述SQL走主鍵索引的話(huà)，則可以使用order by語(yǔ)句：

SELECT id FROM test_user ORDER BY id ASC LIMIT 1000000,100; # 0.250秒

那么如果我們也要查詢(xún)所有列，有兩種方法，一種是id>=的形式，另一種就是利用join。

第一種寫(xiě)法： 

SELECT * FROM test_user WHERE ID >= (SELECT id FROM test_user ORDER BY id ASC LIMIT 1000000,1) LIMIT 100;

上述SQL查詢(xún)時(shí)間為0.281秒

第二種寫(xiě)法：

SELECT * FROM (SELECT id FROM test_user ORDER BY id ASC LIMIT 1000000,100) a LEFT JOIN test_user b ON a.id = b.id;

上述SQL查詢(xún)時(shí)間為0.252秒 

方法三：基于索引再排序

其中pageNum表示頁(yè)碼，其取值從0開(kāi)始；pageSize表示指的是每頁(yè)多少條數(shù)據(jù)。

SELECT * FROM 表名稱(chēng) WHERE id_pk > (pageNum*pageSize) ORDER BY id_pk ASC LIMIT pageSize;

適應(yīng)場(chǎng)景：

• 適用于數(shù)據(jù)量多的情況

• 最好ORDER BY后的列對(duì)象是主鍵或唯一索引

• id數(shù)據(jù)沒(méi)有缺失，可以作為序號(hào)使用

• 使用ORDER BY操作能利用索引被消除，但結(jié)果集是穩(wěn)定的

原因：

• 索引掃描，速度會(huì)很快

• 但MySQL的排序操作，只有ASC沒(méi)有DESC。MySQL中索引存儲(chǔ)的排序方式是ASC的，沒(méi)有DESC的索引。這就能夠理解為啥order by 默認(rèn)是按照ASC來(lái)排序的了吧

方法四：基于索引使用prepare

PREPARE預(yù)編譯一個(gè)SQL語(yǔ)句，并為其分配一個(gè)名稱(chēng) stmt_name，以便以后引用該語(yǔ)句，預(yù)編譯好的語(yǔ)句用EXECUTE執(zhí)行。 

PREPARE stmt_name FROM 'SELECT * FROM test_user WHERE id > ? ORDER BY id ASC LIMIT ?';
SET @a = 1000000;
SET @b = 100;
EXECUTE stmt_name USING @a, @b;;

上述SQL查詢(xún)時(shí)間為0.047秒。 

對(duì)于定義好的PREPARE預(yù)編譯語(yǔ)句，我們可以使用下述命令來(lái)釋放該預(yù)編譯語(yǔ)句：

DEALLOCATE PREPARE stmt_name;

原因：

• 索引掃描,速度會(huì)很快.

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

方法五：利用"子查詢(xún)+索引"快速定位數(shù)據(jù) 

其中page表示頁(yè)碼，其取值從0開(kāi)始；pagesize表示指的是每頁(yè)多少條數(shù)據(jù)。 

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);

方法六：利用復(fù)合索引進(jìn)行優(yōu)化

假設(shè)數(shù)據(jù)表 collect ( id, title ,info ,vtype) 就這4個(gè)字段，其中id是主鍵自增，title用定長(zhǎng)，info用text, vtype是tinyint，vtype是一個(gè)普通索引。

現(xiàn)在往里面填充數(shù)據(jù)，填充10萬(wàn)條記錄，數(shù)據(jù)庫(kù)表占用硬1.6G。

select id,title from collect limit 1000,10;

執(zhí)行上述SQL速度很快，基本上0.01秒就OK。

select id,title from collect limit 90000,10;

然后再執(zhí)行上述SQL，就發(fā)現(xiàn)非常慢，基本上平均8~9秒完成。

這個(gè)時(shí)候如果我們執(zhí)行下述，我們會(huì)發(fā)現(xiàn)速度又變的很快，0.04秒就OK。

select id from collect order by id limit 90000,10;

那么這個(gè)現(xiàn)象的原因是什么？

答案：因?yàn)橛昧薸d主鍵做索引,  這里實(shí)現(xiàn)了索引覆蓋，當(dāng)然快。

所以如果想一起查詢(xún)其它列的話(huà)，可以按照索引覆蓋進(jìn)行優(yōu)化，具體如下：

select id,title from collect where id >= (select id from collect order by id limit 90000,1) limit 10;

再看下面的語(yǔ)句，帶上where 條件：

select id from collect where vtype=1 order by id limit 90000,10;

可以發(fā)現(xiàn)這個(gè)速度上也是很慢的，用了8~9秒！

這里有一個(gè)疑惑：vtype 做了索引了啊？怎么會(huì)慢呢？

vtype做了索引是不錯(cuò)，如果直接對(duì)vtype進(jìn)行過(guò)濾：

select id from collect where vtype=1 limit 1000,10;

可以看到速度還是很快的，基本上0.05秒，如果從9萬(wàn)開(kāi)始，那就是0.05*90=4.5秒的速度了。

其實(shí)加了 order by id 就不走索引，這樣做還是全表掃描，解決的辦法是：復(fù)合索引！

因此針對(duì)下述SQL深度分頁(yè)優(yōu)化時(shí)可以加一個(gè)search_index(vtype,id)復(fù)合索引：

select id from collect where vtype=1 order by id limit 90000,10;

綜上： 

• 在進(jìn)行SQL查詢(xún)深度分頁(yè)優(yōu)化時(shí)，如果對(duì)于有where條件，又想走索引用limit的，必須設(shè)計(jì)一個(gè)索引，將where放第一位，limit用到的主鍵放第二位，而且只能select 主鍵。

• 最后根據(jù)查詢(xún)出的主鍵走一級(jí)索引找到對(duì)應(yīng)的數(shù)據(jù)。

• 按這樣的邏輯，百萬(wàn)級(jí)的limit 在0.0x秒就可以分完，完美解決了分頁(yè)問(wèn)題。

感謝各位的閱讀，以上就是“MySQL調(diào)優(yōu)之SQL查詢(xún)深度分頁(yè)問(wèn)題怎么解決”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)MySQL調(diào)優(yōu)之SQL查詢(xún)深度分頁(yè)問(wèn)題怎么解決這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！