慢查询定位与优化

慢查询是系统性能问题的主要来源。本文从慢查询日志配置、定位分析到优化方案，给出完整的排查流程和实战优化策略，帮助快速解决生产环境 SQL 性能问题。

慢查询日志配置

MySQL 慢查询日志开启

SQL

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 输出示例：
-- slow_query_log        | ON
-- slow_query_log_file   | /var/lib/mysql/slow.log
-- long_query_time       | 2.000000

参数	含义	推荐值
slow_query_log	是否开启慢查询日志	ON
slow_query_log_file	日志文件路径	按需指定
long_query_time	超过此时间（秒）记录为慢查询	1 ~ 2 秒
log_queries_not_using_indexes	记录未使用索引的查询	ON（开发环境建议开启）

SQL

-- 动态开启（无需重启 MySQL）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';

注意：动态设置的配置在 MySQL 重启后失效。永久生效需在 my.cnf / my.ini 中配置。

永久配置（my.cnf）

ini

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

慢查询日志分析工具

mysqldumpslow

MySQL 自带的慢查询日志分析工具，可聚合相似 SQL：

Bash

# 按查询时间排序，取前 10 条
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# 按执行次数排序
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

# 按锁定时间排序
mysqldumpslow -s l -t 10 /var/lib/mysql/slow.log

# 带详细 SQL 文本
mysqldumpslow -s t -t 5 -a /var/lib/mysql/slow.log

输出示例：

Bash

Count: 152  Time=3.45s (524s)  Lock=0.00s (0s)  Rows=100.0 (15200), user@host
  SELECT * FROM user WHERE status = 'N' ORDER BY create_time DESC LIMIT N

Count: 89   Time=2.10s (186s)  Lock=0.01s (0s)  Rows=1.0 (89), user@host
  SELECT u.*, o.* FROM user u LEFT JOIN order o ON u.id = o.user_id WHERE u.id = 'N'

pt-query-digest

Percona Toolkit 提供的更强大的分析工具：

YAML

# 分析慢查询日志，输出报告
pt-query-digest /var/lib/mysql/slow.log

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

# 输出为 JSON 格式
pt-query-digest --output json /var/lib/mysql/slow.log > report.json

输出报告包含：

总体统计：慢查询总数、总执行时间、平均执行时间
Top N 慢 SQL：按 Query_time 排序
每个 SQL 的详细统计：执行次数、平均耗时、最大/最小耗时、扫描行数

监控工具定位慢查询

Prometheus + Grafana 监控

通过 mysqld_exporter 采集 MySQL 指标，Grafana 展示：

监控面板	指标	告警阈值
慢查询速率	`mysql_global_status_slow_queries`	> 10/min
查询耗时	`mysql_global_status_queries` + 延迟	P99 > 2s
连接数使用率	`mysql_global_status_threads_connected`	> 80% max_connections
InnoDB 行锁等待	`mysql_global_status_innodb_row_lock_waits`	> 5/min
临时表创建率	`mysql_global_status_created_tmp_tables`	异常突增

Spring Boot Actuator 指标

Java

management:
  endpoints:
    web:
      exposure:
        include: metrics,health
  metrics:
    tags:
      application: ${spring.application.name}

通过 Micrometer 采集数据源指标：

SQL

@Configuration
public class MetricsConfig {

    @Bean
    public MeterBinder dataSourceMetrics(DataSource dataSource) {
        return (registry) -> {
            // 自定义数据源监控指标
            if (dataSource instanceof HikariDataSource) {
                HikariDataSource hds = (HikariDataSource) dataSource;
                Gauge.builder("hikari.active.connections", hds, HikariDataSource::getHikariPoolMXBean)
                    .register(registry);
            }
        };
    }
}

慢查询优化流程

标准排查步骤

SQL

1. 查看慢查询日志 → 找出执行时间长的 SQL
2. EXPLAIN 分析执行计划 → 确认是否全表扫描
3. 检查索引使用情况 → 是否有合适索引、索引是否失效
4. 查看表结构 → 字段类型是否合理、数据量是否过大
5. 改写 SQL / 添加索引 → 执行优化
6. 重新 EXPLAIN 验证 → 确认优化效果
7. 监控观察 → 确认生产环境恢复正常

索引创建策略

何时创建索引

场景	是否建议建索引	说明
WHERE 条件频繁使用的字段	是	减少全表扫描
JOIN ON 连接的字段	是	避免 join buffer
ORDER BY 排序的字段	是	避免 filesort
GROUP BY 分组的字段	是	避免 temporary
DISTINCT 去重的字段	是	提高去重效率
数据量 < 1,000 行	否	全表扫描成本低，索引收益不大
频繁 UPDATE 的字段	谨慎	每次更新需维护索引
区分度极低的字段（如性别）	否	索引选择率低，优化器可能不使用

