sql優化總結--部落格
第一次自己寫部落格，以後要堅持每掌握一個技能點，就要寫一篇部落格出來，做一個不滿足于一個
隻會寫if...else的程式員。
最近三個月入職了一家新的公司，做的是CRM系統，将公司多個平台的資料同步到CRM，進行
查詢，統計和彙總。由于資料量比較龐大，大部分表資料上百萬，甚至有的表資料上千萬。是以
在系統中做sql優化比較多，特此寫一篇部落格總結一下關于sql優化方面的經驗。
--導緻查詢緩慢的原因
1、資料量過大
2、表設計不合理
3、sql語句寫得不好
4、沒有合理使用索引
-- 針對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認為它執行查詢時必須檢查的行數，行數越多效率越低。
--索引類型
主鍵索引，唯一索引，組合索引，普通索引
--什麼是索引
資料庫索引是資料庫管理系統中的一個排序的資料結構，以協助快速查詢，更新資料庫表中資料，索引的實作通常使用B樹(B-tree)以及其變種B+tree（一些高效率的算法）
--使用索引時有些不生效的情況
1、使用like關鍵字模糊查詢時，% 放在前面索引不起作用，隻有“%”不在第一個位置，索引才會生效（like '%文'--索引不起作用）
2、使用聯合索引時，隻有查詢條件中使用了這些字段中的第一個字段，索引才會生效
3、使用OR關鍵字的查詢，查詢語句的查詢條件中隻有OR關鍵字，且OR前後的兩個條件中的列都是索引時，索引才會生效，否則索引不生效。
4、盡量避免在where子句中使用!=或<>操作符，否則引擎将放棄使用索引而進行全表掃描。
5、對查詢進行優化，應盡量避免全表掃描，首先應考慮在where以及order by涉及的列上建立索引。
6、應盡量避免在 where 子句中對字段進行表達式操作，這将導緻引擎放棄使用索引而進行全表掃描。如：
select id from t where num/2=100
應改為:
select id from t where num=100*2
7、盡量避免在where子句中對字段進行函數操作,将導緻引擎放棄使用索引而進行全表掃描。
8、不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統将可能無法正确使用索引。
9、并不是所有的索引對查詢都有效，sql是根據表中的資料來進行查詢優化的，當索引列有大量資料重複時，sql查詢不會去利用索引，如一表中有字段
sex，male,female幾乎個一半，那麼即使在sex上建立了索引也對查詢效率起不了作用。
10、索引并不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，
因為 insert 或 update 時有可能會重建索引，是以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，
若太多則應考慮一些不常使用到的列上建的索引是否有 必要。
11、盡量使用數字型字段，若隻含數值資訊的字段盡量不要設計為字元型，這會降低查詢和連接配接的性能，并會增加存儲開銷。
這是因為引擎在處理查詢和連接配接時會 逐個比較字元串中每一個字元，而對于數字型而言隻需要比較一次就夠了。
12、mysql查詢隻使用一個索引，是以如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。
是以資料庫預設排序可以符合要求的情況下不要使用排序操作，盡量不要包含多個列的排序，如果需要最好給這些列建複合索引。
13、order by 索引 ，不起作用的問題（除了主鍵索引之外）：
1、 如果select 隻查詢索引字段，order by 索引字段會用到索引，要不然就是全表排列；
2、如果有where 條件，比如where vtype=1 order by vtype asc . 這樣order by 也會用到索引！