回顧MySQL的執行過程，幫助介紹如何進行sql優化。

（1）客戶端發送一條查詢語句到伺服器；

（2）伺服器先查詢緩存，如果命中緩存，則立即返回存儲在緩存中的數據；

（3）未命中緩存後，MySQL通過關鍵字將SQL語句進行解析，並生成一顆對應的解析樹，MySQL解析器將使用MySQL語法進行驗證和解析。

​ 例如，驗證是否使用了錯誤的關鍵字，或者關鍵字的使用是否正確；

（4）預處理是根據一些MySQL規則檢查解析樹是否合理，比如檢查表和列是否存在，還會解析名字和別名，然後預處理器會驗證許可權；

​ 根據執行計劃查詢執行引擎，調用API介面調用存儲引擎來查詢數據；

（5）將結果返回客戶端，併進行緩存；

SQL語句性能優化常用策略

1、 為 WHERE 及 ORDER BY 涉及的列上建立索引

對查詢進行優化，應儘量避免全表掃描，首先應考慮在 WHERE 及 ORDER BY 涉及的列上建立索引。

2、where中使用預設值代替null 應儘量避免在 WHERE 子句中對欄位進行 NULL 值判斷，創建表時 NULL 是預設值，但大多數時候應該使用 NOT NULL，或者使用一個特殊的值，如 0，-1 作為預設值。

為啥建議where中使用預設值代替null，四個原因：

（1）並不是說使用了is null或者 is not null就會不走索引了，這個跟mysql版本以及查詢成本都有關；

（2）如果mysql優化器發現，走索引比不走索引成本還要高，就會放棄索引，這些條件 !=，<>，is null，is not null經常被認為讓索引失效；

（3）其實是因為一般情況下，查詢的成本高，優化器自動放棄索引的；

（4）如果把null值，換成預設值，很多時候讓走索引成為可能，同時，表達意思也相對清晰一點；

3、慎用 != 或 <> 操作符。

MySQL 只有對以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些時候的 LIKE。

所以：應儘量避免在 WHERE 子句中使用 != 或 <> 操作符， 會導致全表掃描。

4、慎用 OR 來連接條件

使用or可能會使索引失效，從而全表掃描；

應儘量避免在 WHERE 子句中使用 OR 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，

可以使用 UNION 合併查詢：

select id from t where num=10

union all

select id from t where num=20

一個關鍵的問題是否用到索引。他們的速度只同是否使用索引有關，如果查詢需要用到聯合索引，用 UNION all 執行的效率更高。多個 OR 的字句沒有用到索引，改寫成 UNION 的形式再試圖與索引匹配。

5、慎用 IN 和 NOT IN

IN 和 NOT IN 要慎用，否則會導致全表掃描。對於連續的數值，能用 BETWEEN 就不要用 IN：select id from t where num between 1 and 3。

6、慎用 左模糊like ‘%…’

模糊查詢，程式員最喜歡的就是使用like，like很可能讓索引失效。

比如：

select id from t where name like‘%abc%’ select id from t where name like‘%abc’ 而select id from t where name like‘abc%’才用到索引。

所以：

首先儘量避免模糊查詢，如果必須使用，不採用全模糊查詢，也應儘量採用右模糊查詢， 即like ‘…%’，是會使用索引的； 左模糊like ‘%…’無法直接使用索引，但可以利用reverse + function index的形式，變化成 like ‘…%’； 全模糊查詢是無法優化的，一定要使用的話建議使用搜索引擎，比如 ElasticSearch。 備註：如果一定要用左模糊like ‘%…’檢索， 一般建議 ElasticSearch+Hbase架構

7、WHERE條件使用參數會導致全表掃描。

如下麵語句將進行全表掃描：

select id from t where num=@num

因為SQL只有在運行時才會解析局部變數，但優化程式不能將訪問計劃的選擇推 遲到 運行時；

它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。

