【mysql】索引相關的個人總結

重點參考： "MySQL索引原理及慢查詢優化（美團技術分享網站）" ：原理、示例優化都寫的很好。 "索引很難麽？帶你從頭到尾捋一遍MySQL索引結構，不信你學不會！" ：原理寫的很好。 "【從入門到入土】令人脫髮的資料庫底層設計" ：很詳細的底層原理一定要仔細看其中講的索引原理！！！本文中都是簡 ...

重點參考：

MySQL索引原理及慢查詢優化（美團技術分享網站）：原理、示例優化都寫的很好。
索引很難麽？帶你從頭到尾捋一遍MySQL索引結構，不信你學不會！：原理寫的很好。
【從入門到入土】令人脫髮的資料庫底層設計：很詳細的底層原理

一定要仔細看其中講的索引原理！！！本文中都是簡單的總結。

參考：

1. 重點知識概括

1.1索引類型

Clustered Index(聚簇索引或聚集索引)
Secondary Index(非聚簇索引或輔助索引或二級索引，一般指的都是單列)
聯合索引，多列二級索引
首碼索引，二級索引只截取前N個字元作為索引
Covering Index(覆蓋索引)

1.2 相關原理

B+樹
最左首碼匹配原則
聯合索引的最左首碼匹配原則
Index Condition Pushdown (ICP), 索引下推

1.3 使用索引的疑問或總結

2. 索引類型

總體來說，索引類型只存在：聚簇索引和非聚簇索引（二級索引）。
聯合索引、首碼索引都是非聚簇索引中的更明確分類。
覆蓋索引（個人覺得）並不算一種索引類型，而是基於非聚簇索引的原理對查詢的一種優化方式。

“回表查詢”：
回到聚簇索引取行數據。1次回表查詢需要2次B+樹的遍歷查找，所以應該儘量避免回表（不要刻意避免，以免得不償失）。

2.1 Clustered Index(聚簇索引或聚集索引)

鍵值的邏輯順序決定了表中相應行的物理順序
葉子節點中存放了該索引對應的行記錄的完整數據（重點）
InnoDB有且只有一個聚簇索引（一般都是PK，MyISAM中都是非聚簇索引）
聚簇索引可以包含多個列（聯合索引），但使用的列越少越好

為什麼InnoDB只有一個聚簇索引，而不將所有索引都使用聚簇索引？
因為“葉子節點中存放了該索引對應的行記錄的完整數據”，如果所有索引都是聚簇索引，意味著每個葉子節點都保存一份數據，會造成數據的冗餘和資源的浪費。
哪些列索引可以是聚簇索引？
InnoDB中一般都是PK；
如果不存在PK，則會選擇唯一非空索引代替。
如果不存在唯一非空索引，則會隱式定義一個PK來作為聚集索引。
建議向聚簇索引中插入有序的值
例如，聚簇索引列是pk，建議選擇int, auto_increment，而避免使用無序的UUID。
a)無序的pk使數據存儲稀疏，這就會出現聚簇索引有可能有比全表掃面更慢
b)無序的pk新插入數據時，可能需要插入到某些列的中間，這可能導致數據頁分裂，從而移動行數據。
c)有序的pk值很好的避免了上述無序的pk帶來的問題。

2.2 Secondary Index(非聚簇索引或輔助索引或二級索引)

（一般都指的是單列索引，相對聯合索引而言）

葉子節點不包含完整的行數據
葉子節點除包含鍵值以外，還包含一個pointer（或者bookmark）用於告訴InnoDB哪裡可以找到與索引相對應的行數據（即需要回表查詢，也增加了IO次數）
非聚簇索引要遠小於聚簇索引（mysql基於此特性，會優化一些sql，例如count(*)）

