1. 索引的基本概述

1.1 索引是什么？

索引：一种帮助MySQL提高查询效率的数据结构。
优点：大大提高数据查询的速度。
缺点：
1. 维护索引需要耗费数据库资源；
2. 索引需要占用磁盘空间；
3. 当对表的数据进行增删改的时候，由于需要维护索引，速度会收到影响。

1.2 索引的分类

  1. 主键索引
    设定为主键后数据库会自动建立索引，在innodb引擎中为聚簇索引
  2. 单值索引
    一个索引只包含单个列，一个表可以有多个单列索引
  3. 唯一索引
    索引列的只必须唯一，但允许有一个空值
  4. 复合索引
    一个索引包含多个列

1.3 索引的使用

可以通过show index from 表名查看索引

  1. 主键索引
create table t_user(
	id varchar(20) primary key,
	name varchar(20)
);

# 添加主键后会自动创建主键索引
alter table t_user add primary key(id);
  1. 单列索引
create table t_user(
	id varchar(20),
	name varchar(20),
	key(name);
);

create index <索引名> on t_user (name);
  1. 复合索引
create table t_user(
	id varchar(20),
	name varchar(20),
	age int,
	key(name, age)
);

create index <索引名> on t_user (name, age);

在进行表查询时，复合索引遵循最左前缀匹配原则，例如：为t_user的三个字段 name age address 按顺寻创建了复合索引，查询下面的字段可以使用到索引：

  • name, age, address
  • name, age
  • name

MySQL引擎为了能更好的利用到索引，因此在查询时会动态调整字段的顺序，因此查询下面的字段也可以使用到索引：

  • age, address, name
  • age, name
  1. 唯一索引
create table t_user(
	id varchar(20),
	name varchar(20),
	unique key <索引名> (name)
);

create unique index <索引名> on t_user(name)

2. 索引的原理

2.1 思考：为什么插入的数据是无需的，而查询出的数据却是有序的？

---建表  
create table t_emp(id int primary key,name varchar(20),age int);

--插入数据  
insert into t_emp values(5,'d',22);  
insert into t_emp values(6,'d',22);  
insert into t_emp values(7,'e',21);  
insert into t_emp values(1,'a',23);  
insert into t_emp values(2,'b',26);  
insert into t_emp values(3,'c',27);  
insert into t_emp values(4,'a',32);  
insert into t_emp values(8,'f',53);  
insert into t_emp values(9,'v',13);

--查询  
select * from t_emp;

查询结果：

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |   23 |
|  2 | b    |   26 |
|  3 | c    |   27 |
|  4 | a    |   32 |
|  5 | d    |   22 |
|  6 | d    |   22 |
|  7 | e    |   21 |
|  8 | f    |   53 |
|  9 | v    |   13 |
+----+------+------+
9 rows in set (0.00 sec)

原因是MySQL会为主键创建索引，在创建索引的时候会进行排序。

2.2 索引的结构

进行排序的目的自然是为了更快的查询，在MySQL中索引的结构使用的是B+树，在它的叶子节点中，包含了数据的key值、data值和一个指针，节点和节点之间通过指针有序的排列在一起，如果只看这些叶子节点，就是一个链表，它们大概是这个样子：

Pasted image 20250623214828

当数据量非常大时，仅仅依靠叶子节点之间的链表遍历会效率低下。B+树通过引入多级索引结构来解决这个问题。上层的非叶子节点（或称作页目录、索引页）存储了指向下层节点的指针，每一层都像一个“目录”，指引着数据所在的页。当查询数据时，查询过程从根节点开始，通过逐层向下查找（类似二分查找），快速定位到目标数据所在的叶子节点，然后可以在该叶子节点内部或通过叶子链表进行精确或范围查找。

Pasted image 20250623215231

2.3 B+树

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

Pasted image 20250623215546

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

  1. 非叶子节点只存储键值信息。
  2. 所有叶子节点之间都有一个链指针。
  3. 数据记录都存放在叶子节点中。
  • InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值(因为是估值，为方便计算，这里的K取值为〖10〗3)。也就是说一个深度为3的B+Tree索引可以维护103 * 10^3 * 10^3 = 10亿 条记录。
  • 实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1-3次磁盘I/O操作。

