文章有點(diǎn)長(zhǎng)，請(qǐng)各位看官按下耐心，一定看下去，雖然數(shù)據(jù)庫(kù)這塊的內(nèi)容很枯燥，但是一定得保證自己全部都掌握，才能拿到一個(gè)很好的Offer，不是么?

大部分人說的SQL優(yōu)化

阿粉之前幫公司面試過一部分人，因?yàn)橹昂屠洗笠黄鹈嬖嚕砸部催^不少人的簡(jiǎn)歷，而簡(jiǎn)歷上對(duì)于數(shù)據(jù)庫(kù)這塊內(nèi)容，很多人都寫的是這個(gè)樣子的。

• 熟練使用 MySQL，SQLServer，熟悉Oracle，熟悉SQL語(yǔ)句優(yōu)化。

確實(shí)，如果你對(duì)數(shù)據(jù)庫(kù)沒有進(jìn)行過深入的了解的時(shí)候，你也不敢往上面寫，只能是去寫關(guān)于SQL語(yǔ)句優(yōu)化方面的，但是很多時(shí)候，在你寫完這個(gè)SQL語(yǔ)句之后，你是壓根不會(huì)去主動(dòng)的去優(yōu)化的，很多時(shí)候都是停在了，不出問題，我就不改，只要功能實(shí)現(xiàn)了，那么，一切萬(wàn)事大吉。

而本文，告訴你，不要只是會(huì)SQL語(yǔ)句優(yōu)化。

SQL語(yǔ)句優(yōu)化我們?cè)诿嬖嚨臅r(shí)候，面試官看你寫了SQL語(yǔ)句優(yōu)化，有時(shí)候就會(huì)提問了，那你說說SQL語(yǔ)句優(yōu)化都是在哪些方面的吧。

[[381723]]

關(guān)于SQL語(yǔ)句優(yōu)化，內(nèi)容可就比較多了。

• 對(duì)查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。
• 盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。
• 應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。

。。。。。

類似這樣的SQL語(yǔ)句優(yōu)化還有很多很多，但是大家有沒有注意到我在上面標(biāo)注出來的內(nèi)容，引擎? 相信大家之前肯定也都知道，但是沒有做過多的研究，但是為了各位面試順利，我們就來加緊分析這個(gè)吧。

MySQL體系以下全文，全部按照MySQL來進(jìn)行分析，分析引擎，我們先從MySQL分析吧。MySQL的體系結(jié)構(gòu)圖如下：

我們能從圖中看出一點(diǎn)內(nèi)容，比如說MySQL的組成部分。

• 連接池組件
• 管理服務(wù)和工具組件
• SQL接口組件
• 查詢分析器組件
• 優(yōu)化器組件
• 緩沖(Cache)組件
• 插件式存儲(chǔ)引擎
• 物理文件

不得不說，這個(gè)插件式存儲(chǔ)引擎總結(jié)的是極其精辟的呀，MySQL數(shù)據(jù)庫(kù)區(qū)別于其他數(shù)據(jù)庫(kù)的最重要的一個(gè)特點(diǎn)就是其插件式的表存儲(chǔ)引擎。

但是我們要注意一個(gè)最重要的，那就是存儲(chǔ)引擎是基于表的，而不是數(shù)據(jù)庫(kù)。

MySQL存儲(chǔ)引擎

存儲(chǔ)引擎是MySQL區(qū)別于其他數(shù)據(jù)庫(kù)的一個(gè)最重要特性，每個(gè)存儲(chǔ)引擎都有他們自己的特點(diǎn)，不同的特點(diǎn)，會(huì)在不同的場(chǎng)景下使用，雖然我們?cè)陂_發(fā)中很多時(shí)候都是使用的一種，但是能夠根據(jù)具體的應(yīng)用建立不同存儲(chǔ)引擎表，這才是最牛逼的吧。

那么我們看看MySQL都支持哪些存儲(chǔ)引擎把，手動(dòng)在自己的MySQL中輸入查詢語(yǔ)句 show engines,就會(huì)如下圖所示。

大家看完是不是感覺還挺多，但是MySQL的9種存儲(chǔ)引擎都是各自是各自的特點(diǎn)，然后根據(jù)需求的不同，我們?cè)诮ū淼臅r(shí)候可以選擇一下，是不是發(fā)現(xiàn)了又一個(gè)帶妹的好機(jī)會(huì)，那我們就開始分析一下吧。

1.FEDERATED存儲(chǔ)引擎

