这是我参与「第五届青训营 」伴学笔记创作活动的第 6 天

索引概述：

B+索引、（balance）

键值对(key value)方式找到数据。索引不能找到具体的行，是将相应的页加载到内存，然后内存中查找行数据。

底层算法支持

二分查找-二分查询树-平衡二叉树-B+树：

B+树：由B树和索引顺序演进而来。叶子节点之间双向链表

索引分类：

• 唯一索引：索引节点不允许重复，一般配合唯一约束
• 主键索引：主键的一种特殊唯一索引，不允许空值
• 普通索引：没什么特别约束

上方三个都是单列索引：

• 全文索引：只会在文本字段上
• 哈希索引:支持哈希索引的自适应，可以认为指定是否干预。
• 空间索引：用的不多。
• 联合索引：在多个字段上创建索引，

• 聚簇索引：逻辑上连续且物理上连续，索引数据和表数据在一起，一张表只能有一个，

表中没有定义主键，InnoDB会选取一个唯一的非空索引作为聚簇索引，如果没有非空的字段，那么隐式创建一个主键作为聚簇索引。

• 非聚簇索引：逻辑上连续物理空间上不连续，索引数据和表数据不在一起
• 前缀索引：利用字段前几个字符创建索引，节约了空间。数据多的时候，效果不明显
• 等名称，大同小异

索引优劣势：

优势：

• 查询速度快
• 创建唯一索引，保证了唯一性
• 排序分组时候快
• 联表查询有索引速度快
• 由于B+tree，范围查询快
• 降低了SQL执行时间

劣势：

• 占用存储变大
• 修改需要维护索引，浪费时间，性能降低

使用：

主键ID做索引

使用ID作为索引，自增非空。主键被作为聚簇索引，由于底层使用B+Tree这样不会容易破坏数据结构，因为操作可能导致数据挪动。

联合索引的矛盾

联合索引遵循左匹配原则，创建索引过程中字段有先后循序，错误的使用联合索引不能够发挥联合索引的作用，不能够加快查询效率。

前缀索引弊端：

利用字段前几个字符创建索引，节约了空间。数据多的时候，效果不明显

全文索引的硬伤：

使用like%进行查询，数据变多性能变差，全文索引进行模糊查询能够解决like缺陷。底层基于分词实现，会占据存储，文件会额外大。需要时间创建。精度不行。

唯一索引：

唯一索引查询方便，查到立即停止，但是插入麻烦，需要确保数据不重复。

hash索引缺陷：

范围查询不速度慢。

使用索引的正确姿势：

一般来说，判断字段是否要添加的索引的依据，是看这个字段是否被经常当做查询条件使用，但也不能光依靠这一个依据来判断，比如用户表中的性别字段，

查询回表：

非聚簇索引，叶子节点保存的是主键值，查询全部数据，需要到主键索引上去查询。

原则：

• ①经常频繁用作查询条件的字段应酌情考虑为其创建索引。
• ②表的主外键或连表字段，必须建立索引，因为能很大程度提升连表查询的性能。
• ③建立索引的字段，一般值的区分性要足够高，这样才能提高索引的检索效率。
• ④建立索引的字段，值不应该过长，如果较长的字段要建立索引，可以选择前缀索引。
• ⑤建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合。
• ⑥经常根据范围取值、排序、分组的字段应建立索引，因为索引有序，能加快排序时间。
• ⑦对于唯一索引，如果确认不会利用该字段排序，那可以将结构改为Hash结构。
• ⑧尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。

注意点：

• ❶值经常会增删改的字段，不合适建立索引，因为每次改变后需维护索引结构。
• ❷一个字段存在大量的重复值时，不适合建立索引，比如之前举例的性别字段。
• ❸索引不能参与计算，因此经常带函数查询的字段，并不适合建立索引。
• ❹一张表中的索引数量并不是越多越好，一般控制在3，最多不能超过5。
• ❺建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。
• ❻当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大。
• ❼索引的字段值无序时，不推荐建立索引，因为会造成页分裂，尤其是主键索引。

联合索引最左原则：

• 尽量使用联合索引代替单值索引，联合索引比多个单值索引快

创建联合索引是x，y，z的顺序，底层现先以x为标准进行排序建立索引，然后x相同则根据y建立索引，同理数据间x和y都相同最后建立z为标准。所以，最左原则指的是，查询时候想利用到联合索引，就需要先匹配左边的字段。

索引失效：

导致索引失效的几个原因

• or
• like以%开头，这样导致索引失效，没办法进行模糊查询。
• 字符查询不用 ``
• 数据进行运算
• 调用了函数
• 违背最左原则
• 不同字段值比较
• not in 这种反范围比较操作

通过explain进行性能分析可以判断。

索引使用正确姿势:

总结：

• ①查询SQL中尽量不要使用OR关键字，可以使用多SQL或子查询代替。
• ②模糊查询尽量不要以%开头，如果实在要实现这个功能可以建立全文索引。
• ③编写SQL时一定要注意字段的数据类型，否则MySQL的隐式转换会导致索引失效。
• ④一定不要在编写SQL时让索引字段执行计算工作，尽量将计算工作放在客户端中完成。
• ⑤对于索引字段尽量不要使用计算类函数，一定要使用时请记得将函数计算放在=后面。
• ⑥多条件的查询SQL一定要使用联合索引中的第一个字段，否则会打破最左匹配原则。
• ⑦对于需要对比多个字段的查询业务时，可以拆分为连表查询，使用临时表代替。
• ⑧在SQL中不要使用反范围性的查询条件，大部分反范围性、不等性查询都会让索引失效。
• ⑨.......

具体：

覆盖索引：

就是要查询的列，在使用的索引中已经包含，被所使用的索引覆盖，这种情况称之为索引覆盖。

查询使用了 select ，需要一行数据，不用主键怎么样都需要进行回表，所以不要用

索引下推：

在模糊查询的时候，匹配出数据，然后从数据中查询。不需要回表查询。、

MMR：

针对于辅助索引的回表查询，减少离散IO，并且将随机IO转换为顺序IO，从而提高查询效率

Index Skip Scan索引跳跃式扫描

没有满足最左原则时候，优化器为你重构SQL，然后能够满足最左原则，然后继续进行去重。