目錄
MySQL之SQL優化詳解（二）
1. SQL的執行順序
1.1 手寫順序
1.2 機讀順序
2. 七種join
3. 索引
3.1 索引初探
是什麼： 排好序的快速查詢資料結構
兩個主要的索引結構： B+tree 索引和雜湊索引。
如何建： 1. ALTER TABLE table_name ADD INDEX index_name (column_list); 2. CREATE INDEX index_name ON table_name (column_list);
優點: 類似大學圖書館建書目索引，提高了檢索效率，降低了資料庫IO，同時還可以通過索引進行排序，降低資料排序的成本，降低了CPU的消耗
缺點: 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行 insert、update和delete，因為更新表時不僅要儲存資料，還要儲存一下索引檔案每次更新新增了索引列的欄位。
3.2 索引分類
1.主鍵索引：主鍵是一種唯一性索引，但它必須指定為PRIMARY KEY，每個表只能有一個主鍵
ALTER TABLE table_name ADD PRIMARY KEY (column_list)2.唯一索引：索引列的所有值都只能出現一次，即必須 唯一，值可以為 空
ALTER TABLE table_name ADD UNIQUE (column_list)3.普通索引：基本的索引型別，值可以為空，沒有唯一性的限制
ALTER TABLE table_name ADD INDEX index_name (column_list);4.全文索引： 全文索引的索引型別為 FULLTEXT，全文索引只能建立在CHAR、VARCHAR、TEXT型別的欄位上。查詢資料量較大的字串型別欄位時，使用全文索引可以提高查詢速度
ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column_list);
3.3 建與不建
對於MySQL的索引建立，我們經常有疑慮，那麼什麼時候該建什麼時候不該建呢？
哪些情況需要建立索引
主鍵自動建立唯一索引
頻繁作為查詢條件的欄位應該建立索引
查詢中與其它表關聯的欄位，外來鍵關係建立索引
查詢中排序的欄位，排序欄位若通過索引去訪問將大大提高排序速度
查詢中統計或者分組欄位
哪些情況不需要建索引
- 頻繁更新的欄位不適合建立索引
where條件用不到的欄位不適合建立索引- 注意，如果某個資料列包含許多重複的內容，為它建立索引就沒有太大的實際效果
4. 效能分析Explain
Explain簡稱執行計劃，使用Explain關鍵字可以模擬優化器執行SQL查詢語句
用法：explain + SQL
1. id：select查詢的序列號,包含一組數字，表示查詢中執行select子句或操作表的順序
① id 相同執行順序由上至下
② id 不同，如果是子查詢，id的序號會遞增，id值越大優先順序越高，越先被執行
③ id相同不相同，不相同
注：id如果相同，可以認為是一組，從上往下順序執行；在所有組中，id值越大，優先順序越高，越先執行
2. select_type：查詢的型別，主要是用於區分 普通查詢、聯合查詢、子查詢等的複雜查詢
- simple：簡單的
select查詢,查詢中不包含子查詢或者union - primary：查詢中若包含任何複雜的子部分，最外層查詢則被標記為
- subquery：在
select或where列表中包含了子查詢 - derived：在
from列表中包含的子查詢被標記為 derived(衍生) - union：若第二個
select出現在之後，則被標記為union（若union包含from子句的子查詢中,外層select將被標記為：derived） - union result：從
union表獲取結果的select
3. table：顯示這一行的資料是關於哪張表的
4. type：訪問型別排列，顯示查詢使用了何種型別
從好到壞，system > const > eq_ref > ref > range > index > all
- system：表只有一行記錄（等於系統表），這是const 型別的特列，平時不會出現，這個也可以忽略不計
- const：表示通過索引一次就找到了，const 用於比較 primary key或者unique索引
- eq_ref：唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配。常見於主鍵或唯一索引掃描
- ref：非唯一性索引掃描，返回匹配某個單獨值的所有行，本質上也是一種索引訪問
- range：只檢索給定範圍的行,使用一個索引來選擇行。key 列顯示使用了哪個索引，一般就是在你的
where語句中出現了 between、<、>、in等的查詢 - index：Full Index Scan，index與 all 區別為 index 型別只遍歷索引樹
- all：Full Table Scan，將遍歷全表以找到匹配的行
5. possible_key：顯示可能應用在這張表的索引，一個或多個。(但不一定被實際應用)
6. key：實際使用的索引，如果為null，則沒有使用索引。
查詢中若使用了覆蓋索引，則該索引與查詢的select欄位重疊
7. key_len：表示索引中使用的位元組數
8. ref：顯示索引的哪一列被使用，如果可能的話，是一個常數，哪些列或常量被用於查詢索引列上的值
注：由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一個常量，即 'ac'
9. rows：根據表統計資訊及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數
10. extra：包含不適合在其他列中顯示但十分重要的額外資訊
- Using filesort (劣)： mysql 會對資料使用一個外部的索引排序（檔案排序），而不是照表內的索引順序進行讀取
- Using temporary (劣)：使了用臨時表儲存中間結果,MySQL在對查詢結果排序時使用臨時表。常見於排序
order by和分組查詢group by
- Using index (優)：表示相應的
select操作中使用了覆蓋索引(Covering Index)，避免訪問了表的資料行，效率不錯！ - Using where：表明使用了
where過濾 - Using join buffer：表明使用了連線快取
- impossible where：
where子句的值總是false，不能用來獲取任何資料
- select tables optimized away：
select操作已經優化到不能再優化了（MySQL根本沒有遍歷表或索引就返回資料了 - distinct：在select部分使用了
distinc關鍵字
心法：
針對explain命令生成的執行計劃，這裡有一個檢視心法。我們可以先從查詢型別type列開始檢視，如果出現all關鍵字，後面的內容就都可以不用看了，代表全表掃描。再看key列，看是否使用了索引,null代表沒有使用索引。然後看rows列，該列用來表示在SQL執行過程中被掃描的行數，該數值越大，意味著需要掃描的行數越多，相應的耗時越長，最後看Extra列，在這列中要觀察是否有Using filesort 或者Using temporary 這樣的關鍵字出現，這些是很影響資料庫效能的。