MariaDB关联查询时特定表未使用主键的原因与优化
核心原因分析
1. 为什么关联icd_codes表性能正常,关联demographics表会出现全表扫
两类查询的性能差异本质是优化器选择了错误的join顺序,触发了跨表笛卡尔积的BNL(块嵌套循环)关联,和是否命中主键索引没有直接关系:
- 关联
icd_codes的执行计划符合「小结果集驱动大表」的正常NLJ(嵌套循环连接)逻辑:先从variants_145K的索引过滤得到280行有效变异记录,接着逐行用Var_ID关联pVCF_145K的主键得到7.5万条患者记录,最后逐行用PT_ID关联icd_codes的联合主键(PT_ID为最左列)完成关联,全程没有无连接条件的笛卡尔积,因此执行速度快。 - 关联
demographics的执行计划存在明显的顺序偏差:优化器将demographics调整到pVCF_145K之前执行,此时variants_145K和demographics之间没有直接关联条件,只能先对两个表的结果集做笛卡尔积,再用组合出的(Var_ID, PT_ID)去匹配pVCF_145K的主键。因为demographics只有191万行,优化器错误低估了全表扫后逐行查找大表pVCF_145K的无效IO成本,认为这种笛卡尔积路径的总成本低于先关联pVCF_145K再关联demographics的顺序,因此触发了全表扫加BNL的高成本路径。
补充对比:如果把体量为1.16亿行的
icd_codes放到pVCF_145K之前做全表扫,成本会远高于demographics的扫描成本,优化器自然不会选择这种笛卡尔积路径,因此关联icd_codes时不会出现性能问题。
2. 为什么增加Platform IS NOT NULL过滤条件后,优化器依然没有使用demographics的PT_ID主键
增加过滤条件后,demographics的扫描行数从191万降到25.8万,但是优化器的核心决策逻辑没有变:依然认为全表/索引扫demographics的顺序IO成本,低于先关联pVCF_145K拿到7.5万有效PT_ID、再逐行用PT_ID查找demographics主键的随机IO成本,因此依然会走笛卡尔积加BNL的路径,只是扫描行数变少后耗时从11分钟降到38秒,并没有从根本上解决join顺序错误的问题。
优化方案
以下方案可以强制让查询按照正确的顺序关联,正常命中demographics的PT_ID主键,避免全表扫描:
- 方案1:强制固定join顺序
将查询中的INNER JOIN替换为STRAIGHT_JOIN,禁止优化器随意调整表的关联顺序,强制按照variants_145K → pVCF_145K → demographics的顺序执行。这个调整可以直接把查询耗时降到和关联icd_codes同量级的毫秒级水平。示例写法:SELECT variants_145K.Var_ID, variants_145K.Gene, variants_145K.HGVSc, pVCF_145K.PT_ID, pVCF_145K.AD_ALT, pVCF_145K.AD_REF, demographics.Sex, demographics.Age FROM public.variants_145K STRAIGHT_JOIN public.pVCF_145K USING (Var_ID) STRAIGHT_JOIN public.demographics USING (PT_ID) WHERE Gene IN ('SLC9A6', 'SLC9A7') AND Canonical AND impact = 'high'; - 方案2:重新收集统计信息纠正成本偏差
优化器的错误决策很大程度来自表统计信息失效,导致成本估算严重失真。执行以下命令重新收集四张关联表的统计信息,让优化器准确感知「先关联pVCF_145K只会得到7.5万条有效PT_ID,后续关联demographics的主键查找成本极低」的事实,大部分情况下更新统计信息后优化器会自动选择正确的join顺序:ANALYZE TABLE variants_145K, pVCF_145K, demographics, icd_codes; - 方案3:关闭BNL join的优先级选择权
通过会话级参数调整,禁止优化器选择全表扫加BNL的关联路径,强制优化器优先选择有索引的嵌套循环连接,避免无意义的全表扫描:
调整后优化器会自动选择能命中主键的关联路径,不会再走全表扫的笛卡尔积逻辑。SET optimizer_switch='block_nested_loop=off';
内容的提问来源于stack exchange,提问作者jon
相关产品推荐
相关产品推荐

