MySQL 索引优化实战

数据库性能优化的关键在于索引设计。本文通过实际案例讲解 MySQL 索引优化策略。

索引基础

什么是索引

索引类似于书籍的目录，是帮助 MySQL 高效获取数据的数据结构。没有索引，数据库需要扫描全表（Full Table Scan）才能找到所需记录。

索引类型

-- 主键索引（聚簇索引）
ALTER TABLE articles ADD PRIMARY KEY (id);

-- 唯一索引
CREATE UNIQUE INDEX uk_slug ON articles(slug);

-- 普通索引
CREATE INDEX idx_status ON articles(status);

-- 联合索引
CREATE INDEX idx_status_published ON articles(status, published_at);

-- 前缀索引（对长字符串）
CREATE INDEX idx_title_prefix ON articles(title(20));

索引设计原则

1. 选择性高的列优先

索引的选择性 = 不重复的值 / 总行数。选择性越高，索引效果越好：

-- 性别字段选择性低（约0.5），不适合单独建索引
-- 用户名字段选择性高，适合建索引
CREATE UNIQUE INDEX uk_username ON users(username);

2. 联合索引与最左前缀

-- 联合索引 (status, published_at, category)
CREATE INDEX idx_composite ON articles(status, published_at, category);

-- ✅ 能用到索引
WHERE status = 1
WHERE status = 1 AND published_at > '2024-01-01'
WHERE status = 1 AND published_at > '2024-01-01' AND category = 'php'

-- ❌ 不能用到索引（违反最左前缀）
WHERE published_at > '2024-01-01'
WHERE category = 'php'

-- ⚠️ 部分使用（只能用到 status 部分）
WHERE status = 1 AND category = 'php'

3. 覆盖索引

如果查询的列都在索引中，无需回表查询：

-- 索引包含 (status, published_at, id)
-- id 是主键，自动包含在二级索引中
SELECT id, status, published_at FROM articles WHERE status = 1;

-- 使用 EXPLAIN 验证
EXPLAIN SELECT id, title FROM articles WHERE status = 1;
-- Extra 列显示 "Using index" 表示覆盖索引

实战案例分析

案例：博客文章列表查询

-- 查询：获取已发布的文章列表，按发布时间倒序
SELECT * FROM articles 
WHERE status = 1 
ORDER BY published_at DESC 
LIMIT 10;

优化前（只有 status 索引）：

• Using where; Using filesort（需要额外排序）

优化后（联合索引）：

CREATE INDEX idx_status_pub ON articles(status, published_at);
-- Using where; Backward index scan（直接利用索引顺序）

案例：标签文章查询

-- 查询：获取某标签下的文章
SELECT a.* FROM articles a
INNER JOIN article_tag at ON a.id = at.article_id
WHERE at.tag_id = 5
ORDER BY a.published_at DESC
LIMIT 10;

优化：

-- article_tag 表的联合索引
CREATE UNIQUE INDEX uk_article_tag ON article_tag(article_id, tag_id);
CREATE INDEX idx_tag_article ON article_tag(tag_id, article_id);

EXPLAIN 详解

使用 EXPLAIN 分析查询计划：

EXPLAIN SELECT * FROM articles WHERE status = 1 AND slug = 'hello';

关键字段解读：

常见误区

1. 索引不是越多越好

-- ❌ 过多索引影响写入性能
CREATE INDEX idx_1 ON articles(title);
CREATE INDEX idx_2 ON articles(excerpt);
CREATE INDEX idx idx_3 ON articles(cover);
-- 每次插入/更新都要维护所有索引

-- ✅ 合理使用联合索引
CREATE INDEX idx_list ON articles(status, published_at);

2. 避免索引失效

-- ❌ 函数操作导致索引失效
WHERE YEAR(published_at) = 2024
-- 改为范围查询
WHERE published_at >= '2024-01-01' AND published_at < '2025-01-01'

-- ❌ 隐式类型转换
WHERE phone = 13800138000  -- phone 是 varchar
-- 改为
WHERE phone = '13800138000'

-- ❌ LIKE 以通配符开头
WHERE title LIKE '%MySQL%'
-- 如需全文搜索，使用 FULLTEXT 索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_title(title);

3. 合理使用前缀索引

-- 长文本字段不需要完整索引
-- 先计算合适的前缀长度
SELECT 
  COUNT(DISTINCT LEFT(title, 10)) / COUNT(*) AS sel_10,
  COUNT(DISTINCT LEFT(title, 15)) / COUNT(*) AS sel_15,
  COUNT(DISTINCT LEFT(title, 20)) / COUNT(*) AS sel_20
FROM articles;

-- 选择选择性接近完整列的前缀长度
CREATE INDEX idx_title ON articles(title(15));

维护建议

-- 查看索引使用情况
SELECT 
  OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
  COUNT_READ, COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_db'
ORDER BY COUNT_READ DESC;

-- 删除未使用的索引
ALTER TABLE articles DROP INDEX idx_unused;

-- 定期分析表
ANALYZE TABLE articles;

-- 重建碎片化的索引
ALTER TABLE articles ENGINE=InnoDB;

总结

索引优化是数据库性能调优的核心。记住几个要点：选择高选择性列建索引、善用联合索引和覆盖索引、定期用 EXPLAIN 分析慢查询、避免常见的索引失效场景。优化是一个持续的过程，需要根据实际查询模式不断调整。