Starrocks查询性能优化问题

💬 StarRocks 用户问答 功能使用相关
#1

【详述】单表才4千万数据 查询比较慢
【背景】正常建表
【业务影响】
【StarRocks版本】例如：1.18.2
【集群规模】例如：3fe（1 follower+2observer）+5be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【附件】

  • fe.log/beINFO/相应截图

  • 慢查询：

    • Profile信息
      PLAN FRAGMENT 0(F01)
      Output Exprs:1: ID | 2: CREATE_TIME | 3: BDLX | 4: BDFS | 5: BDDF | 6: BDPM | 7: RWID | 8: BDBIZID | 9: BDYYID | 10: BDWJID | 11: BDWJMC | 12: BDWJJCBH | 13: BDWJWZBH | 14: BDWJCCDZ | 15: BDWJCCLX | 16: BDMZSWID | 17: BDMZBIZID | 18: BDMZYBBH | 19: BDMZWJID | 20: BDMZWJMC | 21: BDMZWJJCBH | 22: BDMZWJWZBH | 23: BDMZWJCCDZ | 24: BDMZWJCCLX | 25: RYJCXXCJBH | 26: XM | 27: CYZJMC | 28: ZJHM | 29: GMSFHM | 30: XB | 31: HJD_DZMC | 32: XZD_DZMC | 33: CSD_DZMC | 34: CQJZD_DZMC | 35: BCJRYLBMC | 36: CSRQ | 37: CJSJ | 38: ZCSJ | 39: SBSJ | 40: AJBH | 41: JJBH | 42: XCKYBH | 43: AJMC | 44: AJXZ | 45: AJXZMC | 46: AJZT | 47: AJLBMC | 48: DXZPAJLBMC | 49: AJFABMC | 50: AYMC | 51: FAYMC | 52: FASJ | 53: FADZ | 54: FADZXZQHMC | 55: XYRZHLX | 56: XYR_ACCOUNT_ID | 57: XYR_ACCOUNT | 58: XYRNC | 59: GJC | 60: CJDWBM | 61: CJDWMC | 62: BDYQMC | 63: BDYQBB | 64: RDRYXM | 65: RDDWDM | 66: RDDWMC | 67: GXJG | 68: RDSJ | 69: BDSJ | 70: SJLY | 71: XTLY | 72: DWBM | 73: DWMC | 74: CREATE_XM | 75: CREATE_ID | 76: UPDATE_TIME | 77: SFSC
      Input Partition: UNPARTITIONED
      RESULT SINK

    1:EXCHANGE
    cardinality: 24688027

PLAN FRAGMENT 1(F00)

Input Partition: RANDOM
OutPut Partition: UNPARTITIONED
OutPut Exchange Id: 01

