子查询in上层父查询增加查询条件后查询很慢

OceanBase
,
#1

【 使用环境 】测试环境
【 OB or 其他组件 】单机版本的OB
【 使用版本 】4.1.0
【问题描述】单机版OB，没有对表进行分片，子查询in的上层父查询增加查询条件后SQL性能变得很慢，如何进行SQL优化？
【问题现象及影响】
父查询没有增加查询条件的现象如下图：

企业微信截图_20230411103412
企业微信截图_202304111034121585×772 41.7 KB

企业微信截图_20230411103643
企业微信截图_202304111036431628×967 79.6 KB

父查询增加查询条件后的现象如下图：

企业微信截图_20230411105827
企业微信截图_202304111058271587×847 47.1 KB

企业微信截图_20230411105859
企业微信截图_202304111058591601×1014 80.6 KB

【附件】

#4

你好，查询语句和建表语句能发下吗？
方便的话表数据脱敏也发下

#5

第二个图的JOIN 与第一个不同，可能是慢的原因。试试加hint /*+ use_hash(p op) */

#6

SELECT
COUNT(1)
FROM
woke_c_order o
WHERE
o.id IN (
/*+ use_hash(p op) */
SELECT
order_id
FROM
woke_c_order_product op,
woke_c_product p
WHERE
op.product_id = p.id
AND p.nsy_code IS NOT NULL
)
and o.to_crm = 1

是这样写吗？没反应啊

