目錄

• • 要不要用join
  • Join驅(qū)動(dòng)表選擇
  • Multi-Range Read優(yōu)化
  • Batched Key Access （BKA）對(duì)NLJ進(jìn)行優(yōu)化
  • BNL算法性能問題
  • BNL轉(zhuǎn)BKA

要不要用join

1、如果使用的是Index Nested-Loop Join算法，即可以用上被驅(qū)動(dòng)表的索引，可以用

2、如果使用的是Block Nested-Loop Join算法。掃描行數(shù)過多，尤其是大表join會(huì)導(dǎo)致掃描多次被驅(qū)動(dòng)表，會(huì)占用大量系統(tǒng)資源，這種Join盡量不要用

Join驅(qū)動(dòng)表選擇

1、如果是Index Nested-Loop Join算法，使用小表做驅(qū)動(dòng)表

2、如果是Block Nested-Loop Join算法，在 join_buffer_size 足夠大，大表小表一樣，當(dāng) join_buffer_size 不夠大時(shí)，選擇小表做驅(qū)動(dòng)表

注意，在決定哪個(gè)表做驅(qū)動(dòng)表時(shí)，應(yīng)該是兩個(gè)表按照各自條件過濾完成之后，計(jì)算參與join的各個(gè)字段的總數(shù)據(jù)量，數(shù)據(jù)量小的表，那就是小表。

Multi-Range Read優(yōu)化

若有這樣查詢語(yǔ)句：

select * from t1 where a>=1 and a<=100;

a值是遞增的，但是回表后的id并非如此，而是隨機(jī)的，會(huì)帶來性能損失。

大多數(shù)數(shù)據(jù)按照主鍵遞增順序插入得到，所以我們可以認(rèn)為如果按照主鍵的遞增順序查找的話，對(duì)磁盤的讀比較接近順序讀，從而可以提升讀性能。

1、根據(jù)索引a，定位到滿足條件的記錄，將id值放入read_rnd_buffer中；

2、將read_rnd_buffer中的id進(jìn)行遞增排序；

3、排序后的id數(shù)組，依次到主鍵id索引中查找記錄，并作為結(jié)果返回

總的來說就是：**先將索引數(shù)據(jù)緩存，查到id之后，排序之后再回表 **

用法：

設(shè)置：

set optimizer_switch="mrr_cost_based=off

現(xiàn)在的優(yōu)化器在判斷消耗時(shí)，更傾向于不使用MRR，所以需要設(shè)置為off后，就會(huì)固定使用MRR

Batched Key Access （BKA）對(duì)NLJ進(jìn)行優(yōu)化

Index Nested-Loop Join執(zhí)行邏輯是：從驅(qū)動(dòng)表t1，一行行取出a值，再到驅(qū)動(dòng)表t2去做join。對(duì)于表t2來說，每次都是匹配一個(gè)值，MMR優(yōu)勢(shì)用不上。

既然這樣，將表t1的數(shù)據(jù)取出來一部分，先放到一個(gè)臨時(shí)內(nèi)存里：join_buffer.

然后在此基礎(chǔ)上復(fù)用MRR即可。

使用方法：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

BNL算法性能問題

之前提到過InnoDB的LRU優(yōu)化：第一次從磁盤讀入內(nèi)存的數(shù)據(jù)頁(yè)，會(huì)先放到old區(qū)域，如果1s后這個(gè)數(shù)據(jù)頁(yè)不再被訪問，就不會(huì)移動(dòng)到LRU鏈表頭部，這樣對(duì)Buffer Pool命中率影響就不大了。

如果使用了BNL的join語(yǔ)句，多次掃描一個(gè)冷表，并且這個(gè)語(yǔ)句執(zhí)行時(shí)間超過1s，就會(huì)在再次掃描冷表時(shí)，把冷表的數(shù)據(jù)頁(yè)移動(dòng)到LRU鏈表頭部。

如果冷表數(shù)據(jù)很大， 會(huì)一直占據(jù)old區(qū)，正常頁(yè)無法進(jìn)入，無法更新young區(qū)
tips: 冷表，指表中數(shù)據(jù)還沒有加載到bufferpool中，需要先從盤里讀出來的表
又因?yàn)閮?yōu)化機(jī)制，一個(gè)正常訪問的數(shù)據(jù)頁(yè)要進(jìn)入young區(qū)域，需要隔1s再次被訪問到。由于join’語(yǔ)句在循環(huán)都磁盤和淘汰內(nèi)存頁(yè)，進(jìn)入old區(qū)域的數(shù)據(jù)頁(yè)很可能在1s之內(nèi)就被淘汰了。

大表join后對(duì)于Buffer Pool的影響是持續(xù)性的，需要依靠后續(xù)的查詢請(qǐng)求慢慢恢復(fù)內(nèi)存命中率。

總結(jié)，BNL對(duì)于系統(tǒng)的影響：

1、可能多次掃描被驅(qū)動(dòng)表，占用磁盤IO資源

2、判斷join條件執(zhí)行M * N次，占用CPU資源

3、可能導(dǎo)致Buffer Pool的熱數(shù)據(jù)被淘汰，影響內(nèi)存命中率

所以我們需要優(yōu)化BNL，通過給驅(qū)動(dòng)表的join字段加索引的方式，將BNL轉(zhuǎn)換為BKA

BNL轉(zhuǎn)BKA

對(duì)于一些不常執(zhí)行大表join的sql，不在被驅(qū)動(dòng)表上創(chuàng)建索引的情況，可以創(chuàng)建一個(gè)臨時(shí)表 create templete table在這個(gè)臨時(shí)表上創(chuàng)建索引，然后讓驅(qū)動(dòng)表與臨時(shí)表做join操作。 為什么不在被驅(qū)動(dòng)表上創(chuàng)建索引，是因?yàn)檫@塊sql功能不常用，創(chuàng)建索引浪費(fèi)空間，并且可能觸發(fā)這塊的join sql 也不經(jīng)常調(diào)用。

創(chuàng)建臨時(shí)表以及join語(yǔ)句示例如下：

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb; insert into temp_t select * from t2 where b>=1 and b<=2000; select * from t1 join temp_t on (t1.b=temp_t.b);

總結(jié)

以上是生活随笔為你收集整理的《MySQL——join语句优化tips》的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。