MySQL之 索引基础总结

本节内容总结自《High Performance MySQL》

索引基础

索引的概念

• 索引是存储引擎用于快速找到记录的一种数据结构。索引类似于一本书的目录部分，MySQL在利用索引查找的时候与其类似，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。MySQL中索引可以包含一个或多个列的值。列的顺序非常重要，因为MySQL只能高效的使用索引的最左前缀列。

索引的类型

1. B-Tree索引
   1. 定义：B-Tree只使用叶子节点存储数据，其余非叶子节点上存储着区间边界值和左右儿子结点的地址便于细化查找值所在的叶子节点。B-Tree通常意味着所有的值都是按规定的顺序存储的，并且每一个叶子页到根的距离相同，很适合范围查找数据。
   2. 查找：B-Tree索引能够加快访问数据的速度，存储引擎从索引的根节点开始进行搜索，根节点的槽中存放了指向子节点的指针，存储引擎通过这些指针向下层查找。具体的，通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么找到对应的值，要么该记录不存在。叶子节点比较特殊，它们的指针指向的是被索引的数据，而不是其他节点页。
   3. B-Tree的数据结构规则：B-Tree索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。如果索引建立语句是(key1, key2, key3)，则在索引树中，首先按照key1增序排序，key1相等则按key2增序排序，依次类推。
   4. 不同存储引擎中的实现：MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。
   5. 可以使用B-Tree索引的查询类型
      1. 全值匹配：全值匹配指的是和索引中的所有列进行匹配。
      2. 匹配最左前缀：即只使用索引的第一列。
      3. 匹配列前缀：也可以只匹配某一列的值的开头部分，例如以"J"开头的姓的人。
      4. 匹配范围值：遵循最左前缀列的前提下，可以匹配值在一定范围的数据。
      5. 精确匹配某一列并范围匹配另一列：但是必须遵循最左前缀列精确匹配，最右列是范围匹配。
      6. 只访问索引的查询：这是基于聚簇索引的，覆盖索引可以不用回表查询原数据行。
   6. 索引用于顺序查找(ORDER BY)：满足上述六条规则的查询，也可以使用ORDER BY。
   7. B-Tree的限制
      1. 如果不是按照索引的最左列开始查找，则无法使用索引。例如，索引无法单独匹配key3，类似地，也无法查找key1以某个字符串结尾的人，例如LIKE '%bjt'。
      2. 不能跳过索引中的列。例如无法使用只基于key1，key3的查找而跳过key2。
      3. 如果查询中有某个列的范围查询，则其右边所有列的无法使用索引优化查找。如果范围查询列值的数量有限，可以通过使用多个连等条件来代替范围查询。
2. 哈希索引
   1. 定义：哈希索引用哈希表实现，只有精确匹配索引的所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个比较小的值，而且不同键值的行计算出来的哈希码不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。MySQL中只有Memory引擎支持哈希索引，而且Memory引擎支持非唯一哈希索引。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。
   2. 哈希索引的限制
      1. 哈希索引只包含哈希值和行指针，而不存储字段值，所以哈希索引不存在覆盖索引。
      2. 哈希索引数据并不是按照索引值顺序存储的，所以也没有办法用于排序。
      3. 哈希索引不支持部分索引列查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
      4. 哈希索引只支持等值比较查询，例如=,IN(),<=>，也不支持任何范围查询。
      5. 访问哈希索引的数据非常快，除非有很多哈希冲突。当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到答案为止。
      6. 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。
   3. InnoDB的自适应哈希索引：InnoDB注意到某些索引值被使用的非常频繁时，他会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。
   4. 创建自定义哈希索引：在B-Tree的基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree索引进行查找，但是它使用哈希值而不是键本身进行索引查找。具体的，删除原来的索引，新增一个索引，该索引存放的是原来索引列的哈希值，也就是说每次插入或更新的时候都会计算一个哈希值存储到表中，相应的表中需要多出哈希值这个字段。由于会出现哈希冲突，因此当使用哈希索引进行查询的时候，必须在WHERE子句中带入哈希值和对应列值。
      • 弊端：需要维护哈希值。可以通过触发器来实现。可以创建INSERT和UPDATE的触发器，用来自动维护自定义哈希索引。
      • 哈希函数的选用：记住不要使用SHA1()和MD5()函数作为哈希函数，因为这两个函数计算出来的哈希值是非常长的字符串会浪费大量空间，使用时会更慢。如果使用CRC32()会出现大量哈希冲突可以考虑自己实现可以64位的哈希函数，这个函数要返回整数而不是字符串。可以使用MD5()函数返回值的一部分作为自定义哈希函数。
3. 空间数据索引(R-Tree)：MyISAM表支持空间索引，可以用作地理数据存储。这类索引会从所有维度来索引数据，查询时，可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数来维护数据。
4. 全文索引：全文索引用文本中的关键字进行查找，而不是直接比较索引中的值，这个知识点后续进行总结。

索引的优点

1. 索引大大减少了服务器需要扫描的数据量。因为服务器可以通过索引快速定位到表中的指定位置。
2. 索引可以帮助服务器避免排序和临时表。B-Tree索引按照顺序存储数据，所以MySQL可以用来做ORDER BY和GROUP BY操作，因为聚簇索引中存储了实际的列值，所以覆盖查询只使用索引就能够完成全部查询，而不需要回表查询。
3. 索引可以将随机I/O变成顺序I/O。因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。