亚洲综合图片区自拍_思思91精品国产综合在线观看_一区二区三区欧美_欧美黑人又粗又大_亚洲人成精品久久久久桥本

作者：鄭啟龍

摘要：

對(duì)于MYSQL的INNODB存儲(chǔ)引擎的索引，大家是不陌生的，都能想到是 B+樹(shù)結(jié)構(gòu)，可以加速SQL查詢。但對(duì)于B+樹(shù)索引，它到底“長(zhǎng)”得什么樣子，它具體如何由一個(gè)個(gè)字節(jié)構(gòu)成的，這些的基礎(chǔ)知識(shí)鮮有人深究。本篇文章從MYSQL行記錄開(kāi)始說(shuō)起，層層遞進(jìn)，包括數(shù)據(jù)頁(yè)，B+樹(shù)聚簇索引，B+樹(shù)二級(jí)索引，最后在文章末尾給出MYSQL索引的建議。文章涉及較多基礎(chǔ)知識(shí)，內(nèi)容較為枯燥，因此采用較多的圖片補(bǔ)充說(shuō)明，希望能對(duì)讀者有幫助。

【資料圖】

A. 一條記錄存儲(chǔ)格式：COMPACT行記錄結(jié)構(gòu)

mysql是關(guān)系型數(shù)據(jù)庫(kù)，每一行記錄都是表結(jié)構(gòu)定義的關(guān)系的 顯示表達(dá)。在腦中很直觀地想到，記錄存儲(chǔ)時(shí)也可能按行存儲(chǔ)。

的確，mysql是這么存儲(chǔ)一條行記錄的。但會(huì)添加一些額外信息，來(lái)補(bǔ)充行記錄信息。

有一個(gè)概念可能大家不熟悉，是【變長(zhǎng)字段】。mysql數(shù)據(jù)庫(kù)類型中的 VARCHAR(M), VARBINARY(M), 各種TEXT，BLOB類型，這些類型的數(shù)據(jù)長(zhǎng)度是可變的，稱 數(shù)據(jù)類型為可變長(zhǎng)類型的列 為 變長(zhǎng)字段。

?

??

另外，mysql會(huì)默認(rèn)為行記錄添加一些列（隱藏列）。上圖補(bǔ)充這些隱藏列之后，完整行記錄的結(jié)構(gòu)如：

?

??

DB_ROW_ID: 唯一標(biāo)識(shí)一條記錄，在表中未設(shè)置主鍵 或 未有不允許為NULL的UNIQUE鍵時(shí)，則MYSQL新增該隱藏列作為主鍵。DB_TRX_ID: 事務(wù)ID。DB_ROLL_PTR: 回滾指針。

下面再詳細(xì)的鋪開(kāi) ，關(guān)于記錄的額外信息 的具體內(nèi)容。

?

??

?

通過(guò)真實(shí)的數(shù)據(jù)庫(kù)表的行數(shù)據(jù)，來(lái)強(qiáng)化下上面的概念。 首先新增一個(gè)表，并在表中insert兩條記錄。

create table record_format_demo(c1 varchar(10),c2 varchar(10) not null,c3 char(10),c4 varchar(10)) charset=ascii row_format=compactinsert into record_format_demo(c1, c2, c3, c4) values("aaaa", "bbb", "cc", "d"),("eeee", "fff", NULL, NULL);

做一個(gè)簡(jiǎn)單的查詢，驗(yàn)證數(shù)據(jù)正常寫(xiě)入。

?

??

分析這兩行數(shù)據(jù)的存儲(chǔ)記錄。

第一行記錄：

?

??

第二行記錄：

?

??

應(yīng)該會(huì)注意到，變長(zhǎng)字段長(zhǎng)度列表 與 NULL值列表都是逆序存放的。原因是：這樣可以使得記錄中位置靠前的字段 和 它們對(duì)應(yīng)的字段長(zhǎng)度信息在內(nèi)存中的距離更近，可能會(huì)提高 高速緩存的命中率。

