MySQL索引

MySQL学习总结系列

1.MySQL概览

2.MySQL索引

3.MySQL锁

4.MySQL事务

        Innodb和mylsm都采用B+的结构作为数据索引，innodb采用的是聚簇索引（索引和数据在一起），mylsm采用的是（非聚集索引）。通过索引结构（类似于字典中的目录）达到提升数据查询效率的目标。但它也并不是万能的，索引的存储以及数据结构的维护同样需要消耗系统资源（存储索引占用内存，维护索引可能导致频繁的树的旋转，占用cpu资源），在有些场景下索引并不适用。例如写多读少的情况；数据本身存在大量重复导致索引分类不明显（例如用户表中的性别，只有男女）；

        索引采用B+树的原因

1.主要是利用B+树的节点个数较多，树的高度较低能够减少io次数（相比于二叉树和红黑树等）

2.B+树仅在叶子节点存储数据（相比与B树），它能够将更多的数据驻留在内存，提升缓存的命中率。

3.B+树的所有叶子节点形成有序的链表结构，便于范围等数据的查找（相比B树）。

4.B+树搜索的稳定性较好，数据都存储在同一层意味着同一个表中所有数据都查询时间都一致（所有数据搜索的路径长度一致），相比B树，可能在上级树节点就找到了数据，其搜索性能不稳定。

B数索引结构

B+树索引结构

索引分类：聚集索引和非聚集索引（普通索引、唯一索引、全文索引）。非聚集索引也被称为二级索引，非主键字段创建的一般都是二级索引。二级索引命中以后获取到的是当前数据的主键，还需要再次进行回表操作获取完整的数据行。如果查询字段正好是索引字段，那么就通过索引覆盖避免了回表操作，减少一次IO。聚集索引和非聚集索引的差异在于叶子结点存储数据的内容，聚集索引存储的是所有数据，非聚集索引存储的是数据对应的主键。

        唯一索引和主键索引的区别在于主键索引不能为null且主键索引属于聚集索引。

索引下推：在非主键索引上的优化，可以减少回表次数，提升数据库效率。检索数据时附带检索条件，当查询到数据时并不直接返回给服务端去判断，而是将检索条件交给存储引擎进行判断，这样减少了从服务端到存储引擎之间的一次操作。

索引下推生效前

索引下推生效后

最左匹配：针对组合索引而言，索引会按照最左边的列进行有限匹配，如果查询条件种不包括最左边的列则不会命中索引。

索引覆盖：当查询字段中包括当前的索引字段，那么就不会进行回表，而是直接将当前索引值返回。

索引失效场景：索引字段适用函数、不符合最左匹配、不同字符编码转换导致失效、逻辑非判断等。innodb下行锁的实现是基于索引的，当索引失效时其对应的行锁会升级为表锁，因此上线前需对sql进行explain验证确认。

最佳应用实践

1.频繁查询的字段建议添加索引

2.单表的索引个数建议不超过5个

3.更新频繁的字段不适合创建索引

4.唯一性太差的不建议单独创建索引，即便是频繁查询字段