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

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

阿粉之前幫公司面試過一部分人，因為之前和老大一起面試，所以也看過不少人的簡歷，而簡歷上對于數(shù)據(jù)庫這塊內(nèi)容，很多人都寫的是這個樣子的。

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

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

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

SQL語句優(yōu)化我們在面試的時候，面試官看你寫了SQL語句優(yōu)化，有時候就會提問了，那你說說SQL語句優(yōu)化都是在哪些方面的吧。

 

 

 

[[381723]]

 

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

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

。。。。。

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

MySQL體系以下全文，全部按照MySQL來進行分析，分析引擎，我們先從MySQL分析吧。MySQL的體系結構圖如下：

 

 

 

 

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

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

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

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

MySQL存儲引擎

存儲引擎是MySQL區(qū)別于其他數(shù)據(jù)庫的一個最重要特性，每個存儲引擎都有他們自己的特點，不同的特點，會在不同的場景下使用，雖然我們在開發(fā)中很多時候都是使用的一種，但是能夠根據(jù)具體的應用建立不同存儲引擎表，這才是最牛逼的吧。

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

 

 

 

 

大家看完是不是感覺還挺多，但是MySQL的9種存儲引擎都是各自是各自的特點，然后根據(jù)需求的不同，我們在建表的時候可以選擇一下，是不是發(fā)現(xiàn)了又一個帶妹的好機會，那我們就開始分析一下吧。

1.FEDERATED存儲引擎

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

 

 

 

 

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

在這里，阿粉不去著重的講這個，因為重點是我們最常使用的那個，大家肯定都知道，那就是InnoDB存儲引擎。

2.InnoDB存儲引擎

InnoDB存儲引擎這個一般也是大家在面試中經(jīng)常的能和面試官侃侃而談的一個，因為它是默認的數(shù)據(jù)庫存儲引擎。注意，這個默認是從MySQL的5.5.8開始的

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

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

2.1InnoDB的體系架構

 

 

 

 

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

既然圖中我們看到了后臺的線程，那么我們也要說說這個后臺線程是個什么?

InnoDB存儲引擎是多線程的模型，因此其后臺有多個不同的后臺線程，負責處理不同的任務。

而這個后臺線程也是分兩個部分的，一個是核心線程，一個是IO線程。

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

核心線程

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

IO線程

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

凈化線程

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

清理線程

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

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

3.Memory 存儲引擎

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

這種用的比較少，阿粉就不再多給大家介紹了，就說說它的特點得了，畢竟一個關機，直接就沒了。

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

4.MyISAM存儲引擎

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

5.BLACKHOLE存儲引擎

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

• SQL 文件語法的驗證
• 用來查找與存儲引擎自身不相關的性能瓶頸

6.CSV存儲引擎

CSV 存儲引擎實際上操作的就是一個標準的CSV 文件，而且他的特點就是不支持索引，也就說，不支持索引，那么效率必然會很低，這個相信很多人都不會選擇去使用它。

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

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

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

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

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

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