慢SQL优化最佳实践

问题场景

在电商系统中，用户频繁查询近期待处理的订单，导致订单表数据量达到数百万行，查询响应缓慢，进而影响订单处理效率。

问题SQL

SELECT order_no, total_amount, customer_name
FROM orders
WHERE create_time >= '2025-05-01'
  AND status = 'pending'
ORDER BY create_time DESC
LIMIT 100;

问题分析

1. 登录云数据库GaussDB控制台。
2. 在“实例管理”页面，选择指定的实例，单击实例的名称，进入实例详情页面。
3. 单击“诊断优化 > SQL诊断”。
4. 在“慢SQL”页签，选择指定节点、时间范围、SQL文本，配置SQL阈值，单击“查询”按钮，获取慢SQL列表。
   • 用户可自行选择时间区间，最大时间跨度为一天，可按照节点维度展示数据。
   • 过滤系统用户：开启后，将会跳过系统用户所执行的SQL记录。默认开启。
   • 用户可以选择"OR"或者"AND"查询，筛选框中可以填入需要组合过滤的多个SQL文本（不超过5个）。本示例中选择"OR"查询，SQL文本为 orders。
   • 支持自定义慢SQL阈值筛选。本示例中配置为1000ms。
   图4 查询慢SQL
   

5. 在慢SQL列表中，查看SQL计划（执行计划）和平均执行时间。平均时间为2.0286s，执行计划如下。

   [Parameterized]
   Datanode Name: dn_6001_6002_6003
   Limit  (cost=257646.18..257646.30 rows=50 width=44)
     ->  Sort  (cost=257646.18..266236.99 rows=50 width=44)
           Sort Key: create_time DESC
           ->  Seq Scan on orders  (cost=0.00..143494.00 rows=3436323 width=44)
                 Filter: (((status)::text = '***'::text) AND (create_time >= '***'::timestamp without time zone)), (Expression Flatten Optimized)

   执行计划中，Seq Scan on orders表示对整张orders表进行全表顺序扫描，遍历3436323行数据，每一行数据读取出来后，通过Filter过滤status和create_time两个条件，以获取符合条件的数据行。由于是扫描全部数据行后才进行过滤和排序，因此效率极低。

优化方案

通过对执行计划的分析，该SQL扫描了整张orders表，并过滤出符合status和create_time两个条件的数据行，因此需要对这两个条件字段添加联合索引。注意联合索引字段的顺序，顺序错误可能会引起索引失效。

CREATE INDEX idx_orders_status_create_time ON orders(status, create_time DESC);

验证效果

1. 重新执行SQL。

   SELECT order_no, total_amount, customer_name
   FROM orders
   WHERE create_time >= '2025-05-01'
     AND status = 'pending'
   ORDER BY create_time DESC
   LIMIT 100;

2. 查看执行计划和时间。执行时间为0.001s，执行计划如下。
   图5 查看SQL计划和执行时间
   
   执行计划：

   [Parameterized]
   Datanode Name: dn_6001_6002_6003
   Limit  (cost=0.00..2.32 rows=50 width=44)
     ->  Index Scan using idx_orders_status_create_time on orders  (cost=0.00..159532.64 rows=50 width=44)
           Index Cond: (((status)::text = '***'::text) AND (create_time >= '***'::timestamp without time zone))

   从执行计划中可以看到，SQL命令采用了索引扫描：Index Scan，且与优化前执行计划中使用Filter不同，优化后的执行计划采用Index Cond，过滤逻辑直接在索引层面完成，无需将整行数据取出来后再Filter，因此执行效率显著提升。

3. 对比优化前后指标。可以看到执行速度得到了显著提升。

   指标	优化前	优化后	提升
   扫描方式	Seq Scan	Index Scan	-
   平均执行时间	2.0286s	0.001s	2028.6 倍

问题场景

