索引研究：（五）Hash索引及其他索引

索引研究：（五）Hash索引及其他索引

哈希索引基于哈希表实现，只有精确索引所有列的查询才有效。

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每一个数据的指针。

MySQL中，只有Memory存储引擎显示支持hash索引。是Memory表的默认索引类型，尽管Memory表也可以使用B-Tree索引。

Memory存储引擎支持非唯一hash索引，这在数据库领域是罕见的：如果多个值有相同的hash code，索引把他们的行指针用链表保存到同一个hash表项中。

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

哈希索引中存储的是：哈希值+数据行指针

当你执行

select lname from testhash where fname = 'Peter'

MySQL会计算“Peter”的hash值，然后通过它来查询索引的行指针。因为f(‘Peter’) = 8784,MySQL会在索引中查找8784，得到指向记录3的指针。

Hash索引的限制：

1. 由于索引仅包含hash code和记录指针，所以，Mysql不能通过使用索引避免读记录，即使每次使用哈希索引查询到记录指针后都要灰度元祖查取数据。
2. 不能使用hash索引排序
3. Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的
4. Hash索引只支持等值比较，例如使用 = ，in（）和<=>。对于WHERE price > 100并不能加速查询。
5. 访问Hash索引的速度非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
6. 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。当从表中删除一行的时候，存储引擎要遍历对应哈希值的链表中的每一行，找到并删除对应的行的引用，冲突越多，代价越大。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”，由Mysql自动管理，不需要DBA人为干预。默认情况下为开启。我们可以通过参数innodb_adaptive_hash_index来禁用此特性。

当InnoDB注意到某些索引值被使用的非常频繁的时候，他会在内存中基于缓冲池中的B+Tree索引上在创建一个哈希索引，这样的话B-Tree索引也会具有hash索引的一些优点，比如快速的哈希查找。

• 但是这个哈希索引同样只能用于等指标较，例如 = ，<=> , in;
• 无法用于排序

InnoDB官方文档限制，启用自适应哈希索引后，读和写性能可以提高2倍，对于辅助索引的连接操作，性能可以提高五倍。

空间（R - Tree）索引

MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY

全文（Full - text ）索引

全文索引是MyISAM的一个特殊索引类型，他查找的是文本中的关键字，主要用于全文检索。

MySQL InnoDB从5.6已经开始支持全文索引了，但是InnoDB内部并不支持中文、日文等，因为这些语言没有分隔符。可以使用插件付诸实现中文、日文等全文索引。