?

一、數(shù)據(jù)庫(kù)索引

很大一部份程序員對(duì)索引的了解僅限于到“加索引能使查詢(xún)變快”這個(gè)概念為止，但有沒(méi)有考慮過(guò)索引為什么能使查詢(xún)變快呢？索引是使用B+樹(shù)（二叉樹(shù)）實(shí)現(xiàn)的數(shù)據(jù)結(jié)構(gòu)。

上圖中左邊是數(shù)據(jù)庫(kù)中的數(shù)據(jù)表，有col1和col2兩個(gè)字段，一共有15條記錄；右邊是以col2列為索引列的B_TREE索引，每個(gè)節(jié)點(diǎn)包含索引的鍵值和對(duì)應(yīng)數(shù)據(jù)表地址的指針，這樣就可以都過(guò)B_TREE在O(logn)的時(shí)間復(fù)雜度內(nèi)獲取相應(yīng)的數(shù)據(jù)，這樣明顯地加快了檢索的速度。再來(lái)思考下以下幾個(gè)問(wèn)題：

數(shù)據(jù)表為什么會(huì)使用主鍵？

使用索引后會(huì)使插入、修改、刪除變慢？

什么時(shí)候需要在兩個(gè)字段上加索引？

1.數(shù)據(jù)表為什么會(huì)使用主鍵？

一個(gè)沒(méi)加主鍵的表,它的數(shù)據(jù)無(wú)序的放置在磁盤(pán)存儲(chǔ)器上，一行一行的排列的很整齊。一個(gè)加了主鍵的表,并不能被稱(chēng)之為「表」。如果給表上了主鍵,那么表在磁盤(pán)上的存儲(chǔ)結(jié)構(gòu)就由整齊排列的結(jié)構(gòu)轉(zhuǎn)變成了樹(shù)狀結(jié)構(gòu),并且是「平衡樹(shù)」結(jié)構(gòu),換句話(huà)說(shuō),就是整個(gè)表就變成了一個(gè)索引。 這就是為什么一個(gè)表只能有一個(gè)主鍵,一個(gè)表只能有一個(gè)「聚集索引」,因?yàn)橹麈I的作用就是把「表」的數(shù)據(jù)格式轉(zhuǎn)換成「索引（平衡樹(shù)）」的格式放置。

給表中多個(gè)字段加上常規(guī)的索引,那么就會(huì)出現(xiàn)多個(gè)獨(dú)立的索引結(jié)構(gòu).字段中的數(shù)據(jù)就會(huì)被復(fù)制一份出來(lái),用于生成索引,葉子節(jié)點(diǎn)是主鍵ID,這也就是非聚集索引.,下面就是一個(gè)主鍵和三個(gè)常規(guī)索引的結(jié)構(gòu)。通過(guò)其他索引字段去查,那么葉子節(jié)點(diǎn)是主鍵ID,然后再去根據(jù)主鍵查,聚集索引（主鍵）是通往真實(shí)數(shù)據(jù)所在的唯一路徑

2.使用索引后會(huì)使插入、修改、刪除變慢？

這個(gè)很好理解，使用索引后，會(huì)生成新的二叉樹(shù)，插入速度自然變慢

二、Myisam引擎(非聚集索引)

若以這個(gè)引擎創(chuàng)建數(shù)據(jù)庫(kù)表Create table user （…..），它實(shí)際是生成三個(gè)文件：user.myi索引文件、user.myd數(shù)據(jù)文件、user.frm數(shù)據(jù)結(jié)構(gòu)類(lèi)型。如下圖：當(dāng)我們執(zhí)行 ?select?* from user where id = 1的時(shí)候，它的執(zhí)行流程。

查看該表的myi文件有沒(méi)有以id為索引的索引樹(shù)。

根據(jù)這個(gè)id索引找到葉子節(jié)點(diǎn)的id值，從而得到它里面的數(shù)據(jù)地址。(葉子節(jié)點(diǎn)存的是索引和數(shù)據(jù)地址)。

根據(jù)數(shù)據(jù)地址去myd文件里面找到對(duì)應(yīng)的數(shù)據(jù)返回出來(lái)。

三、Innodb引擎(聚集索引)

　　若以這個(gè)引擎創(chuàng)建數(shù)據(jù)庫(kù)表Create table user （…..），它實(shí)際是生成兩個(gè)文件：user.ibd ??索引文件、user.frm數(shù)據(jù)結(jié)構(gòu)類(lèi)型。因?yàn)閕nnodb引擎創(chuàng)建表默認(rèn)就是以主鍵為索引，所以不需要myi文件。下圖為innodb表的結(jié)構(gòu)圖：很顯然它與myisam最大的區(qū)別是將整條數(shù)據(jù)存在葉子節(jié)點(diǎn)，而不是地址。(葉子節(jié)點(diǎn)存的是主鍵索引和數(shù)據(jù)信息)若此時(shí)，你在其他列創(chuàng)建索引例如name，它就會(huì)另外創(chuàng)建一個(gè)以name為索引的索引樹(shù)，(葉子節(jié)點(diǎn)存的是索引和主鍵索引)。你在執(zhí)行select * from user where name = ‘吳磊’，他的執(zhí)行過(guò)程如下：

找到name索引樹(shù)

根據(jù)name的值找到該樹(shù)下葉子的name索引和主鍵值

用主鍵值去主鍵索引樹(shù)去葉子節(jié)點(diǎn)到該條數(shù)據(jù)信息

四、MyISAM引擎和InnoDB引擎的區(qū)別

• MyISAM：支持全文索引;不支持事務(wù);它是表級(jí)鎖;會(huì)保存表的具體行數(shù).
• InnoDB：5.6以后才有全文索引;支持事務(wù)；它是行級(jí)鎖;不會(huì)保存表的具體行數(shù).

一般：不用事務(wù)的時(shí)候,count計(jì)算多的時(shí)候適合myisam引擎。對(duì)可靠性要求高就是用innodby引擎。推薦用InnoDB引擎.加了索引之后能夠大幅度的提高查詢(xún)速度，但是索引也不是越多越好，一方面它會(huì)占用存儲(chǔ)空間，另一方面它會(huì)使得寫(xiě)操作變得很慢。通常我們對(duì)查詢(xún)次數(shù)比較頻繁，值比較多的列才建索引。例如：select * from user where sex = "女"， 這個(gè)就不需要建立索引，因?yàn)樾詣e一共就兩個(gè)值，查詢(xún)本身就是比較快的。select * from user where user_id = 1995 ,這個(gè)就需要建立索引，因?yàn)閡ser_id的值是非常多的。

參考文章：

了解數(shù)據(jù)庫(kù)索引及其原理

MySQL為什么要給表加上主鍵

?

總結(jié)

以上是生活随笔為你收集整理的MySql—索引原理的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。