0:OlapScanNode
table: swk_comp_result, rollup: swk_comp_result
preAggregation: on
Predicates: [7: RWID, VARCHAR, true] = ‘2cabc2d3-19ee-4227-a0f7-3bbacedbb9a2’, [9: BDYYID, VARCHAR, true] = ‘4b71de35-0758-48aa-a617-8dcbece5e4b9’, [4: BDFS, VARCHAR, false] = ‘SAMPLE’, [62: BDYQMC, VARCHAR, true] = ‘IFLYTEK’, [77: SFSC, TINYINT, true] = 0
partitionsRatio=10/37, tabletsRatio=500/500
tabletList=82124,82127,82130,82133,82136,82139,82142,82145,82148,82151 …
actualRows=49375798, avgRowSize=119.0
cardinality: 24688027
column statistics:
* ID–>[-Infinity, Infinity, 0.0, 36.0, 4.92E7] ESTIMATE
* CREATE_TIME–>[1.646064E9, 1.6725024E9, 0.0, 8.0, 24042.702702702703] ESTIMATE
* BDLX–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDFS–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDDF–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDPM–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* RWID–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDBIZID–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDYYID–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDWJID–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDWJMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDWJJCBH–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDWJWZBH–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDWJCCDZ–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDWJCCLX–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDMZSWID–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDMZBIZID–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDMZYBBH–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDMZWJID–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDMZWJMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDMZWJJCBH–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDMZWJWZBH–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDMZWJCCDZ–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDMZWJCCLX–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* RYJCXXCJBH–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* XM–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* CYZJMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* ZJHM–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* GMSFHM–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* XB–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* HJD_DZMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* XZD_DZMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* CSD_DZMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* CQJZD_DZMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BCJRYLBMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* CSRQ–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* CJSJ–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* ZCSJ–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* SBSJ–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* AJBH–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* JJBH–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* XCKYBH–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* AJMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* AJXZ–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* AJXZMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* AJZT–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* AJLBMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* DXZPAJLBMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* AJFABMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* AYMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* FAYMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* FASJ–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* FADZ–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* FADZXZQHMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* XYRZHLX–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* XYR_ACCOUNT_ID–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* XYR_ACCOUNT–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* XYRNC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* GJC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* CJDWBM–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* CJDWMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDYQMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDYQBB–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* RDRYXM–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* RDDWDM–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* RDDWMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* GXJG–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* RDSJ–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* BDSJ–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* SJLY–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* XTLY–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* DWBM–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* DWMC–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* CREATE_XM–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* CREATE_ID–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* UPDATE_TIME–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
* SFSC–>[0.0, 0.0, 0.0, 1.0, 1.0] UNKNOWN

  • 并行度：show variables like ‘%parallel_fragment_exec_instance_num%’;
    16

  • pipeline是否开启：show variables like ‘%pipeline%’;

  • be节点cpu和内存使用率截图

  • 查询报错：
    建表语句
    CREATE TABLE IF NOT EXISTS swk_comp_result_dynamic_test (
    CREATE_TIME datetime NOT NULL COMMENT ‘创建时间’,
    ID varchar(36) NOT NULL COMMENT ‘唯一编码’,
    BDLX varchar(32) DEFAULT NULL COMMENT ‘比对类型 与比对任务类型保持一致’,
    BDFS varchar(32) NOT NULL COMMENT ‘比对方式 01 人到案比对，02 案到人比对’,
    BDDF decimal(32,10) NOT NULL COMMENT ‘比对得分’,
    BDPM int(11) NOT NULL COMMENT ‘比对排名’,
    RWID varchar(64) DEFAULT NULL COMMENT ‘比对任务id 比对任务唯一编码’,
    BDBIZID varchar(64) DEFAULT NULL COMMENT ‘业务编码 与比对任务bizid保持一致’,
    BDYYID varchar(64) DEFAULT NULL COMMENT ‘比对语音唯一编码 关联comp_voice表id’,
    BDWJID varchar(64) DEFAULT NULL COMMENT ‘比对文件编码 比对文件唯一编码’,
    BDWJMC varchar(256) DEFAULT NULL COMMENT ‘比对文件名称’,
    BDWJJCBH varchar(128) DEFAULT NULL COMMENT ‘比对文件检材编号’,
    BDWJWZBH varchar(128) DEFAULT NULL COMMENT ‘比对文件物证编号’,
    BDWJCCDZ varchar(256) DEFAULT NULL COMMENT ‘比对文件存储地址 检材语音地址：JCYYDZ’,
    BDWJCCLX varchar(32) DEFAULT NULL COMMENT ‘比对文件类型’,
    BDMZSWID varchar(64) DEFAULT NULL COMMENT ‘比对命中语音唯一编码 reg_voice表唯一编码’,
    BDMZBIZID varchar(64) DEFAULT NULL COMMENT ‘比对命中语音业务唯一编码 reg_voice业务biz关联编码’,
    BDMZYBBH varchar(64) DEFAULT NULL COMMENT ‘比对命中声纹编码 部标：YBBH，样本编号’,
    BDMZWJID varchar(64) DEFAULT NULL COMMENT ‘比对命中文件编码 关联注册语音wjid’,
    BDMZWJMC varchar(256) DEFAULT NULL COMMENT ‘比对命中文件名称 关联注册语音名’,
    BDMZWJJCBH varchar(64) DEFAULT NULL COMMENT ‘比对命中文件检材编号’,
    BDMZWJWZBH varchar(64) DEFAULT NULL COMMENT ‘比对命中文件物证编号’,
    BDMZWJCCDZ varchar(256) DEFAULT NULL COMMENT ‘比对命中文件存储地址 部标：YBYYDZ，样本语音存储地址；非空’,
    BDMZWJCCLX varchar(32) DEFAULT NULL COMMENT ‘比对命中文件存储类型’,
    RYJCXXCJBH varchar(64) DEFAULT NULL COMMENT ‘人员基础信息采集编号’,
    XM varchar(256) DEFAULT NULL COMMENT ‘姓名’,
    CYZJMC varchar(96) DEFAULT NULL COMMENT ‘常用证件名称’,
    ZJHM varchar(32) DEFAULT NULL COMMENT ‘证件号码’,
    GMSFHM varchar(32) DEFAULT NULL COMMENT ‘公民身份号码 如为空值，表示非中国国 籍’,
    XB varchar(32) DEFAULT NULL COMMENT ‘性别’,
    HJD_DZMC varchar(256) DEFAULT NULL COMMENT ‘户籍地址名称’,
    XZD_DZMC varchar(256) DEFAULT NULL COMMENT ‘现住地详址’,
    CSD_DZMC varchar(256) DEFAULT NULL COMMENT ‘出生地详址’,
    CQJZD_DZMC varchar(256) DEFAULT NULL COMMENT ‘长期居住地’,
    BCJRYLBMC varchar(1024) DEFAULT NULL COMMENT ‘被采集人员类别代码 用逗号分隔’,
    CSRQ datetime DEFAULT NULL COMMENT ‘出生日期’,
    CJSJ datetime DEFAULT NULL COMMENT ‘采集时间 终端完成人员样本信息语 音语音时记录的时间 YYYYMMDDhhmmss’,
    ZCSJ datetime DEFAULT NULL COMMENT ‘注册时间 语音注册入库时间，格式： YYYYMMDDhhmmss’,
    SBSJ datetime DEFAULT NULL COMMENT ‘上报时间 终端采集完成，数据上报声纹库时记录的时间YYYYMMDDhhmmss’,
    AJBH varchar(64) DEFAULT NULL COMMENT ‘案件编号’,
    JJBH varchar(64) DEFAULT NULL COMMENT ‘接警编号’,
    XCKYBH varchar(64) DEFAULT NULL COMMENT ‘现场勘验编号’,
    AJMC varchar(512) DEFAULT NULL COMMENT ‘案件名称’,
    AJXZ varchar(32) DEFAULT NULL COMMENT ‘案件性质’,
    AJXZMC varchar(256) DEFAULT NULL COMMENT ‘案件性质名称’,
    AJZT varchar(256) DEFAULT NULL COMMENT ‘案件状态’,
    AJLBMC varchar(256) DEFAULT NULL COMMENT ‘案件类别名称’,
    DXZPAJLBMC varchar(256) DEFAULT NULL COMMENT ‘电信诈骗案件类别名称’,
    AJFABMC varchar(256) DEFAULT NULL COMMENT ‘案件副案别名称’,
    AYMC varchar(256) DEFAULT NULL COMMENT ‘案由名称’,
    FAYMC varchar(256) DEFAULT NULL COMMENT ‘副案由名称’,
    FASJ datetime DEFAULT NULL COMMENT ‘发案时间’,
    FADZ varchar(256) DEFAULT NULL COMMENT ‘发案地址’,
    FADZXZQHMC varchar(256) DEFAULT NULL COMMENT ‘发案地址行政区划名称’,
    XYRZHLX varchar(32) DEFAULT NULL COMMENT ‘嫌疑人账号类型 检材类型代码，现勘推送类型如不在代码表中，归类于其他，并记录现勘推送信息’,
    XYR_ACCOUNT_ID varchar(64) DEFAULT NULL COMMENT ‘嫌疑人用户ID’,
    XYR_ACCOUNT varchar(256) DEFAULT NULL COMMENT ‘嫌疑人账号’,
    XYRNC varchar(256) DEFAULT NULL COMMENT ‘嫌疑人昵称’,
    GJC varchar(1024) DEFAULT NULL COMMENT ‘关键词’,
    CJDWBM varchar(32) DEFAULT NULL COMMENT ‘采集单位编码’,
    CJDWMC varchar(256) DEFAULT NULL COMMENT ‘采集单位名称’,
    BDYQMC varchar(256) DEFAULT NULL COMMENT ‘比对引擎名称’,
    BDYQBB varchar(256) DEFAULT NULL COMMENT ‘比对引擎版本’,
    RDRYXM varchar(256) DEFAULT NULL COMMENT ‘认定人员姓名’,
    RDDWDM varchar(32) DEFAULT NULL COMMENT ‘认定单位代码’,
    RDDWMC varchar(256) DEFAULT NULL COMMENT ‘认定单位名称’,
    GXJG varchar(32) DEFAULT NULL COMMENT ‘关系结果（标记结果） 01 认定 02 存疑 03 否定’,
    RDSJ datetime DEFAULT NULL COMMENT ‘认定时间’,
    BDSJ datetime DEFAULT NULL COMMENT ‘比对时间’,
    SJLY varchar(64) DEFAULT NULL COMMENT ‘数据来源’,
    XTLY varchar(32) DEFAULT NULL COMMENT ‘系统来源’,
    DWBM varchar(128) NOT NULL COMMENT ‘单位编码 提交比对任务的用户组织机构’,
    DWMC varchar(256) NOT NULL COMMENT ‘单位名称’,
    CREATE_XM varchar(256) DEFAULT NULL COMMENT ‘创建人姓名’,
    CREATE_ID varchar(64) DEFAULT NULL COMMENT ‘创建人标识’,
    UPDATE_TIME datetime NOT NULL COMMENT ‘更新时间’,
    SFSC tinyint(1) NOT NULL COMMENT ‘是否删除’
    )
    PRIMARY KEY (CREATE_TIME,ID)
    PARTITION BY RANGE (CREATE_TIME) (
    START (“2020-01-01”) END (“2022-10-01”) EVERY (INTERVAL 1 MONTH),
    START (“2022-10-01”) END (“2023-06-30”) EVERY (INTERVAL 3 DAY )
    )
    DISTRIBUTED BY HASH(ID) BUCKETS 60
    PROPERTIES(“replication_num” = “2”);

#2

提供一下profile

#3

5226c8dca299f976e08ed9906366957
5226c8dca299f976e08ed99063669574096×3072 2.68 MB

京ICP备2022026421号-1