今天研究下mysql中索引，首先我應(yīng)該知道的是，mysql中不同存儲(chǔ)引擎的索引工作方式不一樣，并且不是所有的存儲(chǔ)引擎都支持所有類型的索引。即使多個(gè)存儲(chǔ)引擎支持同一種類型的索引，那么他們的實(shí)現(xiàn)原理也是不同的，本文將講解Hash索引該如何設(shè)置

除了B-Tree 索引，MySQL還提供了如下索引：

Hash索引

只有Memory引擎支持，場(chǎng)景簡(jiǎn)單

R-Tree索引

MyISAM的一個(gè)特殊索引類型，主要用于地理空間數(shù)據(jù)類型

Full-text

MyISAM的一個(gè)特殊索引，主要用于全文索引，從MySQL 5.6開(kāi)始InnoDB支持全文索引

索引 / 存儲(chǔ)引擎MyISAMInnoDBMemoryB-Tree索引支持支持支持HASH索引不支持不支持支持R-Tree索引支持支持不支持Full-text索引支持支持不支持

最常用的索引也就是B-tree索引和Hash索引，且只有Memory， NDB兩種引擎支持Hash索引。 Hash索引適于key-value查詢，通過(guò)Hash索引比B-tree索引查詢更加迅速。但Hash索引不支持范圍查找例如<><==,>==等。 Memory只有在"="的條件下才會(huì)使用hash索引

MySQL在 8.0才支持函數(shù)索引，在此之前只能對(duì)列的前面某一部分進(jìn)行索引，例如標(biāo)題title字段，可以只取title的前10個(gè)字符索引，這樣的特性大大縮小了索引文件的大小，但前綴索引也有缺點(diǎn)，在order by和group by操作時(shí)失效。

create index idx_title on film(title(10));

1 特點(diǎn)

只存在數(shù)組，用一個(gè)hash函數(shù)把key轉(zhuǎn)換成一個(gè)確定的內(nèi)存位置，然后把value放在數(shù)組的該位置。使用 hash 自然會(huì)有哈希沖突可能，MySQL 采取拉鏈法解決。

Hash索引基于Hash表實(shí)現(xiàn)，只有查詢條件精確匹配Hash索引中的列時(shí)，才能夠使用到hash索引。對(duì)于Hash索引中的所有列，存儲(chǔ)引擎會(huì)為每行計(jì)算一個(gè)hashcode，Hash索引中存儲(chǔ)的就是hashcode。

例如一個(gè)維護(hù)了身份證號(hào)和姓名的表，根據(jù)身份證號(hào)查找對(duì)應(yīng)名字，其hash索引如下：

阿里面試官：設(shè)計(jì)個(gè)MySQL的Hash索引吧？

比如我們想查ID_card_n4對(duì)應(yīng)username：

將ID_card_n4通過(guò)hash函數(shù)算出A
按順序遍歷，找到User4

四個(gè)ID_card_n值并不一定遞增，這樣即使增加新的User，速度也快，只需在后追加。當(dāng)然缺點(diǎn)也很明顯，不是有序，所以hash索引做區(qū)間查詢速度很慢。比如要找身份證號(hào)在[ID_card_X, ID_card_Y]區(qū)間的所有用戶，就須全表掃描。

2 Hash索引的缺陷

必須二次查找
不支持部分索引查找、范圍查找
哈希碼可能存在哈希沖突，如果hash 算法設(shè)計(jì)不好，碰撞過(guò)多，性能也會(huì)變差
索引存放的是hash值,所以僅支持 < = > 以及 IN
無(wú)法通過(guò)操作索引來(lái)排序，因?yàn)榇娣诺臅r(shí)候會(huì)經(jīng)過(guò)hash計(jì)算，但是計(jì)算的hash值和存放的不一定相等，所以無(wú)法排序
不能避免全表掃描，只是由于在memory表里支持非唯一值hash索引，即不同的索引鍵，可能存在相同hash值
因?yàn)楣１硎且环N根據(jù)關(guān)鍵字直接訪問(wèn)內(nèi)存存儲(chǔ)位置的數(shù)據(jù)結(jié)構(gòu) ，所以利用其原理的hash 索引，也就需要將所有數(shù)據(jù)文件添加到內(nèi)存，這就很耗內(nèi)存
如果所有的查詢都是等值查詢，那么hash確實(shí)快，但實(shí)際上范圍查找數(shù)據(jù)更多
智能處理鍵值得全值匹配
查詢Hash函數(shù)決定著索引鍵的大小

要使InnoDB或MyISAM支持哈希索引，可以通過(guò)偽哈希索引來(lái)實(shí)現(xiàn)，叫自適應(yīng)哈希索引。

可通過(guò)增加一個(gè)字段，存儲(chǔ)hash值，將hash值建立索引，在插入和更新的時(shí)候，建立觸發(fā)器，自動(dòng)添加計(jì)算后的hash到表里。

哈希表這種結(jié)構(gòu)適用于只有等值查詢的場(chǎng)景，比如Memcached。

3 案例應(yīng)用

假如有一個(gè)非常非常大的表，比如用戶登錄時(shí)需要通過(guò)email檢索出用戶，如果直接在email列建索引，除了索引區(qū)間匹配，還要進(jìn)行字符串匹配比對(duì)，email短還好，如果長(zhǎng)的話這個(gè)查詢代價(jià)就比較大。若此時(shí)，在email建立哈希索引，查詢以int查詢，性能就比字符串比對(duì)查詢快多了。

Hash 算法

建立哈希索引，首先就要選定哈希算法，《高性能MySQL》說(shuō)到的CRC32算法。

INSERT UPDATE SELECT 操作

在表中添加hash值的字段：

ALTER TABLE `User` ADD COLUMN email_hash int unsigned NOT NULL DEFAULT 0;

接下來(lái)就是在UPDATE和INSERT時(shí)，自動(dòng)更新 email_hash 字段，通過(guò)觸發(fā)器實(shí)現(xiàn)：

DELIMITER |
CREATE TRIGGER user_hash_insert BEFORE INSERT ON `User` FOR EACH ROW BEGIN
SET NEW.email_hash=crc32(NEW.email);
END;
|
CREATE TRIGGER user_hash_update BEFORE UPDATE ON `User` FOR EACH ROW BEGIN
SET NEW.email_hash=crc32(NEW.email);
END;
|
DELIMITER ;

這樣SELECT請(qǐng)求就會(huì)變成：

SELECT `email`, `email_hash` FROM `User` WHERE 
	email_hash = CRC32(“xxoo@gmail.com”) 
			AND `email`= “xxoo@gmail.com”;

+----------------------------+------------+
| email | email_hash |
+----------------------------+------------+
| xxoo@gmail.com | 2765311122 |
+----------------------------+------------+

AND email = "xxoo@gmail.com" 是為了防止哈希碰撞時(shí)數(shù)據(jù)不準(zhǔn)確。

到此這篇關(guān)于MySQL面試題講解之如何設(shè)置Hash索引的文章就介紹到這了,更多相關(guān)MySQL 設(shè)置Hash索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: