一. 為什麼要進行sql優化？
作為一名開發，在你入職一家新的公司時，最常見的需求就是對于一些老程式的疊代和性能優化，而優化的關鍵除了一些業務上的優化和代碼的優化之外，最常見的就是一些複雜腳本的優化，這時候勢必要求我們能夠熟練掌握explain這個關鍵字的使用，當然對于優化前、優化後的接口性能最好進行測試和記錄，讓自己這次優化的效果可以直覺的展現出來，那麼筆者今天就基于sql腳本的優化先談下explain這個關鍵字。
二. SQL 分析
explain 執行計劃分析
explain 用于分析 sql 查詢語句的執行計劃，包括執行順序、是否走索引、用到了哪些優化等，是以 explain 是用于 sql 優化的基礎工具，我們先來分析下 explain 工具。
explain 的使用非常簡單，直接加在查詢語句的前面運作即可：
explain select * from t1 limit 1;
我們來一個字段一個字段分析：
id
id 列是 select 的辨別符，有幾個 select 就有幾個執行計劃，id 越大的優先級越高（越先執行），如果 id 一樣，則從上往下執行。
select_type
select_type 表示對應查詢語句的查詢類型，種類較多，官網上的介紹：
table
table 表示執行計劃通路的哪張表，或聚合哪幾個執行計劃的結果集
partitions
partitions表示查詢記錄所在的分區，如果是非分區表顯示 NULL。
type
type 清單示關聯類型或通路類型，是進行 sql 分析和優化的關鍵字段。
性能由好到差分别是：system > const > eq_ref > ref > index_merge > range > index > ALL。
system
該類型代表查詢的是系統表且隻有一行記錄，可以看作是 const 類型的特例，在我們日常 sql 中不會出現。
const
const 代表該表最多隻有一行比對，出現在使用主鍵索引或唯一索引和常量比較的情況，例如：
-- 字段 id 為主鍵 explain select * from t1 where id = 320001;
-- 字段 a 為唯一索引 explain select * from t2 where a = 111;
eq_ref
eq_ref 出現在關聯查詢時使用的索引是主鍵索引或唯一索引（不為null）的情況下，例如：
explain select * from t1 join t2 on t1.id=t2.id;
ref
ref 出現在使用普通索引等值查詢或者唯一聯合索引的部分字首等值查詢的情況，例如：
-- 字段 a 是普通索引 explain select * from t1 where a = 111;
index_mergeindex_merge 出現在使用 or 的字段是不同的索引，MySQL 分别走索引樹将結果聚合傳回，例如：
-- a, b 都為索引 explain select * from t2 where a = 123 or b = "1234";
rangerange 代表通過索引進行範圍查詢，range 出現情況比較多：當對索引字段使用 <、>、<=、>=、between、is null、in 條件内元素個數大于 1、模糊查詢走索引（滿足字首比對），例如：
explain select * from t1 where id > 111;
一般在開發中我們 sql 的 type 等級至少要到 range。
當 sql 的 type 等級為 index 或 ALL 時，我們就需要進行優化了，二者都可以算全表掃描，唯一的差別是 index 檢索的是整個非聚簇索引樹（非主鍵索引樹），ALL 檢索的是整個聚簇索引樹（主鍵索引樹），是以 index 的性能要比 ALL 好一些，因為非聚簇索引樹的葉子結點存的是主鍵id，而聚簇索引葉子結點存的是整條記錄，是以優化器會選擇小的索引樹進行檢索速度會快一點。當沒有條件字段非索引字段或索引失效（下面分析）或進行全表掃描。
index 等級例如：
explain select a from t1;
ALL 等級例如：
-- 字段 c 非索引字段 explain select * from t1 where c = "111";
possiable_keyspossiable_keys 代表該條語句可以走哪些索引，如果為 null 則代表無可用索引；語句最終通過哪個索引檢索是在 key 字段進行展示。如果 where 條件有多個且都是索引，possiable_keys 就會展示這些索引名稱， 例如：
-- 字段 a 和 字段 b 都為普通索引 explain select * from t1 where a = 123 and b = "2222";
key
key 列代表 MySQL 最終決定使用的索引。key 為 null 代表沒有使用索引，如果想強制使用或不使用某個索引可以通過 force index 或 ignore index 實作。
key_len
key_len 表示使用的索引長度，如果 key 為 null，那麼該字段也為 null。
key_len計算規則如下： 字元串 char(n)：n位元組長度 varchar(n)：2位元組存儲字元串長度，如果是utf-8，則長度 3n + 2 數值類型 tinyint：1位元組 smallint：2位元組 int：4位元組 bigint：8位元組 時間類型 date：3位元組 timestamp：4位元組 datetime：8位元組
ref
ref 顯示的那些列或常量與索引進行比較。
rows
rows 表示預計掃描的行數，官方給出的誤差比例為 40% 。
filtered
filtered 代表按照條件過濾後剩餘資料占總資料的預估百分比；如果是100% ，則代表沒有過濾。
Extra
Extra 列會輸出一些額外資訊，包括該語句用到了哪些優化。Extra 列的資訊較多，我們簡單介紹下：
Using index
Using index 代表語句用到了覆寫索引的優化；覆寫索引就是查詢的列通過索引樹就可以傳回，不需要額外的回表操作；是性能高的展現，例如：
-- 字段 a 是普通索引 explain select id from t1 where a = 111;
a 是普通索引，也就是非聚簇索引，葉子結點存儲的 id，是以可以直接将 id 傳回而不需要回表。
Using index condition
Using index condition 代表語句用到了索引下推的優化；索引下推一般出現在聯合索引中，當對聯合索引進行檢索時且 where 的條件是聯合索引中的字段，可以直接通過索引樹完成過濾省去回表過濾的操作，例如：
-- 聯合索引 (b,a) explain select * from t1 where b = "21313" and a != 123;
b，a 兩個字段構成聯合索引，通過 b = "21313" 在聯合索引樹上快速檢索，針對比對到的值直接判斷 a 是否滿足不等于 123，不滿足就過濾。
Using where一般沒有用到索引的條件查詢會展示該資訊，出現該資訊且 type 為 ALL 則需要進行優化（對條件字段加索引），例如：
-- 字段 c 非索引字段 explain select * from t1 where c = "123";
Using temporary
Using temporary 代表需要建立一個臨時表來儲存結果，一般出現在對非索引字段使用分組查詢（group by）或去重查詢（distinct），例如：
-- 字段 c 非索引 explain select c from t1 group by c;
使用臨時表的性能會比較差，我們對 c 加索引即可優化該情況。
Using filesort
Using filesort 代表語句查詢的資料需要單獨進行排序，而不是通過索引樹完成，是以該情況通常出現進行排序的字段為非索引字段，例如：
-- 字段 c 非索引 explain select * from t1 order by c limit 1000;
。
三. 索引失效的場景及原因
針對慢 SQL 進行優化最常見的手段就是對查詢條件、排序字段、分組字段加索引。
表 t1 有 500 萬的資料，我們看下有無索引的耗時對比：
-- 無索引，耗時：3900 ms select * from t1 where c = "123"; -- 有索引，耗時：0.45 ms select * from t1 where b = "123";
差别還是很明顯的，我們來分析下MySQL是如何進行資料查找的。
在分析之前，先了解下以下内容
innodb 表是索引組織表，由聚簇索引樹和非聚簇索引樹組成；
聚簇索引和非聚簇索引的差別是：聚簇索引的葉子結點存儲的整條記錄，非聚簇索引的葉子結點隻存儲主鍵的值；
這些索引樹是以 B+ 樹的結構存儲在磁盤上的，B+ 樹可以看作是一個平衡多叉搜尋樹，非葉子結點存儲索引，葉子結點存儲資料；
MySQL 索引樹的葉子節點是一個資料頁（16kb），資料頁上通常會有多條記錄，資料頁是 MySQL 的基本操作單元，一次讀取會把整個的資料頁都加載到記憶體，葉子節點（資料頁）之間通過指針相連組成雙向連結清單。
然後我們來分析下有無索引的查找是如何工作的。
· 無索引：沒有索引隻能到聚簇索引樹上葉子節點組成的連結清單從頭開始周遊，每經過一個資料頁都是一次磁盤IO（假設buffer pool中沒有該資料頁），最終需要掃描整張表才能完成查找。
· 有索引：
select * from t1 where age = 12;
就看上面的語句：
1. 假設 age 是主鍵，直接從聚簇索引樹的根節點以 O(logN) 的時間複雜度快速查找到記錄所在的資料頁，并将資料頁上符合條件的記錄直接傳回。
2. 假設 age 是普通索引，首先從 age 的非聚簇索引樹上以 O(logN) 的時間複雜度快速查找到對應資料頁，并将資料頁上符合條件的主鍵值取出，到聚簇索引樹上根據主鍵查找記錄，将結果傳回。
是以，我們需要讓語句走索引掃描而不是進行全表掃描。
索引失效的情況
雖然加了索引，但是也有很多情況會讓索引失效。但是所有索引失效的原因基本上都是沒有利用到 B+ 樹快速存取的優勢；要想防止慢 SQL，就需要避免索引失效，我們來看下索引失效有哪些情況。
1. 條件字段進行函數或計算操作
當對條件字段進行函數或計算操作時會導緻索引失效，例如：
-- name 是索引 explain select * from t1 where length(name) = 3;
using where、ref=null、key= null、key_len= null、type=all皆表明此時沒有用到索引
。
為什麼對字段進行函數或計算操作就會導緻索引失效？
其實原因很簡單，B+ 樹的快速定位的能力來源于兄弟節點的有序性，是以當對字段進行函數操作時，有可能會破壞索引的有序性，無法通過 B+ 樹進行快速定位，是以優化器就會放棄走樹搜尋。
需要強調一點，這裡說的索引失效并不是說不走索引，而是不走樹搜尋，例如下面這種情況還是會走 b 字段的索引樹：
explain select id from t1 where length(b) = 3;
就是我們在 explain 分析那塊講的 type 為 index 的情況：在能在索引樹上直接拿到結果的前提下選擇更小的索引樹（當然也是掃描索引樹所有葉子節點）。
2. 隐式類型轉換
當我們使用字元串類型的字段和 int 整型進行比較時索引會失效，例如：
-- 字段 b 為 varchar 類型并且是索引 explain select * from t1 where b = 123;
explain 執行計劃中的 type 為 ALL 代表全表掃描，possible_keys 列顯示可能會走 idx_b_a 這個索引，但是 key 列為 NULL，是以優化器放棄執行樹搜尋而進行全表掃描。
其實出現這種情況和函數操作的原因一樣，都是對字段進行函數操作導緻無法快速定位；在 MySQL 底層，如果字元串和整型比較，是會先把字元串轉換為整型再繼續比較，那麼這個語句在 MySQL 裡就會轉換成：
-- CAST(b AS signed int) 代表将 b 轉換為 int 型 select * from t1 where CAST(b AS signed int) = 123;
把整型類型的字段和字元串進行比對會導緻索引失效嗎？
-- a 為 int 型且是索引 explain select * from t1 where a = "123456";
發現是走索引的，其實我們上面說的原則就可以解釋了：如果字元串和整型比較，是會先把字元串轉換為整型再繼續比較；上面的情況是對值進行函數操作而不是字段，對值操作完後是可以在樹上快速搜尋的。
3. 隐式字元編碼轉換
當進行關聯查詢時兩個字段的編碼類型不一緻，也會導緻索引失效；比如一個utf8，另一個是utf8mb4，如果進行關聯查詢，也會導緻索引的失效，原因和上面基本一樣，需要将字元類型轉換成比對的類型再繼續搜尋。
4. 聯合索引不滿足最左字首原則
當使用聯合索引時，必須滿足最左字首原則，否則索引會失效，例如：
-- 聯合索引 (b, c) explain select * from t2 where c = '123'
聯合索引的 B+ 樹結構是按照定義時字段順序排序的，是以 where 條件裡需要出現聯合索引中最左邊 N 個字段才可以走索引，這就是最左字首原則。
5. 模糊查詢以%開頭的
當使用模糊查詢時以 % 開頭會造成索引失效，這種情況和第四個一樣是沒有滿足最左字首原則，例如：
-- b 為索引 explain select * from t1 where b like '%123';
當我們的語句滿足最左字首就可以走索引樹快速檢索，例如：
explain select * from t1 where b like '123%';
6. or 一個非索引字段
當使用 or 的條件查詢語句其中有個字段非索引時會導緻所有索引失效，例如：
-- a 為索引，c 非索引 explain select * from t1 where a = 123 or c = "1234";
這種情況，優化器認為再走索引的收益不大，因為有個字段非索引，一定要全表掃描，是以索引會失效。
8. MySQL 優化器不選擇索引
還有一些情況需要具體情況具體分析，優化器會判斷走索引的收益來決定是否走索引：(1) 索引條件不等于判斷（不一定）(2) 字段允許為空時，條件為空（不一定）(3) 走全表比索引更快
四. SQL 優化實戰
count 的選擇
先說結論，性能排行：count(*) ≈ count(1) > count(主鍵id) > count(非空字段) > count(普通字段)
InnoDB 引擎計算 count 時需要把資料一行一行讀出來然後累加，隔壁的 MyISAM 是直接存總數在引擎裡，需要直接傳回就可以了，那為什麼 InnoDB 不存儲 count 總數呢？
我們來分析下為什麼不同的 count(?) 性能不同。
count() 其實是一個聚合函數，代表的是括号裡字段不為 null 的數量。
count(id)：MySQL 會周遊整張表把每一行的 id 取出來傳回給 server 層。server 層拿到 id 後認為不可能為空，按行累加。
count(1)：MySQL 周遊整張表但是不取值，server 層直接按行累加。
count(字段)：
1. 如果字段非空，一行行從記錄裡讀取出來後無需判斷是否為 null 累加就可以了；2. 如果字段可以為空，一行行從記錄裡讀取出來後，需要再判斷下是否為 null，不為 null 再累加。
count(*)：MySQL 對 count(*) 專門進行優化，不需要取值直接累加，和 count(1) 一樣的效果。
綜上所述，count(1) 和 count(*) 的性能最好，是以以後無腦使用 count(*) 進行總數查詢就可以了。
當然，如果隻是想判斷滿足條件的資料是否已經存在時也可以配合limit 1使用；
分頁優化案例
我們在業務開發中，為了提高查詢性能會使用分頁的手段，例如：
-- c 非索引，耗時 4260 ms explain select * from t1 where a > 22222 order by c limit 0, 20;
發現耗時有點大，我們 explain 看下執行計劃：
看到 Extra 資訊裡有：Using filesort 使用外部排序，并且 key 列為空沒有走索引排序。
我們來了解下 Using filesort 的工作流程：
MySQL 會給每個線程配置設定一塊記憶體專門用來排序，稱為 sort_buffer，如果需要排序會把所有符合條件的資料都放進 sort_buffer 裡面，然後根據需要排序的字段進行快速排序，當 sort_buffer 放不下時還會啟用外部臨時檔案，進行多路歸并排序，性能肯定會很差。
我們将排序字段 c 換成索引字段 b 試試看：
-- b 為索引，耗時 0.6 ms select * from t1 where a > 22222 order by b limit 0, 20;
再次使用explain,可以發現，通過索引直接排序而不是外部排序。我們把分頁的深度加大執行看看：
-- 耗時 10900 ms select * from t1 where a > 22222 order by b limit 400000, 20;
随着分頁深度的加大，性能也下降的很厲害，什麼原因導緻的？
我們再 explain 看下啥情況：
發現 key 為 NULL 并且 Extra 又出現了 Using filesort，又不走索引排序了！
limit a, b 分頁的邏輯是這樣的：取出 a + b 條記錄，舍棄前 a 條記錄。由于查詢數量過多（回表次數也增多），優化器選擇放棄索引走全表掃描。
針對這種深度分頁該如何優化呢？
1. 強制索引
既然優化器沒走索引，我們用 force index 強制走索引試試：
-- 耗時 1370 ms select * from t1 force index (idx_b_a) where a > 22222 order by b limit 400000, 20;
強制走索引後提升還是很明顯的，但是還是有點慢。
2. 子查詢
我們再來分析一下分頁的耗時點：limit a, b 分頁的邏輯是這樣的：取出 a + b 條記錄，舍棄前 a 條記錄；故還是需要取出完整的 400020 條資料，需要讀取大量資料頁面，是非常消耗 IO 資源的；我們從這點出發，通過子查詢，在子查詢裡進行條件查詢和分頁，這樣隻會取出 400020 個 id 字段（比整條記錄小很多），最後根據 id 查詢：
-- 耗時 66.5 ms select * from t1 join (select id from t1 where a > 22222 order by b limit 400000, 20) t using(id);
效果太明顯了！耗時直接降到 60 ms 以内！
3. Sql語句優化
① Select * from a left join b on a.bid = b.id where a.age>0;
此時推薦寫法
Select c.* from (select * from a where a.age >0)c left join b on a.bid = b.id;
② 推薦with的使用
③ 推薦union all的使用