高性能MySQL学习五(B+树索引、哈希索引、全文索引)_b+树索引全文索引散列索引-优快云博客

本文链接：https://blog.youkuaiyun.com/yyyxxxs/article/details/100014619

本文详细介绍了MySQL中三种主要的索引类型：B+树索引、哈希索引和全文索引。B+树索引是关系型数据库中常用的高效查找手段，它按照键值顺序存储数据，适用于全键值、键值范围或键前缀查找。哈希索引基于哈希表，适用于精确匹配查询，但不支持范围查找和排序。全文索引则用于查找文本中的关键词，类似搜索引擎。文章还提到了如何利用哈希函数创建伪哈希索引来优化长键值的查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、为什么使用索引
索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后读取整个表以查找相关行。表越大，成本越高。但如果表中有相关列的索引，MySQL就可以快速确定要在数据文件中间寻找的位置，而无需查看所有数据。这比按顺序读取每一行要快得多。
大多数MySQL索引（PRIMARY KEY， UNIQUE，INDEX和 FULLTEXT）存储在B树。例外情况是空间数据类型的索引使用R树，并且该 MEMORY表也支持哈希索引。
在这里插入图片描述
2、B+树索引
B+树索引就是传统意义上的索引,这是目前关系型数据库系统中查找最为常用和最为有效的索引。B+树索引的构造类似于二叉树，根据键值(Key Value)快速找到数据。( B+树中的B不是代表二叉 (binary)，而是代表平衡(balance)，因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。)
另一个常常被DBA忽视的问题是:B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读人到内存，再在内存中进行查找，最后得到要查找的数据。
假设有以下数据表：

mysql> create table people(
    -> last_name varchar(50) not null,
    -> first_name varchar(50) not null,
    -> dob date not null,
    -> gender enum('m','f') not null,
    -> key(last_name,first_name,dob)
    -> )engine=innodb;
Query OK, 0 rows affected (0.05 sec)

该索引组织数据的存储方式如下图所示：
在这里插入图片描述
要注意的是，索引对多个值进行排序的依据是CREATE TABLE 语句中定义索引时列的顺序。看一下最后两个条目，两个人的姓和名都-样，则根据他们的出生日期来排列顺序。

我们插入部分数据来帮助我们实验

mysql> select * from people;
+-----------+------------+------------+--------+
| last_name | first_name | dob        | gender |
+-----------+------------+------------+--------+
| Akroyd    | Christian  | 1958-12-07 | f      |
| Akroyd    | Debbie     | 1990-03-18 | f      |
| Akroyd    | Kirsten    | 1978-11-02 | m      |
| Allen     | Cuba       | 1960-01-02 | m      |
| Allen     | Kim        | 1930-07-12 | m      |
| Allen     | Meryl      | 1980-12-12 | f      |
| Barrymore | Julia      | 2000-05-12 | f      |
+-----------+------------+------------+--------+
7 rows in set (0.01 sec)

这里需要在提到一个MySQL中的语句，EXPLAIN语句，该语句提供了有关SELECT语句执行计划的信息，使用也很简单，只要在SELECT语句前加上EXPLAIN即可，显示列信息如下所示。更多可以参见官网EXPLAIN输出格式
在这里插入图片描述
B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。前面所述的索引对如下类型的查询有效。

此外，B+Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问;数据行。

后面我们将单独讨论这种“覆盖索引”的优化。

//全值匹配
mysql> select * from people where last_name='Allen' and first_name='Cuba' and dob='1960-01-02';		
+-----------+------------+------------+--------+
| last_name | first_name | dob        | gender |
+-----------+------------+------------+--------+
| Allen     | Cuba       | 1960-01-02 | m      |
+-----------+------------+------------+--------+
1 row in set (0.00 sec)

mysql> explain select * from people where last_name='Allen' and first_name='Cuba' and dob='1960-01-02';
+----+-------------+--------+------+---------------+-----------+---------+-------------------+------+-------------+
| id | select_type | table  | type | possible_keys | key       | key_len | ref               | rows | Extra       |
+----+-------------+--------+------+---------------+-----------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | people | ref  | last_name     | last_name | 107     | const,const,const |    1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)


//匹配最左前缀
mysql> select * from people where last_name='Allen';
+-----------+------------+------------+--------+
| last_name | first_name | dob        | gender |
+-----------+------------+------------+--------+
| Allen     | Cuba       | 1960-01-02 | m      |
| Allen     | Kim        | 1930-07-12 | m      |
| Allen     | Meryl      | 1980-12-12 | f      |
+-----------+------------+------------+--------+
3 rows in set (0.00 sec)