為什麼叫二級索引的一種解釋
二級索引需要兩次B+樹的遍歷查找才能取到數據。
第一次通過二級索引找到索引的葉子節點，從而找到數據的主鍵（或者其聚簇索引的索引值），然後用該主鍵去聚簇索引中再次通過B+樹查找到完整的行數據。所以，“回表”會有2次B+樹的查找過程。
為什麼輔助索引使用“聚簇索引的索引值”作為pointer，而不是使用"地址值"作為pointer？
使用"地址值"帶來的好處：
1）"地址值"比"聚簇索引的索引值"占用更少的空間
2）減少了1次B+樹查找的過程。
但是，相應的需要維護輔助索引，這是一個相當困難的維護工作。
使用“聚簇索引的索引值”作為pointer時，當出現行移動或者數據頁分裂時，輔助索引不受影響（即不需要維護輔助索引）
輔助索引中的最左首碼匹配原則
單列輔助索引遇到<, <=, =, >, >=, between, like（右邊模糊）可以用到索引。
假設存在索引(col_1)，例如liek 'xxx%'是可以用到輔助索引的。

2.3 聯合索引

屬於輔助索引，只是：將多列作為索引，預設多列往右匹配。

聯合索引中的最左首碼匹配原則
聯合索引遇到範圍查詢時就停止匹配。（待商榷）
假設存在索引(a, b, c, d)，那麼where a =1 and b = 2 and c > 3 and d = 4中，a, b可以用到聯合索引。此時，創建(a, b, d, c)索引更合適，並且由於查詢優化器的優化 where中 a,b,d可以任意順序。
（擴展疑問：以上聯合索引中，c能否用到索引？參考後面提到的索引下推）
優化器對單列輔助索引與聯合索引的選擇
例如存在單列輔助索引(col_1) 和聯合索引(col_1, col_2)，在執行查詢時，優化器是選擇單列輔助索引還是聯合索引，主要還是需要結合實際SQL。
where col_1=xxx，可能會選擇單列輔助索引。（不確定，具體還是看 explain）
where col_1=xxx order by col_2，選擇聯合索引，因為col_2是在col_1的基礎上排序，避免了進行1次filesort。

2.4 首碼索引

首碼索引能有效減小索引文件的大小，提高索引的速度。
但是首碼索引也有它的壞處：
1）不能在 ORDER BY 或 GROUP BY 中使用首碼索引
2）也不能把它們用作覆蓋索引(Covering Index)。

針對2）的個人理解，首碼索引的葉子節點記錄的也只是"主鍵"和"首碼值"，需要回表才能拿到完整的值。

例如，假設需要創建 article_title列的索引，但是 article_title 可能很長（索引占用空間多），那麼可以只取article_title的前N個字元作為首碼索引。
語法：CREATE INDEX index_name ON table_name(column_name(length));

2.5 Covering Index(覆蓋索引)

InnoDB存儲引擎支持覆蓋索引，即從輔助索引中就可以得到查詢的記錄，而不需要查詢聚集索引中的記錄。因此：
1) 使用覆蓋索引可以避免回表查詢（減少了大量的IO操作）
例如，假設存在索引(col_1, col_2, col_3)，現有查詢SQL select * from table where col_1 = xx。如果在需求滿足的情況下，可以有效利用覆蓋索引來優化查詢SQL select col_1, col_2, col_3 from table where col_1 = xx。

2) 有助於統計
例如，假設存在非聚簇索引(name)和聚簇索引(id)，在執行統計查詢select count(*)時，查詢優化器可能會選擇使用非聚簇索引。因為，非聚簇索引要遠小於聚簇索引。

暫時還無法理解2)，特別是聚集索引、輔助索引、覆蓋索引、聯合索引中基於聯合索引 & count 的示例更不理解~~~

