增加多一个条件导致SQL就卡住

OceanBase
#1

【 使用环境 】测试环境
【 OB or 其他组件 】
【 使用版本 】4.3.3
【问题描述】

9c47188e2915a536a0aec7444c9b61d
9c47188e2915a536a0aec7444c9b61d797×717 29.9 KB

执行计划

1729986093066
17299860930661178×615 18.7 KB

对应表 pos_salesdetail_70
PRIMARY KEY (id),
KEY idx_salesdetail_cb1 (StoreId, SalesDate, ItemCode) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesdetail_cb2 (StoreId, SalesId, ItemCode) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesdetail_cb3 (VendorId, StoreId, StoreSysCode, relatedType, SalesDate, ItemCode) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesdetail_cb6 (StoreId, StoreSysCode, SalesDate, ItemCode, ItemName, relatedType, IsDelete) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesdetail_salesId (SalesId) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesdetail_salesmonth (SalesMonth) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesdetail_salesyear (SalesYear) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesdetail_storeIdsalesDate (StoreId, SalesDate) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesdetail_x2 (StoreId, StoreSysCode, SalesDate, ItemId, SalesNo) BLOCK_SIZE 16384 LOCAL,
KEY idx_stocker_storeid_selfnum (StoreId, SelfNum) 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 = ‘销售明细 当前表只保留1个月’;

若去掉storeSyscode条件就很快，是什么原因呢

1729986231524
1729986231524616×517 15.6 KB

1 个赞
#3

主表 pos_salesh_70 上索引如何？

如果没有下面这个索引，试试看：

create index pos_salesh_70_ind1 on pos_salesh_70( storeid, storesyscode, salestime);

去掉你那个 force index。

看解析执行计划

explain extended_noaddr 你的sql ;

1 个赞
#4

pos_salesh_70有索引
PRIMARY KEY (Id),
KEY idx_posSalesh_storeIdReturnSalesId (StoreId, ReturnSalesId) BLOCK_SIZE 16384 LOCAL,
KEY idx_pos_salesh_70_custid (CustId) BLOCK_SIZE 16384 LOCAL,
KEY idx_pos_salesh_70_storeid_salestype (SalesType) BLOCK_SIZE 16384 LOCAL,
KEY idx_pos_salesh_70_storesyscode (StoreSysCode) BLOCK_SIZE 16384 LOCAL,
KEY idx_pos_salesh_70_sysupdatetime (SysUpdateTime) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesh_cb2 (StoreId, StoreSysCode, SalesTime, SalesNo) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesh_salesdate (SalesDate) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesh_salesmonth (SalesMonth) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesh_salesno (SalesNo) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesh_salestime (SalesTime) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesh_salesyear (SalesYear) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesh_storeidsalesdate (StoreId, SalesDate) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesh_storeid_salesdate (StoreId, SalesDate) BLOCK_SIZE 16384 LOCAL,
KEY idx_salesh_storeid_salestime (StoreId, SalesTime) 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 = ‘销售表’;

1 个赞
#5

image
image2560×1600 248 KB

1 个赞
#6

你还是先换个工具吧 。比如说 dbeaver。
或者就在 mysql 下发这个 explain 命令结果。

1 个赞
#7

先把2种情况的sql explain extended一下发下结果

#8

obclient [apos_sales_0]> explain SELECT
→ count(1)
→ FROM
→ pos_salesdetail_70 t,
→ pos_salesh_70 s
→ WHERE
→ t.salesId = s.id
→ AND s.storeId = ‘2c8dba1a3d6868ac8df7645a90ff1d4d’
→ AND s.storeSysCode = ‘96870’
→ AND s.SalesTime BETWEEN ‘2023-03-01 00:00:00’
→ AND ‘2024-04-31 23:59:59’;
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ========================================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------------- |
| |0 |SCALAR GROUP BY | |1 |355810 | |
| |1 |└─MERGE JOIN | |1661531 |325696 | |
| |2 | ├─TABLE FULL SCAN|s |361543 |59779 | |
| |3 | └─TABLE FULL SCAN|t(idx_salesdetail_salesId)|3425356 |57413 | |
| ========================================================================= |

#9

obclient [apos_sales_0]> explain extended SELECT
→ count(1)
→ FROM
→ pos_salesdetail_70 t,
→ pos_salesh_70 s
→ WHERE
→ t.storeId = ‘2c8dba1a3d6868ac8df7645a90ff1d4d’
→ AND t.storeSysCode = ‘96870’
→ AND t.salesId = s.id
→ AND s.storeId = ‘2c8dba1a3d6868ac8df7645a90ff1d4d’
→ AND s.storeSysCode = ‘96870’
→ AND s.SalesTime BETWEEN ‘2023-03-01 00:00:00’
→ AND ‘2024-04-31 23:59:59’;
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------------------------- |
| |0 |SCALAR GROUP BY | |1 |1652891 | |
| |1 |└─HASH JOIN | |1661462 |1622778 | |
| |2 | ├─TABLE RANGE SCAN|s(idx_salesh_cb2)|361543 |18207 | |
| |3 | └─TABLE FULL SCAN |t |3425213 |392241 | |
| ================================================================= |

