優化成本:
硬體>系統配置>資料庫表結構>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://www.cnblogs.com/yittxbug/p/10912265.html