MySQL数据库优化完全指南：从慢查询到高性能架构

MySQL是世界上最流行的开源数据库。本文将介绍从基础优化到高级架构设计的完整优化方案。

1. 索引优化

索引类型

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

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

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

-- 复合索引
CREATE INDEX idx_user_status ON orders(user_id, status);

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

索引最佳实践

-- 1. 使用覆盖索引避免回表
SELECT id, name FROM users WHERE age = 25;
-- 创建索引：idx_age_name(age, name)

-- 2. 避免索引失效
-- 错误：索引列使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2025;

-- 正确：范围查询
SELECT * FROM users 
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

-- 3. 使用前缀索引
CREATE INDEX idx_email ON users(email(10));

2. 查询优化

执行计划分析

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE age = 25;

-- 详细分析
EXPLAIN ANALYZE SELECT * FROM users WHERE age = 25;

-- 关键指标
-- type: ALL（全表扫描）需优化
-- key: NULL（未使用索引）需优化
-- rows: 扫描行数，越少越好
-- Extra: Using filesort、Using temporary 需要优化

查询优化示例

-- 1. 避免SELECT *
SELECT id, name, email FROM users;

-- 2. 优化分页查询
-- 错误方式（OFFET大时性能差）
SELECT * FROM posts ORDER BY id LIMIT 10000, 20;

-- 正确方式（游标分页）
SELECT * FROM posts 
WHERE id > 10000 
ORDER BY id 
LIMIT 20;

-- 3. 使用JOIN代替子查询
-- 错误
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- 正确
SELECT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

-- 4. 批量插入
INSERT INTO users (name, email) VALUES
('user1', '[email protected]'),
('user2', '[email protected]'),
('user3', '[email protected]');

3. 架构优化

读写分离

-- 主库（写）
-- 从库（读）

-- 应用层配置
-- 写操作 -> 主库
-- 读操作 -> 从库

-- MySQL配置（从库）
server-id = 2
relay-log = mysql-relay
read_only = ON

分库分表

-- 垂直分表
-- users表拆分为：user_base、user_profile

-- 水平分表
-- orders表按时间分表：orders_2025_01, orders_2025_02

-- 分片键选择
-- 选择查询最频繁的列
-- 避免热点问题

连接池配置

-- MySQL配置
max_connections = 500
max_user_connections = 100
wait_timeout = 60

-- 应用层连接池（示例：HikariCP）
maximumPoolSize=20
minimumIdle=5
connectionTimeout=30000
idleTimeout=600000

4. 存储引擎选择

-- InnoDB（默认，推荐）
-- 支持事务、行级锁、外键
-- 适合OLTP场景

-- MyISAM
-- 不支持事务、表级锁
-- 适合只读场景

-- Memory
-- 数据存在内存中
-- 适合临时表、缓存

5. 参数优化

-- my.cnf配置示例
[mysqld]
# 内存配置
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 64M
key_buffer_size = 256M

# 并发配置
max_connections = 500
max_connect_errors = 100
wait_timeout = 60

# InnoDB配置
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

# 查询缓存（MySQL 8.0已移除）
-- query_cache_type = 0
-- query_cache_size = 0

# 日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

6. 高可用方案

主从复制

-- 主库配置
server-id = 1
log-bin = mysql-bin
binlog_format = ROW

-- 从库配置
server-id = 2
relay-log = mysql-relay
read_only = ON

MGR（组复制）

-- 多主架构
-- 自动故障转移
-- 数据一致性保证

InnoDB Cluster

-- 创建集群
mysqlsh> cluster.createInstance('user@host1')
mysqlsh> cluster.createInstance('user@host2')
mysqlsh> cluster.createInstance('user@host3')
mysqlsh> dba.createCluster('myCluster')

7. 监控与维护

慢查询日志

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

-- 分析慢查询
mysqldumpslow -s t /var/log/mysql/slow.log

性能监控

-- 当前连接
SHOW STATUS LIKE 'Threads_connected';

-- 查询缓存
SHOW STATUS LIKE 'Qcache%';

-- InnoDB缓冲池
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 表状态
SHOW TABLE STATUS FROM database_name;

定期维护

-- 优化表
OPTIMIZE TABLE users;

-- 分析表
ANALYZE TABLE users;

-- 检查表
CHECK TABLE users;

8. 备份策略

逻辑备份

-- mysqldump备份
mysqldump -u root -p database_name > backup.sql

-- 恢复
mysql -u root -p database_name < backup.sql

-- 压缩备份
mysqldump database_name | gzip > backup.sql.gz

物理备份

-- xtrabackup备份
xtrabackup --backup --target-dir=/backup

-- 恢复
xtrabackup --prepare --target-dir=/backup
xtrabackup --copy-back --target-dir=/backup

9. 常见问题排查

死锁

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;

-- 分析死锁原因
-- 优化事务顺序
-- 减少事务持有锁的时间

锁等待

-- 查看锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看当前事务
SELECT * FROM information_schema.INNODB_TRX;

10. 优化检查清单

• [ ] 所有表都有主键
• [ ] 关键查询都有合适索引
• [ ] 避免使用SELECT *
• [ ] 慢查询已优化
• [ ] 连接池配置合理
• [ ] 定期分析表结构
• [ ] 备份策略已实施
• [ ] 监控告警已配置

总结

MySQL优化是一个系统性工程。关键要点：

• 索引是关键：合理设计索引
• 查询优化：避免慢查询
• 架构设计：读写分离、分库分表
• 持续监控：建立性能基线
• 定期维护：优化表、分析表