1. 關(guān)聯(lián)查詢優(yōu)化

1.1 驅(qū)動(dòng)表和被驅(qū)動(dòng)表

對于內(nèi)連接來說，優(yōu)化器會(huì)根據(jù)用戶的查詢語句做優(yōu)化，決定先查哪張表。先查詢的那張表就是驅(qū)動(dòng)表，反之就是被驅(qū)動(dòng)表。而對于外連接來說，大多數(shù)情況用戶指定的主表就是驅(qū)動(dòng)表，但優(yōu)化器也會(huì)視情況進(jìn)行選擇。

1.2 Simple Nested-Loop Join (簡單嵌套循環(huán)連接)

從表A中取出一條數(shù)據(jù)，遍歷表B，將匹配到的數(shù)據(jù)放到result… 以此類推，驅(qū)動(dòng)表A中的每一條記錄與被驅(qū)動(dòng)表B的記錄進(jìn)行判斷?？梢钥吹竭@種方式效率是非常低的，以上述表A數(shù)據(jù)100條，表B數(shù)據(jù)1000條計(jì)算，則A*B=10萬次。

1.3 Index Nested-Loop Join （索引嵌套循環(huán)連接）

其優(yōu)化的思路主要是為了減少內(nèi)存表數(shù)據(jù)的匹配次數(shù)，所以要求被驅(qū)動(dòng)表上必須有索引才行。通過外層表匹配條件直接與內(nèi)層表索引進(jìn)行匹配，避免和內(nèi)存表的每條記錄去進(jìn)行比較，這樣極大的減少了對內(nèi)存表的匹配次數(shù)。

驅(qū)動(dòng)表中的每條記錄通過被驅(qū)動(dòng)表的索引進(jìn)行訪問，因?yàn)樗饕樵兊某杀臼潜容^固定的，故mysql優(yōu)化器都傾向于使用記錄數(shù)少的表作為驅(qū)動(dòng)表（外表）。

如果被驅(qū)動(dòng)表加索引，效率是非常高的，但如果索引不是主鍵索引，所以還得進(jìn)行一次回表查詢。相比，被驅(qū)動(dòng)表的索引是主鍵索引，效率會(huì)更高。

1.4 Block Nested-Loop Join（塊嵌套循環(huán)連接）

其優(yōu)化思路為一次性緩存多條數(shù)據(jù)，把參與查詢的列緩存到Join Buffer 里，然后拿join buffer里的數(shù)據(jù)批量與內(nèi)層表的數(shù)據(jù)進(jìn)行匹配，從而減少了內(nèi)層循環(huán)的次數(shù)（遍歷一次內(nèi)層表就可以批量匹配一次Join Buffer里面的外層表數(shù)據(jù)）。當(dāng)不使用Index Nested-Loop Join的時(shí)候，默認(rèn)使用Block Nested-Loop Join。

1.5 總結(jié)

（1）整體效率比較：INLJ > BNLJ > SNLJ

（2）永遠(yuǎn)用小結(jié)果集驅(qū)動(dòng)大結(jié)果集（其本質(zhì)就是減少外層循環(huán)的數(shù)據(jù)數(shù)量）（小的度量單位指的是表行數(shù) * 每行大小）

（3）為被驅(qū)動(dòng)表匹配的條件增加索引(減少內(nèi)存表的循環(huán)匹配次數(shù))

（4）增大join buffer size的大?。ㄒ淮嗡饕臄?shù)據(jù)越多，那么內(nèi)層包的掃描次數(shù)就越少）

（5）減少驅(qū)動(dòng)表不必要的字段查詢（字段越少，join buffer所緩存的數(shù)據(jù)就越多）

（6）從MySQL的8.0.20版本開始將廢棄BNLJ，因?yàn)閺腗ySQL8.0.18版本開始就加入了hash join默認(rèn)都會(huì)使用hash join

1.6 優(yōu)化思路

• 保證被驅(qū)動(dòng)表的JOIN字段已經(jīng)創(chuàng)建了索引
• 需要JOIN 的字段，數(shù)據(jù)類型保持絕對一致。
• LEFT JOIN 時(shí)，選擇小表作為驅(qū)動(dòng)表， 大表作為被驅(qū)動(dòng)表 。減少外層循環(huán)的次數(shù)。
• INNER JOIN 時(shí)，MySQL會(huì)自動(dòng)將 小結(jié)果集的表選為驅(qū)動(dòng)表 。選擇相信MySQL優(yōu)化策略。
• 能夠直接多表關(guān)聯(lián)的盡量直接關(guān)聯(lián)，不用子查詢。(減少查詢的趟數(shù))
• 不建議使用子查詢，建議將子查詢SQL拆開結(jié)合程序多次查詢，或使用 JOIN 來代替子查詢。

2. 子查詢優(yōu)化

子查詢是 MySQL 的一項(xiàng)重要的功能，可以幫助我們通過一個(gè) SQL 語句實(shí)現(xiàn)比較復(fù)雜的查詢。但是，子查詢的執(zhí)行效率不高。原因：

① 執(zhí)行子查詢時(shí)，MySQL需要為內(nèi)層查詢語句的查詢結(jié)果 建立一個(gè)臨時(shí)表 ，然后外層查詢語句從臨時(shí)表 中查詢記錄。查詢完畢后，再 撤銷這些臨時(shí)表 。這樣會(huì)消耗過多的CPU和IO資源，產(chǎn)生大量的慢查詢。

