本文中所提到的SQL優化技巧均是基于Mysql 索引 BTree類型 。將從以下幾個方面介紹常用的SQL優化技巧：

避免在 WHERE 子句中使用 != 或 <> 操作符。
避免在 WHERE 子句中對索引列使用 %前綴模糊查詢。
避免在 WHERE 子句中對索引列使用 OR 來連接條件。
避免在 WHERE 子句中對索引列使用 IN 和 NOT IN。
避免在 WHERE 子句中對索引列使用計算、函數、類型轉換等操作。
避免在 WHERE 子句中對索引列使用參數。
使用合理的分頁方式以提高分頁的效率。
使用 EXISTS 替換 DISTINCT 。
避免在 WHERE 子句中對索引列進行 NULL 值判斷。
避免在 WHERE 子句中對索引列進行 隱式類型轉換。
合理使用 復合索引。

本文篇幅較長，建議先收藏再閱讀，便于后續查閱。

善用EXPLAIN

通常，我們在寫完較為復雜的 SQL 時，一般會進行一下 MySQL 優化，我們要善用 EXPLAIN 查看 SQL 執行計劃。

Explain語法

如下

執行計劃包含如下信息：

type：連接類型。一般來說，需要保證查詢至少達到 range 級別，最好能達到 ref，杜絕出現 all 級別。
key：實際使用的索引，如果沒有可用的索引，則顯示為NULL，可以使用force index強制索引方式。
key_len: 索引字段的最大可能長度，理論上長度越短越好，但并非實際使用長度。
rows: 表示MySQL根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數，ROWS值的大小是個統計抽樣結果，并不十分準確。
extra: 額外說明，當出現Using filesort, Using temporary的時候需要注意。

避免在 WHERE 子句中使用 != 或 <> 操作符

應盡量避免在 WHERE 子句中使用 != 或 <> 操作符，否則將導致引擎放棄使用索引而進行全表掃描。MySQL 只有對以下操作符才會使用索引：，>=，BETWEEN，IN，以及使用 LIKE 時的 后綴模糊查詢 % 。

避免在WHERE 子句中對索引列使用 %前綴模糊查詢

WHERE 子句中使用 LIKE進行模糊查詢時，使用 %前綴模糊查詢 無法使用索引，從而引發全表掃描。解決 %前綴模糊查詢時索引不被使用的方法就是添加覆蓋索引(只訪問索引的查詢，索引和查詢列一致，只需掃描索引而無須回表)。

避免在WHERE 子句中對索引列使用 OR 來連接條件

應盡量避免在 WHERE 子句中使用 OR 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描。使用 OR 的字句可以分解成多個查詢，并且通過 UNION 連接多個查詢。他們的速度只同是否使用索引有關，如果查詢需要用到復合索引，用 UNION ALL 執行的效率更高。

盡量UNION ALL 代替 UNION ，UNION 和 UNION ALL 的差異主要是前者需要將結果集合并后再進行唯一性過濾操作，會涉及到排序，增加大量的CPU運算，加大資源消耗及延遲。當然，UNION ALL的前提條件是兩個結果集沒有重復數據。

避免在WHERE 子句中對索引列使用 IN 和 NOT IN

應盡量避免在 WHERE 子句中使用 IN 和 NOT IN ，否則將導致全表掃描，對于連續的數值，能用 BETWEEN AND 盡量避免使用 IN。一般，用 EXISTS 代替 IN 。若需要使用 IN，在 IN 后面值的列表中，按照值的分布數量降序排列，減少判斷的次數。

嘗試使用BETWEEN AND 替換 IN 。

我們使用 EXISTS 替代 IN，用 NOT EXISTS 替代 NOT IN，無論在哪種情況下， NOT IN 效率都是最低的。

嘗試使用LEFT JOIN 替換 IN。

如上，我們使用了如下方式優化了 IN 和 NOT IN：

使用 between 替換 in ( 如果 in 的條件是連續的)
使用 exists 替代 in、用not exists替代 not in
使用 left join 替換 in 。

避免在WHERE 子句中對索引列使用計算、函數、類型轉換等操作

應盡量避免在 WHERE 子句中對 “=” 左邊的字段進行函數、算術運算及其他表達式運算，可以將表達式運算移至“=”右邊，否則將導致引擎放棄使用索引而進行全表掃描。

避免在WHERE 子句中對索引列使用參數

