MySQL调优的五大神技（实战踩坑总结）_mysql性能调优-优快云博客

本文链接：https://blog.youkuaiyun.com/techfluent/article/details/147684457

最近帮朋友公司做数据库优化（他们系统卡得连老板都打不开报表了！），发现很多新手DBA面对性能问题只会无脑加索引。今天咱们就来聊聊那些真正能打的调优手段，看完绝对让你少走三年弯路！（文末附高频面试题）

一、执行计划分析（必看！！！）

先扔个王炸案例：某订单表3000万数据的统计查询要跑3分钟。原始SQL长这样：

SELECT user_id, COUNT(*) 
FROM orders 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY user_id;

用EXPLAIN一解析，发现走了全表扫描（type=ALL）！这时候就该祭出调优三板斧：

关注type列：至少达到range级别，理想是ref或const
key列不能空：检查是否用到了合适的索引
rows别太大：估算扫描行数超过10万就要警惕

优化后给create_time和user_id建联合索引：

ALTER TABLE orders ADD INDEX idx_ct_uid (create_time, user_id);

执行时间直接降到0.8秒！但要注意索引顺序——where条件在前，group by在后才是正确姿势。

二、索引设计的暗坑（血泪教训）

去年踩过一个巨坑：给varchar(255)的邮箱字段建了普通索引，结果索引文件比数据文件还大两倍！几个避坑指南：

前缀索引：ALTER TABLE users ADD INDEX idx_email (email(20))
别用NULL：is null会让索引失效（除非字段定义为NOT NULL）
联合索引最左匹配：INDEX(a,b,c)只能支持a | a+b | a+b+c的查询

有个反常识的技巧：ORDER BY子句中的字段顺序必须和索引一致，否则会触发filesort。比如：

-- 现有索引(a,b)
SELECT * FROM table WHERE a=1 ORDER BY b; -- 有效
SELECT * FROM table WHERE a=1 ORDER BY b DESC; -- 有效
SELECT * FROM table WHERE a=1 ORDER BY a,b; -- 无效！

三、慢查询的九种死法（真实案例）

最近统计了公司100+条慢查询，发现排名前3的作死写法：

隐式类型转换：

-- user_id是varchar类型
SELECT * FROM orders WHERE user_id = 10086; -- 全表扫描！

函数操作字段：

SELECT * FROM users WHERE DATE(create_time) = '2023-01-01'; -- 改成范围查询

乱用子查询：

SELECT * FROM A WHERE id IN (
  SELECT a_id FROM B WHERE status = 1
); -- 改成JOIN更高效

有个骚操作：当遇到OR条件时，可以尝试改写成UNION ALL：

SELECT * FROM table WHERE a=1 OR b=2
-- 优化为
SELECT * FROM table WHERE a=1
UNION ALL
SELECT * FROM table WHERE b=2 AND a!=1

四、配置参数的黑魔法（调优必杀技）

修改my.cnf这几个参数后，某电商系统QPS从200飙升到1200：

innodb_buffer_pool_size = 16G # 建议是内存的70%
innodb_flush_log_at_trx_commit = 2 # 牺牲点安全性换性能
thread_cache_size = 64 # 减少线程创建开销

但千万注意！query_cache_type在MySQL8.0已经被移除，别再用了。高并发下建议直接关掉：

query_cache_type = 0
query_cache_size = 0

五、表结构设计的玄学（新手易翻车）

最近重构了一个历史遗留系统，发现三个典型问题：

大字段集中存放：把text类型单独拆表
错误使用ENUM：当枚举值超过20个时，改用关联表
忘记更新统计信息：ANALYZE TABLE orders定期执行

有个冷知识：DATETIME类型在MySQL5.6之后支持毫秒存储，但用TIMESTAMP省空间（4字节 vs 8字节）

高频面试题（附答案）

Q：为什么索引不生效？
A：检查字段类型、函数操作、最左匹配原则、索引统计信息

Q：如何避免深分页？
A：WHERE id > 10000 LIMIT 10 代替 LIMIT 10000,10

Q：线上出现死锁怎么办？
A：1. 查看SHOW ENGINE INNODB STATUS 2. 重试机制 3. 控制事务粒度

调优这事吧，就像老中医看病——经验比理论更重要。记得去年有个诡异案例：某查询在测试环境飞快，上生产就慢成狗，最后发现是UTF8MB4字符集导致索引长度不够（MySQL的坑你懂的）。所以遇到问题别慌，先拿EXPLAIN开道，再结合业务场景分析，准能药到病除！