MySQL 哈希索引、空间数据索引、全文索引

灰太狼 2024-03-29 15:20 33阅读 0赞

### 1.哈希索引 ###

哈希索引基于哈希表实现，仅支持精确匹配索引所有列的查询。对于每行数据，存储引擎都会对所有的索引列计算出一个哈希码。哈希索引将所有的哈希码存储在索引中，同时保存指向每个数据行的指针。

#### 1.1 存储结构 ####

常见的存储引擎中，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')=1212
    
    f('Julia')=5656
    
    f('Viven')=2323
    
    哈希索引的数据结构如下：
    +-----------+-----------------------+
    | 槽(Slot)  | 值(Value)              |
    +-----------+-----------------------+
    |      1212 | 指向第1行的指针          |
    |      2323 | 指向第3行的指针          |
    |      5656 | 指向第2行的指针          |
    +-----------+-----------------------+

InnoDB 存储引擎也能支持哈希索引，但它所支持的哈希索引是自适应的。InnoDB 存储引擎会根据表的使用情况，在内存中基于 B-Tree 索引之上再创建一个哈希索引，这种行为是自动的、内部的行为，不能人为去干预是否在一张表中生成哈希索引。

#### 1.2 适合哈希索引的查询类型 ####

**精确匹配所有列**

和索引中的所有列进行精确匹配，如查找名字为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: ref
    possible_keys: idx1_customer
              key: idx1_customer
          key_len: 93
              ref: const
             rows: 2
         filtered: 100.00
            Extra: NULL
    1 row in set, 1 warning (0.00 sec)

#### 1.3 哈希索引的限制 ####

*  哈希索引只支持等值查询，包括=、IN、<=>；
 *  哈希索引不存储字段值，只包含哈希值和行指针，不能使用索引中的值来避免读取行；
 *  哈希索引不是按照索引值顺序存储的，不能用于排序；
 *  哈希索引不支持部分索引列匹配查找，如在字段(last\_name,first\_name)创建哈希索引，此时需要查找last\_name='Allen’的数据行，这种查询无法使用该哈希索引；
 *  哈希索引不支持范围查询，如查找所有姓氏在Allen和Bush之间的客户，这种查询无法使用哈希索引；
 *  如果出现很多哈希冲突（不同的索引列值有相同的哈希值），索引的维护成本是很高的，应尽量避免在选择性很低的字段上创建哈希索引。

### 2.空间数据索引 R-Tree ###

常见的存储引擎中，MyISAM 存储引擎支持空间索引，主要用作地理数据存储。空间索引会从所有维度来索引数据，查询时，可以使用任意维度来组合查询。这点和 B-Tree 索引不同，空间索引不需要前缀查询。MySQL 的 GIS 支持其实并不完善，一般情况并不建议在 MySQL 中使用空间索引。

### 3.全文索引 ###

全文索引查找的是文本中的关键词，并不是直接比较索引中的值，它是一种特殊类型的索引。全文索引和其他索引的匹配方式完全不一样，更类似于搜索引擎，并不是简单的 where 条件匹配。

在相同的列上可以同时创建全文索引和 B-Tree 索引，全文索引适用于 match against 操作，不是简单的where 条件操作。

### 4.小结 ###

本小节介绍了哈希索引、空间数据索引、全文索引这三种索引类型。重点介绍了哈希索引的存储结构、适合哈希索引的查询类型和相关限制。哈希索引仅支持精确匹配所有列的查询，在这种查询中，哈希索引是非常高效的，因为哈希索引存储的是哈希值，存储结构非常紧凑。