所以， 可以改為強制查詢使用索引：

select id from t with(index(索引名)) where num=@num

8、用 EXISTS 代替 IN 是一個好的選擇

很多時候用exists 代替in 是一個好的選擇：

select num from a where num in(select num from b) 用下麵的語句替換： select num from a where exists(select 1 from b where num=a.num)

9、索引並不是越多越好

索引固然可以提高相應的 SELECT 的效率，但同時也降低了 INSERT 及 UPDATE 的效。

因為 INSERT 或 UPDATE 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。

一個表的索引數最好不要超過 6 個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。

10、儘量使用數字型欄位

（1）因為引擎在處理查詢和連接時會逐個比較字元串中每一個字元；

（2）而對於數字型而言只需要比較一次就夠了；

（3）字元會降低查詢和連接的性能，並會增加存儲開銷；

所以：儘量使用數字型欄位，若只含數值信息的欄位儘量不要設計為字元型，這會降低查詢和連接的性能，並會增加存儲開銷。

11、儘可能的使用 varchar, nvarchar 代替 char, nchar

（1）varchar變長欄位按數據內容實際長度存儲，存儲空間小，可以節省存儲空間；

（2）char按聲明大小存儲，不足補空格；

（3）其次對於查詢來說，在一個相對較小的欄位內搜索，效率更高；

因為首先變長欄位存儲空間小，可以節省存儲空間，其次對於查詢來說，在一個相對較小的欄位內搜索效率顯然要高些。

*14、查詢SQL儘量不要使用select ，而是具體欄位

最好不要使用返回所有：select * from t ，用具體的欄位列表代替 “*”，不要返回用不到的任何欄位。

select *的弊端：

（1）增加很多不必要的消耗，比如CPU、IO、記憶體、網路帶寬；

（2）增加了使用覆蓋索引的可能性；

（3）增加了回表的可能性；

（4）當表結構發生變化時，前端也需要更改；

（5）查詢效率低；

15、將需要查詢的結果預先計算好

將需要查詢的結果預先計算好放在表中，查詢的時候再Select，而不是查詢的時候進行計算。

16、IN後出現最頻繁的值放在最前面

如果一定用IN，那麼：在IN後面值的列表中，將出現最頻繁的值放在最前面，出現得最少的放在最後面，減少判斷的次數。

17、儘量使用 EXISTS 代替 select count(1) 來判斷是否存在記錄。

count 函數只有在統計表中所有行數時使用，而且 count(1) 比 count(*) 更有效率。

18、用批量插入或批量更新

當有一批處理的插入或更新時，用批量插入或批量更新，絕不會一條條記錄的去更新。

（1）多條提交

INSERT INTO user (id,username) VALUES(1,'xx'); INSERT INTO user (id,username) VALUES(2,'yy');

（2）批量提交

INSERT INTO user (id,username) VALUES(1,'xx'),(2,'yy'); 預設新增SQL有事務控制，導致每條都需要事務開啟和事務提交，而批量處理是一次事務開啟和提交，效率提升明顯，達到一定量級，效果顯著，平時看不出來。

19、將不需要的記錄在 GROUP BY 之前過濾掉

提高 GROUP BY 語句的效率，可以通過將不需要的記錄在 GROUP BY 之前過濾掉。

下麵兩個查詢返回相同結果，但第二個明顯就快了許多。

低效：

SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER' 高效：

SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP BY JOB

20、避免死鎖

在你的存儲過程和觸發器中訪問同一個表時總是以相同的順序；事務應經可能地縮短，在一個事務中應儘可能減少涉及到的數據量；永遠不要在事務中等待用戶輸入。

21、索引創建規則：

表的主鍵、外鍵必須有索引；

數據量超過 300 的表應該有索引；

經常與其他表進行連接的表，在連接欄位上應該建立索引；

經常出現在 WHERE 子句中的欄位，特別是大表的欄位，應該建立索引；