② 子查詢的結(jié)果集存儲(chǔ)的臨時(shí)表，不論是內(nèi)存臨時(shí)表還是磁盤臨時(shí)表都 不會(huì)存在索引 ，所以查詢性能會(huì) 受到一定的影響。

③ 對于返回結(jié)果集比較大的子查詢，其對查詢性能的影響也就越大。

在MySQL中，可以使用連接（JOIN）查詢來替代子查詢。連接查詢不需要建立臨時(shí)表，其速度比子查詢要快 ，如果查詢中使用索引的話，性能就會(huì)更好。

3. 排序優(yōu)化

在MySQL中，支持兩種排序方式，分別是FileSort和Index排序。Index 排序中，索引可以保證數(shù)據(jù)的有序性，不需要再進(jìn)行排序，效率更高。FileSort 排序則一般在內(nèi)存中進(jìn)行排序，占用CPU較多。如果待排結(jié)果較大，會(huì)產(chǎn)生臨時(shí)文件 I/O 到磁盤進(jìn)行排序的情況，效率較低。

優(yōu)化思路：

（1） SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表掃描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。當(dāng)然，某些情況下全表掃描，或者 FileSort 排序不一定比索引慢。但總的來說，我們還是要避免，以提高查詢效率。

（2）盡量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用單索引列； 如果不同就使用聯(lián)合索引。

（3）無法使用 Index 時(shí)，需要對 FileSort 方式進(jìn)行調(diào)優(yōu)。

4. GROUP BY優(yōu)化

• group by 使用索引的原則幾乎跟order by一致 ，group by 即使沒有過濾條件用到索引，也可以直接使用索引。
• group by 先排序再分組，遵照索引建的最佳左前綴法則
• 當(dāng)無法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 參數(shù)的設(shè)置
• where效率高于having，能寫在where限定的條件就不要寫在having中了
• 減少使用order by，和業(yè)務(wù)溝通能不排序就不排序，或?qū)⑴判蚍诺匠绦蚨巳プ?。Order by、group by、distinct這些語句較為耗費(fèi)CPU，數(shù)據(jù)庫的CPU資源是極其寶貴的。
• 包含了order by、group by、distinct這些查詢的語句，where條件過濾出來的結(jié)果集請保持在1000行 以內(nèi)，否則SQL會(huì)很慢。

5. 分頁查詢優(yōu)化

優(yōu)化思路一
在索引上完成排序分頁操作，最后根據(jù)主鍵關(guān)聯(lián)回原表查詢所需要的其他列內(nèi)容。

優(yōu)化思路二
該方案適用于主鍵自增的表，可以把Limit 查詢轉(zhuǎn)換成某個(gè)位置的查詢 。例如：

--優(yōu)化前
SELECT * FROM student LIMIT 2000000,10;
--優(yōu)化后
SELECT * FROM student WHERE id > 2000000 LIMIT 10;

6. 優(yōu)先使用覆蓋索引

索引是高效找到行的一個(gè)方法，但是一般數(shù)據(jù)庫也能使用索引找到一個(gè)列的數(shù)據(jù)，因此它不必讀取整個(gè)行。畢竟索引葉子節(jié)點(diǎn)存儲(chǔ)了它們索引的數(shù)據(jù)；當(dāng)能通過讀取索引就可以得到想要的數(shù)據(jù)，那就不需要讀取行了。一個(gè)索引包含了滿足查詢結(jié)果的數(shù)據(jù)就叫做覆蓋索引。

正因?yàn)楦采w索引包含了滿足查詢結(jié)果的所有數(shù)據(jù)，因此就可以省去回表的操作，從而大大提升效率。

7. 索引下推ICP

索引下推(Index Condition Pushdown, ICP)是MySQL 5.6中新特性，是一種在存儲(chǔ)引擎層使用索引過濾數(shù)據(jù)的一種優(yōu)化方式。當(dāng)使用索引條件下推是，EXPLAIN語句輸出結(jié)果中Extra列內(nèi)容顯示為Using index condition。

索引下推就是指在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)來提高查詢效率。

# 打開索引下推
SET optimizer_switch = 'index_condition_pushdown=on';

# 關(guān)閉索引下推
SET optimizer_switch = 'index_condition_pushdown=off';

ICP的使用條件

（1）如果表的訪問類型為 range 、 ref 、 eq_ref 或者 ref_or_null 可以使用ICP。
（2）ICP可以使用InnDB和MyISAM表，包括分區(qū)表InnoDB和MyISAM表
（3） 對于InnoDB表，ICP僅用于二級(jí)索引。ICP的目標(biāo)是減少全行讀取次數(shù)，從而減少I/O操作。
（4）當(dāng)SQL使用覆蓋索引時(shí)，不支持ICP優(yōu)化方法。因?yàn)檫@種情況下使用ICP不會(huì)減少I/O。
5. 相關(guān)子查詢的條件不能使用ICP文章來源地址http://www.zghlxwxcb.cn/news/detail-800485.html

到了這里，關(guān)于SQL筆記 -- 查詢優(yōu)化的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！