表结构
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。