索引选择率（Selectivity）

SQL

-- 计算字段选择率
SELECT COUNT(DISTINCT status) / COUNT(*) AS selectivity FROM user;
-- 结果：0.02（区分度极低，不适合单独建索引）

SELECT COUNT(DISTINCT username) / COUNT(*) AS selectivity FROM user;
-- 结果：0.85（区分度高，适合建索引）

选择率 > 0.1 的字段适合建立索引。

联合索引设计原则

SQL

-- 高频查询：WHERE status = 1 AND city = 'Beijing' ORDER BY create_time DESC
-- 联合索引：(status, city, create_time)

CREATE INDEX idx_status_city_time ON user(status, city, create_time);

联合索引字段排序原则：

等值匹配字段优先（WHERE a = ? AND b = ?）
区分度高的字段靠前
排序/分组字段放最后

注意：MySQL 8.0 之后支持降序索引（DESC），之前版本 CREATE INDEX ... (a ASC, b DESC) 中的 DESC 会被忽略，索引仍按 ASC 存储。

覆盖索引设计

SQL

-- 场景：频繁查询用户列表，只需展示 id、username、avatar
SELECT id, username, avatar FROM user WHERE status = 1 ORDER BY create_time DESC LIMIT 20;

-- 覆盖索引：将查询涉及的字段全部包含
CREATE INDEX idx_cover ON user(status, create_time, id, username, avatar);
-- Extra = Using index，无需回表查询数据行

SQL 改写优化

1. 避免 SELECT *

SQL

-- 不推荐：返回所有字段，增加网络和 IO 开销
SELECT * FROM user WHERE status = 1;

-- 推荐：只查询需要的字段
SELECT id, username, email FROM user WHERE status = 1;

SELECT * 的问题：

返回无用字段增加网络传输开销
无法使用覆盖索引，必须回表
大字段（TEXT/BLOB）导致内存暴涨

2. LIMIT 深分页优化

SQL

-- 深分页：LIMIT 100000, 20 需扫描前 100,020 行，丢弃前 100,000 行
SELECT * FROM user ORDER BY id LIMIT 100000, 20;

-- 优化方案 1：子查询 + JOIN（利用索引快速定位起始行）
SELECT u.* FROM user u
INNER JOIN (SELECT id FROM user ORDER BY id LIMIT 100000, 20) t
ON u.id = t.id;

-- 优化方案 2：游标分页（记录上次查询的 id）
SELECT * FROM user WHERE id > 100000 ORDER BY id LIMIT 20;

-- 优化方案 3：延迟关联（先通过覆盖索引定位，再回表取完整数据）
SELECT u.* FROM user u
INNER JOIN (
    SELECT id FROM user ORDER BY id LIMIT 100000, 20
) t ON u.id = t.id;

方案	适用场景	说明
子查询 + JOIN	通用场景	减少回表次数
游标分页	瀑布流、滚动加载	需客户端传递上次 id
延迟关联	数据量大且含大字段	先在索引树定位，再精确回表

3. COUNT 优化

SQL

-- COUNT(*) 和 COUNT(1) 在 InnoDB 中性能相同，优化器会自动选择最优索引
SELECT COUNT(*) FROM user WHERE status = 1;

-- COUNT(column) 会排除 NULL 值，语义不同
SELECT COUNT(email) FROM user; -- 不包含 email 为 NULL 的行

-- 精确计数（无需 COUNT 全部数据）
-- 使用 Redis 等外部缓存维护计数器，避免每次都查库

4. IN 子查询优化

XML

-- 不推荐：IN 子查询在旧版本 MySQL 中优化不佳
SELECT * FROM user WHERE id IN (SELECT user_id FROM order WHERE status = 'PAID');

-- 优化：改为 JOIN
SELECT u.* FROM user u
INNER JOIN order o ON u.id = o.user_id
WHERE o.status = 'PAID';

5. UNION 优化

XML

-- UNION ALL 不去重，性能优于 UNION（需去重，产生临时表）
SELECT id, username FROM user WHERE status = 1
UNION ALL
SELECT id, username FROM user_bak WHERE status = 1;

MyBatis 映射层优化

减少 N+1 查询

XML

<!-- 不推荐：嵌套查询产生 N+1 问题 -->
<resultMap id="UserWithOrders" type="User">
    <id property="id" column="id"/>
    <collection property="orders"
        column="id"
        select="com.example.mapper.OrderMapper.selectByUserId"/>
</resultMap>