Outputs & filters:
0 - output([T_FUN_COUNT(*)(0x7f69ebc2d720)]), filter(nil), rowset=256
group(nil), agg_func([T_FUN_COUNT(*)(0x7f69ebc2d720)])
1 - output(nil), filter(nil), rowset=256
equal_conds([t.SalesId(0x7f69ebc26410) = s.Id(0x7f69ebc26850)(0x7f69ebc25b50)]), other_conds(nil)
2 - output([s.Id(0x7f69ebc26850)]), filter(nil), rowset=256
access([s.Id(0x7f69ebc26850)]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([s.StoreId(0x7f69ebc280b0)], [s.StoreSysCode(0x7f69ebc29910)], [s.SalesTime(0x7f69ebc2cbe0)], [s.SalesNo(0x7f69ebd08a90)], [s.Id(0x7f69ebc26850)]),
range(2c8dba1a3d6868ac8df7645a90ff1d4d,96870,2023-03-01 00:00:00,MIN,MIN ; 2c8dba1a3d6868ac8df7645a90ff1d4d,96870,2024-04-31 23:59:59,MAX,MAX),
range_cond([s.StoreId(0x7f69ebc280b0) = ‘2c8dba1a3d6868ac8df7645a90ff1d4d’(0x7f69ebc277f0)], [s.StoreSysCode(0x7f69ebc29910) = ‘96870’(0x7f69ebc29050)],
[s.SalesTime(0x7f69ebc2cbe0) >= ‘2023-03-01 00:00:00’(0x7f69ebc2b1a0)], [s.SalesTime(0x7f69ebc2cbe0) <= ‘2024-04-31 23:59:59’(0x7f69ebc2ba20)])
3 - output([t.SalesId(0x7f69ebc26410)]), filter([t.StoreId(0x7f69ebc23380) = ‘2c8dba1a3d6868ac8df7645a90ff1d4d’(0x7f69ebc22ac0)], [t.StoreSysCode(0x7f69ebc24be0)
= ‘96870’(0x7f69ebc24320)]), rowset=256
access([t.StoreId(0x7f69ebc23380)], [t.StoreSysCode(0x7f69ebc24be0)], [t.SalesId(0x7f69ebc26410)]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false,false],
range_key([t.id(0x7f69ebc2ebf0)]), range(MIN ; MAX)always true
Used Hint:
-------------------------------------
/*+
#10 
                                                                                                                                                                                                                                                                                                                                                                                                                               |

| */ |

Qb name trace:
stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1 > SEL$EA493D33
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
LEADING(@“SEL$EA493D33” (“s”@“SEL$1” “t”@“SEL$1”))
USE_HASH(@“SEL$EA493D33” “t”@“SEL$1”)
INDEX(@“SEL$EA493D33” “s”@“SEL$1” “idx_salesh_cb2”)
FULL(@“SEL$EA493D33” “t”@“SEL$1”)
SIMPLIFY_GROUP_BY(@“SEL$1”)
OPTIMIZER_FEATURES_ENABLE(‘4.3.3.0’)
END_OUTLINE_DATA
*/
Optimization Info:
-------------------------------------
s:
table_rows:746995
physical_range_rows:361543
logical_range_rows:361543
index_back_rows:0
output_rows:361543
table_dop:1
dop_method:Table DOP
avaiable_index_name:[idx_posSalesh_storeIdReturnSalesId, idx_pos_salesh_70_custid, idx_pos_salesh_70_storeid_salestype, idx_pos_salesh_70_storesyscode, idx_pos_salesh_70_sysupdatetime, idx_salesh_cb2, idx_salesh_salesdate, idx_salesh_salesmonth, idx_salesh_salesno, idx_salesh_salestime, idx_salesh_salesyear, idx_salesh_storeidsalesdate, idx_salesh_storeid_salesdate, idx_salesh_storeid_salestime, pos_salesh_70]
pruned_index_name:[idx_posSalesh_storeIdReturnSalesId, idx_pos_salesh_70_custid, idx_pos_salesh_70_storeid_salestype, idx_pos_salesh_70_sysupdatetime, idx_salesh_salesdate, idx_salesh_salesmonth, idx_salesh_salesno, idx_salesh_salestime, idx_salesh_salesyear, idx_salesh_storeidsalesdate, idx_salesh_storeid_salesdate, idx_salesh_storeid_salestime]
stats info:[version=1729980597376403, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[OPTIMIZER STATISTICS, STORAGE]
t:
table_rows:3425356
physical_range_rows:3425356
logical_range_rows:3425356
index_back_rows:0
output_rows:3425147
table_dop:1
dop_method:Table DOP
avaiable_index_name:[idx_salesdetail_cb1, idx_salesdetail_cb2, idx_salesdetail_cb3, idx_salesdetail_cb6, idx_salesdetail_salesId, idx_salesdetail_salesmonth, idx_salesdetail_salesyear, idx_salesdetail_storeIdsalesDate, idx_salesdetail_x2, idx_stocker_storeid_selfnum, pos_salesdetail_70]
pruned_index_name:[idx_salesdetail_cb1, idx_salesdetail_cb3, idx_salesdetail_salesId, idx_salesdetail_salesmonth, idx_salesdetail_salesyear, idx_salesdetail_storeIdsalesDate, idx_stocker_storeid_selfnum]
stats info:[version=1729981941745382, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[OPTIMIZER STATISTICS, STORAGE]
Plan Type:
LOCAL
Note:
Degree of Parallelisim is 1 because of table property

±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
79 rows in set (0.110 sec)

#21

统计信息重新收集下，防止过期的统计信息，影响优化器成本选择
然后，快的，在黑屏命令行看下详细的执行计划，慢的，也是同样，看看是那里不对了，也可以发出来。

#22

1、这两个的执行计划 发一下 尽量保存在文本里
explain exentded SELECT
count(1)
FROM
pos_salesdetail_70 t,
pos_salesh_70 s
WHERE
t.salesId = s.id
AND s.storeId = ‘2c8dba1a3d6868ac8df7645a90ff1d4d’
AND s.storeSysCode = ‘96870’
AND s.SalesTime BETWEEN ‘2023-03-01 00:00:00’
AND ‘2024-04-31 23:59:59’;

explain exentded SELECT
count(1)
FROM
pos_salesdetail_70 t,
pos_salesh_70 s
WHERE
t.salesId = s.id
AND s.storeId = ‘2c8dba1a3d6868ac8df7645a90ff1d4d’
AND s.storeSysCode = ‘96870’ and t.storeSysCode=‘96870’
AND s.SalesTime BETWEEN ‘2023-03-01 00:00:00’
AND ‘2024-04-31 23:59:59’;
2、obdiag收集一下
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-1000000001491226

#23

sql1.txt (4.9 KB)
sql2.txt (4.7 KB)
帮忙分析一下执行计划

TABLE FULL SCAN代码全表打描吗

#24

TABLE FULL SCAN是扫描全表

#25

explain exentded SELECT
count(1)
FROM
pos_salesdetail_70 t,
pos_salesh_70 s
WHERE
t.salesId = s.id
AND s.storeId = ‘2c8dba1a3d6868ac8df7645a90ff1d4d’
AND s.storeSysCode = ‘96870’ and t.storeSysCode=‘96870’
AND s.SalesTime BETWEEN ‘2023-03-01 00:00:00’
AND ‘2024-04-31 23:59:59’;
这个执行的计划 发给我 尽量保存在文本里

#26

sql3.txt (5.0 KB)

#27

1、查询一下这个语句 看看多久能执行完
SELECT
/*+ LEADING(@“SEL$1” (“s”@“SEL$1” “t”@“SEL$1”)) USE_HASH(@“SEL$1” “t”@“SEL$1”) INDEX(@“SEL$1” “s”@“SEL$1” “idx_salesh_cb2”) /
count(1)
FROM
pos_salesdetail_70 t,
pos_salesh_70 s
WHERE
t.salesId = s.id
AND s.storeId = ‘2c8dba1a3d6868ac8df7645a90ff1d4d’
AND s.storeSysCode = ‘96870’
AND s.SalesTime BETWEEN ‘2023-03-01 00:00:00’
AND ‘2024-04-31 23:59:59’;
2、在查询一下这个执行计划 保存在文本里
explain extended SELECT
/+ LEADING(@“SEL$1” (“s”@“SEL$1” “t”@“SEL$1”)) USE_HASH(@“SEL$1” “t”@“SEL$1”) INDEX(@“SEL$1” “s”@“SEL$1” “idx_salesh_cb2”) */
count(1)
FROM
pos_salesdetail_70 t,
pos_salesh_70 s
WHERE
t.salesId = s.id
AND s.storeId = ‘2c8dba1a3d6868ac8df7645a90ff1d4d’
AND s.storeSysCode = ‘96870’
AND s.SalesTime BETWEEN ‘2023-03-01 00:00:00’
AND ‘2024-04-31 23:59:59’;