CH1
一、UNIQUE INDEX idx_email (email) 的语法解析
1. 括号内 email 的作用
- 指定索引列：
(email)表示该唯一索引基于email字段创建，确保该字段值唯一。 - 等效写法：
CREATE UNIQUE INDEX idx_email ON users (email); -- 显式指定表名 ALTER TABLE users ADD UNIQUE (email); -- 隐式创建同名索引
2. 索引名与列名的关系
idx_email是索引的名称（自定义），email是索引所基于的字段名。- 命名建议：
idx_表名_字段名，例如idx_users_email。
二、B+树结构中叶子节点的存储内容
1. 叶子节点的完整结构
在 非聚簇索引（如 idx_email）中，叶子节点存储：
- 索引键值：
email字段的值。 - 主键值：用于回表查询的
id。
修正后的B+树示例：
非叶子节点（仅存索引键）:
[bob@example.com]
/ \
叶子节点（索引键 + 主键）:
[alice@example.com, id=1] [charlie@example.com, id=3]
[bob@example.com, id=2] [david@example.com, id=4]
2. 为什么需要存储主键值？
当执行 SELECT * FROM users WHERE email = 'charlie@example.com' 时：
- 通过
idx_email索引找到叶子节点[charlie@example.com, id=3]。 - 通过
id=3访问主键索引，获取完整数据行（回表操作）。
三、聚簇索引与非聚簇索引的对比
1. 聚簇索引（主键索引）
- 叶子节点：直接存储 完整数据行。
- 示例：
B+树结构：CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2) );叶子节点: [id=1, name=Apple, price=5.99] [id=2, name=Banana, price=3.99] [id=3, name=Orange, price=4.99]
2. 非聚簇索引（如 idx_email）
- 叶子节点：存储 索引键 + 主键值，需回表查询完整数据。
- 示例：
B+树结构：CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(100), UNIQUE INDEX idx_email (email) );叶子节点: [email=alice@example.com, id=1] [email=bob@example.com, id=2] [email=charlie@example.com, id=3]
四、复合索引的叶子节点存储
1. 复合索引示例
CREATE TABLE orders (
user_id INT,
order_id INT,
amount DECIMAL(10,2),
PRIMARY KEY (user_id, order_id),
UNIQUE INDEX idx_user_order (user_id, order_id)
);
2. B+树结构
叶子节点:
[(user_id=1, order_id=1001), (主键值=1,1001)]
[(user_id=1, order_id=1002), (主键值=1,1002)]
[(user_id=2, order_id=1001), (主键值=2,1001)]
- 索引键：
(user_id, order_id)的组合。 - 主键值：由于主键是复合键
(user_id, order_id)，因此主键值与索引键相同。
五、覆盖索引（Covering Index）的优化
1. 定义
当查询的字段 完全包含在索引中 时，无需回表，直接从索引获取结果。
2. 示例
-- 创建覆盖索引
CREATE INDEX idx_user_email ON users (name, email);
-- 查询直接使用索引返回结果
SELECT name, email FROM users WHERE name = 'Alice';
3. B+树结构
叶子节点:
[name=Alice, email=alice@example.com]
[name=Bob, email=bob@example.com]
- 无需回表，直接从索引获取
name和email。
六、总结：索引叶子节点的核心规则
- 聚簇索引（主键）：叶子节点存储 完整数据行。
- 非聚簇索引：叶子节点存储 索引键 + 主键值，需通过主键回表。
- 复合索引：叶子节点按索引定义的列顺序存储组合键。
- 覆盖索引：叶子节点包含所有查询字段，避免回表。
建议通过 EXPLAIN 命令验证索引使用情况，观察 Extra 列是否出现 Using index，表示触发了覆盖索引优化。
CH2
一、主键与索引的关系澄清
1. 主键是一种特殊的索引
- 定义：主键是用于唯一标识表中每行数据的字段或字段组合，必须唯一且非空。
- 索引特性：MySQL 会自动为 主键 创建 聚簇索引（Clustered Index），即数据行在物理存储上会按主键顺序排列（InnoDB 引擎）。
- 关系总结：
- 主键是索引的一种，但索引不一定是主键。
- 一张表只能有 一个主键，但可以有多个普通索引、唯一索引等。
2. 为什么每个 InnoDB 表必须有主键？
- 聚簇索引依赖：InnoDB 的数据存储方式依赖聚簇索引，如果没有显式定义主键，MySQL 会：
- 尝试使用第一个
NOT NULL的唯一索引作为主键。 - 如果没有这样的索引，自动创建一个 隐藏的主键（6字节的递增值）。
- 尝试使用第一个
3. 示例验证
-- 创建表时不指定主键
CREATE TABLE test (
name VARCHAR(50),
age INT
);
-- MySQL 会自动创建隐藏主键（HEAP表除外）
SHOW CREATE TABLE test;
二、索引如何决定 B+ 树的组织方式
1. 不同索引类型的 B+ 树结构差异
-
聚簇索引（主键）：
- 叶子节点包含 完整数据行，数据按主键顺序物理存储。
- 示例：
B+ 树叶子节点：CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), age INT );[id=1, name=Alice, age=25] [id=2, name=Bob, age=30] [id=3, name=Charlie, age=22]
-
非聚簇索引（普通索引）：
- 叶子节点包含 索引键 + 主键值，需通过主键回表查询完整数据。
- 示例：
B+ 树叶子节点：CREATE INDEX idx_name ON users (name);[name=Alice, id=1] [name=Bob, id=2] [name=Charlie, id=3]
2. 复合索引的 B+ 树组织
- 索引键排序规则：按定义的列顺序排序。
- 示例：
B+ 树叶子节点：CREATE INDEX idx_user_age ON users (user_id, age);[(user_id=1, age=25), id=1] [(user_id=1, age=30), id=2] [(user_id=2, age=22), id=3] - 查询
WHERE user_id = 1 AND age > 20可利用该索引快速定位。
- 示例：
三、索引与查询优化的实践
1. 覆盖索引（Covering Index）
- 定义：查询的字段完全包含在索引中，无需回表。
- 示例：
-- 创建覆盖索引 CREATE INDEX idx_name_email ON users (name, email); -- 查询直接使用索引返回结果 SELECT name, email FROM users WHERE name = 'Alice';
- 示例：
2. EXPLAIN 验证索引使用
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
- 输出关键列：
列名 含义 key实际使用的索引 Extra是否使用覆盖索引（ Using index）
四、总结：索引与主键的核心关系
-
主键是聚簇索引的基础：
- InnoDB 强制要求表有主键，数据按主键聚簇存储。
- 主键的选择直接影响数据的物理存储顺序。
-
索引决定查询效率：
- 合理的索引（如复合索引、覆盖索引）可减少磁盘IO，避免回表。
-
B+ 树是索引的实现基础：
- 所有索引（主键、普通索引）均以 B+ 树结构组织，确保高效查询。
建议实践：
- 通过
SHOW INDEX FROM table_name;查看表的索引结构。 - 使用
EXPLAIN分析查询执行计划，优化索引设计。
浙公网安备 33010602011771号