我們?nèi)粘Ｗ龇猪撔枨髸r，一般會用limit實現(xiàn)，但是當偏移量特別大的時候，查詢效率就變得低下。

本文將分4個方案，討論如何優(yōu)化MySQL百萬數(shù)據(jù)的深分頁問題.

一、limit深分頁為什么會變慢

表結構

CREATE TABLE account (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵Id',
  name varchar(255) DEFAULT NULL COMMENT '賬戶名',
  balance int(11) DEFAULT NULL COMMENT '余額',
  create_time datetime NOT NULL COMMENT '創(chuàng)建時間',
  update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (id),
  KEY idx_name (name),
  KEY idx_update_time (update_time) //索引
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='賬戶表';

執(zhí)行的深分頁SQL為

select id,name,balance from account where update_time> '2020-09-19' limit 100000,10;

這個SQL的執(zhí)行時間如下：

執(zhí)行完需要0.742秒，深分頁為什么會變慢呢？如果換成 limit 0,10，只需要0.006秒哦

我們先來看下這個SQL的執(zhí)行流程：

• 通過普通二級索引樹idx_update_time，過濾update_time條件，找到滿足條件的記錄ID。
• 通過ID，回到主鍵索引樹，找到滿足記錄的行，然后取出展示的列（回表）
• 掃描滿足條件的100010行，然后扔掉前100000行，返回。

（每一條select語句都會從1遍歷至當前位置，若跳轉到第10000頁，則會遍歷100000條記錄）

執(zhí)行計劃如下：

SQL變慢原因有兩個：

1. limit語句會先掃描offset+n行，然后再丟棄掉前offset行，返回后n行數(shù)據(jù)。也就是說limit 100000,10，就會掃描100010行，而limit 0,10，只掃描10行。
2. limit 100000,10 掃描更多的行數(shù)，也意味著回表更多的次數(shù)。

二、優(yōu)化方案

2.1 通過子查詢優(yōu)化（覆蓋索引）

因為以上的SQL，回表了100010次，實際上，我們只需要10條數(shù)據(jù)，也就是我們只需要10次回表其實就夠了。因此，我們可以通過減少回表次數(shù)來優(yōu)化。

回顧B+樹結構

如何減少回表次數(shù)呢？我們先來復習下B+樹索引結構

InnoDB中，索引分主鍵索引（聚簇索引）和二級索引

• 主鍵索引，葉子節(jié)點存放的是整行數(shù)據(jù)
• 二級索引，葉子節(jié)點存放的是主鍵的值。

覆蓋索引

覆蓋索引（covering index ，或稱為索引覆蓋）即從非主鍵索引中就能查到的記錄，而不需要查詢主鍵索引中的記錄，避免了回表的產(chǎn)生減少了樹的搜索次數(shù)，顯著提升性能。

如何確定數(shù)據(jù)庫成功使用了覆蓋索引呢？ —— 當發(fā)起一個索引覆蓋查詢時，在explain的extra列可以看到using index的信息

可以看到Extra中的Using index，表明我們成功使用了覆蓋索引

把條件轉移到主鍵索引樹

如果我們把查詢條件，轉移回到主鍵索引樹，那就不就可以減少回表次數(shù)啦。轉移到主鍵索引樹查詢的話，查詢條件得改為主鍵id了，之前SQL的update_time這些條件咋辦呢？抽到子查詢那里嘛~

子查詢那里怎么抽的呢？因為二級索引葉子節(jié)點是有主鍵ID的，所以我們直接根據(jù)update_time來查主鍵ID即可，同時我們把 limit 100000的條件，也轉移到子查詢，完整SQL如下：

select id,name,balance FROM account where id >= (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 1) LIMIT 10; -- （可以加下時間條件到外面的主查詢）

查詢效果一樣的，執(zhí)行時間只需要0.038秒！ 0.742秒 ——> 0.038秒

我們來看下執(zhí)行計劃

由執(zhí)行計劃得知，子查詢 table a查詢是用到了idx_update_time索引。

首先在索引上拿到了聚集索引的主鍵ID，省去了回表操作，然后第二查詢直接根據(jù)第一個查詢的ID往后再去查10個就可以了!

所謂的覆蓋索引就是從普通索引樹中就能查到的想要數(shù)據(jù)，而不需要通過回表從主鍵索引中查詢其他列，能夠顯著提升性能。

因此，這個方案是可以的~

2.2 INNER JOIN 延遲關聯(lián)

延遲關聯(lián)的優(yōu)化思路，跟子查詢的優(yōu)化思路其實是一樣的：都是把條件轉移到主鍵索引樹，然后減少回表。不同點是，延遲關聯(lián)使用了inner join代替子查詢。

優(yōu)化后的SQL如下：

SELECT  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.update_time >= '2020-09-19' ORDER BY a.update_time LIMIT 100000, 10) AS  acct2 on acct1.id= acct2.id;

查詢效果也是杠桿的，只需要0.034秒

執(zhí)行計劃如下：

查詢思路就是，先通過idx_update_time二級索引樹查詢到滿足條件的主鍵ID，再與原表通過主鍵ID內(nèi)連接，這樣后面直接走了主鍵索引了，同時也減少了回表。

2.3 標簽記錄法（要求id是有序的）

limit 深分頁問題的本質原因就是：偏移量（offset）越大，mysql就會掃描越多的行，然后再拋棄掉。這樣就導致查詢性能的下降。

其實我們可以采用標簽記錄法，就是標記一下上次查詢到哪一條了，下次再來查的時候，從該條開始往下掃描。就好像看書一樣，上次看到哪里了，你就折疊一下或者夾個書簽，下次來看的時候，直接就翻到啦。

select id,name,balance from account limit 1000000,10;

假設上一次記錄到100000，則SQL可以優(yōu)化為：

select id,name,balance FROM account where id > 100000 order by id limit 10;

這樣的話，后面無論翻多少頁，性能都會不錯的，因為命中了id索引。但是你，這種方式有局限性：要求id是連續(xù)的、并且有序。

在有序的條件下，也可以使用比如創(chuàng)建時間等其他字段來代替主鍵id，但是前提是這個字段是建立了索引的。

id不是連續(xù)，我們可以通過order by讓它連續(xù)

總之，使用條件過濾的方式來優(yōu)化 limit 是有諸多限制的，一般還是推薦使用覆蓋索引的方式來優(yōu)化。

2.4 使用between…and…

很多時候，可以將limit查詢轉換為已知位置的查詢，這樣MySQL通過范圍掃描between...and，就能獲得到對應的結果。

select id,name,balance from account limit 1000000,10;

如果知道邊界值為100000，100010后，就可以這樣優(yōu)化：

select id,name,balance FROM account where id between 100000 and 100010 order by id desc;

總結

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

最新評論