B. 盛放記錄的盒子：數(shù)據(jù)頁(yè)

為了更清楚的理解 數(shù)據(jù)頁(yè)結(jié)構(gòu) 以及 下文中的索引，更換一個(gè)帶主鍵的表。

CREATE TABLE page_demo(c1 INT,c2 INT,c3 VARCHAR(10000),PRIMARY KEY (c1)) CHARSET=ascii ROW_FORMAT=Compact;INSERT INTO page_demo VALUES(1, 100, "aaaa"),(2, 200, "bbbb"),(3, 300, "cccc"),(4, 400, "dddd");

做一個(gè)簡(jiǎn)單的查詢，驗(yàn)證數(shù)據(jù)正常寫(xiě)入。

?

??

根據(jù)行記錄結(jié)構(gòu)中的next_recrod屬性的含義，多條行記錄是以單向鏈表的形式存儲(chǔ)。mysql為了后續(xù)更好的查詢，單向鏈表上的記錄是按照主鍵順序排列的。上述這四條記錄，可以顯示的畫(huà)成：

?

??

假如刪除其中c1=2這條數(shù)據(jù)，則單向鏈表變更如下。其中變化點(diǎn)為 c1=2 這條數(shù)據(jù)中，deleted_flag變更成0， next_record=0，但并沒(méi)有從磁盤(pán)上直接清除掉，head_no也未清除。第一條記錄的next_record 指向了第三條記錄。

?

??

?

當(dāng)我們查詢數(shù)據(jù)時(shí)，如果數(shù)據(jù)以行記錄的形式一條一條從磁盤(pán)加載到內(nèi)存中，那么因?yàn)镮O過(guò)多的原因，整體性能肯定較為低效。因此mysql規(guī)定，磁盤(pán)與內(nèi)存交換數(shù)據(jù)的基本單位是一個(gè)頁(yè)大小。這個(gè)頁(yè)大小 默認(rèn)是16K。根據(jù)頁(yè)中存儲(chǔ)的數(shù)據(jù)類型不同，頁(yè)也分成許多類型。對(duì)于存儲(chǔ)行記錄的頁(yè)，稱為索引頁(yè)（Index Page），也稱為數(shù)據(jù)頁(yè)。

那么接下來(lái)我們看看，數(shù)據(jù)頁(yè)的結(jié)構(gòu)如何，一條條行記錄如何存放在數(shù)據(jù)頁(yè)中。先上個(gè)圖。

?

??

?

從上圖中，我們可以猜到，數(shù)據(jù)頁(yè)總共分成7個(gè)模塊，目前咱們只關(guān)心 User Records 部分，它存儲(chǔ)的就是用戶真實(shí)的行記錄。但是一個(gè)數(shù)據(jù)頁(yè)的空間總共是16K，不會(huì)自動(dòng)增大空間，隨著User Records 模塊中的行記錄越來(lái)越多，那么肯定有其他模塊的空間就越來(lái)越小。這個(gè)模塊是 Free Space，是頁(yè)中尚未使用的空間。更新一下上面這個(gè)圖，補(bǔ)充 Free Space的內(nèi)容。隨著User Records中行記錄的增加，F(xiàn)ree Space空間則越來(lái)越小。

?

??

在一個(gè)數(shù)據(jù)頁(yè)中，除了真實(shí)的行記錄之外，還有兩條固定的偽記錄。其中一條記錄稱為 infimum 【[?n?fa?m?m] ，下確界】行記錄，規(guī)定是數(shù)據(jù)頁(yè)中記錄的最小值。infimum記錄特別簡(jiǎn)單，僅包含了 記錄頭信息(5字節(jié)) + 真實(shí)記錄數(shù)據(jù)(8字節(jié))，其中【69 6E 66 69 6D 75 6D 00】16進(jìn)制轉(zhuǎn)換成對(duì)應(yīng)的單詞，就是【infimum】。