在电商平台中，用户查看自己的历史订单列表时，需要展示订单基本信息以及最后一条订单明细的商品名称。数据库已为orders表创建了id主键索引，但未创建user_id索引。查询过程中，优化器选择按主键索引进行反向扫描（期望利用ORDER BY id DESC的有序性），导致需要大量Filter过滤user_id条件，页面加载缓慢，影响用户体验。

问题SQL

SELECT o.order_no, o.total_amount, o.customer_name, o.status, o.create_time,
       (SELECT oi.product_name FROM order_items oi WHERE oi.order_id = o.id AND ROWNUM = 1 ORDER BY oi.id DESC) as last_product
FROM orders o
WHERE o.user_id = 12345
ORDER BY o.id DESC
LIMIT 20;

问题分析

1. 登录云数据库GaussDB控制台。
2. 在“实例管理”页面，选择指定的实例，单击实例的名称，进入实例详情页面。
3. 单击“诊断优化 > SQL诊断”。
4. 在“慢SQL”页签，选择指定节点、时间范围、SQL文本，配置SQL阈值，单击“查询”按钮，获取慢SQL列表。
   • 用户可自行选择时间区间，最大时间跨度为一天，可按照节点维度展示数据。
   • 过滤系统用户：开启后，将会跳过系统用户所执行的SQL记录。默认开启。
   • 用户可以选择"OR"或者"AND"查询，筛选框中可以填入需要组合过滤的多个SQL文本（不超过5个）。本示例中选择"OR"查询，SQL文本为 orders。
   • 支持自定义慢SQL阈值筛选。本示例中配置为1000ms。
   图6 查询慢SQL
   

5. 在慢SQL列表中，查看SQL计划（执行计划）和平均执行时间。平均时间为2.1985s，执行计划如下。

   [Parameterized]
   Datanode Name: dn_6001_6002_6003
   Limit  (cost=176666.67..176666.67 rows=1 width=61)
     ->  Index Scan Backward using orders_pkey on orders o  (cost=0.00..176666.67 rows=1 width=61)
           Filter: (user_id = $2)
           SubPlan 1
             ->  Sort  (cost=0.01..0.02 rows=1 width=18)
                   Sort Key: oi.id DESC
                   ->  Rownum  (cost=0.00..0.00 rows=1 width=18)
                         StopKey: (ROWNUM = $1)
                         ->  Seq Scan on order_items oi  (cost=0.00..47983.00 rows=1 width=18)
                               Filter: (order_id = o.id), (Expression Flatten Optimized)

   从执行计划可以看出，优化器选择按主键索引反向扫描（Index Scan Backward using orders_pkey），期望利用id DESC有序性避免排序。但WHERE条件是user_id，需要回表+Filter过滤，实际扫描500万行才找到几条匹配记录，效率极低。子查询中Seq Scan on order_items表明对order_items进行了全表扫描，过滤条件 Filter: order_id = o.id，加剧了SQL执行开销。

优化方案

方案一：SQL Patch强制全表扫描

优化器错误选择主键索引反向扫描，导致大量Filter过滤。通过SQL Patch强制使用全表扫描做临时规避，顺序扫描效率反而会更高。

1. 登录云数据库GaussDB控制台。
2. 在“实例管理”页面，选择指定的实例，单击实例的名称，进入实例详情页面。
3. 单击“诊断优化 > SQL诊断”。
4. 在“慢SQL”页签，选择指定节点，时间范围，组合过滤SQL文本、归一化SQL ID以及其他扩展字段，单击“查询”按钮，获取慢SQL列表。
5. 在慢SQL列表，选择对应慢SQL，在“操作”列单击“更多 > SQL Patch”，显示“SQL PATCH详情”页面。
   图7 创建SQL PATCH
   

6. 选择PATCH类型为HINT，输入PATCH名称和PATCH内容，单击“创建”。
   图8 创建SQL PATCH
   

