一、问题概述
SELECTDB 2.0.2 私有化部署，带倒排索引的JOIN查询慢，t_req_detail 是大表，约1G数据。无压力情况下4分钟出不了数据。
二、具体数据
主键为unique key (“trace_id”，“rk”，“component”，“req_time”)
前缀索引为"trace_id"
PARTITION BY RANGE(req_time) ()
DISTRIBUTED BY HASH(component), HASH(trace_id)
PROPERTIES
(
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
mobile 、req_content都带了倒排索引
SQL脚本和数据结构如下：
select counter.component, counter.req_content, req.mobile, req.http_path, req.component as reqcomponent
from t_req_detail as counter join t_req_detail as req on 1=1
where counter.component = '柜台调度' and req.component not in ('柜台调度')
and counter.req_content match_all req.trace_id
and req.mobile match_all '13349000000'
and counter.req_content like '%GSUN%'
limit 10
数据结构如下：
{
"select * from t_req_detail where mobile match_all '13349000000'": [
{
"trace_id" : "0057841649",
"rk" : 864691128455220939,
"component" : "柜台调度",
"req_time" : "2023-11-22 14:06:18",
"cluster_name" : "东莞",
"host_name" : "TEST",
"http_path" : "420519",
"ms_name" : "柜台调度",
"ms_method" : "420519",
"pkg" : "undefined",
"mobile" : "13349000000"
},
{
"trace_id" : "0057842029",
"rk" : 5188146770730899183,
"component" : "柜台调度",
"req_time" : "2023-11-22 14:11:20",
"cluster_name" : "东莞",
"host_name" : "TEST",
"http_path" : "420503",
"ms_name" : "柜台调度",
"ms_method" : "420503",
"pkg" : "undefined",
"mobile" : "13349000000"
},
{
"trace_id" : "0057842039",
"rk" : 7782220156096304889,
"component" : "柜台调度",
"req_time" : "2023-11-22 14:11:29",
"cluster_name" : "东莞",
"host_name" : "TEST",
"http_path" : "420531",
"ms_name" : "柜台调度",
"ms_method" : "420531",
"pkg" : "undefined",
"mobile" : "13349000000"
},
{
"trace_id" : "0057842054",
"rk" : 87816,
"component" : "柜台调度",
"req_time" : "2023-11-22 14:11:37",
"cluster_name" : "东莞",
"host_name" : "TEST",
"http_path" : "420503",
"ms_name" : "柜台调度",
"ms_method" : "420503",
"pkg" : "undefined",
"mobile" : "13349000000"
}
]}
三、相关疑问
如果去掉req.mobile match_all '13349000000’条件，结果根本出不了，dbeaver客户端一直在转圈。