MySQL之高性能分析二

话说上一篇 MySQL高性能分析一 写于半年以前了，现在继续聊聊下一部分-索引类型的相关知识。

在写作本文之前有必要说明一下索引的优缺点，使用索引本身是为了解决问题，但是如果使用者滥用索引或者使用不当，索引也会让你付出惨痛的代价，因此我们有必要合理正确的使用索引，来解决遇到的问题，而不是带来新的问题。简单的说使用索引可以帮助我们：

• 索引大大减少了服务器需要扫描的数据量；

• 索引可以帮助服务器避免排序和临时表；

• 索引可以将随机I/O变为顺序I/O；

本文主要介绍关于索引的类型，索引可分为：B-Tree、哈希索引（hash index）、空间数据索引（R-Tree）、全文索引、其他索引类型。

B-Tree

如果在没有指定索引类型的情况下，多数都是指B-Tree索引（实际情况下是使用的B+Tree），不同的存储引擎之间B-Tree性能也是各有不同，如MyISAM使用的前缀压缩技术使得索引变小，但是InnoDB则按照原数据格式进行存储，再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。如下图所示：

由上图可知，B-Tree对索引是按照顺序组织数据存储的，所以很适合查找范围数据，例如一个基于文本域的索引树上，按照字母顺序传递连续的值进行查找非常适合，所以像查找以某个字母开头的方式效率非常高。通常这种B-Tree索引有如下六种查询方式（这里主要介绍联合索引的形式）：

• 全值匹配：和索引中的所有列进行匹配，联合索引或者单列索引；

• 匹配最左前缀：联合索引中只匹配第一列；

• 匹配列前缀：联合索引或者单列索引中匹配第一列的首位；

• 匹配范围值：查找值是一个范围区间，如小于一个值或者大于一个值；

• 精确匹配某一列并范围匹配另一列：即如在联合索引中第一列精确匹配，第二列范围匹配；

• 只访问索引的查询：查询只需要访问索引，不需要访问数据，即覆盖索引；

当然针对以上的几种查询方式，在B-Tree联合索引中也有一些限制，如果不是按照最左列开始查找，则无法使用索引，另外也不能跳过索引中的列进行查找，即在联合索引中，可能跳过中间列进行查找，这样也只能使用跳过列之前的索引列查找，如果查询某个列是范围查找，则其右边的列都不能使用索引，这些限制并非是B-Tree本身的限制，而是MySQL优化器和存储引擎使用的方式导致，看到这里也许大家应该清楚了创建联合索引的顺序至关重要，因此我们也需要优化我们的SQL来满足不同的查询需求。

哈希索引（hash index）

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

假设某一数据表中有数据

+----------|----------+
| username | password |
+----------+----------+
| ZhangSan |  123456  |
+----------+----------+
| LiSi     |  234567  |
+----------+----------+
| WangWu   |  345678  |
+----------+----------+
| ZhaoLiu  |  456789  |
+----------+----------+

如果使用哈希索引函数func()，他们返回值假设如下

func('ZhangSan') = 1345
func('LiSi') = 9876
func('WangWu') = 9999
func('ZhaoLiu') = 1234

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

| 槽（Slot）|   值（Value）   |
|   1234    | 指向第4行的指针 |
|   1345    | 指向第1行的指针 |
|   9876    | 指向第2行的指针 |
|   9999    | 指向第3行的指针 |

通过上面可以看出槽是按照顺序排列，但是值并非是按照顺序进行，如果MySQL查询WangWu则先进行计算WangWu的哈希值，并使用该值寻找对应的记录指针，因为func(‘WangWu’) = 9999 ,所以MySQL在索引中查找9999就可以找到对应的指针是第3行，最后比较查找第三行的值是否是WangWu，以确保是要找的行。

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

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行（即查找数据行），不过访问内存中的行速度非常快，所以大部分情况造成的影响并不明显；

• 哈希索引数据并不是按照索引值顺序存储的，也就无法用于排序；

• 哈希所以也不支持部分索引列的匹配查找，因为哈希索引始终是件索引列的全部内容计算哈希值。例如数据列（A,B）创建了哈希索引，如果只查询数据列A则无法使用哈希索引；

• 哈希索引只支持等值比较查询，包括=、IN()、<=>（注意<>和<=>是不同的操作），也不支持范围查找。

• 访问哈希索引的数据非常快，除非很多哈希冲突（不同的索引列值却有相同的哈希值），当出现哈希冲突时，存储引擎必须遍历链表中的所有的行指针，逐行进行比较，直到找到符合条件的行为止；

• 如果哈希冲突很多，一些索引维护操作的代价就会很高。

基于上述的限制，哈希索引也只适合某些特定的场合，而一旦适合哈希索引，则它带来的性能提升也将非常显著，如“星型”schema。

空间数据索引（R-Tree）

MyISAM表支持空间索引，可以用来存储地理数据，和B-Tree索引不同，这类索引无需前缀查询。空间索引会从所有维度来索引数据，查询时可以有效的使用任意维度来组合查询，但必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据，由于目前GIS还不算完善，所以大部分人都不会使用这个特性。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的keyword，而不是直接比较索引中的值。全文搜索和其他几种类型索引的匹配方式也完全不一样，它有很多需要注意的细节，如停用词，词干和复数、布尔搜索等，全文索引更类似搜索引擎，而不是简单的where条件匹配查找。因此在相同的列上同时创建全文索引和基于值的B-Tree索引是不会有冲突的，全文索引适用于MATCH AGAINST 操作，而不是WHERE条件操作。

其他索引类型

还有很多第三方的存储引擎使用了不同类型的数据结构来存储数据，如TokuDB使用分形树索引（fractal tree index），这是一类较新开发的数据结构，算是B-Tree的改进版。

通过上述介绍的索引类型，我们应该很清楚的了解了MySQL内部的索引方式，如何构建高性能的索引策略我们下一站继续！~

(The End)