3. 聚簇索引和非聚簇索引

3.1 概念

  • 聚簇索引： 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

  • 非聚簇索引：将数据与索引分开存储，非聚簇索引叶子节点存储的不再是行的物理位置，而是主键值

  • 在innodb中，主键索引默认就是聚簇索引，但如果没有在表中定义主键，InnoDB也会为表创建一个聚簇索引，规则如下：

    • 唯一非空索引：如果表上存在唯一索引且所有键列都非空，InnoDB会选择这个索引作为聚簇索引。
    • 隐式主键：如果以上两种情况都不满足，InnoDB会隐式地生成一个隐藏的6字节GEN_CLUST_INDEX作为聚簇索引。

  • 在innodb中，在聚簇索引之上创建的索引称之为辅助索引，而辅助索引都是是非聚簇索引，辅助索引访问数据总是需要二次查找（需要查找聚簇索引）。

Pasted image 20250624141648

3.2 索引查找过程

  1. InnoDB
  • InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
  • 若对name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。(重点在于通过其他键需要建立辅助索引)
  1. MyISAM
  • MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

Pasted image 20250624141618

3.3 聚簇索引的优点

问题: 每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？

在 InnoDB 存储引擎中，聚簇索引（Clustered Index）的叶子节点本身就是数据页，也就是说，数据是与主键索引紧密存储在一起的。每个数据页通常包含多条行记录，访问某一行数据时，所在的整页会被加载到内存的缓冲池中，这样后续访问同一页中的其他行可以直接从内存中获取，避免了磁盘 I/O。此外，聚簇索引按照主键的顺序对数据进行物理排序，因此在执行范围查询时，能够顺序地读取磁盘块，具有更高的性能优势。

辅助索引（Secondary Index）中，叶子节点并不直接存储行数据，而是存储该行对应的主键值。查询时需要先通过辅助索引定位主键，再回到聚簇索引中根据主键获取完整行数据（即“回表”）。这种设计一方面避免了在辅助索引中重复存储整行数据，从而节省空间；另一方面，由于只依赖主键作为引用，即便行的物理位置发生变化（如页分裂或行迁移），辅助索引的引用也无需修改，提高了数据页变动时的维护效率。不过需要注意的是，如果涉及到辅助索引自身包含的列发生更新，仍需对索引进行相应的维护。

3.4 为什么建议主键使用自增id

在 InnoDB 中，表的数据以聚簇索引（Clustered Index）的形式组织，数据的物理存储顺序与主键索引顺序保持一致。这意味着当插入新数据时，其在磁盘上的位置会尽可能地保持与主键值的顺序一致。如果主键是自增 ID，新插入的记录总是追加到当前索引的末尾，数据页填满后顺序写入新的页，这种模式写入逻辑简单，索引结构紧凑，有助于减少页分裂和磁盘碎片，提高插入性能。

相反，如果主键是非自增、无序或具有跳跃性（如 UUID 或随机字符串），新记录可能需要插入到索引树的中间位置，InnoDB 为保持索引有序性，需要在插入时执行页分裂、移动已有记录和重新分配页等操作，这不仅增加了写入开销，还可能导致磁盘碎片化，使查询和维护效率降低。虽然 InnoDB 内部通过一定机制（如自适应哈希索引、延迟页分裂）缓解这些问题，但仍无法完全避免性能影响。

因此，选择自增 ID 作为主键，可以最大程度地保持插入的顺序性，简化存储管理，提高整体写入性能和索引效率，这也是实际开发中广泛推荐的做法。

3.5 无法使用索引的场景

  • 查询语句中使用LIKE关键字
    在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。

    • LIKE 'abc%' 可以
    • LIKE '%abc' 或 LIKE '%abc%' 不可以

  • 查询语句中使用多列索引
    多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。

  • 查询语句中使用OR关键字
    查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。

    • WHERE col1 = 1 OR col2 = 2（且 col1、col2 都是独立索引）可以
    • WHERE col1 = 1 OR col3 = 2（若 col3 无索引，则全表扫描） 不可以