sql不走索引

💬 StarRocks 用户问答
#1

【详述】
STATIC_BOM_ALL表的“AUTO_SN” 加了bitmap索引，并且设置了强制走索引：bitmap_max_filter_ratio=1000，重启了be。
但是通过analyze命令分析以下sql，没有走索引。
SELECT
*
FROM
table1
WHERE
PROVINCE= ‘浙江省’
AND MATERIAL_CODE IN
(
SELECT
MODULE_CODE
FROM
STATIC_BOM_ALL
WHERE
AUTO_SN IN ( SELECT A.SERNR FROM table3 A WHERE A.PROVINCE = ‘浙江省’)
);

【背景】
【业务影响】
【是否存算分离】否
【StarRocks版本】例如：3.1.11
【集群规模】例如：3fe（1 follower+2observer）+3be（fe与be混部）
【机器信息】
【联系方式】
【附件】
表结构：
STATIC_BOM_ALL的表结构：

CREATE TABLE STATIC_BOM_ALL(
AUTO_SN varchar(18) NULL COMMENT “”,
ID decimal(16, 0) NULL COMMENT “”,
SLEVEL varchar(10) NULL COMMENT “”,
PARENT_MODULE_NAME varchar(120) NULL COMMENT “”,
PARENT_MODULE_CODE varchar(30) NULL COMMENT “”,
DUMPS varchar(1) NULL COMMENT “”,
MODULE_NAME varchar(120) NULL COMMENT “”,
MODULE_CODE varchar(30) NULL COMMENT “”,
AMOUNT decimal(15, 5) NULL COMMENT “”,
UNIT varchar(30) NULL COMMENT “”,
UNIT_CODE varchar(30) NULL COMMENT “”,
MODULE_STYLE varchar(200) NULL COMMENT “”,
MODULE_TYPE varchar(20) NULL COMMENT “”,
ETL_DT datetime NULL COMMENT “”,
BUY_DATE datetime NULL COMMENT “”,
PROVINCE varchar(200) NULL COMMENT “”,
CITY varchar(200) NULL COMMENT “”
) ENGINE=OLAP
DUPLICATE KEY(AUTO_SN)
DISTRIBUTED BY HASH(ID)；

