除了B-Tree 索引,MySQL还提供了如下索引:
索引 / 存储引擎 | MyISAM | InnoDB | Memory |
---|---|---|---|
B-Tree索引 | 支持 | 支持 | 支持 |
HASH索引 |
|
| 支持 |
R-Tree索引 | 支持 | 支持 |
|
Full-text索引 | 支持 | 支持 |
|
最常用的索引也就是B-tree索引和Hash索引,且只有Memory
,NDB
两种引擎支持Hash索引。
Hash索引适于key-value查询,通过Hash索引比B-tree索引查询更加迅速。但Hash索引不支持范围查找例如<><==,>==等。
Memory只有在"="的条件下才会使用hash索引
MySQL在 8.0才支持函数索引,在此之前是能对列的前面某一部分进行索引,例如标题title字段,可以只取title的前10个字符索引,这样的特性大大缩小了索引文件的大小,但前缀索引也有缺点,在order by和group by操作时失效。
create index idx_title on film(title(10));
值存在数组,用一个hash函数把key转换成一个确定的内存位置,然后把value放在数组的该位置。使用 hash 自然会有哈希冲突可能,MySQL 采取拉链法解决。
Hash索引基于Hash表实现,只有查询条件精确匹配Hash索引中的列时,才能够使用到hash索引。对于Hash索引中的所有列,存储引擎会为每行计算一个hashcode,Hash索引中存储的就是hashcode。
比如我们想查ID_card_n4
对应username:
ID_card_n4
通过hash函数算出A四个ID_card_n
值并不一定递增,这样即使增加新的User,速度也快,只需在后追加。
当然缺点也很明显,不是有序,所以hash索引做区间查询速度很慢。比如要找身份证号在ID_card_X, ID_card_Y区间的所有用户,就须全表扫描。
要使InnoDB或MyISAM支持哈希索引,可以通过伪哈希索引来实现,叫自适应哈希索引。 可通过增加一个字段,存储hash值,将hash值建立索引,在插入和更新的时候,建立触发器,自动添加计算后的hash到表里。
哈希表这种结构适用于只有等值查询的场景,比如Memcached。
假如有一个非常非常大的表,比如用户登录时需要通过email检索出用户,如果直接在email列建索引,除了索引区间匹配,还要进行字符串匹配比对,email短还好,如果长的话这个查询代价就比较大。
若此时,在email建立哈希索引,查询以int查询,性能就比字符串比对查询快多了。
建立哈希索引,首先就要选定哈希算法,《高性能MySQL》说到的CRC32算法。
在表中添加hash值的字段:
ALTER TABLE `User` ADD COLUMN email_hash int unsigned NOT NULL DEFAULT 0;
接下来就是在UPDATE和INSERT时,自动更新 email_hash
字段,通过触发器实现:
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请求就会变成:
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"
是为了防止哈希碰撞时数据不准确。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。