student表：PRIMARY KEY (id), KEY idx_name (name), KEY idx_school_age (school,age)`

執行sql：select count(*) from student
優化器會選用 idx_name 這個輔助索引。（具體看 explain）

執行SQL：select count(*) from student where age > 10 and age < 15
優化器會選用 idx_school_age 這個輔助索引。（具體看 explain）

3. 索引中的原理

3.1 B+樹

MySQL索引原理及慢查詢優化（美團技術分享網站）：原理、示例優化都寫的很好。
索引很難麽？帶你從頭到尾捋一遍MySQL索引結構，不信你學不會！：原理寫的很好。
【從入門到入土】令人脫髮的資料庫底層設計：很詳細的底層原理
再次，再次，再次通過這3篇大神的文章簡單理解就好。如果想深入理解，再baidu/google。

3.2 （單列輔助索引的）最左首碼匹配原則

忽略。

3.3 聯合索引的最左首碼匹配原則

相比單例輔助索引的最左首碼匹配原則，聯合索引是從左往右依次比較列。
例如col_1, col_2, col_3, col_4，先比較col_1，再比較col_2，以此類推。

3.4 Index Condition Pushdown (ICP), 索引下推

參考：

在前面提到了一個疑問：
where a = 1 and b = 2 and c > 3 and d = 4在已有聯合索引(a, b, c, d)時，c/d能否用到聯合索引？

在主要閱讀的的2篇文章（美團大佬、java知音）都說的是：

最左首碼匹配原則，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配。
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，
如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。

其中並未提到c，而且個人覺得 c&d 都可以用到索引（只是不知道其性能如何）。
針對這疑問，我看到了索引下推。

例如以上SQL可能有2種執行可能：
1）假設 c&d 都沒用到索引，根據聯合索引查詢到滿足 a&b 的條件，然後就回表找到所有行數據，再進行遍歷篩選出c > 3 and d = 4的數據行。

2）假設 c&d 都用到了索引，那麼最後回表的數據行一定小於等於 1)中回表的數據行，這就是mysql的索引下推

mysql預設啟用索引下推，可以通過變數來修改：
SET optimizer_switch = 'index_condition_pushdown=off';

註意：

a) 索引下推只能用於二級索引。（聚簇索引包含了行數據，這時候索引下推並不會起到減少回表操作的效果）

b) 索引下推一般可用於所求查詢欄位（select列）不是/不全是聯合索引的欄位，查詢條件為多條件查詢且查詢條件子句（where/order by）欄位全是聯合索引。（沒理解~~）

備註：
個人並不確定是 c&d 都用到索引，還是只有 c 用到索引，d未用到索引。

4. 索引使用中的疑問總結

(ps: cnblog的markdown對於 1.和-的解析貌似有錯誤，導致下麵的序號是亂的)

索引不一定能提高查詢速度，甚至可能比不存在索引時更慢！
一次查詢只能用到1個索引
如果多列查詢存在多個索引，查詢優化器一般選擇區分度高的索引列。
區分度，簡單公式：count(distinct col) / count(*)。
意味著通過索引列可以返回更少的rows（回表查詢的行數更少）
具體需要看實際數據，比如假設is_download只存在true/false，當下載完成後將false改為true。
此時實際業務數據是很少存在false，當存在大量查詢false的時候，可以創建索引。
覆蓋索引擁有更高效率和性能
無法使用索引的情況

<>, !=, not in
對欄位進行函數運算
索引欄位存在null
字元串不加單引號，例如phone是varchar類型但sql是where phone=13800010002

創建索引的原則

最左首碼匹配原則
區分度高的列（美團文章提到）一般需要join的欄位都要求是0.1以上。
儘量的擴展索引，不要新建索引。
聯合索引 & 最左首碼匹配原則的優化，當存在(col_a, col_b)的聯合索引後，大多情況下不需要再創建a索引
例如書 “SQL Tuning“，如果選擇性超過 20% 那麼全表掃描比使用索引性能更優。

基於新增/修改索引來優化查詢時，不能只看到當前需要優化的SQL，還需要結合該表的其餘查詢SQL來綜合分析。
例如，當前待優化sql創建了聯合索引(col_1, col_2, col_3, col_4)，但是可能另外一條sql可能需要聯合索引(col_1, col_2, col_4)。所以，最終聯合索引(col_1, col_2, col_4, col_3)更適合。
聯合索引，如何決定其col的順序？
最左前匹配原則&列的區分度的理解運用，當然還要結合實際SQL。
範圍查詢是否會使用索引（例如 like、between-and、in）？
可以使用到索引（但具體還是要看寫法）。
性別欄位是否需要創建索引（十萬級以上的表，只有男/女）？
為什麼重覆值高的欄位不能建索引
 mysql千萬級大表，關於性別及年齡欄位是否需要加索引？
沒有絕對，要根據實際的數據。
例如1億的數據，其中只有10萬的"男"數據，並且總是查詢少的那部分數據，那麼存在索引的效果更好。

（ps：整理完一看，並沒有寫或整理出多少東西...但磨磨唧唧也花費了蠻多時間）