<select id="selectUsers" resultMap="UserWithOrders">
    SELECT * FROM user WHERE status = 1
</select>

N 个用户 = 1 次查询用户 + N 次查询订单 = N+1 次 SQL。

SQL

<!-- 推荐：使用 JOIN 一次查询 -->
<resultMap id="UserWithOrdersJoin" type="User">
    <id property="id" column="user_id"/>
    <result property="username" column="username"/>
    <collection property="orders" ofType="Order">
        <id property="id" column="order_id"/>
        <result property="amount" column="amount"/>
    </collection>
</resultMap>

<select id="selectUsersWithOrders" resultMap="UserWithOrdersJoin">
    SELECT
        u.id AS user_id, u.username,
        o.id AS order_id, o.amount
    FROM user u
    LEFT JOIN order o ON u.id = o.user_id
    WHERE u.status = 1
</select>

合理使用 resultType vs resultMap

SQL

<!-- 简单场景：直接使用 resultType -->
<select id="selectById" resultType="User">
    SELECT id, username, email FROM user WHERE id = #{id}
</select>

<!-- 复杂场景：列名与字段名不一致时使用 resultMap -->
<resultMap id="ComplexUserMap" type="User">
    <id property="id" column="user_id"/>
    <result property="username" column="user_name"/>
    <result property="createTime" column="created_at"/>
</resultMap>

<select id="selectComplex" resultMap="ComplexUserMap">
    SELECT user_id, user_name, created_at FROM user
</select>

实战案例分析

案例 1：慢查询日志发现排序耗时过长

现象：日志显示某查询耗时 3.5s：

SQL

SELECT * FROM order WHERE user_id = 1001 ORDER BY create_time DESC LIMIT 20;

EXPLAIN 分析：

SQL

+----+------+------+------+---------------+------+---------+------+-------+----------------+
| id | type | table | key  | key_len       | ref  | rows    | Extra              |
+----+------+------+------+---------------+------+---------+------+-------+----------------+
|  1 | ref  | order | idx_user | 4       | const| 5000    | Using filesort     |
+----+------+------+------+---------------+------+---------+------+-------+----------------+

问题：存在 user_id 索引，但 ORDER BY create_time 需额外排序（Using filesort）。

优化：创建联合索引 (user_id, create_time)：

text

CREATE INDEX idx_user_time ON order(user_id, create_time DESC);

优化后 EXPLAIN：

text

+----+-------+------+---------------+----------------+---------+------+-------+-------------+
| id | type  | table | key           | key_len        | ref     | rows  | Extra              |
+----+-------+------+---------------+----------------+---------+------+-------+-------------+
|  1 | ref   | order | idx_user_time | 4              | const   | 5000  | Using index condition |
+----+-------+------+---------------+----------------+---------+------+-------+-------------+

耗时从 3.5s 降至 50ms。

案例 2：大表 JOIN 产生全表扫描

现象：

text

SELECT u.*, o.order_no, o.amount
FROM user u
LEFT JOIN order o ON u.phone = o.phone
WHERE u.status = 1;

EXPLAIN：

text

+----+------+------+------+---------------+------+---------+------+--------+-------------+
| id | type | table | key  | key_len       | ref  | rows    | Extra                |
+----+------+------+------+---------------+------+---------+------+--------+-------------+
|  1 | ALL  | user | NULL | NULL          | NULL | 100000  | Using where         |
|  1 | ALL  | order | NULL | NULL         | NULL | 500000  | Using join buffer   |
+----+------+------+------+---------------+------+---------+------+--------+-------------+

两张表均全表扫描，使用 join buffer。

优化：为 JOIN 条件创建索引：

text

CREATE INDEX idx_phone ON user(phone);
CREATE INDEX idx_order_phone ON order(phone);

设计反思：使用 phone（VARCHAR）作为关联字段不如使用 user_id（INT），建议后续重构为外键关联。

要点总结

慢查询日志是定位性能问题的首要入口，long_query_time 建议设 1 ~ 2 秒
mysqldumpslow 可快速聚合相似 SQL，pt-query-digest 提供更详细的统计分析
索引创建应基于 WHERE/JOIN/ORDER BY/GROUP BY 字段，选择率 > 0.1 的字段优先
联合索引字段排序：等值匹配字段优先，区分度高的靠前，排序字段放最后
深分页使用子查询 + JOIN 或游标分页优化，避免 LIMIT 偏移量过大
N+1 查询通过 JOIN 一次性获取关联数据，避免循环查询
SELECT * 应避免，指定需要的字段以利用覆盖索引
优化前后务必 EXPLAIN 验证，关注 type、key、rows、Extra 四个核心字段

📝 发现内容有误？点击此处直接编辑