?

??

另外一條記錄稱為 supremum【[s?"prim?m]，上確界】行記錄，規(guī)定是數(shù)據(jù)頁(yè)中記錄的最大值。supremum記錄同樣特別簡(jiǎn)單，僅包含了 記錄頭信息(5字節(jié)) + 真實(shí)記錄數(shù)據(jù)(8字節(jié))，其中【73 75 70 72 65 6D 75 6D】16進(jìn)制轉(zhuǎn)換成對(duì)應(yīng)的單詞，就是【supremum】。

?

??

再更新一版數(shù)據(jù)庫(kù)頁(yè)結(jié)構(gòu)， 補(bǔ)充上infimum 與 supremum。

?

??

既然規(guī)定了頁(yè)面中的最小記錄 與 最大記錄，理所應(yīng)當(dāng)，上文中串聯(lián)各個(gè)行記錄的單向鏈表，則應(yīng)該有一個(gè)固定的頭部 與 固定的尾部。更新一下單鏈表的圖。注意下infimum 與 supremum中的幾個(gè)關(guān)鍵值。

infimum: n_owned=1，表示是某一個(gè)分組的最后一條記錄，當(dāng)前分組共1條記錄；record_type=2； next_record=第一條真實(shí)行記錄的真實(shí)值的相對(duì)位置。supremum: n_owned=5，表示是某個(gè)分組的最后一條記錄，當(dāng)前分組共5條記錄；record_type=3； next_record=0，表示記錄是本數(shù)據(jù)頁(yè)中最后一條記錄。

?

??

OK，到現(xiàn)在數(shù)據(jù)頁(yè)中完整的單鏈表已經(jīng)形成了。思考一個(gè)問(wèn)題，如何根據(jù)主鍵值，在數(shù)據(jù)頁(yè)中的單鏈表如何查找到相應(yīng)的數(shù)據(jù)。最直接的做法是：從 infimum記錄開(kāi)始，沿著單鏈表的順序不斷的向后查找，直到supremum結(jié)束。在這期間，找到滿足條件的記錄就返回，找不到則不返回。

一個(gè)數(shù)據(jù)頁(yè)默認(rèn)大小是16K，用于存儲(chǔ)真實(shí)行記錄的空間超過(guò) 98%以上。也就是說(shuō)，一個(gè)數(shù)據(jù)頁(yè)中在行記錄填充滿的情況下，行記錄的數(shù)量是較多的（當(dāng)然與行記錄大小有關(guān)）。如果每次查詢都從單鏈表的infimum記錄 一直到 supremum記錄，肯定是無(wú)法接受的，很有必要對(duì)此現(xiàn)狀進(jìn)行優(yōu)化。由此，引出了數(shù)據(jù)頁(yè)中的另一個(gè)模塊，Page Directory，頁(yè)目錄。

首先返回看下上面完整單鏈表中，infimum記錄 與 supremum記錄中的兩個(gè)值，n_owned。這個(gè)值分別為 n_owned=1 與 n_owned=5。

參考下n_owned的定義，它是：【頁(yè)面分組之后，每個(gè)組最后一條行記錄中該值等于本組內(nèi)的記錄數(shù)量。本組內(nèi)其他記錄該值都等于0?！?對(duì)于上面的單鏈表，其它行記錄的owned值 都為零。也就是說(shuō)，infimum單條記錄作為一個(gè)組，剩余的四條行記錄+supremum記錄作為一個(gè)組。mysql規(guī)定：

?對(duì)于infimum記錄所在的分組只能有1條記錄，也就是它本身。

?對(duì)于supremum記錄所在的分組的記錄數(shù)在1~8條之間。

?其它的分組記錄的條數(shù)范圍，在4~8條之間。

將每個(gè)組中 最后一條記錄在頁(yè)面中的地址偏移量（該記錄的真實(shí)數(shù)據(jù)與數(shù)據(jù)頁(yè)中第0個(gè)字節(jié)之間的距離）單獨(dú)提取出來(lái)，以倒序存儲(chǔ)到數(shù)據(jù)頁(yè)中的固定模塊中。這個(gè)模塊，就稱為：Page Directory。Page Directory中存儲(chǔ)的地址偏移量，也稱為Slot 【[sl?t]， 槽】，每個(gè)Slot兩字節(jié)?！究梢韵胂霝樯妒莾勺止?jié)？】

