分类：数据库 | 标签：MySQL, 索引，性能优化，SQL

一、索引基础原理

1.1 为什么需要索引

索引是数据库性能优化的核心。没有索引的查询需要全表扫描，时间复杂度为 O(n)。使用 B+ 树索引后，查询复杂度降为 O(log n)，大幅提升查询效率。

1.2 索引类型详解

• 主键索引（PRIMARY KEY）：唯一标识每行，自动创建
• 唯一索引（UNIQUE）：保证列值唯一
• 普通索引（INDEX）：加速查询
• 联合索引（COMPOSITE）：多列组合索引
• 覆盖索引（COVERING）：查询只需索引数据

1.3 索引创建原则

• 高频查询字段优先创建索引
• 高选择性字段（唯一值多）更适合索引
• 避免过度索引（影响写入性能）

二、WordPress 数据库索引优化

2.1 wp_posts 表优化

-- 查看现有索引
SHOW INDEX FROM wp_posts;

-- 添加状态 + 类型联合索引
ALTER TABLE wp_posts 
ADD INDEX idx_status_type (post_status, post_type);

-- 添加日期索引
ALTER TABLE wp_posts 
ADD INDEX idx_date (post_date);

2.2 wp_postmeta 表优化

postmeta 表是 WordPress 性能瓶颈，需要重点优化：

-- 添加 meta_key 索引
ALTER TABLE wp_postmeta 
ADD INDEX idx_meta_key (meta_key);

-- 添加联合索引
ALTER TABLE wp_postmeta 
ADD INDEX idx_key_value (meta_key, meta_value(100));

-- 添加 post_id + meta_key 联合索引
ALTER TABLE wp_postmeta 
ADD INDEX idx_post_key (post_id, meta_key);

2.3 wp_options 表优化

-- 检查 autoload 选项
SELECT option_name FROM wp_options 
WHERE autoload = 'yes';

-- 清理不必要的 autoload
UPDATE wp_options 
SET autoload = 'no' 
WHERE option_name IN ('不需要的选项');

三、EXPLAIN 详解

3.1 EXPLAIN 输出字段解读

EXPLAIN SELECT * FROM wp_posts 
WHERE post_status = 'publish' 
AND post_type = 'post' 
ORDER BY post_date DESC 
LIMIT 10;

3.2 type 字段详解

system > const > eq_ref > ref > range > index > ALL

- system: 只有一行（系统表）
- const: 主键/唯一索引等值查询
- eq_ref: 主键/唯一索引连接
- ref: 非唯一索引查询
- range: 索引范围扫描
- index: 全索引扫描
- ALL: 全表扫描（最差）

3.3 Extra 字段关键信息

• Using index：覆盖索引（好）✅
• Using where：正常过滤
• Using temporary：临时表（需优化）⚠️
• Using filesort：文件排序（需优化）⚠️

四、慢查询优化实战

4.1 开启慢查询日志

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

4.2 分析慢查询日志

# 使用 mysqldumpslow
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# 使用 pt-query-digest
pt-query-digest /var/log/mysql/mysql-slow.log

4.3 典型慢查询优化案例

案例 1：文章列表查询慢

-- 优化前（3.2s）
SELECT * FROM wp_posts 
WHERE post_status = 'publish' 
ORDER BY post_date DESC;

-- 添加索引
ALTER TABLE wp_posts 
ADD INDEX idx_status_date (post_status, post_date);

-- 优化后（0.05s）

案例 2：meta 查询慢

-- 优化前（5.8s）
SELECT p.* FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE pm.meta_key = 'post_views_count';

-- 添加索引
ALTER TABLE wp_postmeta 
ADD INDEX idx_meta_key (meta_key);

-- 优化后（0.12s）

五、联合索引最佳实践

5.1 最左前缀原则

-- 联合索引 (a, b, c)

-- ✅ 使用索引
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1

-- ❌ 不使用索引
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

5.2 索引列顺序选择

原则：选择性高的列在前

-- 假设 post_status 只有 3 个值，post_type 有 10 个值
-- 更好的索引顺序：
ALTER TABLE wp_posts 
ADD INDEX idx_type_status (post_type, post_status);

5.3 覆盖索引优化

-- 创建覆盖索引
ALTER TABLE wp_posts 
ADD INDEX idx_status_date_id (post_status, post_date, ID);

-- 查询只使用索引
SELECT ID, post_date FROM wp_posts 
WHERE post_status = 'publish' 
ORDER BY post_date DESC;

六、索引维护策略

6.1 索引碎片清理

-- 检查表碎片率
SELECT table_name, 
       data_free / (data_length + data_free) AS fragment_rate
FROM information_schema.tables 
WHERE table_schema = 'wordpress';

-- 优化表（清理碎片）
OPTIMIZE TABLE wp_posts;
OPTIMIZE TABLE wp_postmeta;

6.2 索引使用监控

-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'wordpress';

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes 
WHERE object_schema = 'wordpress';

七、性能基准测试

7.1 测试环境

MySQL 8.0
WordPress 6.4
文章数：1000 篇
meta 记录：50000 条

7.2 优化前后对比

八、常见问题与解决方案

8.1 索引不生效

• 检查数据类型匹配
• 避免函数操作索引列
• 检查字符集一致性

8.2 索引失效场景

-- ❌ 对索引列使用函数
WHERE DATE(post_date) = '2026-04-07'

-- ✅ 改用范围查询
WHERE post_date >= '2026-04-07' 
AND post_date < '2026-04-08'

-- ❌ 模糊查询前缀通配符
WHERE post_title LIKE '%关键词%'

-- ✅ 使用全文索引
WHERE MATCH(post_title) AGAINST('关键词')

九、总结与检查清单

索引优化检查清单

□ 主键索引存在
□ 高频查询字段有索引
□ 联合索引顺序合理
□ 避免重复索引
□ 定期清理未使用索引
□ 监控慢查询日志
□ 定期 OPTIMIZE TABLE

持续优化建议

• 每周分析慢查询日志
• 每月检查索引使用情况
• 每季度清理冗余索引
• 持续监控查询性能

参考资源：

• MySQL 官方文档：索引优化
• Percona Toolkit 工具集
• sysbench 性能测试工具