【性能调优】全方位教你定位慢SQL

👉 这是一个或许对你有用的社群

🐱 一对一交流/面试小册/简历优化/求职解惑，欢迎加入「芋道快速开发平台」知识星球。下面是星球提供的部分资料： 

• 《项目实战（视频）》：从书中学，往事中“练”
• 《互联网高频面试题》：面朝简历学习，春暖花开
• 《架构 x 系统设计》：摧枯拉朽，掌控面试高频场景题
• 《精进 Java 学习指南》：系统学习，互联网主流技术栈
• 《必读 Java 源码专栏》：知其然，知其所以然

• 1. 使用数据库自带工具
• MySQL
• PostgreSQL
• Oracle
• 2. 监控工具
• 3. SQL 诊断和分析
• 4. 优化建议
• 示例代码：使用 EXPLAIN 分析慢查询（MySQL）

定位慢 SQL（Slow SQL）是数据库性能调优中的一个重要任务，目的是找到和优化那些执行时间较长的 SQL 查询。以下是常用的定位慢 SQL 的方法和步骤：

1. 使用数据库自带工具

大多数数据库管理系统（DBMS）提供了内置的工具和视图来帮助定位慢 SQL。以下是一些主要数据库的常用工具：

MySQL

慢查询日志：

可以启用 MySQL 的慢查询日志，记录超过指定执行时间的查询。

配置示例：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 单位是秒

查看慢查询日志：

SHOW VARIABLES LIKE 'slow_query_log_file';

EXPLAIN：

使用 EXPLAIN 语句来分析查询的执行计划。

示例：

EXPLAIN SELECT * FROM your_table WHERE your_condition;

PostgreSQL

pg_stat_statements：

PostgreSQL 提供了 pg_stat_statements 扩展来记录 SQL 语句的执行统计信息。

启用方法：

CREATE EXTENSION pg_stat_statements;

查看统计信息：

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

EXPLAIN ANALYZE：

使用 EXPLAIN ANALYZE 来查看 SQL 查询的实际执行计划和执行时间。

示例：

EXPLAIN ANALYZE SELECT * FROM your_table WHERE your_condition;

Oracle

Automatic Workload Repository (AWR)：

Oracle 提供了 AWR 报告来分析性能问题，包括慢 SQL。

查看 AWR 报告：

@?/rdbms/admin/awrrpt.sql

SQL Trace and tkprof：

使用 SQL Trace 和 tkprof 工具来跟踪和分析 SQL 语句的执行。

启用 SQL Trace：

ALTER SESSION SET sql_trace = TRUE;

V$SQL 和 V$SQLAREA

查询 V$SQL 和 V$SQLAREA 视图来获取 SQL 语句的性能数据。

示例：

SELECT sql_text, elapsed_time, cpu_time, executions FROM v$sql ORDER BY elapsed_time DESC;

基于 Spring Boot + MyBatis Plus + Vue & Element 实现的后台管理系统 + 用户小程序，支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

• 项目地址：https://github.com/YunaiV/ruoyi-vue-pro
• 视频教程：https://doc.iocoder.cn/video/

2. 监控工具

使用数据库监控工具可以帮助实时监控数据库性能，定位慢 SQL。这些工具通常提供图形化界面和详细的性能指标。常见的监控工具包括：

• MySQL Enterprise Monitor（MySQL）
• pgAdmin、pgWatch（PostgreSQL）
• Oracle Enterprise Manager（Oracle）
• 第三方工具：如 Datadog、New Relic、Prometheus 等。

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 实现的后台管理系统 + 用户小程序，支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

• 项目地址：https://github.com/YunaiV/yudao-cloud
• 视频教程：https://doc.iocoder.cn/video/

3. SQL 诊断和分析

在实际分析慢 SQL 时，可以结合以下步骤进行详细诊断：

1.收集信息：

收集慢查询的 SQL 语句、执行时间、发生频率等信息。

2.分析执行计划：

使用数据库提供的 EXPLAIN 或 EXPLAIN ANALYZE 工具来查看查询的执行计划，找出可能的性能瓶颈（如全表扫描、索引未使用、JOIN 操作不当等）。

3.检查索引：

确认查询涉及的列是否有合适的索引，索引是否被正确使用。

4.统计信息和表结构：

检查表的统计信息是否最新，表结构是否合理。

示例：

ANALYZE TABLE your_table;

5.数据库配置：

检查数据库的配置参数是否优化，例如缓冲区大小、连接池配置等。

6.硬件资源：

确认服务器的硬件资源（CPU、内存、磁盘 I/O 等）是否充足，是否存在资源瓶颈。

4. 优化建议

在找到慢 SQL 后，可以考虑以下优化措施：

添加或优化索引：

根据查询条件和执行计划，添加或优化索引。

示例：

CREATE INDEX idx_your_column ON your_table(your_column);

重构查询：

改写查询语句，避免不必要的复杂操作。示例：使用子查询、分解复杂查询等。

调整表结构：

归一化或反归一化表结构，根据需要调整分区。

数据库参数调优：

调整数据库的配置参数，如内存分配、缓存大小、并发限制等。

分区和分片：

对于大表，可以考虑使用分区或分片来提高查询性能。

缓存：

使用缓存（如 Redis、Memcached）来减少数据库查询的频率。

示例代码：使用 EXPLAIN 分析慢查询（MySQL）

-- 慢查询示例
SELECT * FROM orders WHERE customer_id = 12345 AND order_date > '2023-01-01';

-- 使用 EXPLAIN 分析
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345 AND order_date > '2023-01-01';
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SlowQueryAnalyzer {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/yourdatabase";
        String username = "yourusername";
        String password = "yourpassword";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             Statement stmt = conn.createStatement()) {

            String slowQuery = "SELECT * FROM orders WHERE customer_id = 12345 AND order_date > '2023-01-01'";
            long startTime = System.currentTimeMillis();
            ResultSet rs = stmt.executeQuery(slowQuery);
            long endTime = System.currentTimeMillis();

            System.out.println("Query executed in " + (endTime - startTime) + " ms");

            // 使用 EXPLAIN 分析
            ResultSet explainRs = stmt.executeQuery("EXPLAIN " + slowQuery);
            while (explainRs.next()) {
                System.out.println("id: " + explainRs.getInt("id"));
                System.out.println("select_type: " + explainRs.getString("select_type"));
                System.out.println("table: " + explainRs.getString("table"));
                System.out.println("type: " + explainRs.getString("type"));
                System.out.println("possible_keys: " + explainRs.getString("possible_keys"));
                System.out.println("key: " + explainRs.getString("key"));
                System.out.println("rows: " + explainRs.getInt("rows"));
                System.out.println("Extra: " + explainRs.getString("Extra"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

上述示例展示了如何执行一个慢查询，并使用 EXPLAIN 命令来分析查询的执行计划。实际应用中，需要结合执行计划的输出结果来确定优化方向。