MySQL慢查询优化实战指南：从8秒到0.3秒的蜕变之路-优快云博客

文章目录

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

一、揪出元凶：定位慢查询的三种姿势

1. 慢查询日志（必杀技）

在my.cnf配置中加入这三行（超级重要）：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1  # 超过1秒的都记下来（建议生产环境设2秒）

重启MySQL后，慢查询就像被装了GPS的逃犯，所有执行超过1秒的SQL都会在日志里现形！

2. 进程列表快照（急诊室诊断）

突发卡顿时，速查当前运行语句：

SHOW FULL PROCESSLIST;  -- 重点看Time列和State列

看到Sending data状态超过3秒的？立马标记为嫌疑犯！

3. 性能模式（高阶装备）

MySQL 5.7+用户试试这个监控方案：

UPDATE setup_consumers SET ENABLED = 'YES' 
WHERE NAME LIKE '%statement%';

所有SQL执行细节自动归档到events_statements_summary_by_digest表，比X光还清晰！

二、深度解剖：EXPLAIN执行计划六脉神剑

拿到慢SQL后，先祭出EXPLAIN大法：

EXPLAIN SELECT * FROM orders WHERE user_id=123 AND status=1;

关键指标解读（划重点！！）：

type列：ALL全表扫描（立即报警！），index走索引，range范围扫描
rows列：预估扫描行数，超过1000就要警惕
Extra列：出现Using filesort或Using temporary说明在犯罪现场！

举个真实案例（血泪教训）：

-- 优化前
EXPLAIN SELECT * FROM users WHERE DATE(create_time) = '2023-01-01';

-- 优化后
EXPLAIN SELECT * FROM users 
WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';

就改了个时间范围查询，type从ALL变成range，查询速度直接提升10倍！

三、索引优化九阴真经（实战精华）

1. 联合索引避坑指南

错误示范：

ALTER TABLE orders ADD INDEX idx_status (status);

status字段只有3个枚举值，建单列索引≈白建！

正确姿势：

ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

联合索引选择性直接飙升到90%！

2. 隐式转换陷阱

这条SQL会让你死得很惨：

SELECT * FROM users WHERE phone = 13800138000; 
-- phone是varchar类型！！

MySQL会默默做全表扫描，改成phone='13800138000'速度立马上天！

3. 索引失效的七宗罪

❌ 对索引字段使用函数：WHERE LEFT(name,3)='张'
❌ 类型不匹配：WHERE id='100'（id是int类型）
❌ 模糊查询乱用：WHERE content LIKE '%优化%'
❌ 索引列参与运算：WHERE YEAR(create_time)=2023

四、慢查询终极优化五式

招式1：化整为零

-- 原语句（耗时5秒）
DELETE FROM logs WHERE create_time < '2022-01-01';

-- 优化版（每次删1000条）
WHILE EXISTS(SELECT 1 FROM logs WHERE create_time < '2022-01-01' LIMIT 1) DO
  DELETE FROM logs WHERE create_time < '2022-01-01' LIMIT 1000;
  COMMIT;
  SLEEP 1;
END WHILE;

招式2：空间换时间

给统计报表加个缓存表：

CREATE TABLE daily_report (
    date DATE PRIMARY KEY,
    order_count INT,
    total_amount DECIMAL(10,2)
) ENGINE=InnoDB;

-- 每天凌晨更新
REPLACE INTO daily_report 
SELECT DATE(create_time), COUNT(*), SUM(amount) 
FROM orders 
WHERE create_time >= CURDATE() - INTERVAL 1 DAY
GROUP BY DATE(create_time);

招式3：拆解复杂查询

原语句：

SELECT u.*, o.order_count 
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS order_count 
    FROM orders 
    GROUP BY user_id
) o ON u.id = o.user_id
WHERE u.create_time > '2023-01-01';

优化版：

-- 先查用户ID
CREATE TEMPORARY TABLE tmp_users 
SELECT id FROM users WHERE create_time > '2023-01-01';

-- 再查订单数
CREATE TEMPORARY TABLE tmp_orders 
SELECT user_id, COUNT(*) AS order_count 
FROM orders 
WHERE user_id IN (SELECT id FROM tmp_users)
GROUP BY user_id;

-- 最后联查
SELECT u.*, o.order_count 
FROM tmp_users tu
JOIN users u ON tu.id = u.id
LEFT JOIN tmp_orders o ON tu.id = o.user_id;

五、真实案例：电商系统8秒查询优化记

问题场景：
商品搜索页SQL耗时8秒：

SELECT * FROM products 
WHERE category_id=5 
AND price BETWEEN 100 AND 1000 
AND status=1 
ORDER BY sales DESC 
LIMIT 100 OFFSET 0;

优化过程：

原执行计划显示type=ALL，扫描20万行
创建联合索引：

ALTER TABLE products ADD INDEX idx_search (category_id, status, price, sales);

改写分页语句：

SELECT * FROM products 
WHERE category_id=5 
AND status=1 
AND price >= 100 
AND price <= 1000 
AND sales <= (SELECT sales FROM products 
              WHERE category_id=5 
              AND status=1 
              ORDER BY sales DESC 
              LIMIT 100,1)
ORDER BY sales DESC 
LIMIT 100;

最终效果：查询时间从8秒降到0.3秒！

六、性能监控三板斧（日常必备）

每周慢查询分析：

mysqldumpslow -s t /var/log/mysql/slow.log | head -20

实时监控利器：

watch -n 1 "mysqladmin -uroot -p processlist | grep -v Sleep"

性能趋势图：

SELECT DIGEST_TEXT, COUNT_STAR, 
       AVG_TIMER_WAIT/1000000000 AS avg_time 
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY avg_time DESC 
LIMIT 10;