幾天前更過(guò)一篇技術(shù)筆記“MySQL全備份如何只恢復(fù)一個(gè)庫(kù)或者一個(gè)表？”

今日來(lái)講講 ：MySQL 查詢優(yōu)化之道

一、查詢優(yōu)化器模塊

查詢優(yōu)化器的任務(wù)是發(fā)現(xiàn)執(zhí)行 SQL 查詢的最佳方案。大多數(shù)查詢優(yōu)化器，要么基于規(guī)則、要么基于成本。

大多數(shù)查詢優(yōu)化器，包含 MySQL 的查詢優(yōu)化器，總或多或少地在所有可能的查詢?cè)u(píng)估方案中搜索最佳方案。

MySQL 中 MySQL Query Optimizer 是優(yōu)化器的核心，當(dāng) MySQL 數(shù)據(jù)拿到一個(gè) Query 語(yǔ)句之后會(huì)交給 Query Optimizer 去解析，并產(chǎn)生一個(gè)最優(yōu)的執(zhí)行計(jì)劃（這個(gè)是 Optimizer 認(rèn)為是最優(yōu)的，但不一定是真正最優(yōu)的，就跟 Oracle 數(shù)據(jù)庫(kù)會(huì)估算錯(cuò) rows 一樣）。

然后數(shù)據(jù)庫(kù)按照這個(gè)執(zhí)行計(jì)劃去執(zhí)行查詢語(yǔ)句。

在 SQL 語(yǔ)句整個(gè)執(zhí)行過(guò)程中，Optimizer 是最耗時(shí)的，但是也有第三方工具為了提高性能繞開(kāi) MySQL 的 Query Optimizer 模塊，比如：handlersocket。

對(duì)于多表關(guān)聯(lián)查詢，MySQL 優(yōu)化器所查詢的可能方案數(shù)隨查詢中引用的表的數(shù)目成指數(shù)增長(zhǎng)。對(duì)于小數(shù)量的表，這不是一個(gè)問(wèn)題。

然而，當(dāng)提交的查詢需要的結(jié)果集很大時(shí)，查詢優(yōu)化所花的時(shí)間會(huì)很容易地成為服務(wù)器性能的瓶頸。

查詢優(yōu)化的一個(gè)更加靈活的方案時(shí)容許用戶控制優(yōu)化器詳細(xì)地搜索最佳查詢?cè)u(píng)估方案。一般思想是調(diào)查的方案越少，它編譯一個(gè)查詢所花費(fèi)的時(shí)間越少。

另外，由于優(yōu)化器跳過(guò)一些方案，它可能錯(cuò)過(guò)一個(gè)最佳方案。優(yōu)化器關(guān)于方案數(shù)量評(píng)估的行為可以通過(guò)兩個(gè)系統(tǒng)變量來(lái)控制：

optimizer_prune_level?變量告訴優(yōu)化器根據(jù)對(duì)每個(gè)表訪問(wèn)的行數(shù)的估計(jì)跳過(guò)一些方案。我們的試驗(yàn)顯示該類 “有根據(jù)的猜測(cè)” 很少錯(cuò)過(guò)最佳方案，并且可以大大降低查詢編輯次數(shù)。

這就是為什么默認(rèn)情況該選項(xiàng)為 on(optimizer_prune_level=1)。

然而，如果你認(rèn)為優(yōu)化器錯(cuò)過(guò)了一個(gè)更好的查詢方案，則該選項(xiàng)可以關(guān)閉(optimizer_prune_level=0)，風(fēng)險(xiǎn)是查詢編輯花費(fèi)的時(shí)間更長(zhǎng)。

請(qǐng)注意即使使用該啟發(fā)，優(yōu)化器仍然可以探測(cè)呈指數(shù)數(shù)目的方案。

timizer_search_depth 變量告訴優(yōu)化器對(duì)于每個(gè)未完成的 “未來(lái)的” 方案，應(yīng)查看多深，以評(píng)估是否應(yīng)對(duì)它進(jìn)一步擴(kuò)大。

optimizer_search_depth 值較小會(huì)使查詢編輯次數(shù)大大減小。

例如，如果optimizer_search_depth 接近于查詢中表的數(shù)量，對(duì) 12、13 或更多表的查詢很可能需要幾小時(shí)甚至幾天的時(shí)間來(lái)編譯。

同時(shí)，如果用 optimizer_search_depth 等于 3 或 4 編輯，對(duì)于同一個(gè)查詢，編譯器編譯時(shí)間可以少于 1 分鐘。

如果不能確定合理的 optimizer_search_depth 值，該變量可以設(shè)置為 0，告訴優(yōu)化器自動(dòng)確定該值。

二、查詢優(yōu)化的基本思路

不管做項(xiàng)目設(shè)計(jì)還是產(chǎn)品設(shè)計(jì)都需要先有思路，才能規(guī)避一些問(wèn)題。

當(dāng)然 MySQL 查詢優(yōu)化也需要研發(fā)或者 DBA 擁有一些思路，唯有思路指導(dǎo)書寫，才會(huì)更加合理。

