MySQL 性能调优：索引、EXPLAIN 与 InnoDB

MySQL 性能根因

缺少索引、索引顺序错误或查询绕过索引。

EXPLAIN 分析

EXPLAIN SELECT u.name, COUNT(o.id) cnt
FROM users u LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id ORDER BY cnt DESC;

-- type: ALL = 差。ref, eq_ref, const = 好
-- key: NULL = 未使用索引
-- Extra: "Using filesort", "Using temporary" = 警告

索引策略

-- 复合索引：选择性高的列在前，范围/排序列在后
CREATE INDEX idx_orders ON orders(user_id, created_at DESC);

-- 覆盖索引：所有列都在索引中（避免回表）
CREATE INDEX idx_products ON products(category_id, price, name, id);

-- 部分索引：仅针对部分行
CREATE INDEX idx_active ON users(email) WHERE status = 'active';

应避免的反模式

-- 1. 对索引列使用函数 → 无法使用索引
-- 差：  WHERE YEAR(created_at) = 2025
-- 好：  WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'

-- 2. 大 OFFSET → 使用键集分页
-- 差：  LIMIT 20 OFFSET 10000
-- 好：  WHERE id < :lastId ORDER BY id DESC LIMIT 20

InnoDB 配置

[mysqld]
innodb_buffer_pool_size = 12G  # 内存的 70-80%
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G

-> 使用 JSON 查看器 格式化查询结果。