MySQL主键索引与联合索引原理详解

MySQL主键索引与联合索引原理详解

引用

CSDN

1.

https://blog.csdn.net/YZL40514131/article/details/137385699

在数据库系统中，索引是提高查询效率的关键技术。本文将深入解析MySQL中主键索引和联合索引的原理，帮助读者理解索引的工作机制，以及如何合理设计索引以优化数据库性能。

一、主键索引

主键索引是按照主键数据从小到大排序的B+树结构，叶节点只存储数据区的指针。当通过主键查找数据时，从B+树的头部开始寻址数据、读取数据。

上面为索引页，下面为数据页

例如，执行查询 select * from table where a=6 时，会从上到下查找索引：

对于范围查询如 select * from table where a<6，先找到a=6的位置，然后返回左边的所有数据：

如果查询条件是 select * from table where b=6，由于b未命中索引，需要进行全表扫描。

二、什么是联合索引? 对应的B+树是如何生成的?

联合索引是将数据库表中多个字段组成一个索引。例如，创建bcd联合索引时，bcd三个字段会按照从小到大的顺序进行排序。

1、建立索引方式

创建联合索引的SQL语句如下：

create index idx_t1_bcd on t1(b,c,d);

2、什么是最左前缀原则?

复合索引（联合索引）遵循最左匹配原则。创建一个(k1,k2,k3)的复合索引，相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引。

例如，执行查询：

select * from table where k1=A and k2=B and k3=C

3、回表

当执行查询 select * from t1 where b=1 and c=1 and d=1 时，通过联合索引找到数据的3个字段，但由于需要返回所有字段，因此需要通过主键字段去主键索引中查找完整的数据，这个过程称为回表。

4、为什么要遵守最左前缀原则才能利用到索引?

最左前缀原则要求查询条件从左到右依次匹配索引列。例如：

• 符合最左匹配原则：b=1（需要回表1次）
• 不符合最左匹配原则：b>1（需要回表多次）

5、什么是覆盖索引?

如果查询的字段正好在联合索引字段里面，就不需要回表。例如：

• select b from t1 where b>1（不需要回表）
• select a,b,c,d from t1 where b>1（不需要回表，因为a字段在联合索引中）
• select a,b,c,d,e from t1 where b>1（需要回表，因为e字段不在联合索引中）

6、索引扫描底层原理

从bcd索引的叶子节点开始遍历。主键索引存储的是完整的数据，而联合索引中存储的是不完整的数据。从叶子节点开始扫描不需要符合最左匹配原则。

7、order by为什么会导致索引消失？

当使用order by时，如果需要排序的列不在索引中，或者排序方式与索引顺序不一致，可能会导致索引失效。因为走全表扫描效率可能更高，不需要回表多次。

8、有哪些情况会导致索引失效?

• 使用!=、not、is null等操作符
• like通配符匹配后缀（%xxx）
• 对索引列进行函数运算
• or连接查询时，or语句前后没有同时使用索引
• 索引列存在隐式转换

9、索引设计原则

• 选择合适的列作为索引：经常作为查询条件的列、区分度高的列、更新不频繁的列
• 避免使用过多的索引：每个索引都需要占用额外的磁盘空间，更新表时需要更新所有索引

三、InnoDB 与MyISAM 的区别

• 聚簇索引：将数据存储与索引放到了一块，并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的。
• 非聚簇索引：叶子节点不存储数据，存储的是数据行地址，需要二次查询才能获取数据。

聚簇索引的优势：

1. 查询效率高，直接获取数据
2. 范围查询效率高
3. 适合排序场景