SQL慢查询怎么办，DBA老手教你5步快速定位与优化

第一章：SQL慢查询怎么办，DBA老手教你5步快速定位与优化

开启慢查询日志监控

定位慢查询的第一步是确保数据库已启用慢查询日志。在 MySQL 中，可通过以下配置开启：

-- 查看当前慢查询状态
SHOW VARIABLES LIKE 'slow_query_log';

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

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

上述操作将记录执行时间超过1秒的SQL语句，便于后续分析。

使用EXPLAIN分析执行计划

对可疑SQL使用 EXPLAIN 命令查看其执行路径：

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';

重点关注 type（访问类型）、key（使用的索引）和 rows（扫描行数）。若出现 ALL 或 index 类型且扫描行数巨大，说明缺少有效索引。

建立合适的索引策略

• 为高频查询条件字段创建单列或复合索引
• 避免过度索引，影响写入性能
• 定期审查冗余或未被使用的索引

优化SQL书写方式

避免全表扫描和临时表生成。例如，改用覆盖索引减少回表：

-- 原始低效查询
SELECT * FROM users WHERE age > 25;

-- 优化后使用覆盖索引
SELECT id, name FROM users WHERE age > 25;

监控与持续调优

通过性能模式（Performance Schema）长期跟踪SQL执行情况。可定期运行如下查询识别最耗时语句：

指标	说明
Query_time	查询总耗时
Lock_time	锁等待时间
Rows_sent	返回行数
Rows_examined	扫描行数

第二章：精准定位慢查询根源

2.1 启用慢查询日志并合理设置阈值

启用慢查询日志是定位性能瓶颈的关键步骤。MySQL通过记录执行时间超过指定阈值的SQL语句，帮助开发者识别低效查询。

配置慢查询日志参数

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1.0;
SET GLOBAL log_output = 'TABLE';

上述命令开启慢查询日志，将阈值设为1秒，并输出到mysql.slow_log表中。long_query_time可根据业务响应需求调整，例如高并发场景可设为0.5秒以更严格监控。

阈值设定建议

• OLTP系统建议设置在0.5~2秒之间，避免遗漏潜在问题SQL
• 报表类应用可适当放宽至5秒以上
• 结合监控平台动态调整，定期分析日志趋势

合理配置后，系统将持续捕获慢查询，为后续优化提供数据支撑。

2.2 使用EXPLAIN分析执行计划关键指标

在优化SQL查询性能时，`EXPLAIN` 是分析执行计划的核心工具。它揭示了MySQL如何执行查询，帮助识别潜在的性能瓶颈。

执行计划输出字段解析

使用 `EXPLAIN` 后，返回的关键字段包括：

• id：查询中每个SELECT的序号，表示执行顺序；
• type：连接类型，从system到ALL，性能由高到低；
• key：实际使用的索引；
• rows：扫描行数预估值，越小性能越好；
• Extra：附加信息，如“Using filesort”需警惕。

示例分析

EXPLAIN SELECT * FROM users WHERE age > 30 AND city = 'Beijing';

该语句执行后，若发现 type=ALL 且 rows 值较大，说明进行了全表扫描。应检查是否在 city 或 age 上建立联合索引，以减少扫描行数并避免临时排序。

2.3 识别全表扫描与索引失效典型场景

在数据库查询优化中，全表扫描和索引失效是性能瓶颈的常见根源。理解其触发条件有助于精准定位问题。

常见索引失效场景

• 对索引列使用函数或表达式，如 WHERE YEAR(create_time) = 2023
• 隐式类型转换，如字符串字段与数字比较
• 使用 OR 条件时部分字段无索引
• 最左前缀原则被破坏，如联合索引 (a, b, c) 未从 a 开始使用

全表扫描识别方法

通过执行计划分析是否出现全表扫描：

EXPLAIN SELECT * FROM users WHERE name LIKE '%John%';

该查询因模糊匹配前置通配符导致索引失效，type=ALL 表示全表扫描，应改用后置通配符或引入全文索引。

2.4 借助Performance Schema深入追踪SQL性能

MySQL的Performance Schema为数据库性能分析提供了低开销的运行时监控能力，尤其适用于深度追踪SQL执行瓶颈。

启用与配置

默认情况下Performance Schema已启用，可通过以下命令验证状态：

SHOW VARIABLES LIKE 'performance_schema';

若返回值为ON，则表示已激活。该功能通过内存表记录事件，不影响正常事务处理。

关键性能表

重点关注以下三张表以定位慢查询：

• events_statements_history：记录每个线程最近的SQL语句事件
• events_waits_history：展示SQL等待资源的详细信息
• file_summary_by_instance：统计文件I/O操作，识别磁盘读写热点

实战示例：定位高延迟查询

查询执行时间超过1秒的SQL语句：

