前言

之前有看過(guò)到mysql大數(shù)據(jù)量分頁(yè)情況下性能會(huì)很差，但是沒(méi)有探究過(guò)它的原因，今天講一講mysql大數(shù)據(jù)量下偏移量很大，性能很差的問(wèn)題，并附上解決方式。

原因

將原因前我們先做一個(gè)試驗(yàn)，我做試驗(yàn)使用的是mysql5.7.24版本（mysql8上我也試驗(yàn)出來(lái)同樣的問(wèn)題），看看mysql是不是在偏移量比較大的時(shí)候分頁(yè)會(huì)比較慢，性能比較差

版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.24    |
+-----------+
1 row in set (0.00 sec)

表結(jié)構(gòu)

CREATE TABLE `trace_monitor_log` (
  `id` varchar(30) NOT NULL COMMENT '表主鍵id',
  `user_id` varchar(30) DEFAULT NULL COMMENT '用戶(hù)id',
  `trace_id` varchar(30) DEFAULT NULL COMMENT '追蹤id',
  `trace_type` varchar(30) DEFAULT NULL COMMENT '追蹤類(lèi)型',
  `path` mediumtext COMMENT '追蹤路徑',
  `source_ip` varchar(255) DEFAULT NULL COMMENT '來(lái)源ip',
  `ext_params` mediumtext COMMENT '請(qǐng)求擴(kuò)展參數(shù)',
  `costs` int(11) DEFAULT '0' COMMENT '請(qǐng)求耗時(shí)(毫秒)',
  `exception` mediumtext COMMENT '異常信息',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  PRIMARY KEY (`id`),
  KEY `trace_id` (`trace_id`),
  KEY `trace_type` (`trace_type`),
  KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='監(jiān)控日志表';

試驗(yàn)過(guò)程

這個(gè)是我從測(cè)試環(huán)境找的一張日志表，里面的數(shù)據(jù)量是580萬(wàn)左右，我們先看看只查詢(xún)普通10條數(shù)據(jù)的情況。

數(shù)據(jù)量

mysql> select count(*) from trace_monitor_log;
+----------+
| count(*) |
+----------+
|  5806836 |
+----------+
1 row in set (1.66 sec)

explain select * from trace_monitor_log order by trace_id limit 10;

可以看到?jīng)]有offset偏移量的時(shí)候可以直接走索引，key是trace_id，并且只查詢(xún)了10條數(shù)據(jù)。

我們?cè)趤?lái)看看如果offset是1000的時(shí)候。

explain select * from trace_monitor_log order by trace_id limit 10 offset 1000;

可以看到偏移量比較小的時(shí)候還是可以走索引，rows是1010，這時(shí)候發(fā)現(xiàn)雖然我們只要查詢(xún)10條數(shù)據(jù)，但是查詢(xún)的時(shí)候還是會(huì)掃描1000條無(wú)用的索引記錄。

我們接下往下把offset加到100萬(wàn)

explain select * from trace_monitor_log order by trace_id limit 10 offset 1000000;

這個(gè)時(shí)候就會(huì)發(fā)現(xiàn)一個(gè)神奇的現(xiàn)象，竟然沒(méi)有走索引了，type是ALL，就是全表掃描了，執(zhí)行時(shí)間大概花了40多秒，性能確實(shí)很差。這里的原因，本來(lái)根據(jù)索引查出來(lái)100萬(wàn)條記錄，然后把不需要的數(shù)據(jù)給丟棄掉，mysql會(huì)計(jì)算查詢(xún)成本，發(fā)現(xiàn)這樣走索引還沒(méi)有全表掃描快，所以用了全表掃描，但是全表掃描就為了拿到十條數(shù)據(jù)顯然是性能很差的。mysql并不會(huì)自動(dòng)判斷先根據(jù)trace_id的索引找到偏移量需要的10條數(shù)據(jù)，再根據(jù)這10條索引找到葉子節(jié)點(diǎn)的主鍵記錄去回表查詢(xún)數(shù)據(jù)，導(dǎo)致了這么差的性能。

解決方式

1.延遲關(guān)聯(lián)

先使用覆蓋索引的方式找到對(duì)應(yīng)order by 之后的limit條索引，因?yàn)槭歉采w索引，直接用的索引記錄，沒(méi)有回表所以很快。接著在使用join的方式，將索引記錄和原表關(guān)聯(lián)起來(lái)就可以查出來(lái)對(duì)應(yīng)的limit條數(shù)據(jù)。

explain select * from trace_monitor_log t1 join (select trace_id from trace_monitor_log  order by trace_id limit 1000000,10) t2 on t1.trace_id = t2.trace_id

執(zhí)行時(shí)間平均在500-600毫秒左右，相比全表掃描快了很多。

2.書(shū)簽記錄

這個(gè)概念我也是從網(wǎng)上看到的，還沒(méi)找到具體這個(gè)概念的出處在哪里。不過(guò)不要困于這個(gè)概念，只要理解是先找到對(duì)應(yīng)要查詢(xún)一條索引記錄（書(shū)簽），再根據(jù)這個(gè)索引去范圍查詢(xún)對(duì)應(yīng)的limit條數(shù)數(shù)據(jù)就容易理解了。

explain select * from trace_monitor_log t1 where trace_id > (select trace_id from trace_monitor_log  order by trace_id limit 999999,1)   order by trace_id limit 10

執(zhí)行時(shí)間和延遲關(guān)聯(lián)差不多，也都走了索引，所以性能也比較好。

到此這篇關(guān)于MySQL進(jìn)行大數(shù)據(jù)量分頁(yè)的優(yōu)化技巧分享的文章就介紹到這了,更多相關(guān)MySQL大數(shù)據(jù)量分頁(yè)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論