MySQL索引入门

索引于MySQL数据库，就像是目录于书籍，读者可以通过目录可以快速定位到自己想要的内容。同时，索引基本是对MySQL进行查询性能优化最有效的手段。

因为MySQL的索引是在引擎层而不是服务器层实现的，所以不同存储引擎的索引的种类和工作方式是不同的，即便看起来相同，底层实现逻辑也可能大相径庭。

MySQL支持的索引种类

B-Tree索引（其实使用B+Tree实现）
- 一般来说，如果没有特别说明，MySQL的索引都会是B-Tree索引。
- 虽然叫做B-Tree索引，但其实很多存储引擎使用的是B+Tree（比如InooDB引擎），通过让每一个叶子节点包含住指向下一个叶子节点的指针，来方便叶子节点的范围遍历。
- B+Tree是一种平衡多叉树（数据结构有序且平衡，索引的性能消耗也来自其为保持有序和平衡而做的数据调整）
- B-Tree索引之所以能加快速访问数据的速度，是因为它不需要进行全表的扫描来获取数据，而是通过从索引的根节点开始进行搜索（复杂度马上从N级别降低到log m(N)级别），往下查找，找到叶子节点（叶子节点指针就是指向数据）或找不到合适的叶子节点（数据找不到）为止。
- 在B-Tree索引的"按值查找"场景下，索引对这些查询有效：全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另一列、只访问索引的查询；
- 在B-Tree索引的"按顺序查找"场景下，索引对ORDER BY操作有效
- B-Tree索引的限制（因B-Tree本身的数据结构导致，限制都是和索引的顺序有关）：
  - 如不是按照索引最左列开始查找，则无法使用索引；
  - 不能跳过索引中的列；
  - 如果查询中有某个列的范围查询，则其右边所有列都无法使用所以优化查找；
哈希索引
- 哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才能使用哈希索引。
- 在MySQL中，只有Memory引擎显式支持哈希索引。
- 存储引擎会对每个数据行计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
- 如果多个列的哈希值相同，哈希会以链表方式将多个记录指针存放到同一个哈希目录中。
- 哈希索引的限制（这些限制使哈希索引只能使用在某些特定的场合）
  - 哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用索引中的值来避免读取行。不过因为内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
  - 哈希索引数据不是按照索引值顺序存储的，所以也就无法用于排序。
  - 哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
  - 哈希索引只支持等值比较查询。
  - 如果有很多哈希冲突，哈希索引的速度会被拉慢。
- InnoDB引擎有个自带的"自适应哈希索引"功能可以自动在B-Tree索引上再创建一个哈希索引，使B-Tree具有一些哈希索引的优点。
空间数据索引（R-Tree）
- 这个索引不需像B-Tree那样必须前缀查询，而可以从任何维度来索引数据。
- 这个索引必须使用MySQL的GIS相关函数MBRCONTAINS()等来维护数据，而MySQL的GIS支持又做的不完善，所以大部分人都不会去用这个特性。
全文索引
- 全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。
- 全文索引做的事情更类似搜索引擎，而不是简单的WHERE条件匹配。
- 在相同的列上同时创建全文索引和基于值得B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。

索引的优点

大大减少了服务器需要扫描的数据量
可以帮助服务器避免排序和临时表
可以将随机I/O变为顺序I/O

索引的优点在中到大型表中才能得到体现，对于小型表直接全表查询更高效，对于超大型表用分库分表等策略更高效。

提高索引性能

提高索引的选择性，可以显著提升索引的性能。索引的选择性，指的是不重复的索引值（基数，cardinality）和数据表的记录总数（#T）的比值，范围从1 / #T到1之间。索引的选择性越高，查询效率越高，因为选择性搞的索引可以让MySQL查找时过滤掉更过多的行。值得一提，唯一性索引的选择性是1，这是最好的索引选择性，性能也是最好的。

SQL分析中的索引字段解读

使用explain可以分析SQL语句，分析的语句中会有一个type值，使用索引后这个type值会有很多不同，下面对不同type值的含义做一个归类（来源），按索引由好到差逐一列出：

system：表里只有一行数据时显示，是const的特殊情况。
const：表里只有一行匹配，当使用主键索引或唯一索引时显示这个类型。
eq_ref：当使用主键索引或唯一索引时，索引的所有组成成分都被用上。
ref：触发了联合索引的最左匹配原则，在这个索引上搜索返回的结果多于一行，一般只匹配到少数行。
all：出现了全表扫描，索引已报废