mysql的二级索引是什么？二级索引的工作方式

二级索引（Secondary Index）是指除主键索引（聚集索引）之外的索引。

在 InnoDB 中，二级索引也被称为非聚集索引（Non-Clustered Index）。

二级索引的特点

1. 存储结构：二级索引的叶子节点不存储完整的数据行，而是存储索引列的值和对应的主键值
2. 回表查询：通过二级索引查询完整记录时，需要先找到主键值，再通过主键索引查询完整记录（这个过程称为"回表"）
3. 维护成本：当数据发生变化时，所有相关的二级索引都需要更新

具体例子

假设有一个用户表：

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT,
    created_at DATETIME,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_age_created (age, created_at)
);

在这个表中：

• id列是主键，形成聚集索引
• idx_username、idx_email和idx_age_created都是二级索引

二级索引的工作方式

1. 单列二级索引例子： 假设我们执行查询：SELECT * FROM users WHERE username = 'zhang_san';

   • 数据库会使用idx_username索引
   • 索引查找zhang_san对应的记录，得到主键值（比如id=42）
   • 再通过主键索引查找id=42的完整记录（这就是回表操作）

2. 联合索引例子： 假设我们执行查询：SELECT * FROM users WHERE age = 25 AND created_at > '2023-01-01';

   • 数据库使用idx_age_created联合索引
   • 索引先按age=25过滤，再按created_at > '2023-01-01'条件筛选，得到符合条件的主键值集合
   • 对每个主键值进行回表查询，获取完整记录

3. 覆盖索引的情况： 假设我们执行查询：SELECT username, email FROM users WHERE username = 'zhang_san';

   • 由于查询只需要username和email列
   • 如果有一个包含这两列的联合索引，就可以直接从索引中获取所需数据，无需回表
   • 这种情况称为"覆盖索引"查询，效率更高

二级索引与聚集索引的区别

以实际存储的角度看：

聚集索引（主键索引）的结构：
id(主键) -> [完整数据行]

二级索引（idx_username）的结构：
username -> id(主键值)

当使用二级索引查询时，需要先找到主键，再通过主键找到完整数据，这就是"回表"操作。

这种结构使得 InnoDB 的二级索引比聚集索引占用空间小，但查询完整数据时需要额外的一次查找操作。