4. 慢查询优化实战：慢查询日志配置，pt-query-digest工具使用，常见慢SQL场景优化

说到慢查询优化，这可能是咱们DBA日常工作中最常打交道的事情了。我个人的习惯是，接手一个新系统，第一件事就是打开慢查询日志看看。说白了，慢查询就是数据库里的「交通拥堵点」，不解决它，系统迟早要出问题。

4.1 慢查询日志配置

MySQL的慢查询日志，就像飞机的黑匣子。它记录着所有执行时间超过阈值的SQL。嗯，这里要注意，默认情况下这个功能是关闭的。

先看看当前配置：

-- 查看慢查询相关参数
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE '%long_query_time%';
SHOW VARIABLES LIKE '%log_queries_not_using_indexes%';

我个人建议的配置方案是这样的：

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;

-- 设置慢查询阈值（单位：秒），我一般设成1秒
SET GLOBAL long_query_time = 1;

-- 指定日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

-- 记录没有使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

如果要永久生效，记得写到my.cnf配置文件里：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

4.2 pt-query-digest工具使用

光有日志还不够，你得会分析。pt-query-digest是Percona Toolkit里的明星工具。说实话，我用了这么多年，还没找到比它更好用的慢查询分析工具。

安装很简单：

# 下载安装
wget https://www.percona.com/downloads/percona-toolkit/LATEST/
tar -xzf percona-toolkit-*.tar.gz
cd percona-toolkit-*
perl Makefile.PL
make && make install

基本用法：

# 分析慢查询日志
pt-query-digest /var/log/mysql/mysql-slow.log

# 分析最近1小时的慢查询
pt-query-digest --since=1h /var/log/mysql/mysql-slow.log

# 输出到文件
pt-query-digest /var/log/mysql/mysql-slow.log > slow_analysis.txt

输出结果怎么看？我一般重点关注这几个指标：

指标	说明	重点关注
Query_time	查询总耗时	平均值和最大值
Lock_time	锁等待时间	占比超过10%要警惕
Rows_sent	返回行数	跟Rows_examined对比
Rows_examined	扫描行数	越大说明索引效率越低

4.3 常见慢SQL场景优化

好了，工具会用了，咱们来看看实际工作中最常见的几种慢SQL场景。说白了，大部分慢查询就那几类问题。

4.3.1 索引失效

这是最常见的问题。你明明建了索引，但MySQL就是不用。为什么会这样？

-- 场景1：对索引列使用函数
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';
-- 优化：使用范围查询
SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

-- 场景2：隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- phone是varchar类型
-- 优化：保持类型一致
SELECT * FROM users WHERE phone = '13800138000';

-- 场景3：LIKE以通配符开头
SELECT * FROM products WHERE name LIKE '%手机%';
-- 优化：考虑全文索引
SELECT * FROM products WHERE MATCH(name) AGAINST('手机');

4.3.2 大表全表扫描

我记得有个项目，订单表有5000万数据。每次查历史订单都慢得要命。后来怎么解决的？

-- 问题SQL
SELECT * FROM orders WHERE status = 1 AND create_time > '2023-01-01';

-- 优化方案1：复合索引
ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);

-- 优化方案2：分页查询（避免大偏移量）
-- 坏的写法
SELECT * FROM orders LIMIT 1000000, 20;
-- 好的写法
SELECT * FROM orders WHERE id > 1000000 LIMIT 20;

4.3.3 JOIN查询优化

多表关联查询，是慢查询的重灾区。我见过最夸张的一个SQL，关联了8张表，跑了30秒。

-- 问题场景：大表驱动小表
-- 坏的写法（用小表驱动大表）
SELECT * FROM orders o 
LEFT JOIN users u ON o.user_id = u.id 
WHERE u.status = 1;

-- 优化：确保被驱动表有索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- 更好的写法：先过滤再关联
SELECT * FROM (
  SELECT * FROM users WHERE status = 1
) u 
JOIN orders o ON o.user_id = u.id;

4.3.4 ORDER BY和GROUP BY优化

排序和分组操作，如果处理不好，很容易产生临时表和文件排序。嗯，这里要特别注意。

-- 问题：没有利用索引排序
EXPLAIN SELECT * FROM products 
WHERE category_id = 10 
ORDER BY price DESC;

-- 优化：建立覆盖索引
ALTER TABLE products ADD INDEX idx_category_price (category_id, price DESC);

-- GROUP BY优化
-- 坏的写法
SELECT user_id, COUNT(*) FROM orders 
GROUP BY user_id 
HAVING COUNT(*) > 10;

-- 优化：先过滤再分组
SELECT user_id, cnt FROM (
  SELECT user_id, COUNT(*) as cnt 
  FROM orders 
  GROUP BY user_id
) t WHERE cnt > 10;

4.3.5 子查询优化

MySQL对子查询的优化一直是个短板。我建议能用JOIN就用JOIN，别用子查询。

-- 坏的写法：子查询
SELECT * FROM products 
WHERE id IN (
  SELECT product_id FROM order_items 
  WHERE order_id IN (1,2,3)
);

-- 优化：改为JOIN
SELECT DISTINCT p.* 
FROM products p 
JOIN order_items oi ON p.id = oi.product_id 
WHERE oi.order_id IN (1,2,3);

好了，慢查询优化这块就讲这么多。记住一个原则：先分析，后优化，再验证。别一上来就改SQL，先用pt-query-digest看看问题到底出在哪。我这些年踩过的坑，十有八九都是因为没做好分析就动手。