MySQL 索引总结

索引

索引：存储引擎用于快速找到记录的一种数据结构

索引优化是对查询性能优化的最有效的手段

类型

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

B - Tree 索引

大多数 MySQL 引擎都支持这种索引 （Archive 引擎例外）

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

B - Tree 索引的性能比较：

• MyISAM 使用前缀压缩技术使得索引更小，索引通过数据的物理位置引用被索引的行
• InnoDB 按照原数据格式进行存储，根据主键引用被索引的行

---

B - Tree 索引适用于全键值、键值范围或者键前缀查找（左端原则）
B - Tree 索引 还可用于 ORDER BY 操作

• 全值匹配

  全值匹配指的是和索引中的所有列进行匹配

• 匹配最左前缀

  使用单独列

• 匹配列前缀

  匹配某一列的值的开头部分

• 匹配范围值

• 精确匹配某一列并范围匹配另外一列

限制：

1. 左端原则，若不是按照索引的最左端开始查找，则无法使用索引。
2. 不能跳过索引中的列。索引的使用必须是连续的，才能正确使用到索引
3. 若查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

哈希索引

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

对于每一行数据，存储引擎都会对所有的索引列计算出一个哈希码（hash code）。

哈希索引将索引的不同的哈希吗存储在索引中，同时，在哈希表中保存每个数据行的指针。

MySQL 中，只有Memory 引擎显示支持哈希索引，且支持非唯一哈希索引。

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

限制：

1. 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
2. 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
3. 哈希索引也不支持部分索引列匹配查找，其值是关联使用索引的全部内容。
4. 哈希索引只支持等值比较查询。
5. 访问哈希索引的数据非常快，除非有很多哈希冲突。出现哈希冲突，会遍历链表中所有的行指针，逐行比较，直到找到所有符合条件的行。
6. 哈希冲突的增多，使得索引维护操作的代价变高。

基于上述限制，哈希索引只适用于某些特定的场合。

• 自适应哈希索引 当 InnoDB 注意到某些索引值被使用的非常频繁时，它会在内存中基于 B - Tree 索引之上再创建一个哈希索引。

• 空间数据索引

• 全文索引

  查找文本中的关键词，而不是直接比较索引中的值

• 其它索引