什么是索引

• 官方定義: 一種幫助mysql提高查詢效率的數(shù)據(jù)結(jié)構(gòu)
• 索引的優(yōu)點(diǎn):
  1、大大加快數(shù)據(jù)查詢速度
• 索引的缺點(diǎn):
  1、維護(hù)索引需要耗費(fèi)數(shù)據(jù)庫(kù)資源
  2、索引需要占用磁盤(pán)空間
  3、當(dāng)對(duì)表的數(shù)據(jù)進(jìn)行增刪改的時(shí)候，因?yàn)橐S護(hù)索引，速度會(huì)受到影響

索引分類

• a.主鍵索引
  設(shè)定為主鍵后數(shù)據(jù)庫(kù)會(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存儲(chǔ)引擎支持全文索引

索引的基本操作

(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ù)明明沒(méi)有按順序插入,為什么查詢時(shí)卻是有順序呢?

• 原因是:mysql底層為主鍵自動(dòng)創(chuàng)建索引,一定創(chuàng)建索引會(huì)進(jìn)行排序
• 也就是mysql底層真正存儲(chǔ)是這樣的
• 為什么要排序呢?因?yàn)榕判蛑笤诓樵兙拖鄬?duì)比較快了 如查詢 id=3的我只需要按照順序找到3就行啦(如果沒(méi)有排序大海撈針,全靠運(yùn)氣??!)

為了進(jìn)一步提高效率mysql索引又進(jìn)行了優(yōu)化

• 就是基于頁(yè)的形式進(jìn)行管理索引
• 如 查詢id=4的 直接先比較頁(yè) 先去頁(yè)目錄中找,再去 數(shù)據(jù)目錄中找

B+樹(shù)數(shù)據(jù)結(jié)構(gòu)

什么是B+樹(shù)呢?

• 參考資料: https://www.cnblogs.com/lianzhilei/p/11250589.html

B+Tree是在B-Tree基礎(chǔ)上的一種優(yōu)化，使其更適合實(shí)現(xiàn)外存儲(chǔ)索引結(jié)構(gòu)，InnoDB存儲(chǔ)引擎就是用B+Tree實(shí)現(xiàn)其索引結(jié)構(gòu)。

從上一節(jié)中的B-Tree結(jié)構(gòu)圖中可以看到每個(gè)節(jié)點(diǎn)中不僅包含數(shù)據(jù)的key值，還有data值。而每一個(gè)頁(yè)的存儲(chǔ)空間是有限的，如果data數(shù)據(jù)較大時(shí)將會(huì)導(dǎo)致每個(gè)節(jié)點(diǎn)（即一個(gè)頁(yè)）能存儲(chǔ)的key的數(shù)量很小，當(dāng)存儲(chǔ)的數(shù)據(jù)量很大時(shí)同樣會(huì)導(dǎo)致B-Tree的深度較大，增大查詢時(shí)的磁盤(pán)I/O次數(shù)，進(jìn)而影響查詢效率。在B+Tree中，所有數(shù)據(jù)記錄節(jié)點(diǎn)都是按照鍵值大小順序存放在同一層的葉子節(jié)點(diǎn)上，而非葉子節(jié)點(diǎn)上只存儲(chǔ)key值信息，這樣可以大大加大每個(gè)節(jié)點(diǎn)存儲(chǔ)的key值數(shù)量，降低B+Tree的高度。

B+Tree相對(duì)于B-Tree有幾點(diǎn)不同：

1. 非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值信息。
2. 所有葉子節(jié)點(diǎn)之間都有一個(gè)鏈指針。
3. 數(shù)據(jù)記錄都存放在葉子節(jié)點(diǎn)中。

• InnoDB存儲(chǔ)引擎中頁(yè)的大小為16KB，一般表的主鍵類型為INT（占用4個(gè)字節(jié)）或BIGINT（占用8個(gè)字節(jié)），指針類型也一般為4或8個(gè)字節(jié)，也就是說(shuō)一個(gè)頁(yè)（B+Tree中的一個(gè)節(jié)點(diǎn)）中大概存儲(chǔ)16KB/(8B+8B)=1K個(gè)鍵值（因?yàn)槭枪乐担瑸榉奖阌?jì)算，這里的K取值為10^3）。
  也就是說(shuō)一個(gè)深度為3的B+Tree索引可以維護(hù)10^3 * 10^3 * 10^3 = 10億 條記錄。

