泰兴做网站的公司/百度知道问答首页

where 1=1

问题描述

在动态 SQL 拼接场景中，开发者常使用 WHERE 1=1 简化条件拼接逻辑（避免处理首个条件的 AND）。理论上，数据库优化器会忽略 1=1，但字符串拼接可能带来性能损耗，部分数据库优化器可能无法完全优化 1=1，导致索引失效或全表扫描。建议使用<where> 标签替代 WHERE 1=1 。

错误实现

select *
from job_info
where 1=1
<if test="jobName != null and jobName != '' "><bind name="jobNameBind" value="'%' + jobName + '%'"/>and job_name like #{jobNameBind}
</if>

正确实现

select *
from job_info
<where>
<if test="jobName != null and jobName != '' "><bind name="jobNameBind" value="'%' + jobName + '%'"/>job_name like #{jobNameBind}
</if>
</where>

性能对比测试

• 数据库：mysql-8.0.29

• EXPLAIN测试对比：

EXPLAIN SELECT * FROM koca_order WHERE 1=1 AND order_id > 1012EXPLAIN SELECT * FROM koca_order WHERE order_id > 1012

id	select_type	type	possible_keys	rows	filtered	Extra
1	SIMPLE	ALL	PRIMARY	600	33.33	Using where

• JMH测试：

@BenchmarkMode(Mode.Throughput)
@OutputTimeUnit(TimeUnit.MILLISECONDS)
@State(Scope.Benchmark)
@Fork(3)
@Warmup(iterations = 5, time = 2)
@Measurement(iterations = 5, time = 5)
@Threads(16)
public class WhereConditionBenchmark {private List<String> conditions;@Setuppublic void setup() {// 模拟 5 个动态查询条件conditions = Arrays.asList("age > 18", "status = 1", "name LIKE 'John%'", "city = 'NY'", "score >= 60");}/*** 原始实现：使用 WHERE 1=1 简化拼接*/@Benchmarkpublic void withDummyCondition(Blackhole bh) {StringBuilder sql = new StringBuilder("SELECT * FROM users WHERE 1=1");for (String cond : conditions) {sql.append(" AND ").append(cond);}bh.consume(sql.toString()); // 避免 JIT 优化}/*** 优化实现：直接拼接 WHERE 条件*/@Benchmarkpublic void withoutDummyCondition(Blackhole bh) {StringBuilder sql = new StringBuilder("SELECT * FROM users");boolean first = true;for (String cond : conditions) {if (first) {sql.append(" WHERE ");first = false;} else {sql.append(" AND ");}sql.append(cond);}bh.consume(sql.toString());}}

测试结果：

Benchmark                                          Mode  Cnt      Score      Error   Units
TT.WhereConditionBenchmark.withDummyCondition     thrpt   15  16509.986 ± 3028.386  ops/ms
TT.WhereConditionBenchmark.withoutDummyCondition  thrpt   15  20124.583 ±  420.773  ops/ms

测试结论：

移除冗余的 WHERE 1=1 后，吞吐量提升约 21.9%，说明 1=1 对动态 SQL 生成性能有明显影响。