MySQL学习专题

MySQL 索引:聚簇索引、二级索引与回表

观察二级索引如何定位记录,以及什么是覆盖查询。说明适用版本、完整示例、预期结果与验证边界。

简明结论

InnoDB 聚簇索引的叶子记录包含行数据,二级索引包含定位聚簇记录所需的主键值;查询需要的列全部在索引中时可能避免回表。

先限定讨论范围

本文针对 MySQL 8.4 的 InnoDB 普通 B-tree 索引,解释聚簇和二级索引的区别。不要把结论直接推广到所有存储引擎或全文、空间索引。

索引叶子层保存什么查询时的含义
聚簇索引行记录通过主键找到行
二级索引索引列及主键定位信息需要其他列时继续访问聚簇记录

没有显式主键时,InnoDB 会按规则选择合适的唯一索引或内部行 ID,因此不应把“没有建主键”理解为“没有聚簇索引”。

建立隔离的演示数据

仅在可丢弃测试库执行,下面会重建演示表,不连接生产数据库。

CREATE DATABASE IF NOT EXISTS cxykk_knowledge;
USE cxykk_knowledge;
DROP TABLE IF EXISTS demo_user;
CREATE TABLE demo_user (
  id INT PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  age INT NOT NULL,
  KEY idx_email (email)
) ENGINE=InnoDB;
INSERT INTO demo_user (id, email, age)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 500)
SELECT i, CONCAT('user', i, '@example.test'), 20 + MOD(i, 40) FROM n;
DROP TABLE IF EXISTS account;
CREATE TABLE account (id INT PRIMARY KEY, balance INT NOT NULL) ENGINE=InnoDB;
INSERT INTO account VALUES (1, 100), (2, 100);
USE cxykk_knowledge;
SHOW INDEX FROM demo_user;
EXPLAIN SELECT id, email FROM demo_user WHERE email = 'user42@example.test';
SELECT id, email FROM demo_user WHERE email = 'user42@example.test';

预期选中 idx_email,查询返回 42 与 user42@example.test。在本次验证中,EXPLAIN 的 Extra 包含 Using index。

为什么小表也可能不用索引

优化器会根据统计信息和成本选择计划,建了索引不代表必须使用。示例数据和查询投影刻意固定;换成大量不在索引中的列、低选择性条件或不同数据分布时,应重新 EXPLAIN。

实践边界

更长的主键会增大二级索引的存储成本。添加索引同时会增加写入维护成本,不能仅凭一次查询结果就为所有字段建索引。本文没有比较磁盘页布局或生产延迟。

编辑说明

本页依据原资料重新整理,保留来源链接;新增的最小示例和验证范围由本站编辑补充。原作者身份未在历史资料中明确时,不另造署名。