什么是索引？
-
索引是保证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+树索引分裂原理
-
没有直接使用50%分裂
，
空间利用率不高
，顺序插入时会导致右页面更快分裂，
分裂次数多
。 -
通过记录页面中记录的插入方向(PAGE_LAST_INSERT,PAGE_DIRECTION,PAGE_N_DIRECTION)来确定选择左右分裂，在
递增
插入的情况下不需要进行页分裂性能最好。该优化存在一个bug参考
Bug#67718
。
B+树和Hash索引的区别
-
Hash索引对于等值查询效率更高
-
Hash索引不支持模糊，多列索引最左前缀匹配，范围查询，排序等。
-
Hash冲突会降低查询效率。
聚簇索引
-
数据存储方式，聚集索引的顺序与数据真实的物理存储顺序一致。
-
一个表中只能有一个聚簇索引，默认是主键或者是非空唯一索引。
辅助索引（非聚簇索引）
-
索引的叶子节点存放的是键和聚簇索引的键。
-
一个表可以有多个非聚簇索引。
联合索引
-
由多个列组成的索引，节点中键包含
索引列以及聚簇索
，按照列定义的顺序(优先级)进行排序。 -
遵循最左匹配原则。
-
联合索引最多包含16个字段。
覆盖索引
-
辅助索引中就能获取到需要的字段，
不需要回表
。 -
覆盖索引中不包含记录，数据量比聚簇索引上，可能会
减少IO次数
。
索引失效的情况
-
未使用该列作为查询条件。
-
隐式转换，例如字符串类型，查询条件未加引号。(
注意数值类型，带引号和不带引号都可以使用索引
-
使用like+前缀通配符。
-
对
非索引列
使用or，或者关联查询中对
不同表
使用or。 -
索引列使用运算或者函数，例如对索引列使用hash函数。
-
使用联合索引但不符合最左匹配原则。
-
优化器选择全表查询。
MySQL命中索引但是效率低的原因
-
索引字段
重复值
太多。 -
没有利用
覆盖索引
，需要回表。 -
表中包含多个索引，命中的
不是最优索引
。 -
查询
字段过多
，或者包含大字段。
数据库 where a = xx and b = xx 和 where b = xx ，这两条SQL如何建索引？
- 建立联合索引key(b,a)，where条件中的=可以乱序会自动优化满足最左匹配原则。
建立索引的原则
- 选择唯一索引，或者选择性高的索引。
- 为经常查询以及需要排序、分组和联合操作的字段建立索引。
- 限制索引数量。
- 使用数据量小的列作为索引或者使用前缀索引。
- 将区分度不高的单列索引，修改为联合索引。