MySQL之索引

1、什么是索引
索引是存储引擎用于快速找到记录的一种数据结构。
本质：索引是数据结构
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
2、索引的类型
<1>B-Tree索引

• 当我们讨论索引的时候，如果没有特别指明类型，那么多半说的都是B-Tree索引，它使用B-tree数据结构来存储数据。大多数MySQL引擎都支持这种索引。
• 存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优势。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再例如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。
• B-Tree通常意味着所有值都是按顺序存储的，并且每一个叶子页到根的距离相同，下图展示了B-Tree索引的抽象表示大致反应了InnoDB索引是如何工作的。MyISAM使用的结构有所不同，但基本思想是类似的
  
• B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际定义了子节点页的上限和下限。最终存储引擎要么找到对应的值，要么记录不存在。
• 树的深度和表的大小直接相关
• B-Tree树对索引列是顺序组织存储的，所以很适合查找范围数据

可以使用B-Tree索引的查询类型
B-Tree索引适用于全键值，键值范围或键前缀查找。

• 全值匹配：是指和索引中的所有列进行匹配
• 匹配最左前缀：即只使用索引的第一列
• 匹配列前缀
• 匹配范围值
• 精确匹配某一列并范围匹配另外一列
• 只访问索引的查询

关于B-Tree索引的限制

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

<2>哈希索引（hash index）
哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
在MySQL中，只有Memory引擎显式支持哈希索引，也是Memory的默认索引，（Memory同时也支持B-Tree索引，Memory引擎是支持非唯一哈希索引的）。如果多个列的哈希值相同，索引会议链表的方式存放多个记录指针到同一个哈希条目中。

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

哈希索引限制

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

<3>空间数据索引（R-Tree）
MyISAM表支持空间索引，可以用作地理数据存储。必须使用MySQL的GIS相关函数来维护数据库。MySQL的GIS支持并不完善，所以大部分不会使用。开源数据库系统对GIS的解决方案做的较好的是PostgreSQL的PostGIS。

<4>全文索引
全文索引是一种特殊类型的索引。它查找的是文本中的关键字，而不是直接比较索引的值。全文索引更类似于搜索引擎做的事，而不是简单的where条件匹配。
在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引只适用于MATCH AGAINST操作，而不是普通的where条件操作。

索引优势

1、索引大大减少了服务器需要扫描的数据量。

2、索引可以帮助服务器避免排序和临时表，通过索引对数据库排序，降低数据排序的成本，降低了CPU的消耗。

3、索引可以将随机I/O变成顺序I/O，降低数据库IO成本，大大提高了性能

索引的劣势

1、实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

2、虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert，update，delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

3、索引只是提高效率的一个因素，如果你的MySQL有大量数据量的表，就需要花时间研究建立最优秀的索引，或者优化查询

在选择索引和编写利用这些索引的查询时，有如下三个原则要切记：

1. 单行访问是很慢的
2. 按顺序访问范围数据是很快的，这有两个原因。第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O快很多。第二，如果服务器能够按照需要顺序读取数据，那么就不再需要额外的排序操作，并且Group By查询也无需在做排序和将行按组进行聚合计算了。
3. 索引覆盖查询是很快的。

总的来说，编写查询语句时应该尽可能选择合适的索引以避免单行查找、尽可能使用数据原生顺序从而避免额外的排序操作，并尽可能的使用索引覆盖查询。

哪些情况下需要建索引

1. 主键自动建立唯一索引。
2. 频繁作为查询条件的字段应该创建索引。
   3.查询中与其他表关联的字段，外键关系建立索引。
3. 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引文件。
4. where条件里用不到的字段不创建索引。
5. 单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）。
6. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。（索引干两件事：检索和排序）。
7. 查询中统计或者分组字段。

哪些情况下不需要建索引

1. 表记录太少。
2. 经常增删改的表的字段。
3. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。如果某个数据列包含许多重复的内容，为它建立索引就没太大的实际效果。

【推荐阅读】
《高性能MySQL》

如有总结不当，有问题，错误的地方请大家予以指正，共同学习，共同进步