无索引的BigQuery中在WHERE/ON子句使用函数有何影响？

和传统OLTP/数仓的影响逻辑差异 #

传统带索引的数据库、依赖块级索引的传统数仓，之所以普遍要求避免在WHERE/ON子句对过滤列使用函数，核心原因是：对列施加函数会切断优化器与索引/分区裁剪路径的关联——比如写WHERE YEAR(create_time) = 2024，优化器无法直接将条件转化为create_time BETWEEN '2024-01-01' AND '2024-12-31'这类可索引、可裁剪的形式，最终会触发不必要的全量扫描，IO成本会高出几个数量级。
而BigQuery是无索引的存算分离架构，性能优化完全依赖两个核心机制：分区裁剪、聚簇排序，所以影响逻辑和传统场景完全不同，不能直接套用旧的经验教条。

BigQuery场景下的实际影响边界 #

会导致成本、性能严重劣化的场景 #

• 当函数直接作用在分区键上时，BigQuery的优化器会无法识别等价的分区过滤条件，直接跳过分区裁剪步骤。比如一张按dt日期分区、累计10TB数据的表，本来过滤某一天的数据只需要扫描约10GB，要是写WHERE DATE(PARSE_TIMESTAMP('%Y%m%d', dt)) = '20240501'这类嵌套函数，优化器无法推导对应分区范围，会直接全表扫描所有分区，成本直接放大上百倍。哪怕是简单的单函数，比如WHERE EXTRACT(YEAR FROM dt) = 2024，优化器的自动条件推导能力也不稳定，涉及自定义函数、多层嵌套函数时，基本100%会出现裁剪失效的问题。
• 当函数直接作用在大表的聚簇排序键上时，会让聚簇的排序匹配机制失效：本来聚簇键的有序存储可以让同值数据在存储块上连续排布，减少IO读取量，经过函数变换后的聚簇键值，完全打破了原本的有序匹配逻辑，会导致对应列的扫描成本提升数倍。
• 当函数用在大表连接的等值ON条件上时，会让优化器无法识别有效的等值连接关系，直接影响连接策略的选择：本来可以用低开销的广播连接的场景，会退化成高成本的全量Shuffle连接，网络传输和计算成本都会大幅上涨。

影响可忽略的场景 #

如果函数作用在非分区、非聚簇、非连接等值键的普通列上，实际开销和直接过滤原列几乎没有差异。因为BigQuery本身就不会为普通列建立索引，不管你是直接写列过滤，还是对列套函数过滤，都需要读取对应分区下的该列全部存储值做计算过滤，函数带来的CPU开销在整体扫描成本里占比极低，基本不会被用户感知到。比如在非键列上写WHERE LOWER(email) = 'test@example.com'，其查询成本和提前在表里冗余存储一个全小写的email列再做等值过滤的成本差距通常在10%以内。

代码评审的实操判断标准 #

不需要一刀切禁止所有WHERE/ON子句的函数使用，按以下规则判断即可：

• 只要函数涉及分区键、聚簇键、大表连接的等值连接键，必须要求改写为可被优化器识别的等价范围/等值条件，这类场景的函数写法是明确需要打回的
• 对普通过滤列上的函数写法，不做强制要求：BigQuery的计算引擎对列函数的CPU优化非常成熟，这类场景的开销远低于为了规避函数额外维护冗余预计算列的长期成本
• 不要机械套用传统数仓“WHERE子句绝对不能用函数”的教条，很多时候这类写法没有实际性能损失，硬改反而会提升代码的维护复杂度。

内容的提问来源于stack exchange，提问作者Maria