1. 優(yōu)化更需要優(yōu)化的 Query 語(yǔ)句

應(yīng)該優(yōu)化并發(fā)高的 Query 語(yǔ)句，不至于高并發(fā)下，由于 SQL 導(dǎo)致應(yīng)用程序卡死，比如 php-fpm 的大量等待，而且一個(gè)高并發(fā)的 Query 語(yǔ)句，如果走錯(cuò)執(zhí)行計(jì)劃，本來(lái)只需要掃描幾百行，結(jié)果掃描了幾百萬(wàn)行，可能會(huì)有災(zāi)難性的后果，更加會(huì)導(dǎo)致業(yè)務(wù)卡頓，尤其是核心業(yè)務(wù)下出現(xiàn)的高并發(fā) Query 語(yǔ)句。

2. 查看執(zhí)行計(jì)劃調(diào)整 Query 語(yǔ)句

根據(jù) explain extended SQL 分析查詢語(yǔ)句，就能查看執(zhí)行計(jì)劃，這個(gè)時(shí)候需要關(guān)注執(zhí)行計(jì)劃中的一些要素：

id：查詢的序列化

select type

depent subquery：說(shuō)明該查詢是子查詢中的第一個(gè) Select, 依賴與外部查詢的結(jié)果集

PRIMARY：子查詢的最外層查詢，注意不是主鍵查詢

simple：除子查詢或者 UNION 之外的其它查詢

table：訪問(wèn)數(shù)據(jù)表的名稱，書寫 SQL 的人，需要明確此表是否是核心表、是否是大數(shù)據(jù)量表等

type 掃描方式

all：全表掃描

const：讀常量，且最多只有一條記錄匹配。由于是常量只需要讀一次

index：全索引掃描

eq_ref：最多只有一條匹配結(jié)果 通過(guò)主鍵和唯一索引來(lái)訪問(wèn)的

range：索引范圍掃描

possible_keys：該查詢可以利用到的索引有哪些

key：優(yōu)化器模塊選擇用了哪個(gè)索引，有索引不一定就會(huì)用到，看執(zhí)行計(jì)劃才知道用了哪個(gè)。

key_len：索引長(zhǎng)度

rows：返回的行數(shù)

extra：附加信息，比如 using filesort---> 說(shuō)明用了排序算法

filtered：列給出了一個(gè)百分比的值，這個(gè)百分比值和 rows 列的值一起使用，可以估計(jì)出那些將要和 QEP 中的前一個(gè)表進(jìn)行連接的行的數(shù)目。前一個(gè)表就是指 id 列的值比當(dāng)前表的 id 小的表。這一列只有在 EXPLAIN EXTENDED 語(yǔ)句中才會(huì)出現(xiàn)。

3. 學(xué)會(huì)查看性能損耗（cpu 消耗、io 消耗）

當(dāng)發(fā)現(xiàn)有慢 Query 語(yǔ)句時(shí)，需要定位到底是哪里慢，CPU 還是 IO 等：

mysql>set profiling=1;mysql>show profiles;mysql>show profile cpu,block io for query n;

三、查詢的基本原則

1. 永遠(yuǎn)用小結(jié)果集驅(qū)動(dòng)大結(jié)果集

做 join 查詢時(shí)，驅(qū)動(dòng)表，一定是條件限定后記錄較少的表。

MySQL 的 join 只有一種算法 nested loop 也就是程序中的 for 循環(huán)，通過(guò)嵌套循環(huán)實(shí)現(xiàn)，驅(qū)動(dòng)結(jié)果集越大，所需要循環(huán)的次數(shù)越多，訪問(wèn)被驅(qū)動(dòng)表的次數(shù)也越多。降低 IO 同時(shí)降低 CPU。

2. 只查詢需要的列

只查詢需要的列，可以讓 IO 降低，列和排序算法也有關(guān)系。

3. 僅僅使用最有效的過(guò)濾條件

前提是用 a 條件 查詢出結(jié)果 用 b 條件查詢出結(jié)果，a、b 都用查詢出結(jié)果，這三次結(jié)果都一樣。

到底是用 a 條件還是 b 條件，還是兩個(gè)條件都限定，只能看執(zhí)行計(jì)劃。

4. 盡量避免復(fù)雜的 join 和子查詢

5. 盡量在索引列上完成排序和查詢

在索引列上排序：索引列上是排好序的，不需要啟動(dòng)額外的排序的算法降低了 CPU 的損耗。

在索引列上查詢：降低了 IO 的損耗

創(chuàng)建索引，優(yōu)化器模塊并不一定會(huì)用，但可以 SQL 中加上 force index（強(qiáng)制走那個(gè)索引）

四、索引利弊及索引分類

萬(wàn)事萬(wàn)物都有利弊，一個(gè)東西的出現(xiàn)，比如會(huì)在不同場(chǎng)景下有好好壞，就看如何權(quán)衡。

好處：

通過(guò)索引列查詢數(shù)據(jù)，能夠提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫(kù)的 IO 成本。

