MySQL杂谈——索引的创建

创建高性能的索引

 索引（键、key）是存储引擎中用于快速找到记录的一种数据结构，这也是索引的基本功能。

 在MySQL中，一个好的索引对于查询时间的提升非常重要，能够将查询性能提高几个数量级，因此学习索引的原理及其优化技巧与注意事项是非常重要的。

 索引基础

在MySQL中，存储引擎先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行

 索引可能包含多个列，此时列的顺序十分重要，因为MySQL只能高效使用索引的最左前缀列。

 索引类型

B-TREE索引

当谈论索引的时候，没特别说明的话，那就是这个类型。（其实很多使用的是B+tree，只是这么叫）

InnoDB的索引结构大致是这样的：

引擎在有了B-tree索引后，不需要再进行全表扫描，而是从索引的根节点出发，根节点的槽中存放了指向子节点的指针，引擎根据指针向下查找。通过比较key值和要查找的值而进入不同的叶子页进而找到需要的数据。

B-tree对索引列是顺序存储的，适合查找范围数据。

索引对多个值进行排序依据的是建表的时候定义索引时列的顺序。当最后一列两个人的姓名都一样时，根据出生日期来排列。

相应的，下面来看一些B-tree索引的限制：

所以说，索引列的顺序非常重要

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希值，这是一个较小的值，不同行的哈希值一般来说不一样。所有的哈希码存储在索引中，同时在哈希表中存储指向数据行的指针。

因为哈希索引自身的限制和特性，它只适用于某些场景。

当使用的引擎不支持哈希索引时，可以手动创建模拟哈希索引；在B-tree上创建一个伪哈希索引（实际上还是使用哈希索引查找），但它使用哈希值而不是键本身进行查找。只需要在查询中的where子句中手动指定哈希函数。

举个例子：

这个例子就是查找crc32函数计算出的哈希值，性能很高。

空间数据索引（R-tree）

全文索引

这是一种特殊的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。

 索引的优点

需要注意的是索引并不总是最好的方案，对于小表来说，全表扫描可能要比索引更高效，对于中大表来说，索引带来的性能提升就比较大了。

高性能的索引策略

独立的列

这句话的意思是索引必须单独使用，而不能是表达式的一部分

前缀索引和索引选择性

通常可以索引开始部分的字符，这样可以大大节约索引空间，但同时也会降低索引的选择性。索引选择性指的是不重复的索引值和数据表的记录总数的比值，数值越高查询效率越高；唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

上面的例子就说明了试探出合适的前缀长度并不太容易，但可以通过计算完整列的选择性大致确定出一个前缀长度。

多列索引

先看第一个问题，在每个列创建独立的索引

这种方式在大多数情况下无法提高性能。

MySQL5.0和更新版本中引入了一种叫索引合并的策略，一定程度上可以使用表上的多个单列索引来定位数据行。

选择合适的索引列顺序

索引列的顺序对于查询性能的提升也是比较重要的，并且索引列的顺序跟平时写SQL也是相关的。