文章目錄
- where 1=1
- 問題描述
- 錯誤實現
- 正確實現
- 性能對比測試
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 生成性能有明顯影響。
)