mysql> explain select * from people where last_name='Allen';
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | people | ref  | last_name     | last_name | 52      | const |    2 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
1 row in set (0.00 sec)


//匹配列前缀
mysql> select * from people where last_name like 'B%';
+-----------+------------+------------+--------+
| last_name | first_name | dob        | gender |
+-----------+------------+------------+--------+
| Barrymore | Julia      | 2000-05-12 | f      |
+-----------+------------+------------+--------+
1 row in set (0.01 sec)

mysql> explain select * from people where last_name like 'B%';
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | people | range | last_name     | last_name | 52      | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)


//匹配范围值
mysql> select * from people where last_name > 'Akroyd' and last_name < 'Barrymore';
+-----------+------------+------------+--------+
| last_name | first_name | dob        | gender |
+-----------+------------+------------+--------+
| Allen     | Cuba       | 1960-01-02 | m      |
| Allen     | Kim        | 1930-07-12 | m      |
| Allen     | Meryl      | 1980-12-12 | f      |
+-----------+------------+------------+--------+
3 rows in set (0.00 sec)

mysql> explain select * from people where last_name > 'Akroyd' and last_name < 'Barrymore';
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | people | range | last_name     | last_name | 52      | NULL |    2 | Using where |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)


//精确匹配某一列并范围匹配另一列
mysql> select * from people where last_name='Allen' and first_name like 'K%';
+-----------+------------+------------+--------+
| last_name | first_name | dob        | gender |
+-----------+------------+------------+--------+
| Allen     | Kim        | 1930-07-12 | m      |
+-----------+------------+------------+--------+
1 row in set (0.00 sec)

mysql> explain select * from people where last_name='Allen' and first_name like 'K%';
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | people | range | last_name     | last_name | 104     | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)

在这里插入图片描述
像下面的例子，从索引的第二列first_name开始查找，在查找时没有使用索引，key列为NULL表示没有使用索引，type列为ALL，表明这是全表扫描数据文件。跳过first_name列也不会使用索引，依旧为全表扫描。

mysql> explain select * from people where first_name='Julia';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | people | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from people where last_name='Allen' and dob='1960-01-02';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | people | ALL  | last_name     | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.08 sec)

这个查询只能使用索引的前两列，因为这里的like是一个范围条件。

mysql> explain select * from people where last_name='Allen' and first_name like 'K%' and dob='1960-01-02';
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | people | range | last_name     | last_name | 107     | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.02 sec)

通过上面的例子，我们可以看到，索引列的顺序是极其重要的，这些限制都和索引列的顺序有关。所以在优化性能时，我们可能会使用列相同但顺序不同的索引来满足不同的查询需求。

3、哈希索引

(1)哈希索引(hash index)
哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code),哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在MySQL中，只有Memory引擎显式支持哈希索引。值得一提的是，Memory引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同,索引会以链表的方式存放多个记录指针到同一个哈希条目中。

mysql> create table testhash(
    -> fname varchar(50) not null,
    -> lname varchar(50) not null,
    -> key using hash(fname)
    -> )engine=memory;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from testhash;		//插入了如下数据
+-------+----------+
| fname | lname    |
+-------+----------+
| Arjen | Lentz    |
| Baron | Schwartz |
| Peter | Zaitsev  |
| Vadim | Tkacheko |
+-------+----------+
4 rows in set (0.00 sec)

mysql> explain select * from testhash where fname='Peter';
+----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys | key   | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+
|  1 | SIMPLE      | testhash | ref  | fname         | fname | 52      | const |    2 | Using where |
+----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+
1 row in set (0.00 sec)

假设索引使用hash函数f( )，如下：

f('Arjen') = 2323
f('Baron') = 7437
f('Peter') = 8784
f('Vadim') = 2458

此时，索引的结构大概如下：
在这里插入图片描述
哈希索引中存储的是：哈希值+数据行指针 (Slots是有序的，但是记录不是有序的。)

当执行 SELECT lname FROM testhash WHERE fname=‘Peter’时，MySQL会计算’Peter’的hash值，然后通过它来查询索引的行指针。因为f(‘Peter’) = 8784，MySQL会在索引中查找8784，得到指向记录3的指针。最后比较第三行的值是不是’Peter’，以确保就是我们要查找的行。

