首页 / MySQL 入门教程 / 慢查询分析与性能调优

← 上一篇 下一篇 →
MySQL 入门教程

慢查询分析与性能调优

本教程共 46 篇 · 第 43 篇 · 更新于 2026-07-30 · 约 13 分钟阅读

MySQLMySQL 入门教程慢查询性能调优EXPLAIN深分页索引优化SQL优化

43. 慢查询分析与性能调优

本节目标：学会开启和分析慢查询日志找到慢 SQL，掌握减少回表、优化 JOIN、深分页、COUNT 等常见调优手法，能把一条慢查询优化到合理水平。

43.1 慢查询日志

慢查询日志（Slow Query Log） 记录所有执行时间超过阈值的 SQL。这是发现慢查询的第一手段。

开启慢查询日志

-- 查看状态
SHOW VARIABLES LIKE 'slow_query_log%';
-- slow_query_log = OFF（默认关闭）
-- slow_query_log_file = /var/lib/mysql/hostname-slow.log

-- 开启
SET GLOBAL slow_query_log = ON;

-- 设置阈值（秒），默认 10 秒
SET GLOBAL long_query_time = 1;  -- 超过 1 秒就记录

-- 设置日志文件位置
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
Note

SET GLOBAL 只对当前运行实例有效，重启后失效。永久生效需要写入配置文件（my.cnf 或 my.ini），下一节会讲。

配置文件方式（永久生效）

[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1  -- 记录没走索引的查询

分析慢查询日志

-- 查看慢查询日志中有多少条记录
SHOW VARIABLES LIKE 'slow_query_log%';

-- 日志文件是文本文件，可以用 mysqldumpslow 工具分析
-- 命令行执行：
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
-- -s t 按时间排序，-t 10 显示前 10 条

mysqldumpslow 常用参数：

参数含义
-s t按总耗时排序
-s r按返回行数排序
-s c按出现次数排序
-t 10只显示前 10 条
Tip

我之前排查线上慢查询，用 mysqldumpslow -s t -t 10 一跑，排第一的 SQL 占了总耗时的 60%。一条 SQL 优化好，整体性能就上去了。

43.2 调优基本流程

拿到一条慢 SQL 后，按以下步骤排查：

  1. EXPLAIN 分析：看 type、key、rows、Extra；
  2. 判断是否走索引：key 为 NULL 说明没走索引；
  3. 检查索引失效：函数操作、隐式转换、LIKE ‘%xxx’ 等；
  4. 检查扫描行数：rows 远大于实际需要说明索引选择性差；
  5. 检查 Extra：Using filesort、Using temporary 需要优化；
  6. 优化 SQL 或索引：加索引、改写 SQL、减少回表。

43.3 减少 SELECT * 和回表

-- 差：SELECT * 需要所有列，必然回表
SELECT * FROM users WHERE age = 25;

-- 好：只查需要的列，可能用上覆盖索引
SELECT id, username FROM users WHERE age = 25;
-- 如果有联合索引 (age, username)，Extra: Using index，不回表
Warning

SELECT * 是性能杀手。它不仅可能导致回表，还增加了网络传输量。养成只查需要列的习惯。

43.4 优化 JOIN 查询

JOIN 性能取决于被驱动表（JOIN 右边的表）是否走索引。

-- 差：orders 表的 user_id 没有索引，被驱动表全表扫描
EXPLAIN SELECT u.username, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 20;
-- o 表 type=ALL，Using join buffer
-- 好：给 orders.user_id 建索引
CREATE INDEX idx_uid ON orders(user_id);

EXPLAIN SELECT u.username, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 20;
-- o 表 type=ref，走索引

JOIN 优化原则：

  • 被驱动表的 JOIN 条件列必须有索引；
  • 小表驱动大表（小表做驱动表）；
  • JOIN 表数量不超过 3 张，多了拆分查询。
Note

MySQL 优化器会自动选择小表做驱动表（Nested Loop Join）。但如果两张表都很大，JOIN 效率会很差。考虑用 Hash Join（MySQL 8.0.18+ 自动选择）或分批查询。

43.5 深分页优化

-- 传统分页：LIMIT 偏移量很大时很慢
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
-- MySQL 要扫描前 100010 行，丢弃前 100000 行，只返回 10 行

偏移量越大，扫描的无效行越多，越慢。

优化方案一：延迟关联

-- 先通过覆盖索引查出主键，再关联回表
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 10
) t ON o.id = t.id;
-- 子查询走覆盖索引（只扫主键），再精确回表 10 行

优化方案二：记录上次最大 ID

-- 第一页
SELECT * FROM orders WHERE id > 0 ORDER BY id LIMIT 10;
-- 假设最后一条 id=10

-- 第二页：直接从上次最大 id 开始
SELECT * FROM orders WHERE id > 10 ORDER BY id LIMIT 10;
-- 不需要偏移，直接定位
Tip

记录上次最大 ID 的方案性能最好，但只适用于连续翻页（不能跳页）。适合下拉加载更多的场景。如果必须跳页，用延迟关联。

