一、快速定位性能瓶颈
1. 开启慢查询日志
-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';-- 临时开启慢查询日志（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 超过 2 秒的查询记录
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';-- 永久生效需修改 my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1 -- 记录未走索引的查询
2. 分析慢查询日志
使用 mysqldumpslow 或 pt-query-digest 工具：
# 查看最耗时的 10 条慢查询
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
# 使用 Percona Toolkit 详细分析
pt-query-digest /var/lib/mysql/slow.log > slow_report.txt
3. 实时监控运行状态
二、SQL 语句优化
1. 避免全表扫描
SELECT * FROM orders WHERE status = 'pending' ORDER BY create_time DESC;
2. 避免 SELECT *
3. 分页优化
低效分页：
SELECT * FROM logs ORDER BY id LIMIT 1000000, 10;
优化方案：
-- 使用主键游标分页
SELECT * FROM logs WHERE id > 1000000 ORDER BY id LIMIT 10;-- 或使用延迟关联
SELECT t.* FROM logs t
JOIN (SELECT id FROM logs ORDER BY id LIMIT 1000000, 10) tmp
ON t.id = tmp.id;
4. 避免隐式类型转换
SELECT * FROM users WHERE phone = 13800138000; -- phone 是 VARCHAR 类型
优化：
SELECT * FROM users WHERE phone = '13800138000';
三、索引优化核心策略
1. 索引设计原则
最左前缀原则：联合索引
(a, b, c)可生效于a,a,b,a,b,c的查询，但b,c无法生效。区分度高：选择区分度高的列（如
user_id而不是gender）。避免冗余索引：删除重复或未被使用的索引。
2. 索引失效场景
对索引列使用函数或计算：
WHERE YEAR(create_time) = 2023类型不匹配：
WHERE id = '100'（id是 INT）使用
OR连接非索引列：WHERE a=1 OR b=2（若b无索引）模糊查询前缀通配符：
WHERE name LIKE '%abc'
3. 强制索引
SELECT * FROM orders FORCE INDEX(idx_user) WHERE user_id = 100;
4. 索引维护
-- 重建索引优化碎片
ALTER TABLE orders ENGINE=InnoDB;-- 删除无用索引
SELECT * FROM sys.schema_unused_indexes; -- 需要开启 performance_schema
DROP INDEX idx_old ON orders;
四、配置参数调优（my.cnf）
1. InnoDB 关键配置
[mysqld]
innodb_buffer_pool_size = 16G -- 通常设为物理内存的 70%~80%
innodb_log_file_size = 2G -- 增大日志文件减少刷盘频率
innodb_flush_log_at_trx_commit = 2 -- 非严格 ACID 场景可提升写入性能
innodb_thread_concurrency = 0 -- 自动调整并发线程数
2. 连接与线程
max_connections = 1000 -- 最大连接数
thread_cache_size = 100 -- 线程缓存
wait_timeout = 600 -- 非交互连接超时时间
3. 查询缓存（通常建议关闭）
query_cache_type = 0 -- 高并发写入场景关闭查询缓存
五、架构级优化
1. 读写分离
主库：处理写操作和实时性高的读请求。
从库：处理报表、统计等非实时读请求
-- 从库配置
CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='repl_user',
MASTER_PASSWORD='repl_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=123456;
START SLAVE;
2. 分库分表
垂直拆分：按业务模块拆分（如
user_db,order_db）。水平拆分：按哈希或范围分表（如
user_001,user_002）。使用中间件：ShardingSphere、Vitess。
3. 缓存层
Redis：缓存热点数据（如用户会话、商品信息）。
MySQL 查询缓存：慎用，适用于读多写少且数据更新低频的场景。
六、高频问题解决方案
1. 死锁分析
-- 查看最近死锁日志
SHOW ENGINE INNODB STATUS;
解决：
保持事务短小，按固定顺序访问资源。
使用
SELECT ... FOR UPDATE明确锁定顺序。
2. 主从延迟
优化：
从库使用 SSD。
调整
innodb_flush_log_at_trx_commit和sync_binlog。使用半同步复制（
rpl_semi_sync_master_enabled=1）。
3. 大表 ALTER 操作
ALTER TABLE orders ADD INDEX idx_new_column (new_column), ALGORITHM=INPLACE, LOCK=NONE;
七、必备工具
Percona Toolkit：分析慢查询、归档数据。
mysqltuner.pl：一键生成配置优化建议。
Prometheus + Grafana：实时监控数据库状态。
Explain 可视化工具：explain.dalibo.com