MySQL索引的类型

前言

MySQL索引是面试中最常见的问题，笔者前几天接到一个HR小姐姐的面试电话，小姐姐说公司实行996，问我能不能接受?，我没996过，我哪里知道996是什么感觉呀啊，我就敷衍的说了一句应该可以吧，然后我回到家仔细想了想，还是委婉的拒绝了面试的邀请。

最有趣的是第一次接受HR小姐姐的技术面试，问了我一堆MySQL索引的问题，这是要闹哪样？被一个HR面试了一堆技术问题，我表示不服(开个玩笑)，侧面也反映了公司估计确实很忙，HR需要通过技术问题过滤掉一部分简历，这样就可以减少开发的时间成本。

然鹅，上周突然github上的一个996-icu的repository突然就火了，截止到目前已经11K的star了。就是抗议现在很多的公司实行的996政策，很多公司被口诛笔伐，我也看了许多关于996的讨论，我总结的关键点是：开发觉得强制996让他们不爽，把996当成了理所应当让他们不爽。他们觉得如果公司忙可以接受加班，而且大部分的开发如果事情没做完，都会自觉加班。我个人表示赞同这些观点。

额，说了那么多废话，现在就开始介绍些MySQL索引的类型，其中部分内容参考了《MySQL高性能》。

MySQL索引的类型

索引的类型有多种，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层工作的。所以没有统一的索引标准，不同的索引工作方式是不同的，也不是所有的引擎都支持所有的索引类型。以下五种是很常用的索引类型，我们就来分析他们的优点和缺点。

索引优化应该是对查询性能优化最有效的手段了。

B-Tree索引

当人们讨论索引的时候，如果没有特殊说明指的就是B-Tree索引，大多数MySQL引擎都是支持这种索引的。不同的MySQL引擎实现B-Tree索引的方法略有不同，各有优势。例如，MyISAM使用前缀压缩技术使得索引的空间更小，但InnoDB则是按照原数据进行存储。再比如MyISAM索引是通过数据的物理地址引用被索引的行，而InnoDB则是通过主键字段引用被索引的行。

B-Tree索引能大大加快查询的效率。因为查询过程不需要要全表扫描所有的行，只需要从根节点向下搜索，比改节点小的就向左子节点查询，否则就向右子节点查询，最终就能快速的找到要查询的值。

假设有如下数据表：

  CREATE TABLE People(
    last_name varchar(20) not null,
    first_name varchar(20) not null,
    dob date null,
    gender enum('m', 'f') not null,
    key(last_name, first_name, dob)
  );
复制代码

对于表中的每一行数据，索引包含了last_name, first_name, dob列的值，如下是该索引的存储结构图。

索引树中部分条目示例

需要注意的是对于多列索引，是根据CREATE TABLE时定义索引的顺序来决定的，例如我们上面创建的索引，在节点上是按照先last_name然后first_name最后dob字段来存储的，上图右下角当last_name和first_name相等时，是按照生日的日期来排序的。

可以使用B-Tree索引的查询类型

全值查询

全值查询是指匹配所有的索引列。例如前面提到的索引可以用于查找姓名为Cuba Allen、出生于1960-01-01的人。
匹配最左前缀查询

匹配最左前缀查询是指按照从左到右的顺序匹配索引字段，例如前面提到的索引用于查询姓为Allen的人。
匹配列前缀查询

匹配列前缀，就是匹配某个列最开头的部分。例如前面提到的索引可用于查找所有以Ja开头的姓。具体查询语句。
```
SELECT * FROM People WHERE last_name Like 'Ja%';
复制代码
```
匹配范围查询

范围查询顾名思义就是查询一段范围，例如前面提到的索引可用于查询姓Allen到Astaire之间的所有人。
匹配某一列并范围匹配到另一列

前面提到的索引也可以查找所有姓为Allen，并且名字以字母K开头（例如Kim、Karl等）。即第一列lsit_name全匹配，第二列first_name列前缀匹配。
只访问索引的查询

B-Tree支持“只访问索引的查询”，即查询的时候只需要访问索引，无需访问数据行。这种查询方式叫“覆盖索引”，简单来说覆盖索引避免了回表查询数据行的开销，提高了查询效率，感兴趣的同学可以自行搜索“覆盖索引“的相关知识。

