一、识别慢 SQL
1. 启用慢查询日志
-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';-- 开启慢查询日志（临时生效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 设置慢查询阈值（秒）
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';-- 永久生效需修改 my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1查看慢查询日志内容:使用系统命令查看日志文件（Linux）
假设你的日志文件在 /var/lib/mysql/slow.log，可以直接用 tail、cat 或 less 查看：
tail -f /var/lib/mysql/slow.log # 实时查看新增的日志
cat /var/lib/mysql/slow.log # 查看全部内容
less /var/lib/mysql/slow.log # 分页查看示例日志格式如下：
# Time: 2025-06-20T17:30:45.123456Z
# User@Host: root[root] @ localhost []
# Query_time: 3.123456 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1000000
SET timestamp=1750486245;
SELECT * FROM users WHERE username = 'test';- Query_time: 查询耗时（秒），超过
long_query_time才会被记录。 - Rows_examined: 扫描行数，越大越可能需要优化。
- SQL语句: 实际执行的 SQL。
2. 使用性能分析工具
-- 查看当前运行中的慢查询
SHOW FULL PROCESSLIST;-- 使用 EXPLAIN 分析执行计划
EXPLAIN SELECT * FROM orders WHERE customer_id = 1000;-- 使用 EXPLAIN ANALYZE（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM products WHERE price > 100;-- 性能分析（临时启用）
SET profiling = 1;
SELECT * FROM large_table;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;3.使用 EXPLAIN 分析 SQL 执行计划的详细指南
基础用法（直接在 SQL 前加 EXPLAIN）
EXPLAIN SELECT *
FROM orders
WHERE user_id = 100 AND status = 'completed'
ORDER BY created_at DESC
LIMIT 10;输出结果示例及关键列解析：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | orders | NULL | ref | idx_user_id | idx_user | 4 | const | 500 | 10.00 | Using where; Using filesort |
核心列深度解析：
-
type（访问类型） - 最重要指标
-
性能排序：
system>const>eq_ref>ref>range>index>ALL -
优化目标：至少达到
range级别，避免ALL（全表扫描） -
示例诊断：
ref表示使用了非唯一索引
-
-
key（实际使用索引）
-
检查是否使用预期索引：
idx_user（实际使用） vsidx_user_status（可能更好的索引） -
若为
NULL表示未使用索引 → 需优化
-
-
rows（扫描行数）
-
预估扫描 500 行 → 结合
filtered列，实际返回约 500 × 10% = 50 行 -
目标：减少该值
-
-
Extra（额外信息） - 问题高发区
-
Using filesort：手动文件排序（需优化） -
Using temporary：创建临时表（需优化） -
Using index：覆盖索引（好现象） -
Using where：存储引擎返回后再次过滤
-
| 类型 (type) | 性能排序 | 含义描述 | 典型场景 | 扫描方式 | 扫描行数 | 是否用索引 | 优化建议 |
|---|---|---|---|---|---|---|---|
| system | ★★★★★ 最佳 | 系统表/仅有一行记录 | • MyISAM/Inemory引擎的单行系统表 • 衍生表只有一行数据 | 直接访问系统记录 | 1 | ✓ | 无需优化 |
| const | ★★★★☆ 极优 | 主键/唯一索引的单行访问 | • WHERE id = 1 (主键查询)• WHERE unique_col = 'value' (唯一索引) | 常量扫描 直接定位单行 | 1 | ✓ | 确保主键/唯一索引有效 |
| eq_ref | ★★★★☆ 极优 | 主键关联查询 (多表JOIN时) | • JOIN ... ON t1.primary_key = t2.primary_key• 驱动表每行匹配被驱动表主键 | 唯一索引扫描 每行只匹配一次 | 1/N (N=驱动表行数) | ✓ | 确保JOIN字段是主键或唯一索引 |
| ref | ★★★☆☆ 良好 | 非唯一索引的等值查询 | • WHERE index_col = 'value'• 普通索引关联查询 • 最左前缀匹配查询 | 索引范围扫描 可能返回多行 | N (匹配行数) | ✓ | 提升索引选择性 添加组合索引 |
| range | ★★☆☆☆ 中等 | 索引范围扫描 | • WHERE id > 100• BETWEEN 100 AND 200• IN (1,2,3)• LIKE 'prefix%' | 索引部分扫描 指定范围内遍历 | M (范围行数) | ✓ | 控制扫描行数<1万 避免大范围扫描 |
| index | ★☆☆☆☆ 较差 | 全索引扫描 | • SELECT indexed_col FROM table (覆盖索引)• ORDER BY indexed_col (无WHERE条件)• 索引全扫描 | 遍历整个索引树 不读数据文件 | 全索引 (索引条目数) | ✓ | 确认是否需回表 检查排序必要性 |
| ALL | ⚠️ 最差 | 全表扫描 | • 无索引字段查询：WHERE non_index_col=...• 前导通配符： LIKE '%value%'• 未优化的JOIN条件 | 逐行扫描数据文件 性能灾难 | 全表 (数据行数) | ✗ | 紧急优化！ • 添加索引 • 重写SQL • 限制结果集 |
通过 EXPLAIN 分析后，若出现 ALL 或 index 且扫描行数>1000,需优化
-
优先关注 type 避免全表扫描
-
重点检查 Extra 消除警告项
-
通过 rows 评估执行成本
-
结合 key_len 判断索引利用率
-
每次优化后必须重新执行 EXPLAIN 验证效果
二、慢 SQL 的常见原因
1. 索引问题
-- 缺失索引
SELECT * FROM users WHERE last_name = 'Smith'; -- 无索引-- 索引失效
SELECT * FROM orders WHERE YEAR(order_date) = 2023; -- 函数导致索引失效2. 全表扫描
-- 未使用索引导致全表扫描
SELECT * FROM products WHERE category LIKE '%electronics%';3. 复杂 JOIN
-- 多表 JOIN 未优化
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE c.country = 'USA' AND p.price > 100;4. 大表分页
-- 低效的分页查询
SELECT * FROM logs ORDER BY id LIMIT 1000000, 20;5.低效子查询
-- 低效的子查询
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'China'
);三、慢 SQL 优化方案
1. 索引优化策略
-- 添加必要索引
CREATE INDEX idx_last_name ON users(last_name);
CREATE INDEX idx_order_date ON orders(order_date);-- 使用覆盖索引
SELECT customer_id, order_date FROM orders; -- 建立 (customer_id, order_date) 索引-- 索引合并优化
SELECT * FROM products
WHERE category_id = 5 OR price > 100; -- 分别建立两个索引-- 强制索引使用
SELECT * FROM orders FORCE INDEX (idx_order_date)
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';2. SQL 重写
-- 避免 SELECT *
SELECT id, name, email FROM users;-- 分页优化
SELECT * FROM logs WHERE id > 1000000 ORDER BY id LIMIT 20;-- JOIN 优化
SELECT o.id, c.name, p.product_name
FROM orders o
FORCE INDEX (idx_customer)
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE c.country = 'USA';-- 子查询转 JOIN
SELECT o.*
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'China';-- 避免函数操作
SELECT * FROM orders
WHERE order_date >= '2023-01-01'
AND order_date < '2023-02-01';3. 表结构优化
数据库分库分表:垂直拆分/水平拆分
4. 架构级优化
-
读写分离：写操作到主库，读操作到从库
-
缓存层：使用 Redis 缓存热点数据
-
搜索引擎：复杂查询使用 Elasticsearch
)
 附源码)
