MySQL索引機(jī)制的詳細(xì)解析及原理
一.索引的類型與常見的操作
前綴索引
MySQL 前綴索引能有效減小索引文件的大小,提高索引的速度。但是前綴索引也有它的壞處:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前綴索引,也不能把它們用作覆蓋索引(Covering Index)。
復(fù)合索引
集一個索引包含多個列(最左前綴匹配原則)
唯一索引
索引列的值必須唯一,但允許有空值
全文索引
在MySQL 5.6版本以前,只有MyISAM存儲引擎支持全文引擎.在5.6版本中,InnoDB加入了對全文索引的支持,但是不支持中文全文索引.在5.7.6版本,MySQL內(nèi)置了ngram全文解析器,用來支持亞洲語種的分詞.
全文索引為FUllText,在定義索引的列上支持值的全文查找,允許在這些索引列中插入重復(fù)值和空值,全文索引可以在CHAR,VARCHAR,TEXT類型列上創(chuàng)建
主鍵索引
設(shè)定主鍵后數(shù)據(jù)會自動建立索引,InnoDB為聚簇索引
單列索引
即一個索引只包含單個列,一個表可以有多個單列索引
覆蓋索引
覆蓋索引是指一個查詢語句的執(zhí)行只用從所有就能夠得到,不必從數(shù)據(jù)表中讀取,覆蓋索引不是索引樹,是一個結(jié)果,當(dāng)一條查詢語句符合覆蓋索引條件時候,MySQL只需要通過索引就可以返回查詢所需要的數(shù)據(jù),這樣避免了查到索引后的回表操作,減少了I/O效率
-- 目前有一個key(name)索引,聚簇索引是key(id) -- 使用了覆蓋索引 select id from stu where key = '天天'; -- 不使用覆蓋索引,因?yàn)椴樵兊慕Y(jié)果無法從普通索引樹中得到 select * from stu where key = '天天'
查看索引
show index from table_name;
列名解析:
列名title | 解釋desc | 取值value |
---|---|---|
table | 索引對應(yīng)表的名稱 | DB中的表 |
Non_unique | 索引包含value是否為唯一(是否為唯一索引) | 0代表是唯一,1代表不是 |
Key_name | 索引的名稱 | 不命名為創(chuàng)建時列名稱,聯(lián)合查詢?yōu)镾eq_in_index為1的列名稱,重復(fù)是使用_+number區(qū)分 |
Seq_in_index | 索引中列的序列號,從1開始,表明在聯(lián)合查詢中的順序,我們可以根據(jù)這個推斷出聯(lián)合索引中索引的前后順序(使用最左優(yōu)化原則) | 從1遞增至聯(lián)合索引的列數(shù) |
Column_name | 索引的列名 | 索引的列名 |
Collation(n.排序方式,校隊) | 指排序方式 | A表示升序,B表示降序,NULL表示未排序。 |
Cardinality | 基數(shù)的意思,表示索引中唯一值的數(shù)目的估計值,我們知道某個字段的重復(fù)值越少越適合建立索引,所以我們一般根據(jù)Cardinality來判斷索引是否具有高選擇性,如果這個值非常小,就需要評估這個字段是否適合做索引 | 最小值為1,表示索引的列字段值都重復(fù),最大為表中字段數(shù) |
Sub_part | 當(dāng)索引是前綴索引的時候,sub_part表示前綴的字符數(shù) | 非前綴為0,前綴索引為字符數(shù) |
Packed | 指示關(guān)鍵字如何被壓縮。 | 如果沒有被壓縮,則為NULL |
NUll | 如果列含有null,則含有yes | null/yes |
Index_type | 表示索引類型,全文索引是Fulltext,Memory引擎對應(yīng)Hash,其他大多數(shù)是Btree,Rtree沒有見過 | FULLTEXT,HASH,BTREE,RTREE |
Comment | 注釋 | ... |
Index_comment | 注釋 | ... |
刪除索引
drop index index_name on table name; -- 錯誤刪除primary索引 drop index `PriMary` on temp; -- >:Incorrect table definition; there can be only one auto column and it must be defined as a key
二.常見的索引詳解與創(chuàng)建
主鍵索引
-- mysql中InnoDB使用主鍵索引作為聚簇索引,主鍵索引無法使用 -- 創(chuàng)建時候,主鍵自動定義 create table temppp(id int auto_increment,primary key(id),name varchar(20) not null unique); -- 無法刪除primary key索引,需要改變的時候,首先需要刪除主鍵列,刪除后自動選擇一行unique的列作為主鍵索引 alter table temppp drop COLUMN id;
查看:
刪除前:
刪除后:
單列索引
普通的索引,沒有什么介紹
-- 建表時候表級約束建立索引 create table otest( id int(25) PRIMARY key, `name` varchar(255), -- 這一句就是在建立普通字段的索引,但是無法設(shè)置名字 key(`name`) ) -- 建表后 alter table otest add index key(`name`);
查看:(注意和前綴索引Sub_part的區(qū)別)
唯一索引
當(dāng)索引的列是unique的時候,會生成唯一索引,唯一索引關(guān)于null有下列兩種情況
SQLSERVER 下的唯一索引的列,允許null值,但最多允許有一個空值
-- 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語句 insert into temp values(1,null); insert into temp values(2,null);
結(jié)果:
MYSQL下的唯一索引的列,允許null值,并且允許多個空值
-- mysql下實(shí)驗(yàn)代碼 create table otest ( id int primary key, age varchar(20) unique, key(age) ); show index from otest
查看:
會建立兩個索引,一個非聚簇索引,一個是唯一索引
-- 插入兩條null語句 ....與上代碼相似
結(jié)果:
可以插入兩個空值(明人不說暗話,我喜歡MySQL)
前綴索引
- 一方面,它不會索引所有字段所有字符,會減小索引樹的大小.
- 另外一方面,索引只是為了區(qū)別出值,對于某些列,可能前幾位區(qū)別很大,我們就可以使用前綴索引。
- 一般情況下某個前綴的選擇性也是足夠高的,足以滿足查詢性能。對于BLOB,TEXT,或者很長的VARCHAR類型的列,必須使用前綴索引,因?yàn)镸ySQL不允許索引這些列的完整長度。
ALTER TABLE table_name ADD KEY(column_name(prefix_length)); -- 表級創(chuàng)建 create table temppp (id int auto_increment,primary key(id), name varchar(20) not null unique, key(name(2))); -- 表級創(chuàng)建 alter table temppp add index(name(2))
查看:
前綴索引實(shí)例的博文:http://www.dbjr.com.cn/article/243195.htm很好,推薦
復(fù)合索引
-- 建表時候表級約束建立索引 drop table if exists `otest`; create table otest( id int(25) PRIMARY key, `name` varchar(255), age varchar(255), -- 這一句就是在建立普通字段的索引,但是無法設(shè)置名字 key(`name`,age) ); -- 建表后 alter table otest add key(`name`,age);
查看:
復(fù)合索引的最左前綴匹配原則:
對于復(fù)合索引,查詢在一定條件才會使用該索引
-- 假設(shè)一個下列的索引 alter table otest add index(id,name,age); -- 只有查詢條件滿足組合索引的前綴匹配才能使用索引,也就是對于查詢的順序?yàn)? -- id id,name id,name,age這三種情況下才能使用組合索引 -- 對于下列這種就無法使用索引 select * from otest where id=?,age=? -- 缺少了name列 select * from otest where name=?,age=? -- 缺少了id列 -- 對于下列查詢MySQL會使用優(yōu)化調(diào)整位置 select * from otest where id=?,age=?,name=? -- 查詢順序是 id,age,name看起來是不能使用索引的,但是MySQL在執(zhí)行的時候會進(jìn)行優(yōu)化,將順序調(diào)整為id name age。
復(fù)合索引的優(yōu)點(diǎn)
減少開銷。建一個聯(lián)合索引(col1,col2,col3),實(shí)際相當(dāng)于建了(col1),(col1,col2),(col1,col2,col3)三個索引。每多一個索引,都會增加寫操作的開銷和磁盤空間的開銷。對于大量數(shù)據(jù)的表,使用聯(lián)合索引會大大的減少開銷!
覆蓋索引。對聯(lián)合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通過遍歷索引取得數(shù)據(jù),而無需回表,這減少了很多的隨機(jī)io操作。減少io操作,特別的隨機(jī)io其實(shí)是dba主要的優(yōu)化策略。所以,在真正的實(shí)際應(yīng)用中,覆蓋索引是主要的提升性能的優(yōu)化手段之一。
效率高。索引列越多,通過索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表,有如下sql:select from table where col1=1 and col2=2 and col3=3,假設(shè)假設(shè)每個條件可以篩選出10%的數(shù)據(jù),如果只有單值索引,那么通過該索引能篩選出1000W10%=100w條數(shù)據(jù),然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù),然后再排序,再分頁;如果是聯(lián)合索引,通過索引篩選出1000w10% 10% *10%=1w。
全文索引(FULLTEXT)
在模糊搜索中很有效,搜索全文中的某一個字段,可以參考這篇博文:http://www.dbjr.com.cn/article/243201.htm
三.索引的原理
1.通過實(shí)驗(yàn)介紹B+tree
我們先進(jìn)行下面一個實(shí)驗(yàn)看看InnoDB下的主鍵索引的一個現(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ù)據(jù)的id都是亂序的,為什么這里最后select查詢出來的結(jié)果都是進(jìn)行了排序?
這是因?yàn)镮nnoDB索引底層實(shí)現(xiàn)的是B+tree,B+tree具有下列的特點(diǎn):
和B-tree一樣是自平衡樹
m個子樹上層有m個中間節(jié)點(diǎn),但是m個中間節(jié)點(diǎn)只保存索引,而不保存數(shù)據(jù)。
所有的葉子結(jié)點(diǎn)中包含了全部關(guān)鍵字的信息,及指向含有這些關(guān)鍵字記錄的指針,且葉子結(jié)點(diǎn)本身依關(guān)鍵字的大小自小而大的順序鏈接。
所有的非終端結(jié)點(diǎn)可以看成是索引部分,結(jié)點(diǎn)中僅含有其子樹根結(jié)點(diǎn)中最大(或最?。╆P(guān)鍵字。
所以上面的排序是為了使用B+tree的結(jié)構(gòu),B+tree為了范圍搜索,將主鍵按照從小到大排序后,拆分成節(jié)點(diǎn)。后續(xù)還有新的節(jié)點(diǎn)進(jìn)入的時候,和B-tree相同的操作,會進(jìn)行分裂。
一般來說,聚簇索引的B+tree都是三層
- ①:每一個底層片稱為一個頁,InnoDB中一個頁的大小默認(rèn)是16kb,上層的中間結(jié)點(diǎn)稱為頁目錄,每個頁目錄都有一個指針指向下層存儲數(shù)據(jù)的葉結(jié)點(diǎn)
- ②:下層每個葉結(jié)點(diǎn)之間都使用鏈表連接(ps:這里是單鏈表還是雙向鏈表我記不清楚了,讀者可以查查)
- ③:這部分是葉結(jié)點(diǎn)存儲的數(shù)據(jù)信息
- ④:這部分是底層鏈表的指針
2.延伸
B-tree是所有結(jié)點(diǎn)都要存儲數(shù)據(jù),相同的數(shù)據(jù)更深,查找速度變慢,所以底層沒有使用B-tree。
MySQL的InnoDB存儲引擎設(shè)計時頂層頁目錄常駐內(nèi)存,對于2-4層B+樹查詢時,聚簇索引IO查詢1-3次,也就是和硬盤交互進(jìn)行IO讀
計算一個元素的字節(jié)大?。?*字段類型所占字節(jié) + 一個指針的字節(jié)數(shù)(32位4byte,64位8byte)
實(shí)際單表列過多要拆表,這樣主表存數(shù)據(jù)更多深度也低,查詢也快
對于InnoDB來說主鍵索引就是聚簇索引,而普通索引就是非聚簇索引
對于表中數(shù)據(jù)操作過多會造成存在許多的頁碎片,關(guān)于碎片整理可以看我這篇博文
http://www.dbjr.com.cn/article/243206.htm
四.聚簇索引和非聚簇索引
- 聚簇索引:將數(shù)據(jù)存儲和索引放到了一塊,索引結(jié)構(gòu)的葉子結(jié)點(diǎn)保存了行數(shù)據(jù)
- 非聚簇索引:將數(shù)據(jù)與索引分開存儲,索引結(jié)構(gòu)的葉子結(jié)點(diǎn)指向主鍵的值,也就是對應(yīng)的聚簇索引的row id(需要查找兩個B+tree,這個操作過程叫做回表)。
InnoDB中主鍵索引一定是聚簇索引,聚簇索引一定是主鍵索引。
為什么這里輔助索引葉子結(jié)點(diǎn)不直接存儲數(shù)據(jù)呢?
- 數(shù)據(jù)冗余
- 修改,增加,刪除需要操作的更多,時間線性增加,也就是難以維護(hù)
- 占用磁盤存儲增大
MYISAM只有非聚簇索引,索引最終指向的都是物理地址。
1.使用聚簇索引的優(yōu)勢
Q:既然有回表的存在,那么聚簇索引的優(yōu)勢在哪里?
- 由于行數(shù)據(jù)和聚簇索引的葉子結(jié)點(diǎn)存儲在一起,同一頁中會有多條行數(shù)據(jù),訪問同一數(shù)據(jù)頁不同行記錄時,已經(jīng)把也加載到了buffer中(緩存器),再次訪問時,會在內(nèi)存中完成訪問,不必訪問磁盤,這樣主鍵和行數(shù)據(jù)是一起被載入內(nèi)存的,找到葉子結(jié)點(diǎn)就可以立刻將行數(shù)據(jù)返回了,獲得數(shù)據(jù)更快。
- 輔助索引的葉子結(jié)點(diǎn),存儲主鍵值,而不是數(shù)據(jù)的存放地址,好處是當(dāng)行數(shù)據(jù)發(fā)生變化時,索引樹的節(jié)點(diǎn)也需要分裂變化,或者是我們需要查找的數(shù)據(jù),在上一次讀寫的緩存中沒有,需要發(fā)送一次新的IO操作時,可以避免對輔助索引的維護(hù)工作,只要維護(hù)聚簇索引樹就好了,另外一個好處是,因?yàn)檩o助索引存放的是主鍵值,減少了輔助索引占用的存儲空間的大小。
Q:主鍵索引作為聚簇索引需要注意什么
- 當(dāng)使用主鍵為聚簇索引時,主鍵最好不要使用UUID,因?yàn)閁UID的值過于離散(可以查看UUID的產(chǎn)生過程),不適合排序,并且可能在兩個已經(jīng)排序好的結(jié)點(diǎn)中會出現(xiàn)新插入的節(jié)點(diǎn),導(dǎo)致索引樹調(diào)整復(fù)雜度變大。
- 建議使用int類型的自增,int類型自增主鍵數(shù)據(jù)量為4億,滿足一般開發(fā)要求,并且由于自增,主鍵本身就有序,因此開銷很小,輔助索引中保存的主鍵值也會跟著變化,占用存儲空間,也會影響到IO操作讀取到的數(shù)據(jù)量。
2.什么情況下無法使用索引
查詢語句中使用Like關(guān)鍵字
在查詢語句中使用LIke關(guān)鍵字進(jìn)行查詢時,如果匹配字符串的第一個字符為"%",索引不會使用。如果“%”不是在第一位,索引就會使用
查詢語句中使用多列索引
多列索引是在表的多個字段上創(chuàng)建的索引,滿足最左前綴匹配原則,索引才會被使用
查詢語句中使用OR關(guān)鍵字
查詢語句只有Or關(guān)鍵字時候,如果OR前后的兩個條件都是索引,這這次查詢將會使用索引,否則Or前后有一個條件的列不是索引,那么查詢中將不使用索引
5.關(guān)于Explain語句
作者不會,建議查找,這里列出是作為提醒
永遠(yuǎn)年輕,永遠(yuǎn)熱淚盈眶
TIPS:MySQL底層存儲文件:
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)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
借助PHP的mysql_query()函數(shù)來創(chuàng)建MySQL數(shù)據(jù)庫的教程
這篇文章主要介紹了借助PHP的mysql_query()函數(shù)來創(chuàng)建MySQL數(shù)據(jù)庫的教程,將函數(shù)配合CREATE DATABASE語句使用,需要的朋友可以參考下2015-12-12mysqli多查詢特性 實(shí)現(xiàn)多條sql語句查詢
mysqli相對于mysql有很多優(yōu)勢,mysqli連接數(shù)據(jù)庫和mysqli預(yù)處理prepare使用,不僅如此,mysqli更是支持多查詢特性2012-12-12