再次更新下數(shù)據(jù)頁(yè)結(jié)構(gòu)圖。

?

??

目前的只有四條記錄，兩個(gè)分組，數(shù)量太少了。我們繼續(xù)往表中繼續(xù)增加一些記錄。

INSERT INTO page_demo VALUES(5, 500, "eeee"),(6, 600, "ffff"),(7, 700, "gggg"),(8, 800, "hhhh"),(9, 900, "iiii"),(10, 1000, "jjjj"),(11, 1100, "kkkk"),(12, 1200, "llll"),(13, 1300, "mmmm"),(14, 1400, "nnnn"),(15, 1500, "oooo"),(16, 1600, "pppp");INSERT INTO page_demo VALUES(5, 500, "eeee"),(6, 600, "ffff"),(7, 700, "gggg"),(8, 800, "hhhh"),(9, 900, "iiii"),(10, 1000, "jjjj"),(11, 1100, "kkkk"),(12, 1200, "llll"),(13, 1300, "mmmm"),(14, 1400, "nnnn"),(15, 1500, "oooo"),(16, 1600, "pppp");

?

??

?

在不斷的插入新行記錄時(shí)，因此不同類型分組數(shù)量的約束，所以分組也會(huì)增加。這個(gè)過(guò)程如下：

?在初始情況下，一個(gè)數(shù)據(jù)頁(yè)中只會(huì)有 infimum 與 supremum 這兩條記錄，它們分別屬于兩個(gè)組。此時(shí)Page Directory 也只會(huì)有兩個(gè)slot，分別代表infimum的地址偏移量 與 supremum的地址偏移量。

?之后每新增一條行記錄，都會(huì)從Page Directory中找到對(duì)應(yīng)的記錄的主鍵值 比 待新增記錄的主鍵值大 并且差值最小的slot【slot是一個(gè)組內(nèi)最大的那條記錄在頁(yè)面中的地址偏移量，通過(guò)slot可以快速找到對(duì)應(yīng)記錄的主鍵值】， 然后把該slot對(duì)應(yīng)的記錄的 n_owned值加1，表示本組內(nèi)新增了一條記錄，直到該組中的記錄數(shù)等于8個(gè)。

?當(dāng)一個(gè)組中的記錄數(shù)等于8后，再插入一條記錄，會(huì)將組中的記錄拆分成兩個(gè)組，其中一個(gè)組中是4條記錄，另外一個(gè)組中是5條記錄。拆分過(guò)程中，會(huì)新增一個(gè)slot，記錄這個(gè)新增分組中最大的那條記錄的地址偏移量。

現(xiàn)在來(lái)看看下，目前該數(shù)據(jù)頁(yè)中的行記錄的分組情況。

?

??

?

來(lái)演繹一個(gè)根據(jù)主鍵查詢行記錄的例子。假如想查詢主鍵值 ，也就是C1=6的行記錄。通過(guò)二分法查找，過(guò)程如下：

1.設(shè)置low=0，high=4。計(jì)算中間slot的位置，(0 + 4) / 2 = 2, 通過(guò)slot 2 查詢到對(duì)應(yīng)的主鍵值等于8。因?yàn)? > 6， 所以設(shè)置high = 2， low = 0不變。

