MySQL调优实战手册：老司机压箱底的7个优化绝招（实战案例详解）-CSDN博客

本文链接：https://blog.csdn.net/TechOrbit/article/details/147939936

文章目录

一、先看这个真实案例（血泪教训！）

上周刚处理了一个线上事故：某电商平台促销活动时，数据库CPU直接飙到100%，订单查询接口超时15秒！经过紧急排查，发现罪魁祸首竟是一条看起来人畜无害的SQL：

SELECT * FROM orders 
WHERE user_id = 12345 
AND status IN (1,3,5)
ORDER BY create_time DESC
LIMIT 20;

（看起来很正常对吧？但执行计划显示全表扫描了2000万行数据！！！）

二、基础优化三板斧（小白必看）

2.1 先查慢查询日志（超级重要）

在my.cnf中开启配置：

slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1

第二天发现日志里躺着几十条这样的慢查询：

-- 执行时间8.7秒
SELECT COUNT(*) FROM users 
WHERE last_login_time > '2023-01-01';

2.2 EXPLAIN执行计划解读（看这一张图就懂）

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传
（注意看type列：ALL全表扫描要立即优化，range算及格，const才是王者）

三、索引优化实战技巧（核心重点）

3.1 复合索引避坑指南

错误示范：

ALTER TABLE orders ADD INDEX idx_user (user_id);
ALTER TABLE orders ADD INDEX idx_status (status);

正确姿势：

-- 联合索引顺序很重要！
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

3.2 覆盖索引的魔法

案例对比：

-- 需要回表
SELECT * FROM products WHERE category = '电子产品';

-- 覆盖索引
SELECT id, name FROM products 
WHERE category = '电子产品';

（建立(category, name)联合索引后，速度提升20倍！）

四、查询语句优化实战（高频考点）

4.1 IN语句优化技巧

错误写法：

SELECT * FROM orders 
WHERE user_id IN (
  SELECT id FROM users WHERE vip_level > 3
);

优化方案：

-- 改用JOIN
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.vip_level > 3;

4.2 分页查询优化

原始分页：

SELECT * FROM logs 
ORDER BY id DESC 
LIMIT 1000000, 20; -- 慢到怀疑人生

优化方案：

SELECT * FROM logs 
WHERE id < 上次最小ID 
ORDER BY id DESC 
LIMIT 20;

五、参数调优黄金配置（生产环境推荐）

在my.cnf中加入：

[mysqld]
innodb_buffer_pool_size = 16G  # 建议设置为物理内存的70%
innodb_flush_log_at_trx_commit = 2
sync_binlog = 1000
thread_cache_size = 64
max_connections = 500

（某电商系统调整后，QPS从800直接飙到3500！）

六、架构层优化策略（高并发必看）

6.1 读写分离方案

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

6.2 分库分表实战

按用户ID分片：

-- 原始表
CREATE TABLE messages (
  id BIGINT PRIMARY KEY,
  user_id INT,
  content TEXT
);

-- 分表方案
CREATE TABLE messages_00 (同结构);
CREATE TABLE messages_01 (同结构);
-- 共16个分表