当前位置：首页 > news >正文

MySQL 调优

news 2026/4/25 10:00:42

MySQL 调优

一、第一步：慢查询优化（最见效、优先级最高）

1. 开启慢查询日志（定位问题 SQL）

# my.cnf 配置 slow_query_log = 1 long_query_time = 1 # 超过1秒的SQL记录 slow_query_log_file = /var/log/mysql/slow.log log_queries_not_using_indexes = 1 # 记录未使用索引的SQL

查看慢 SQL：

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

2. 通用 SQL 优化规则

禁止SELECT *，只查需要的字段
避免在索引列上运算 / 函数❌ 坏：WHERE YEAR(create_time) = 2025✅ 好：WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31'
少用OR，改用IN
分页深分页优化❌ 坏：LIMIT 100000,20✅ 好：WHERE id > 100000 LIMIT 20
JOIN 最多 2~3 张表，关联字段必须同类型、同编码
避免子查询，改用 JOIN

二、第二步：索引优化（性能提升核心）

1. 最佳索引设计原则

高频查询字段建索引：where、order by、group by、join 字段
联合索引遵循最左前缀原则例：索引(a,b,c)，可命中：a /a+b /a+b+c
单表索引不超过 5 个，避免写入变慢
区分度低的字段不建索引（性别、状态）

2. 用 EXPLAIN 分析 SQL

EXPLAIN SELECT * FROM user WHERE name = '张三';

重点看 3 列：

type：最优 > 最差：system > const > eq_ref >range>ref> ALLALL = 全表扫描，必须优化
key：实际使用的索引（NULL = 没命中）
rows：扫描行数（越小越好）

3. 索引失效场景（必避坑）

使用!=、NOT IN、IS NULL
字符串不加引号
联合索引不满足最左前缀
LIKE 以 % 开头（%abc）

三、第三步：MySQL 配置调优（my.cnf）

根据服务器内存调整，核心参数如下：

1. 连接与并发

max_connections = 1000 # 最大连接数 wait_timeout = 600 # 空闲连接关闭时间 interactive_timeout = 600

2. 内存配置（最重要）

# 缓冲池（InnoDB 最重要参数） # 建议：物理内存的 50%~70% innodb_buffer_pool_size = 16G # 32G内存服务器举例 innodb_log_file_size = 2G # 事务日志大小 innodb_flush_log_at_trx_commit = 1 # 安全优先 # 高并发写入可改为 2，提升性能

3. 临时表与排序

tmp_table_size = 256M max_heap_table_size = 256M sort_buffer_size = 2M join_buffer_size = 2M

4. 其他关键优化

default-storage-engine = InnoDB character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci innodb_file_per_table = 1 # 独立表空间

四、第四步：表结构与架构调优

1. 表设计规范

使用合适的数据类型int 够用就不用 bigint；时间用 datetime 不用 varchar
字段尽量NOT NULL，用默认值代替 NULL
大表拆小：垂直拆分（字段）、水平拆分（数据）
禁止使用外键（业务层保证完整性）

2. 架构优化（高并发必备）

读写分离：主库写，从库读
分库分表：单表超过 1000w 数据考虑
加缓存：Redis 缓存热点数据，减少 DB 压力

五、快速排查命令（运维必备）

-- 查看当前运行SQL SHOW PROCESSLIST; -- 查看InnoDB状态 SHOW ENGINE INNODB STATUS; -- 查看配置 SHOW VARIABLES LIKE '%buffer_pool%'; -- 查看性能指标 SHOW STATUS LIKE '%Threads%'; SHOW STATUS LIKE '%Slow_queries%';