第5章 创建高性能的索引（一）

索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键。索引优化应该是对查询性能优化最有效的手段了。

5.1 索引基础

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

5.1.1 索引的类型

在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的索引标准

B-Tree索引

大多数MySQL引擎都支持这种索引。
存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。
B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点（图示并未画出）开始进行搜索。
B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。
索引对多个值进行排序的依据是CREATE TABLE 语句中定义索引时列的顺序

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

• 全值匹配，指的是和索引中的所有列进行匹配
• 匹配最左前缀
• 匹配列前缀，也可以只匹配某一列的值的开头部分
• 匹配范围值
• 精确匹配某一列并范围匹配另外一列
• 只访问索引的查询

下面是一些关于B-Tree索引的限制：

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

哈希索引

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

哈希索引的限制：

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

InnoDB引擎有一个特殊的功能叫做 “自适应哈希索引” 。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引得一些优点，比如快速得哈希查找

创建自定义哈希索引，思路很简单：在B-Tree基础上创建一个伪哈希索引。这和真正得哈希索引不是一回事，因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询得WHERE子句中手动指定使用哈希函数

空间数据索引

MyISAM表支持空间索引，可以用作地理数据存储。开源关系数据库系统中对GIS的解决方案做得比较好的是PostgreSQL的PostGIS。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。在相同的列上同时创建全文索引和基于值得B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST 操作，而不是普通得WHERE条件操作

其他所有类别

例如TokuDB使用分形树索引（fractal tree index）。ScaleDB使用Patricia tries

5.2 索引的优点

索引可以让服务器快速地定位到表地指定位置。
索引有如下三个优点：

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

索引并不总是最好的工具。 总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。例如可以使用分区技术、建立元数据信息表