欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL索引機(jī)制的詳細(xì)解析及原理

 更新時間:2022年04月02日 10:58:53   作者:寫不完作業(yè)還要玩  
引是為了加速對表中數(shù)據(jù)行的檢索而創(chuàng)建的一種分散存儲的數(shù)據(jù)結(jié)構(gòu),下面這篇文章主要給大家介紹了關(guān)于MySQL索引機(jī)制的詳細(xì)解析及原理的相關(guān)資料,文中通過示例代碼介紹的非常詳細(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,則含有yesnull/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)文章

最新評論