共計(jì) 3588 個(gè)字符，預(yù)計(jì)需要花費(fèi) 9 分鐘才能閱讀完成。

本篇內(nèi)容介紹了“關(guān)于 SQL 的優(yōu)化有哪些”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓丸趣 TV 小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

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

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

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

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

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

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

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

對(duì)查詢(xún)進(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)行全表掃描。

。。。。。

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

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

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

連接池組件

管理服務(wù)和工具組件

SQL 接口組件

查詢(xún)分析器組件

優(yōu)化器組件

緩沖 (Cache) 組件

插件式存儲(chǔ)引擎

物理文件

不得不說(shuō)，這個(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è)陂_(kāi)發(fā)中很多時(shí)候都是使用的一種，但是能夠根據(jù)具體的應(yīng)用建立不同存儲(chǔ)引擎表，這才是最牛逼的吧。

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

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

1.FEDERATED 存儲(chǔ)引擎

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

就像圖上說(shuō)的，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 開(kāi)始的

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

支持事務(wù)。默認(rèn)的事務(wù)隔離級(jí)別為可重復(fù)，這個(gè)我們經(jīng)常用，所以肯定也都知道。

支持外鍵，這個(gè)外鍵大家肯定也都清楚，有利有弊，畢竟外鍵的作用在那里放著(利：增加可讀性，若出現(xiàn)宕機(jī)，最大限度的保證數(shù)據(jù)的一致性和完整性，弊：降低了表的查詢(xún)速度，如果數(shù)據(jù)了過(guò)大，那么你插入數(shù)據(jù)庫(kù)數(shù)據(jù)的時(shí)長(zhǎng)可能是不增加外鍵的十倍)

行鎖設(shè)計(jì)，這樣可以支持更高的并發(fā)，這也是為什么有時(shí)候面試官說(shuō)你們上 ES 有點(diǎn)大材小用，因?yàn)?MySQL 自己也能處理那么多。

使用多版本并發(fā)控制 (MVCC) 來(lái)獲得高并發(fā)性，并且實(shí)現(xiàn)了 SQL 標(biāo)準(zhǔn)的 4 種隔離級(jí)別，默認(rèn)為 REPEATABLE READ 級(jí)別。

使用一種被稱(chēng)為 next-key locking(有人稱(chēng)它為間隙鎖)的策略來(lái)避免幻讀 (phantom) 現(xiàn)象的產(chǎn)生

數(shù)據(jù)存儲(chǔ)采用了聚集 (clustered) 的方式，每張表的存儲(chǔ)都是按主鍵的順序進(jìn)行存放。

InnoDB 的索引結(jié)構(gòu)和 MySQL 其他的存儲(chǔ)引擎不同，聚簇索引對(duì)主鍵查詢(xún)性能非常高，這時(shí)候就得有個(gè)限制要求，如果表上的索引較多，主鍵就盡可能的小。

InnoDB 通過(guò)一些機(jī)制和工具都支持真正的熱備份，也就是在線(xiàn)熱備份。

數(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)存池，就是線(xiàn)程池是類(lèi)似的那種，

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

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

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

核心線(xiàn)程 Master Thread

IO 線(xiàn)程 IO Thread

凈化線(xiàn)程 Purge Thread

清理線(xiàn)程 Page Cleaner Thread

核心線(xiàn)程

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

IO 線(xiàn)程

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

凈化線(xiàn)程

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

清理線(xiàn)程

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

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

3.Memory 存儲(chǔ)引擎

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

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

不支持 TEXT 和 BLOB 類(lèi)型，對(duì)于字符串類(lèi)型的數(shù)據(jù)，只支持固定長(zhǎng)度的行，VARCHAR 會(huì)被自動(dòng)存儲(chǔ)為 CHAR 類(lèi)型;

速度非常快，只支持表鎖，并發(fā)性能較差，并且不支持 TEXT 和 BLOB 列類(lèi)型

服務(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è)引擎就像他的名字一樣，肉包子打狗，有去無(wú)回，它的用途也比較簡(jiǎn)單

SQL 文件語(yǔ)法的驗(yàn)證

用來(lái)查找與存儲(chǔ)引擎自身不相關(guān)的性能瓶頸

6.CSV 存儲(chǔ)引擎

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

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

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

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

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

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

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

“關(guān)于 SQL 的優(yōu)化有哪些”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編將為大家輸出更多高質(zhì)量的實(shí)用文章！