一.索引的類(lèi)型與常見(jiàn)的操作

前綴索引

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

復(fù)合索引

集一個(gè)索引包含多個(gè)列（最左前綴匹配原則）

唯一索引

索引列的值必須唯一，但允許有空值

全文索引

在MySQL 5.6版本以前,只有MyISAM存儲(chǔ)引擎支持全文引擎.在5.6版本中,InnoDB加入了對(duì)全文索引的支持,但是不支持中文全文索引.在5.7.6版本,MySQL內(nèi)置了ngram全文解析器,用來(lái)支持亞洲語(yǔ)種的分詞.

全文索引為FUllText，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復(fù)值和空值，全文索引可以在CHAR,VARCHAR,TEXT類(lèi)型列上創(chuàng)建

主鍵索引

設(shè)定主鍵后數(shù)據(jù)會(huì)自動(dòng)建立索引，InnoDB為聚簇索引

單列索引

即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引

覆蓋索引

覆蓋索引是指一個(gè)查詢(xún)語(yǔ)句的執(zhí)行只用從所有就能夠得到，不必從數(shù)據(jù)表中讀取，覆蓋索引不是索引樹(shù)，是一個(gè)結(jié)果，當(dāng)一條查詢(xún)語(yǔ)句符合覆蓋索引條件時(shí)候，MySQL只需要通過(guò)索引就可以返回查詢(xún)所需要的數(shù)據(jù)，這樣避免了查到索引后的回表操作，減少了I/O效率

-- 目前有一個(gè)key(name)索引，聚簇索引是key(id)
-- 使用了覆蓋索引
select id from stu where key = '天天';
-- 不使用覆蓋索引，因?yàn)椴樵?xún)的結(jié)果無(wú)法從普通索引樹(shù)中得到
select * from stu where key = '天天'

查看索引

show index from table_name;

列名解析：

刪除索引

drop index index_name on table name;
-- 錯(cuò)誤刪除primary索引
drop index `PriMary` on temp;
-- >:Incorrect table definition; there can be only one auto column and it must be defined as a key

二.常見(jiàn)的索引詳解與創(chuàng)建

主鍵索引

-- mysql中InnoDB使用主鍵索引作為聚簇索引，主鍵索引無(wú)法使用
-- 創(chuàng)建時(shí)候,主鍵自動(dòng)定義
create table temppp(id int auto_increment,primary key(id),name varchar(20) not null unique);
-- 無(wú)法刪除primary key索引,需要改變的時(shí)候，首先需要?jiǎng)h除主鍵列,刪除后自動(dòng)選擇一行unique的列作為主鍵索引
alter table temppp drop COLUMN id;

查看：

刪除前:

刪除后：

單列索引

普通的索引，沒(méi)有什么介紹

-- 建表時(shí)候表級(jí)約束建立索引
create table otest(
id int(25) PRIMARY key,
`name` varchar(255),
-- 這一句就是在建立普通字段的索引，但是無(wú)法設(shè)置名字
key(`name`)
)
-- 建表后
alter table otest add index key(`name`)；

查看:（注意和前綴索引Sub_part的區(qū)別）

唯一索引

當(dāng)索引的列是unique的時(shí)候，會(huì)生成唯一索引，唯一索引關(guān)于null有下列兩種情況

SQLSERVER 下的唯一索引的列，允許null值，但最多允許有一個(gè)空值

-- sql server 下實(shí)驗(yàn)代碼
create table temp
(
id int primary key,
age varchar(20) unique,
);
 
create unique index age on temp(age)
execute sp_helpindex @objname='temp'

查看:

-- 插入兩條null語(yǔ)句
insert into temp values(1,null);
insert into temp values(2,null);

結(jié)果:

MYSQL下的唯一索引的列，允許null值，并且允許多個(gè)空值

-- mysql下實(shí)驗(yàn)代碼
create table otest
(
id int primary key,
age varchar(20) unique,
key(age)
);
show index from otest

查看:

會(huì)建立兩個(gè)索引，一個(gè)非聚簇索引，一個(gè)是唯一索引

-- 插入兩條null語(yǔ)句
....與上代碼相似

結(jié)果:

可以插入兩個(gè)空值（明人不說(shuō)暗話(huà)，我喜歡MySQL）

前綴索引

