什么是索引
- 官方定義: 一種幫助mysql提高查詢效率的數(shù)據(jù)結(jié)構(gòu)
- 索引的優(yōu)點(diǎn):
1稠炬、大大加快數(shù)據(jù)查詢速度 - 索引的缺點(diǎn):
1、維護(hù)索引需要耗費(fèi)數(shù)據(jù)庫資源
2怔球、索引需要占用磁盤空間
3他去、當(dāng)對表的數(shù)據(jù)進(jìn)行增刪改的時(shí)候，因?yàn)橐S護(hù)索引柱锹，速度會(huì)受到影響
索引分類
a.主鍵索引
設(shè)定為主鍵后數(shù)據(jù)庫會(huì)自動(dòng)建立索引哪自，innodb為聚簇索引b.單值索引
即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引c.唯一索引
索引列的值必須唯一禁熏，但允許有空值d.復(fù)合索引
即一個(gè)索引包含多個(gè)列e.Full Text 全文索引 (My5.7版本之前 只能由于MYISAM引擎)
全文索引類型為FULLTEXT提陶，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復(fù)值和空值匹层。全文索引可以在CHAR、VARCHAR锌蓄、 TEXT類型列上創(chuàng)建升筏。MYSQL只有MYISAM存儲引擎支持全文索引
索引的基本操作
(1)主鍵索引 自動(dòng)創(chuàng)建
--建表 主鍵自動(dòng)創(chuàng)建主鍵索引
create table t_user(id varchar(20) primary key,name varchar(20));
--查看索引
show index from t_user;
(2)單列索引(普通索引|單值索引)
--建表時(shí)創(chuàng)建
create table t_user(id varchar(20) primary key,name varchar(20),key(name));
'注意:隨表一起建立的索引索引名同列名一致'
--建表后創(chuàng)建
create index nameindex on t_user(name);
--刪除索引
drop index 索引名 on 表名
(3)唯一索引
--建表時(shí)創(chuàng)建
create table t_user(id varchar(20) primary key,name varchar(20),unique(name));
--建表后創(chuàng)建
create unique index nameindex on t_user(name);
(4)復(fù)合索引
---建表時(shí)創(chuàng)建
create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));
--建表后創(chuàng)建
create index nameageindex on t_user(name,age);
索引的底層原理
1.思考
---建表
create table t_emp(id int primary key,name varchar(20),age int);
--插入數(shù)據(jù)
insert into t_emp values(5,'d',22);
insert into t_emp values(6,'d',22);
insert into t_emp values(7,'e',21);
insert into t_emp values(1,'a',23);
insert into t_emp values(2,'b',26);
insert into t_emp values(3,'c',27);
insert into t_emp values(4,'a',32);
insert into t_emp values(8,'f',53);
insert into t_emp values(9,'v',13);
--查詢
select * from t_emp;
為什么上面數(shù)據(jù)明明沒有按順序插入,為什么查詢時(shí)卻是有順序呢?
- 原因是:mysql底層為主鍵自動(dòng)創(chuàng)建索引,一定創(chuàng)建索引會(huì)進(jìn)行排序
- 也就是mysql底層真正存儲是這樣的
- 為什么要排序呢?因?yàn)榕判蛑笤诓樵兙拖鄬Ρ容^快了 如查詢 id=3的我只需要按照順序找到3就行啦(如果沒有排序大海撈針,全靠運(yùn)氣??!)
為了進(jìn)一步提高效率mysql索引又進(jìn)行了優(yōu)化
- 就是基于頁的形式進(jìn)行管理索引
- 如 查詢id=4的 直接先比較頁 先去頁目錄中找,再去 數(shù)據(jù)目錄中找
什么是B+樹呢?
B+Tree是在B-Tree基礎(chǔ)上的一種優(yōu)化，使其更適合實(shí)現(xiàn)外存儲索引結(jié)構(gòu)瘸爽，InnoDB存儲引擎就是用B+Tree實(shí)現(xiàn)其索引結(jié)構(gòu)您访。
從上一節(jié)中的B-Tree結(jié)構(gòu)圖中可以看到每個(gè)節(jié)點(diǎn)中不僅包含數(shù)據(jù)的key值，還有data值剪决。而每一個(gè)頁的存儲空間是有限的灵汪，如果data數(shù)據(jù)較大時(shí)將會(huì)導(dǎo)致每個(gè)節(jié)點(diǎn)（即一個(gè)頁）能存儲的key的數(shù)量很小，當(dāng)存儲的數(shù)據(jù)量很大時(shí)同樣會(huì)導(dǎo)致B-Tree的深度較大柑潦，增大查詢時(shí)的磁盤I/O次數(shù)享言，進(jìn)而影響查詢效率。在B+Tree中渗鬼，所有數(shù)據(jù)記錄節(jié)點(diǎn)都是按照鍵值大小順序存放在同一層的葉子節(jié)點(diǎn)上览露，而非葉子節(jié)點(diǎn)上只存儲key值信息，這樣可以大大加大每個(gè)節(jié)點(diǎn)存儲的key值數(shù)量譬胎，降低B+Tree的高度差牛。
B+Tree相對于B-Tree有幾點(diǎn)不同：
- 非葉子節(jié)點(diǎn)只存儲鍵值信息命锄。
- 所有葉子節(jié)點(diǎn)之間都有一個(gè)鏈指針。
- 數(shù)據(jù)記錄都存放在葉子節(jié)點(diǎn)中偏化。
InnoDB存儲引擎中頁的大小為16KB脐恩，一般表的主鍵類型為INT（占用4個(gè)字節(jié)）或BIGINT（占用8個(gè)字節(jié)），指針類型也一般為4或8個(gè)字節(jié)侦讨，也就是說一個(gè)頁（B+Tree中的一個(gè)節(jié)點(diǎn)）中大概存儲16KB/(8B+8B)=1K個(gè)鍵值（因?yàn)槭枪乐凳幻埃瑸榉奖阌?jì)算，這里的K取值為10^3）搭伤。
也就是說一個(gè)深度為3的B+Tree索引可以維護(hù)10^3 * 10^3 * 10^3 = 10億 條記錄只怎。實(shí)際情況中每個(gè)節(jié)點(diǎn)可能不能填充滿，因此在數(shù)據(jù)庫中怜俐，B+Tree的高度一般都在2~4層身堡。
mysql的InnoDB存儲引擎在設(shè)計(jì)時(shí)是將根節(jié)點(diǎn)常駐內(nèi)存的，也就是說查找某一鍵值的行記錄時(shí)最多只需要1~3次磁盤I/O操作拍鲤。
聚簇索引和非聚簇索引
- 聚簇索引： 將數(shù)據(jù)存儲與索引放到了一塊贴谎，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)保存了行數(shù)據(jù)
- 非聚簇索引：將數(shù)據(jù)與索引分開存儲，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)指向了數(shù)據(jù)對應(yīng)的位置
注意:在innodb中季稳，在聚簇索引之上創(chuàng)建的索引稱之為輔助索引擅这，非聚簇索引都是輔助索引，像復(fù)合索引景鼠、前綴索引仲翎、唯一索引。輔助索引葉子節(jié)點(diǎn)存儲的不再是行的物理位置铛漓，而是主鍵值溯香，輔助索引訪問數(shù)據(jù)總是需要二次查找。
1. InnoDB中
InnoDB使用的是聚簇索引浓恶，將主鍵組織到一棵B+樹中玫坛，而行數(shù)據(jù)就儲存在葉子節(jié)點(diǎn)上，若使用"where id = 14"這樣的條件查找主鍵包晰，則按照B+樹的檢索算法即可查找到對應(yīng)的葉節(jié)點(diǎn)湿镀，之后獲得行數(shù)據(jù)。
若對Name列進(jìn)行條件搜索伐憾，則需要兩個(gè)步驟：第一步在輔助索引B+樹中檢索Name勉痴，到達(dá)其葉子節(jié)點(diǎn)獲取對應(yīng)的主鍵。第二步使用主鍵在主索引B+樹種再執(zhí)行一次B+樹檢索操作塞耕，最終到達(dá)葉子節(jié)點(diǎn)即可獲取整行數(shù)據(jù)蚀腿。（重點(diǎn)在于通過其他鍵需要建立輔助索引）
聚簇索引默認(rèn)是主鍵，如果表中沒有定義主鍵，InnoDB 會(huì)選擇一個(gè)唯一且非空的索引代替莉钙。如果沒有這樣的索引廓脆，InnoDB 會(huì)隱式定義一個(gè)主鍵（類似oracle中的RowId）來作為聚簇索引。如果已經(jīng)設(shè)置了主鍵為聚簇索引又希望再單獨(dú)設(shè)置聚簇索引磁玉，必須先刪除主鍵停忿，然后添加我們想要的聚簇索引，最后恢復(fù)設(shè)置主鍵即可蚊伞。
2. MYISAM
- MyISAM使用的是非聚簇索引席赂，非聚簇索引的兩棵B+樹看上去沒什么不同，節(jié)點(diǎn)的結(jié)構(gòu)完全一致只是存儲的內(nèi)容不同而已时迫，主鍵索引B+樹的節(jié)點(diǎn)存儲了主鍵颅停，輔助鍵索引B+樹存儲了輔助鍵。表數(shù)據(jù)存儲在獨(dú)立的地方掠拳，這兩顆B+樹的葉子節(jié)點(diǎn)都使用一個(gè)地址指向真正的表數(shù)據(jù)癞揉，對于表數(shù)據(jù)來說，這兩個(gè)鍵沒有任何差別溺欧。由于索引樹是獨(dú)立的喊熟，通過輔助鍵檢索無需訪問主鍵的索引樹。
使用聚簇索引的優(yōu)勢
問題: 每次使用輔助索引檢索都要經(jīng)過兩次B+樹查找姐刁，看上去聚簇索引的效率明顯要低于非聚簇索引芥牌，這不是多此一舉嗎？聚簇索引的優(yōu)勢在哪聂使？
1.由于行數(shù)據(jù)和聚簇索引的葉子節(jié)點(diǎn)存儲在一起壁拉，同一頁中會(huì)有多條行數(shù)據(jù)，訪問同一數(shù)據(jù)頁不同行記錄時(shí)柏靶，已經(jīng)把頁加載到了Buffer中（緩存器）扇商，再次訪問時(shí)，會(huì)在內(nèi)存中完成訪問宿礁，不必訪問磁盤。這樣主鍵和行數(shù)據(jù)是一起被載入內(nèi)存的蔬芥，找到葉子節(jié)點(diǎn)就可以立刻將行數(shù)據(jù)返回了梆靖，如果按照主鍵Id來組織數(shù)據(jù)，獲得數(shù)據(jù)更快笔诵。
2.輔助索引的葉子節(jié)點(diǎn)返吻，存儲主鍵值，而不是數(shù)據(jù)的存放地址乎婿。好處是當(dāng)行數(shù)據(jù)放生變化時(shí)测僵，索引樹的節(jié)點(diǎn)也需要分裂變化；或者是我們需要查找的數(shù)據(jù)，在上一次IO讀寫的緩存中沒有捍靠，需要發(fā)生一次新的IO操作時(shí)沐旨，可以避免對輔助索引的維護(hù)工作，只需要維護(hù)聚簇索引樹就好了榨婆。另一個(gè)好處是磁携，因?yàn)檩o助索引存放的是主鍵值，減少了輔助索引占用的存儲空間大小良风。
聚簇索引需要注意什么?
- 當(dāng)使用主鍵為聚簇索引時(shí)谊迄，主鍵最好不要使用uuid，因?yàn)閡uid的值太過離散烟央，不適合排序且可能出線新增加記錄的uuid统诺，會(huì)插入在索引樹中間的位置，導(dǎo)致索引樹調(diào)整復(fù)雜度變大疑俭，消耗更多的時(shí)間和資源粮呢。
- 建議使用int類型的自增，方便排序并且默認(rèn)會(huì)在索引樹的末尾增加主鍵值怠硼，對索引樹的結(jié)構(gòu)影響最小鬼贱。而且，主鍵值占用的存儲空間越大香璃，輔助索引中保存的主鍵值也會(huì)跟著變大这难，占用存儲空間，也會(huì)影響到IO操作讀取到的數(shù)據(jù)量葡秒。
為什么主鍵通常建議使用自增id
- 聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的姻乓，即：只要索引是相鄰的，那么對應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤上的眯牧。如果主鍵不是自增id蹋岩，那么可以想象，它會(huì)干些什么学少，不斷地調(diào)整數(shù)據(jù)的物理地址剪个、分頁，當(dāng)然也有其他一些措施來減少這些操作版确，但卻無法徹底避免扣囊。但，如果是自增的绒疗，那就簡單了侵歇，它只需要一頁一頁地寫，索引結(jié)構(gòu)相對緊湊吓蘑，磁盤碎片少惕虑，效率也高。
什么情況下無法利用索引呢?
1.查詢語句中使用LIKE關(guān)鍵字
在查詢語句中使用 LIKE 關(guān)鍵字進(jìn)行查詢時(shí)，如果匹配字符串的第一個(gè)字符為“%”溃蔫，索引不會(huì)被使用健提。如果“%”不是在第一個(gè)位置，索引就會(huì)被使用酒唉。2.查詢語句中使用多列索引
多列索引是在表的多個(gè)字段上創(chuàng)建一個(gè)索引矩桂，只有查詢條件中使用了這些字段中的第一個(gè)字段，索引才會(huì)被使用痪伦。
- 3.查詢語句中使用OR關(guān)鍵字
查詢語句只有OR關(guān)鍵字時(shí)侄榴，如果OR前后的兩個(gè)條件的列都是索引，那么查詢中將使用索引网沾。如果OR前后有一個(gè)條件的列不是索引癞蚕，那么查詢中將不使用索引。