索引應該建在選擇性高的欄位上；

索引應該建在小欄位上，對於大的文本欄位甚至超長欄位，不要建索引；

複合索引的建立需要進行仔細分析，儘量考慮用單欄位索引代替；

正確選擇複合索引中的主列欄位，一般是選擇性較好的欄位；

複合索引的幾個欄位是否經常同時以 AND 方式出現在 WHERE 子句中？單欄位查詢是否極少甚至沒有？如果是，則可以建立複合索引；否則考慮單欄位索引；

如果複合索引中包含的欄位經常單獨出現在 WHERE 子句中，則分解為多個單欄位索引；

如果複合索引所包含的欄位超過 3 個，那麼仔細考慮其必要性，考慮減少複合的欄位；

如果既有單欄位索引，又有這幾個欄位上的複合索引，一般可以刪除複合索引；

頻繁進行數據操作的表，不要建立太多的索引； 刪除無用的索引，避免對執行計劃造成負面影響；

表上建立的每個索引都會增加存儲開銷，索引對於插入、刪除、更新操作也會增加處理上的開銷。

另外，過多的複合索引，在有單欄位索引的情況下，一般都是沒有存在價值的；相反，還會降低數據增加刪除時的性能，特別是對頻繁更新的表來說，負面影響更大。 儘量不要對資料庫中某個含有大量重覆的值的欄位建立索引。

22、在寫 SQL 語句時，應儘量減少空格的使用

查詢緩衝並不自動處理空格，因此，在寫 SQL 語句時，應儘量減少空格的使用，尤其是在 SQL 首和尾的空格（因為查詢緩衝並不自動截取首尾空格）。

23、每張表都設置一個 ID 做為其主鍵

我們應該為資料庫里的每張表都設置一個 ID 做為其主鍵，而且最好的是一個 INT 型的（推薦使用 UNSIGNED），並設置上自動增加的 AUTO_INCREMENT 標誌。

24、使用explain分析你SQL執行計劃

（1）type

system：表僅有一行，基本用不到；

const：表最多一行數據配合，主鍵查詢時觸發較多；

eq_ref：對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型；

ref：對於每個來自於前面的表的行組合，所有有匹配索引值的行將從這張表中讀取；

range：只檢索給定範圍的行，使用一個索引來選擇行。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用range；

index：該聯接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數據文件小；

all：全表掃描；

性能排名：system > const > eq_ref > ref > range > index > all。 實際sql優化中，最後達到ref或range級別。

（2）Extra常用關鍵字

Using index：只從索引樹中獲取信息，而不需要回表查詢；

Using where：WHERE子句用於限制哪一個行匹配下一個表或發送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where並且表聯接類型為ALL或index，查詢可能會有一些錯誤。需要回表查詢。

Using temporary：mysql常建一個臨時表來容納結果，典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時；

25、當只要一行數據時使用 LIMIT 1

當你查詢表的有些時候，你已經知道結果只會有一條結果，但因為你可能需要去fetch游標，或是你也許會去檢查返回的記錄數。

在這種情況下，加上 LIMIT 1 可以增加性能。

這樣一來，MySQL 資料庫引擎會在找到一條數據後停止搜索，而不是繼續往後查少下一條符合記錄的數據。

26、將大的DELETE，UPDATE、INSERT 查詢變成多個小查詢

能寫一個幾十行、幾百行的SQL語句是不是顯得逼格很高？然而，為了達到更好的性能以及更好的數據控制，你可以將他們變成多個小查詢。

27、合理分表 儘量控制單表數據量的大小，建議控制在500萬以內

500萬並不是MySQL資料庫的限制，過大會造成修改表結構，備份，恢復都會有很大的問題。

可以用歷史數據歸檔（應用於日誌數據），分庫分表（應用於業務數據）等手段來控制數據量大小。

作者：京東科技 梁發文

來源：京東雲開發者社區 轉載請註明來源