3.0.3 存算分离 通过profile分析索引加速效果,RowsInvertedIndexFiltered 一直都是0

Viewed 25

表结构

CREATE TABLE `dwd_daq_2b_mall_sku_collector` (
  `dt` datetime NOT NULL,
  `city_id` int NULL COMMENT '城市id',
  `sku_id` int NULL COMMENT '事件id',
  `event_id` int NULL COMMENT '事件id',
  `company_id` int NULL COMMENT 'uid',
  `c_t` bigint NULL COMMENT 'ts',
  `ts_offset` int NULL COMMENT 'ts_offset',
  `tid` varchar(72) NULL COMMENT 'tid',
  `seq_id` varchar(72) NULL COMMENT '连续编号',
  `session_id` varchar(72) NULL COMMENT 'session_id',
  `event` int NULL COMMENT '事件类型',
  `bu_id` int NULL COMMENT 'bu_id',
  `app_id` int NULL COMMENT '业务方',
  `device_id` text NULL COMMENT 'device_id',
  `passport_id` bigint NULL COMMENT 'passport_id',
  `open_id` text NULL COMMENT 'open_id',
  `type` int NULL COMMENT '事件类型',
  `spm` text NULL COMMENT 'spm',
  `page_id` int NULL COMMENT '页面id',
  `sdk_ver` text NULL COMMENT 'sdk版本',
  `app_system` text NULL COMMENT '应用系统类型',
  `app_version` text NULL COMMENT '应用版本',
  `app_runtime` text NULL COMMENT '应用执行环境',
  `sn` text NULL COMMENT '手机sn',
  `ip` bigint NULL COMMENT 'ip',
  `url` text NULL COMMENT '页面url',
  `referrer` text NULL COMMENT 'referrer',
  `user_agent` text NULL COMMENT 'user_agent',
  `device_type` text NULL COMMENT '设备类型',
  `web_browser` text NULL COMMENT '浏览器信息',
  `os` text NULL COMMENT '操作系统名称',
  `os_version` text NULL COMMENT '操作系统版本',
  `net` text NULL COMMENT '网络类型',
  `mno` text NULL COMMENT '网络运营商',
  `area_id` int NULL COMMENT '售卖区id',
  `latitude` text NULL COMMENT '纬度',
  `longitude` text NULL COMMENT '经度',
  `params` text NULL COMMENT '埋点参数',
  `extra` text NULL COMMENT '拓展参数',
  `refer_id` text NULL COMMENT '上游spm',
  `warehouse_id` int NULL COMMENT '仓库id',
  `category_group_id` int NULL COMMENT '采销组id',
  `group_path` varchar(200) NULL COMMENT '采销组路径逗号隔开',
  INDEX idx_category_group_id (`category_group_id`) USING INVERTED,
  INDEX idx_c_t (`c_t`) USING INVERTED,
  INDEX idx_sku_id (`sku_id`) USING INVERTED,
  INDEX idx_city_id (`city_id`) USING INVERTED,
  INDEX idx_group_path (`group_path`) USING INVERTED
) ENGINE = OLAP UNIQUE KEY(
  `dt`,
  `city_id`,
  `sku_id`,
  `event_id`,
  `company_id`,
  `c_t`,
  `ts_offset`,
  `tid`,
  `seq_id`,
  `session_id`
) AUTO PARTITION BY RANGE (date_trunc(`dt`, 'day')) ()
    
    DISTRIBUTED BY HASH(`c_t`) BUCKETS 10 PROPERTIES ( "file_cache_ttl_seconds" = "0", "is_being_synced" = "false", "storage_medium" = "hdd", "storage_format" = "V2", "inverted_index_storage_format" = "V2", "enable_unique_key_merge_on_write" = "true", "light_schema_change" = "true", "storage_vault_name" = "s3_vault", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false", "group_commit_interval_ms" = "10000", "group_commit_data_bytes" = "134217728", "enable_mow_light_delete" = "false" );
select count(1) from dwd.dwd_daq_2b_mall_sku_collector where dt ='2025-02-11'

三千万+的数据量

查看 下面sql 有city_id条件的的 profile ,结果如图:

select * from dwd.dwd_daq_2b_mall_sku_collector where dt ='2025-02-11' and city_id = 1  limit 1000

其中 RowsInvertedIndexFiltered 一直是0 。city_id 是倒排索引,根据官方描述,https://doris.apache.org/zh-CN/docs/3.0/table-design/index/inverted-index
RowsInvertedIndexFiltered 理论上不会是0。

1 Answers

image.png
这个前缀索引已经扫过了,然后倒排没扫到,所以过滤的就是0