MySQL索引之雜湊索引

雜湊索引（Hash Index）建立在雜湊表的基礎上，它只對使用了索引中的每一列的精確尋找有用。對於每一行，儲存引擎計算出了被索引的雜湊碼（Hash Code），它是一個較小的值，並且有可能和其他行的雜湊碼不同。它把雜湊碼儲存在索引中，並且儲存了一個指向雜湊表中的每一行的指標。

在mysql中，只有memory儲存引擎支援顯式的雜湊索引。如果多個值有相同的雜湊碼，索引就會把行指標以鏈表的方式儲存在雜湊表的同一條記錄中。

雜湊索引的細節還有很多，由於myISAM和innodb並不支援，所以在這裡不詳解。

下面著力講解建立自己的MySQL雜湊索引

想法非常簡單，在標準的B-Tree索引上建立一個偽雜湊索引。它和真正的雜湊索引不是一回事，因為它還是使用B-Tree索引進行尋找。然而，它將會使用鍵的雜湊值進行尋找，而不是鍵自身。你所要做的事情就是在where子句中手動地定義雜湊函數。

例子：URL尋找。

URL通常會導致B-Tree索引變大，因為它們非常長。通常會按照下面的方式來尋找URL表。

mysql>select id from url where url='http://www.bkjia.com';

但是，如果移除掉url列上的索引並且給表添加一個被索引的url_src列，就可以按照下面的方式進行查詢：

mysql>select id from url where url='http://www.bkjia.com' and url_src=CRC32('http://www.bkjia.com');

mysql查詢最佳化工具注意到url_src列上有很小的，選擇性很高的索引，並且它會使用裡面的值進行索引尋找。即使有幾列相同的url_src值，也很容易進行精確的對比來確定需要的行。替代方案是把完整的URL索引為字串，它要慢很多。

這個辦法的一個缺點就是要維護雜湊值。你可以手工進行維護，在mysql5.0 以上版本中，可以使用觸發器來進行維護。

1.建立一個表：

1. create table pseudohash(
2.     id int unsigned NOT NULL auto_increment,   
3.     url varchar(255) NOT NULL,   
4.     url_src int unsigned NOIT NULL DEFAULT 0,   
5.     PRIMARY KEY(id)   
6. );  

接下來建立觸發器。我們先暫時更新一下命令分隔字元，這樣就可以在觸發器中使用分號：

1. DELIMITER |
2. CREATE TRIGGER pseudohash_src_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN SET NEW.url_src = crc32(NEW.url);   
3. END;   
4. |   
5. CREATE TRIGGER pseudohash_src_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN SET NEW.url_src = crc32(NEW.url);   
6. END;   
7. |   
8. DELIMITER;

剩下的工作就是驗證觸發器自動維護了雜湊值。

如果使用這種方式，就不應該使用SHA1()和MD5()這此雜湊函數。它們返回很長的字串，會浪費大量的儲存空間並且減慢比較速度。它們是強加密函數，被設計為不產生任務衝突。這並不是我們的目標。簡單的雜湊函數能在有較好效能的同時保證可接受的衝突率。當然，如果表有很多行並且CRC32()產生了很多衝突，就要實現自己的64位雜湊函數，要確保自己的函數返回整數，而不是字串。

mysql>select conv(right(md5('http://www.bkjia.com/'),16),16,10) as hash64;