方案适用场景优点缺点
传统 LIMIT偏移量小简单深分页慢
延迟关联必须跳页减少回表SQL 稍复杂
记录上次 ID连续翻页性能最优不能跳页

43.6 COUNT 优化

-- COUNT(*) 统计总行数
SELECT COUNT(*) FROM users;
-- InnoDB 需要扫描整张表（或索引），大表很慢

InnoDB 的 COUNT(*) 没有 MyISAM 那样的计数器，需要实际扫描。优化思路：

-- 方案一：用 SHOW TABLE STATUS 估算（不精确但快）
SHOW TABLE STATUS LIKE 'users';
-- Rows 字段是估算值

-- 方案二：用缓存或汇总表
-- 维护一张统计表，定时更新
CREATE TABLE table_stats (table_name VARCHAR(50), row_count INT);
-- 业务层从统计表取值

-- 方案三：只查有索引的列
SELECT COUNT(id) FROM users;  -- 走主键索引扫描，比 COUNT(*) 稍快
Note

COUNT(*)、COUNT(1)、COUNT(主键) 在 InnoDB 中性能差不多，优化器会选择最小的索引扫描。COUNT(列) 不统计 NULL 值，语义不同。需要精确总数的大表场景，建议用汇总表。

43.7 避免在索引列上运算

-- 差：索引列上用了函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-30';
-- 索引失效，全表扫描

-- 好：改写为范围查询
SELECT * FROM orders
WHERE created_at >= '2026-07-30 00:00:00'
  AND created_at < '2026-07-31 00:00:00';
-- 走索引范围扫描
-- 差：索引列上做运算
SELECT * FROM orders WHERE amount / 2 = 50;
-- 索引失效

-- 好：把运算移到等号右边
SELECT * FROM orders WHERE amount = 50 * 2;
-- 走索引

43.8 优化 GROUP BY 和 ORDER BY

-- 差：GROUP BY 的列没有索引
SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Extra: Using temporary; Using filesort

-- 好：给 GROUP BY 的列建索引
CREATE INDEX idx_status ON orders(status);
SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Extra: Using index（覆盖索引 + 索引有序，不需要临时表和排序）

ORDER BY 同理：

-- 差：ORDER BY 的列没有索引
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
-- Extra: Using filesort

-- 好：给排序列建索引
CREATE INDEX idx_created ON users(created_at);
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
-- 利用索引的有序性，不需要 filesort
Warning

ORDER BY 的方向必须和索引方向一致。如果索引是 ASC，ORDER BY DESC 可能仍然需要 filesort（MySQL 8.0+ 支持降序索引可以解决这个问题）。

43.9 批量操作优化

-- 差：循环单条插入
INSERT INTO users(username, email) VALUES('user1', 'user1@test.com');
INSERT INTO users(username, email) VALUES('user2', 'user2@test.com');
INSERT INTO users(username, email) VALUES('user3', 'user3@test.com');
-- 每条一个事务，三次网络往返

-- 好：批量插入
INSERT INTO users(username, email) VALUES
('user1', 'user1@test.com'),
('user2', 'user2@test.com'),
('user3', 'user3@test.com');
-- 一个事务，一次网络往返

批量 UPDATE/DELETE 也要分批：

-- 差：一次性删除 100 万条
DELETE FROM logs WHERE created_at < '2025-01-01';
-- 锁多、undo log 大、可能超时

-- 好：分批删除
DELETE FROM logs WHERE created_at < '2025-01-01' LIMIT 1000;
-- 重复执行直到 affected_rows = 0

43.10 SQL 优化速查表

优化点差的写法好的写法
查询列SELECT *只查需要的列
索引列运算WHERE DATE(col) = ...WHERE col >= ... AND col < ...
隐式转换WHERE int_col = '1'WHERE int_col = 1
LIKELIKE '%abc'LIKE 'abc%'
深分页LIMIT 100000, 10延迟关联或记录上次 ID
JOIN被驱动表无索引给 JOIN 条件列建索引
批量插入循环单条 INSERT一条 INSERT 多值
批量删除一次删百万行分批 LIMIT 删除
GROUP BY无索引给分组列建索引
ORDER BY无索引给排序列建索引

43.11 小结

  • 慢查询日志是发现慢 SQL 的第一手段，long_query_time 设 1 秒比较合理；
  • 调优流程：EXPLAIN -> 看是否走索引 -> 检查索引失效 -> 优化 SQL 或索引；
  • SELECT * 导致回表和传输浪费，只查需要的列；
  • JOIN 被驱动表的关联列必须有索引；
  • 深分页用延迟关联或记录上次 ID 优化；
  • COUNT 大表用估算或汇总表；
  • 避免在索引列上用函数和运算；
  • GROUP BY 和 ORDER BY 的列建索引可消除临时表和文件排序；
  • 批量操作用多值 INSERT 和分批 DELETE/UPDATE。

下一节学用户与权限管理，掌握 MySQL 的账号创建和权限分配。

上一篇
执行计划与 EXPLAIN
下一篇
用户与权限管理