通過(guò)索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了 CPU 的消耗。

壞處：

假設(shè)表 a 其中有列 column ca 給其創(chuàng)建索引 indxaca：

每次更新 ca 的操作，都會(huì)調(diào)整因?yàn)楦滤鶐?lái)的鍵值變化后的索引信息，這樣就會(huì)增加 IO 損耗，索引列也是要占用空間的，a 列數(shù)據(jù)的增多，indxaca 索引占用的空間也會(huì)不斷增長(zhǎng)。所以索引還會(huì)帶來(lái)存儲(chǔ)空間資源的消耗。

五、索引分類

b-tree 索引：根據(jù)平衡二叉樹(shù)演變來(lái)的

hash 索引：

hash 索引只能滿足 "="、"in" <> 查詢，不能支持范圍查詢

hash 索引無(wú)法被利用進(jìn)行排序操作

hash 索引不能利用部分索引鍵查詢

hash 索引不能避免表掃描

full-text 索引：只有 myisam 存儲(chǔ)引擎支持 ---> 只有 char 、varchar、text 支持，但是在 MySQL 5.7，innodb 存儲(chǔ)引擎也支持啦。

R-Tree 索引：主要解決空間數(shù)據(jù)檢索問(wèn)題，極少使用。

六、索引相關(guān)優(yōu)化

1. 如何判斷是否需要?jiǎng)?chuàng)建索引

頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引。

唯一性太差的字段不適合單獨(dú)創(chuàng)建索引。比如該字段重復(fù)上千萬(wàn)；即使你創(chuàng)建了索引優(yōu)化器模塊是不會(huì)選擇使用的；會(huì)有極大的性能問(wèn)題 有很多重復(fù)值，會(huì)帶來(lái)大量的隨機(jī) IO 甚至是重復(fù) IO。

更新非常頻繁的字段不適合創(chuàng)建索引：不僅僅更新表中的數(shù)據(jù)，還需要更新索引數(shù)據(jù) IO 訪問(wèn)增大。

不會(huì)出現(xiàn)在 where 字句中的字段不該創(chuàng)建索引。

單鍵索引還是組合索引。

2. MySQL 中索引的限制

是否用到了索引可以查看執(zhí)行計(jì)劃

在任何索引列上做計(jì)算、函數(shù)、類型轉(zhuǎn)換（哪怕是自動(dòng)的）都會(huì)使得索引失效而轉(zhuǎn)向全表掃描操作：不要在索引列上做任何操作因?yàn)榭赡転閷?dǎo)致索引失效。

MySQL 在使用不等于 (!= or <>) 的時(shí)候無(wú)法使用索引會(huì)導(dǎo)致全表掃描。

is null ,is not null 也無(wú)法使用索引。

join 語(yǔ)句中 join 條件字段類型不一致的時(shí)候 MySQL 無(wú)法使用索引。

模糊查詢的時(shí)候 (like 操作) 如果以通配符開(kāi)頭 ('%abc...')MySQL 索引失效會(huì)變成全表掃描的操作。

如果使用的是 hash 索引，在做非等值連接時(shí)候無(wú)法使用索引，會(huì)是全表掃描的操作。

在 MySQL 中 BLOB 和 Text 類型的列只能創(chuàng)建前綴索引。

MyISAM 存儲(chǔ)引擎的話索引鍵長(zhǎng)度總和不能超過(guò) 1000 字節(jié)。（好像從 5.7 之后，大多默認(rèn) innodb 存儲(chǔ)引擎）

當(dāng)有唯一性索引和非唯一性索引都存在時(shí)，往往只會(huì)選擇唯一性索引。

組合索引，查詢時(shí)組合索引第一列出現(xiàn)的時(shí)候會(huì)使用索引。

3. 使用索引的一些建議

對(duì)于單鍵索引，盡量選擇針對(duì)當(dāng)前 Query 過(guò)濾性更好的索引。

在選擇組合索引的時(shí)候，當(dāng)前 Query 中過(guò)濾性最好的字段在索引字段順序中，位置越靠前越好。

在選擇組合索引的時(shí)候，盡量選擇可以能夠包含當(dāng)前 Query 中的 where 字句中更多字段的索引。

盡可能通過(guò)分析統(tǒng)計(jì)信息和調(diào)整 Query 的寫法來(lái)達(dá)到選擇合適索引的目的。減少通過(guò)使用 Hint 認(rèn)為控制索引的選擇，如果使用 Hint 會(huì)使得后期維護(hù)成本比較高。

綜上所述，大致簡(jiǎn)單明了的闡述了 MySQL 查詢優(yōu)化一些相關(guān)的東西，至少對(duì)于中小型企業(yè)，可以作為研發(fā)人員的數(shù)據(jù)庫(kù)規(guī)范，避免后期遷移或擴(kuò)容時(shí)的一些問(wèn)題。一切相關(guān)問(wèn)題可以在讀者圈交流，謝謝翻到文末的眾人。

來(lái)源：科多大數(shù)據(jù)