查询SQL优化问题

AntTech_CTIJUU · 2025 年1 月 20 日 11:01

【使用环境】测试环境
【 OB or 其他组件】OB
【使用版本】4.3.4
【问题描述】有一张sc_order_ob_test表，表里总共又八千多W的数据，表按createtime和vcardid做了分区，查询的时候加了并行查询，查询还需要27s左右，很慢，vcardid ，paystatus，order_type这三个字段也有联合索引。应该如何优化呢。具体的SQL如下
SELECT /*+ PARALLEL(sc_order_ob_test 8) */ COUNT(DISTINCT userid)
FROM sc_order_ob_test
WHERE vcardid = 38 AND paystatus = 1 AND order_type = 0
【复现路径】问题出现前后相关操作
【附件及日志】

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

论坛小助手 · 2025 年1 月 20 日 11:02

推荐使用 obdiag 工具进行日志采集，特别是针对SQL性能问题的日志采集，并且将采集到的日志提供给我们以便确认问题所在。

日志采集场景: SQL性能问题
日志采集命令: obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”

请根据上述命令采集日志并提供给我们。为了更好地帮助您优化查询性能，还需要请您提供以下信息：

该表的结构定义（包括所有字段类型、索引等），可以通过SHOW CREATE TABLE sc_order_ob_test;获取。
表中数据分布情况，例如不同vcardid、paystatus和order_type组合的大致记录数。
是否存在其他并发查询或写入操作影响当前查询性能？
OceanBase 集群的硬件配置（如CPU、内存、磁盘类型）以及集群规模（如节点数量）。
当前系统负载状况，在执行这条查询语句时是否有其他高负载任务同时运行？

通过以上信息可以帮助我们更准确地定位问题并给出有效的优化建议。

附上敏捷诊断工具 obdiag 使用帮助链接

AntTech_CTIJUU · 2025 年1 月 20 日 11:04