2.重新計(jì)算中間slot的位置，（0 + 2）/ 2 = 1, 查看 slot 1對(duì)應(yīng)記錄的主鍵值為4。又因?yàn)?4 < 6, 所以設(shè)置low = 1，high 不變。

3.此時(shí)low = 1， high = 2， 所以確定主鍵值為6的行記錄在 slot2 對(duì)應(yīng)的組中。此時(shí)找到slot 2的最小一條記錄【通過(guò)slot 1 的next_record找到slot 2的最小記錄】，遍歷slot 2中的所有記錄即可。

?

截止目前，數(shù)據(jù)頁(yè)模塊中，還要三個(gè)模塊是未知的?；叵胍幌拢瑢?duì)于一條行記錄，它有 記錄頭信息 來(lái)描述這條行記錄的相關(guān)信息，那么對(duì)于一個(gè)數(shù)據(jù)頁(yè)，它有對(duì)應(yīng)的頭部來(lái)記錄數(shù)據(jù)頁(yè)的相關(guān)信息嗎？

有的，自然是有的，而且還不少。這個(gè)模塊就稱為 Page Header。

?

??

Page Header的結(jié)構(gòu)如下：

主要作用是標(biāo)識(shí) 數(shù)據(jù)頁(yè)中記錄了多少條記錄，F(xiàn)ree Space在頁(yè)面中的地址偏移量，頁(yè)目錄中包含多少slot等等。

額外說(shuō)下page_direction 與 page_n_direction的含義。

page_direction: 假如新插入的一條記錄的主鍵值比上一條記錄的主鍵值比上一條記錄大，我們說(shuō)這條記錄的插入方向是右邊，反之則是左邊。用來(lái)表示最后一條記錄插入方向的狀態(tài)就是page_direction。

page_n_direction: 假設(shè)連續(xù)幾次插入新記錄的方向都是一致的，InnoDB會(huì)把沿著同一個(gè)方向插入記錄的條數(shù)記下來(lái)，這個(gè)條數(shù)就用PAGE_N_DIRECTION這個(gè)狀態(tài)表示。 當(dāng)然，如果最后一條記錄的插入方向改變了的話，這個(gè)狀態(tài)的值會(huì)被清零重新統(tǒng)計(jì)。

?

??

?

到此為止，僅剩下兩個(gè)模塊了，加油啊。

上文中的Page Header 是專門針對(duì)數(shù)據(jù)頁(yè)記錄的各種狀態(tài)信息。但數(shù)據(jù)頁(yè) 僅僅是多種頁(yè)類型中的一種，其它的還有例如undo日志頁(yè)，溢出頁(yè)，存儲(chǔ)段信息頁(yè)，存儲(chǔ)區(qū)信息頁(yè)等。因此mysql 使用了File Header 來(lái)描述各種頁(yè)的通用信息。

從fil_page_prev 與 fil_page_next 兩個(gè)屬性可以聯(lián)想到，不同頁(yè)之間是有關(guān)聯(lián)的，而且是以雙向鏈表的形式。

?

??

最后一個(gè)模塊，F(xiàn)ile Trailer 【 [?tre?l?(r)]，掛車】。

InnoDB存儲(chǔ)引擎會(huì)把數(shù)據(jù)存儲(chǔ)到磁盤(pán)上，但是磁盤(pán)速度太慢，需要以頁(yè)為單位把數(shù)據(jù)加載到內(nèi)存中處理，如果該頁(yè)中的數(shù)據(jù)在內(nèi)存中被修改了，那么在修改后的某個(gè)時(shí)間需要把數(shù)據(jù)同步到磁盤(pán)中。

但是在同步了一半的時(shí)候中斷電了怎么處理呢？此時(shí)就需要靠 File Trailer 模塊中數(shù)據(jù)起作用。

?

??

展示下完整的數(shù)據(jù)頁(yè)結(jié)構(gòu)。

?

??

