MySQL-索引

索引类型：

• B+树类型索引（最常用）
• 哈希索引
• 全文索引

B+树的特点：

B+树是一个多叉树，一个父节点，可以有多个子节点，主要的特征有三个：

• B+树的中间节点不会存储数据，而只有叶子节点才会存储，中间节点之用来存储到叶子结点的路由信息（索引），而且每个节点的里面的数据都是根据索引的值来顺序存放的。
• B+树的所有叶子节点之间会通过双向指针串联在一起，构成一个双向链表，可以方便扫表和范围查询
• B+查询性能稳定，因为所以叶子节点都在同一层，确保了所有数据的检索都具有相同的I/O延迟，而且B+树保存千万级别的数据，树的高度依然维持在3~4层左右，也就是从千万级别数据查询一条数据只需要3~4此的磁盘I/O操作就能查询到目标数据。

聚簇索引和非聚簇索引：

聚簇索引和非聚簇索引（二级索引）存放主要的区别是B+树叶子节点存放的内容不同：

• 聚簇索引的B+数叶子节点存放的是主键值+完整的记录；
• 非聚簇索引的B+树叶子节点存放的是索引值+主键值；

回表：

如果查询语句的查询条件用了二级索引，但是查询的数据不是主键值，也不是二级索引值，这是在二级索引找到主键值后，就需要回表才能查到数据，需要扫描两次B+树。

覆盖索引：

如果查询的列是主键值和二级索引值时，因为在二级索引就能查到数据，这时候就会用到覆盖索引，不需要回表，只需要扫描一次B+树。

问题：

数据实例：

主键值VS索引值

结合到之前的索引结构：

聚簇索引叶子节点：存放主键值（1）+完整记录（id=1，usename=zhangsan，age=20，address=广州）这里主键值时“核心锚点”，叶子节点直接通过它关联到完整数据，无需额外查找。

非聚簇索引：存放索引值（zhangsan）+主键值（1，3）这里索引值是“检索入口”-先通过索引值找到所有匹配的主键值，再拿着主键值去聚簇索引找完成记录（这个过程叫做“回表”）。如果要找的数据就是username，那么那么直接返回值（这个过程叫做“覆盖索引”）。

主键VS索引

主键本质上是一个数据约束，而索引是一个数据结构。

InnoDB：必须有聚簇索引-如果没有定义主键，数据库会优先找唯一非空列创建聚簇索引；如果都没有，会隐式生成一个6字节的行ID作为聚簇索引。

MyISAM：主键和索引完全分离-主键只是普通的唯一索引（非聚簇），MyISAM里的“主键索引”就是一个普通的唯一非聚簇索引，叶子节点存的是行号（非完整数据），和其他二级索引结构一致。

为什么需要二级索引：

二级索引的核心价值是避免全表扫描，通过B+树的有序结构快读定位数据，直接减少数据库扫描的行数，这是新能优化的关键。

优化原理：

没有二级索引时：查询WHERE username = ‘zhagnsan’ 需要逐行遍历整张表（全表扫描），表数据越多，耗时越长。

有了二级索引：数据库查询二级索引的B+树（叶子节点存放了username值+主键id），快速查找所有zhansan对应的主键id，在用过主键id查聚簇索引获取到完整的数据距离（回表）整个过程扫描的行数远少于全表扫描。

对于二级索引来说，能够显著优化查询的性能，但是对于新增，删除，更新操作来说更加的耗时了。

索引失效：

• 当我们查询语句对索引字段进行左模糊匹配、表达式计算、函数、隐式类型转换操作，这时后查询语句就无法走索引了，查询方式就变了全表扫描的方式。我们使用联合索引进行查询的时候，如果没有遵循最左匹配原则，也是会发生索引失效的。
• 优化器时基于成本考虑来选择查询的方式，在使用二级索引进行查询的时候，优化器会计算回表的成本和全表扫描的成本，如果回表的代价太高，优化器会选择不走索引，而是走全表扫描。

什么是最左匹配原则：

假设有一个（a，b，c）联合索引，它的存储顺序时先按a排序，在a相同的情况下再按b排序，再b相同的情况下再按c排序。由于这个特性，在使用联合索引时，存在最左匹配原则，具体的表现规则：

• MySQL会从联合索引最左边的索引列开始匹配查询条件，然后依次从最左到右的顺序匹配，如果查询条件没有使用到某个列，那么该列右边的所有列都无法使用走索引。
• 当查询条件使用了某个列，但是该列的值包含范围查询，范围查询的字段额可以用到联合索引，但是在范围查询字段后面的字段无法使用联合索引。