MySQL索引基础

前言

索引是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键。然而索引经常被误解，好的索引能够轻易将查询性能提高几个数量级，糟糕的索引则会导致各种问题。

索引基础

看一本书的时候，一般会先看书的目录，然后找到对应的页码。在MySQL中，存储引擎用类似的方法使用索引，先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

索引的类型

B-TREE索引
如果没有特别指明类型，一般谈及的索引都是指B-TREE索引，它使用B-TREE数据结构存储数据。存储引擎以不同的方式使用B-TREE索引，性能也各有不同，各有优劣。

B-TREE通常所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。B-TREE索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根结点开始进行搜索。

通过比较节点页的值和要查找的值可以找到合适的指针进入下层字节点，这些指针实际上定义了字节点页中值的上限和下限。

B-TREE索引有一些限制：

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

哈希索引
hash index基于哈希表实现，MySQL中只有Memory引擎显示支持哈希索引。对于表中的每一行数据，存储引擎都会对所有的索引列计算一个hash code（可自定义算法实现hash code），哈希索引把所有的hash code存储在索引中，同时在哈希表中保存指向每个数据行的指针。

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

• 哈希索引不存字段值，所有不能使用索引中的值来避免读取行。
• 哈希索引数据不按照索引值顺序存储（按照哈希表中的hash code值排序），所有无法用于排序。
• 哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算hash code的。
• 哈希索引只支持等值比较查询。
• 当出现哈希冲突的时候，存储引擎必须遍历链表中的所有的行指针，逐行匹配。
• 如果哈希冲突很多的话，索引的维护操作的代价也会很高。

空间索引（R-TREE）
MySQL的MyISAM表支持空间索引，可以用作地理数据存储。和B-TREE索引不同，空间索引会从所有维度来索引数据。可以有效的使用任意维度来组合查询。必须使用MySQL的GIS相关函数来维护数据。但是MySQL的GIS支持并不完善，所有很多时候并不会使用这个特性。

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

其他索引
还有很多第三方的存储引擎使用不同类型的数据结构来存储索引。比如TokuDB使用分形树索引（fractal tree index），既有B-TREE的很多优点，也避免了B-TREE的一些缺点。

索引的优点

索引可以让服务器快速定位到表的指定位置，但是这不是索引的唯一作用，索引也有一些其他的附加作用。总结下来索引有如下三个优点：

1. 索引大大减少了服务器需要扫描的数据量。
2. 索引可以帮助服务器避免排序和临时表。
3. 索引可以将随机I/O变为顺序I/O。

索引的“三星系统”

“三星系统”是评价一个索引师傅合适某个查询，定义如下：如果索引将相关的记录放到一起则获得一星；如果索引中的数据顺序和查找的排列顺序一致则获得二星；如果索引中的列包含了查询中需要的全部列则获得三星。