MySQL调优的常用手段（实战向指南）-CSDN博客

本文链接：https://blog.csdn.net/pythonxiaowang8/article/details/147642893

文章目录

前言：调优不是玄学！

你是不是经常遇到这些场景？（疯狂点头.JPG）

页面加载突然变慢
凌晨三点收到数据库CPU报警
面试官让你手撕慢查询优化

别慌！今天咱们就掰开揉碎讲讲MySQL调优的常用手段。全程实战向干货，看完就能用！（文末有面试高频考点总结）

一、执行计划分析（核心中的核心！）

1.1 EXPLAIN的魔法

EXPLAIN SELECT * FROM user WHERE age > 18 ORDER BY create_time DESC;

输出结果每个字段都要看懂：

type：ALL（全表扫）→ index → range → ref → const（性能从低到高）
key_len：计算索引使用长度的秘密（varchar字段要考虑字符集）
Extra：Using filesort（警惕！） / Using temporary（高危！）

1.2 必须掌握的优化器提示

SELECT /*+ INDEX(user idx_age) */ * FROM user FORCE INDEX(idx_age)

当优化器犯傻时，这些指令能救命！（但不要滥用！）

二、索引优化三板斧

2.1 索引建立的黄金法则

高频查询字段优先
联合索引注意最左前缀原则（最常用字段放左边）
区分度高的字段适合单列索引（性别字段建索引？不如不建！）

2.2 索引失效的八大酷刑（血泪教训！）

对索引列使用函数：WHERE YEAR(create_time)=2023
隐式类型转换：WHERE phone=13800138000（phone是varchar类型）
前导通配符：WHERE name LIKE '%张%'
范围查询后的列失效：联合索引(a,b,c)中WHERE a>1 AND b=2

三、SQL改写奇技淫巧

3.1 分页优化：别再用LIMIT了！

原始写法：

SELECT * FROM logs ORDER BY id LIMIT 1000000,10;

优化方案：

SELECT * FROM logs WHERE id > 1000000 ORDER BY id LIMIT 10;

（前提是连续分页，适合新闻类场景）

3.2 JOIN优化：小表驱动大表

-- 错误示范（大表在前）
SELECT * FROM big_table JOIN small_table ON... 

-- 正确姿势（小表驱动）
SELECT * FROM small_table STRAIGHT_JOIN big_table ON...

四、参数调优的平衡术

4.1 内存相关参数

innodb_buffer_pool_size = 物理内存的60-70%
sort_buffer_size = 2M  # 不是越大越好！
join_buffer_size = 256K

4.2 连接数控制

max_connections=1000  # 根据实际压力调整
wait_timeout=300      # 防止sleep连接过多

五、表结构优化冷知识

5.1 垂直拆分三原则

把text/blob等大字段单独拆表
频繁更新的字段单独分组
遵循"热数据分离"原则

5.2 冷热数据归档方案

-- 创建归档表
CREATE TABLE orders_archive LIKE orders;

-- 数据迁移
INSERT INTO orders_archive SELECT * FROM orders 
WHERE create_time < '2020-01-01';

-- 原表删除
DELETE FROM orders WHERE create_time < '2020-01-01';

六、硬件层面的降维打击

当SQL和参数都优化到极致时：

SSD替换机械硬盘（IOPS提升10倍+）
升级CPU主频（OLTP场景效果显著）
增加内存容量（你永远需要更大的buffer pool）

面试高频考点总结

最左前缀原则的实际应用场景
如何判断索引是否生效？
遇到过哪些索引失效的情况？
大表分页查询怎么优化？
JOIN查询的执行顺序是怎样的？

结语：调优没有银弹

记住这个调优公式：
执行计划分析 × 索引优化 × SQL改写 × 参数调整 = 性能提升

最后送大家一句话：调优不是炫技，要结合业务场景做取舍！（比如有时候加个缓存比死磕SQL更有效）

下次遇到慢查询时，记得回来看看这篇攻略！（收藏不迷路~）