olap_scan节点的profile：
OLAP_SCAN (id=0) |
| │ Estimates: [row: 2632502397, cpu: 2632502397.00, memory: 0.00, network: 0.00, cost: 1316251198.50] |
| │ TotalTime: 39s107ms (70.16%) [CPUTime: 3s314ms, ScanTime: 35s793ms] |
| │ OutputRows: 222.440M (222440117) |
| │ RuntimeFilter: 2.580B (2580083301) -> 222.440M (222440117) (91.38%) |
| │ Table: : STATIC_BOM_ALL |
| │ SubordinateOperators: |
| │ CHUNK_ACCUMULATE |
| │ NOOP |
| │ OLAP_SCAN_PREPARE |
| │ Details: [ScanTime = IOTaskExecTime + IOTaskWaitTime] |
| │ Infos: |
| │ MorselQueueType: physical_split_morsel_queue |
| │ Predicates: 38: MODULE_CODE IS NOT NULL |
| │ Rollup: STATIC_BOM_ALL |
| │ SharedScan: False |
| │ Table: STATIC_BOM_ALL |
| │ Counters: |
| │ BufferUnplugCount: 9.131K (9131) [min=188, max=1.867K (1867)] |
| │ BufferUnplugThreshold: 32 |
| │ BytesRead: 78.650 GB [min=438.929 MB, max=3.868 GB] |
| │ CachedPagesNum: 0 |
| │ ChunkBufferCapacity: 256 |
| │ CompressedBytesRead: 11.130 GB [min=65.811 MB, max=524.211 MB] |
| │ DefaultChunkBufferCapacity: 256 |
| │ IOTaskExecTime: 7s551ms [min=1s553ms, max=19s334ms] |
| │ CreateSegmentIter: 1.373ms [min=266.112us, max=4.621ms] |
| │ DictDecode: 2s569ms [min=177.630ms, max=6s951ms] |
| │ GetDelVec: 0ns |
| │ GetDeltaColumnGroup: 272.395us [min=39.459us, max=4.134ms] |
| │ GetRowsets: 23.666us [min=4.898us, max=62.985us] |
| │ IOTime: 403.630ms [min=36.337ms, max=1s294ms] |
| │ ReadPKIndex: 0ns |
| │ SegmentInit: 588.845ms [min=89.754ms, max=1s656ms] |
| │ BitmapIndexFilter: 0ns |
| │ BitmapIndexFilterRows: 0 |
| │ BitmapIndexIteratorInit: 147.201us [min=44.085us, max=340.544us] |
| │ BloomFilterFilter: 824.300us [min=13.378us, max=36.922ms] |
| │ BloomFilterFilterRows: 0 |
| │ ColumnIteratorInit: 36.105ms [min=3.250ms, max=149.035ms] |
| │ SegmentRuntimeZoneMapFilterRows: 22.864M (22863872) [min=0, max=1.049M (1048576)] |
| │ SegmentZoneMapFilterRows: 778.265K (778265) [min=0, max=778.265K (778265)] |
| │ ShortKeyFilter: 42.998us [min=11.962us, max=109.199us] |
| │ ShortKeyFilterRows: 0 |
| │ ZoneMapIndexFilterRows: 51.616M (51616207) [min=0, max=5.243M (5242880)] |
| │ ZoneMapIndexFiter: 91.680ms [min=38.767ms, max=203.207ms] |
| │ SegmentRead: 4s131ms [min=612.868ms, max=10s554ms] |
| │ BlockFetch: 2s415ms [min=601.469ms, max=5s477ms] |
| │ BlockFetchCount: 629.926K (629926) [min=3.488K (3488), max=30.894K (30894)] |
| │ BlockSeek: 103.666ms [min=4.831ms, max=448.871ms] |
| │ BlockSeekCount: 519.846K (519846) [min=3.901K (3901), max=21.927K (21927)] |
| │ ChunkCopy: 449.280us [min=98.001us, max=1.733ms] |
| │ DecompressT: 445.623ms [min=229.533ms, max=875.351ms] |
| │ DelVecFilterRows: 0 |
| │ PredFilter: 1s578ms [min=1.697ms, max=5s65ms] |
| │ PredFilterRows: 24.624K (24624) [min=0, max=16.289K (16289)] |
| │ RowsetsReadCount: 2.480K (2480) [min=13, max=121] |
| │ SegmentsReadCount: 83.442K (83442) [min=37, max=5.324K (5324)] |
| │ TotalColumnsDataPageCount: 25.076M (25076067) [min=287.161K (287161), max=951.913K (951913)] |
| │ IOTaskWaitTime: 7s57ms [min=1s485ms, max=18s797ms] |
| │ MorselsCount: 2.480K (2480) [min=59, max=469] |
| │ PeakChunkBufferSize: 256 |
| │ PeakIOTasks: 4 |
| │ PeakScanTaskQueueSize: 158 [min=12, max=15] |
| │ PushdownPredicates: 3 |
| │ RawRowsRead: 2.580B (2580107925) [min=14.287M (14286848), max=126.540M (126540235)] |
| │ ReadPagesNum: 526.066K (526066) [min=3.932K (3932), max=22.250K (22250)] |
| │ RowsRead: 2.580B (2580083301) [min=14.287M (14286848), max=126.532M (126531900)] |
| │ SubmitTaskCount: 14.966K (14966) [min=393, max=2.624K (2624)] |
| │ TabletCount: 6 [min=2, max=2] |
| │ UncompressedBytesRead: 22.581 GB [min=209.156 MB, max=848.397 MB]

#2

该sql可以改写成如下：
select tbl1.* from table1 tbl1,STATIC_BOM_ALL tbl2,table3 tbl3 WHERE
tbl1.MATERIAL_CODE=tbl2.MODULE_CODE and tbl2.AUTO_SN=tbl3.SERNR and
tab1.PROVINCE= ‘浙江省’ and tbl3.PROVINCE = ‘浙江省’;

从改写后的sql看，三张表走全表扫描会使用到pipeline并行执行比串行执行走索引更优

#3

按照你的建议改造试了下，速度变慢了：原来32s，现在42s。
这三个表的数据量：
table1： 1千万
table2 ：26亿
table3 ： 70万

三个表的表结构是：
CREATE TABLE table1 (
…
) ENGINE=OLAP
DUPLICATE KEY(PROVINCE, MATERIAL_CODE, BUS_TY, IS_STORE)
DISTRIBUTED BY HASH(PROVINCE)；

CREATE TABLE table2(
…
) ENGINE=OLAP
DUPLICATE KEY(AUTO_SN)
DISTRIBUTED BY HASH(ID)；

CREATE TABLE table3 (
…
) ENGINE=OLAP
DUPLICATE KEY(PROVINCE, CITY)
DISTRIBUTED BY HASH(TIME_KEY)

#4

可以分别执行下每个表单读走where province='浙江省’的时间在多大，如果你是按省份做前缀索引，理论上应该不会慢，观察下瓶颈呢。不行就拆分成3条SQL去串行执行呢

#5

观察了，瓶颈主要是，查询table2表比较慢，从26亿里边返回2.6亿条记录。后来我们根据业务情况改造了sql。大幅减少了从table2里边返回的数据条目数，性能提升很多。多谢。

只是比较好奇，明明开了强制走索引（bitmap_max_filter_ratio=1000），有些情况却不走，是不是这个配置不能完全强制用索引

#6

把原来的sql收集一下执行计划 explain + sql 和 explain analyze +sql 分别各收集一份，发出来对比看一下

京ICP备2022026421号-1