佟年为韩商言做的网站合肥网络公司

【MySQL学习笔记】关于索引

1.索引数据结构

索引是一种能提高查询速度的数据结构。

1、索引结构/索引类型：

按数据结构分类：B+树索引、Hash索引、全文索引。

按物理存储分类：聚簇索引、二级索引。

按字段特性分类：主键索引、唯一索引、普通索引、前缀索引。

按字段个数分类：单列索引、联合索引。

2、InnoDB的索引数据结构是什么？（InnoDB是支持B+树和FULLTEXT索引的）

我所了解的是B+树索引，在数据组织形式上，B+树的非叶子节点只存放索引和指向子节点的指针，叶子节点存储索引和行数据。并且所有叶子节点都是通过指针相连，形成一个双向链表，支持快速的顺序访问和范围查询。树的高度平衡，稳定性好。

2.索引存储

堆表和索引组织表有什么区别？分别应用场景是什么？

堆表是 MyISAM 使用的存储方式，索引组织表是 InnoDB 使用的存储方式。

堆表的数据是无序存放的，无需维护索引与数据的一致性，因此插入性能较好。同时，堆表的索引是非聚簇索引，所有索引是平级的，索引查询性能较高，通常一次可以定位到结果。但堆表仅支持表锁，在高并发场景下性能较差，适用于低并发、以读为主的场景。

索引组织表的数据按照主键顺序存储，主键索引即为数据本身，并支持行锁，因此在高并发场景下性能远高于堆表，特别适用于频繁的增删改操作。但由于需要维护索引顺序和数据一致性，其插入性能相对较低，尤其是当表的主键频繁变化或数据碎片较多时。

3.联合索引

3.1 联合索引的b+树结构

推荐这篇文章：联合索引在B+树上的存储结构及数据查找方式上一篇文章《MySQL索引那些事》主要讲了MySQL索引的底层原理，且对比了B - 掘金
引用文章里面的这个图：

InnoDB会使用主键索引在B+树维护索引和数据文件，创建的联合索引也会生成一个索引树，同样都是B+树的结构，只不过它的data部分存储的是联合索引所在行的值。
对于这个联合索引，排序规则就是根据定义索引的顺序，(b,c,d)，先根据b排序，若b相同，再根据c排序，依次类推。

3.2 索引覆盖？回表？

创建联合索引之后，进行查询时索引覆盖还是回表，主要是看SELECT的列。

就还是要上面的例子，进行查询。

select * from table_name where b = 12 and c = 14 and d = 3;

这个查询的是数据全部，所以需要回表，为什么呢？

这个还是基于联合索引的特性，它会创建一个新的B+树，但是这个树的叶子节点只有联合索引的参数，如果你查询的不是其中的列，那么就会根据这个查询到的主键id回表查询。

如果你查询的列就是其中一个，那么就不需要回表查询了，直接返回这个数据就行，这就是索引覆盖。

所以说，联合索引的优势在于支持索引覆盖查询，避免回表操作，并且对索引列的查询可直接利用索引的有序性完成排序，无需额外的外部排序，显著提升查询性能。

3.3 联合索引最左匹配原则

1. MySQL会从联合索引最左边的索引开始匹配查询条件，从左到右匹配，如果查询条件没有使用到某个列，那么该列右边的列全部失效。
2. 当查询条件使用了某个列，但是该列的值包含范围查询，范围查询的字段可以用到联合索引，但是范围查询字段后面的字段无法用到联合索引。

3.5 索引下推

索引下推是MySQL5.6的优化机制，默认是开启的，如果条件判断字段，在二级索引B+树里，就会下推到InnoDB存储引擎层来过滤，过滤完的记录，才会回表，相比没有索引下推的时候，可以减少回表次数。

Where a>100 and b=100 and c=200 order by d 怎么建立联合索引？

(bcda)，bc走索引，d利用了索引有序性，避免file sort，a不能走索引，走索引下推。

4.索引失效

索引失效有哪些？