为什么 InnoDB 选择 B+tree 作为索引的数据结构？
什么是索引，它有什么优缺点？
索引是一种数据结构, 像指向表行的指针, 可以快速查询并确定哪些行与WHERE子句中的条件匹配，并检索这些行的其他列值.
- 提升查询检索速度,减少磁盘I/O.
- 不必要的索引浪费了额外的物理空间，浪费了MySQL的时间来确定要使用哪些索引.
- 同时它牺牲了插入、更新、删除的速度, 因为每个索引都必须更新.
- 使表更复杂,增加维护成本
必须找到正确的平衡，才能使用最佳的索引集实现快速查询。
InnoDB 索引类型
按照四个角度来分类索引。
- 按「物理存储」分类：聚簇索引、二级索引。
- 按「字段个数」分类：单列索引、联合索引。
- 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
- 按「数据结构」分类：B+tree索引、Full-text索引(5.6版本后支持)。
聚簇索引和非聚簇索引
聚簇索引和非聚簇索引的区别
- 聚簇索引的 B+Tree 的叶子节点存放的是实际完整数据，非叶子节点存的是主键key值
- 非聚簇索引(二级索引)的 B+Tree 的叶子节点存放的是主键key，非叶子节点是列索引键key值
InnoDB 聚簇索引选择场景
在创建表时:
- 有主键，默认会使用主键；
- 没有主键，就选择第一个不包含 NULL 值的唯一列；
- 上面两个都没有，将自动生成一个隐式自增 id 列做代替；
聚簇索引外其他都属于辅助索引，也被称为二级索引或非聚簇索引。聚簇索引和二级索引默认使用的是 B+Tree 索引
覆盖索引和回表
- 在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。
- 如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。
单列索引、联合索引
- 建立在单列上的索引称为单列索引，比如主键索引；
- 建立在多列上的索引称为联合索引(复合索引)；
联合索引
联合索引的底层是一颗B+树，构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。在联合索引中，只有左边的字段被用到，右边的字段才能够被使用到。
所以使用联合索引时，存在最左匹配原则，按照最左优先的方式进行索引的匹配。不遵循「最左匹配原则」，联合索引会失效。
最左匹配原则
比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：
- where a=1；
- where a=1 and b=2 and c=3；
- where a=1 and b=2；
需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。
但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:
- where b=2；
- where c=3；
- where b=2 and c=3；
上面这些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。
只有在 a 相同的情况才，b 才是有序的，比如 a 等于 2 的时候，b 的值为（7，8），这时就是有序的，这个有序状态是局部的，因此，执行where a = 2 and b = 7是 a 和 b 字段能用到联合索引的，也就是联合索引生效了。
索引条件下推 (ICP)
之前通过「非主键索引」查询时，先通过索引回表查询数据，再过滤符合条件的数据
5.6版本后有索引下推优化，当存在索引列作为判断条件时，先选出符合条件的索引项，然后回表查询得到符合条件的数据.
Eg:
a、b均为非主键索引查询
……where 1<a<2 and b =1
没有索引下推
1<a<2 的id ===> 1<a<2 的完整数据 ===> 再过滤出b =1的数据.
ICP优化后
查到符合 1<a<2 的 and b =1 条件的主键值 ===> 回表查完整数据
明显ICP优化后的需要查询的完整数据小于优化前,这样减少了回表查询次数,提升整体性能
主键索引、唯一索引、普通索引、前缀索引
主键索引
建立在主键字段上的索引，通常在建表时创建，一张表最多有一个主键索引，不许为空。
唯一索引
建立在 UNIQUE 字段上的索引，一张表可以有多个，值必须唯一，允许为空。
普通索引
建立在普通字段上的索引，非主键，非UNIQUE字段。
前缀索引
指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。
前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。
B+tree索引、Full-text索引
Full-text索引
全文索引（Full-text indexing）是一种特殊类型的索引，用于在数据库中搜索文本信息。它允许你对文本数据进行快速的全文搜索。
B+tree索引
B+tree的所有数据储存在叶子结点上,并且叶子节点的数据是用双向链表关联. 非叶子节点存Key和指针.
为什么 InnoDB 选择 B+tree 作为索引的数据结构？
1、B+Tree vs B Tree
B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。
另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。
2、B+Tree vs 二叉树
对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。
在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。
而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。
3、B+Tree vs Hash
Hash 在做等值查询效率快，搜索复杂度为 O(1)。
但是 Hash 表不适合做范围查询，它更适合做等值的查询，B+Tree 索引要比 Hash 表索引有更广泛的适用场景。
索引失效的场景
- 不满足最左匹配原则
- 计算、函数
- 类型转换
- 范围条件右边的列索引失效
- 没覆盖索引时，“不等于”、not like 、左模糊查询
- “OR”前后存在非索引列
- 索引列的null值比较少,is not null不走索引
- 索引列的null值比较多,is null不走索引
索引的创建优先
单列索引:
尽量选择查询过滤性更强的字段、长度尽量小的字段
组合索引:
- 尽量选择where子句中更多的索引;
- 根据最左匹配原则: 根据独特程度, 独特的排前面; 频繁程度, 频繁的排前面.
- 如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。
不建或少建索引场景
- 如果表数据量太少可以不建索引，有时候全表扫描可能比索引快。
- 对于DML操作很频繁的表不建议，前提是保证查询性能的情况下。
- 表数据重复且分布平均的表字段，比如表记录10万行，取值只有男或者女。
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
