知识模块
☕ Java 知识模块
十二、性能优化
慢查询优化

慢查询优化

面试高频考点：慢查询日志配置、SQL 优化技巧、执行计划分析、实际案例分析

一、慢查询日志

开启慢查询日志

-- 查看慢查询配置
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
 
-- 临时开启（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 记录未使用索引的查询
 
-- 永久配置（my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100  -- 扫描行数少于 100 不记录

分析慢查询日志

# 使用 mysqldumpslow 工具
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
 
# 参数说明
-s t  # 按查询时间排序
-s c  # 按查询次数排序
-s l  # 按锁定时间排序
-s r  # 按返回记录数排序
-t 10 # 显示前 10 条
 
# 示例输出
Count: 100  Time=2.50s (250s)  Lock=0.00s (0s)  Rows=1000.0 (100000)
SELECT * FROM orders WHERE status = 'S'
 
# 解读：该 SQL 执行了 100 次，平均耗时 2.5 秒

使用 pt-query-digest（推荐）

# 安装 Percona Toolkit
# yum install percona-toolkit
 
# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
 
# 输出示例
# 1. 按查询时间排名的 SQL
# 2. 每条 SQL 的详细统计
# 3. EXPLAIN 分析建议

二、EXPLAIN 执行计划

EXPLAIN 基础

-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;
 
-- JSON 格式（更详细）
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 1001;
 
-- 实际执行分析（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1001;

EXPLAIN 输出字段

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

type 访问类型（从好到差）

type说明示例
system单行系统表SELECT * FROM mysql.proxies_priv
const主键/唯一索引常量查询WHERE id = 1
eq_ref主键/唯一索引连接JOIN ON t1.id = t2.id
ref非唯一索引等值查询WHERE name = '张三'
fulltext全文索引WHERE MATCH(content) AGAINST('关键词')
ref_or_null索引查询或 NULLWHERE name = '张三' OR name IS NULL
range索引范围扫描WHERE age > 20
index索引全扫描SELECT id FROM t
ALL全表扫描SELECT * FROM t WHERE status + 1 = 2

Extra 额外信息

Extra说明优化建议
Using index覆盖索引好，无需优化
Using where服务器过滤检查是否可下推
Using index condition索引下推好
Using temporary临时表检查 GROUP BY/ORDER BY
Using filesort文件排序添加排序索引
Using join buffer连接缓冲添加连接索引
Impossible WHERE条件不可能检查 SQL 逻辑

三、SQL 优化技巧

1. 避免 SELECT *

-- 问题
SELECT * FROM orders WHERE user_id = 1001;
 
-- 优化
SELECT id, order_no, amount, status FROM orders WHERE user_id = 1001;
 
-- 原因：
-- 1. 减少网络传输
-- 2. 可能使用覆盖索引
-- 3. 减少内存占用

2. 避免在索引列上运算

-- 问题
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-15';
 
-- 优化
SELECT * FROM orders 
WHERE create_time >= '2024-01-15 00:00:00' 
  AND create_time < '2024-01-16 00:00:00';
 
-- 问题
SELECT * FROM orders WHERE amount + 100 > 1000;
 
-- 优化
SELECT * FROM orders WHERE amount > 900;

3. 避免 LIKE 左模糊

-- 问题
SELECT * FROM users WHERE name LIKE '%张%';
 
-- 优化方案
-- 1. 使用右模糊（可用索引）
SELECT * FROM users WHERE name LIKE '张%';
 
-- 2. 使用全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张');
 
-- 3. 使用 Elasticsearch

4. 避免 OR 导致索引失效

-- 问题（假设 status 没有索引）
SELECT * FROM orders WHERE user_id = 1001 OR status = 1;
 
-- 优化方案 1：UNION
SELECT * FROM orders WHERE user_id = 1001
UNION
SELECT * FROM orders WHERE status = 1;
 
-- 优化方案 2：给两个字段都建索引
CREATE INDEX idx_userid ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
-- MySQL 可能使用 index merge

5. 避免 != 和 <>

-- 问题
SELECT * FROM orders WHERE status != 1;
 
-- 优化
SELECT * FROM orders WHERE status IN (0, 2, 3, 4);

6. 避免 NULL 值判断

-- 问题
SELECT * FROM users WHERE name IS NULL;
 
-- 优化：设置默认值
ALTER TABLE users MODIFY name VARCHAR(50) NOT NULL DEFAULT '';
SELECT * FROM users WHERE name = '';

7. 优化 LIMIT 分页

-- 问题
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
 
-- 优化：延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) t
ON o.id = t.id;
 
-- 优化：游标分页
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

