MySQL 优化：从索引到架构的性能调优指南

MySQL 是最流行的关系型数据库，性能优化是每个 DBA 和开发者的必修课。本文从索引设计、查询优化、架构调优三个维度，系统讲解 MySQL 性能优化技巧。

一、索引优化

1.1 索引类型

-- 主键索引
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 普通索引
CREATE INDEX idx_name ON users(name);

-- 复合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

1.2 索引设计原则

1. 选择区分度高的列作为索引

2. 遵循最左前缀原则

3. 避免在索引列上使用函数

4. 复合索引顺序：等值查询列在前，范围查询列在后

1.3 索引分析

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- 查看表索引
SHOW INDEX FROM users;

-- 分析索引使用情况
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'mydb';

二、查询优化

2.1 避免全表扫描

-- 不推荐
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- 推荐
SELECT * FROM orders WHERE created_at >= '2024-01-01' 
                        AND created_at < '2025-01-01';

-- 不推荐
SELECT * FROM users WHERE name LIKE '%张%';

-- 推荐
SELECT * FROM users WHERE name LIKE '张%';

2.2 优化 JOIN

-- 小表驱动大表
SELECT o.*, u.name 
FROM orders o 
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid';

-- 避免 JOIN 过多表（建议不超过 5 个）
-- 确保 JOIN 字段有索引

2.3 分页优化

-- 传统分页（性能差）
SELECT * FROM orders LIMIT 1000000, 10;

-- 延迟关联优化
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders LIMIT 1000000, 10
) t ON o.id = t.id;

-- 游标分页
SELECT * FROM orders 
WHERE id > 1000000 
ORDER BY id LIMIT 10;

2.4 子查询优化

-- 不推荐
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE status = 'paid');

-- 推荐（JOIN 替代）
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid';

三、架构优化

3.1 分库分表

-- 垂直分表：按字段拆分
-- 主表：users_core (id, name, email)
-- 扩展表：users_profile (user_id, avatar, bio)

-- 水平分表：按数据拆分
-- users_0, users_1, users_2 ...
-- 分表键：user_id % 3

3.2 读写分离

写操作走主库，读操作走从库，通过代理层自动路由。

主库 -> binlog 同步 -> 从库
           |
           v
      应用层读写分离中间件

3.3 缓存策略

1. 查询缓存：MySQL 8.0 已移除，建议使用 Redis

2. 应用层缓存：热点数据缓存到 Redis

3. 缓存穿透：缓存空值或布隆过滤器

四、服务器参数优化

[mysqld]
# InnoDB 缓冲池（建议设为物理内存的 70-80%）
innodb_buffer_pool_size = 4G

# 日志文件大小
innodb_log_file_size = 512M

# 并发线程数
innodb_thread_concurrency = 16

# 查询缓存（MySQL 5.7）
query_cache_size = 64M
query_cache_type = ON

# 连接数
max_connections = 500
max_connect_errors = 1000

# 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M

五、慢查询分析

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 查看慢查询
SELECT * FROM mysql.slow_log 
ORDER BY query_time DESC 
LIMIT 10;

-- 使用 mysqldumpslow 分析
mysqldumpslow -s t /var/log/mysql/slow.log

六、性能监控

-- 查看状态
SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL STATUS LIKE 'Connections';
SHOW GLOBAL STATUS LIKE 'Innodb%';

-- 查看进程
SHOW PROCESSLIST;

-- 使用 Performance Schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

七、总结

MySQL 优化是一个系统工程，需要从索引、查询、架构、参数等多个维度综合考虑。

优化建议：

1. 合理设计索引，遵循最佳实践

2. 避免 SELECT *，只查需要的列

3. 大表分库分表，读写分离

4. 定期分析慢查询，持续优化

5. 监控关键指标，及时发现问题

本文链接：https://www.kkkliao.cn/?id=777 转载需授权！