建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
索引在數(shù)據(jù)庫(kù)中的地位是及其的重要，同時(shí)要想完全的掌握索引并不是一件容易的事，需要對(duì)數(shù)據(jù)的查詢(xún)?cè)硪约坝?jì)算機(jī)操作系統(tǒng)有深刻的認(rèn)識(shí)，當(dāng)然相關(guān)的算法和數(shù)據(jù)結(jié)構(gòu)也是必須的。因此，這篇文章感到了一些壓力，不過(guò)還是決定先拿出來(lái)總結(jié)一下，理一理索引，就當(dāng)做學(xué)習(xí)筆記了。
創(chuàng)新互聯(lián)自2013年起，先為潞城等服務(wù)建站，潞城等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢(xún)服務(wù)。為潞城企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問(wèn)題。
索引的重要習(xí)性猶如一本字典的拼音檢索和部首檢索部分，想象一下你買(mǎi)了一本只有正文的字典，那該有多么抓狂。而且在一個(gè)軟件系統(tǒng)中，通常數(shù)據(jù)的查詢(xún)與修改往往占到了10:1的比例，也就是我們需要將大部分的精力投入到數(shù)據(jù)的查詢(xún)上，其中很多工作是用來(lái)提升查詢(xún)的速度的，那么在這個(gè)過(guò)程中索引就扮演者非常重要的角色。
索引的實(shí)質(zhì)
如果說(shuō)一本字典的正文內(nèi)容的實(shí)質(zhì)是紙張的話(huà)，那么它前面按照拼音或者部首檢索的索引的實(shí)質(zhì)是什么呢!當(dāng)然也是紙張了，可能有人覺(jué)得是廢話(huà)，當(dāng)我們同時(shí)類(lèi)比到索引上就可以知道，一個(gè)數(shù)據(jù)表的實(shí)質(zhì)是數(shù)據(jù)文件（即文件），那么索引的實(shí)質(zhì)也當(dāng)然是文件了，Mysql的InnoDB的數(shù)據(jù)表中的索引就是表空間的一部分。因此，初學(xué)者可以把索引完全想象成為一本字典，一本字典就是一個(gè)數(shù)據(jù)表，正文部分呢，就是這個(gè)數(shù)據(jù)表的詳細(xì)內(nèi)容，按照拼音和部首的檢索都是索引。 當(dāng)我們通過(guò)索引查找一條數(shù)據(jù)項(xiàng)的時(shí)候就猶如通過(guò)拼音索引查找某一個(gè)字，當(dāng)在索引中查找到那個(gè)字的時(shí)候，會(huì)根據(jù)右邊對(duì)應(yīng)的頁(yè)碼找到那個(gè)字的解釋?zhuān)瑯?，MySQL的索引工作原理也是如此，每個(gè)索引項(xiàng)都有一個(gè)建索引列的關(guān)鍵字和一個(gè)指向該數(shù)據(jù)項(xiàng)的指針（類(lèi)似字典中的頁(yè)碼），當(dāng)我們查找到那個(gè)目標(biāo)關(guān)鍵字時(shí)，根據(jù)指針便可以直接定位到數(shù)據(jù)表中該關(guān)鍵字的位置。
但現(xiàn)實(shí)中的真是數(shù)據(jù)表并不像字典那樣，內(nèi)容都是嚴(yán)格按照拼音序列排列的，因?yàn)橛泻芏嗖淮_定的新內(nèi)容要插入或者要進(jìn)行其他操作，所以字典只是一種特殊的數(shù)據(jù)表，并不能把所有的數(shù)據(jù)表都拿來(lái)和字典比，這里用字典來(lái)類(lèi)比只是為了對(duì)索引的實(shí)質(zhì)有一個(gè)立體的認(rèn)識(shí)。
索引是如何實(shí)現(xiàn)快速查找的
假設(shè)A公司有1024名員工（員工編號(hào)1-1024），早上的出勤人數(shù)為1023次，現(xiàn)在想知道員工編號(hào)為8的小明是否出勤，如何通過(guò)早上的簽到表快速的查詢(xún)小明是否出勤呢！在沒(méi)有索引的情況下，通常的做法是從第一條記錄逐個(gè)向后查找，如果小明最后一個(gè)來(lái)或者沒(méi)有來(lái)，那么就需要查找1023次，效率為O(N)。也許會(huì)覺(jué)得1023次查找對(duì)現(xiàn)在的計(jì)算機(jī)根本不算什么，但是對(duì)于很多系統(tǒng)動(dòng)輒都是上千萬(wàn)的數(shù)據(jù)記錄，你可以想象查找的時(shí)間，比如你用15分鐘終于通過(guò)了qq的身份校驗(yàn)登陸上了qq是一種怎么樣的體驗(yàn)。因此，索引就呼之欲出了，我們?nèi)绾瓮ㄟ^(guò)索引來(lái)提高一個(gè)數(shù)量級(jí)的查找效率，這個(gè)時(shí)候就需要對(duì)這1024條數(shù)據(jù)做些什么了，在每條記錄插入的時(shí)候，可以根據(jù)每條記錄的員工編號(hào)和存儲(chǔ)該記錄的地址（指針）建立一個(gè)二叉查找樹(shù)，這樣1023條記錄便可以通過(guò)10次查找便可查到，查找效率足足提高了100倍。
同樣，為了效率的提升，也付出了一些代價(jià)，因?yàn)榻⒍娌檎覙?shù)需要額外的存儲(chǔ)空間，同時(shí)每次插入數(shù)據(jù)的時(shí)候需要對(duì)二叉查找樹(shù)進(jìn)行維護(hù)，減緩了數(shù)據(jù)的更新速度。但綜合來(lái)考慮，一般認(rèn)為這樣做是值得的。
當(dāng)然在MySQL中，不是采用的二叉樹(shù)查找樹(shù)來(lái)完成索引的存儲(chǔ)的，上面舉得例子只是為了說(shuō)明索引的工作過(guò)程，但其思想是相同的。MySQL中沒(méi)有特殊說(shuō)明的話(huà)，一般說(shuō)的索引指的就是B-Tree索引，采用B-Tree這種數(shù)據(jù)結(jié)構(gòu)是綜合了計(jì)算機(jī)操作系統(tǒng)以及組成而綜合考慮的。其核心思想主要是減少磁盤(pán)的IO次數(shù)，提高查詢(xún)速度。
如何理解聚簇索引
你只需記住它的名字叫聚簇索引，它不同于其他普通的索引！聚簇索引不僅僅是一種索引，更是一種存儲(chǔ)方式，InnoDB中將B-Tree索引和數(shù)據(jù)行存儲(chǔ)在一個(gè)數(shù)據(jù)結(jié)構(gòu)中，意味著什么呢？這意味著數(shù)據(jù)行即索引，索引即數(shù)據(jù)行，它們是在一起，在一起，在一起的。
接著通過(guò)來(lái)建立字典的例子來(lái)理解聚簇索引和其他普通索引的區(qū)別，現(xiàn)在要建立一本中華字典，這個(gè)時(shí)候字典是空的，要我們?cè)煲槐咀值涑鰜?lái)，首先我們要完成字典的正文，我們按照漢語(yǔ)拼音的順序去組織字典的正文，第一個(gè)是“安 an ”，放在一個(gè)位置，同時(shí)后面附加上注釋(這里，“an”就相當(dāng)于聚簇索引的關(guān)鍵字，而后面的注釋就是數(shù)據(jù)行，它們存在一起)，第二個(gè)是“王 wang”字，通過(guò)和“安 an”字比較，拼音順序靠后，所以放第二個(gè)。注意這里的放是指存儲(chǔ)在磁盤(pán)中的位置，可以理解為存儲(chǔ)順序。第三個(gè)字是“小 xiao”字，通過(guò)拼音順序，需要將第三個(gè)字存在第一個(gè)字“安”后面，那“王”字已經(jīng)占了磁盤(pán)頁(yè)面的位置，所以它需要向后面移動(dòng)，如果移出該列，就導(dǎo)致了也分裂，所以可以看到聚集索引的更新代價(jià)真的很大，那為什么還要這樣做呢？聚簇索引帶來(lái)了那些好處？
......，假設(shè)按照上面方法已經(jīng)建立好了字典的正文內(nèi)容，這個(gè)時(shí)候就可以讓我們體會(huì)一下聚簇索引的好處了，假如你要找“安 an”字，根據(jù)拼音序列，你一定知道它一定在前幾個(gè)頁(yè)面，所以直接可以翻到前面，同時(shí)你也可以找到拼音“an”所對(duì)應(yīng)的所有的漢字，這就是聚簇索引帶來(lái)的好處，同時(shí)聚簇索引是和數(shù)據(jù)行放在一起的，你不需要在根據(jù)索引里的指針找到對(duì)應(yīng)的數(shù)據(jù)行，然后翻頁(yè)找到（翻頁(yè)就相當(dāng)于磁盤(pán)IO），這些都是效率的提升。然而，上面也看到了聚簇索引的負(fù)面影響比如插入的時(shí)候，因此它就像一般利劍，用的合適效率提升，用的糟糕也會(huì)帶來(lái)很大的不好影響。
是時(shí)候區(qū)別一下非聚聚索引了，這個(gè)時(shí)候我們又按照部首建立了一個(gè)索引，那么按照部首建立的索引就是非聚簇索引，它單獨(dú)的存在在字典的前幾個(gè)頁(yè)面，而且同一個(gè)部首的字所對(duì)應(yīng)的頁(yè)碼也是沒(méi)有順序的，如果我們要山字旁所對(duì)應(yīng)的所有漢字，那就要取很多個(gè)頁(yè)面的值，導(dǎo)致很多隨機(jī)IO產(chǎn)生，同時(shí)不能很好利用計(jì)算機(jī)存儲(chǔ)系統(tǒng)的緩存系統(tǒng)，因此效率遠(yuǎn)沒(méi)有聚簇索引高。
InnoDB中聚簇索引產(chǎn)生的原則：
- 當(dāng)有主鍵時(shí)，主鍵為聚簇索引
- 當(dāng)沒(méi)有主鍵時(shí)，引擎會(huì)選擇一個(gè)唯一的*非空*列來(lái)作為聚簇索引
- 如果沒(méi)有以上兩種情況的列，引擎會(huì)隱式的定義一個(gè)主鍵作為聚簇索引
最后，通過(guò)建立一個(gè)數(shù)據(jù)表來(lái)感受聚簇索引，首先建立有一個(gè)沒(méi)有主鍵也沒(méi)有唯一非空列索引的數(shù)據(jù)表，即聚簇索引是系統(tǒng)隱式生成這種情況。這種情況，一般就是按數(shù)據(jù)插入的先后順序進(jìn)行排列。
- CREATE TABLE user_log (
- user_id INT NOT NULL,
- place VARCHAR(20) NOT NULL DEFAULT '',
- login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
- ) DEFAULT CHARSET utf8;
接下來(lái)插入三條數(shù)據(jù)：
- > INSERT INTO user_log(user_id,place)
- VALUES('1','中國(guó)');
- > INSERT INTO user_log(user_id,place)
- VALUES('2','中國(guó)');
- > INSERT INTO user_log(user_id,place)
- VALUES('1','美國(guó)');
然后，選擇所有列（即按照存儲(chǔ)順序查看數(shù)據(jù)列），果然如我們猜想，是按照插入順序存儲(chǔ)的：
- > SELECT * FROM user_log;
- user_id place login_time
- 1 中國(guó) 2015-11-20 10:32:41
- 2 中國(guó) 2015-11-20 10:33:18
- 1 美國(guó) 2015-11-20 10:33:18
接下來(lái)，我們添加一個(gè)唯一的索引（ID+login_time），此時(shí) 系統(tǒng)引擎應(yīng)該將此索引作為聚簇索引，因此我們?cè)俅尾迦胄碌臄?shù)據(jù)的時(shí)候是按照ID聚簇存儲(chǔ)的，就是說(shuō)ID相同的會(huì)存儲(chǔ)在一起，存儲(chǔ)在同一個(gè)頁(yè)面，甚至連續(xù)的幾個(gè)頁(yè)面。 下面首先添加這個(gè)唯一索引 并接著添加三條新的記錄：
- > ALTER TABLE user_log ADD UNIQUE KEY U_USER_LOG_ID_LOGIN_TIME(user_id,login_time);
- > INSERT INTO user_log(user_id,place)
- VALUES('1','法國(guó)');
- > INSERT INTO user_log(user_id,place)
- VALUES('2','日本');
- > INSERT INTO user_log(user_id,place)
- VALUES('1','韓國(guó)');
然后接著查看數(shù)據(jù)的存儲(chǔ)情況：
- > SELECT * FROM user_log;
- user_id place login_time
- 1 中國(guó) 2015-11-20 10:32:41
- 1 美國(guó) 2015-11-20 10:33:18
- 1 法國(guó) 2015-11-20 10:48:00
- 2 中國(guó) 2015-11-20 10:33:18
- 2 日本 2015-11-20 10:48:00
這樣以來(lái)，如果我們要獲取某一個(gè)用戶(hù)的登陸情況，就可以非常的方便，因?yàn)樵撚脩?hù)的所有登陸記錄是按照ID聚集的存儲(chǔ)在一起的，這樣主存緩存一個(gè)頁(yè)面的數(shù)據(jù)可能就OK了，如果是非聚集存儲(chǔ)的，假如某個(gè)id的數(shù)據(jù)分散在100個(gè)頁(yè)面，那么主存就要緩存這100個(gè)頁(yè)面，效率可想而知。
總結(jié)
這篇文中一開(kāi)始的構(gòu)想是想從計(jì)算機(jī)操作系統(tǒng)的存儲(chǔ)系統(tǒng)和B-Tree入手寫(xiě)的，結(jié)果寫(xiě)著寫(xiě)著發(fā)現(xiàn)，有點(diǎn)不太現(xiàn)實(shí)，一來(lái)文章長(zhǎng)度可能增加幾倍，二來(lái)可能自己都寫(xiě)暈了，既然是小白篇嘛，就先來(lái)個(gè)綜述吧！總之索引是非常強(qiáng)大而且有意思的，然而當(dāng)數(shù)據(jù)量達(dá)到一定量時(shí)，感覺(jué)索引也是有點(diǎn)乏力，但是盡量用好每一個(gè)索引是非常有必要而且是一種態(tài)度。
當(dāng)前標(biāo)題：小白學(xué)習(xí)mysql之高新能索引基礎(chǔ)篇
標(biāo)題鏈接：http://m.5511xx.com/article/dhiiigg.html