8. 优化 ORDER BY

-- 问题
SELECT * FROM orders WHERE user_id = 1001 ORDER BY create_time DESC;
-- Extra: Using filesort
 
-- 优化：创建联合索引
CREATE INDEX idx_userid_createtime ON orders(user_id, create_time);
-- Extra: Using index

9. 优化 GROUP BY

-- 问题
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;
-- Extra: Using temporary; Using filesort
 
-- 优化：创建索引
CREATE INDEX idx_userid ON orders(user_id);
-- Extra: Using index
 
-- 如果不需要排序，可以禁止排序
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id ORDER BY NULL;

10. 优化 COUNT

-- 问题
SELECT COUNT(*) FROM orders;
 
-- 优化方案 1：使用覆盖索引
SELECT COUNT(id) FROM orders;  -- 如果 id 是主键
 
-- 优化方案 2：维护计数表
CREATE TABLE table_counts (table_name VARCHAR(50), row_count BIGINT);
UPDATE table_counts SET row_count = row_count + 1 WHERE table_name = 'orders';
 
-- 优化方案 3：估算（InnoDB）
SHOW TABLE STATUS LIKE 'orders';

11. 优化 IN

-- IN 值过多可能导致索引失效
-- 问题
SELECT * FROM orders WHERE user_id IN (1, 2, 3, ..., 10000);
 
-- 优化方案 1：分批查询
-- 优化方案 2：使用 EXISTS
SELECT * FROM orders o 
WHERE EXISTS (SELECT 1 FROM temp_users t WHERE t.id = o.user_id);
 
-- 优化方案 3：使用 JOIN
SELECT o.* FROM orders o
INNER JOIN temp_users t ON o.user_id = t.id;

12. 使用批量操作

-- 问题：循环插入
INSERT INTO orders (order_no, amount) VALUES ('001', 100);
INSERT INTO orders (order_no, amount) VALUES ('002', 200);
-- ...
 
-- 优化：批量插入
INSERT INTO orders (order_no, amount) VALUES 
('001', 100),
('002', 200),
('003', 300);
<!-- MyBatis 批量插入 -->
<insert id="batchInsert">
    INSERT INTO orders (order_no, amount) VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.orderNo}, #{item.amount})
    </foreach>
</insert>

四、实战案例分析

案例 1：复杂查询优化

问题 SQL：

SELECT o.*, u.name, p.product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.status = 1
  AND o.create_time >= '2024-01-01'
ORDER BY o.create_time DESC
LIMIT 10;

执行计划：

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------+
| id | select_type | table | type       | key  | ref           | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------+------+--------+----------+--------------------------------+
|  1 | SIMPLE      | o     | ALL        | NULL | NULL          | 100000| 10.00   | Using where; Using filesort    |
|  1 | SIMPLE      | u     | eq_ref     | PRIMARY | o.user_id | 1   | 100.00  |                                |
|  1 | SIMPLE      | p     | eq_ref     | PRIMARY | o.product_id | 1 | 100.00 |                              |
+----+-------------+-------+------------+------+---------+------+--------+----------+--------------------------------+

问题分析：

  • orders 表全表扫描 (type: ALL)
  • 使用了文件排序 (Using filesort)

优化方案：

-- 创建联合索引
CREATE INDEX idx_status_createtime ON orders(status, create_time);
 
-- 创建覆盖索引（如果只需要部分字段）
CREATE INDEX idx_status_createtime_cover ON orders(status, create_time, user_id, product_id);

优化后执行计划：

+----+-------------+-------+------------+-------+---------------------------+---------+------+------+----------+-------------+
| id | select_type | table | type       | key   | ref                       | rows    | filtered | Extra   |
+----+-------------+-------+------------+-------+---------------------------+---------+------+----------+-------------+
|  1 | SIMPLE      | o     | ref        | idx_status_createtime | const | 1000 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------------------+---------+------+----------+-------------+

案例 2：子查询优化

问题 SQL：

SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE status = 1);

问题分析：

  • MySQL 5.6 之前，子查询会导致全表扫描
  • MySQL 5.6+ 会优化为 semi-join

优化方案：

-- 方案 1：使用 JOIN
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.status = 1;
 
-- 方案 2：使用 EXISTS（适合外层表数据少的情况）
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 1);

案例 3：大表删除优化

问题：需要删除 orders 表中 100 万条历史数据

-- 问题：大事务删除
DELETE FROM orders WHERE create_time < '2023-01-01';  -- 100 万条
 
-- 问题：
-- 1. 长时间锁表
-- 2. 大量 undo log
-- 3. 主从延迟

优化方案：