盜用一下網(wǎng)上的一個(gè)很好的數(shù)據(jù)頁(yè)圖。

?

??

?

C. 快速查詢的秘籍：B+樹(shù)索引

上文在介紹File Header時(shí)，我們特別說(shuō)明了里面的兩個(gè)數(shù)據(jù)：FIL_PAGE_PREV，指向前一個(gè)頁(yè)號(hào)。FIL_PAGE_NEXT, 指向后一個(gè)頁(yè)號(hào)。由此可以得出，多個(gè)數(shù)據(jù)頁(yè)之間的數(shù)據(jù)結(jié)構(gòu)是雙鏈表。

上文使用的數(shù)據(jù)共有16條，為了演示這個(gè)雙鏈表的效果，現(xiàn)在假設(shè)【僅僅是假設(shè)】每個(gè)頁(yè)中存放不超過(guò)4條行記錄。則上文的16條記錄，形成的數(shù)據(jù)頁(yè)雙鏈表結(jié)構(gòu)如下圖【此處省略了許多非必要展示的字段】。

?

??

從上面這個(gè)鏈表，可以得到以下結(jié)論：

1.雙向鏈表的頁(yè)號(hào)并不保證是連續(xù)的。

2.下一個(gè)數(shù)據(jù)頁(yè)中用戶記錄的主鍵值必須大于上一個(gè)頁(yè)中用戶記錄的主鍵值。【在依次寫(xiě)入主鍵數(shù)據(jù)不連續(xù)的行記錄時(shí)，會(huì)發(fā)生頁(yè)中數(shù)據(jù)的遷移?！?/p>

從目前的雙向鏈表結(jié)構(gòu)中，想要根據(jù)主鍵值查找記錄，也只能是從第一頁(yè)開(kāi)始，一頁(yè)一頁(yè)的依次查找。雖然在一個(gè)數(shù)據(jù)頁(yè)中，可以根據(jù) Page Directory進(jìn)行快速的二分查找，但總體效率肯定不盡人意。得優(yōu)化。

我們注意到，【下一個(gè)數(shù)據(jù)頁(yè)中用戶記錄的主鍵值必須大于上一個(gè)頁(yè)中用戶記錄的主鍵值】。因此，首先進(jìn)行第一步改進(jìn)。維護(hù)一個(gè)目錄項(xiàng)，目錄項(xiàng)中記錄某個(gè)頁(yè)中主鍵的最小值以及頁(yè)號(hào)，各個(gè)目錄項(xiàng)再以單向鏈表的形式鏈接起來(lái)。這樣就可以根據(jù)主鍵查詢目錄項(xiàng)，得到滿足的條件頁(yè)，再進(jìn)入相應(yīng)的頁(yè)中查詢行記錄即可。

?

??

到現(xiàn)在看看，目錄項(xiàng)是不是也很像行記錄，只是它的列值是主鍵與頁(yè)號(hào)。那把這些目錄項(xiàng)維護(hù)成在一個(gè)頁(yè)中看看效果。毫無(wú)違和感，渾然天成。現(xiàn)在看到的，就是主鍵索引的雛形了。

?

??

目前數(shù)據(jù)有些少了，繼續(xù)補(bǔ)充一些數(shù)據(jù)，再畫(huà)個(gè)圖看看。

INSERT INTO page_demo VALUES(17, 1700, "qqqq"),(18, 1800, "rrrr"),(19, 1900, "sss"),(20, 2000, "tttt");

?

??

?

??

現(xiàn)在看到的，就是一個(gè)典型的INNODB的主鍵索引了。它包含以下特點(diǎn)：

1.整個(gè)主鍵索引的數(shù)據(jù)結(jié)構(gòu)是一棵樹(shù)，具體是以B+樹(shù)實(shí)現(xiàn)。

