索引问题汇总

什么是索引？

• 索引是保证MySQL高效查询数据的数据结构
• 索引减少服务器需要扫描的数据量。
• 索引可以避免排序和临时表。
• 索引可以将随机IO变为顺序IO。

索引有哪些数据结构

• B-Tree索引
• HASH索引
• Full-Text索引
• R-Tree索引

B-Tree索引结构

一个m介B树的定义如下：

• 每个节点最多有m个子树
• 根节点不是叶子节点，至少有两个子树。
• 除根节点之外的所有非叶子节点至少有m/2棵子树。
• 所有非叶子节点中包含k个关键字和k+1个子树。
• 所有叶子节点都出现在同一层次，并且不带信息，视为查找失败节点。

B+树和B树的区别

• 非叶子节点仅用作索引，全量数据记录在叶子节点中。
• 每个非叶子节点中包含k个关键字和k个子树。
• 叶子节点之间用指针连接。

索引使用B+树而不是用B树的原因

• B+树非叶子节点不存数据，可以在一个节点中包含更多建，有利于降低树的高度。
• B+树叶子节指针连接，可以在叶子节点所在层支持范围访问，而B树则需要进行中序遍历，这可能需要更多内存或更多磁盘IO。

索引使用B+树而不是用红黑树的原因

• 红黑树高度太高，如果一个页只存一个节点那么会浪费空间，如果一个页存多个节点，插入删除复杂，且会涉及大量页的访问和修改。

索引使用B+树而不是用跳表的原因

​ 可能因为跳表高度更高一些吧，而且高度随机，查询效率不稳定。

B+树索引分裂原理

1. 没有直接使用50%分裂，空间利用率不高，顺序插入时会导致右页面更快分裂，分裂次数多。

2. 通过记录页面中记录的插入方向(PAGE_LAST_INSERT,PAGE_DIRECTION,PAGE_N_DIRECTION)来确定选择左右分裂，在递增插入的情况下不需要进行页分裂性能最好。该优化存在一个bug参考Bug#67718。

B+树和Hash索引的区别

1. Hash索引对于等值查询效率更高

2. Hash索引不支持模糊，多列索引最左前缀匹配，范围查询，排序等。

3. Hash冲突会降低查询效率。

聚簇索引

1. 数据存储方式，聚集索引的顺序与数据真实的物理存储顺序一致。

2. 一个表中只能有一个聚簇索引，默认是主键或者是非空唯一索引。

辅助索引（非聚簇索引）

1. 索引的叶子节点存放的是键和聚簇索引的键。

2. 一个表可以有多个非聚簇索引。

联合索引

1. 由多个列组成的索引，节点中键包含索引列以及聚簇索，按照列定义的顺序(优先级)进行排序。

2. 遵循最左匹配原则。

3. 联合索引最多包含16个字段。

覆盖索引

1. 辅助索引中就能获取到需要的字段，不需要回表。

2. 覆盖索引中不包含记录，数据量比聚簇索引上，可能会减少IO次数。

索引失效的情况

1. 未使用该列作为查询条件。

2. 隐式转换，例如字符串类型，查询条件未加引号。(注意数值类型，带引号和不带引号都可以使用索引

3. 使用like+前缀通配符。

4. 对非索引列使用or，或者关联查询中对不同表使用or。

5. 索引列使用运算或者函数，例如对索引列使用hash函数。

6. 使用联合索引但不符合最左匹配原则。

7. 优化器选择全表查询。

MySQL命中索引但是效率低的原因

1. 索引字段重复值太多。

2. 没有利用覆盖索引，需要回表。

3. 表中包含多个索引，命中的不是最优索引。

4. 查询字段过多，或者包含大字段。

数据库 where a = xx and b = xx 和 where b = xx ，这两条SQL如何建索引？

1. 建立联合索引key(b,a)，where条件中的=可以乱序会自动优化满足最左匹配原则。

建立索引的原则

1. 选择唯一索引，或者选择性高的索引。
2. 为经常查询以及需要排序、分组和联合操作的字段建立索引。
3. 限制索引数量。
4. 使用数据量小的列作为索引或者使用前缀索引。
5. 将区分度不高的单列索引，修改为联合索引。