【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), 索引下推

參考：

索引下推（5.6版本+）
MySQL--索引條件下推優化
mysql索引篇之覆寫索引、聯合索引、索引下推

在前面提到了一個疑問：
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：整理完一看，并沒有寫或整理出多少東西...但磨磨唧唧也花費了蠻多時間）

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/81206.html

標籤：MySQL

上一篇：MySQL基礎

下一篇：[MySQL]mysql binlog回滾資料

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