在阿粉之前翻看書籍的時(shí)候就看到過這個(gè)，說這個(gè)引擎不是存放數(shù)據(jù)的引擎，而是一個(gè)指向遠(yuǎn)程MySQL數(shù)據(jù)庫(kù)服務(wù)器的，那是什么意思呢，其實(shí)一句大白話：“我這里不存表結(jié)構(gòu)文件和數(shù)據(jù)文件，我是在遠(yuǎn)程端存的”，這時(shí)候就有了個(gè)比較有意思的地方，如下圖：

就像圖上說的，F(xiàn)EDERATED存儲(chǔ)引擎分成了2部分，一部分是本地服務(wù)，另外一部分就是遠(yuǎn)程服務(wù)，那么如果在你切換到這個(gè)引擎的時(shí)候，他在執(zhí)行CRUD的時(shí)候，就會(huì)把執(zhí)行操作的命令發(fā)到遠(yuǎn)程服務(wù)器，然后執(zhí)行完了之后，在發(fā)回本地，然后從本地服務(wù)器中返回匹配的行即可。

在這里，阿粉不去著重的講這個(gè)，因?yàn)橹攸c(diǎn)是我們最常使用的那個(gè)，大家肯定都知道，那就是InnoDB存儲(chǔ)引擎。

2.InnoDB存儲(chǔ)引擎

InnoDB存儲(chǔ)引擎這個(gè)一般也是大家在面試中經(jīng)常的能和面試官侃侃而談的一個(gè)，因?yàn)樗悄J(rèn)的數(shù)據(jù)庫(kù)存儲(chǔ)引擎。注意，這個(gè)默認(rèn)是從MySQL的5.5.8開始的

特點(diǎn)比較多，下面就開始分析一下都有哪些特點(diǎn)吧。

• 支持事務(wù)。默認(rèn)的事務(wù)隔離級(jí)別為可重復(fù)，這個(gè)我們經(jīng)常用，所以肯定也都知道。
• 支持外鍵，這個(gè)外鍵大家肯定也都清楚，有利有弊，畢竟外鍵的作用在那里放著(利：增加可讀性，若出現(xiàn)宕機(jī)，最大限度的保證數(shù)據(jù)的一致性和完整性，弊：降低了表的查詢速度，如果數(shù)據(jù)了過大，那么你插入數(shù)據(jù)庫(kù)數(shù)據(jù)的時(shí)長(zhǎng)可能是不增加外鍵的十倍)
• 行鎖設(shè)計(jì)，這樣可以支持更高的并發(fā)，這也是為什么有時(shí)候面試官說你們上ES有點(diǎn)大材小用，因?yàn)镸ySQL自己也能處理那么多。
• 使用多版本并發(fā)控制(MVCC)來獲得高并發(fā)性，并且實(shí)現(xiàn)了SQL標(biāo)準(zhǔn)的4種隔離級(jí)別，默認(rèn)為REPEATABLE READ級(jí)別。
• 使用一種被稱為next-key locking(有人稱它為間隙鎖)的策略來避免幻讀(phantom)現(xiàn)象的產(chǎn)生
• 數(shù)據(jù)存儲(chǔ)采用了聚集(clustered)的方式，每張表的存儲(chǔ)都是按主鍵的順序進(jìn)行存放。
• InnoDB的索引結(jié)構(gòu)和MySQL其他的存儲(chǔ)引擎不同，聚簇索引對(duì)主鍵查詢性能非常高，這時(shí)候就得有個(gè)限制要求，如果表上的索引較多，主鍵就盡可能的小。
• InnoDB通過一些機(jī)制和工具都支持真正的熱備份，也就是在線熱備份。
• 數(shù)據(jù)存儲(chǔ)在表空間(tablespace)中，這個(gè)表空間實(shí)際上是由InnoDB管理的一個(gè)黑盒，由一系列的文件組成。

2.1InnoDB的體系架構(gòu)

我們從上面的圖中就能看到，InnoDB存儲(chǔ)引擎有許多的內(nèi)存塊，可以認(rèn)為這些內(nèi)存塊其實(shí)就相當(dāng)于是一個(gè)大的內(nèi)存池，就是線程池是類似的那種，

既然圖中我們看到了后臺(tái)的線程，那么我們也要說說這個(gè)后臺(tái)線程是個(gè)什么?

InnoDB存儲(chǔ)引擎是多線程的模型，因此其后臺(tái)有多個(gè)不同的后臺(tái)線程，負(fù)責(zé)處理不同的任務(wù)。

而這個(gè)后臺(tái)線程也是分兩個(gè)部分的，一個(gè)是核心線程，一個(gè)是IO線程。

• 核心線程Master Thread
• IO線程 IO Thread
• 凈化線程Purge Thread
• 清理線程Page Cleaner Thread

