本小节重点介绍哈希索引、空间数据索引、全文索引。
哈希索引基于哈希表实现,仅支持精确匹配索引所有列的查询。对于每行数据,存储引擎都会对所有的索引列计算出一个哈希码。哈希索引将所有的哈希码存储在索引中,同时保存指向每个数据行的指针。
常见的存储引擎中,MEMORY 存储引擎显式支持哈希索引。如果多个列的哈希值相同,哈希索引会以链表的方式存放多个记录指针到同一个哈希条目中。
以 customer 表为例,我们来看看索引是如何组织数据的存储的:
mysql>?create?table?customer(
?id?int,
?????????last_name?varchar(30),
?first_name?varchar(30),
?birth_date?date,
?key?idx1_customer(first_name)?using?hash
?????)?ENGINE=MEMORY;
?????
mysql>?select?*?from?customer;
+------+-----------+------------+------------+|?id???|?last_name?|?first_name?|?birth_date?|
+------+-----------+------------+------------+
|????1?|?Allen?????|?Cuba???????|?1960-01-01?||????2?|?Barrymore?|?Julia??????|?2000-05-06?|
|????3?|?Basinger??|?Viven??????|?1979-01-24?|+------+-----------+------------+------------+3?rows?in?set?(0.00?sec)
假设哈希索引使用哈希函数f(),返回的值如下:
f('Cuba')=1212f('Julia')=5656f('Viven')=2323哈希索引的数据结构如下:
+-----------+-----------------------+|?槽(Slot)??|?值(Value)??????????????|
+-----------+-----------------------+
|??????1212?|?指向第1行的指针??????????||??????2323?|?指向第3行的指针??????????|
|??????5656?|?指向第2行的指针??????????|+-----------+-----------------------+
InnoDB 存储引擎也能支持哈希索引,但它所支持的哈希索引是自适应的。InnoDB 存储引擎会根据表的使用情况,在内存中基于 B-Tree 索引之上再创建一个哈希索引,这种行为是自动的、内部的行为,不能人为去干预是否在一张表中生成哈希索引。
精确匹配所有列
和索引中的所有列进行精确匹配,如查找名字为Julia的客户。
数据库先会计算first_name='Julia’的哈希值5656,然后在索引中查找5656,找到对应的指针为:指向第2行的指针,最后根据指针从原表拿到具体值,并进行比较是否为Julia
mysql>?explain?select?*?from?customer?where?first_name='Julia'\G***************************?1.?row?***************************
???????????id:?1
??select_type:?SIMPLE
????????table:?customer
???partitions:?NULL
?????????type:?refpossible_keys:?idx1_customer
??????????key:?idx1_customer
??????key_len:?93
??????????ref:?const
?????????rows:?2
?????filtered:?100.00
????????Extra:?NULL1?row?in?set,?1?warning?(0.00?sec)
常见的存储引擎中,MyISAM 存储引擎支持空间索引,主要用作地理数据存储。空间索引会从所有维度来索引数据,查询时,可以使用任意维度来组合查询。这点和 B-Tree 索引不同,空间索引不需要前缀查询。MySQL 的 GIS 支持其实并不完善,一般情况并不建议在 MySQL 中使用空间索引。
全文索引查找的是文本中的关键词,并不是直接比较索引中的值,它是一种特殊类型的索引。全文索引和其他索引的匹配方式完全不一样,更类似于搜索引擎,并不是简单的 where 条件匹配。
在相同的列上可以同时创建全文索引和 B-Tree 索引,全文索引适用于 match against 操作,不是简单的where 条件操作。
本小节介绍了哈希索引、空间数据索引、全文索引这三种索引类型。重点介绍了哈希索引的存储结构、适合哈希索引的查询类型和相关限制。哈希索引仅支持精确匹配所有列的查询,在这种查询中,哈希索引是非常高效的,因为哈希索引存储的是哈希值,存储结构非常紧凑。