第二十二章 索引原理与实战
本章目标
通过本章学习，你将能够：
- 理解 B+Tree 索引的内部结构和为什么它能大幅提升查询效率
- 掌握聚簇索引（Clustered Index）和二级索引（Secondary Index）的区别和协作方式
- 深入理解回表（回表查询）的概念和对性能的影响
- 掌握覆盖索引的原理及如何利用它避免回表
- 理解复合索引的"最左前缀原则"，能够正确设计复合索引
- 掌握创建、查看、删除索引的实际操作方法
- 应用索引设计的黄金法则，权衡索引的收益和成本
前置知识
在学习本章之前，你需要：
- 理解 SQL 的基本查询语句（SELECT、WHERE、ORDER BY、GROUP BY、JOIN）
- 了解基本的磁盘 I/O 概念（磁盘寻道时间、顺序读取 vs 随机读取）
- 了解 InnoDB 的基本概念（页、行格式、表空间）
- 了解二叉搜索树（BST）的基本概念将有助于理解 B+Tree
- 知道主键（PRIMARY KEY）和外键（FOREIGN KEY）的作用
22.1 为什么需要索引
22.1.1 图书馆类比
想象一个藏书 100 万册的图书馆：
- 没有索引（全表扫描）：在书架间逐本翻阅，直到找到目标书籍——可能需要检查所有 100 万册。
- 有索引（使用目录）：先在目录柜中按书名查找，卡片指示"3 楼 B 区 12 排 5 层"——直接走到目标位置，只需检查几张卡片。
MySQL 中的索引就是这个"目录柜"。数据库系统维护着与数据分离的索引结构，查询时先在索引中找到目标位置，然后直接定位到数据。
22.1.2 磁盘 I/O 的代价
理解索引为什么重要，必须先理解磁盘 I/O 的代价。
典型延时数量级（相对比较）：
- L1 缓存访问： 0.5 ns （1 秒类比）
- L2 缓存访问： 7 ns （14 秒类比）
- 内存访问： 100 ns （3.3 分钟类比）
- SSD 随机读取： 150,000 ns（2.8 天类比）
- HDD 磁盘寻道： 10,000,000 ns（11.6 天类比！）一次磁盘 I/O 操作需要寻道（磁头移动）+ 旋转延迟（盘片旋转）+ 数据传输。相对于内存访问，磁盘 I/O 慢了数十万倍。索引的核心目标就是用最少的磁盘 I/O 完成查询。
全表扫描为什么不合理：一个包含 1000 万行数据的表，如果每行 100 字节，大约需要 1GB 存储空间。遍历所有数据需要大量磁盘读取，可能耗时数秒甚至数十秒。
索引扫描为什么高效：B+Tree 索引只有 3-4 层高度，即使索引亿万行数据，找到目标也只需要 3-4 次磁盘 I/O。
22.2 B+Tree 索引结构
22.2.1 从二叉搜索树到 B+Tree
二叉搜索树（BST）在数据量大时会退化为链表（如果按顺序插入），且每个节点只有 2 个子节点导致树高过大。B+Tree 通过以下优化解决了这些问题：
- 多路搜索树：每个节点可以有数百到数千个子节点（高扇出率），树高极低。
- 自平衡：插入和删除操作后自动保持平衡，不会退化。
- 所有数据在叶子节点：非叶子节点只存储键和指针，叶子节点形成有序链表。
22.2.2 B+Tree 索引的详细结构
[50 | 100] ← 根节点（非叶子）
/ | \
[20|30|40] [60|70|80|90] [120|150] ← 内部节点（非叶子）
/ | | \ ... | \
[叶子节点 - 双向链表连接] ← 叶子节点（存储完整键值+数据）
↓ ↓ ↓ ↓
(10,row) (20,row) (30,row) ...非叶子节点的特点：
- 只存储索引键值和指向子节点的页号（指针）
- 不存储实际的行数据
- 目的是快速导航到目标叶子节点
叶子节点的特点：
- 存储完整的索引键值
- 对于聚簇索引：叶子节点直接包含完整的行数据
- 对于二级索引：叶子节点包含索引键值 + 主键值
- 所有叶子节点通过双向链表连接（支持高效的范围扫描和 ORDER BY）
22.2.3 为什么 B+Tree 高度极低 —— 具体计算
以 InnoDB 默认页大小 16KB 为例：
页大小：16,384 字节
每个索引条目：主键（BIGINT = 8 字节）+ 指针（6 字节）= 14 字节
每页可存储条目数：16,384 / 14 ≈ 1,170
非叶子节点每页约 1,170 个键值 + 指针
树高度计算：
- 高度 2：1,170 个叶子页 → 可索引约 1,170 * N 行数据
- 高度 3：1,170 × 1,170 个叶子页 → 可索引约 1,368,900 * N 行数据
- 高度 4：1,170^3 个叶子页 → 可索引约 1,601,613,000 * N 行数据
其中 N 是每个叶子页能存储的行数（取决于行大小）
以每行 200 字节为例，每页约存储 80 行：
- 高度 3 可索引约 1 亿行
- 高度 4 可索引约 1280 亿行
结论：即使数据量极大，B+Tree 也只需要 3-4 层！22.2.4 为什么范围查询高效
叶子节点的双向链表结构是 B+Tree 范围查询高效的关键：
sql
-- 范围查询示例
SELECT * FROM orders WHERE id BETWEEN 1000 AND 2000;
-- B+Tree 执行过程：
-- 1. 从根节点开始，通过二分查找定位到 id=1000 所在的叶子页（3-4 次 I/O）
-- 2. 在叶子页中找到 id=1000 的第一个记录
-- 3. 沿着叶子节点的双向链表向右遍历，顺序读取直到 id > 2000
-- 4. 步骤 3 中，页面之间通过双向链表连接，都是顺序读取！顺序读取远快于随机读取，因为磁盘的连续扇区可以一次读取，无需额外的寻道时间。
22.3 聚簇索引（Clustered Index）
22.3.1 聚簇索引的概念
在 InnoDB 中，聚簇索引是一种特殊的索引——数据本身按照主键的顺序物理存储。换句话说，主键就是数据。
sql
-- 创建一个使用 InnoDB 的表
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY, -- 这就是聚簇索引
name VARCHAR(100),
email VARCHAR(200),
created_at DATETIME
) ENGINE=InnoDB;
-- 数据行按照 id 的顺序存储在聚簇索引的叶子页中
-- 通过 id 查询数据是最快的，因为叶子页直接包含完整行数据22.3.2 聚簇索引的存储结构
聚簇索引的叶子页内容：
┌─────────────────────────────────────────────────────┐
│ 页头 │ [id=1, name, email, ...] [id=2, name, ...] ... │ 页尾 │
│ │ [id=3, name, email, ...] [id=4, name, ...] ... │ │
└─────────────────────────────────────────────────────┘
# 每行数据都在这里，按 id 排序
# 通过 id 查找 → 直接在叶子页中找到完整数据，无需额外查找22.3.3 AUTO_INCREMENT 主键的重要性
sql
-- AUTO_INCREMENT 主键的优点：
-- 1. 新数据总是追加在末尾，不会引起页分裂（Page Split）
-- 2. 页填充率接近 100%，磁盘空间利用率高
-- 3. 写入性能最好（不需要在中间插入）
-- 随机主键（如 UUID）的问题：
-- 1. 新数据的 id 可能比已有数据小，需要插入到已有页的中间
-- 2. 如果目标页没有足够空间，会发生页分裂：
-- 原页 50% 数据 → 留在原页
-- 原页 50% 数据 → 移动到新分配的页
-- 新插入的数据 → 插入到合适位置
-- 3. 页分裂产生磁盘碎片，写性能下降
-- 4. 页填充率约 65-75%，磁盘空间利用率低
-- 对比验证
CREATE TABLE test_auto (
id INT AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(500)
);
CREATE TABLE test_uuid (
id CHAR(36) PRIMARY KEY, -- 使用 UUID
data VARCHAR(500)
);
-- 插入同样数量的数据，test_uuid 的磁盘空间和插入耗时
-- 通常显著高于 test_auto22.4 二级索引（Secondary Index）
22.4.1 二级索引的结构
除聚簇索引之外的所有其他索引都是二级索引。二级索引的叶子节点不存储完整行数据，而是存储索引键值 + 对应行的主键值。
sql
-- 创建一个二级索引
CREATE INDEX idx_name ON users(name);
-- idx_name 的 B+Tree 结构（简化）：
-- 非叶子节点：存储 name 的键值范围 + 子节点指针
-- 叶子节点：存储 (name, id) 的有序列表，按 name 排序
-- 比如：("Alice", 5), ("Bob", 1), ("Bob", 10), ("Charlie", 3)22.4.2 回表（二次查询 / Double Lookup）
当查询通过二级索引找到目标行后，如果需要索引中不包含的列，就必须用主键值回到聚簇索引中查找完整行。这个过程称为"回表"。
sql
-- 假设有以下索引
CREATE INDEX idx_email ON users(email);
-- 查询仅涉及索引列和主键（不需要回表）
SELECT id, email FROM users WHERE email = 'alice@example.com';
-- 执行过程：
-- 1. 在 idx_email 中定位 email='alice@example.com'
-- 2. 叶子节点包含 (email, id)，直接返回 → 完成！
-- 查询涉及非索引列（需要回表）
SELECT id, email, name, phone FROM users WHERE email = 'alice@example.com';
-- 执行过程：
-- 1. 在 idx_email 中定位 email='alice@example.com' → 获取 id=5
-- 2. 用 id=5 回到聚簇索引中查找完整行数据 → 获取 name, phone
-- 3. 返回结果
-- 步骤 2 就是"回表"，每次都涉及随机 I/O！
-- 如果查询返回 1000 行，就需要 1000 次回表（1000 次随机磁盘读取）
-- 这就是为什么"大量回表"会成为性能杀手22.4.3 覆盖索引（Covering Index）—— 避免回表
覆盖索引是指：查询所需的所有列都包含在索引中，不需要回表获取数据。
sql
-- 假设频繁查询用户邮箱和姓名
-- 常见查询模式：
SELECT email, name FROM users WHERE email LIKE 'alice%';
-- 创建覆盖索引（包含 WHERE + SELECT 的所有列）
CREATE INDEX idx_email_name ON users(email, name);
-- 叶子节点存储：(email, name, id)
-- 查询只用到 email 和 name，完全在索引内完成，不需要回表！
-- EXPLAIN 验证
EXPLAIN SELECT email, name FROM users WHERE email LIKE 'alice%';
-- Extra 中显示 "Using index" → 覆盖索引，性能最优
-- 对比非覆盖索引
EXPLAIN SELECT email, name, phone FROM users WHERE email LIKE 'alice%';
-- Extra 中显示 NULL（无 "Using index"）→ 需要回表覆盖索引不是一种新的索引类型——它指的是索引"覆盖"了查询需求的状态。一个精心设计的复合索引可以达到覆盖索引的效果。
22.5 索引类型
22.5.1 普通索引（INDEX / KEY）
最基本的索引类型，不对数据做唯一性约束。
sql
-- 创建表时指定
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200),
category_id INT,
INDEX idx_category (category_id),
INDEX idx_name (name)
);
-- 通过 CREATE INDEX 创建
CREATE INDEX idx_name ON products(name);
-- 通过 ALTER TABLE 添加
ALTER TABLE products ADD INDEX idx_category (category_id);22.5.2 唯一索引（UNIQUE INDEX）
在普通索引的基础上增加唯一性约束。允许 NULL 值，且允许多个 NULL 值（因为 NULL != NULL）。
sql
-- 创建表时指定
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(200) UNIQUE, -- 简写
phone VARCHAR(20),
UNIQUE INDEX idx_phone (phone) -- 显式指定
);
-- CREATE UNIQUE INDEX
CREATE UNIQUE INDEX idx_email ON users(email);
-- 允许插入多个 NULL（MySQL 中 NULL 不被视为相等）
INSERT INTO users (email) VALUES (NULL), (NULL); -- 成功！22.5.3 前缀索引（Prefix Index）
只索引列值的前 N 个字符。适用于 TEXT/BLOB 或很长的 VARCHAR 列，可以大幅节省索引空间。
sql
-- 创建前缀索引：只索引前 20 个字符
CREATE INDEX idx_content_prefix ON articles(content(20));
-- 优点：节省索引空间（索引大小从几百字节降到几十字节）
-- 缺点：
-- 1. 无法用于 ORDER BY（因为索引不完整）
-- 2. 无法用于覆盖索引
-- 3. 前缀长度选择不当会导致选择性下降
-- 如何选择合适的前缀长度：
-- 目标：让索引的选择性（不重复值的比例）接近完整列的选择性
SELECT COUNT(DISTINCT LEFT(content, 10)) / COUNT(*) AS sel_10,
COUNT(DISTINCT LEFT(content, 20)) / COUNT(*) AS sel_20,
COUNT(DISTINCT LEFT(content, 30)) / COUNT(*) AS sel_30,
COUNT(DISTINCT content) / COUNT(*) AS sel_full
FROM articles;
-- 选择使选择性足够接近 sel_full 的最小长度22.5.4 全文索引（FULLTEXT）
用于文本内容的高效搜索。InnoDB 从 MySQL 5.6 开始支持。
sql
-- 创建全文索引
CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT INDEX ft_body (body),
FULLTEXT INDEX ft_title_body (title, body)
);
-- 使用全文搜索
SELECT * FROM articles
WHERE MATCH(body) AGAINST('MySQL 索引优化' IN NATURAL LANGUAGE MODE);
-- 布尔模式（支持 + - > < ~ * "" 等操作符）
SELECT * FROM articles
WHERE MATCH(body) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);
-- 查询扩展模式（自动扩展搜索词）
SELECT * FROM articles
WHERE MATCH(body) AGAINST('database' WITH QUERY EXPANSION);22.5.5 空间索引（SPATIAL INDEX）
用于几何数据类型（POINT、LINESTRING、POLYGON 等）。
sql
CREATE TABLE locations (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
coordinates POINT NOT NULL SRID 4326,
SPATIAL INDEX sp_coordinates (coordinates)
);
-- 空间查询
SELECT name, ST_AsText(coordinates)
FROM locations
WHERE ST_Contains(
ST_GeomFromText('POLYGON((...))', 4326),
coordinates
);22.6 复合索引与最左前缀原则
22.6.1 复合索引的结构
复合索引（Composite / Multi-Column Index）是在多个列上建立的索引，列的顺序极为重要。
sql
-- 创建复合索引
CREATE INDEX idx_a_b_c ON table_name(a, b, c);
-- B+Tree 中的排序：
-- 先按 a 排序 → a 相同则按 b 排序 → b 相同则按 c 排序
-- 叶子节点内容示意：
-- (a=1, b=1, c=1, id=...)
-- (a=1, b=1, c=2, id=...)
-- (a=1, b=2, c=1, id=...)
-- (a=2, b=1, c=1, id=...)
-- (a=2, b=2, c=1, id=...)22.6.2 最左前缀原则 —— 最重要！
复合索引 INDEX(a, b, c) 可以等效于创建了三个索引：
INDEX(a, b, c)—— 搜索条件使用了 a, b, c 三列INDEX(a, b)—— 搜索条件使用了 a, b 两列INDEX(a)—— 搜索条件使用了 a 一列
不能等效于：
INDEX(b)—— 搜索条件只使用 b（缺少最左列 a）INDEX(c)—— 搜索条件只使用 c（缺少最左列 a）INDEX(b, c)—— 搜索条件只使用 b, c（缺少最左列 a）
sql
-- 创建索引
CREATE INDEX idx_name_age_city ON users(name, age, city);
-- 能使用索引的查询：
SELECT * FROM users WHERE name = 'Alice'; -- 使用 (name)
SELECT * FROM users WHERE name = 'Alice' AND age = 25; -- 使用 (name, age)
SELECT * FROM users WHERE name = 'Alice' AND age = 25 AND city = 'NY'; -- 使用全部
SELECT * FROM users WHERE name = 'Alice' AND city = 'NY'; -- 使用 (name), city 条件不能利用索引但 name 条件可以
-- 不能使用索引的查询：
SELECT * FROM users WHERE age = 25; -- 缺少最左列 name！
SELECT * FROM users WHERE city = 'NY'; -- 缺少最左列 name！
SELECT * FROM users WHERE age = 25 AND city = 'NY'; -- 缺少最左列 name！
-- 注意：name = 'Alice' AND age = 25 的条件顺序不影响索引使用
-- MySQL 优化器会自动调整条件顺序以匹配索引
-- 特殊情况：使用 LIKE 的范围查询
SELECT * FROM users WHERE name LIKE 'Al%' AND age = 25;
-- name 是范围查询，可以使用索引
-- age 是否能用索引？可以！（因为 name 使用 LIKE 'Al%' 仍是前缀匹配，不是纯范围查询）22.6.3 范围查询对复合索引的影响
sql
CREATE INDEX idx_a_b_c ON t(a, b, c);
-- 范围查询（>、<、>=、<=、BETWEEN、LIKE 'prefix%'）在某一列上会"中断"后续列的索引使用
-- 情况 1：a 的范围查询 → b 和 c 不能使用索引
SELECT * FROM t WHERE a > 10 AND b = 20 AND c = 30;
-- 索引仅用于 a 的条件（定位到 a>10 的起始位置），b 和 c 的条件需要额外过滤
-- 情况 2：a 等值 + b 范围 → c 不能使用索引
SELECT * FROM t WHERE a = 10 AND b > 20 AND c = 30;
-- 索引用于 (a, b)，c 的条件不能使用索引
-- 情况 3：a 等值 + b 等值 + c 范围 → 全部可以使用索引
SELECT * FROM t WHERE a = 10 AND b = 20 AND c > 30;
-- 索引用于 (a, b, c) 全部！因为范围在最后一列
-- 情况 4：IN 被视为等值查询，不中断后续列
SELECT * FROM t WHERE a IN (1, 2, 3) AND b = 20 AND c = 30;
-- 索引可以使用 (a, b, c) 全部！IN 被视为多个等值查询的合并
-- 实战建议：等值查询的列放在复合索引前面，范围查询的列放在最后22.7 索引管理操作
22.7.1 创建索引
sql
-- 方式 1：CREATE INDEX
CREATE INDEX idx_name ON users(name);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_name_age ON users(name, age); -- 复合索引
CREATE FULLTEXT INDEX ft_content ON articles(content);
-- 方式 2：ALTER TABLE
ALTER TABLE users ADD INDEX idx_name (name);
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);
ALTER TABLE users DROP INDEX idx_name;
-- 方式 3：CREATE TABLE 时定义
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
order_no VARCHAR(32),
created_at DATETIME,
INDEX idx_user (user_id),
INDEX idx_order_no (order_no),
INDEX idx_user_created (user_id, created_at)
);22.7.2 查看索引
sql
-- SHOW INDEX FROM table_name
SHOW INDEX FROM users;
-- 关键列说明：
-- Table: 表名
-- Non_unique: 0=唯一索引, 1=非唯一索引
-- Key_name: 索引名称（PRIMARY 表示主键索引）
-- Seq_in_index: 列在索引中的序号（1-based，复合索引的重要信息）
-- Column_name: 列名
-- Collation: A=升序, NULL=未排序
-- Cardinality: 索引中唯一值的估计数量（越高选择性越好）
-- Sub_part: 前缀索引的长度（NULL 表示索引整个列）
-- Index_type: BTREE（最常见）, FULLTEXT, HASH, RTREE
-- 查看建表语句中的索引定义
SHOW CREATE TABLE users;
-- 从 information_schema 查询索引信息
SELECT * FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'users';22.7.3 Cardinality 的意义
Cardinality 表示索引中不重复值的估计数量，越接近总行数，索引的选择性越好。
Cardinality = 1：所有行该列值相同（如都是 'active'），索引几乎无用
Cardinality ≈ 总行数：该列值几乎全部唯一（如主键），索引选择性强
注意：Cardinality 是估算值（基于采样），不是精确值。
可以通过 ANALYZE TABLE table_name; 更新统计信息。22.7.4 删除索引
sql
-- DROP INDEX
DROP INDEX idx_name ON users;
-- ALTER TABLE DROP INDEX
ALTER TABLE users DROP INDEX idx_email;
-- 注意：不能直接删除 PRIMARY KEY（需要先 ALTER TABLE ... DROP PRIMARY KEY）22.8 索引的代价
索引不是免费的，每次索引使用都有成本和收益的权衡。
22.8.1 写入性能下降
sql
-- 每次 INSERT / UPDATE / DELETE 都需要维护所有相关索引
-- 假设表上有 5 个索引：
-- 1 个 INSERT 操作需要：
-- 1 次聚簇索引插入（必选）
-- + 5 次二级索引插入
-- = 共 6 次 B+Tree 操作
-- 这就是为什么索引不是越多越好！
-- 频繁写入的表应该尽量减少索引数量22.8.2 磁盘空间占用
sql
-- 查看表（数据 + 索引）的磁盘空间
SELECT
TABLE_NAME,
ROUND((DATA_LENGTH) / 1024 / 1024, 2) AS data_mb,
ROUND((INDEX_LENGTH) / 1024 / 1024, 2) AS index_mb,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';
-- 索引大小可能接近甚至超过数据大小！
-- 例如：一张订单表有用户 ID、商品 ID、日期、状态等索引
-- 数据 2GB，索引可能 3GB22.9 索引设计的黄金法则
22.9.1 八条核心原则
1. 为 WHERE、JOIN、ORDER BY、GROUP BY 中的列创建索引
sql
-- 分析慢查询的 WHERE 和 JOIN 条件
-- 慢查询示例：
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC;
-- 建议索引：
-- orders: INDEX(status, created_at) -- 覆盖 WHERE + ORDER BY
-- orders: INDEX(user_id) -- 覆盖 JOIN（如果尚未有）
-- users: id 已有 PRIMARY KEY2. 高选择性列放在复合索引前面
sql
-- 查询：WHERE gender = 'F' AND city = 'Beijing'
-- gender 选择性低（只有 M/F 两种值）
-- city 选择性高（几百个不同城市）
-- 错误顺序：
CREATE INDEX idx_gender_city ON users(gender, city);
-- 正确顺序：
CREATE INDEX idx_city_gender ON users(city, gender);
-- 对于 WHERE gender='F' AND city='Beijing'：两种顺序都能用索引
-- 但对于 WHERE city='Beijing'（不带 gender）：只有 idx_city_gender 能用3. 不要过度索引
sql
-- 错误做法：在每个列上都创建单列索引
CREATE INDEX idx_col1 ON t(col1);
CREATE INDEX idx_col2 ON t(col2);
CREATE INDEX idx_col3 ON t(col3);
CREATE INDEX idx_col4 ON t(col4);
-- 如果查询经常是 WHERE col1=? AND col2=? AND col3=?
-- 应该创建一个复合索引而非三个单列索引
CREATE INDEX idx_col1_col2_col3 ON t(col1, col2, col3);
-- 原因：
-- 1. 复合索引可以满足查询，避免 index_merge（合并多个索引，效率不如单索引）
-- 2. 减少索引维护开销
-- 3. 复合索引可以成为覆盖索引4. 使用覆盖索引优化热点查询
sql
-- 如果有一个高频查询：
SELECT user_id, order_no, amount FROM orders WHERE status = 'pending';
-- 创建覆盖索引：
CREATE INDEX idx_status_user_order_amount ON orders(status, user_id, order_no, amount);
-- 查询完全在索引内完成，无需回表5. 监控并删除未使用的索引
sql
-- MySQL 8.0+：使用 performance_schema 查看未使用的索引
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'mydb'
AND COUNT_STAR = 0; -- 从未被使用的索引
-- MySQL 5.7：使用 sys.schema_unused_indexes
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'mydb';6. 字符串索引使用前缀索引（合适时）
sql
-- 长 VARCHAR 或 TEXT 列，只索引前缀
CREATE INDEX idx_email_prefix ON users(email(10)); -- 仅索引前 10 字符
-- 但前缀索引不能用于 ORDER BY 和覆盖索引7. 避免在索引列上使用函数或表达式
sql
-- 错误：WHERE 中在索引列上使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- created_at 上的索引不会被使用！
-- 正确：
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- 错误：对索引列进行运算
SELECT * FROM orders WHERE amount + tax > 100;
-- 正确：SELECT * FROM orders WHERE amount > 100 - tax;8. 联合唯一索引列的 NULL 问题
sql
-- UNIQUE INDEX 允许多个 NULL，但有时我们不希望这样
CREATE UNIQUE INDEX idx_user_email ON users(user_id, email);
-- 如果希望 (user_id, email) 中 email 为 NULL 时也唯一
-- 一种方案是使用默认值代替 NULL
-- 另一种是在应用层处理22.9.2 索引决策流程
分析慢查询 → 查看 EXPLAIN → type=ALL/index? → 评估是否加索引
↓
确定索引列（WHERE + JOIN + ORDER BY）
↓
选择列顺序（等值 → 范围 | 高选择性 → 低选择性）
↓
评估是否覆盖索引（SELECT 的列是否都能放入索引）
↓
创建索引 → 验证 EXPLAIN → 对比性能常见错误
认为索引越多越好
- 错误：在表上创建 10+ 个单列索引，认为能加速所有查询。
- 后果：写入性能急剧下降，磁盘空间大幅增加，优化器在多个索引间选择可能错误。
- 解决：按查询模式设计索引，不用的索引及时删除。通常一张表的索引数控制在 5 个以内。
忽视最左前缀原则
- 错误：创建
INDEX(a, b, c)后，在WHERE b=? AND c=?的查询中等效于有索引可用。 - 后果：查询不利用索引，执行全表扫描。
- 解决：设计复合索引时，确保查询条件包含最左列。使用 EXPLAIN 验证索引使用情况。
- 错误：创建
在索引列上使用函数
- 错误：
WHERE DATE(created_at) = '2024-01-01'或WHERE LEFT(name, 3) = 'abc'。 - 后果：索引失效，变为全表扫描或全索引扫描。
- 解决：将条件改写为不包裹索引列的形式：
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'。
- 错误：
主键用 UUID 或随机值
- 错误：使用 UUID 字符串作为 InnoDB 表的主键。
- 后果：随机插入导致频繁页分裂，B+Tree 页填充率低，写入性能差，磁盘碎片严重。
- 解决：使用 AUTO_INCREMENT 整数主键。如果必须用 UUID，使用有序 UUID（UUID v7）或将 UUID 存储为 BINARY(16) 并使用有序生成策略。
不做 EXPLAIN 验证就上线索引
- 错误：凭感觉创建索引，不看 EXPLAIN 的执行计划。
- 后果：可能索引未被使用（浪费），或使用了低效的索引组合。
- 解决：创建索引后，用 EXPLAIN 验证查询是否使用了预期索引。对比索引前后的 rows 和 Extra 信息。
本章练习
B+Tree 高度计算练习：假设 InnoDB 页大小 16KB，主键为 BIGINT（8 字节），指针为 6 字节。计算：非叶子节点每页可存储多少个条目？如果表有 1000 万行数据（每行 200 字节），B+Tree 需要几层？写出完整的计算过程。
复合索引最左前缀分析：对于
INDEX(a, b, c, d)，分析以下查询条件中哪些列可以利用索引（假设所有条件出现在 WHERE 子句中，且使用 AND 连接）：(1) a=1 AND b=2 AND c=3 AND d=4；(2) a=1 AND c=3；(3) b=2 AND c=3；(4) a=1 AND b>2 AND c=3 AND d=4；(5) a=1 AND b=2 AND c>3 AND d=4；(6) a IN (1,2) AND b=2 AND c=3。覆盖索引设计：有一个订单表 orders(id PK, user_id, product_id, amount, status, created_at)，高频查询为
SELECT user_id, SUM(amount) FROM orders WHERE status='paid' GROUP BY user_id。请设计一个覆盖索引，然后用 EXPLAIN 解释为什么它能够避免回表。选择性评估：有一张 100 万行的用户表，列包括 gender（'M'/'F'）、city（200 个不同值）、email（接近唯一）。为
WHERE gender='F' AND city='Beijing'设计索引，列出两种复合索引顺序，分别分析它们的 Cardinality 估算和适用场景。索引优化实践：给定一个慢查询和执行计划（type=ALL），分析为什么全表扫描，如何添加索引将其优化为 range 或 ref，重新 EXPLAIN 对比前后差异（rows、key、Extra 等）。
前缀索引前缀长度选择：有一张表包含 url 列（VARCHAR(2083)），100 万行。编写 SQL 计算不同前缀长度（10、15、20、25、30）的选择性，确定最优前缀长度。