核心線程

核心線程的作用就是將緩沖池中的數(shù)據(jù)異步的刷新到磁盤上，來保證數(shù)據(jù)的一致性。

IO線程

IO線程則很簡(jiǎn)單，主要就是用來IO請(qǐng)求的回調(diào)處理。

凈化線程

主要作用就是事務(wù)提交之后回收已經(jīng)使用并分配的undo頁(yè)

清理線程

他的作用是將之前版本中臟頁(yè)的刷新操作都放入到單獨(dú)的線程中來完成。

如果阿粉當(dāng)時(shí)面試的時(shí)候能夠把這些內(nèi)容給面試官說上，相信工資肯定還能再多來點(diǎn)。

3.Memory 存儲(chǔ)引擎

Memory 存儲(chǔ)引擎實(shí)際上就是將表中的數(shù)據(jù)存放在內(nèi)存中，如果數(shù)據(jù)庫(kù)重啟或發(fā)生崩潰，表中的數(shù)據(jù)都將消失。也就是說，如果你們的數(shù)據(jù)存儲(chǔ)在Memory 存儲(chǔ)引擎中，如果機(jī)房不慎掉電，那完了，之前存在里面的東西都沒了，就和你用了個(gè) rm -rf效果一樣，不過一個(gè)是被動(dòng)的，一個(gè)是主動(dòng)的。

這種用的比較少，阿粉就不再多給大家介紹了，就說說它的特點(diǎn)得了，畢竟一個(gè)關(guān)機(jī)，直接就沒了。

• 不支持TEXT和BLOB類型，對(duì)于字符串類型的數(shù)據(jù)，只支持固定長(zhǎng)度的行，VARCHAR會(huì)被自動(dòng)存儲(chǔ)為CHAR類型;
• 速度非常快，只支持表鎖，并發(fā)性能較差，并且不支持TEXT和BLOB列類型
• 服務(wù)器一但出現(xiàn)宕機(jī)，所有數(shù)據(jù)全部消失
• 存儲(chǔ)變長(zhǎng)字段(varchar)時(shí)是按照定常字段(char)的方式進(jìn)行的，因此會(huì)浪費(fèi)內(nèi)存

4.MyISAM存儲(chǔ)引擎

這個(gè)引擎特點(diǎn)非常明顯，不支持事務(wù)，但是支持全文檢索，要面向一些OLAP(聯(lián)機(jī)分析處理)數(shù)據(jù)庫(kù)應(yīng)用.

5.BLACKHOLE存儲(chǔ)引擎

這個(gè)引擎就像他的名字一樣，肉包子打狗，有去無回，它的用途也比較簡(jiǎn)單

• SQL 文件語(yǔ)法的驗(yàn)證
• 用來查找與存儲(chǔ)引擎自身不相關(guān)的性能瓶頸

6.CSV存儲(chǔ)引擎

CSV 存儲(chǔ)引擎實(shí)際上操作的就是一個(gè)標(biāo)準(zhǔn)的CSV 文件，而且他的特點(diǎn)就是不支持索引，也就說，不支持索引，那么效率必然會(huì)很低，這個(gè)相信很多人都不會(huì)選擇去使用它。

針對(duì)這些引擎，阿粉就說，既然都了解了這么多的內(nèi)容了，那肯定不能在繼續(xù)說我們的SQL語(yǔ)句優(yōu)化了，當(dāng)你在面試的時(shí)候，就要有針對(duì)性的了。

假如說面試的時(shí)候，面試官問你：現(xiàn)在有一個(gè)功能，測(cè)試那邊反饋過來的信息是，這個(gè)功能的響應(yīng)時(shí)間超出了預(yù)期的值，你都從哪些方面入手來處理這個(gè)問題。

這個(gè)問題看似不是很難，但是可以根據(jù)這個(gè)問題延伸出很多很多的問題。

問題一：如果你說從SQL語(yǔ)句優(yōu)化，入手，那么勢(shì)必要去檢查索引，而面試官下一個(gè)問題可能就是，為什么加了索引，速度就會(huì)變快。

問題二：如果你說服務(wù)器的配置，那么面試官可能心里就想，為了一個(gè)功能上的反饋，你讓我去給服務(wù)器增加配置，代價(jià)太大。

如果你對(duì)索引還沒有了解到很深層面的時(shí)候，這個(gè)時(shí)候你就可以把SQL語(yǔ)句優(yōu)化，和看數(shù)據(jù)庫(kù)該表的引擎是否可以進(jìn)行修改，如果可以，那么你就可以開始把這些關(guān)于SQL引擎的優(yōu)勢(shì)往自己的方向走了。