MySQL 调优

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查看慢 SQLmysqldumpslow -s t -t 10 /var/log/mysql/slow.log2. 通用 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 20JOIN 最多 2~3 张表关联字段必须同类型、同编码避免子查询改用 JOIN二、第二步索引优化性能提升核心1. 最佳索引设计原则高频查询字段建索引where、order by、group by、join 字段联合索引遵循最左前缀原则例索引(a,b,c)可命中a /ab /abc单表索引不超过 5 个避免写入变慢区分度低的字段不建索引性别、状态2. 用 EXPLAIN 分析 SQLEXPLAIN SELECT * FROM user WHERE name 张三;重点看 3 列type最优 最差system const eq_ref rangeref ALLALL 全表扫描必须优化key实际使用的索引NULL 没命中rows扫描行数越小越好3. 索引失效场景必避坑使用!、NOT IN、IS NULL字符串不加引号联合索引不满足最左前缀LIKE 以 % 开头%abc三、第三步MySQL 配置调优my.cnf根据服务器内存调整核心参数如下1. 连接与并发max_connections 1000 # 最大连接数 wait_timeout 600 # 空闲连接关闭时间 interactive_timeout 6002. 内存配置最重要# 缓冲池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 2M4. 其他关键优化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%;六、调优优先级总结慢 SQL 优化→ 2.索引优化→ 3.配置调优→ 4.架构升级90% 的性能问题都能在前两步解决。总结调优先找慢 SQL不要上来改配置索引是性能关键联合索引 最左前缀是核心InnoDB 缓冲池innodb_buffer_pool_size是最关键配置高并发最终靠读写分离 分库分表 缓存解决