分析慢sql语句

前言

对于遇到慢SQL的情况，我们应该如何分析，一些方法的分享。

1查找慢sql语句

首先我们要明确慢sql一般分为两种类型：

SQL执行时间慢，但执行的频率不高，该SQL对数据库整体性能影响不大，可以放到最后进行优化。
SQL单独执行时间可能很快，但执行频率非常高，高并发下执行效率降低。

1.1 通过sqllog查看

我们可以通过开启跟踪日志记录的方式，在dm.ini 文件，设置 SVR_LOG = 1 以启用 sqllog.ini 配置，也可以通过调用数据库函数直接修改

SP_SET_PARA_VALUE(1,'SVR_LOG',1);

开启了sqllog后我们可以在对应的log文件夹下看到跟踪日志文件通常以“dmsql_实例名_日期_时间命名.log”命名，跟踪日志内容包含系统各会话执行的 SQL 语句、参数信息、错误信息、执行时间等。跟踪日志主要用于分析错误和分析性能问题，基于跟踪日志可以对系统运行状态进行分析。

在拿到跟踪日志后我们可以通过DMLOG日志分析工具进行系统的分析，通过该工具我们会生成echart统计图、QPS统计图、echart负载图、jfree散点图、sql语句执行时间排序、sql语句执行次数排序，通过该excel我们可以清楚的看到哪些sql是慢sql。

1.2 通过系统视图查看

我们可以将ENABLE_MONITOR=1打开，显示系统最近 1000 条执行时间超过预定值的 SQL 语句，默认预定值为 1000 毫秒

可以通过查询 V$LONG_EXEC_SQLS 系统视图获取结果：

SELECT * FROM V$LONG_EXEC_SQLS;

2 查看执行计划

2.1 查看执行计划

我们可以通过以下两种方式查看执行计划：

通过 DM 数据库配套管理工具查看。
使用 explain 命令查看。

我们对于执行计划的参数再进行查看

执行计划的每行即为一个计划节点，主要包含三部分信息。

第一部分NEST2、PRJT2、CSCN2 为操作符及数据库具体执行了什么操作。
第二部分的三元组为该计划节点的执行代价，具体含义为[代价，记录行数，字节数]。
第三部分为操作符的补充信息。

2.2 执行计划操作符

操作符	含义	作用
NSET	结果集收集	NSET 是用于结果集收集的操作符，一般是查询计划的顶层节点。一般无优化空间
PRJT	投影	PRJT 是关系的【投影】 (project) 运算，用于选择表达式项的计算。广泛用于查询，排序，函数索引创建等。一般无优化空间
SLCT	选择	SLCT 是关系的【选择】运算，用于查询条件的过滤。若过滤条件过滤性好，可考虑增加索引
AAGR	简单聚集	AAGR 用于没有 GROUP BY 的 COUNT、SUM、AGE、MAX、MIN 等聚集函数的计算。
FAGR	快速聚集	FAGR 用于没有过滤条件时，从表或索引快速获取 MAX、MIN、COUNT 值。
HAGR	HASH 分组聚集	HAGR 用于分组列没有索引只能走全表扫描的分组聚集。
SAGR	流分组聚集	SAGR 用于分组列是有序的情况下，可以使用流分组聚集。
BLKUP	二次扫描 (回表)	BLKUP 先使用二级索引索引定位 rowid，再根据表的主键、聚集索引、rowid 等信息获取数据行中其它列。
CSCN	全表扫描	CSCN2 是 CLUSTER INDEX SCAN 的缩写即通过聚集索引扫描全表。在一个高并发的系统中应尽量避免全表扫描
SSEK	索引扫描	SSEK2 是二级索引扫描即先扫描索引，再通过主键、聚集索引、rowid 等信息去扫描表。
CSEK	索引扫描	CSEK2 是聚集索引扫描只需要扫描索引，不需要扫描表，即无需 BLKUP 操作，如果 BLKUP 开销较大时，可考虑创建聚集索引。
SSCN	索引扫描	SSCN 是索引全扫描，不需要扫描表。
NEST LOOP	嵌套循环连接	嵌套循环连接是最基础的连接方式，将一张表（驱动表）的每一个值与另一张表（被驱动表）的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行。
HASH JOIN	哈希连接	哈希连接是在没有索引或索引无法使用情况下大多数连接的处理方式。哈希连接使用关联列去重后结果集较小的表做成 HASH 表，另一张表的连接列在 HASH 后向 HASH 表进行匹配。
MERGE JOIN	归并排序连接	归并排序连接需要两张表的连接列都有索引，对两张表扫描索引后按照索引顺序进行归并。

3 常用优化手段

索引优化：这是最常见的优化手段。
- 确保索引被有效利用：检查WHERE子句、JOIN条件、ORDER BY/GROUP BY字段是否已有合适索引。
- 避免索引失效：注意字段上的函数计算、隐式类型转换可能导致索引失效。
- 考虑组合索引：如果多个字段常一起作为条件，考虑创建组合索引，并遵循最左匹配原则。将等值匹配的列和过滤性好的列放在前面。
- 更新统计信息：优化器依赖统计信息来估算成本。如果统计信息过时，可能导致选择错误的执行计划。定期或在对表进行大量DML操作后更新统计信息很重要。
SQL语句优化：
- 避免 SELECT *：只获取需要的字段。
- 优化JOIN：确保JOIN条件上有索引，尽量避免多表关联时产生笛卡尔积。
- 优化子查询：考虑使用JOIN或EXISTS重写子查询。
- 合理使用WITH和临时表：对复杂查询，有时使用临时表分步处理会更高效。
数据库参数与设计优化：
- 调整内存参数：如 BUFFER（系统缓冲区）、MEMORY_POOL（共享内存池）等，减少物理I/O。
- 考虑分区表：对特别大的表，按时间或范围分区可以显著提升查询效率。
- 使用物化视图：对于复杂的报表查询，可考虑使用物化视图预计算和存储结果。