这个是表结构
CREATE TABLE sc_order_ob_test (
id bigint(20) NOT NULL AUTO_INCREMENT,
authcode bigint(20) DEFAULT NULL,
countprice decimal(19,2) DEFAULT NULL,
createtime datetime NOT NULL,
customertype int(11) DEFAULT NULL,
disable int(11) DEFAULT NULL,
discount decimal(19,2) DEFAULT NULL,
follow_ticket varchar(255) DEFAULT NULL,
follow_ticket_time datetime DEFAULT NULL,
iccard varchar(255) DEFAULT NULL,
invoice_time datetime DEFAULT NULL,
invoice_title varchar(255) DEFAULT NULL,
invoice_type varchar(255) DEFAULT NULL,
is_comment int(11) DEFAULT NULL,
is_invoice int(11) DEFAULT NULL,
isbill int(11) NOT NULL,
mj_coupon_discount decimal(19,2) DEFAULT NULL,
mj_discount decimal(19,2) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
native_orderno varchar(255) DEFAULT NULL,
number decimal(19,2) DEFAULT NULL,
openid varchar(255) DEFAULT NULL,
order_type int(11) DEFAULT NULL,
orderno varchar(255) NOT NULL,
paymoney decimal(19,2) DEFAULT NULL,
payname varchar(255) DEFAULT NULL,
paysource int(11) DEFAULT NULL,
paystatus int(11) DEFAULT NULL,
paystatustime datetime DEFAULT NULL,
paytime datetime DEFAULT NULL,
port varchar(255) DEFAULT NULL,
price decimal(19,2) DEFAULT NULL,
remind varchar(3000) DEFAULT NULL,
sourcetype int(11) DEFAULT NULL,
spid int(11) DEFAULT NULL,
sta_coupon_discount decimal(19,2) DEFAULT NULL,
sta_discount decimal(19,2) DEFAULT NULL,
state int(11) DEFAULT NULL,
taxnumber varchar(255) DEFAULT NULL,
third_content longtext DEFAULT NULL,
third_orderno varchar(255) DEFAULT NULL,
updatetime datetime DEFAULT NULL,
cardid bigint(20) DEFAULT NULL,
class_shift bigint(20) DEFAULT NULL,
gun bigint(20) DEFAULT NULL,
mj_promotionid bigint(20) DEFAULT NULL,
oligasid bigint(20) DEFAULT NULL,
openeruserid bigint(20) DEFAULT NULL,
plateid bigint(20) DEFAULT NULL,
promotionid bigint(20) DEFAULT NULL,
rechargeid bigint(20) DEFAULT NULL,
stationid bigint(20) DEFAULT NULL,
userid bigint(20) DEFAULT NULL,
vcardid bigint(20) NOT NULL,
paylabel varchar(255) DEFAULT NULL,
platform_price decimal(19,2) DEFAULT NULL,
pay_mer_no varchar(255) DEFAULT NULL,
remark text DEFAULT NULL,
univalent decimal(19,2) DEFAULT NULL,
use_balance decimal(19,2) DEFAULT NULL,
use_givebalance decimal(19,2) DEFAULT NULL,
radio_iccard bigint(20) DEFAULT NULL,
finishactivity int(11) DEFAULT NULL,
handle_give int(11) DEFAULT NULL,
pay_promotionid bigint(20) DEFAULT NULL,
pay_pro_discount decimal(19,2) DEFAULT NULL,
handle_share int(11) DEFAULT NULL,
appid bigint(20) DEFAULT NULL,
can_refund_givemoney decimal(19,2) DEFAULT NULL,
can_refund_integral bigint(20) DEFAULT NULL,
can_refund_money decimal(19,2) DEFAULT NULL,
refundmoney decimal(19,2) DEFAULT NULL,
allinpay_orderid bigint(20) DEFAULT NULL,
lasttime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
accountid bigint(20) DEFAULT NULL,
ismessage int(11) DEFAULT NULL,
orderfeesid bigint(20) DEFAULT NULL,
channel int(11) DEFAULT NULL,
pay_mer_appid varchar(255) DEFAULT NULL,
pay_mer_key varchar(3000) DEFAULT NULL,
refundstatus int(11) DEFAULT NULL,
fuelcard varchar(255) DEFAULT NULL,
account varchar(255) DEFAULT NULL,
card varchar(255) DEFAULT NULL,
cardnumber varchar(255) DEFAULT NULL,
classid bigint(20) DEFAULT NULL,
company varchar(255) DEFAULT NULL,
companyid bigint(20) DEFAULT NULL,
csid bigint(20) DEFAULT NULL,
guns int(11) DEFAULT NULL,
icnumber varchar(255) DEFAULT NULL,
integral decimal(19,2) DEFAULT NULL,
iscredit int(11) DEFAULT NULL,
oil varchar(255) DEFAULT NULL,
opener varchar(255) DEFAULT NULL,
platenumber varchar(255) DEFAULT NULL,
station varchar(255) DEFAULT NULL,
user varchar(255) DEFAULT NULL,
ictype int(11) DEFAULT NULL,
fuelid bigint(20) DEFAULT NULL,
linkid bigint(20) DEFAULT NULL,
goods_orderno varchar(255) DEFAULT NULL,
recharge_grouprule bigint(20) DEFAULT NULL,
phonei bigint(20) DEFAULT NULL,
recharge_stationid bigint(20) DEFAULT NULL,
is_sale varchar(255) DEFAULT NULL,
begintime datetime DEFAULT NULL COMMENT ‘提枪时间’,
start_pressure decimal(9,0) DEFAULT NULL,
end_pressure decimal(9,0) DEFAULT NULL,
PRIMARY KEY (id, createtime, vcardid),
UNIQUE KEY UK_af4dnlqcgv1ruqqmwb7th6n7u (orderno, createtime, vcardid) BLOCK_SIZE 16384 LOCAL,
KEY lasttime_paystatus (lasttime, paystatus, stationid) BLOCK_SIZE 16384 LOCAL,
KEY order_paytime (paytime, vcardid, paystatus, order_type, stationid) BLOCK_SIZE 16384 LOCAL,
KEY fuelcard (fuelcard) BLOCK_SIZE 16384 LOCAL,
KEY idx_vcardid_ordertype_createtime_number (vcardid, order_type, createtime, number) BLOCK_SIZE 16384 LOCAL,
KEY idx_gun_stationid_paystatus_sourcetype_createtime (gun, stationid, paystatus, sourcetype, createtime) BLOCK_SIZE 16384 LOCAL,
KEY order_create_station (createtime, stationid) BLOCK_SIZE 16384 LOCAL,
KEY sfc (stationid, fuelid, classid) BLOCK_SIZE 16384 LOCAL,
KEY plateid (plateid) BLOCK_SIZE 16384 LOCAL,
KEY idx_stationid_ordertype_paystatus_state_createtime_paymoney (stationid, order_type, paystatus, state, createtime, paymoney) BLOCK_SIZE 16384 LOCAL,
KEY FK5w74p9tsat109r3q5c9xth7ln (appid) BLOCK_SIZE 16384 LOCAL,
KEY FKjufoy2gme1u80mb62q31ecb61 (userid, plateid) BLOCK_SIZE 16384 LOCAL,
KEY idx_gun_stationid_paystatus_state_createtime_orderno (gun, stationid, paystatus, state, createtime, orderno) BLOCK_SIZE 16384 LOCAL,
KEY FKrfhtj7ugwwmteesgcik7tauku (cardid) BLOCK_SIZE 16384 LOCAL,
KEY idx_vcardid_customertype_paystatus_state_createtime_paymoney (vcardid, customertype, paystatus, state, createtime, paymoney) BLOCK_SIZE 16384 LOCAL,
KEY FKm3unsqwq81wq9rneeam63097e (class_shift) BLOCK_SIZE 16384 LOCAL,
KEY idx_vcardid_paystatus_ordertype (vcardid, paystatus, order_type) BLOCK_SIZE 16384 LOCAL
) AUTO_INCREMENT = 116882590 AUTO_INCREMENT_MODE = ‘ORDER’ DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by range columns(createtime) subpartition by hash(vcardid) subpartition template (
subpartition p0,
subpartition p1,
subpartition p2,
subpartition p3)
(partition p2018_q1 values less than (‘2018-04-01 00:00:00’),
partition p2018_q2 values less than (‘2018-07-01 00:00:00’),
partition p2018_q3 values less than (‘2018-10-01 00:00:00’),
partition p2018_q4 values less than (‘2019-01-01 00:00:00’),
partition p2019_q1 values less than (‘2019-04-01 00:00:00’),
partition p2019_q2 values less than (‘2019-07-01 00:00:00’),
partition p2019_q3 values less than (‘2019-10-01 00:00:00’),
partition p2019_q4 values less than (‘2020-01-01 00:00:00’),
partition p2020_q1 values less than (‘2020-04-01 00:00:00’),
partition p2020_q2 values less than (‘2020-07-01 00:00:00’),
partition p2020_q3 values less than (‘2020-10-01 00:00:00’),
partition p2020_q4 values less than (‘2021-01-01 00:00:00’),
partition p2021_q1 values less than (‘2021-04-01 00:00:00’),
partition p2021_q2 values less than (‘2021-07-01 00:00:00’),
partition p2021_q3 values less than (‘2021-10-01 00:00:00’),
partition p2021_q4 values less than (‘2022-01-01 00:00:00’),
partition p2022_q1 values less than (‘2022-04-01 00:00:00’),
partition p2022_q2 values less than (‘2022-07-01 00:00:00’),
partition p2022_q3 values less than (‘2022-10-01 00:00:00’),
partition p2022_q4 values less than (‘2023-01-01 00:00:00’),
partition p2023_q1 values less than (‘2023-04-01 00:00:00’),
partition p2023_q2 values less than (‘2023-07-01 00:00:00’),
partition p2023_q3 values less than (‘2023-10-01 00:00:00’),
partition p2023_q4 values less than (‘2024-01-01 00:00:00’),
partition p2024_q1 values less than (‘2024-04-01 00:00:00’),
partition p2024_q2 values less than (‘2024-07-01 00:00:00’),
partition p2024_q3 values less than (‘2024-10-01 00:00:00’),
partition p2024_q4 values less than (‘2025-01-01 00:00:00’),
partition p2025_q1 values less than (‘2025-04-01 00:00:00’),
partition p2025_q2 values less than (‘2025-07-01 00:00:00’),
partition p2025_q3 values less than (‘2025-10-01 00:00:00’),
partition p2025_q4 values less than (‘2026-01-01 00:00:00’),
partition p2026_q1 values less than (‘2026-04-01 00:00:00’),
partition p_default values less than (MAXVALUE))

