關于SQL的優化有哪些

本篇內容介紹了“關于SQL的優化有哪些”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

大部分人說的SQL優化

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

• 熟練使用 MySQL，SQLServer，熟悉Oracle，熟悉SQL語句優化。

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

而本文，告訴你，不要只是會SQL語句優化。

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

關于SQL語句優化，內容可就比較多了。

• 對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。

• 盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。

• 應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描。

。。。。。

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

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

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

• 連接池組件

• 管理服務和工具組件

• SQL接口組件

• 查詢分析器組件

• 優化器組件

• 緩沖(Cache)組件

• 插件式存儲引擎

• 物理文件

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

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

MySQL存儲引擎

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

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

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

1.FEDERATED存儲引擎

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

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

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

2.InnoDB存儲引擎

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

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

• 支持事務。默認的事務隔離級別為可重復，這個我們經常用，所以肯定也都知道。

• 支持外鍵，這個外鍵大家肯定也都清楚，有利有弊，畢竟外鍵的作用在那里放著(利：增加可讀性，若出現宕機，最大限度的保證數據的一致性和完整性，弊：降低了表的查詢速度，如果數據了過大，那么你插入數據庫數據的時長可能是不增加外鍵的十倍)

• 行鎖設計，這樣可以支持更高的并發，這也是為什么有時候面試官說你們上ES有點大材小用，因為MySQL自己也能處理那么多。

• 使用多版本并發控制(MVCC)來獲得高并發性，并且實現了SQL標準的4種隔離級別，默認為REPEATABLE READ級別。

• 使用一種被稱為next-key locking(有人稱它為間隙鎖)的策略來避免幻讀(phantom)現象的產生

• 數據存儲采用了聚集(clustered)的方式，每張表的存儲都是按主鍵的順序進行存放。

• InnoDB的索引結構和MySQL其他的存儲引擎不同，聚簇索引對主鍵查詢性能非常高，這時候就得有個限制要求，如果表上的索引較多，主鍵就盡可能的小。

• InnoDB通過一些機制和工具都支持真正的熱備份，也就是在線熱備份。

• 數據存儲在表空間(tablespace)中，這個表空間實際上是由InnoDB管理的一個黑盒，由一系列的文件組成。

2.1InnoDB的體系架構

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

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

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

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

• 核心線程Master Thread

• IO線程 IO Thread

• 凈化線程Purge Thread

• 清理線程Page Cleaner Thread

核心線程

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

IO線程

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

凈化線程

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

清理線程

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

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

3.Memory 存儲引擎

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

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

• 不支持TEXT和BLOB類型，對于字符串類型的數據，只支持固定長度的行，VARCHAR會被自動存儲為CHAR類型;

• 速度非常快，只支持表鎖，并發性能較差，并且不支持TEXT和BLOB列類型

• 服務器一但出現宕機，所有數據全部消失

• 存儲變長字段(varchar)時是按照定常字段(char)的方式進行的，因此會浪費內存

4.MyISAM存儲引擎

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

5.BLACKHOLE存儲引擎

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

• SQL 文件語法的驗證

• 用來查找與存儲引擎自身不相關的性能瓶頸

6.CSV存儲引擎

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

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

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

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

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

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

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

“關于SQL的優化有哪些”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！