概述

最近做查詢，統計和匯總。由于數據量比較龐大，大部分表數據上百萬，甚至有的表數據上千萬。所以在系統中做sql優化比較多，特此寫一篇文章總結一下關于sql優化方面的經驗。

導致查詢緩慢的原因

1、數據量過大

2、表設計不合理

3、sql語句寫得不好

4、沒有合理使用索引

下面主要針對sql語句的優化和索引優化做個總結。

---

針對SQL語句的優化

1、查詢語句中不要使用 *

2、盡量減少子查詢，使用關聯查詢(left join,right join,inner join)替代

3、減少使用IN或者NOT IN ,使用exists，not exists或者關聯查詢語句替代

4、or 的查詢盡量用 union或者union all 代替

(在確認沒有重復數據或者不用剔除重復數據時，union all會更好)

5、合理的增加冗余的字段(減少表的聯接查詢)

6、增加中間表進行優化(這個主要是在統計報表的場景，

后臺開定時任務將數據先統計好，盡量不要在查詢的時候去統計)

7、建表的時候能使用數字類型的字段就使用數字類型(type,status...)，數字類型的字段作為條件查詢比字符串的快

8、那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的最末尾

---

索引優化

如果針對sql語句已經沒啥可以優化的，那我們就要考慮加索引了。

--說索引前需要explain查看sql的執行計劃

1 id

SELECT識別符。這是select查詢序列號。這個不重要

2 select_type

表示查詢中每個select子句的類型(簡單OR復雜)

有以下幾種值：

1 simple

查詢中不包含查詢或者UNION(聯合查詢)

2 PRIMARY

查詢中若包含任何復雜的子部分，最外層查詢則被標記為：PRIMARY

3 UNION

表示連接查詢的第2個或后面的查詢語句。

4 DEPENDENT UNION

UNION 中的第二個或者后面的select語句，取決于外面的查詢

5 UNION RESULT

連接查詢的結果

6 SUBQUERY

子查詢中的第一個select語句

7 DEPENDENT SUBQUERY

子查詢中的第一個select語句，取決于外面的查詢

8 DERIVED

select(from子句的子查詢)

3 table 表示查詢的表

4 type

表示表的連接類型

以下的連接類型的順序是從最佳類型到最差類型

1 syste

表僅有一行，這是const類型的特例，平時不會出現

2 const

數據表最多只有一個匹配行，因為只匹配一行數據，所以很快，常用于PRIMARY KEY

或者UNIQUE查詢，可理解為是最優化的。

3 eq_ref

mysql手冊是這樣說的： 對于每個來自前面的表的行組合，從該表中讀取一行。

這可能是最好的聯接類型，除了const類型。他用在一個索引的所有部分被聯接使用并且并且索引是UNIQUE或PRIMARY KEY eq_ref可以用于使用=比較帶索引的列。

4 ref

查詢條件索引既不是UNIQUE 也不是PRIMARY KEY 的情況，ref可用于=或操作符的帶索引的列。

5 ref_or_null

該聯接類型如同ref，但是添加了Mysql可以專門搜索包含null值的行，在解決子查詢中經常使用該聯接類型的優化。

以上這五種情況都是很理想的索引使用情況。

6 index

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

7 ALL

對于每個來自先前的表的行組合，進行完整的表掃描。

5 possible_key

指出Mysql能使用哪個索引在該表中找到行。

如果該列為NULL 說明沒有使用索引，可以對該列創建索引來提高性能

6 Key

顯示mysql實際決定使用的索引，如果沒有選擇索引，鍵是null

可以強制使用索引或者忽略索引：

強制使用索引：USE index(列名)

忽略使用索引：IGNORE INDEX(列名)

7 key_len

顯示mysql決定使用的鍵長度。如果鍵是NULL則長度為NULL。

注意：key_len 是確定了mysql將實際使用的索引長度

8 ref

顯示使用哪個列或常數與key一起從表中選擇行

9 rows

顯示mysql認為它執行查詢時必須檢查的行數

10 extra

關于MYSQL如何解析查詢的額外信息。Using temporary和Using filesort，意思MYSQL根本不能使用索引，結果是檢索會很慢

說明：extra列返回的描述的意義

Distinct ：一旦mysql找到了與行相聯合匹配的行，就不再搜索了。

Not exists ：mysql優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行，就不再搜索了。

Range checked for each Record(index map:#) ：沒有找到理想的索引，因此對從前面表中來的每一個行組合，mysql檢查使用哪個索引，并用它來從表中返回行。這是使用索引的最慢的連接之一。

Using filesort ：看到這個的時候，查詢就需要優化了。mysql需要進行額外的步驟來發現如何對返回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行。

Using index ：列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對表的全部的請求列都是同一個索引的部分的時候。

Using temporary ：看到這個的時候，查詢需要優化了。這里，mysql需要創建一個臨時表來存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上。

Where used ：使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會發生，或者是查詢有問題。

使用explain查看sql執行計劃后，我們主要先看下type屬性，表示連接的類型，如果是ALL這種那就需要優化了，

再看下possible_key屬性，表示可以使用的索引，如果沒有則為null，key屬性表示mysql實際決定使用的索引，如果沒有選擇索引，鍵是null，

rows 表示mysql認為它執行查詢時必須檢查的行數，行數越多效率越低。

---

篇幅有限，關于sql方面的優化就介紹到這了，后面會分享更多關于優化方面的內容，感興趣的朋友可以關注下！！