7. 验证效果。
   1. 重新执行如下SQL。

      SELECT o.order_no, o.total_amount, o.customer_name, o.status, o.create_time,
             (SELECT oi.product_name FROM order_items oi WHERE oi.order_id = o.id AND ROWNUM = 1 ORDER BY oi.id DESC) as last_product
      FROM orders o
      WHERE o.user_id = 12345
      ORDER BY o.id DESC
      LIMIT 20;

   2. 查看执行计划和时间。执行时间为1.0434s，执行计划如下。
      图9 查看SQL计划和执行时间
      

      执行计划：

      [Parameterized]
      Datanode Name: dn_6001_6002_6003
      Limit  (cost=178977.03..178977.03 rows=1 width=61)
        ->  Sort  (cost=178977.03..178977.03 rows=1 width=61)
              Sort Key: o.id DESC
              ->  Seq Scan on orders o  (cost=0.00..178977.02 rows=1 width=61)
                    Filter: (user_id = '***'), (Expression Flatten Optimized)
                    SubPlan 1
                      ->  Sort  (cost=47983.01..47983.01 rows=1 width=18)
                            Sort Key: oi.id DESC
                            ->  Rownum  (cost=0.00..47983.00 rows=1 width=18)
                                  StopKey: (ROWNUM = '***')
                                  ->  Seq Scan on order_items oi  (cost=0.00..47983.00 rows=1 width=18)
                                        Filter: (order_id = o.id), (Expression Flatten Optimized)

      执行计划强制使用全表扫描（Seq Scan），避免了大量回表和Filter过滤操作。在高负载场景下，暂时无法创建更优索引时，可使用SQL Patch作为临时调优手段。

8. 对比优化前后指标。可以看到执行速度得到了有效提升。

   指标	优化前	优化后	提升
   扫描方式	Index Scan Backward	Seq Scan	-
   执行时间	2.1985s	1.0434s	2.11倍

方案二：创建最优复合索引

• orders表没有user_id、id相关索引，导致必须全表扫描，再筛选、排序。
• order_items没有order_id + id + product_name索引，子查询只能用主键反向扫描+大量回表，无法快速定位当前订单的商品。

在业务非高负载期间，可以通过分析执行计划来创建更优的索引。

1. 创建如下复合索引。

   -- 1. 解决 orders 全表扫描 + 额外排序
   CREATE INDEX idx_orders_user_id_id ON orders(user_id, id DESC);
   
   -- 2. 解决子查询慢，消除低效扫描
   CREATE INDEX idx_order_items_order_id_id_product_name ON order_items(order_id, id DESC, product_name);

2. 对于指定的慢SQL，检查是否存在已创建的SQL Patch，若存在需关闭或删除SQL Patch。
   1. 登录云数据库GaussDB控制台。
   2. 在“实例管理”页面，选择指定的实例，单击实例的名称，进入实例详情页面。
   3. 单击“诊断优化 > SQL诊断”。
   4. 在“慢SQL”页签，选择指定节点，时间范围，组合过滤SQL文本、归一化SQL ID以及其他扩展字段，单击查询按钮，获取慢SQL列表。
   5. 在慢SQL列表，选择对应慢SQL，在“操作”列单击“更多 > SQL Patch”。
      图10 查看SQL Patch详情
      

   6. 如果已创建SQL PATCH，单击按钮关闭SQL Patch，或单击“删除”按钮直接删除SQL Patch。
      图11 关闭/删除SQL Patch
      

