A hash index is built on a hash table and is useful only for exact lookups that use every column in the index.
It stores the hash codes in the index and stores a pointer to each row in a hash table.
hash索引只能用于精确查询。
In MySQL, only the Memory storage engine supports explicit hash indexes. They are the default index type for Memory tables,though Memory tables can have B-Tree indexes too. The Memory engine supports nonunique hash indexes, which is unusual in the database world. If multiple values have the same hash code, the index will store their row pointers in the same hash table entry, using a linked list.
Because the indexes themselves store only short hash values, hash indexes are very compact. The hash value's length doesn't depend on the type of the columns you index——a hash index on a TINYINT will be the same size as a hash index on a large character column.
hash索引的key长度都是一样的,而且都非常短。key长度和索引列类型无关——TINYINT类型和字符类型列的hash索引key长是一样的。索引只包含了键值和列指针,所以MySQL还是需要去访问实际的列数据——B-Tree索引可以在叶子节点存储实际的列数据来避免这样的事情。不过幸好只有Memory 表支持Hash索引,所以速度还是很快。
不能用Hash索引来做排序。
不能用Hash索引来做模式匹配查找(通配符),因为需要有完整的列值来计算出Hash key值。
Hash索引只能做确定值的查找,如"="、"IN()"、"<=>"(<=>的作用是确定两个NULL值是否真的不同)。
在没有键值冲突的情况下,用Hash索引查找是非常快的——有键值冲突时,指针会被组织成单链表,遍历链表很耗时。
欢迎光临 Chinaunix (http://bbs.chinaunix.net/) | Powered by Discuz! X3.2 |