MySQL索引类型

MySQL支持哪几种索引类型呢？从不同的角度讨论不一样，这篇文章是从数据结构的角度去分类，还有物理和逻辑角度只做简单说明。

从物理存储角度

1、聚集索引（clustered index）

2、非聚集索引（non-clustered index）

从逻辑角度

1、主键索引：主键索引是一种特殊的唯一索引，不允许有空值

2、普通索引或者单列索引

3、多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

4、唯一索引或者非唯一索引

5、空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

索引是存储引擎用于快速找到记录的一种数据结构。

要理解 MySQL 中索引是如何工作的，最简单的方法就是去看看一本书的 “索引” 部分：如果想在一本书中找到某个主题，一般会先看书的 “索引“，找到对应的页码。

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为 MySQL 只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个包含一个列的索引是大不相同的。

索引有很多种类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务层实现的。所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的引擎，其底层实现也可能不同。（不过这里我们不展开说，只介绍几种索引类别）

下面我们先来看看MySQL 支持的索引类型，以及他们的优缺点：

1、B-Tree 索引

当人们谈论索引的时候，如果没有特别指明类型，那多半说的就是B-Tree索引，它使用 B-Tree 数据结构来存储数据。大多数MySQL引擎都支持这种索引。Archive 引擎是一个例外：5.1 之前Archive 不支持任何索引，直到5.1 才开始支持单个自增列 （AUTO_INCREMENT） 的索引。

我们使用术语“B-Tree”，是因为MySQL在 CREATE TABLE 和其他语句中也使用该关键字。不过底层的存储引擎也可能使用不同的存储结构，例如，NDB集群存储引擎内部实际上使用了 T-Tree 结构存储这种索引，即使其名字是BTREE；InooDB则使用的是B+Tree。

存储引擎以不同的方式使用B-Tree 索引，性能也各有所不同，各有优劣。如MyISAM使用前缀压缩技术使索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM 索引通过数据的物理位置引用被索引的行，而InooDB则根据主键引用被索引的行。

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。下图展示了索引的表示方式：

B-Tree 索引能过加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可找到合适的指针进入下层子节点，大的往右，小的就往左。叶子节点的指针指向的是被索引的数据，而不是其他的节点页。

B-Tree 索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。前面所述的索引类型对如下类型的查询有效：

    全值匹配：全值匹配指的是和索引中的所有列进行匹配。

    匹配最左前缀：在有多列索引的情况下，只使用索引的第一列。

    匹配列前缀：可以只匹配某一列的值的开头部分。例如某个字符串以某个字母开头。

    匹配范围值：在有多列索引的情况下，只使用索引的第一列匹配一定范围内的值。

    精确匹配某一列并范围匹配另外一列：例如在某个多列索引表中，第一列全匹配，第二列范围匹配。

    只访问索引的查询：即查询只需要访问索引，而无须访问数据行

因为索引树的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY 操作（按顺序查找）。一般来说，如果 B-Tree 可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果 ORDER BY 子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

下面是是一些 B-Tree 的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。
• 不能跳过索引中的列。
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

到这里可以明白，前面提到的索引列的顺序是多么重要（文章开头，下划线句子）：这些限制都和索引列的顺序有关。在性能优化时，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

2、哈希索引

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

在MySQL中，只有Memory 引擎显示支持哈希索引。这也是Memory 引擎表的默认索引类型，Memory 引擎同时也支持B-Tree索引。

下面来看一个例子：

CREATE TABLE testhash(

        fname VARCHAR(50)  NOT NULL,

        lname VARCHAR(50) NOT NULL,

        KEY USING HASH(fname)

) ENGINE = MEMORY;

假设索引使用假象的哈希函数 f( )，它返回下面的值（都是示例数据，非真实数据）：

        f('Arjen')=2323;

        f('Liming')=7434;

        f('Hanfeng')=6745;

        f('Mars')=3245;

则哈希索引的数据结构如下：

槽（Slot）        值（Value）

2323                指向第 1 行的指针

3245                指向第 4 行的指针

6745                指向第 3 行的指针

7434                指向第 2 行的指针

注意每个槽的编号是顺序的，但是数据行不是。现在，来看如下查询：

    mysql> SELECT lname FROM testhash WHERE fname= 'Liming';

MySQL 先计算 ‘Liming’的哈希值，并使用该值寻找对应的记录指针。因为f('Liming')=7434，所以MySQL 在索引中查找 7434 ，可以找到指向第 2 行的指针，最后一步是比较第三行的值是否是 'Liming'，以确保就是要查找的行。

因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的书读非常快。然而，哈希索引也有它的限制：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能上使用索引中的值来避免读取行。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
• 哈希索引只支持等值比较查询，包括=、IN( )、<=>（注意<>和<=>是不同的操作）。也不支持任何范围查询，如 WHERE price > 100 ;
• 访问哈希索引的数据结构非常快，除非有许多哈希冲突
• 如果哈希冲突很多，一些索引维护操作的代价也会很高。

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

Innodb引擎有一个特殊的功能叫做“自适应哈希索引”。当Innodb注意到某些索引值使用非常频繁时，它会在内存中基于B-tree索引之上再建立一个哈希索引。这是一个完全自动、内部的行为，用户无法配置或者设置，不过有必要可以关闭此功能。

3、创建自定义哈希索引

思路：在B-tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，它使用哈希值而不是键本身经行查找。需要在操作中在where语句中手动指定哈希函数。

具体看看实例：

注意：在使用 DELIMITER 后请记得 DELIMITER; 结束。

4、空间数据索引( R-Tree )

MyISAM 表支持空间索引，可以用作地理数据存储。和B-Tree 索引不同，这类索引无须前缀查询。空间索引会从所有唯独来索引数据。查询时，可以有效地使用任意维度来组合查询。

5、全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。

6、其他索引类别

还有许多第三方的存储引擎使用不同类型的数据结构来存储索引。例如TokuDB 使用分形树索引，这是一类较新开发的数据结构，既有 B-Tree 的很多优点，也避免了 B-Tree 一些缺点。