Python MYSQL數(shù)據(jù)庫之聚集索引與輔助索引

一、 聚集索引與輔助索引

在數(shù)據(jù)庫中，B+樹的高度一般都在2~4層，這也就是說查找某一個(gè)鍵值的行記錄時(shí)最多只需要2到4次IO，這倒不錯(cuò)。因?yàn)楫?dāng)前一般的機(jī)械硬盤每秒至少可以做100次IO，2~4次的IO意味著查詢時(shí)間只需要0.02~0.04秒。

數(shù)據(jù)庫中的B+樹索引可以分為聚集索引（clustered index）和輔助索引（secondary index），

聚集索引與輔助索引相同的是：不管是聚集索引還是輔助索引，其內(nèi)部都是B+樹的形式，即高度是平衡的，葉子結(jié)點(diǎn)存放著所有的數(shù)據(jù)。

聚集索引與輔助索引不同的是：葉子結(jié)點(diǎn)存放的是否是一整行的信息

1、聚集索引

#InnoDB存儲(chǔ)引擎表是索引組織表，即表中數(shù)據(jù)按照主鍵順序存放。而聚集索引（clustered?index）就是按照每張表的主鍵構(gòu)造一棵B+樹， 同時(shí)葉子結(jié)點(diǎn)存放的即為整張表的行記錄數(shù)據(jù)，也將聚集索引的葉子結(jié)點(diǎn)稱為數(shù)據(jù)頁。聚集索引的這個(gè)特性決定了索引組織表中數(shù)據(jù)也是索引的一部分。 同B+樹數(shù)據(jù)結(jié)構(gòu)一樣，每個(gè)數(shù)據(jù)頁都通過一個(gè)雙向鏈表來進(jìn)行鏈接。 #如果未定義主鍵，MySQL取第一個(gè)唯一索引（unique）而且只含非空列（NOT?NULL）作為主鍵，InnoDB使用它作為聚簇索引。 #如果沒有這樣的列，InnoDB就自己產(chǎn)生一個(gè)這樣的ID值，它有六個(gè)字節(jié)，而且是隱藏的，使其作為聚簇索引。 #由于實(shí)際的數(shù)據(jù)頁只能按照一棵B+樹進(jìn)行排序，因此每張表只能擁有一個(gè)聚集索引。在多數(shù)情況下，查詢優(yōu)化器傾向于采用聚集索引。 因?yàn)榫奂饕軌蛟贐+樹索引的葉子節(jié)點(diǎn)上直接找到數(shù)據(jù)。此外由于定義了數(shù)據(jù)的邏輯順序，聚集索引能夠特別快地訪問針對范圍值得查詢。

聚集索引的好處之一：它對主鍵的排序查找和范圍查找速度非常快，葉子節(jié)點(diǎn)的數(shù)據(jù)就是用戶所要查詢的數(shù)據(jù)。如用戶需要查找一張表，查詢最后的10位用戶信息，由于B+樹索引是雙向鏈表，所以用戶可以快速找到最后一個(gè)數(shù)據(jù)頁，并取出10條記錄

#參照第六小結(jié)測試索引的準(zhǔn)備階段來創(chuàng)建出表student1 mysql>?desc?student1;?#最開始沒有主鍵

mysql>?explain?select?*?from?student1?order?by?id?desc?limit?10;?#Using?filesort，需要二次排序

mysql>?alter?table?student?add?primary?key(id);?#添加主鍵

mysql> explain select * mysql> explain select * from student1 order by id desc limit 10;?#基于主鍵的聚集索引在創(chuàng)建完畢后就已經(jīng)完成了排序，無需二次排序

聚集索引的好處之二：范圍查詢（range query），即如果要查找主鍵某一范圍內(nèi)的數(shù)據(jù)，通過葉子節(jié)點(diǎn)的上層中間節(jié)點(diǎn)就可以得到頁的范圍，之后直接讀取數(shù)據(jù)頁即可

mysql>? mysql>?alter?table?student1?drop?primary?key; Query?OK,?0?rows?affected?(0.07?sec) Records:?0??Duplicates:?0??Warnings:?0 mysql>?desc?student1;

mysql>?explain?select?*?from?student1?where?id?>?1?and?id?<100000;?#沒有聚集索引，預(yù)估需要檢索的rows數(shù)如下

mysql>?alter?table?student1?add?primary?key(id); Query?OK,?0?rows?affected?(0.04?sec) Records:?0??Duplicates:?0??Warnings:?0 mysql>?explain?select?*?from?student1?where?id?>?1?and?id?

2、輔助索引

表中除了聚集索引外其他索引都是輔助索引（Secondary Index，也稱為非聚集索引），與聚集索引的區(qū)別是：輔助索引的葉子節(jié)點(diǎn)不包含行記錄的全部數(shù)據(jù)。

葉子節(jié)點(diǎn)除了包含鍵值以外，每個(gè)葉子節(jié)點(diǎn)中的索引行中還包含一個(gè)書簽（bookmark）。該書簽用來告訴InnoDB存儲(chǔ)引擎去哪里可以找到與索引相對應(yīng)的行數(shù)據(jù)。

由于InnoDB存儲(chǔ)引擎是索引組織表，因此InnoDB存儲(chǔ)引擎的輔助索引的書簽就是相應(yīng)行數(shù)據(jù)的聚集索引鍵。如下圖

輔助索引的存在并不影響數(shù)據(jù)在聚集索引中的組織，因此每張表上可以有多個(gè)輔助索引，但只能有一個(gè)聚集索引。當(dāng)通過輔助索引來尋找數(shù)據(jù)時(shí)，InnoDB存儲(chǔ)引擎會(huì)遍歷輔助索引并通過葉子級別的指針獲得只想主鍵索引的主鍵，然后再通過主鍵索引來找到一個(gè)完整的行記錄。

舉例來說，如果在一棵高度為3的輔助索引樹種查找數(shù)據(jù)，那需要對這個(gè)輔助索引樹遍歷3次找到指定主鍵，如果聚集索引樹的高度同樣為3，那么還需要對聚集索引樹進(jìn)行3次查找，最終找到一個(gè)完整的行數(shù)據(jù)所在的頁，因此一共需要6次邏輯IO訪問才能得到最終的一個(gè)數(shù)據(jù)頁。

聚集索引和非聚集索引的區(qū)別：

聚集索引 1.紀(jì)錄的索引順序與無力順序相同 ???因此更適合between?and和order?by操作 2.葉子結(jié)點(diǎn)直接對應(yīng)數(shù)據(jù) ?從中間級的索引頁的索引行直接對應(yīng)數(shù)據(jù)頁 3.每張表只能創(chuàng)建一個(gè)聚集索引 非聚集索引 1.索引順序和物理順序無關(guān) 2.葉子結(jié)點(diǎn)不直接指向數(shù)據(jù)頁 3.每張表可以有多個(gè)非聚集索引，需要更多磁盤和內(nèi)容 ???多個(gè)索引會(huì)影響insert和update的速度

軟件開發(fā) 人工智能 云計(jì)算 機(jī)器學(xué)習(xí)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。