论坛小助手 · 2025 年1 月 20 日 11:13

根据您提供的表结构信息，sc_order_ob_test 表已经按照 createtime 和 vcardid 进行了分区，并且为 vcardid、paystatus 和 order_type 创建了联合索引。然而，查询性能仍然不理想。为了进一步优化查询性能，可以考虑以下几个方面：

1. 检查现有索引的有效性

您的查询使用了 vcardid = 38 AND paystatus = 1 AND order_type = 0 条件，并且有一个联合索引 idx_vcardid_paystatus_ordertype (vcardid, paystatus, order_type)。理论上这个索引应该能够很好地支持您的查询。
然而，由于查询中使用了 COUNT(DISTINCT userid)，这可能会导致即使有合适的索引，查询仍然需要扫描大量数据来计算唯一值。建议您通过 EXPLAIN 命令查看查询执行计划，确认是否确实使用了该索引。

2. 优化查询语句

减少 DISTINCT 的使用：COUNT(DISTINCT userid) 可能会导致性能问题，因为它需要对 userid 进行去重操作。如果业务场景允许，可以考虑是否可以通过其他方式简化查询逻辑，例如：

SELECT COUNT(userid)
FROM sc_order_ob_test
WHERE vcardid = 38 AND paystatus = 1 AND order_type = 0
GROUP BY userid;

