WHERE 1=1的性能疑问~
今天在网上看到这个文章 低效的“WHERE 1=1” 文章说：使用添加了“1=1”的过滤条件以后数据库系统就无法使用索引等查询优化策略，数据库系统将会被迫对每行数据进行扫描（也就是全表扫描）以比较此行是否满足过滤条件，当表中数据量比较大的时候查询速度会非常慢。因此如果数据检索对性能有比较高的要求就不要使用这种“简便”的方式。
让我有点晕了 ，没觉得有什么问题啊 ·~~来跟大家讨论讨论·~~
让我有点晕了 ，没觉得有什么问题啊 ·~~来跟大家讨论讨论·~~
20同行回答
where 1=1 这类条件在 优化器在做 query rewrite 的时候会直接去掉的。不会对性能造成影响。
Original Statement:
------------------
select *
from t3
where 1 = 1
Optimized Statement:
-------------------
SELECT Q1.C1 AS "C1"
FROM DB2INST2.T3 AS Q1收起
Original Statement:
------------------
select *
from t3
where 1 = 1
Optimized Statement:
-------------------
SELECT Q1.C1 AS "C1"
FROM DB2INST2.T3 AS Q1收起
浏览811
浏览837
我做了一个测试
db2expln -d ubtdb -g -q "select VISIT_ID from DW_DPA.TSA_DPA_VISIT where 1=1" -t
VISIT_ID是这个表的主键，这条搜索会走索引
db2expln -d ubtdb -g -q "select VISIT_ID from DW_DPA.TSA_DPA_VISIT where 1=1" -t
VISIT_ID是这个表的主键，这条搜索会走索引
[db2inst1@dadw ~]$ db2expln -d ubtdb -g -q "select VISIT_ID from DW_DPA.TSA_DPA_VISIT where 1=1" -t DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008 Licensed Material - Program Property of IBM IBM DB2 Universal Database SQL and XQUERY Explain Tool DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008 Licensed Material - Program Property of IBM IBM DB2 Universal Database SQL and XQUERY Explain Tool ******************** DYNAMIC *************************************** ==================== STATEMENT ========================================== Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = No Intra-Partition Parallel = No SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", "DB2INST1" Statement: select VISIT_ID from DW_DPA.TSA_DPA_VISIT where 1=1 Section Code Page = 1208 Estimated Cost = 51293.703125 Estimated Cardinality = 8218489.000000 Access Table Name = DW_DPA.TSA_DPA_VISIT ID = -6,-32754 | Index Scan: Name = SYSIBM.SQL110801091540160 ID = 1 | | Regular Index (Not Clustered) | | Index Columns: | | | 1: VISIT_ID (Ascending) | #Columns = 1 | Data-Partitioned Table | Skip Inserted Rows | Avoid Locking Committed Data | Currently Committed for Cursor Stability | All data partitions will be accessed | #Key Columns = 0 | | Start Key: Beginning of Index | | Stop Key: End of Index | Index-Only Access | Index Prefetch: Eligible 41763 | Lock Intents | | Table: Intent Share | | Row : Next Key Share | Sargable Index Predicate(s) | | Return Data to Application | | | #Columns = 1 Return Data Completion End of section Optimizer Plan: Rows Operator (ID) Cost 8.21849e+06 RETURN ( 1) 51293.7 | 8.21849e+06 IXSCAN ( 2) 51293.7 | 8.21849e+06 Index: SYSIBM SQL110801091540160收起
浏览903
我也写过WHERE 1=1，但是具体场景我想不起来了。你说的是WHERE后面有这个谓词还是包含这个谓词？如果只有这一个条件，是要全表扫描呀收起
浏览807