高性能MySQL（第三版）第五章：创建高性能的索引

• 索引是存储引擎用于快速找到记录的一种数据结构。
• 对良好的性能非常关键。尤其是表中的数据较大时，索引对性能的影响愈发重要。
• 对查询性能优化最有效的手段。轻易将查询性能提高几个数量级。

5.1索引基础

• 索引，又叫key（键）
• 存储引擎使用索引，先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。
• 可以包含一个或多个列的值。如果包含多个列，那么列的顺序也很重要，因为MySQL只能高效地使用索引的最左前缀列。

最左前缀：就是KEY(id, name, sex)中，id在id、name、sex里面是写在左边的，id就叫最左前缀

5.1.1 索引的类型

索引是在存储引擎层而不是服务器层实现的，所以没有统一的索引标准：不同存储引擎的索引的工作方式不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

5.1.1.1 B-Tree索引

实际上很多存储引擎使用的是B+Tree，即每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。

• innoDB使用B+Tree
• B-Tree意味着所有的数据都是按顺序存储，适合查找某一范围内的数据，对数据排序。
• 可加快访问数据的速度，存储引擎不再需要扫描全表，而是从根节点开始进去搜索。
• 既然索引按顺序存储，也就意味着它对数据排序了，索引对数据排序的依据是按照定义索引的顺序来排序，例如KEY(id, name, sex)，先对id排序，再是name，最后是sex。

B-Tree索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找。

• 全值匹配。全值匹配指的是和索引中的所有列进行匹配，例如，前述示例中的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人。
• 匹配最左前缀。示例索引可用于查找所有姓为Allen的人，即只使用索引的第一列。
• 匹配列前缀。也可以只匹配某一列的值的开头部分，例如，示例索引可用于查找所有以J开头的姓的人，这里也只使用了索引的第一列。
• 匹配范围值。例如，示例索引可用于查找姓在Allen和Barrymore之间的人，这里也只使用了索引的第一列。
• 精确匹配某一列并范围匹配另外一列。示例索引也可用于查找所有姓为Allen，并且名字是字母K开头的人，即第一列last_name全匹配，第二列first_name范围匹配。
• 只访问索引的查询。B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。

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

限制

• 如果不是按照索引的最左列开始查找，则无法使用索引。例如，示例索引中无法用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似的，也无法查找姓氏以某个字母结尾的人。
• 不能跳过索引中的列。例如，示例索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定first_name，则mysql只能使用索引的第一列。
• 如果索引中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如，示例索引中有如下查询语句WHERE last_name = ‘Smith’ AND first_name LIKE ‘J%’ AND dob = ‘1976-12-23’，此查询只能使用索引的前两列，因为这里LIKE是一个范围条件。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

上述限制都和索引列的顺序有关，在优化性能时，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。也有些限制并不是B-Tree本身导致的，而是mysql优化器和存储引擎使用索引的方式导致的。

想要了解B-Tree和B+Tree可以参考博客：https://blog.csdn.net/u010727189/article/details/79399384

5.1.1.2 哈希索引

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

在mysql中，只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-Tree索引。Memory引擎支持非唯一哈希索引，如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，也让哈希索引查找的速度非常快。

限制：

• 只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。但由于在内存中，对性能的影响并不明显。
• 并不是按照索引值顺序存储的，所以无法用于排序。
• 不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值。
• 只支持等值比较查询，包括=、IN()、<=>。也不支持任何范围查询。
• 访问哈希索引的数据非常快，除非有很多哈希冲突(不同索引列值却有相同的hash值)。当出现哈希冲突时，存储引擎必须遍历所有的行指针，逐行比较，直到找到对应的行。
• 如果哈希冲突很多的话，一些索引维护操作的代价也非常高。例如删除一行时，需遍历对应哈希值的每一行。

应用场景：
数据仓库应用中有一种经典的“星型”schema，需要管理很多查找表

InnoDB的自适应哈希索引(adaptive hash index):
当InnoDB注意到某些值被使用的非常频繁时，会在内存中基于B-Tree索引之上在创建一个hash索引，从而拥有hash索引的优点，如快速的hash查找。这是一个完全自动的，内部的行为，用户无法控制或者配置，但可以关闭。

创建自定义哈希索引：

• 如果存储引擎不支持hash索引，可以模仿像InnoDB一样创建hash索引。
• 思路：在B-Tree基础上创建一个伪哈希索引，即将要索引的列删除索引，对其创建一个被索引哈希列，里面存放原索引列每一行数据的哈希值。
• 缺陷：需要维护哈希值，可以手动维护，也可以使用触发器实现。
• 使用时不要使用SHA1()和MD5()作为哈希函数，因为这两个函数计算出来的哈希值非常长，浪费大量空间而且比较时也会更慢。可以使用CRS32()。
• 如果数据表非常大，CRS32()会出现大量的数据冲突，可以自行实现一个简单的64位哈希函数，这个函数要返回整数，而不是字符串。也可以使用MD5()函数返回值的一部分来作为哈希函数。

SELECT CONV(RIGHT(MD5("str"), 16), 16, 10) AS HASH64

• 使用哈希索引进行查询时，必须在WHERE子句包含对应列值，因为可能会有哈希冲突从而选出多个不同的数据。

5.1.1.3 空间数据索引(R-Tree)

• 与B-Tree不同，这类索引无需前缀查询，会从所有维度来索引数据。
• 查询时可以使用任意维度来组合查询。
• 必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。(MySQL的GIS支持不完善，开源关系数据库中较好的解决方案是PostgreSQL的PostGIS)。
• MyISAM表支持空间索引，可以用作地理数据存储。

5.1.1.4 全文索引

• 查找的是文中关键词，而不是直接比较索引中的值。
• 与其他索引完全不一样，需注意如停用词、词干和复数、布尔搜索等细节。
• 更类似于搜索引擎做的事，而不是简单的WHERE条件匹配。
• 在相同列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作

为什么索引结构默认使用B-Tree，而不是hash，二叉树，红黑树？

• hash：虽然可以快速定位，但是没有顺序，IO复杂度高。
• 二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
• 红黑树：树的高度随着数据量增加而增加，IO代价高。

5.2 索引的优点

优点：

• 大大减少服务器需要扫描的数据量<