#7 
CREATE TABLE `woke_c_order` (
  `id` varchar(64) NOT NULL COMMENT '订单id',
  `tenant_id` varchar(64) NOT NULL COMMENT '租户id',
  `p_id` varchar(64) DEFAULT NULL COMMENT '订单父id 针对赠送订单关联主订单',
  `client_id` varchar(64) NOT NULL COMMENT '客户关系id',
  `order_no` varchar(64) NOT NULL COMMENT '订单编号',
  `contract_no` text DEFAULT NULL COMMENT '合同编号',
  `invoice_no` varchar(100) DEFAULT NULL COMMENT '发票编号',
  `contract_status` tinyint(1) DEFAULT NULL COMMENT '合同状态 1草稿，2正式、0未签署',
  `invoice_status` tinyint(1) DEFAULT NULL COMMENT '发票状态(-1未申请、0审核中、1开票中、2开票成功、3审核拒绝、4开票失败)',
  `order_amount` decimal(11,2) DEFAULT NULL COMMENT '订单总价格',
  `boss_name` varchar(64) DEFAULT NULL COMMENT '老板姓名',
  `boss_tel` varchar(64) DEFAULT NULL COMMENT '老板电话',
  `boss_email` varchar(64) DEFAULT NULL COMMENT '老板邮箱',
  `order_type` varchar(64) DEFAULT NULL COMMENT '订单类型（字典模式）',
  `from_systerm` varchar(64) DEFAULT NULL COMMENT '系统来源 字典',
  `open_class_time` datetime DEFAULT NULL COMMENT '开课时间',
  `open_class_type` varchar(64) DEFAULT NULL COMMENT '开课类别',
  `open_class_name` varchar(64) DEFAULT NULL COMMENT '开课名称',
  `open_class_place` varchar(64) DEFAULT NULL COMMENT '开课地点',
  `navigation_seller` varchar(64) DEFAULT NULL COMMENT '导航班销售',
  `navigation_company_name` varchar(64) DEFAULT NULL COMMENT '导航版公司名称',
  `navigation_department` varchar(64) DEFAULT NULL COMMENT '导航班部门/分公司',
  `chance` varchar(64) DEFAULT NULL COMMENT '商机id',
  `counselor` text DEFAULT NULL COMMENT '学习顾问',
  `creater` varchar(64) DEFAULT NULL COMMENT '创建人',
  `ower` varchar(64) DEFAULT NULL COMMENT '所属人',
  `seller` varchar(64) DEFAULT NULL COMMENT '所属销售人员',
  `caller` varchar(64) DEFAULT NULL COMMENT '客户人员',
  `to_crm` varchar(1) DEFAULT '0' COMMENT '1 订单插入crm成功 0 订单插入crm不成功',
  `is_to_crm` varchar(1) DEFAULT NULL COMMENT '是否需要传入crm：1是、0否',
  `promoter` varchar(64) DEFAULT NULL COMMENT '推广人员',
  `updater` varchar(64) DEFAULT NULL COMMENT '更新人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deal_reason` text DEFAULT NULL COMMENT '成交原因',
  `deal_time` datetime DEFAULT NULL COMMENT '成交时间',
  `attachment` text DEFAULT NULL COMMENT '附件',
  `remark` text DEFAULT NULL COMMENT '备注',
  `order_status` varchar(1) DEFAULT '1' COMMENT '订单状态 1 生效 0 不生效',
  `old_amount` varchar(64) DEFAULT '0' COMMENT '订单原始金额',
  `order_count` varchar(64) DEFAULT '0' COMMENT '订单对应的产品总量',
  `receive_amount` varchar(64) DEFAULT '0' COMMENT '回款总金额',
  `un_receive_amount` varchar(64) DEFAULT '0' COMMENT '未回款金额',
  `receive_per` varchar(64) DEFAULT '0' COMMENT '回款进度',
  `overdue_status` varchar(64) DEFAULT '0' COMMENT '逾期状态 1逾期 0 没逾期',
  `delete_reason` text DEFAULT NULL COMMENT '作废理由',
  `invoice_header` varchar(64) DEFAULT NULL COMMENT '发票抬头',
  `contract_time` datetime DEFAULT NULL COMMENT '合同时间',
  `invoice_time` datetime DEFAULT NULL COMMENT '发票时间',
  `crm_cw_time` datetime DEFAULT NULL COMMENT 'crm财务审核时间',
  `discount_amount` varchar(64) DEFAULT '0' COMMENT '总折扣额',
  `is_free` varchar(1) DEFAULT '0' COMMENT '是否赠送 1 是 0 否',
  `transaction_person` varchar(64) DEFAULT NULL COMMENT '成交人',
  `ower_table` varchar(64) DEFAULT NULL COMMENT '桌长',
  `promote_person` varchar(64) DEFAULT NULL COMMENT '推广人',
  `payee` varchar(64) DEFAULT NULL COMMENT '刷卡人',
  `trace_person` varchar(64) DEFAULT NULL COMMENT '跟单人',
  `yyr` varchar(255) DEFAULT NULL COMMENT '邀约人',
  `zg` varchar(255) DEFAULT NULL COMMENT '助攻',
  `qds` varchar(255) DEFAULT NULL COMMENT '渠道商',
  `to_nswyun` tinyint(1) DEFAULT NULL COMMENT '传到牛商云是否成：1成功、0失败',
  `xysl` int(11) DEFAULT '1' COMMENT '学员数量',
  `bmbpz` varchar(255) DEFAULT NULL COMMENT '报名表凭证',
  `lscode` varchar(255) DEFAULT NULL COMMENT '支付流水编号',
  `jfzt` varchar(50) DEFAULT NULL COMMENT '交付状态',
  `order_type_new` varchar(50) DEFAULT NULL COMMENT '订单类型-字典',
  `order_origin` varchar(50) DEFAULT NULL COMMENT '订单来源-字典',
  `order_level` varchar(20) DEFAULT 'LEAF' COMMENT '订单层级',
  `intention_city` varchar(50) DEFAULT NULL COMMENT '意向城市',
  `deposit_order_ids` varchar(255) DEFAULT NULL COMMENT '定金订单ID，多个用逗号隔开',
  `idddzt` varchar(50) DEFAULT '4028818340acf3d50140b9be330e48ad' COMMENT '订单审批状态',
  `hwshr` varchar(50) DEFAULT NULL COMMENT '会务审核人',
  `hwshsj` datetime DEFAULT NULL COMMENT '会务审核时间',
  `ywshr` varchar(50) DEFAULT NULL COMMENT '业务审核人',
  `ywshsj` datetime DEFAULT NULL COMMENT '业务审核时间',
  `xgshr` varchar(50) DEFAULT NULL COMMENT '销管审核人',
  `bzrq` datetime DEFAULT NULL COMMENT '销管审核日期',
  `cwshr` varchar(50) DEFAULT NULL COMMENT '财务审核人',
  `cwshsj` datetime DEFAULT NULL COMMENT '财务审核时间',
  `jfsj` datetime DEFAULT NULL COMMENT '交付时间',
  `sfhddd` tinyint(1) DEFAULT '0' COMMENT '是否活动订单',
  `yhcp` text DEFAULT NULL COMMENT '优惠凭证',
  PRIMARY KEY (`id`),
  KEY `ux_client_id` (`client_id`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_tenant_id` (`tenant_id`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_ower` (`ower`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_seller` (`seller`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_from_systerm` (`from_systerm`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_order_type` (`order_type`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_order_no` (`order_no`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_creater` (`creater`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_caller` (`caller`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_promoter` (`promoter`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_deal_time` (`deal_time`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_order_type_new` (`order_type_new`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_order_origin` (`order_origin`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_order_level` (`order_level`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_idddzt` (`idddzt`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_sfhddd` (`sfhddd`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_p_id` (`p_id`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_to_crm` (`to_crm`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_is_to_crm` (`is_to_crm`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_create_time` (`create_time`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_boss_tel` (`boss_tel`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_boss_name` (`boss_name`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_order_amount` (`order_amount`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_order_status` (`order_status`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_tenant_id_to_crm` (`tenant_id`, `to_crm`) BLOCK_SIZE 16384 LOCAL
) 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 COMMENT = '订单表';

CREATE TABLE `woke_c_order_product` (
  `id` varchar(64) NOT NULL,
  `tenant_id` varchar(64) NOT NULL COMMENT '租户id',
  `client_id` varchar(64) NOT NULL COMMENT '客户id',
  `order_id` varchar(64) DEFAULT NULL COMMENT '订单id',
  `product_id` varchar(64) NOT NULL COMMENT '产品id',
  `product_name` varchar(64) DEFAULT NULL COMMENT '产品名称',
  `product_price` varchar(64) DEFAULT NULL COMMENT '产品单价',
  `deal_price` varchar(64) DEFAULT '0' COMMENT '成交单价',
  `deal_count` varchar(64) DEFAULT '0' COMMENT '成交数量',
  `deal_amount` varchar(64) DEFAULT '0' COMMENT '成交总价',
  `receive_amount` varchar(64) DEFAULT '0' COMMENT '已收款',
  `remark` text DEFAULT NULL COMMENT '备注',
  `discount_per` varchar(64) DEFAULT '0' COMMENT '折扣率',
  `creater` varchar(64) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL,
  `updater` varchar(64) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT NULL,
  `stand_price` varchar(64) DEFAULT '0' COMMENT '标准价格 价格表价格',
  `old_price` varchar(64) DEFAULT '0' COMMENT '原价',
  `discount_amount` varchar(64) DEFAULT '0' COMMENT '总折扣额',
  PRIMARY KEY (`id`),
  KEY `idx_client_id` (`client_id`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_tenant_id` (`tenant_id`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_order_id` (`order_id`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_product_id` (`product_id`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_order_id_product_id` (`order_id`, `product_id`) BLOCK_SIZE 16384 LOCAL
) 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 COMMENT = '订单产品明细表';

CREATE TABLE `woke_c_product` (
  `id` varchar(64) NOT NULL,
  `tenant_id` varchar(64) NOT NULL COMMENT '租户id',
  `category_id` varchar(64) NOT NULL COMMENT '产品分类id',
  `name` varchar(100) NOT NULL COMMENT '产品名称',
  `is_split` int(1) DEFAULT '1',
  `thumb` text DEFAULT NULL COMMENT '产品缩略图',
  `remark` text DEFAULT NULL COMMENT '产品描述',
  `sell_score` varchar(9) DEFAULT '0' COMMENT '销售积分',
  `sort` varchar(11) DEFAULT '99' COMMENT '权重值',
  `price` decimal(9,2) DEFAULT NULL COMMENT '产品价格',
  `sell_remark` text DEFAULT NULL COMMENT '销售单位说明',
  `care_remark` text DEFAULT NULL COMMENT '注意事项说明',
  `create_user_id` varchar(64) NOT NULL COMMENT '创建人用户id',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用 1启用 0禁用',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '是否删除 1已删除 0未删除',
  `nsy_code` varchar(255) DEFAULT NULL COMMENT '牛商云产品编号',
  `deliver_count` tinyint(1) DEFAULT '1' COMMENT '交付数量',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_tenantId` (`tenant_id`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_is_split` (`is_split`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_is_delete` (`is_delete`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_nsy_code` (`nsy_code`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_sort` (`sort`) BLOCK_SIZE 16384 LOCAL,
  KEY `idx_is_active` (`is_active`) BLOCK_SIZE 16384 LOCAL
) 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;

表结构如上

#9

hint 放第一个select 后

#10

能否执行下下面的查询，然后截图执行计划发一下
SELECT
COUNT(1)
FROM
( select 【想要的值】 from woke_c_order where to_crm = 1) o WHERE o.id IN
(
SELECT order_id FROM woke_c_order_product op,
woke_c_product p WHERE op.product_id = p.id AND p.nsy_code IS NOT NULL
)

#11

试了没用，执行计划也没有变

企业微信截图_20230412085848
企业微信截图_202304120858481596×807 47.5 KB

企业微信截图_20230412085917
企业微信截图_202304120859171589×961 80.4 KB

#12

企业微信截图_20230412090321
企业微信截图_202304120903211592×888 49.4 KB

企业微信截图_20230412090415
企业微信截图_202304120904151920×680 53 KB

企业微信截图_20230412090508
企业微信截图_202304120905081602×1010 81.1 KB

#13

收到 我找人帮您看下

#14

查询语句中加上/+NO_REWRITE/ 试一下
类似这样
SELECT /+NO_REWRITE/
COUNT(1)
FROM
( select 【想要的值】 from woke_c_order where to_crm = 1) o WHERE o.id IN
(
SELECT order_id FROM woke_c_order_product op,
woke_c_product p WHERE op.product_id = p.id AND p.nsy_code IS NOT NULL
)

#15

企业微信截图_20230413121040
企业微信截图_202304131210401610×904 31.3 KB

企业微信截图_20230413121101
企业微信截图_202304131211011587×1013 80 KB

执行计划详情如下：

======================================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|

|0 |SCALAR GROUP BY| |1 |347674 |
|1 | SUBPLAN FILTER| |584 |347664 |
|2 | SUBPLAN SCAN |o |1168 |7363 |
|3 | TABLE SCAN |woke_c_order |1168 |7360 |
|4 | HASH JOIN | |56126 |154977 |
|5 | TABLE SCAN |p |141 |117 |
|6 | TABLE SCAN |op(idx_order_id_product_id)|498763 |23546 |

Outputs & filters:

0 - output([T_FUN_COUNT(1)(0x7f2f87aacce0)]), filter(nil), rowset=256
group(nil), agg_func([T_FUN_COUNT(1)(0x7f2f87aacce0)])
1 - output(nil), filter([o.id(0x7f2f87aac780) = ANY(subquery(1)(0x7f2f87a10490))(0x7f2f87a0fb40)]), rowset=256
exec_params_(nil), onetime_exprs_(nil), init_plan_idxs_([1]), batch_das=false
2 - output([o.id(0x7f2f87aac780)]), filter(nil), rowset=256
access([o.id(0x7f2f87aac780)])
3 - output([woke_c_order.id(0x7f2f87a0e890)]), filter([cast(woke_c_order.to_crm(0x7f2f87a0cb80), DECIMAL(-1, -1))(0x7f2f87a0d0c0) = cast(1, DECIMAL(1,
0))(0x7f2f87a0db30)(0x7f2f87a0c4d0)]), rowset=256
access([woke_c_order.id(0x7f2f87a0e890)], [woke_c_order.to_crm(0x7f2f87a0cb80)]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false],
range_key([woke_c_order.id(0x7f2f87a0e890)]), range(MIN ; MAX)always true
4 - output([op.order_id(0x7f2f87aac340)]), filter(nil), rowset=256
equal_conds([op.product_id(0x7f2f87aaa700) = p.id(0x7f2f87aaa9c0)(0x7f2f87aaa050)]), other_conds(nil)
5 - output([p.id(0x7f2f87aaa9c0)]), filter([p.nsy_code(0x7f2f87aabb10) IS NOT NULL(0x7f2f87aab460)]), rowset=256
access([p.id(0x7f2f87aaa9c0)], [p.nsy_code(0x7f2f87aabb10)]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false],
range_key([p.id(0x7f2f87aaa9c0)]), range(MIN ; MAX)always true
6 - output([op.product_id(0x7f2f87aaa700)], [op.order_id(0x7f2f87aac340)]), filter(nil), rowset=256
access([op.product_id(0x7f2f87aaa700)], [op.order_id(0x7f2f87aac340)]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([op.order_id(0x7f2f87aac340)], [op.product_id(0x7f2f87aaa700)], [op.id(0x7f2f87aadf40)]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
Used Hint:

/*+

  NO_REWRITE

*/
Qb name trace:

stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1
stmt_id:2, SEL$2 > SEL$2F8A4177
stmt_id:3, SEL$3
Outline Data:

/*+
BEGIN_OUTLINE_DATA
FULL(@“SEL$2F8A4177” “nsw_xiaoshouyi_xsy_zs”.“woke_c_order”@“SEL$2”)
LEADING(@“SEL$3” (“nsw_xiaoshouyi_xsy_zs”.“p”@“SEL$3” “nsw_xiaoshouyi_xsy_zs”.“op”@“SEL$3”))
USE_HASH(@“SEL$3” “nsw_xiaoshouyi_xsy_zs”.“op”@“SEL$3”)
FULL(@“SEL$3” “nsw_xiaoshouyi_xsy_zs”.“p”@“SEL$3”)
INDEX(@“SEL$3” “nsw_xiaoshouyi_xsy_zs”.“op”@“SEL$3” “idx_order_id_product_id”)
PROJECT_PRUNE(@“SEL$2”)
OPTIMIZER_FEATURES_ENABLE(‘4.0.0.0’)
END_OUTLINE_DATA
*/
Optimization Info:

woke_c_order:
table_rows:233460
physical_range_rows:233460
logical_range_rows:233460
index_back_rows:0
output_rows:1167
est_method:local_storage
optimization_method:cost_based
avaiable_index_name:[idx_to_crm, idx_tenant_id_to_crm, woke_c_order]
pruned_index_name:[ux_client_id, idx_tenant_id, idx_ower, idx_seller, idx_from_systerm, idx_order_type, idx_order_no, idx_creater, idx_caller, idx_promoter,
idx_deal_time, idx_order_type_new, idx_order_origin, idx_order_level, idx_idddzt, idx_sfhddd, idx_p_id, idx_is_to_crm, idx_create_time, idx_boss_tel, idx_boss_name,
idx_order_amount, idx_order_status]
table_id:501617:estimation info:(table_type:10, version:-1–1–1, logical_rc:233460, physical_rc:233460)]
stats version:1680271510106934
p:
table_rows:1542
physical_range_rows:1542
logical_range_rows:1542
index_back_rows:0
output_rows:141
est_method:local_storage
optimization_method:cost_based
avaiable_index_name:[woke_c_product]
pruned_index_name:[idx_name, idx_tenantId, idx_is_split, idx_is_delete, idx_nsy_code, idx_sort, idx_is_active]
table_id:501677:estimation info:(table_type:10, version:-1–1–1, logical_rc:1542, physical_rc:1542)]
stats version:1680271520263878
op:
table_rows:498763
physical_range_rows:498763
logical_range_rows:498763
index_back_rows:0
output_rows:498763
est_method:local_storage
optimization_method:cost_based
avaiable_index_name:[idx_product_id, idx_order_id_product_id, woke_c_order_product]
pruned_index_name:[idx_client_id, idx_tenant_id, idx_order_id]
table_id:501902:estimation info:(table_type:10, version:-1–1–1, logical_rc:498763, physical_rc:498763)]
stats version:1680271518928169
Plan Type:
LOCAL

#16

SELECT /+NO_REWRITE /
COUNT(1)
FROM
woke_c_order o WHERE o.id IN
(
SELECT order_id FROM woke_c_order_product op,
woke_c_product p WHERE op.product_id = p.id AND p.nsy_code IS NOT NULL
） and o.to_crm =1 看下执行计划和时间呢 用no_rewrite 现在的执行计划达到预期了 时间没有到达预期

#17

用了no_rewrite后，执行时间变得耗时太久了，而且执行计划也没有在父查询不加to_crm这个查询条件好，用了no_rewrite后执行计划没有走MERGE SEMI JOIN还是不行

企业微信截图_20230418090419
企业微信截图_202304180904191614×815 45.7 KB

企业微信截图_20230418090443
企业微信截图_202304180904431901×779 31.5 KB

企业微信截图_20230418090500
企业微信截图_202304180905001907×1033 105 KB

#18

在sql语句上不好优化 这边建议可以给and条件后的to_crm字段加上一个索引 还有也可以在查询语句上加上并行

#19

to_crm已经有索引了，你说的并行是对表进行分区？

#20

非分区表也可以进行并行，可以尝试下文档里的做法

image
image2252×304 84.3 KB

https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001697455