2.葉子節(jié)點(diǎn)與非葉子節(jié)點(diǎn)中的行記錄按照主鍵大小順序排成一個(gè)單向鏈表，頁(yè)內(nèi)的記錄被劃分成若干組。可以利用Page Directory進(jìn)行二分法快速查找到行記錄。

3.存放用戶記錄的數(shù)據(jù)頁(yè)，根據(jù)頁(yè)中用戶記錄的主鍵大小順序排成一個(gè)雙向鏈表。所有用戶記錄都存放在葉子節(jié)點(diǎn)上。

4.存放目錄項(xiàng)記錄的數(shù)據(jù)頁(yè)都是非葉子節(jié)點(diǎn)，分層不同的層級(jí)。同一層級(jí)中的頁(yè)也是根據(jù)頁(yè)中目錄項(xiàng)記錄的主鍵大小順序，排成一個(gè)雙向鏈表。

通常也把INNODB的B+樹(shù)索引稱為 聚簇索引（clustered/?kl?st?d / index），所有的真實(shí)數(shù)據(jù)都存儲(chǔ)在聚簇索引中?！舅饕磾?shù)據(jù)，數(shù)據(jù)即索引】。

通常除了主鍵索引之外，肯定還會(huì)建一些普通索引，稱為二級(jí)索引，或者輔助索引。上面的數(shù)據(jù)，我們以上文中的數(shù)據(jù) C2列建立一個(gè)二級(jí)索引看看。

?

??

現(xiàn)在來(lái)看看下，INNODB中 二級(jí)索引的特點(diǎn)。

1.二級(jí)索引的數(shù)據(jù)結(jié)構(gòu) 與 聚簇索引一樣，是B+樹(shù)結(jié)構(gòu)。

2.二級(jí)索引的所有目錄項(xiàng)頁(yè)存儲(chǔ)行記錄的真實(shí)數(shù)據(jù)是 索引列+頁(yè)號(hào)。

3.二級(jí)索引的非葉子節(jié)點(diǎn)存儲(chǔ)行記錄的真實(shí)數(shù)據(jù)是 索引列+主鍵值。

因?yàn)樵诙?jí)索引中查詢到的是主鍵值，如果想要得到完整的行記錄，則需要拿著主鍵值再到聚簇索引中再進(jìn)行一次查詢。這個(gè)過(guò)程稱為【回表】。【回表的過(guò)程，特別要強(qiáng)調(diào)下每次對(duì)于二級(jí)索引來(lái)說(shuō)，每次查詢到一條滿足二級(jí)索引字段條件的記錄時(shí)，都進(jìn)行一次 回表 判斷是否滿足其他條件，然后將滿足所有條件的一條查詢結(jié)果返回給客戶端?！?/strong>

再講講聯(lián)合索引。現(xiàn)在以C2 與 C3兩列作為聯(lián)合索引，為了更好的展示聯(lián)合索引的效果，先修改幾條行記錄。

update page_demo set c3 = "dddd" where c2 = 100;update page_demo set c3 = "cccc" where c2 = 200;update page_demo set c3 = "bbbb" where c2 = 300;update page_demo set c3 = "aaaa" where c2 = 400;update page_demo set c2 = 300 where c1 = 4;

?

??

給聯(lián)合索引畫(huà)個(gè)圖。

?

??

總結(jié)下聯(lián)合索引的特點(diǎn)：

聯(lián)合索引的數(shù)據(jù)頁(yè)中記錄的排序，默認(rèn)是按照定義聯(lián)合索引的第一列排序的，在第一列值相等的情況下，再按照第二列排序。其它的方面均與單列的二級(jí)索引無(wú)差別。

聯(lián)合索引還有一個(gè)比較特殊的使用場(chǎng)景：最左前綴匹配。例如有聯(lián)合索引的列包含：C1，C2，C3 三列，而且順序也是 C1，C2，C3。則查詢語(yǔ)句：select * from page_demo where c1 = x and c2 = y and c3= z, 使用到了C1，C2，C3 列排序的結(jié)果。select * from page_demo where c1 = x and c2 = y, 使用到了C1，C2 列排序的結(jié)果。select * from page_demo where c1 = x and c3 = z, 僅使用到了C1 列排序的結(jié)果。

