復(fù)雜的深分頁問題優(yōu)化
有一個article表衩辟，用于存儲文章的基本信息的，有文章id波附，作者id等一些屬性艺晴，有一個content表，主要用于存儲文章的內(nèi)容掸屡，主鍵是article_id封寞，需求需要將一些滿足條件的作者發(fā)布的文章導(dǎo)入到另外一個庫。
所以我同事就在項目中先查詢出了符合條件的作者id仅财，然后開啟了多個線程狈究，每個線程每次取一個作者id，執(zhí)行查詢和導(dǎo)入工作盏求。
查詢出作者id是1111抖锥，名下的所有文章信息，文章內(nèi)容相關(guān)的信息的SQL如下：
SELECT
a.*, c.*
FROM
article a
LEFT JOIN content c ON a.id = c.article_id
WHERE
a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
LIMIT 210000,100
因為查詢的這個數(shù)據(jù)庫是機(jī)械硬盤的碎罚，在offset查詢到20萬時磅废，查詢時間已經(jīng)特別長了。
運維同事那邊直接收到報警荆烈，說這個庫已經(jīng)IO阻塞了拯勉，已經(jīng)多次進(jìn)行主從切換了，我們就去navicat里面試著執(zhí)行了一下這個語句憔购，也是一直在等待谜喊， 然后對數(shù)據(jù)庫執(zhí)行show proceesslist 命令查看了一下，發(fā)現(xiàn)每個查詢都是處于Writing to net的狀態(tài)倦始。
沒辦法只能先把導(dǎo)入的項目暫時下線斗遏，然后執(zhí)行kill命令將當(dāng)前的查詢都?xì)⑺肋M(jìn)程(因為只是客戶端Stop的話，MySQL服務(wù)端會繼續(xù)查詢)鞋邑。
然后我們開始分析這條命令執(zhí)行慢的原因：
1诵次、是否是聯(lián)合索引的問題
當(dāng)前是索引情況如下：
article表的主鍵是id，author_id是一個普通索引
content表的主鍵是article_id
所以認(rèn)為當(dāng)前是執(zhí)行流程枚碗，是先去article表的普通索引author_id里面找到1111的所有文章id逾一，然后根據(jù)這些文章id去article表的聚集索引中找到所有的文章，然后拿每個文章id去content表中找文章內(nèi)容等信息肮雨，然后判斷create_time是否滿足要求遵堵，進(jìn)行過濾，最終找到offset為20000后的100條數(shù)據(jù)。
所以我們就將article的author_id索引改成了聯(lián)合索引(author_id,create_time),這樣聯(lián)合索引(author_id,create_time)中的B+樹就是先安裝author_id排序陌宿，再按照create_time排序锡足，這樣一開始在聯(lián)合(author_id,create_time)查詢出來的文章id就是滿足create_time < '2020-04-29 00:00:00'條件的，后面就不用進(jìn)行過濾了壳坪，就不會就是符合就不用對create_time過濾舶得。
流程確實是這個流程，但是去查詢時爽蝴，如果limit還是210000, 100時沐批，還是查不出數(shù)據(jù)，幾分鐘都沒有數(shù)據(jù)蝎亚，一直到navica提示超時九孩，使用Explain看的話，確實命中索引了发框，如果將offset調(diào)小躺彬，調(diào)成6000, 100，勉強(qiáng)可以查出數(shù)據(jù)矫限，但是需要46s易稠，所以瓶頸不在這里。
真實原因如下：
先看關(guān)于深分頁的兩個查詢，id是主鍵俩檬，val是普通索引
2、直接查詢法
select * from test where val=4 limit 300000,5;
3构蹬、先查主鍵再join
select * from test a
inner join
(select id from test where val=4 limit 300000,5) as b
on a.id=b.id;
這兩個查詢的結(jié)果都是查詢出offset是30000后的5條數(shù)據(jù)碎乃，區(qū)別在于第一個查詢需要先去普通索引val中查詢出300005個id，然后去聚集索引下讀取300005個數(shù)據(jù)頁鳖枕，然后拋棄前面的300000個結(jié)果魄梯，只返回最后5個結(jié)果，過程中會產(chǎn)生了大量的隨機(jī)I/O宾符。
第二個查詢一開始在普通索引val下就只會讀取后5個id酿秸，然后去聚集索引下讀取5個數(shù)據(jù)頁。
同理我們業(yè)務(wù)中那條查詢其實是更加復(fù)雜的情況魏烫，因為我們業(yè)務(wù)的那條SQL不僅會讀取article表中的210100條結(jié)果辣苏，而且會每條結(jié)果去content表中查詢文章相關(guān)內(nèi)容，而這張表有幾個TEXT類型的字段哄褒，我們使用show table status命令查看表相關(guān)的信息發(fā)現(xiàn)
發(fā)現(xiàn)兩個表的數(shù)據(jù)量都是200多萬的量級稀蟋，article表的行平均長度是266，content表的平均長度是16847呐赡。
簡單來說是當(dāng) InnoDB 使用 Compact 或者 Redundant 格式存儲極長的 VARCHAR 或者 BLOB 這類大對象時退客，我們并不會直接將所有的內(nèi)容都存放在數(shù)據(jù)頁節(jié)點中，而是將行數(shù)據(jù)中的前 768 個字節(jié)存儲在數(shù)據(jù)頁中，后面會通過偏移量指向溢出頁萌狂。
這樣再從content表里面查詢連續(xù)的100行數(shù)據(jù)時档玻，讀取每行數(shù)據(jù)時，還需要去讀溢出頁的數(shù)據(jù)粥脚，這樣就需要大量隨機(jī)IO窃肠，因為機(jī)械硬盤的硬件特性，隨機(jī)IO會比順序IO慢很多刷允。所以我們后來又進(jìn)行了測試冤留，
只是從article表里面查詢limit 200000，100的數(shù)據(jù)树灶，發(fā)現(xiàn)即便存在深分頁的問題纤怒，查詢時間只是0.5s，因為article表的平均列長度是266天通，所有數(shù)據(jù)都存在數(shù)據(jù)頁節(jié)點中泊窘，不存在頁溢出，所以都是順序IO像寒，所以比較快烘豹。
//查詢時間0.51s
SELECT a.* FROM article a
WHERE a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
LIMIT 200100, 100
相反的，我們直接先找出100個article_id去content表里面查詢數(shù)據(jù)诺祸，發(fā)現(xiàn)比較慢携悯，第一次查詢時需要3s左右(也就是這些id的文章內(nèi)容相關(guān)的信息都沒有過，沒有緩存的情況)筷笨，第二次查詢時因為這些溢出頁數(shù)據(jù)已經(jīng)加載到buffer pool憔鬼，所以大概0.04s。
SELECT SQL_NO_CACHE c.*
FROM article_content c
WHERE c.article_id in(100個article_id)
4胃夏、解決方案
所以針對這個問題的解決方案主要有兩種：
（1）先查出主鍵id再inner join
非連續(xù)查詢的情況下轴或，也就是我們在查第100頁的數(shù)據(jù)時，不一定查了第99頁仰禀，也就是允許跳頁查詢的情況照雁。
那么就是使用先查主鍵再join這種方法對我們的業(yè)務(wù)SQL進(jìn)行改寫成下面這樣，下查詢出210000, 100時主鍵id答恶，作為臨時表temp_table囊榜，將article表與temp_table表進(jìn)行inner join，查詢出中文章相關(guān)的信息亥宿，并且去left Join content表查詢文章內(nèi)容相關(guān)的信息卸勺。
第一次查詢大概1.11s，后面每次查詢大概0.15s
SELECT
a.*, c.*
FROM article a
INNER JOIN(
SELECT id FROM article a
WHERE a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
LIMIT 210000 ,
100
) as temp_table ON a.id = temp_table.id
LEFT JOIN content c ON a.id = c.article_id
優(yōu)化結(jié)果
優(yōu)化前烫扼，offset達(dá)到20萬的量級時曙求，查詢時間過長，一直到超時。
優(yōu)化后悟狱，offset達(dá)到20萬的量級時静浴，查詢時間為1.11s。
（2）利用范圍查詢條件來限制取出的數(shù)據(jù)
這種方法的大致思路如下挤渐，假設(shè)要查詢test_table中offset為10000的后100條數(shù)據(jù)苹享，假設(shè)我們事先已知第10000條數(shù)據(jù)的id，值為min_id_value浴麻。
select * from test_table where id > min_id_value order by id limit 0, 100 得问，就是即利用條件id > min_id_value在掃描索引是跳過10000條記錄，然后取100條數(shù)據(jù)即可软免，這種處理方式的offset值便成為0了宫纬。
但此種方式有限制，必須知道offset對應(yīng)id膏萧，然后作為min_id_value漓骚，增加id > min_id_value的條件來進(jìn)行過濾，如果是用于分頁查找的話榛泛，也就是必須知道上一頁的最大的id蝌蹂，所以只能一頁一頁得查，不能跳頁曹锨，但是因為我們的業(yè)務(wù)需求就是每次100條數(shù)據(jù)孤个，進(jìn)行分批導(dǎo)數(shù)據(jù)，所以我們這種場景是可以使用艘希。
針對這種方法硼身，我們的業(yè)務(wù)SQL改寫如下：
//先查出最大和最小的id
SELECT min(a.id) as min_id , max(a.id) as max_id
FROM article a
WHERE a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
//然后每次循環(huán)查找
while(min_id<max_id) {
SELECT a.*, c.* FROM article a LEFT JOIN content c ON a.id = c.article_id WHERE a.author_id = 1111 AND a.id > min_id LIMIT 100
//這100條數(shù)據(jù)導(dǎo)入完畢后硅急，將100條數(shù)據(jù)數(shù)據(jù)中最大的id賦值給min_id覆享，以便導(dǎo)入下100條數(shù)據(jù)
}
優(yōu)化結(jié)果
優(yōu)化前，offset達(dá)到20萬的量級時营袜，查詢時間過長撒顿，一直到超時。
優(yōu)化后荚板，offset達(dá)到20萬的量級時凤壁，由于知道第20萬條數(shù)據(jù)的id，查詢時間為0.34s跪另。
聯(lián)合索引問題優(yōu)化
聯(lián)合索引其實有兩個作用：
1拧抖、充分利用where條件，縮小范圍
例如我們需要查詢以下語句：
SELECT * FROM test WHERE a = 1 AND b = 2
如果對字段a建立單列索引免绿，對b建立單列索引唧席，那么在查詢時，只能選擇走索引a，查詢所有a=1的主鍵id淌哟，然后進(jìn)行回表迹卢，在回表的過程中，在聚集索引中讀取每一行數(shù)據(jù)徒仓，然后過濾出b = 2結(jié)果集腐碱，或者走索引b，也是這樣的過程掉弛。
如果對a症见，b建立了聯(lián)合索引(a,b),那么在查詢時，直接在聯(lián)合索引中先查到a=1的節(jié)點狰晚，然后根據(jù)b=2繼續(xù)往下查筒饰，查出符合條件的結(jié)果集，進(jìn)行回表壁晒。
2瓷们、避免回表（此時也叫覆蓋索引）
這種情況就是假如我們只查詢某幾個常用字段，例如查詢a和b如下：
SELECT a,b FROM test WHERE a = 1 AND b = 2
對字段a建立單列索引秒咐，對b建立單列索引就需要像上面所說的谬晕，查到符合條件的主鍵id集合后需要去聚集索引下回表查詢，但是如果我們要查詢的字段本身在聯(lián)合索引中就都包含了携取，那么就不用回表了攒钳。
3、減少需要回表的數(shù)據(jù)的行數(shù)
這種情況就是假如我們需要查詢a>1并且b=2的數(shù)據(jù)
SELECT * FROM test WHERE a > 1 AND b = 2
如果建立的是單列索引a雷滋，那么在查詢時會在單列索引a中把a(bǔ)>1的主鍵id全部查找出來然后進(jìn)行回表不撑。
如果建立的是聯(lián)合索引(a,b),基于最左前綴匹配原則，因為a的查詢條件是一個范圍查找(=或者in之外的查詢條件都是范圍查找)晤斩，這樣雖然在聯(lián)合索引中查詢時只能命中索引a的部分焕檬，b的部分命中不了，只能根據(jù)a>1進(jìn)行查詢澳泵，但是由于聯(lián)合索引中每個葉子節(jié)點包含b的信息实愚，在查詢出所有a>1的主鍵id時，也會對b=2進(jìn)行篩選兔辅，這樣需要回表的主鍵id就只有a>1并且b=2這部分了腊敲，所以回表的數(shù)據(jù)量會變小。
我們業(yè)務(wù)中碰到的就是第3種情況维苔，我們的業(yè)務(wù)SQL本來更加復(fù)雜碰辅，還會join其他表，但是由于優(yōu)化的瓶頸在于建立聯(lián)合索引介时，所以進(jìn)行了一些簡化没宾，下面是簡化后的SQL：
SELECT
a.id as article_id ,
a.title as title ,
a.author_id as author_id
from
article a
where
a.create_time between '2020-03-29 03:00:00.003'
and '2020-04-29 03:00:00.003'
and a.status = 1
我們的需求其實就是從article表中查詢出最近一個月忍法，status為1的文章，我們本來就是針對create_time建了單列索引榕吼，結(jié)果在慢查詢?nèi)罩局邪l(fā)現(xiàn)了這條語句饿序，查詢時間需要0.91s左右，所以開始嘗試著進(jìn)行優(yōu)化羹蚣。
為了便于測試原探，我們在表中分別對create_time建立了單列索引create_time，對(create_time,status)建立聯(lián)合索引idx_createTime_status顽素。
強(qiáng)制使用idx_createTime進(jìn)行查詢
SELECT
a.id as article_id ,
a.title as title ,
a.author_id as author_id
from
article a FORCE INDEX(idx_createTime)
where
a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1
強(qiáng)制使用idx_createTime_status進(jìn)行查詢（即使不強(qiáng)制也是會選擇這個索引）
SELECT
a.id as article_id ,
a.title as title ,
a.author_id as author_id
from
article a FORCE INDEX(idx_createTime_status)
where
a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1
優(yōu)化結(jié)果：
優(yōu)化前使用idx_createTime單列索引咽弦，查詢時間為0.91s
優(yōu)化前使用idx_createTime_status聯(lián)合索引，查詢時間為0.21s
EXPLAIN的結(jié)果如下：
idtypekeykey_lenrowsfilteredExtra1rangeidx_createTime431160825.00Using index condition; Using where2rangeidx_createTime_status6310812100.00Using index condition
4胁出、原理分析
先介紹一下EXPLAIN中Extra列的各種取值的含義
Using filesort
當(dāng)Query 中包含 ORDER BY 操作型型，而且無法利用索引完成排序操作的時候，MySQL Query Optimizer 不得不選擇相應(yīng)的排序算法來實現(xiàn)全蝶。數(shù)據(jù)較少時從內(nèi)存排序闹蒜，否則從磁盤排序。Explain不會顯示的告訴客戶端用哪種排序抑淫。
Using index
僅使用索引樹中的信息從表中檢索列信息绷落，而不需要進(jìn)行附加搜索來讀取實際行(使用二級覆蓋索引即可獲取數(shù)據(jù))。當(dāng)查詢僅使用作為單個索引的一部分的列時始苇，可以使用此策略砌烁。
Using temporary
要解決查詢，MySQL需要創(chuàng)建一個臨時表來保存結(jié)果催式。如果查詢包含不同列的GROUP BY和ORDER BY子句函喉，則通常會發(fā)生這種情況。
官方解釋：”為了解決查詢荣月，MySQL需要創(chuàng)建一個臨時表來容納結(jié)果管呵。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時。很明顯就是通過where條件一次性檢索出來的結(jié)果集太大了喉童，內(nèi)存放不下了撇寞，只能通過加臨時表來輔助處理顿天。
Using where
表示當(dāng)where過濾條件中的字段無索引時堂氯，MySQL Sever層接收到存儲引擎(例如innodb)的結(jié)果集后，根據(jù)where條件中的條件進(jìn)行過濾牌废。
Using index condition
Using index condition 會先條件過濾索引咽白，過濾完索引后找到所有符合索引條件的數(shù)據(jù)行，隨后用 WHERE 子句中的其他條件去過濾這些數(shù)據(jù)行鸟缕；
我們的實際案例中晶框，其實就是走單個索引idx_createTime時排抬，只能從索引中查出 滿足a.create_time between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'條件的主鍵id，然后進(jìn)行回表授段，因為idx_createTime索引中沒有status的信息蹲蒲，只能回表后查出所有的主鍵id對應(yīng)的行。
然后innodb將結(jié)果集返回給MySQL Sever侵贵，MySQL Sever根據(jù)status字段進(jìn)行過濾届搁，篩選出status為1的字段，所以第一個查詢的Explain結(jié)果中的Extra才會顯示Using where窍育。
filtered字段表示存儲引擎返回的數(shù)據(jù)在server層過濾后卡睦，剩下多少滿足查詢的記錄數(shù)量的比例，這個是預(yù)估值漱抓，因為status取值是null表锻，1，2乞娄，3瞬逊，4，所以這里給的25%仪或。
所以第二個查詢與第一個查詢的區(qū)別主要在于一開始去idx_createTime_status查到的結(jié)果集就是滿足status是1的id码耐，所以去聚集索引下進(jìn)行回表查詢時，掃描的行數(shù)會少很多（大概是2.7萬行與15萬行的區(qū)別）溶其。
之后innodb返回給MySQL Server的數(shù)據(jù)就是滿足條件status是1的結(jié)果集（2.7萬行）骚腥，不用再進(jìn)行篩選了，所以第二個查詢才會快這么多瓶逃，時間是優(yōu)化前的23%束铭。
兩種查詢方式的EXPLAIN預(yù)估掃描行數(shù)都是30萬行左右是因為idx_createTime_status只命中了createTime，因為createTime不是查單個值厢绝，查的是范圍契沫。
//查詢結(jié)果行數(shù)是15萬行左右
SELECT count(*) from article a
where a.post_time
between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'
?
//查詢結(jié)果行數(shù)是2萬6行左右
SELECT count(*) from article a
where a.post_time
between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'
and a.audit_status = 1
發(fā)散思考：如果將聯(lián)合索引(createTime，status)改成(status昔汉，createTime)會怎么樣懈万？
where
a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1
根據(jù)最左匹配的原則，因為我們的where查詢條件是這樣靶病，如果是(createTime会通，status)那么索引就只能用到createTime；
如果是(status娄周，createTime)涕侈，因為status是查詢單個值，所以status煤辨，createTime都可以命中裳涛，在(status木张，createTime)索引中掃描行數(shù)會減少；
但是由于(createTime端三，status)這個索引本身值包含createTime舷礼，status，id三個字段的信息郊闯，數(shù)據(jù)量比較小且轨，而一個數(shù)據(jù)頁是16k，可以存儲1000個以上的索引數(shù)據(jù)節(jié)點虚婿，而且是查詢到createTime后旋奢，進(jìn)行的順序IO，所以讀取比較快然痊，總得的查詢時間兩者基本是一致至朗。
下面是測試結(jié)果：
首先創(chuàng)建了(status，createTime)名叫idx_status_createTime剧浸，
SELECT
a.id as article_id ,
a.title as title ,
a.author_id as author_id
from
article a FORCE INDEX(idx_status_createTime)
where
a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1
查詢時間是0.21锹引，跟第二種方式(createTime，status)索引的查詢時間基本一致唆香。
Explain結(jié)果對比：
掃描行數(shù)確實會少一些嫌变，因為在idx_status_createTime的索引中，一開始根據(jù)status = 1排除掉了status取值為其他值的情況躬它。
文源網(wǎng)絡(luò)腾啥，僅供學(xué)習(xí)之用，如有侵權(quán)冯吓，聯(lián)系刪除倘待。
我將面試題和答案都整理成了PDF文檔，還有一套學(xué)習(xí)資料组贺，涵蓋Java虛擬機(jī)凸舵、spring框架、Java線程失尖、數(shù)據(jù)結(jié)構(gòu)啊奄、設(shè)計模式等等，但不僅限于此掀潮。
關(guān)注公眾號【java圈子】獲取資料菇夸，還有優(yōu)質(zhì)文章每日送達(dá)。
file