• 一方面，它不會(huì)索引所有字段所有字符，會(huì)減小索引樹(shù)的大小.
• 另外一方面，索引只是為了區(qū)別出值，對(duì)于某些列，可能前幾位區(qū)別很大，我們就可以使用前綴索引。
• 一般情況下某個(gè)前綴的選擇性也是足夠高的，足以滿(mǎn)足查詢(xún)性能。對(duì)于BLOB，TEXT，或者很長(zhǎng)的VARCHAR類(lèi)型的列，必須使用前綴索引，因?yàn)镸ySQL不允許索引這些列的完整長(zhǎng)度。

ALTER TABLE table_name ADD KEY(column_name(prefix_length));
-- 表級(jí)創(chuàng)建
create table temppp (id int auto_increment,primary key(id),
                     name varchar(20) not null unique,
                    key(name(2)));
-- 表級(jí)創(chuàng)建
alter table temppp add index(name(2))

查看：

前綴索引實(shí)例的博文:http://www.dbjr.com.cn/article/243195.htm很好，推薦

復(fù)合索引

-- 建表時(shí)候表級(jí)約束建立索引
drop table if exists `otest`;
create table otest(
id int(25) PRIMARY key,
`name` varchar(255),
age varchar(255),
-- 這一句就是在建立普通字段的索引，但是無(wú)法設(shè)置名字
key(`name`,age)
);
-- 建表后
alter table otest add key(`name`,age);

查看：

復(fù)合索引的最左前綴匹配原則：

對(duì)于復(fù)合索引，查詢(xún)?cè)谝欢l件才會(huì)使用該索引

-- 假設(shè)一個(gè)下列的索引
alter table otest add index(id,name,age);
 
-- 只有查詢(xún)條件滿(mǎn)足組合索引的前綴匹配才能使用索引，也就是對(duì)于查詢(xún)的順序?yàn)?
-- id id,name id,name,age這三種情況下才能使用組合索引
 
-- 對(duì)于下列這種就無(wú)法使用索引
select * from otest where id=?,age=? -- 缺少了name列
select * from otest where name=?,age=? -- 缺少了id列
 
-- 對(duì)于下列查詢(xún)MySQL會(huì)使用優(yōu)化調(diào)整位置
select * from otest where id=?,age=?,name=? -- 查詢(xún)順序是 id,age,name看起來(lái)是不能使用索引的，但是MySQL在執(zhí)行的時(shí)候會(huì)進(jìn)行優(yōu)化，將順序調(diào)整為id name age。

復(fù)合索引的優(yōu)點(diǎn)

減少開(kāi)銷(xiāo)。建一個(gè)聯(lián)合索引(col1,col2,col3)，實(shí)際相當(dāng)于建了(col1),(col1,col2),(col1,col2,col3)三個(gè)索引。每多一個(gè)索引，都會(huì)增加寫(xiě)操作的開(kāi)銷(xiāo)和磁盤(pán)空間的開(kāi)銷(xiāo)。對(duì)于大量數(shù)據(jù)的表，使用聯(lián)合索引會(huì)大大的減少開(kāi)銷(xiāo)！

覆蓋索引。對(duì)聯(lián)合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通過(guò)遍歷索引取得數(shù)據(jù)，而無(wú)需回表，這減少了很多的隨機(jī)io操作。減少io操作，特別的隨機(jī)io其實(shí)是dba主要的優(yōu)化策略。所以，在真正的實(shí)際應(yīng)用中，覆蓋索引是主要的提升性能的優(yōu)化手段之一。

效率高。索引列越多，通過(guò)索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設(shè)假設(shè)每個(gè)條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過(guò)該索引能篩選出1000W10%=100w條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù)，然后再排序，再分頁(yè)；如果是聯(lián)合索引，通過(guò)索引篩選出1000w10% 10% *10%=1w。

全文索引（FULLTEXT）

在模糊搜索中很有效，搜索全文中的某一個(gè)字段，可以參考這篇博文:http://www.dbjr.com.cn/article/243201.htm

三.索引的原理

1.通過(guò)實(shí)驗(yàn)介紹B+tree

我們先進(jìn)行下面一個(gè)實(shí)驗(yàn)看看InnoDB下的主鍵索引的一個(gè)現(xiàn)象。

create table otest(
id int(25) PRIMARY key,
`name` varchar(255),
age varchar(255)
);
 
 
insert into otest values(3,'q',1);
insert into otest values(1,'q',1);
insert into otest values(5,'q',1);
insert into otest values(2,'q',1);
insert into otest values(6,'q',1)
-- 查看現(xiàn)象
SELECT * from otest

