索引简述及创建高性能索引

一. 何为索引

就如同看一本书，想要从一本较厚的书中快速查找某一项内容，那么【目录】就是最好的工具、指引，从目录可以快速定位到内容所在书籍页面，直达书籍页面研究个人感兴趣的内容。
那么，数据库索引就是数据库的【目录】，是存储引擎用于快速找到记录的一种数据结构。设计优良的索引能大幅度提升查询性能，从而提升整体系统的并发性能。

在现在系统中，针对数据库的操作大多会使用到ORM，ORM虽能帮助实现复杂的各类数据库操作，但无法生成合适的索引查询。
在精妙和复杂的索引面前，ORM都只是“浮云”。

二. 索引类型

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

下面就来简单探究下MySQL支持的索引类型。

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。
在设计中，将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

从数据结构方面考虑，哈希索引的实现是表或者链表，其内存结构也十分紧凑，这就使得哈希索引的速度非常快。但是也有其自身的限制：

• 哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用索引中的值来避免读取行。
• 哈希索引数据不是按照索引值顺序存储的，就无法用于排序。
• 哈希索引是使用索引列的全部内容来计算哈希值，并根据哈希值进行查找，故而不支持索引列匹配查找，相应的，也不支持进行范围查找。
• 如果存在哈希冲突，冲突值按照链表进行存储，则查找时需要遍历链表，然后逐行进行匹配查找，如果冲突较多，则严重影响查找性能。

B-Tree索引

当人们谈论索引的时候，如果没有特别指明类型，大概率说的是B-Tree索引，其使用B-Tree数据结构来存储数据。

B-Tree索引基于树的数据结构进行设计，其索引是顺序存储的，查找时使用二分查找进行快速匹配查找。

在MySQL中，B-Tree树的叶子结点存储指向数据的指针，也就是在查找到叶子结点后即是查找到数据。

基于以上特点，可以看到MySQL查询拥有以下特点：

• 全值匹配
  • 查找时和索引中的所有列进行匹配，快速查找数据
• 匹配最左前缀
  • 按照索引列从左向右进行匹配查找
• 匹配列前缀
  • 匹配索引列的前缀数据，如 like ‘J%’ 查找
• 匹配范围值
  • 因为B+Tree的叶子结点是按照顺序进行存储的，故而很方便按照叶子结点进行范围查找
• 精确匹配某一列并范围查找另外一列
• 只访问索引的查询
  • 因为叶子结点直接存储数据，故而在查找索引列数据时，可以在叶子结点中快速进行查找

任何事物都有其两面性，B-Tree树能够实现各种快速查找，但是其本身也有一些限制：

• 跳过索引列的左列，则无法使用索引，进而退化为全表查询。
• 索引的左列范围查询时，其右列数据无法使用到索引。

空间数据索引（R-Tree）

MySQL的MyISAM引擎支持空间索引，可以用作地理数据存储。空间索引会从所有维度来索引数据，查询时，可以有效的使用任意维度来组合查询。

全文索引

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

三. 索引的优点

索引可以让服务器快速地定位到表的指定位置，不同存储引擎使用索引算法不同，但其目标都是基本一致的：快速定位、查找数据。总结下来索引有以下几个优点：

1. 索引大大减少服务器需要扫描的数据量；
2. 索引可以帮助服务器避免排序和临时表；
3. 索引可以将随机I/O变为顺序I/O，结合操作系统特点，更好的提升查找性能。

四. 索引策略

了解了索引的类型以及索引数据结构、算法，那我们如何利用这些知识来建立高性能的索引，从而提升查询的整体性能呢？这就牵扯到索引的创建策略，不同应用场景需要建立的索引不同，但大体上索引的建立可以遵循如下的几点建议：

1. 索引选择性
   不论使用何种索引算法，如果所有的数据都是唯一的，那么不论是哈希索引还是B-Tree索引都能很快定位到数据，那么索引选择性就尤为重要。所谓索引选择性，即不重复的索引值和数据表的记录总数（T）的比值，介于1/T ～ 1 之间，此值越接近于1，则表明数据唯一性越高，查找效率越高。

2. 独立的列
   独立的列 是指索引列不能是表达式的一部分或者是函数的参数，否则MySQL则无法使用此列上的索引值进行数据查找。

3. 前缀索引
   有时候查询的字段数据内容比较长，此类数据并不适合作为索引使用，因为会使得索引变得异常的大，在内存、硬盘等存储占据相当大的空间，不利于查询，此时前缀索引则可以帮助提升查询性能。
   前缀索引，就是选择数据列相当一部分前缀建立索引，同时又不能太长，尽量保证数据的唯一性。其主要优点：使得索引变小、查找性能更高，但是诸如排序、分组等则无法使用到此索引。

4. 多列索引和索引顺序
   因为MySQL的存储引擎使用的索引算法大多为B-Tree结构，而复杂的应用场景，就需要我们选择建立合适的索引来提升整体的查找性能。
   因为B-Tree数据结构的特点，在建立所列复合索引时，合适的索引顺序就显得特别重要，可以充分利用索引来完成数据的查找、排序、分组等各种复杂查找需求。

5. 聚簇索引
   聚簇索引并不是一种索引类型，而是一种数据存储方式，不同的存储引擎在实现上可能会有所不同。“聚簇”表示数据行和相邻的键值紧凑的存储在一起，因为无法将数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
   聚簇索引的节点中存储索引列，所有的叶子节点存储数据。其主要优点：

   • 可以把相关的数据保存在一起，通过ID可以查询所有的数据
   • 数据访问更快
   • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

   同时也有一定的限制：

   • 插入速度严重依赖于插入顺序
   • 更新聚簇索引列的代价很高，因为会涉及到每个被更新的行移动位置

6. 覆盖索引
   通常应用中，大家都会根据查询的WHERE条件来创建合适的索引，然而这只是索引优化的一个方面。而设计优秀的索引应该考虑到整个查询，不单单是WHERE条件部分。“覆盖索引”，即一个索引包含或者覆盖所有需要查询的字段的值。
   在InnoDB中这种情况就比较利于性能的优化、提升，因为InnoDB的二级索引叶子结点中存储索引列的值以及主键ID，则通过二级索引直接查询索引字段，避免通过主键二次回表的查询，进而提升查询性能。可以参考如下数据结构模拟图
   

7. 冗余和重复索引
   在设计索引时尽量避免在一个列中建立多个或者重复的索引，因为索引的建立需要使用内存、硬盘等占用相当的空间，会增加MySQL服务器的查询负担，且数据插入时因为索引的存在性能也会有所下降。
   在多数情况下最好是扩展或者新增加索引，以便能更好的利用索引特性提升查询性能、减小索引的内存使用、CPU计算资源。

五. 总结

通过以上的介绍以及分析，相信大家对于如何创建一个合适的查询索引应该有了一个总的纲领、框架。具体到某些应用或者某具体的SQL语句时，可以根据以上介绍的原则进行分析、建立合适的索引，提升整体的应用性能。
虽然索引能大幅度提升查询的性能，但是并不意味着索引越多越好，因为太多的索引，在数据的插入或者更新时，性能损耗是比较大的。只有充分利用索引的特点、业务场景，建立合适的索引，对于性能提升才是最大的助力。当然也可以使用 EXPLAIN 来分析SQL语句，查找问题点，进而做到最优的创建或者优化索引。
诚然当数据达到一定程度，索引带来的好处也会有所打折，此时就需要其他策略来提升整体系统的性能，此待后续分享。

[延伸阅读]
MySQL数据类型