【学习笔记】mysql创建高性能索引（一）-索引基础

本文属于读书笔记，大部分内容摘抄于《高性能MYSQL》，摘抄内容版权属于原作者。

 

索引有很多种类型，可以为不同的场景提供更好的性能。在MYSQL中，索引实在存储引擎层而不是服务器层实现的。

不同的索引种类也有不同的优缺点：

B-Tree索引

当我们谈论索引的时候，如果没有特别指明类型，那多半说的是B-TREE索引，它使用B-Tree数据结构来存储数据(实际上很多存储引擎用的是B+Tree，即每个叶子结点都包涵之下想一个椰子节点的指针，从而方便叶子节点的范围遍历)。大多数Mysqlyinq dou zhichi zhezhong suoyin .

 

我们说B-Tree是因为mysql在create table和其他语句中也使用该关键词。但是实际上底层的存储引擎也有可能使用不同的存储结构，例如NDB用的是T-Tree，而InnoDB使用的则是B+Tree。

 

B-Tree通常意味着所有的值都是按照顺序存储的，并且每一个叶子页到根的距离相同。具体的结构可以自己研究B-Tree的数据结构。

 

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要权标扫描偶来获取相应的数据，取而代之的是从索引的根节点开始进行搜索。B-Tree对索引列是顺序存储的，所以很适合查找范围数据。

 

B-Tree索引适用于全键值、键值范围或键前缀查找（只适用于最左前缀）。以下是可以使用B-Tree索引的查询类型：

1. 全值匹配（显而易见）
2. 匹配最左前缀（索引的第一列）
3. 匹配列前缀（某一列的开头部分）
4. 匹配范围值（也只适用第一列）
5. 精确匹配某一列并范围匹配另一列
6. 只访问索引的查询（查询只需要访问索引，而无需访问数据行）

 

因为索引树中的节点是有序的，所以除了按值查找以外，索引页可以用于排序。

 

B-Tree索引的限制：

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

 

哈希索引

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

 

下面是我从书中截取出来的一个列子：

 

 

 

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

然而哈希索引也有它的限制：

• 哈希索引只包含哈希值和行指针，而不存储字段，所以不能使用索引中的值来避免读取行。不过访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
• 哈希索引数据并不是按照索引值的顺序存储的，所以无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
• 哈希索引只支持等值比较，包括=，IN(),<=>。也不支持任何范围查询，例如> ,<。
• 访问哈希索引的数据非常快，除非有很多哈希冲突（不容索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须便利列表中的所有行指针，朱行进行比较，直到找到所有符合条件的行。
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。

 

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

 

空间数据索引

myISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无需前缀查询。空间索引会从所有唯独来索引数据。查询时，可以有效地使用任意唯独来组合查询。必须使用mysql的GIS相关函数如MBRCOUNTAINS()等来维护数据。MySQL的GIS支持并不完善，所以大部分人都不会使用这个特性。开元关系数据库系统中对GIS的解决方案做的比较好的是PostgreSQL的PostGIS。

 

全文索引

全文索引时一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他积累索引的匹配方式完全不一样。它有许多需要注意的鞋机，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。

 

在相同的列上同事创建全文索引和基于值的B-Tree索引不会有冲突，全文索引使用与MATCH AGAINST操作，而不是普通的WHERE条件操作。

 

其他索引类别

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