SELECT sql_text, timer_wait / 1000000000 AS latency_sec
FROM performance_schema.events_statements_history
WHERE timer_wait > 1000000000000
ORDER BY timer_wait DESC LIMIT 5;

其中timer_wait单位为皮秒，转换为秒需除以10^12，结果可直观反映响应延迟。

2.5 结合监控工具快速锁定高耗时语句

在高并发系统中，数据库性能瓶颈常源于少数高耗时SQL语句。通过集成Prometheus与Grafana构建实时监控体系，可对SQL执行时间进行可视化追踪。

慢查询识别流程

• 启用MySQL的慢查询日志（slow_query_log）并设置阈值
• 使用pt-query-digest分析日志，提取执行时间最长的SQL
• 结合Performance Schema定位锁等待与资源争用

示例：Prometheus告警规则配置

- alert: HighLatencyQuery
  expr: mysql_slow_queries_duration_seconds > 2
  for: 1m
  labels:
    severity: warning
  annotations:
    summary: "高耗时SQL检测"
    description: "发现执行时间超过2秒的SQL语句，请立即排查"

该规则持续监控慢查询指标，一旦触发即推送至Alertmanager，联动钉钉通知运维人员。

性能数据看板

指标	正常值	告警阈值
QPS	< 5000	> 8000
平均响应时间	< 50ms	> 200ms

第三章：索引设计与优化策略

3.1 高效创建复合索引的原则与案例

复合索引的设计原则

创建复合索引时，应遵循“最左前缀”匹配原则，确保查询条件能有效利用索引。字段顺序至关重要：选择性高的字段应靠前，过滤性强的字段优先。

• 避免冗余索引，减少写入开销
• 覆盖索引可避免回表查询，提升性能
• 索引列不宜过多，通常不超过3~4个

实际案例分析

假设订单表包含用户ID、状态和创建时间：

CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at);

该索引适用于以下查询：

SELECT * FROM orders 
WHERE user_id = 1001 
  AND status = 'paid' 
  AND created_at > '2023-01-01';

此复合索引首先按 user_id 精确匹配，再在结果内按 status 过滤，最后基于 created_at 范围扫描，三层过滤显著提升查询效率。

3.2 覆盖索引减少回表提升查询效率

在MySQL查询优化中，覆盖索引是一种能显著减少I/O操作的策略。当查询的字段全部包含在索引中时，数据库无需回表查询主键对应的数据行，直接从索引即可获取所需信息。

覆盖索引的工作原理

传统查询需通过二级索引找到主键值，再回表查找完整记录。而覆盖索引避免了这一过程：

-- 假设 idx_name_age 为 (name, age) 的联合索引
SELECT name, age FROM users WHERE name = 'Alice';

该查询仅涉及索引字段，无需访问数据页，大幅降低磁盘I/O。

性能对比示例

查询类型	是否回表	执行效率
普通索引查询	是	较慢
覆盖索引查询	否	较快

合理设计联合索引，使常用查询命中覆盖索引，是提升查询性能的关键手段之一。

3.3 避免过度索引带来的写性能损耗

在数据库设计中，索引虽能显著提升查询效率，但过多索引会增加写操作的开销。每次INSERT、UPDATE或DELETE执行时，数据库需同步维护所有相关索引，导致I/O负载上升和事务延迟。

索引与写性能的权衡

每新增一个索引，写入性能通常下降5%~20%，具体取决于数据量和索引结构。应优先为高频查询字段建立索引，避免对低选择性字段（如性别）创建单列索引。

优化示例：合理精简索引

-- 冗余索引示例
CREATE INDEX idx_user_name ON users(name);
CREATE INDEX idx_user_name_age ON users(name, age);

-- 可删除idx_user_name，复合索引已覆盖name单独查询
DROP INDEX idx_user_name;

上述代码中，idx_user_name_age 已包含 name 字段前缀，可支持基于 name 的查询，因此单独的 idx_user_name 成为冗余，删除后减少写维护成本。

• 定期审查索引使用率，识别未被使用的索引
• 利用执行计划（EXPLAIN）验证索引实际调用情况
• 考虑部分索引或函数索引以降低开销

第四章：SQL语句级优化实践

4.1 重写低效SQL：避免SELECT * 和函数索引陷阱

在SQL查询优化中，避免使用 SELECT * 是提升性能的第一步。它不仅增加了I/O负载，还可能导致额外的网络传输开销。

明确字段选择

只查询所需字段，能显著减少数据读取量：

-- 低效写法
SELECT * FROM users WHERE created_at > '2023-01-01';

-- 高效写法
SELECT id, name, email FROM users WHERE created_at > '2023-01-01';

指定具体字段有助于利用覆盖索引，避免回表操作。

警惕函数索引陷阱

对索引列使用函数会导致索引失效：

