MySQL中的索引相关问题

1. 索引采用的数据结构

• Hash表： Hash 索引不支持范围查询。
• 二叉搜索树：有可能树会退化成线性链表，时间复杂退化为 O（N）。
• AVL树和红黑树：除了要保持树的平衡，当数据量增加时，二叉树的高度会越来越高，进而导致一些数据需要进行多次磁盘 I/O 操作才能查询到。
• B树：B 树的每个节点都包含数据（索引+记录），记录数据的大小很有可能远远超过了索引数据的大小，除了需要花费更多的磁盘 I/O 操作次数来读到有用的索引数据，也占用了更多的内存资源，同时B树也不支持范围查询。
• B+树 ：B+树只有叶子节点存放索引和数据，非叶子节点只存放索引。叶子节点有一条双向链表进行相连，有利于范围查询。

2. 索引类型

• 主键索引：一张数据表有只能有一个主键，主键不能为 null且不重复。

• 二级索引（唯一索引、普通索引、前缀索引、全文索引）

• 聚簇索引（主键索引）：索引和数据一起存放的索引。

  • 优点：查询速度非常快，定位到索引的节点，就相当于定位到了数据。
  • 缺点：依赖于有序数据、需要维护索引，叶子节点放着数据，修改代价大，一般主键索引是不可被修改的。

• 非聚簇索引（二级索引）：索引和数据分开存放的索引。

  • 优点：更新代价相对聚簇索引要小。
  • 缺点：依赖于有序数据、可能会产生回表操作。

• 联合索引：使用表中的多个字段创建索引，就是联合索引，也叫组合索引或复合索引。最左前缀匹配原则：假设有一个联合索引(column1, column2, column3)，其从左到右的所有前缀为(column1)、(column1, column2)、(column1, column2, column3)，从最左侧字段开始向右进行匹配。

• 前缀索引(Prefix):前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。

• 索引覆盖：索引包含所有需要查询的字段的值。如果是索引覆盖，非聚簇索引可以不用产生回表操作，减少回表次数。

• 索引下推：将Server 层负责的事情，交给了存储引擎层去处理。除了可以减少回表次数之外，索引下推还可以减少存储引擎层和 Server 层的数据传输量。

3. 索引失效场景

• 模糊匹配：当使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
• 对索引进行计算：当我们在查询条件中对索引列使用函数，或者对索引列进行表达式计算时，也是无法走索引的。
• 隐式转换：当查询字段与索引字段类型不匹配时会发生隐式转换，如果是字符串和数字比较，会自动把字符串转为数字，然后再进行比较。由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
• 联合索引非最左匹配：要能正确使用联合索引需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
• WHERE子句中的OR：在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。