MySQL调优的十八般武艺（实战经验分享）-CSDN博客

本文链接：https://blog.csdn.net/cloudnomad1/article/details/147706021

老铁们！今天咱们来聊聊MySQL调优那些事儿（保准都是干货）。你猜怎么着？上周我亲眼见证了一个2000万行的表，从5秒查询优化到0.02秒的全过程，老板当场就给DBA加了鸡腿🍗！

一、索引这把双刃剑（用好了真香！）

前两天有个同事把索引建得像圣诞树🎄一样，结果写入速度直接扑街。记住这三个口诀：

最左前缀原则：(a,b,c)索引能查a、ab、abc，但查b或c就抓瞎
别在索引列搞事情：WHERE LEFT(name,3)='abc'这种操作会让索引当场罢工
覆盖索引大法好：EXPLAIN看到Using index时，就像坐上了火箭🚀

举个栗子🌰：

-- 错误示范（索引失效）：
SELECT * FROM users WHERE YEAR(create_time) = 2023;

-- 正确姿势：
SELECT * FROM users 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

二、SQL语句的骚操作（坑我踩过你们别跳）

去年双十一，有个SELECT COUNT(*)把整个库拖垮了。血泪教训告诉你：

LIMIT分页深坑：LIMIT 100000,10这种写法会把前10万条数据全扫描（试试id范围查询）
JOIN要讲基本法：小表驱动大表，给关联字段加索引（重要的事情说三遍）
**别用SELECT ***：特别是TEXT/BLOB字段，传输数据量能吓死你

看这个慢查询改造：

-- 改造前（执行时间2.8s）：
SELECT * FROM orders 
WHERE status = 1 
ORDER BY create_time DESC 
LIMIT 1000;

-- 改造后（0.05s）：
SELECT id,order_no FROM orders 
WHERE status = 1 AND create_time > '2023-06-01' 
ORDER BY create_time DESC 
LIMIT 1000;

三、配置参数调校手册（新手慎入）

把my.cnf当女朋友一样对待（要温柔！），这几个参数能救命：

参数名	默认值	推荐值	作用说明
innodb_buffer_pool_size	128M	物理内存的70%	缓存池大小（性能关键！）
max_connections	151	500-1000	并发连接数（小心OOM）
wait_timeout	28800秒	300秒	空闲连接超时
slow_query_log	OFF	ON	慢查询日志（查案必备）

⚠️警告：修改innodb_flush_log_at_trx_commit前先备份！从1改成2性能提升明显，但可能丢1秒数据。

四、架构层面的降维打击

当单机撑不住时，试试这些骚操作：

读写分离：用Maxwell监听binlog同步到从库（主库写，从库读）
冷热分离：把半年前的订单扔到ClickHouse
缓存爆破：Redis+本地缓存二级缓存，命中率直接拉满
垂直拆分：把用户画像字段单独拆表（高频查基础信息，低频查画像）

最近帮某电商做的分库分表方案：

原始表：user_orders（5000万行）
拆分后：
- user_orders_001 ~ user_orders_016（按user_id%16分表）
- 每个分表单独数据库实例
- 查询路由组件自动分发

五、执行计划阅读指南（DBA必备技能）

看懂EXPLAIN输出比看懂女朋友脸色还重要：

type列：至少要是range级别（system > const > ref > range > index > ALL）
Extra列：
- Using filesort：赶紧加索引！
- Using temporary：临时表警告！
- Using index condition：索引下推（5.6+福利）
rows列：扫描行数超过1万就要警惕了

举个真实案例：

EXPLAIN SELECT * FROM products 
WHERE category_id = 5 AND price > 100 
ORDER BY sales_count DESC 
LIMIT 10;

当看到Using filesort时，赶紧建个(category_id, price, sales_count)的联合索引。

六、面试官的死亡拷问（这样回答稳了）

最近帮学员整理的面试高频题：

Q：遇到慢查询怎么处理？
A：先看执行计划→检查索引→优化SQL→考虑缓存→最后才调参数
Q：说下MVCC实现原理？
A：undo log版本链+ReadView机制，配合事务ID和回滚指针（别忘提间隙锁）
Q：主从同步延迟怎么办？
A：半同步复制+并行复制+适当调大relay_log空间（加分项：提MGR方案）
Q：大表DDL怎么操作？
A：用pt-online-schema-change工具（原理：建影子表→同步数据→原子切换）

最后说句掏心窝的

调优就像中医把脉，得望（监控）、闻（日志）、问（沟通）、切（分析）。记住三个不要：

不要一上来就动配置参数（先看SQL和索引）
不要过度设计架构（能单机别分库）
不要相信银弹（适合业务场景的才是最好的）

最近发现个好用的监控组合：Prometheus+Grafana监控大盘+Percona监控插件，实时掌握数据库心跳。调优路漫漫，咱们且调且珍惜～（抱拳）