Schema的最佳化和索引

Hash索引

Hash索引是建立在Hash table至上的，並且它只對準確尋找有效，也就是說，必須尋找索引上的每一個列。對於每個行，儲存引擎計算了索引列的Hash code。這個hash code是非常小，並且對於其他行不同的數值，這些Hash code也是不相同的。在索引中儲存了hash code並且在hash table中儲存了一個指標指向每一行。

在MySQL中，只有Memory儲存引擎支援顯式的Hash索引。雖然Memory表也可以使用B-Tree索引，但是它們是預設的索引類型是Hash索引。Memory引擎支援非唯一的Hash索引，這在資料庫領域中是不尋常的。如果多個值有相同的Hash code，那麼索引就會在在相同的hash table的實體中，使用一個linked list儲存這些值所屬行的指標。

說一個例子吧，假設表結構如下

CREATE TABLE testhash (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
KEY USING HASH(fname)
) ENGINE=MEMORY;

包含的資料如下：

mysql> SELECT * FROM testhash;
+--------+-----------+
| fname | lname |
+--------+-----------+
| Arjen | Lentz |
| Baron | Schwartz |
| Peter | Zaitsev |
| Vadim | Tkachenko |
+--------+-----------+

假設我們有一個hash的函數叫f(),它會返回如下值（這些都是例子，並不是真實的值）：

f('Arjen') = 2323
f('Baron') = 7437
f('Peter') = 8784
f('Vadim') = 2458

索引的資料結構為

Slot Value
2323 Pointer to row 1
2458 Pointer to row 4
7437 Pointer to row 2
8784 Pointer to row 3

要注意的是這個slot是有序的。但是行並不是有序。現在我們執行下面的查詢

mysql> SELECT lname FROM testhash WHERE fname='Peter';

MySQL將會計算Peter的hash值，並且用它來找到索引中的指標。因為f(peter)=8784。MySQL會在索引中尋找8784，也就找到了它的指標，指向行3。最後一步就是把行3的值和Peter做個比較。來確定是否是正確的行。

因為索引本身儲存的就是比較短的Hash值，所以hash索引是壓縮的。hasn值的長度和列的類型並沒有什麼關係。對一個tinyint建立一個hash索引的長度和一個值非常大列類型的長度是一樣的。

因此尋找是非常輕量和快速的。然而，hash索引有如下的限制：

因為索引僅僅包含了hash值和行的指標，而並不是資料本身，所以MySQL並不能使用在索引中的值，而去避免讀取行。幸運的是，訪問記憶體中的行是非常快的，因此一般來說不太會降低效能。

MySQL不能對排序使用hash索引。因為它們沒有儲存排序後的行。

Hash索引不支援部分鍵的匹配，因為它們計算的是整個索引值的hash值。所以如果你對（A,B）進行hash索引，並且你的查詢僅僅以A的條件進行查詢，此時Hash索引就會失效。

Hash索引只支援是否相等的一些比較。比如=，IN(),<=>操作都可以。（注意<>和<=>不是一樣的）。它們不能加速範圍查詢。比如WHERE PRICE>100。

在hash索引訪問資料是非常快速的，除非有一些衝突（許多值有相同的hash值）。當衝突出現的時候，儲存引擎必須找到每個linked list中的指標所指向的行，並且比較這些行的值和要尋找的值作比較，直到找到正確的行。

如果有許多hash值衝突，那麼索引的維護操作將會非常慢。比如，如果你在一個列上建立了一個索引並且索引值衝突很多，那麼之後你從表中刪除一個行，從索引找到正確的指標的消耗是非常大的。儲存引擎會在Linked list中尋找具有相同hash值每一行的指標，然後找到了再刪除這個要刪除行的引用。

這些限制導致了只有在特殊的情況下才能使用Hash索引。然而，它們如果符合了應用的需求，效能就會有夢幻般的提升。一個例子是關於資料倉儲的，經典的star shema需要很多串連來尋找表。Hash索引可以準確的找到哪張表是所需的。

除了Memory儲存引擎是hash索引，NDB叢集儲存引擎也支援唯一的hash索引。這個功能主要針對於NDB叢集索引，已經超出了本書的範圍。