優化成本: 硬件>系統配置>數據庫表結構>SQL及索引 優化效果: 硬件<系統配置<數據庫表結構<SQL及索引 本文我們就來談談 MySQL 中常用的 SQL 優化方法，利用好這些方法會讓你的 MySQL 效率提高提升至少 3 倍。 ** 1、EXPLAIN ** 做?`MySQL`?優化，我們要善用?`EXPLAIN`?查看?`SQL`?執行計劃。 ?Type 列，連接類型。一個好的 SQL 語句至少要達到 Range 級別。杜絕出現 All 級別。 ?Key 列，使用到的索引名。如果沒有選擇索引，值是 NULL。可以采取強制索引方式。 ?Key\_Len 列，索引長度。 ?Rows 列，掃描行數。該值是個預估值。 ?Extra 列，詳細說明。注意，常見的不太友好的值，如下：Using filesort，Using temporary。 **2、SQL 語句中 IN 包含的值不應過多 ** MySQL 對于?`IN`?做了相應的優化，即將?`IN`?中的常量全部存儲在一個數組里面，而且這個數組是排好序的。但是如果數值較多，產生的消耗也是比較大的。 再例如：`Select Id From T where Num IN (1,2,3)`?對于連續的數值，能用?`Between`?就不要用?`IN`?了；再或者使用連接來替換。 **3、Select 語句務必指明字段名稱** `Select *`?會增加很多不必要的消耗（如：CPU、IO、內存、網絡帶寬等）, 增加了使用覆蓋索引的可能性。 當表結構發生改變時，前斷也需要更新。所以要求直接在?`Select`?后面接上字段名。 **4、當只需要一條數據的時候，使用 Limit 1 ** 這是為了使?`EXPLAIN`?中?`Type`?列達到?`Const`?類型。 **5、如果排序字段沒有用到索引，就盡量少排序 ** **6、如果限制條件中其他字段沒有索引，盡量少用 OR ** `OR`?兩邊的字段中，如果有一個不是索引字段，而其他條件也不是索引字段，會造成該查詢不走索引的情況。很多時候使用?`Union All`?或者是?`Union`（必要的時候）的方式來代替?`OR`?會得到更好的效果。 **7、盡量用 Union All 代替 Union ** `Union`?和?`Union All`?的差異主要是前者需要將結果集合并后再進行唯一性過濾操作，這就會涉及到排序，增加大量的 CPU 運算，加大資源消耗及延遲。當然，`Union All`?的前提條件是兩個結果集沒有重復數據。 **8、不使用 ORDER BY RAND()** ~~~ Select Id From `dynamic` Order By rand() Limit 1000; ~~~ 上面的?`SQL`?語句，可優化為： ~~~ Select Id From `dynamic` T1 Join (Select rand() * (Select Max(Id) From `dynamic`) as nid) T2 on T1.Id > T2.nidlimit 1000 ~~~ **9、區分 IN 和 Exists、Not In 和 Not Exists ** ~~~ Select * From 表A Where Id IN (Select Id From 表B) ~~~ 上面?`SQL`?語句相當于 ~~~ Select * From 表A Where Exists(Select * From 表B Where 表B.Id=表A.Id) ~~~ 區分?`IN`?和?`Exists`?主要是造成了驅動順序的改變（這是性能變化的關鍵）。如果是?`Exists`，那么以外層表為驅動表，先被訪問，如果是?`IN`，那么先執行子查詢。所以?`IN`?適合于外表大而內表小的情況，`Exists`?適合于外表小而內表大的情況。 關于?`Not IN`?和?`Not Exists`，推薦使用?`Not exists`。這不僅僅是效率問題，`Not IN`?可能存在邏輯問題。 如何高效的寫出一個替代?`not in`?的?`SQL`?語句？ 原?`SQL`?語句： ~~~ Select Colname … From A 表 Where A.id Not IN (Select B.id From B表) ~~~ 高效的?`SQL`?語句： ~~~ Select Colname … From A表 Left join B表 ON Where A.id = B.id Where B.id is Null ~~~ **10、使用合理的分頁方式以提高分頁的效率 ** ~~~ Select Id,Name From Product Limit 866613, 20 ~~~ 使用上述?`SQL`?語句做分頁的時候，可能有人會發現，隨著表數據量的增加，直接使用?`Limit`?分頁查詢會越來越慢。 優化的方法如下：可以取前一頁的最大行數的 Id ，然后根據這個最大的 Id 來限制下一頁的起點。比如：此列中，上一頁最大的 Id 是 866612。SQL 可以采用如下的寫法： ~~~ Select Id,Name From Product Where Id> 866612 Limit 20 ~~~ **11、分段查詢 ** 在一些用戶選擇頁面中，可能一些用戶選擇的時間范圍過大，造成查詢緩慢。主要的原因是掃描行數過多。這個時候可以通過程序，分段進行查詢，循環遍歷，將結果合并處理進行展示。 **12、避免在 Where 子句中對字段進行 Null 值判斷 ** 對于?`Null`?的判斷會導致引擎放棄使用索引而進行全表掃描。 **13、不建議使用 % 前綴模糊查詢 ** 例如：`Like "%name"`?或者?`Like "%name%"`?，這種查詢會導致索引失效而進行全表掃描。但是可以使用?`Like "name%"`。那如何要查詢?`%name%`?呢？ **14、避免在 Where 子句中對字段進行表達式操作 ** 比如下面這個例子： ~~~ Select user_id,user_project from user_base Where age*2=36; ~~~ 在上述?`SQL`?中對字段就行了算術運算，這會造成引擎放棄使用索引，建議改成： ~~~ Select user_id,user_project from user_base where age=36/2; ~~~ **15、避免隱式類型轉換 ** `Where`?子句中出現?`Column`?字段的類型和傳入的參數類型不一致的時候發生的類型轉換，建議先確定 Where 中的參數類型。 **16、對于聯合索引來說，要遵守最左前綴法則 ** 舉列來說：索引含有字段 id、name、school，可以直接用 id 字段，也可以 id、name 這樣的順序，但是 name，school 都無法使用這個索引。所以在創建聯合索引的時候一定要注意索引字段順序，常用的查詢字段放在最前面。 **17、必要時可以使用 Force Index 來強制查詢走某個索引 ** 有的時候?`MySQL`?優化器采取它認為合適的索引來檢索?`SQL`?語句，但是可能它所采用的索引并不是我們想要的。這時就可以采用?`Force index`?來強制優化器使用我們制定的索引。 **18、注意范圍查詢語句 ** 對于聯合索引來說，如果存在范圍查詢，比如?`Between`、`>`、`<`?等條件時，會造成后面的索引字段失效。 **19、關于 JOIN 優化** `LEFT JOIN`?A 表為驅動表，`INNER JOIN`?MySQL 會自動找出那個數據少的表作用驅動表，`RIGHT JOIN`?B表為驅動表。 *注意：* ?MySQL 中沒有?`Full Join`?，可以用以下方式來解決： ~~~ Select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B; ~~~ ?盡量使用?`Inner Join`，避免?`Left Join` 參與聯合查詢的表至少為 2 張表，一般都存在大小之分。如果連接方式是?`Inner Join`，在沒有其他過濾條件的情況下 MySQL 會自動選擇小表作為驅動表。但是?`Left Join`?在驅動表的選擇上遵循的是左邊驅動右邊的原則，即?`Left join`?左邊的表名為驅動表。 ?合理利用索引 被驅動表的索引字段作為?`ON`?的限制字段。 ?利用小表去驅動大表 從原理圖能夠直觀的看出如果能夠減少驅動表的話，減少嵌套循環中的循環次數，以減少 IO 總量及 CPU 運算的次數。 ?巧用 STRAIGHT\_JOIN `Inner Join`?是由?`MySQL`?選擇驅動表，但是有些特殊情況需要選擇另外的表作為驅動表。比如：`Group By`、`Order By`?等 「Using filesort」、「Using temporary」時。 *`STRAIGHT_JOIN`?用來強制連接順序，在?`STRAIGHT_JOIN`?左邊的表名就是驅動表，右邊則是被驅動表。在使用?`STRAIGHT_JOIN`?有個前提條件是該查詢是內連接，也就是?`Inner Join`。其他連接不推薦使用?`STRAIGHT_JOIN`，否則可能造成查詢結果不準確。* *原文作者：[https://mp.weixin.qq.com/s/XlBOTUGmAk54IU\_HnsvxLQ](https://mp.weixin.qq.com/s/XlBOTUGmAk54IU_HnsvxLQ)*