如果在 WHERE 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變量，但優化程序不能將訪問計劃的選擇推遲到運行時。它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項，可以改為強制查詢使用索引。

使用合理的分頁方式以提高分頁的效率

分頁查詢在我們的實際應用中非常普遍，也是最容易出問題的查詢場景。比如對于下面簡單的語句，一般想到的辦法是在name，age，register_time字段上創建復合索引。這樣條件排序都能有效的利用到索引，性能迅速提升。

如上例子，當 LIMIT 子句變成 “LIMIT 100000, 50” 時，此時我們會發現，只取50條語句為何會這么慢?

原因很簡單，MySQL并不知道第 100000條記錄從什么地方開始，即使有索引也需要從頭計算一次，因此會感覺非常的慢，一般我們在做翻頁時，是可以獲取上一頁中的某個數據標志來縮小查詢范圍的，比如時間，可以將上一頁的最大值時間作為查詢條件的一部分，SQL可以優化為這樣：

使用EXISTS 替換 DISTINCT

EXISTS語句用來判斷()內的表達式是否存在返回值，如果存在就返回 True，如果不存在就返回 False，同時它只要括號中的表達式有一個值存在，就立刻返回 True ，而不用遍歷表中所有的數據。因此 EXISTS 使查詢效率更高。

避免在WHERE 子句中對索引列進行 NULL 值判斷

應盡量避免在 WHERE 子句中對字段進行 NULL 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，創建表時 NULL 是默認值，但大多數時候應該使用 NOT NULL，或者使用一個默認值，如 0 作為默認值。

例如，性別，使用1表示男，2表示女，0表示未知或者是用戶沒有選擇，默認值設置為 0，因為大部分編程語言的數字類型的默認值0。

空值和NULL是有區別的，以一個杯子為例：

空值代表杯子是真空的。
NULL 代表杯子中裝滿了空氣。

如果字段允許為空，可能會有以下問題：

查詢條件就必須處理為空的情況，否則會出現一些很奇怪的問題，比如 NOT IN、!= 等負向條件查詢在有 NULL 值的情況下返回永遠為空結果，查詢容易出錯。
在部分數據庫中會導致索引失效。
可空列需要更多的存儲空間，導致空間變大，進而導致數據庫系統查詢分析變的復雜。
在程序中也需要每次都判斷是不是空，導致程序復雜了。

但凡事沒有絕對的，使用默認值的思路可以解決很大一部分可為空的問題，但不是所有都需這樣做，具體還是要根據具體業務進行分析。

避免在WHERE 子句中對索引列進行隱式類型轉換

當我們對不同類型的值進行比較的時候，為了使得這些數值可比較，MySQL會做一些隱式轉化(Implicit type conversion)。

SQL查詢語句的條件中字段賦值與字段定義類型不匹配是一種常見的錯誤用法。

如上，字段 account 的定義為 varchar 類型，在 WHERE 條件中 account 字段是數字型，兩者數據類型不一樣，這時是沒法直接進行比較的，需要進行類型轉換。MySQL的策略是將表中 account 字段全部轉換為數字型之后再比較，由于函數作用于表字段，引起索引失效，導致全表掃描，正確的寫法如下：

合理使用復合索引

如果經常執行如上查詢，那么建立三個單獨索引 不如建立一個復合索引，因為三個單獨索引通常數據庫每次執行只能使用其中一個，雖然這樣比不使用索引而進行全表掃描提高了很多效率，但使用復合索引因為索引本身就對應到三個字段上的，效率會有更大提高。

那么為什么數據庫只支持一條查詢語句只使用一個索引，簡單的講是因為N個獨立索引同時在一條語句使用的效果比只使用一個索引還要慢，開銷太大。

在使用索引字段作為條件時，如果該索引是復合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用，并且應盡可能的讓字段順序與索引順序相一致。

同時，復合索引的生效原則是從前往后依次使用生效，如果中間某個索引沒有使用，那么斷點前面的索引部分起作用，斷點后面的索引沒有起作用，造成斷點的原因一般有：

前邊的任意一個索引沒有參與查詢，后面的不生效。
前邊的任意一個索引失效，當前索引及后面全部不生效。
前邊的任意一個索引字段參與的是范圍查詢，后面的不生效。

引發索引失效，導致全表掃描的原因有：