4 举例验证

4.1 创建实例以及查看慢sql

为了演示就创建两个表：orders（订单表）和 order_details（订单详情表）。

-- 1. 创建订单表 orders
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount DECIMAL(15, 2) NOT NULL
)[2](@ref);

-- 2. 创建订单详情表 order_details
CREATE TABLE order_details (
    detail_id INT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(15, 2) NOT NULL,
    PRIMARY KEY (detail_id, order_id)
)[2](@ref);

-- 3. 插入模拟数据（使用循环和随机数生成大量数据）
-- 插入订单数据
BEGIN
  FOR i IN 1..1000000 LOOP
    INSERT INTO orders (order_id, customer_id, order_date, status, total_amount)
    VALUES (i, 
            MOD(i, 10000) + 1, 
            SYSDATE - MOD(i, 365), 
            CASE MOD(i, 10) WHEN 0 THEN '已完成' WHEN 1 THEN '已发货' ELSE '已付款' END, 
            DBMS_RANDOM.VALUE(10, 5000));
  END LOOP;
  COMMIT;
END;
/

-- 插入订单详情数据
BEGIN
  FOR i IN 1..5000000 LOOP
    INSERT INTO order_details (detail_id, order_id, product_id, quantity, price)
    VALUES (i, 
            MOD(i, 1000000) + 1, 
            MOD(i, 1000) + 1, 
            MOD(i, 10) + 1, 
            DBMS_RANDOM.VALUE(1, 1000));
  END LOOP;
  COMMIT;
END;
/

我们进行查询某位客户所有已完成订单的详情（包括订单信息和详情信息），并按订单金额排序，SQL 如下：

SELECT o.order_id, o.order_date, o.total_amount, d.product_id, d.quantity, d.price
FROM orders o
INNER JOIN order_details d ON o.order_id = d.order_id
WHERE o.customer_id = 12345 
AND o.status = '已完成'
ORDER BY o.total_amount DESC;

4.2 获取执行计划

我们可以通过使用 EXPLAIN或 SET AUTOTRACE ON查看执行计划：

EXPLAIN
SELECT *
FROM orders o
INNER JOIN order_details d ON o.order_id = d.order_id
WHERE o.customer_id = 1101
AND o.status = '已完成'
ORDER BY o.total_amount DESC;

我们查询到的执行计划如下

可以从执行计划中看到有两个CSCN2结果集是100万/500万，这是性能瓶颈的根源。执行计划的第一步是对 ORDERS和 ORDER_DETAILS两个表进行了全表扫描。这意味着数据库没有使用索引，而是逐行读取了所有数据，效率非常低下。

然后我们进行了SLCT2则是select对于我们的where条件中满足的条件筛选除了625行；再将这过滤后的 625 条订单记录与 500 万条订单详情记录通过 order_id进行哈希关联。再对关联后的结果集（3093行）按照 ORDER BY子句（o.total_amount DESC）进行排序操作。这是整个计划中代价最高的一步（1054），因为它需要在内存或磁盘上对数据进行排序。

4.3 分析执行计划

通过上述的执行计划我们可以看到的问题是：缺乏有效的索引，导致大量的全表扫描和昂贵的排序。我们就需要想办法去优化怎么避免进行全表扫描和排序。

创建索引以避免全表扫描

我们可以在ORDERS表上，为 WHERE条件的字段和连接字段创建复合索引。优先考虑 (customer_id, status)，因为它们过滤性更好，能将100万行快速缩小到625行。

CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

通过修改复合索引后我们在查找时从100万的全表扫描变为了625行的索引扫描。

我们还可以在 ORDER_DETAILS表上，为连接字段 order_id创建索引。

CREATE INDEX idx_order_details_oid ON order_details(order_id);

我们通过在连接字段order_id上添加了索引之后将哈希关联HASH2部分优化为了嵌套循环关联NEST LOOP INDEX JOIN2，将代价由1054将为了13。

优化排序操作

由于我们这里查询sql的排序字段是 total_amount，可以考虑在 ORDERS表的复合索引中包含它，使其成为覆盖索引。这样数据库可以直接从索引中按顺序读取数据，避免昂贵的 SORT3操作。

CREATE INDEX idx_orders_customer_status_amount ON orders(customer_id, status, total_amount DESC);

优化后的执行计划显示 SSEK2（索引范围扫描）代替 CSCN2（全表扫描），并且 SORT3操作会因为使用了有序索引而消失，整个查询的代价将降低。

4.4 总结

总体而言我们需要通过执行计划以及我们的业务逻辑来来修改我们的慢sql，先看sql语句的执行瓶颈是什么，再通过执行瓶颈来反向推到我们需要优化的是哪些地方，当然不止是我上述提到的两个方案，还有别的方式。

我们可以优化sql语句，将select * 改为只选择必要的列
我们还可以使用HINT引导优化器，即使用提示强制索引

SELECT /*+ INDEX(TABLE_NAME INDEX_NAME) */ * FROM TABLE_NAME WHERE ...;

如果数据量持续巨大，我们也可以考虑分区表，将数据按照日期来进行分类

-- 对orders表按范围分区（例如按订单日期）
CREATE TABLE orders_partitioned (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount DECIMAL(15, 2) NOT NULL
) PARTITION BY RANGE (order_date) (
    PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
    PARTITION p2025 VALUES LESS THAN (MAXVALUE)
);