-- 方案 1：分批删除
DELETE FROM orders WHERE create_time < '2023-01-01' LIMIT 1000;
-- 循环执行，直到影响行数为 0
 
-- 方案 2：使用存储过程
DELIMITER //
CREATE PROCEDURE batch_delete()
BEGIN
    DECLARE rows_affected INT DEFAULT 1;
    WHILE rows_affected > 0 DO
        DELETE FROM orders WHERE create_time < '2023-01-01' LIMIT 1000;
        SET rows_affected = ROW_COUNT();
        DO SLEEP(0.1);  -- 短暂休息，减少锁竞争
    END WHILE;
END //
DELIMITER ;
 
-- 方案 3：重建表（适合删除大量数据）
-- 1. 创建新表，保留需要的数据
CREATE TABLE orders_new LIKE orders;
INSERT INTO orders_new SELECT * FROM orders WHERE create_time >= '2023-01-01';
-- 2. 重命名表
RENAME TABLE orders TO orders_old, orders_new TO orders;
-- 3. 删除旧表
DROP TABLE orders_old;

案例 4：COUNT 优化

问题：

-- 慢查询
SELECT COUNT(*) FROM orders WHERE status = 1;

优化方案：

-- 方案 1：使用覆盖索引
CREATE INDEX idx_status ON orders(status);
 
-- 方案 2：维护计数表
CREATE TABLE order_stats (
    status INT,
    count BIGINT,
    PRIMARY KEY (status)
);
 
-- 使用触发器或应用层维护
INSERT INTO order_stats VALUES (1, 0) ON DUPLICATE KEY UPDATE count = count + 1;
 
-- 方案 3：近似统计
EXPLAIN SELECT * FROM orders WHERE status = 1;
-- 查看 rows 字段
 
-- 方案 4：Redis 计数
INCR order:status:1:count

五、SQL 优化检查清单

□ 是否使用了 SELECT *？
□ WHERE 条件是否有索引？
□ 是否有索引列运算/函数？
□ LIKE 是否左模糊？
□ OR 条件是否导致索引失效？
□ 是否有 != 或 <>？
□ ORDER BY 是否使用了文件排序？
□ GROUP BY 是否使用了临时表？
□ LIMIT 深分页是否优化？
□ JOIN 是否有索引？
□ 是否有小表驱动大表？
□ IN 值是否过多？
□ 是否有批量操作？
□ 子查询是否可以改写为 JOIN？
□ COUNT(*) 是否可以优化？

六、常用监控工具

1. MySQL 慢查询日志

# 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

2. Performance Schema

-- 启用性能监控
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%statement/%';
 
-- 查询最慢 SQL
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

3. sys schema

-- 查看慢查询
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;
 
-- 查看全表扫描
SELECT * FROM sys.statements_with_full_table_scans;
 
-- 查看未使用索引
SELECT * FROM sys.schema_unused_indexes;

4. 第三方工具

  • pt-query-digest：分析慢查询日志
  • MySQL Tuner：MySQL 配置优化建议
  • Percona Monitoring and Management (PMM)：监控平台
  • Grafana + Prometheus：可视化监控

七、面试要点

Q1: 如何定位慢查询？

回答要点：

  1. 开启慢查询日志
  2. 设置 long_query_time 阈值
  3. 使用 mysqldumpslow 或 pt-query-digest 分析
  4. 使用 EXPLAIN 分析执行计划

Q2: 如何优化慢查询？

回答要点：

  1. 分析 EXPLAIN 执行计划
  2. 检查是否使用索引
  3. 避免 SELECT *
  4. 避免索引列运算
  5. 优化 ORDER BY、GROUP BY
  6. 使用覆盖索引

Q3: type 字段有哪些值？从好到差排序？

回答要点： system > const > eq_ref > ref > range > index > ALL

Q4: Using filesort 是什么？如何优化？

回答要点：

  • 含义：MySQL 需要在内存/磁盘上额外排序
  • 优化：为 ORDER BY 列创建索引
  • 联合索引遵循最左前缀原则

Q5: 如何优化大批量数据删除？

回答要点：

  1. 分批删除，每次少量
  2. 避免长事务
  3. 重建表（适合删除大量数据）
  4. 考虑主从延迟

小结

  • 开启慢查询日志定位慢 SQL
  • EXPLAIN 分析执行计划
  • 关注 type、key、rows、Extra 字段
  • 避免 SELECT *、索引列运算、左模糊
  • 优化 ORDER BY、GROUP BY、LIMIT
  • 使用覆盖索引、批量操作
  • 定期检查慢查询日志，持续优化
缓存策略优化概述