或者：

SELECT COUNT(*)
FROM (SELECT DISTINCT userid
      FROM sc_order_ob_test
      WHERE vcardid = 38 AND paystatus = 1 AND order_type = 0) AS t;

3. 调整分区策略

当前表是按 createtime 进行一级分区，按 vcardid 进行二级分区。考虑到查询条件中并没有涉及 createtime，这意味着查询可能无法有效利用一级分区的优势，导致全表扫描或扫描多个分区。
如果查询条件主要集中在 vcardid、paystatus 和 order_type，可以考虑重新设计分区策略，使得查询能够更高效地定位到相关分区。例如：
- 将 vcardid 作为一级分区键，createtime 作为二级分区键。
- 或者，如果 createtime 对查询影响不大，可以考虑只按 vcardid 进行分区。

4. 增加覆盖索引

覆盖索引是指查询所需的所有列都在索引中，这样数据库可以直接从索引中获取数据，而不需要回表查询。对于您的查询，可以考虑创建一个包含 userid 的覆盖索引：
```
CREATE INDEX idx_vcardid_paystatus_ordertype_userid ON sc_order_ob_test(vcardid, paystatus, order_type, userid);
```

5. 并行查询的优化

您已经在查询中使用了 PARALLEL(sc_order_ob_test 8) 提示，但需要注意的是，并行度设置过高可能会导致资源争用，反而降低性能。建议根据实际硬件资源和集群负载情况，适当调整并行度。

6. 分析表和索引

定期对表和索引进行分析，确保统计信息是最新的，以便查询优化器能够做出更好的决策：
```
ANALYZE TABLE sc_order_ob_test;
```

7. 检查系统配置和硬件资源

确认 OceanBase 集群的硬件配置（如 CPU、内存、磁盘类型）以及集群规模（如节点数量），确保有足够的资源支持查询操作。
同时，检查当前系统负载状况，在执行这条查询语句时是否有其他高负载任务同时运行。

8. 收集更多诊断信息

使用 obdiag 工具采集日志，特别是针对 SQL 性能问题的日志，以便更深入地分析查询性能瓶颈：

obdiag gather scene run --scene=observer.perf_sql --env "{db_connect='-h127.0.0.1 -P2881 -utest@test -p****** -Dtest', trace_id='Yxx'}"

通过以上措施，您可以逐步排查并优化查询性能。如果问题依然存在，建议进一步收集详细的诊断信息并与 OceanBase 技术支持团队联系以获取更多帮助。

检索到的文档如下:

(小助手的答复已结束，如未能解决您的问题，请继续提问并等待其他同学的回复，谢谢！)

淇铭 · 2025 年1 月 20 日 14:09

1、发一下执行计划 explain extended sql 保存在文本里
2、收集SQL性能问题信息

obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”
https://www.oceanbase.com/docs/common-obdiag-cn-1000000002023082

AntTech_MNIQLB · 2025 年1 月 20 日 16:52

vcardid ，paystatus，order_type，userid 四个字段构建一个索引试试