3. 验证效果。
   1. 重新执行SQL语句。

      SELECT o.order_no, o.total_amount, o.customer_name, o.status, o.create_time,
             (SELECT oi.product_name FROM order_items oi WHERE oi.order_id = o.id AND ROWNUM = 1 ORDER BY oi.id DESC) as last_product
      FROM orders o
      WHERE o.user_id = 12345
      ORDER BY o.id DESC
      LIMIT 20;

   2. 执行完后得到如下的SQL详情。执行时间为0.001s，执行计划如下。
      图12 查看SQL计划和执行时间
      

      执行计划：

      [Parameterized]
      Datanode Name: dn_6001_6002_6003
      Limit  (cost=0.00..3.54 rows=1 width=61)
        ->  Index Scan using idx_orders_user_id_id on orders o  (cost=0.00..3.54 rows=1 width=61)
              Index Cond: (user_id = '***')
              SubPlan 1
                ->  Rownum  (cost=0.00..1.27 rows=1 width=18)
                      StopKey: (ROWNUM = '***')
                      ->  Index Only Scan using idx_order_items_order_id_id_product_name on order_items oi  (cost=0.00..1.27 rows=1 width=18)
                            Index Cond: (order_id = o.id)

      总耗时从2.1985s降至0.001s，执行效率提升2198.5倍。orders表从主键全扫描+Filter改为索引查找，order_items子查询实现了仅索引扫描（Index Only Scan）， 避免了回表操作和全表顺序扫描，大幅降低了随机 I/O 和总查询成本，远超未添加索引时的查询效率。

问题场景

电商平台进行大促活动期间，某爆款商品页面被大量用户频繁访问。后台持续执行查询商品库存的SQL，每秒执行次数超过10000次，导致数据库CPU使用率飙升至90%+。该SQL属于并发型高危慢SQL，高频执行会导致数据库资源被大量占用，从而引起其他正常业务（如订单提交、支付）响应缓慢甚至超时。

问题SQL

SELECT id, product_name, price, stock, status
FROM products
WHERE id = 991015 and status = 'available' and stock > 0;

问题表现

优化方案

验证效果

本案例中将并发数设置为200，同一时刻该SQL最多同时执行200次，超过限制时将报错拦截，以防止在业务高峰期时，并发型高危慢SQL导致其他正常业务（如订单提交、支付）响应缓慢甚至超时。

【执行SQL：(1)】
SELECT id, product_name, price, stock, status
FROM products
WHERE id = 991015 and status = 'available' and stock > 0;
执行失败，失败原因：[***:52656/***:8635] ERROR: The workload rule takes effect and this request will be cancelled. rule_id: 1, rule_name: "sql-limit"

问题场景

某电商后台管理系统新上线了“订单统计导出”功能，用户选择“导出全部订单”时，SQL缺少分页限制，一次性查询几百万条订单数据。该SQL首次执行时导致数据库内存耗尽（OOM），严重时可能触发实例宕机，影响全部业务。运维人员需要立即拦截该高危慢SQL，防止再次执行，但业务代码修复需要走发布流程（预计2小时），期间需用ABORT补丁紧急止损。

问题SQL

SELECT u.username, u.email, o.order_no, o.total_amount, o.customer_name, o.status, o.create_time
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.create_time >= '2025-01-01'
ORDER BY o.create_time DESC;
-- 缺少 LIMIT，一次性返回几百万条数据

问题表现

优化方案

通过GaussDB服务SQL Patch的ABORT功能，禁止该SQL执行，并提前报错返回，从而避免再次触发崩溃。

1. 登录云数据库GaussDB控制台。
2. 在“实例管理”页面，选择指定的实例，单击实例的名称，进入实例详情页面。
3. 单击“诊断优化 > SQL诊断”。
4. 在“慢SQL”页签，用户可按需选择指定节点，时间范围，SQL文本，SQL阈值，单击查询按钮，获取慢SQL列表。
5. 在慢SQL列表找到执行的SQL，下拉更多，找到SQL Patch。
   图15 创建SQL Patch
   

6. 选择PATCH类型为ABORT，输入PATCH名称，点击创建。
   图16 配置SQL PATCH
   

验证效果

重新执行SQL语句，会被限制住：

【执行SQL：(1)】
SELECT u.username, u.email, o.order_no, o.total_amount, o.customer_name, o.status, o.create_time
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.create_time >= '2025-01-01'
ORDER BY o.create_time DESC;
执行失败，失败原因：[***:52656/***:8635] ERROR: Statement 1858775530 canceled by abort patch test2

禁止该SQL执行，提前报错返回，避免再次触发崩溃。