PolarDB这个条 SQL，强行列存的效果还没有行存查询高，怎么回事？

PolarDB这个条 SQL，强行列存的效果还没有行存查询高，怎么回事？SELECT
/+ SET_VAR(cost_threshold_for_imci=0) /
roi.merchant_id,
roi.sku_id,
roi.machine_code,
roi.door_num,
roi.sku_name,
SUM(
CASE
roi.create_type
WHEN 1 THEN
roi.sku_num
WHEN 3 THEN
roi.sku_num
WHEN 2 THEN

roi.sku_num
WHEN 5 THEN
roi.sku_num
WHEN 6 THEN
roi.sku_num ELSE 0
END
) AS skuSaleNum,
SUM(
CASE
roi.create_type
WHEN 1 THEN
roi.amount
WHEN 3 THEN
roi.amount
WHEN 2 THEN
- roi.amount
  WHEN 5 THEN
  roi.amount
  WHEN 6 THEN
- roi.amount ELSE 0
  END
  ) AS skuSaleAmount
  FROM
  r_order_item roi
  WHERE
  roi.order_id IN (
  SELECT
  r.order_id
  FROM
  r_order r
  WHERE
  r.anomaly_status IN ( 1, 9 )
  AND r.close_type IN ( 0, 1, 5, 9, 10 )
  AND r.company_id = 314
  AND r.merchant_id = 2532
  AND r.create_time BETWEEN '2024-04-01 22:07:13.389'
  AND '2024-04-18 23:59:59.999'
  AND r.amount != 0
  )
  AND roi.STATUS = 1
  AND roi.sku_id NOT IN ( 2, 10 )
  GROUP BY
  roi.machine_id,
  roi.door_num,
  roi.sku_id
  ORDER BY
  skuSaleNum DESC

展开

收起

三分钟热度的鱼 2024-04-24 23:33:38 63 版权

阿里云 AI 助理回答

问题分析

您提到的SQL语句在强制使用列存索引（通过/*+ SET_VAR(cost_threshold_for_imci=0) */）后，查询性能反而不如行存查询。这种情况可能由以下几个原因导致：

1. 查询代价模型与优化器选择

PolarDB的优化器会根据查询的预估执行代价来决定是否使用列存索引。即使强制指定列存索引，如果查询涉及的数据量较小或查询模式不适合列存索引，优化器可能会选择次优的执行计划。

数据量不足：列存索引适合处理大规模扫描和聚合操作。如果查询涉及的数据量较少，列存索引的优势无法体现，甚至可能因为额外的开销（如统计信息计算、数据块过滤等）导致性能下降。
复杂条件过滤：您的SQL包含多个过滤条件（如r.anomaly_status IN (1, 9)、r.close_type IN (0, 1, 5, 9, 10)等），这些条件可能导致列存索引的粗糙索引机制无法有效过滤数据，从而增加不必要的数据访问。

建议： - 使用EXPLAIN查看执行计划，确认是否真正使用了列存索引，并检查是否有过多的数据块被扫描。 - 如果数据量较小，考虑移除强制列存索引的Hint，让优化器自行选择更优的执行计划。

2. 列存索引覆盖性不足

列存索引只能访问被其所覆盖的列。如果SQL中涉及的列未被列存索引完全覆盖，优化器可能需要回退到行存索引以补充缺失的列数据，这会导致额外的开销。

检查列存索引覆盖性：使用存储过程dbms_imci.check_columnar_index()验证SQL中涉及的所有列是否被列存索引覆盖。例如：
```
CALL dbms_imci.check_columnar_index('SELECT roi.merchant_id, roi.sku_id, ... FROM r_order_item roi WHERE ...');
```
如果返回结果中存在未被覆盖的列，说明列存索引不完整，需为相关表创建完整的列存索引。
生成列存索引DDL：如果发现列存索引不完整，可以使用dbms_imci.columnar_advise()生成相应的DDL语句，确保所有涉及的列都被覆盖。

3. 查询模式不适合列存索引

列存索引适合处理大规模扫描和聚合操作，但对于某些特定的查询模式（如点查、小范围查询、复杂JOIN等），其性能可能不如行存索引。

复杂JOIN操作：您的SQL中包含子查询（roi.order_id IN (SELECT r.order_id FROM r_order r WHERE ...)），这种复杂的JOIN操作可能导致列存索引的性能优势无法体现。
GROUP BY和ORDER BY：虽然列存索引对聚合操作有一定加速效果，但如果分组字段或排序字段未被列存索引覆盖，可能会导致额外的开销。

建议： - 确认子查询和JOIN操作是否可以通过改写优化。例如，将子查询改写为JOIN，减少嵌套层级。 - 检查GROUP BY和ORDER BY字段是否被列存索引覆盖。

4. 资源负载与并行查询限制

列存索引的性能依赖于并行查询的能力。如果集群资源负载较高，或者并行查询未能有效拆分任务，可能导致列存索引的性能下降。

并行查询未生效：使用EXPLAIN查看执行计划，确认是否启用了并行查询（如Parallel Scan字段）。如果未启用，可能是由于以下原因：
- 查询数据量不足以触发并行查询。
- 集群地址为可读可写模式，默认路由到主节点，而主节点可能未开启并行查询。
资源负载抖动：如果集群资源负载较高，可能导致并行查询被限制回退为串行执行。

建议： - 确保SQL被转发到只读列存节点，并通过/* FORCE_IMCI_NODES */强制指定列存节点执行。 - 检查集群资源负载，避免高负载情况下执行复杂查询。

5. Hint设置不当

您使用了/*+ SET_VAR(cost_threshold_for_imci=0) */强制指定列存索引，但这种方式可能忽略了其他影响性能的因素。

阈值设置过低：将cost_threshold_for_imci设置为0可能导致优化器忽略实际的查询代价，选择次优的执行计划。
Hint冲突：如果同时使用了其他Hint（如FORCE_SLAVE），可能导致执行计划混乱。

建议： - 尝试移除SET_VAR Hint，观察优化器是否能自动选择更优的执行计划。 - 如果必须使用Hint，确保与其他Hint无冲突。

6. 其他潜在问题

统计信息不准确：列存索引依赖统计信息进行数据块过滤。如果统计信息未及时更新，可能导致优化器选择错误的执行计划。
查询特性限制：某些SQL特性（如窗口函数、递归查询等）可能不支持列存索引。请参考列存索引的使用限制列表，确认SQL是否符合要求。

总结与操作步骤

检查列存索引覆盖性：
- 使用dbms_imci.check_columnar_index()验证SQL中涉及的列是否被列存索引完全覆盖。
- 如果未完全覆盖，使用dbms_imci.columnar_advise()生成并应用列存索引DDL。
优化查询模式：
- 改写复杂子查询为JOIN，减少嵌套层级。
- 确认GROUP BY和ORDER BY字段是否被列存索引覆盖。
确认并行查询生效：
- 使用EXPLAIN查看执行计划，确认是否启用了并行查询。
- 确保SQL被转发到只读列存节点，并通过/* FORCE_IMCI_NODES */强制指定列存节点执行。
调整Hint设置：
- 移除SET_VAR Hint，观察优化器是否能自动选择更优的执行计划。
- 确保Hint之间无冲突。
检查统计信息与资源负载：
- 确保统计信息已更新，避免因统计信息不准确导致优化器选择错误的执行计划。
- 检查集群资源负载，避免高负载情况下执行复杂查询。