查看:

我們插入進(jìn)去的時(shí)候，數(shù)據(jù)的id都是亂序的，為什么這里最后select查詢(xún)出來(lái)的結(jié)果都是進(jìn)行了排序？

這是因?yàn)镮nnoDB索引底層實(shí)現(xiàn)的是B+tree，B+tree具有下列的特點(diǎn)：

• 和B-tree一樣是自平衡樹(shù)

• m個(gè)子樹(shù)上層有m個(gè)中間節(jié)點(diǎn)，但是m個(gè)中間節(jié)點(diǎn)只保存索引，而不保存數(shù)據(jù)。

• 所有的葉子結(jié)點(diǎn)中包含了全部關(guān)鍵字的信息，及指向含有這些關(guān)鍵字記錄的指針，且葉子結(jié)點(diǎn)本身依關(guān)鍵字的大小自小而大的順序鏈接。

• 所有的非終端結(jié)點(diǎn)可以看成是索引部分，結(jié)點(diǎn)中僅含有其子樹(shù)根結(jié)點(diǎn)中最大（或最?。╆P(guān)鍵字。

所以上面的排序是為了使用B+tree的結(jié)構(gòu)，B+tree為了范圍搜索，將主鍵按照從小到大排序后，拆分成節(jié)點(diǎn)。后續(xù)還有新的節(jié)點(diǎn)進(jìn)入的時(shí)候，和B-tree相同的操作，會(huì)進(jìn)行分裂。

一般來(lái)說(shuō)，聚簇索引的B+tree都是三層

• ①：每一個(gè)底層片稱(chēng)為一個(gè)頁(yè)，InnoDB中一個(gè)頁(yè)的大小默認(rèn)是16kb，上層的中間結(jié)點(diǎn)稱(chēng)為頁(yè)目錄,每個(gè)頁(yè)目錄都有一個(gè)指針指向下層存儲(chǔ)數(shù)據(jù)的葉結(jié)點(diǎn)
• ②：下層每個(gè)葉結(jié)點(diǎn)之間都使用鏈表連接（ps:這里是單鏈表還是雙向鏈表我記不清楚了，讀者可以查查）
• ③：這部分是葉結(jié)點(diǎn)存儲(chǔ)的數(shù)據(jù)信息
• ④：這部分是底層鏈表的指針

2.延伸

• B-tree是所有結(jié)點(diǎn)都要存儲(chǔ)數(shù)據(jù)，相同的數(shù)據(jù)更深，查找速度變慢，所以底層沒(méi)有使用B-tree。

• MySQL的InnoDB存儲(chǔ)引擎設(shè)計(jì)時(shí)頂層頁(yè)目錄常駐內(nèi)存，對(duì)于2-4層B+樹(shù)查詢(xún)時(shí)，聚簇索引IO查詢(xún)1-3次，也就是和硬盤(pán)交互進(jìn)行IO讀

• 計(jì)算一個(gè)元素的字節(jié)大?。?*字段類(lèi)型所占字節(jié) + 一個(gè)指針的字節(jié)數(shù)（32位4byte，64位8byte）

• 實(shí)際單表列過(guò)多要拆表，這樣主表存數(shù)據(jù)更多深度也低，查詢(xún)也快

• 對(duì)于InnoDB來(lái)說(shuō)主鍵索引就是聚簇索引，而普通索引就是非聚簇索引

• 對(duì)于表中數(shù)據(jù)操作過(guò)多會(huì)造成存在許多的頁(yè)碎片，關(guān)于碎片整理可以看我這篇博文

http://www.dbjr.com.cn/article/243206.htm

四.聚簇索引和非聚簇索引

• 聚簇索引:將數(shù)據(jù)存儲(chǔ)和索引放到了一塊，索引結(jié)構(gòu)的葉子結(jié)點(diǎn)保存了行數(shù)據(jù)
• 非聚簇索引：將數(shù)據(jù)與索引分開(kāi)存儲(chǔ)，索引結(jié)構(gòu)的葉子結(jié)點(diǎn)指向主鍵的值,也就是對(duì)應(yīng)的聚簇索引的row id(需要查找兩個(gè)B+tree，這個(gè)操作過(guò)程叫做回表)。

InnoDB中主鍵索引一定是聚簇索引，聚簇索引一定是主鍵索引。

為什么這里輔助索引葉子結(jié)點(diǎn)不直接存儲(chǔ)數(shù)據(jù)呢？

