开篇引入
"这个查询太慢了，给我加个索引！"
很多人以为索引是万能的，加个索引就万事大吉。实际上，索引设计是一门艺术，选错列、选错顺序、选错类型，都会让索引成为负担。
《高性能MySQL》第7章讲的高性能索引策略，这篇文章帮你把索引设计做对。
索引设计原则
原则1：只为查询条件创建索引
-- 这个查询需要索引
SELECT * FROM orders WHERE user_id = 1;
-- 需要在user_id列建索引
CREATE INDEX idx_user_id ON orders(user_id);
-- 这个查询不需要额外的索引
SELECT COUNT(*) FROM orders; -- COUNT(*)有优化，不需要索引
原则2：选择性高的列放前面
-- 选择性 = 不同值数量 / 总行数
-- 越高越好，唯一索引选择性=1
-- 用户ID（唯一）vs 状态（只有几种）
SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM orders; -- ≈ 1
SELECT COUNT(DISTINCT status) / COUNT(*) FROM orders; -- ≈ 0.001
-- 索引：(user_id, status) 比 (status, user_id) 好
-- 因为user_id选择性高，过滤更多数据
原则3：考虑查询的WHERE子句顺序
-- 查询
SELECT * FROM orders
WHERE user_id = 1 AND status = 'paid';
-- 如果这个查询最频繁：
CREATE INDEX idx_user_status ON orders(user_id, status);
-- user_id在最前面，能高效过滤
-- 如果经常这样查：
SELECT * FROM orders
WHERE status = 'paid' AND created_at > '2024-01-01';
CREATE INDEX idx_status_created ON orders(status, created_at);
-- status在最前面
联合索引设计
什么时候用联合索引
-- 场景1：一个查询需要匹配多个列
SELECT * FROM orders
WHERE user_id = 1 AND status = 'paid';
-- 联合索引一个索引顶多个单列索引
CREATE INDEX idx_user_status ON orders(user_id, status);
-- 场景2：避免回表
SELECT user_id, status, created_at
FROM orders
WHERE user_id = 1 AND status = 'paid';
-- 如果这些列都在索引中，不需要回表
CREATE INDEX idx_covering ON orders(user_id, status, created_at);
联合索引的列顺序
经验法则：选择性高的列放前面，但也要考虑查询模式。
-- 查询1：按user_id过滤，按status过滤，按created_at排序
SELECT * FROM orders
WHERE user_id = 1 AND status = 'paid'
ORDER BY created_at DESC;
-- 正确顺序：user_id > status > created_at
CREATE INDEX idx_1 ON orders(user_id, status, created_at);
-- 查询2：按status过滤，按user_id排序
SELECT * FROM orders
WHERE status = 'paid'
ORDER BY user_id;
-- 正确顺序：status > user_id
CREATE INDEX idx_2 ON orders(status, user_id);
常见错误：过度创建单列索引
-- 错误做法：为每个列建单列索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);
-- 查询 WHERE user_id = 1 AND status = 'paid'
-- MySQL可能只用一个索引，另一个条件要过滤
-- 因为两个单列索引无法协同工作
-- 正确做法：建联合索引
CREATE INDEX idx_user_status ON orders(user_id, status);
前缀索引
什么时候用前缀索引
-- 适合场景：
-- 1. 字符串很长，全列索引太大
-- 2. 前缀有足够的选择性
-- 例如：存储URL的表
CREATE TABLE urls (
id INT PRIMARY KEY,
url VARCHAR(500) NOT NULL
);
-- url列很长，建全文索引浪费空间
-- 用前缀索引
CREATE INDEX idx_url ON urls(url(50));
如何确定前缀长度
-- 计算完整列的选择性
SELECT
COUNT(DISTINCT url) / COUNT(*) AS full_selectivity
FROM urls;
-- 计算不同前缀长度的选择性
SELECT
COUNT(DISTINCT LEFT(url, 10)) / COUNT(*) AS selectivity_10,
COUNT(DISTINCT LEFT(url, 20)) / COUNT(*) AS selectivity_20,
COUNT(DISTINCT LEFT(url, 30)) / COUNT(*) AS selectivity_30,
COUNT(DISTINCT LEFT(url, 50)) / COUNT(*) AS selectivity_50,
COUNT(DISTINCT LEFT(url, 100)) / COUNT(*) AS selectivity_100
FROM urls;
-- 选择性接近完整列的前缀长度就是合适的
-- 找到那个"边际效益递减"的点
前缀索引的缺点
-- 1. 无法用于ORDER BY和GROUP BY
SELECT * FROM urls ORDER BY url; -- 无法使用前缀索引
-- 2. 无法用于覆盖索引
SELECT url FROM urls WHERE url LIKE 'https://example.com%';
-- 需要回表取完整url
-- 3. 无法做范围查询
SELECT * FROM urls WHERE url > 'https://aaa'; -- 无法使用
覆盖索引
什么是覆盖索引
-- 如果查询所需的所有列都在索引中
-- MySQL不需要回表，直接从索引返回结果
-- 这叫"覆盖索引"（Covering Index）
覆盖索引示例
-- 普通查询：需要回表
SELECT name, email FROM users WHERE name = 'Alice';
-- name列索引中没有email，需要回表查聚簇索引
-- 覆盖索引：不需要回表
CREATE INDEX idx_name_email ON users(name, email);
SELECT name, email FROM users WHERE name = 'Alice';
-- name和email都在索引中，直接返回
EXPLAIN验证覆盖索引
EXPLAIN SELECT name, email FROM users WHERE name = 'Alice';
-- 输出中的Extra列：
-- Using index ← 使用了覆盖索引！
-- 如果没有Using index，说明在回表
索引设计实战
场景1：用户中心
-- 用户表查询模式：
-- 1. 按手机号登录
-- 2. 按邮箱登录
-- 3. 按用户名搜索
-- 4. 按注册时间排序
-- 索引设计：
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
phone VARCHAR(20),
created_at DATETIME,
UNIQUE KEY uk_email (email),
UNIQUE KEY uk_phone (phone),
KEY idx_username (username),
KEY idx_created_at (created_at)
);
-- 为什么不把所有列做成联合索引？
-- 因为查询模式不同，无法一个联合索引覆盖所有
场景2：订单中心
-- 订单表查询模式：
-- 1. 按订单号查询（唯一）
-- 2. 按用户ID查订单列表（分页）
-- 3. 按状态查待处理订单
-- 4. 按创建时间查最近订单
CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL UNIQUE,
user_id BIGINT NOT NULL,
status TINYINT NOT NULL DEFAULT 0,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME,
-- 联合索引：用户查自己的订单（最常见）
KEY idx_user_created (user_id, created_at DESC),
-- 联合索引：按状态筛选（结合时间排序）
KEY idx_status_created (status, created_at DESC)
);
-- 查询1：查用户订单
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 10;
-- 命中 idx_user_created
-- 查询2：查待处理订单（最新）
SELECT * FROM orders WHERE status = 0 ORDER BY created_at DESC LIMIT 20;
-- 命中 idx_status_created
场景3：商品搜索
-- 商品表查询模式：
-- 1. 按分类查商品
-- 2. 按品牌筛选
-- 3. 按价格区间筛选
-- 4. 按销量排序
CREATE TABLE products (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
category_id INT NOT NULL,
brand_id INT,
name VARCHAR(200) NOT NULL,
price DECIMAL(10,2) NOT NULL,
sales_count INT DEFAULT 0,
KEY idx_category (category_id),
KEY idx_brand (brand_id),
KEY idx_price (price),
KEY idx_sales (sales_count DESC)
);
-- 为什么不建(category_id, brand_id, price)联合索引？
-- 因为范围查询（price）会阻断后续列
-- 单独建索引更灵活
索引设计checklist
-- 建索引前检查：
-- 1. 查询是否高频？
-- 2. 该查询有哪些过滤条件？
-- 3. 查询结果的排序方式？
-- 4. 是否需要回表？
-- 5. 选择性如何？
-- 不要建的索引：
-- 1. 选择性太低的列（如：性别、状态）
-- 2. 更新频繁的列（维护成本高）
-- 3. 很少使用的查询条件的列
使用EXPLAIN验证索引
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- 关键列：
-- type: ALL=全表扫描, ref=索引查找, range=范围扫描
-- key: 实际使用的索引
-- rows: 预计扫描行数
-- Extra: Using index=覆盖索引
-- 检查：
-- 1. type是否为ALL（全表扫描）？
-- 2. rows是否太大（应该几十几百，不应该几万）？
-- 3. 是否Using filesort（需要排序）？
索引与查询优化器
优化器如何选择索引
-- MySQL优化器基于成本选择索引
-- 成本因素：
-- 1. 索引统计信息
-- 2. 预估扫描行数
-- 3. 磁盘IO成本
-- 可以强制使用索引
SELECT * FROM orders FORCE INDEX (idx_user_created)
WHERE user_id = 1;
索引统计信息
-- 统计信息不准确会导致错误的选择
-- 更新统计信息
ANALYZE TABLE orders;
-- InnoDB统计信息抽样
SHOW INDEX FROM orders;
-- Cardinality列显示索引基数
-- 如果统计信息不准，ANALYZE TABLE修复
小结
- 选择性高的列优先放索引前面：过滤更多数据
- 联合索引优于多个单列索引：查询条件协同工作
- 范围查询会阻断后续列：设计索引顺序时考虑
- 前缀索引节省空间：但有局限性
- 覆盖索引最快：减少回表操作
- 分析查询模式：根据实际查询设计索引
- 避免过度索引：每个索引都有维护成本
- 用EXPLAIN验证：确认索引被正确使用
索引设计的核心是：让查询以最小的代价找到需要的数据。
延伸阅读
- 《高性能MySQL》第7章 创建高性能的索引
- MySQL 8.0 Reference Manual: Optimization and Indexes
- EXPLAIN Output Format: dev.mysql.com/doc/refman/…
