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

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

引用

CSDN

1.

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

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

一、主键索引

主键索引是按照主键数据从小到大排序的，叶节点只存储数据区。通过主键查找数据时，从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. 为什么要遵守最左前缀原则才能利用到索引?

例如，执行查询：

explain select * from t1 where c=1 and d=1 and b=1;

最左前缀原则和后面条件的顺序没有关系：

• 不符合最左匹配原则：11*
• 符合最左匹配原则：1*1

对于 b>1 的查询，由于需要回表很多次，因此不走联合索引。

5. 什么是覆盖索引?

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

select b from t1 where b>1;

如果查询包含主键字段，如：

select a,b,c,d from t1 where b>1;

同样不需要回表，因为主键字段在联合索引中。但如果查询包含不在联合索引中的字段，如：

select a,b,c,d,e from t1 where b>1;

则需要回表。

6. 索引扫描底层原理

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

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

因为走全表扫描效率更高，不需要回表。而走索引会回表多次，效率低。

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

1. 索引列使用 !=、not、is null、is not null 查询时
2. 使用 like 通配符匹配后缀 %xxx 时
3. 对索引列上进行函数运算时
4. 使用 or 连接查询时，只有 or 左右查询字段都是索引列时才会生效
5. 索引列存在隐式转化时

9. 索引设计原则

1. 经常作为查询条件、排序条件、分组条件的列建立索引
2. 区分度低的字段（如性别）不要建立索引
3. 更新频繁的字段不适合创建索引
4. 对于定义为 text、image 和 bit 的数据类型的列不要建立索引
5. 定义有外键的数据列一定要建立索引
6. 数据量较小的表，索引效果较差，没有必要在此列建立索引
7. 尽量使用扩展索引，不要新建索引
8. 使用短索引，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行

三、InnoDB 与MyISAM 的区别

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

聚簇索引的优势：

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