• 數(shù)據(jù)冗余
• 修改，增加，刪除需要操作的更多，時(shí)間線(xiàn)性增加，也就是難以維護(hù)
• 占用磁盤(pán)存儲(chǔ)增大

MYISAM只有非聚簇索引，索引最終指向的都是物理地址。

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

Q:既然有回表的存在，那么聚簇索引的優(yōu)勢(shì)在哪里？

• 由于行數(shù)據(jù)和聚簇索引的葉子結(jié)點(diǎn)存儲(chǔ)在一起，同一頁(yè)中會(huì)有多條行數(shù)據(jù)，訪(fǎng)問(wèn)同一數(shù)據(jù)頁(yè)不同行記錄時(shí)，已經(jīng)把也加載到了buffer中(緩存器),再次訪(fǎng)問(wèn)時(shí)，會(huì)在內(nèi)存中完成訪(fǎng)問(wèn)，不必訪(fǎng)問(wèn)磁盤(pán)，這樣主鍵和行數(shù)據(jù)是一起被載入內(nèi)存的，找到葉子結(jié)點(diǎn)就可以立刻將行數(shù)據(jù)返回了，獲得數(shù)據(jù)更快。
• 輔助索引的葉子結(jié)點(diǎn)，存儲(chǔ)主鍵值，而不是數(shù)據(jù)的存放地址，好處是當(dāng)行數(shù)據(jù)發(fā)生變化時(shí)，索引樹(shù)的節(jié)點(diǎn)也需要分裂變化，或者是我們需要查找的數(shù)據(jù)，在上一次讀寫(xiě)的緩存中沒(méi)有，需要發(fā)送一次新的IO操作時(shí)，可以避免對(duì)輔助索引的維護(hù)工作，只要維護(hù)聚簇索引樹(shù)就好了，另外一個(gè)好處是，因?yàn)檩o助索引存放的是主鍵值，減少了輔助索引占用的存儲(chǔ)空間的大小。

Q:主鍵索引作為聚簇索引需要注意什么

• 當(dāng)使用主鍵為聚簇索引時(shí)，主鍵最好不要使用UUID，因?yàn)閁UID的值過(guò)于離散（可以查看UUID的產(chǎn)生過(guò)程），不適合排序，并且可能在兩個(gè)已經(jīng)排序好的結(jié)點(diǎn)中會(huì)出現(xiàn)新插入的節(jié)點(diǎn)，導(dǎo)致索引樹(shù)調(diào)整復(fù)雜度變大。
• 建議使用int類(lèi)型的自增，int類(lèi)型自增主鍵數(shù)據(jù)量為4億，滿(mǎn)足一般開(kāi)發(fā)要求，并且由于自增，主鍵本身就有序，因此開(kāi)銷(xiāo)很小，輔助索引中保存的主鍵值也會(huì)跟著變化，占用存儲(chǔ)空間，也會(huì)影響到IO操作讀取到的數(shù)據(jù)量。

2.什么情況下無(wú)法使用索引

• 查詢(xún)語(yǔ)句中使用Like關(guān)鍵字

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

• 查詢(xún)語(yǔ)句中使用多列索引

  多列索引是在表的多個(gè)字段上創(chuàng)建的索引，滿(mǎn)足最左前綴匹配原則，索引才會(huì)被使用

• 查詢(xún)語(yǔ)句中使用OR關(guān)鍵字

  查詢(xún)語(yǔ)句只有Or關(guān)鍵字時(shí)候，如果OR前后的兩個(gè)條件都是索引，這這次查詢(xún)將會(huì)使用索引，否則Or前后有一個(gè)條件的列不是索引，那么查詢(xún)中將不使用索引

5.關(guān)于Explain語(yǔ)句

作者不會(huì)，建議查找，這里列出是作為提醒

永遠(yuǎn)年輕，永遠(yuǎn)熱淚盈眶

TIPS:MySQL底層存儲(chǔ)文件：

MyISAM：.frm是存放表結(jié)構(gòu)的文件，.MYD是存放表數(shù)據(jù)的文件,.MYI是存放表索引的文件

InnoDB：.frm存放表結(jié)構(gòu)，.Ibd是存放表數(shù)據(jù)和索引的

總結(jié)

到此這篇關(guān)于MySQL索引機(jī)制的詳細(xì)解析及原理的文章就介紹到這了,更多相關(guān)MySQL索引機(jī)制內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論