《MySQL自學(xué)筆記》之索引

索引的分類

按照類型分類

主鍵索引 : 一個表的主鍵就是一種特殊的唯一索引戴质，不能有控制，一個表只能有一個主鍵
普通索引 : 是最基本的索引霞赫，它沒有任何限制
唯一索引 : 索引列的值必須唯一腮介，但允許有空值，如果是組合索引端衰，則列值的組合必須唯一
全文索引 : 主要用來查找文本中的關(guān)鍵字叠洗，而不是直接與索引中的值相比較
按照數(shù)量還可以分成:單列索引和組合索引

索引的操作

創(chuàng)建索引

create [UNIQUE|FULLTEXT|SPATIAL|不填(不填表示普通索引)] index 索引名稱 [USING index_type(索引類型:默認是Btree)] on 表名(列1(長度),列2...)
#列里面可以指定長度,指定了特定的長度就表示用了前綴索引

刪除索引

alter table 表名 drop index 索引名;

查看索引

show index from 表名 [FROM db_name]

前綴索引

對于BLOB,TEXT或很長的VARCHAR類型列如果要作為索引顯然是不合適的蝙眶，這時候可以用前綴索引，MySQL不允許索引這些列的完成長度模闲。

創(chuàng)建前綴索引

只需要在創(chuàng)建索引的時候加上長度

create [UNIQUE|FULLTEXT|SPATIAL|不填(不填表示普通索引)] index 索引名稱 [USING index_type(索引類型:默認是Btree)] on 表名(列1(長度),列2...)
#列里面可以指定長度,指定了特定的長度就表示用了前綴索引

如何決定前綴索引的長度

1.首先先計算得到完整列的比例

select count(DISTINCT name) / count(*) from student;
#查詢不重復(fù)列跟總列的比例忘嫉，例如得到值為0.0322

2.繼續(xù)執(zhí)行

select count(DISTINCT LEFT(name,3)) / count(*) from student;
select count(DISTINCT LEFT(name,4)) / count(*) from student;
#算出來的值如果接近于0.0332則可以選擇對應(yīng)的長度

前綴索引的缺點

Mysql無法使用前綴索引做group by , order by,也無法使用其做覆蓋掃描

多列索引(組合索引)

多列索引是指一個索引中使用了多個列，不是多個單個索引

索引最左匹配原則

如果是聯(lián)合索引案腺，那么key也由多個列組成，同時劈榨，索引只能用于查找key是否存在（相等），遇到范圍查詢(>同辣、<拷姿、between、like左匹配)等就不能進一步匹配了旱函，后續(xù)退化為線性查找响巢。因此棒妨，列的排列順序決定了可命中索引的列數(shù)。
例子:

如有索引(a, b, c, d)券腔，相當(dāng)于我們建立了(a),(a,b),(a,b,c)索引,查詢條件a = 1 and b in (2) and c > 3 and d = 4，則會在每個節(jié)點依次命中a颅眶、b、c涛酗，無法命中d。(很簡單：索引命中只能是相等的情況商叹，不能是范圍匹配)
#不需要糾結(jié)=和in的順序，mysql會自動優(yōu)化以匹配盡可能多的索引

如何選定哪個列可以作為最左的索引卵洗，這里有一個例子可以參考：
如下，有一個表需要選擇customer_id或者staff_id誰作為最左邊的索引过蹂，根據(jù)下面的語句:

select count(distinct customer_id)/count(*),count(distinct staff_id)/count(*),count(*) from student;
# count(distinct customer_id)/count(*) : 0.0324
#count(distinct staff_id)/count(*) : 0.0001
#count(*) : 1453566

如下面數(shù)據(jù)可知customer_id的不可重復(fù)性(基數(shù))更高酷勺，適合作為第一項索引

image.png

聚簇索引

聚簇索引的兩大特點:

使用記錄主鍵值的大小進行記錄和頁的排序
1. 頁內(nèi)的記錄是按照主鍵的大小順序排成一個單向鏈表
2. 各個存放用戶記錄的頁也是根據(jù)頁中記錄的主鍵大小順序排成一個雙向鏈表
  3.各個存放目錄項的頁也是根據(jù)頁中記錄的主鍵大小順序排成一個雙向鏈表
B+樹的葉子節(jié)點存儲的是完整的用戶記錄
我們把具有這兩種特性的B+樹稱為聚簇索引，所有完整的用戶記錄都存放在這個聚簇索引的葉子節(jié)點處甚亭。這種聚簇索引并不需要我們在MySQL語句中顯式的去創(chuàng)建击胜，InnoDB存儲引擎會自動的為我們創(chuàng)建聚簇索引亏狰。另外有趣的一點是偶摔，在InnoDB存儲引擎中骚揍，聚簇索引就是數(shù)據(jù)的存儲方式（所有的用戶記錄都存儲在了葉子節(jié)點）啰挪，也就是所謂的索引即數(shù)據(jù)

索引優(yōu)化建議

盡可能的擴展索引嘲叔，不要新建立索引。比如表中已經(jīng)有了a的索引锰什，現(xiàn)在要加（a,b）的索引丁逝，那么只需要修改原來的索引即可,為什么不新建一個b列索引呢汁胆，因為mysql查詢只能使用一個索引霜幼，如果條件是 a =1 and b=2 實際上也只能用到其中一個索引
最左前綴匹配原則。這是非常重要铸题、非常重要琢感、非常重要（重要的事情說三遍）的原則，MySQL會一直向右匹配直到遇到范圍查詢（>,<,BETWEEN,LIKE）就停止匹配
盡量選擇區(qū)分度高的列作為索引驹针，區(qū)分度的公式COUNT(DISTINCT col) / COUNT(*)。表示字段不重復(fù)的比率饮六，比率越大我們掃描的記錄數(shù)就越少
索引列不能參與計算，比如喜滨，F(xiàn)ROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引
單個多列組合索引和多個單列索引的檢索查詢效果不同，因為在執(zhí)行SQL時棒口，MySQL只能使用一個索引辜膝，會從多個單列索引中選擇一個限制最為嚴格的索引

InnoDB索引原理

InnoDB的存儲結(jié)構(gòu)

$\color{red}{InnoDB}$ 是一個將表中的數(shù)據(jù)存儲到磁盤上的存儲引擎。他的存儲方式是將數(shù)據(jù)劃分為若干個頁厂抖，以頁作為磁盤和內(nèi)存之間交互的基本單位，InnoDB中頁的大小一般為 $\color{red}{16KB}$ 忱辅。