关于B-Tree索引使用的一些限制

B-Tree索引如果不是从最左边的列开始查询，则无法使用索引。例如前面例子中索引无法查询名字为Kim的人，以及也无法查询某个特定生日的人，因为这两列不是最左列。类似也无法查找姓氏以某个字母结尾的人。
不能跳过索引的列，前面提到的索引无法查询姓为Allen且生日为1960-01-01的所有人，如果是这样的查询语句，只能用到索引的第一列。
如果查询中有某个列用到了范围查询，则其右边的所有列均不能用到索引优化查询。例如这个查询语句：
```
SELECT * FROM People WHERE last_name='Allen' AND first_name like 'K%' AND dob='1930-07-12';
复制代码
```

上面的查询语句只能用到索引的前两列，因为第二个查询条件是一个范围查询。如果范围查询的条件是有限的，可以通过多个等值条件查询来代替。

小结

通过上面一系列的讲解，我们可以发现，创建索引的顺序简直太重要了，查询条件的顺序也是非常的重要。我们在工作中创建MySQL表的时候一定要注意避开这些坑。我们会发现，由于业务的变化，我们其实需要回头对原来创建表的结构进行优化，这些优化就包括对索引结构的优化，

哈希索引

哈希索引（hash index）是基于哈希表实现的，只有精确等值匹配索引所有列的查询才是有效的。在MySQL中只有Memory引擎支持哈希索引，这也是Memory引擎默认的索引类型，下面看一个例子。假设有如下表：

CREATE TABLE testhash(
  fname varchar(50) not null,
  lname varchar(50) not null,
  KEY USING HASH(fname)
)ENGINE=MEMORY;
复制代码

插入数据：

INSERT INTO testhash(fname, lname) values('Arjen', 'Lentz'),('Baron', 'Schwartz'),('Peter', 'Zitsevo'), ('Vadim', 'Tachenkoe');
复制代码

表数据如下

fname	lname
Arjen	Lentz
Baron	Schwartz
Peter	Zaitsev
Vadim	Tkachenko

假设有哈希函数f()，他返回如下数据（哈希值都是示例数据）：

f('Arjen') = 2098

f('Baron') = 4920

f('Peter') = 8372

f('Vadim') = 5293

哈希索引的存储结构如下：

槽（Slot）	值（value）
2098	指向第1行
4920	指向第2行
5293	指向第4行
8372	指向第3行

现在我们来看如下查询：

SELECT * FROM testhash WHERE fname='Peter';
复制代码

上面的查询语句MySQL先计算'Peter'的哈希值，并使用该值寻找到对应的指针记录，最后一步就是要比较查询的结果是否为'Peter'。哈希索引只需要存储对应的哈希值，所以哈希索引结构比较紧凑，这也使得哈希索引的查询速度非常的快。然而哈希索引也有一些限制：

哈希索引存储的是行指针，而没有存储行数据，所以每次查询无法避免读取行。
哈希索引不是按照索引顺序存储的，所以无法用于排序。
哈希索引不支持部分索引列匹配查询，因为哈希索引存储是全部索引列来计算哈希值的。
哈希索引只能用于等值查询，不支持任何的范围查询。
如果哈希冲突很多的话，一些索引的维护成本就会很高。

因为以上这些限制，哈希索引只适用于某些特定的应用场景，而一旦使用哈希索引，则它带来的性能提升是非常明显的。

空间数据索引(R-Tree)

MyISAM表支持空间索引，可以用于地理数据的存储。和B-Tree索引不同，这类索引无需前缀查询。空间索引会从各个维度来索引数据。查询时，可以有效的使用任意维度的组合来查询。必须使用MySQL的GIS相关的的函数，如MBRCONTAINS()等来维护数据。MySQL的GIS函数还不够完善，所以大部分人都不会使用这个特性。开源数据库中对GIS的解决方案做的比较好的是PostgreSQL的PostGIS。

全文索引

全文索引是一种特殊的索引方式，他查找的是文本中的关键词，一般用户关键词检索的业务场景，全文索引更像是类似于搜索引擎做的事。全文索引在实际工作中用的非常的少，一般数据量稍大一点的关键词检索都用elasticsearch这种专门提供搜索引擎的框架。