索引列進行計算、函數、類型轉換等操作。
索引列使用不等于，如 != 或<>。
索引列使用 IS NULL ，IS NOT NULL。
模糊查詢LIKE 以通配符開頭如，%str。
索引列使用使用 OR 來連接條件。
索引列使用 NOT IN 。
類型錯誤，如字段 NUM 類型為varchar，WHERE 條件用 number，NUM = 1。
WHERE子句和 ORDER BY使用相同的索引，并且 ORDER BY的順序和索引順序相同，并且 ORDER BY 的字段都是升序或者降序，否則不會使用索引。
復合索引不符合最佳左前綴原則或存在斷點。
如果MYSQL評估使用索引比全表掃描更慢，則不使用索引。

例如我們建立了一個這樣復合索引key index (col1, col2, col3)，那么其實相當于創建了(col1)，(col1, col2)，(col1, col2, col3) 三個索引，即最佳左前綴特性。

其他優化技巧

當索引列有大量數據重復時，SQL查詢可能不會去利用索引，并不是所有索引對查詢都有效，SQL是根據表中數據來進行查詢優化的。如表中有“性別”字段，即使在“性別”字段建立索引也對查詢效率起不了作用，盡量不要對數據庫中某個含有大量重復的值的字段建立索引。

建立索引可以提高 SELECT 的效率，但 索引并不是越多越好。索引同時也降低了 INSERT 及 UPDATE 的效率，因為 INSERT 或 UPDATE 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視實際應用情況而定。同時，一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用的字段是否有建立索引的必要。

對于數值字段，盡量使用數字型字段，若只含數值信息的字段盡量不要設計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數字型而言，只需要比較一次就夠了。

對于字符型字段，盡量的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長字段存儲空間小，可以節省存儲空間，其次對于查詢來說，在一個相對較小的字段內搜索效率顯然要高些。

避免使用 select * from table，用具體的字段列表代替“*”，避免返回用不到的任何字段。

盡可能的使用索引字段作為查詢條件，尤其是聚簇索引，必要時可以通過index index_name來強制指定索引，避免對大表查詢時進行table scan，必要時考慮新建索引。要注意索引的維護，周期性重建索引，重新編譯存儲過程。

在新建臨時表時，如果一次性插入數據量很大，那么可以使用 SELECT INTO 代替 CREAT TABLE，避免造成產生大量日志，以提高速度。如果數據量不大，為了緩和系統表的資源，應先CREAT TABLE，然后INSERT。

當服務器的內存夠多時，配置 線程數量 = 最大連接數 + 5，使其發揮最大的效率。否則使用配置線程數量 < 最大連接數啟用SQL SERVER的線程池來解決，如果還是線程數量 = 最大連接數+5，可能會嚴重的損害服務器的性能。

盡量避免向客戶端返回大量結果數據，若數據量過大，應該考慮相應需求是否合理。盡量避免大事務操作，提高系統并發能力。

創建索引的一般規則

表的主鍵、外鍵需要建立索引。
頻繁與其他表進行連接的表，在連接字段上應該建立索引。
頻繁出現在 WHERE 子句及 ORDER BY 中的字段，特別是大表的字段，應該建立索引。
索引應該建在短字段上，對于大的文本字段甚至超長字段，避免建索引。
復合索引的建立需要結合實際應用進行分析，盡量考慮用單字段索引代替。
正確選擇復合索引中的主列字段，一般是選擇性較好的字段；
復合索引的幾個字段是否經常同時以 AND 方式出現在 WHERE 子句中，單字段查詢是否極少甚至沒有，如果是，則可以建立復合索引，否則考慮單字段索引。
如果復合索引中包含的字段經常單獨出現在 WHERE 子句中，則分解為多個單字段索引。
如果復合索引所包含的字段超過3個，需要結合實際應用考慮其必要性，考慮減少復合的字段。
如果既有單字段索引，又有這幾個字段上的復合索引，通常可以刪除復合索引。
頻繁進行數據操作的表，不要建立太多的索引，刪除無用的索引。
建立的每個索引都會增加存儲開銷，索引對于插入、刪除、更新操作也會增加處理上的開銷。另外，過多的復合索引，在有單字段索引的情況下，一般都是沒有存在價值的。相反，還會降低數據增加刪除時的性能，特別是對頻繁更新的表來說，負面影響非常大。
盡量不要對數據庫中某個含有大量重復的值的字段建立索引。