我要评论参考资料：小林coding、阿秀
一、为什么innodb采用b+树作为索引数据结构？
b 树是一个自平衡多路搜索树，每一个节点最多可以包括 m 个子节点，m 称为 b 树的阶，所以 b 树就是一个多叉树。
b+ 树与 b 树的差异：
-
叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引；
-
所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表；
-
非叶子节点的索引也会同时存在子节点中，并且是在子节点中所有索引的最大（或最小）。
-
非叶子节点中有多少个子节点，就有多少个索引；
mysql中b+树结构：
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 表索引有着更广泛的适用场景的原因。
二、索引适用场景
首先，先明确为什么需要使用索引?
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
- 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
- 帮助服务器避免排序和临时表
- 将随机io变为顺序io
- 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
在此基础上，思考什么场景下适合使用索引？
- 字段有唯一性限制的，比如商品编码；
- 经常用于
where查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。 - 经常用于
group by和order by的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 b+tree 中的记录都是排序好的。
什么时候不需要创建索引？
where条件，group by，order by里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。- 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 mysql 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
- 表数据太少的时候，不需要创建索引；
- 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 b+tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。
三、索引优化
1. 前缀索引优化
使用某个字段中字符串的前几个字符建立索引，减小索引字段大小，增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。
不过，前缀索引有一定的局限性，例如：
- order by 就无法使用前缀索引；
- 无法把前缀索引用作覆盖索引；
2. 覆盖索引优化
覆盖索引是指 sql 中 query 的所有字段，在索引 b+tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作，减少大量的 i/o 操作。
3. 主键索引最好是自增的
innodb 创建主键索引默认为聚簇索引，数据被存放在了 b+tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。
如果使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
如果使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
4. 索引最好设置为 not null
为了更好的利用索引，索引列要设置为 not null 约束。有两个原因：
-
索引列存在 null 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 null 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为null 的行。
-
null 值是一个没意义的值，但会占用物理空间，带来存储空间的问题，因为 innodb 存储记录的时候，如果表中存在允许为 null 的字段，那么行格式中至少会用 1 字节空间存储 null 值列表，如下图的紫色部分：
四、索引失效
发生索引失效的情况：
- 当我们使用左或者左右模糊匹配的时候，也就是
like %xx或者like %xx%这两种方式都会造成索引失效； - 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
- 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
- 在 where 子句中，如果在 or 前的条件列是索引列，而在 or 后的条件列不是索引列，那么索引会失效。
发表评论