InnoDB 中文參考手冊 --- 11 表和索引結(jié)構(gòu)
2024-07-21 02:08:53
供稿：網(wǎng)友
本文來源于網(wǎng)頁設(shè)計(jì)愛好者web開發(fā)社區(qū)http://www.html.org.cn收集整理，歡迎訪問。innodb 中文參考手冊 --- 犬犬(心帆)翻譯 11 表和索引結(jié)構(gòu)
mysql 在數(shù)據(jù)庫目錄下的 .frm 文件中存儲它的數(shù)據(jù)字典信息。但是每個(gè) innodb 類型表也同樣在 innodb 表空間內(nèi)的內(nèi)部的數(shù)據(jù)字典中存在它自己的進(jìn)入點(diǎn)。當(dāng) mysql 移除(drop) 一個(gè)表或一個(gè)數(shù)據(jù)庫時(shí)，它將同時(shí)刪除 .frm 文件，以及在 innodb 的數(shù)據(jù)字典中相對應(yīng)的進(jìn)入點(diǎn)。這就是為什么不能通過簡單的刪除 .frm 文件為移除數(shù)據(jù)庫中的 innodb 類型表的原因，以及為什么在 mysql 版本 <= 3.23.43 的版本中，drop database 不能用于 innodb 表的原因。
每一個(gè) innodb 表都有一個(gè)被稱為聚簇索引的特殊索引用于保存記錄行信息。如果一個(gè)表定義一個(gè) primary key ，那么主鍵的索引就是聚簇索引。
如果表沒有定義一個(gè) primary key ，mysql 將選出第一個(gè) not null 字段的 unique 鍵做為主鍵，innodb 也將用這個(gè)鍵的索引做為聚簇索引。如果表中沒有這樣的鍵，innodb 將在內(nèi)部產(chǎn)生一個(gè)聚簇索引，它是由按 innodb 分配給它們的 row id 順序排序的記錄行組成。這個(gè) row id 是一個(gè)單調(diào)地增加并插入新行的 6-byte 字段。因而由 row id 排序的記錄順序也就是插入時(shí)的物理順序。
通過聚簇索引訪問一個(gè)記錄行是非?？斓?，因?yàn)橛涗浶袛?shù)據(jù)與引導(dǎo)我們查找到它的索引在同一個(gè)頁面上。 在大多數(shù)的數(shù)據(jù)庫系統(tǒng)中記錄行數(shù)據(jù)與索引記錄通常并不是放在同一個(gè)頁面上的。如果一個(gè)表太大了，那么聚簇索引體系通常比傳統(tǒng)的方式更能減少磁盤 i/o 。
在非-聚簇索引(non-clustered indexes)中的記錄 (我們通常稱它為輔助索引secondary indexes)，在 innodb 中會(huì)為這行包含主鍵值。innodb 將使用這個(gè)主鍵值來在聚簇索引中查找這行。注意如果主鍵太長，那么輔助索引將會(huì)占用更多的空間。
innodb 在比較不同長度的 char 和 varchar 時(shí)，較短字串的多余長度將被空格(spaces)填充。
11.1 索引的物理結(jié)構(gòu)
all indexes in innodb 中所有的索引是索引記錄存放在樹的葉頁面(leaf pages)上的 b-trees。一個(gè)索引頁面的大小默認(rèn)為 16 kb。當(dāng)新的記錄被插入時(shí)，innodb 將試圖為將來的插入與更新索引記錄保留頁面的 1 / 16 空余。
如果索引記錄以一個(gè)連續(xù)的 (升序或降序) 被插入，那么索引頁面的將會(huì)被使用約 15/16 。如果以一個(gè)隨機(jī)的順序插入，那么頁面大約使用了 1/2 - 15/16 。如果一個(gè)索引頁面被撤銷(drop)地低于 1/2，那么 innodb 將縮短索引樹并釋放頁面空間。
11.2 插入緩沖
主鍵是一個(gè)唯一標(biāo)識符，新的記錄以主鍵的升序被插入，這在數(shù)據(jù)庫系統(tǒng)中是一個(gè)普遍的情形。因而在聚簇索引內(nèi)插入的值不需要在硬盤上隨意讀取。
另一方面，輔助索引通常是非唯一的(non-unique)，插入在輔助索引中是相當(dāng)隨意的順序。如果在 innodb 中不使用一個(gè)特殊的機(jī)制這將會(huì)引起大量隨機(jī)的磁盤 i/o。
如果一個(gè)索引記錄被插入到一個(gè)非唯一的輔助索引中去，innodb 將檢查輔助索引頁面是否已在緩沖池(buffer pool)中。在這種情形下，innodb 直接地將它插入到索引頁面中去。但是，如果在緩沖池中沒有發(fā)現(xiàn)索引頁面，inndb 將索引記錄插入到一個(gè)特殊的插入緩沖結(jié)構(gòu)中去。插入緩沖被控制地如些小以至于可以完全放在緩沖池中， 因而插入速度很快。
插入緩沖會(huì)定時(shí)地歸并到數(shù)據(jù)庫中的輔助索引樹中去。為了減少磁盤 i/o，通常將同一個(gè)頁面上的幾個(gè)插入同時(shí)歸并到索引樹上。插入緩沖可以提高向一個(gè)表中插入速度 15倍。
11.3 適應(yīng)性的散列索引 (adaptive hash indexes)
如果一個(gè)數(shù)據(jù)庫幾乎占滿了所有主同存，那么在其上運(yùn)行查詢的一個(gè)快捷之路就是使用散列索引(hash indexes)。innodb 有一個(gè)用于監(jiān)視在表定義的索引上的索引搜索動(dòng)作的自動(dòng)調(diào)整結(jié)構(gòu)，如果 innodb 發(fā)現(xiàn)一個(gè)散列索引對查詢有益，那么它將自動(dòng)地建造這個(gè)散列索引。
但是要注意地就是散列索引通常是基于表中已存在的 b-tree 創(chuàng)建的。innodb 可能通過 b-tree 中定義的任何長度的鍵的前綴來構(gòu)建散列索引，這依賴于 innodb 觀察 b-tree 上索引的模式。一個(gè)散列索引可以是部分的：它在緩沖池中并不需要有整個(gè) b-tree 索引的高速緩沖。innodb 按照需要來為經(jīng)常訪問的索引頁面構(gòu)建散列索引。
在理論上，通過這個(gè)適應(yīng)性的散列索引機(jī)制，innpdb 使它自己更適合于大的主存(ample main memory)，更接近于主存儲器數(shù)據(jù)庫系統(tǒng)體系(the architecture of main memory databases)。
11.4 記錄的物理結(jié)構(gòu) innodb 中每個(gè)索引記錄都包含一個(gè) 6 字節(jié)的頭。這個(gè)頭部用于聯(lián)連相連貫的記錄，也同樣用于行鎖定。 聚簇索引中的記錄包含了所有用戶定義的字段。另外，有一個(gè) 6-byte 字段用于記錄事務(wù) id(transaction id)和一個(gè) 7-byte 字段用于行指針(roll pointer)。 在一個(gè)表中，如果用戶沒有定義一個(gè)主鍵，那么每個(gè)聚簇索引記錄包含一個(gè) 6-byte 的行 id 字段(row id field)。 每個(gè)輔助索引記錄包含為聚簇索引鍵定義的所有字段。 一個(gè)索引包含著一個(gè)指向?qū)?yīng)字段記錄的指針。如果一個(gè)記錄的所有字段總長度< 128 bytes，那么這個(gè)指針為 1 byte，否則為 2 bytes。 innodb 在內(nèi)部也是以一個(gè)固定長度來存儲定長的字符型字段，比如 char(10)。對于 varchar 型字段，innodb 將截去結(jié)尾的空間。注意，mysql 可能內(nèi)部地將 char 轉(zhuǎn)換為 varchar。查看 mysql 用戶手冊有關(guān)“列規(guī)約的默認(rèn)地改變”( 'silent column specification changes')。 如果存儲一個(gè)變長的字段，一個(gè) sql null 為一個(gè) 0 bytes 被存儲，但是如果是一個(gè)定長字段將以固定的長度被存儲。以相應(yīng)的 nulls 存儲定長的空間的目的地就在于在將 null 字段值更新為 非 null 的值時(shí)不至產(chǎn)生索引頁面的磁盤碎片。