因为索引自身只需存储对应的哈希值，所以，索引结构非常紧凑查找速度也很快。Hash值长度是固定的，其值不取决于列的数据类型。

我们了解了使用哈希索引的查询的过程，这可以帮助我们学习哈希索引的限制：

由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的，不会对性造成太大的影响。
哈希索引数据并不是按照索引值顺序存储的，所以不能使用hash索引排序。
Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。例如，在数据列（A,B）上建立哈希索引，如果查询只有数据列A，则无法使用该索引。
Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。

像>这样的比较并不能使用到索引
mysql> explain select * from testhash where fname > 'Baron';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | testhash | ALL  | fname         | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)


//等值比较才会用到哈希索引
mysql> explain select * from testhash where fname <=> 'Baron';
+----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys | key   | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+
|  1 | SIMPLE      | testhash | ref  | fname         | fname | 52      | const |    2 | Using where |
+----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+
1 row in set (0.02 sec)

在这里插入图片描述

mysql> select 1=1,1=2,null=null;
+-----+-----+-----------+
| 1=1 | 1=2 | null=null |
+-----+-----+-----------+
|   1 |   0 |      NULL |
+-----+-----+-----------+
1 row in set (0.00 sec)

mysql> select 1=1,1=2,null<=>null;		//安全的等于运算<=>
+-----+-----+-------------+
| 1=1 | 1=2 | null<=>null |
+-----+-----+-------------+
|   1 |   0 |           1 |
+-----+-----+-------------+
1 row in set (0.00 sec)

访问Hash索引的速度非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
如果哈希冲突很多的话，一些索引维护操作的代价也会很高。当从表中删除一行时，存储引擎要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

(2)伪哈希索引
Innodb存储引擎支持的哈希索引是自适应的，Innodb存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。

如果存储引擎不支持哈希索引，我们可以模拟像InnoDB一样创建哈希索引，这可以享受一些哈希索引的便利，例如只需要很小的索引就可以为超长的键创建索引。

思路很简单：在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B+Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询的WHERE子句中手动指定使用哈希函数。
看个例子，需要存储大量URL，并根据URL进行搜索，如果使用B+树，那么存储的内容就会很大，因为URL本身很长，正常情况下我们的查询可能像下面这样：

mysql> create table url(id varchar(100) not null,
    -> key(id)
    -> )engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into url values('www.mysql.com');
Query OK, 1 row affected (0.02 sec)

mysql>  select id from url where id='www.mysql.com';
+---------------+
| id            |
+---------------+
| www.mysql.com |
+---------------+
1 row in set (0.00 sec)

mysql> explain select id from url where id='www.mysql.com';
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | url   | ref  | id            | id   | 102     | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.01 sec)

但我们完全可以删除原URL列上的索引，新增一个被索引的url_crc列，使用CRC32做哈希。这样的话，查询速度就会快很多，因为查询时只需要根据哈希值做快速的整数比较就能找到索引条目，然后一一比较返回对应行。

这里我们不使用SHA1和MD5作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串，会浪费大量空间，比较时也会更慢。SHA1和MD5是强加密函数，设计目标是最大限度消除冲突，但这里并不需要这样高的要求。CRC32的冲突在一个可以接受的范围，同时又能够提供更好的性能。

mysql> alter table url add column url_crc int unsigned not null;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into url values('www.cctv.com',CRC32('www.cctv.com'));
Query OK, 1 row affected (0.00 sec)

mysql> drop index id on url;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create index url_index on url(url_crc);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select *  from url where id='www.cctv.com' and url_crc=CRC32('www.cctv.com');
+--------------+------------+
| id           | url_crc    |
+--------------+------------+
| www.cctv.com | 2075738837 |
+--------------+------------+
1 row in set (0.00 sec)

mysql> explain select *  from url where id='www.cctv.com' and url_crc=CRC32('www.cctv.com');
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | url   | ref  | url_index     | url_index | 4       | const |    1 | Using where |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

如果数据表非常大，CRC32会出现大量的哈希冲突，CRC32返回的是32位的整数，当索引有93000条记录时出现冲突的概率是1%。为了避免冲突问题，查询时在where条件中带入哈希值和对应列值，而尽量避免只使用哈希值。

4、全文索引
全文索引是一种特殊类型的索引，它查找都是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。
在相同的列上同时创建全文索引和基于值对B+Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。

这个后面写了几篇文章详细介绍：
MySQL全文索引之自然语言全文索引
 MySQL全文索引之布尔全文索引、查询扩展全文索引