?

D. 索引的優(yōu)缺點(diǎn)及建議

優(yōu)點(diǎn)：

1.對(duì)于等值查詢，可快速定位到對(duì)于的行記錄。

2.對(duì)于范圍查詢，可輔助縮小掃描區(qū)間。

3.當(dāng)ORDER BY的列名 與 索引的列名完全一致時(shí)，可加快排序的順序。

4.當(dāng)GROUP BY的列名 與 索引的列名完全一致時(shí)，可加快分組。

5.當(dāng)二級(jí)索引列中 包含了 SELECT 關(guān)鍵字后面寫(xiě)明的所有列，則在查詢完成二級(jí)索引之后無(wú)需進(jìn)行回表操作，直接返回即可。這種情況，稱為【覆蓋索引】。

缺點(diǎn)：

1.建立索引占用磁盤(pán)空間。

2.對(duì)表中的數(shù)據(jù)進(jìn)行 增加，刪除，修改 操作時(shí)，都需要修改各個(gè)索引樹(shù)，特別是如果新增的行記錄的主鍵順序不是遞增的，就會(huì)產(chǎn)生頁(yè)分裂，頁(yè)回收等操作，有較大的時(shí)間成本。

3.當(dāng)二級(jí)索引列的值 的 不重復(fù)值的個(gè)數(shù)較少時(shí)，通過(guò)二級(jí)索引查詢找到的數(shù)據(jù)量就會(huì)比較多，相應(yīng)的就會(huì)產(chǎn)生過(guò)多的回表操作。

4.在執(zhí)行查詢語(yǔ)句的時(shí)候，首先要生成一個(gè)執(zhí)行計(jì)劃。通常情況下，一個(gè)SQL在執(zhí)行過(guò)程中最多使用一個(gè)二級(jí)索引，在生成執(zhí)行計(jì)劃時(shí)需要計(jì)算使用不同索引執(zhí)行查詢時(shí)所需的成本，最后選擇成本最低的那個(gè)索引執(zhí)行查詢。因此，如果建立太多的索引，就會(huì)導(dǎo)致成本分析過(guò)程耗時(shí)太多，從而影響查詢語(yǔ)句的性能。

建議：

1.只為用于搜索，排序，分組的列創(chuàng)建索引。

2.索引的列需要有辨識(shí)性，盡可能地區(qū)分出不同的記錄。

3.索引列的類型盡量小。因?yàn)閿?shù)據(jù)類型越小，索引占用的存儲(chǔ)空間就越少，在一個(gè)數(shù)據(jù)頁(yè)內(nèi)就可以存放更多的記錄，磁盤(pán)I/O帶來(lái)的性能損耗也就越小。

4.如果需要對(duì)很長(zhǎng)的字段進(jìn)行快速查詢，可考慮為列前綴建立索引?！綼lter table table_M add index idx_key1(column_n(10)) --> 將table_M表的 idx_key1列的前10個(gè)字符創(chuàng)建索引】

5.覆蓋索引，當(dāng)二級(jí)索引列中 包含了 SELECT 關(guān)鍵字后面寫(xiě)明的所有列，則在查詢完成二級(jí)索引之后無(wú)需進(jìn)行回表操作，直接返回即可。因此，編寫(xiě)【select *】的時(shí)候，要想想是否必要。

6.在查詢語(yǔ)句中，索引列不要參與 條件值計(jì)算，也是把條件值計(jì)算完成之后，再和索引列對(duì)比。【否則MYSQL會(huì)認(rèn)為 搜索條件不能形成 合適的掃描區(qū)間來(lái)減少掃描的記錄數(shù)量】

?