• 實(shí)際情況中每個(gè)節(jié)點(diǎn)可能不能填充滿，因此在數(shù)據(jù)庫(kù)中，B+Tree的高度一般都在2~4層。
  mysql的InnoDB存儲(chǔ)引擎在設(shè)計(jì)時(shí)是將根節(jié)點(diǎn)常駐內(nèi)存的，也就是說(shuō)查找某一鍵值的行記錄時(shí)最多只需要1~3次磁盤(pán)I/O操作。

聚簇索引和非聚簇索引

• 聚簇索引： 將數(shù)據(jù)存儲(chǔ)與索引放到了一塊，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)保存了行數(shù)據(jù)
• 非聚簇索引：將數(shù)據(jù)與索引分開(kāi)存儲(chǔ)，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)指向了數(shù)據(jù)對(duì)應(yīng)的位置

注意:在innodb中，在聚簇索引之上創(chuàng)建的索引稱之為輔助索引，非聚簇索引都是輔助索引，像復(fù)合索引、前綴索引、唯一索引。輔助索引葉子節(jié)點(diǎn)存儲(chǔ)的不再是行的物理位置，而是主鍵值，輔助索引訪問(wèn)數(shù)據(jù)總是需要二次查找。

1. InnoDB中

• InnoDB使用的是聚簇索引，將主鍵組織到一棵B+樹(shù)中，而行數(shù)據(jù)就儲(chǔ)存在葉子節(jié)點(diǎn)上，若使用"where id = 14"這樣的條件查找主鍵，則按照B+樹(shù)的檢索算法即可查找到對(duì)應(yīng)的葉節(jié)點(diǎn)，之后獲得行數(shù)據(jù)。

• 若對(duì)Name列進(jìn)行條件搜索，則需要兩個(gè)步驟：第一步在輔助索引B+樹(shù)中檢索Name，到達(dá)其葉子節(jié)點(diǎn)獲取對(duì)應(yīng)的主鍵。第二步使用主鍵在主索引B+樹(shù)種再執(zhí)行一次B+樹(shù)檢索操作，最終到達(dá)葉子節(jié)點(diǎn)即可獲取整行數(shù)據(jù)。（重點(diǎn)在于通過(guò)其他鍵需要建立輔助索引）

• 聚簇索引默認(rèn)是主鍵，如果表中沒(méi)有定義主鍵，InnoDB 會(huì)選擇一個(gè)唯一且非空的索引代替。如果沒(méi)有這樣的索引，InnoDB 會(huì)隱式定義一個(gè)主鍵（類似oracle中的RowId）來(lái)作為聚簇索引。如果已經(jīng)設(shè)置了主鍵為聚簇索引又希望再單獨(dú)設(shè)置聚簇索引，必須先刪除主鍵，然后添加我們想要的聚簇索引，最后恢復(fù)設(shè)置主鍵即可。

2. MYISAM

• MyISAM使用的是非聚簇索引，非聚簇索引的兩棵B+樹(shù)看上去沒(méi)什么不同，節(jié)點(diǎn)的結(jié)構(gòu)完全一致只是存儲(chǔ)的內(nèi)容不同而已，主鍵索引B+樹(shù)的節(jié)點(diǎn)存儲(chǔ)了主鍵，輔助鍵索引B+樹(shù)存儲(chǔ)了輔助鍵。表數(shù)據(jù)存儲(chǔ)在獨(dú)立的地方，這兩顆B+樹(shù)的葉子節(jié)點(diǎn)都使用一個(gè)地址指向真正的表數(shù)據(jù)，對(duì)于表數(shù)據(jù)來(lái)說(shuō)，這兩個(gè)鍵沒(méi)有任何差別。由于索引樹(shù)是獨(dú)立的，通過(guò)輔助鍵檢索無(wú)需訪問(wèn)主鍵的索引樹(shù)。

使用聚簇索引的優(yōu)勢(shì)

