MySQL 8.x 性能优化：索引、查询缓存与 InnoDB 调优

MySQL 8.x 性能优化

MySQL 为数百万应用提供动力。以下是如何从中榨取最大性能。

索引策略

-- 覆盖索引：所需所有列都在索引中
CREATE INDEX idx_orders_cover ON orders (user_id, status, created_at, total);
-- SELECT user_id, status, total FROM orders WHERE user_id=1 AND status='paid'
-- 完全不触及主表！

-- 不可见索引：删除前测试影响
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;
-- 运行查询，检查是否变慢
ALTER TABLE users ALTER INDEX idx_email VISIBLE;

-- 降序索引（MySQL 8+）
CREATE INDEX idx_posts_recent ON posts (created_at DESC, id DESC);
-- 高效用于：ORDER BY created_at DESC, id DESC LIMIT 20

-- 函数索引（MySQL 8.0.13+）
CREATE INDEX idx_lower_email ON users ((LOWER(email)));
-- 支持：WHERE LOWER(email) = 'user@example.com'

EXPLAIN 分析

-- 使用 EXPLAIN FORMAT=TREE 提高可读性（MySQL 8+）
EXPLAIN FORMAT=TREE
SELECT u.name, COUNT(o.id) as orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.active = 1
GROUP BY u.id;

-- 检查：
-- "Using filesort" → 添加覆盖索引，ORDER BY 列放在最后
-- "Using temporary" → GROUP BY 需要排序
-- "Using index" → 好！覆盖索引命中
-- type = "ALL" → 全表扫描
-- rows 估计值 vs 实际值

-- 优化器提示（当规划器做出错误选择时）
SELECT /*+ INDEX(u idx_users_active) */ *
FROM users u
WHERE u.active = 1 AND u.created_at > '2026-01-01';

InnoDB 配置

# my.cnf
[mysqld]
# 缓冲池：可用 RAM 的 70-80%
innodb_buffer_pool_size = 12G

# 多个缓冲池实例（用于高并发）
innodb_buffer_pool_instances = 8  # 每 GB 一个

# I/O 调优
innodb_io_capacity = 2000        # 磁盘能处理的 IOPS
innodb_io_capacity_max = 4000
innodb_flush_log_at_trx_commit = 2  # 1=最安全，2=更快（1秒数据丢失风险）
innodb_flush_method = O_DIRECT

# 重做日志大小（越大 = 检查点越少）
innodb_redo_log_capacity = 4G   # MySQL 8.0.30+

# 连接设置
max_connections = 500
thread_cache_size = 50

常见慢查询模式

-- 坏：对索引列使用函数会破坏索引
-- 坏：
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- 好：
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- 坏：LIKE 前导通配符
-- 坏：
SELECT * FROM products WHERE name LIKE '%wireless%';
-- 好：使用 FULLTEXT 索引
ALTER TABLE products ADD FULLTEXT(name, description);
SELECT * FROM products WHERE MATCH(name, description) AGAINST('wireless' IN BOOLEAN MODE);

-- 坏：SELECT *
-- 坏：
SELECT * FROM users JOIN orders ON ...;
-- 好：只选择需要的列
SELECT u.id, u.name, o.total FROM users u JOIN orders o ON ...;

-- 坏：相关子查询
-- 坏：
SELECT * FROM users WHERE (SELECT COUNT(*) FROM orders WHERE user_id = users.id) > 5;
-- 好：JOIN 聚合
SELECT u.* FROM users u
JOIN (SELECT user_id, COUNT(*) cnt FROM orders GROUP BY user_id HAVING cnt > 5) o
ON o.user_id = u.id;

表分区

-- 按日期范围分区（对时间序列数据高效）
CREATE TABLE events (
  id BIGINT NOT NULL AUTO_INCREMENT,
  event_type VARCHAR(50),
  created_at DATETIME NOT NULL,
  payload JSON,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027),
  PARTITION pFuture VALUES LESS THAN MAXVALUE
);

-- 快速删除旧分区（相比 DELETE 慢操作）
ALTER TABLE events DROP PARTITION p2024;

减少复制延迟

# 在副本上
slave_parallel_workers = 8          # 并行应用
slave_parallel_type = LOGICAL_CLOCK # 组提交感知
binlog_transaction_dependency_tracking = WRITESET

查询缓存替代方案（MySQL 8 已移除）

-- 使用 ProxySQL 查询缓存，或应用层缓存
-- 或通过生成列使用 MySQL 内置结果缓存
ALTER TABLE products ADD COLUMN search_tokens TEXT GENERATED ALWAYS AS (
  CONCAT(name, ' ', IFNULL(description, ''), ' ', brand)
) STORED;
CREATE FULLTEXT INDEX idx_search ON products (search_tokens);