-- 错误用法：无法使用索引
SELECT id, name FROM users WHERE YEAR(created_at) = 2023;

-- 正确写法：可命中索引
SELECT id, name FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

应将函数应用于比较值而非字段本身，确保索引有效参与查询执行计划。

4.2 优化JOIN操作：驱动表选择与小表前置

在执行JOIN操作时，驱动表的选择直接影响查询性能。数据库通常将左表作为驱动表，因此应优先将数据量较小的表置于左侧，以减少中间结果集的大小。

小表驱动大表原则

遵循“小表驱动大表”的原则，可显著降低资源消耗。优化器基于统计信息评估行数与大小，但显式调整表顺序仍具重要意义。

• 驱动表决定外层循环次数
• 小表作为驱动表减少I/O访问
• 适用于INNER JOIN和LEFT JOIN场景

-- 推荐写法：小表t1放在前面
SELECT * FROM small_table t1 
JOIN large_table t2 ON t1.id = t2.t1_id;

上述SQL中，small_table作为驱动表，逐行匹配large_table，有效控制了连接过程中的数据膨胀。执行计划通常显示更优的cost值与更快的响应时间。

4.3 分页查询优化：游标法替代OFFSET深翻页

在深度分页场景中，传统 OFFSET 方式会导致数据库扫描大量已读记录，性能随偏移量增长急剧下降。游标法（Cursor-based Pagination）通过记录上一页末尾的排序键值，实现“从断点继续”的高效查询。

基于游标的时间戳分页

适用于按时间排序的数据流，如日志或消息列表：

SELECT id, user_id, created_at 
FROM messages 
WHERE created_at < '2023-10-01 10:00:00' 
  AND id < 10000 
ORDER BY created_at DESC, id DESC 
LIMIT 20;

此处 created_at 和 id 组成唯一游标锚点，避免数据重复或遗漏。相比 OFFSET 10000 LIMIT 20，该查询始终定位起始位置，执行效率稳定。

性能对比

方法	查询复杂度	适用场景
OFFSET	O(n + m)	浅页展示
游标法	O(log n)	深翻页、实时流

4.4 子查询与临时表的合理使用建议

在复杂查询中，子查询和临时表是提升逻辑清晰度的重要工具。合理使用可优化执行计划，避免数据冗余。

子查询的应用场景

当需要基于另一查询结果进行过滤时，推荐使用相关子查询或非相关子查询：

SELECT name FROM employees e 
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

该查询查找每个部门中薪资高于平均值的员工。子查询按部门分组计算平均薪资，主查询逐行比较。注意相关子查询会对外部行产生依赖，性能开销较高，应配合索引使用。

临时表的适用时机

对于多步骤聚合或中间结果复用的场景，临时表更高效：

CREATE TEMPORARY TABLE temp_dept_stats AS
SELECT department, AVG(salary) AS avg_sal, COUNT(*) AS emp_count
FROM employees GROUP BY department;

创建临时表缓存部门统计信息，后续可多次引用，避免重复计算。临时表仅在当前会话可见，连接断开后自动清理。

• 优先使用CTE替代嵌套子查询以提高可读性
• 大数据量中间结果建议使用临时表并建立索引
• 避免在高频执行语句中使用复杂子查询

第五章：构建可持续的数据库性能保障体系

监控与告警机制设计

建立全面的数据库监控体系是性能保障的基础。应采集关键指标如查询延迟、连接数、缓存命中率和慢查询日志。使用 Prometheus + Grafana 实现可视化，结合 Alertmanager 设置阈值告警。

• 监控项包括：QPS、TPS、InnoDB 缓冲池使用率
• 慢查询阈值建议设置为 100ms，并定期分析执行计划
• 通过 pt-query-digest 分析日志，识别高频低效 SQL

自动化索引优化策略

-- 示例：基于高频查询自动创建复合索引
CREATE INDEX idx_user_status_created 
ON users (status, created_at) 
WHERE status = 'active';

-- 使用覆盖索引减少回表
SELECT id, name, email 
FROM users 
WHERE status = 'active' AND created_at > '2023-01-01';

容量规划与弹性扩展

制定基于业务增长趋势的容量模型，避免资源瓶颈。采用读写分离与分库分表策略应对高并发场景。

节点类型	CPU 核心	内存	适用场景
主库	16	64GB	高 IOPS 写入
只读副本	8	32GB	报表查询分流

故障演练与预案管理

流程图：主库宕机切换流程 1. 检测心跳中断 → 2. 触发 MHA 切换 → 3. 提升备库为主 → 4. DNS 更新指向新主库 → 5. 应用重连恢复

定期执行数据库压测，模拟流量高峰，验证连接池配置与超时策略的有效性。某电商系统在大促前通过 sysbench 模拟 5000 并发，提前发现连接泄漏问题并修复。