• 問(wèn)題: 每次使用輔助索引檢索都要經(jīng)過(guò)兩次B+樹(shù)查找，看上去聚簇索引的效率明顯要低于非聚簇索引，這不是多此一舉嗎？聚簇索引的優(yōu)勢(shì)在哪？

• 1.由于行數(shù)據(jù)和聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)在一起，同一頁(yè)中會(huì)有多條行數(shù)據(jù)，訪問(wèn)同一數(shù)據(jù)頁(yè)不同行記錄時(shí)，已經(jīng)把頁(yè)加載到了Buffer中（緩存器），再次訪問(wèn)時(shí)，會(huì)在內(nèi)存中完成訪問(wèn)，不必訪問(wèn)磁盤(pán)。這樣主鍵和行數(shù)據(jù)是一起被載入內(nèi)存的，找到葉子節(jié)點(diǎn)就可以立刻將行數(shù)據(jù)返回了，如果按照主鍵Id來(lái)組織數(shù)據(jù)，獲得數(shù)據(jù)更快。

• 2.輔助索引的葉子節(jié)點(diǎn)，存儲(chǔ)主鍵值，而不是數(shù)據(jù)的存放地址。好處是當(dāng)行數(shù)據(jù)放生變化時(shí)，索引樹(shù)的節(jié)點(diǎn)也需要分裂變化；或者是我們需要查找的數(shù)據(jù)，在上一次IO讀寫(xiě)的緩存中沒(méi)有，需要發(fā)生一次新的IO操作時(shí)，可以避免對(duì)輔助索引的維護(hù)工作，只需要維護(hù)聚簇索引樹(shù)就好了。另一個(gè)好處是，因?yàn)檩o助索引存放的是主鍵值，減少了輔助索引占用的存儲(chǔ)空間大小。

聚簇索引需要注意什么?

• 當(dāng)使用主鍵為聚簇索引時(shí)，主鍵最好不要使用uuid，因?yàn)閡uid的值太過(guò)離散，不適合排序且可能出線新增加記錄的uuid，會(huì)插入在索引樹(shù)中間的位置，導(dǎo)致索引樹(shù)調(diào)整復(fù)雜度變大，消耗更多的時(shí)間和資源。
• 建議使用int類型的自增，方便排序并且默認(rèn)會(huì)在索引樹(shù)的末尾增加主鍵值，對(duì)索引樹(shù)的結(jié)構(gòu)影響最小。而且，主鍵值占用的存儲(chǔ)空間越大，輔助索引中保存的主鍵值也會(huì)跟著變大，占用存儲(chǔ)空間，也會(huì)影響到IO操作讀取到的數(shù)據(jù)量。

為什么主鍵通常建議使用自增id

• 聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的，那么對(duì)應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤(pán)上的。如果主鍵不是自增id，那么可以想象，它會(huì)干些什么，不斷地調(diào)整數(shù)據(jù)的物理地址、分頁(yè)，當(dāng)然也有其他一些措施來(lái)減少這些操作，但卻無(wú)法徹底避免。但，如果是自增的，那就簡(jiǎn)單了，它只需要一頁(yè)一頁(yè)地寫(xiě)，索引結(jié)構(gòu)相對(duì)緊湊，磁盤(pán)碎片少，效率也高。

什么情況下無(wú)法利用索引呢?

• 1.查詢語(yǔ)句中使用LIKE關(guān)鍵字
  在查詢語(yǔ)句中使用 LIKE 關(guān)鍵字進(jìn)行查詢時(shí)，如果匹配字符串的第一個(gè)字符為“%”，索引不會(huì)被使用。如果“%”不是在第一個(gè)位置，索引就會(huì)被使用。

• 2.查詢語(yǔ)句中使用多列索引
  多列索引是在表的多個(gè)字段上創(chuàng)建一個(gè)索引，只有查詢條件中使用了這些字段中的第一個(gè)字段，索引才會(huì)被使用。

• 3.查詢語(yǔ)句中使用OR關(guān)鍵字
  查詢語(yǔ)句只有OR關(guān)鍵字時(shí)，如果OR前后的兩個(gè)條件的列都是索引，那么查詢中將使用索引。如果OR前后有一個(gè)條件的列不是索引，那么查詢中將不使用索引。