占位符与开关操作

最新推荐文章于 2020-08-06 14:31:08 发布

namu

最新推荐文章于 2020-08-06 14:31:08 发布

阅读量1.1k

点赞数

分类专栏： ORACLE performance tuning 文章标签： sql table filter access 编程数据库

ORACLE performance tuning 专栏收录该内容

4 篇文章

订阅专栏

最近发现应用sql经常出现where 1=1 或1=2等条件，现在记录一下对占位符与开关操作的理解

sql占位操作：

where 1=1; 这个条件始终为True，在不定数量查询条件情况下，1=1可以很方便的规范语句。

一、不用where 1=1 在多条件查询中的困扰

　　举个例子，如果您做查询页面，并且，可查询的选项有多个，同时，还让用户自行选择并输入查询关键词，那么，按平时的查询语句的动态构造，代码大体如下：

　　string MySqlStr=”select * from table where”；

　　if(Age.Text.Lenght>0)
　　{
　　　　MySqlStr=MySqlStr+“Age=“+“'Age.Text'“；
　　}

　　if(Address.Text.Lenght>0)
　　{
　　　　MySqlStr=MySqlStr+“and Address=“+“'Address.Text'“；
　　}

　　①种假设

　　如果上述的两个IF判断语句，均为True，即用户都输入了查询词，那么，最终的MySqlStr动态构造语句变为：

　　MySqlStr=”select * from table where Age='18' and Address='云南省文山州广南县小波吗村'”

　　可以看得出来，这是一条完整的正确的SQL查询语句，能够正确的被执行，并根据数据库是否存在记录，返回数据。

　　②种假设

　　如果上述的两个IF判断语句不成立，那么，最终的MySqlStr动态构造语句变为：

　　MySqlStr=”select * from table where“

　　现在，我们来看一下这条语句，由于where关键词后面需要使用条件，但是这条语句根本就不存在条件，所以，该语句就是一条错误的语句，肯定不能被执行，不仅报错，同时还不会查询到任何数据。

　　上述的两种假设，代表了现实的应用，说明，语句的构造存在问题，不足以应付灵活多变的查询条件。

　　二、使用 where 1=1 的好处

　　假如我们将上述的语句改为：

　　string MySqlStr=”select * from table where 1=1 ”；

　　if(Age.Text.Lenght>0)
　　{
　　　　MySqlStr=MySqlStr+“and Age=“+“'Age.Text'“；
　　}

　　if(Address.Text.Lenght>0)
　　{
　　　　MySqlStr=MySqlStr+“and Address=“+“'Address.Text'“；
　　}

　　现在，也存在两种假设

　　①种假设

　　如果两个IF都成立，那么，语句变为：

　　MySqlStr=”select * from table where 1=1 and Age='18' and Address='云南省文山州广南县小波吗村'”，很明显，该语句是一条正确的语句，能够正确执行，如果数据库有记录，肯定会被查询到。

　　②种假设

　　如果两个IF都不成立，那么，语句变为：

　　MySqlStr=”select * from table where 1=1”，现在，我们来看这条语句，由于where 1=1 是为True的语句，因此，该条语句语法正确，能够被正确执行，它的作用相当于：MySqlStr=”select * from table”，即返回表中所有数据。

　　言下之意就是：如果用户在多条件查询页面中，不选择任何字段、不输入任何关键词，那么，必将返回表中所有数据；如果用户在页面中，选择了部分字段并且输入了部分查询关键词，那么，就按用户设置的条件进行查询。

　　说到这里，不知道您是否已明白，其实，where 1=1的应用，不是什么高级的应用，也不是所谓的智能化的构造，仅仅只是为了满足多条件查询页面中不确定的各种因素而采用的一种构造一条正确能运行的动态SQL语句的一种方法。

where 1=0; 这个条件始终为false，结果不会返回任何数据，只有表结构，可用于快速建表

"SELECT * FROM strName WHERE 1 = 0"; 该select语句主要用于读取表的结构而不考虑表中的数据，这样节省了内存，因为可以不用保存结果集。

create table newtable as select * from oldtable where 1=0; 创建一个新表，而新表的结构与查询的表的结构是一样的。

开关操作

开关操作：开关操作也是一种编程人员常用的编程手段，比如写好一个SQL模板，其中有一个开关(1=:p1 and ....)，如果我们希望后面的AND起作用，那么p1就赋值为1，如果不希望后面的AND起作用，p1就赋值非1的值。

例子分析

在出现该问题的数据库系统中，它的初始化参数 cursor_sharing 的值是 force 。

从问题现象看，使用绑定变量的 SQL 执行计划和不使用绑定变量的不一样，前者走的执行计划不合理。

这个 SQL 也比较复杂， where 子句中既有自定义的变量语句，也有很多常量语句。在常量条件中，有个占位符子句，紧随 where 关键字。

这类写法在 JAVA 中拼装 SQL 语句时很常见。在需要新加条件判断语句时，直接加上 ”and xx=yy” ，变成 ”where 1=1 and xx=yy” 。

这种写法很通用吧？

语句

出现问题的 SQL 语句如下：

SELECT *

FROM (SELECT row_.*, ROWNUM rownum_

FROM (select count(*)

from (select t1.id as id,

'sms' as type,

t1.empid as empid,

t1.deptno as deptno,

t1.content as title,

to_char(t1.send_time, 'yyyy-MM-dd hh24:mi:ss') as plantime,

t1.sysuid as sysuid,

t1.custid as custid,

t1.mobile as contact,

t1.mark as mark

from liantong_send_back t1

where 1 = 1

and t1.send_time >=

to_date('2011-11-01 00:00:00',

'yyyy-mm-dd hh24:mi:ss')

and t1.send_time <

(to_date('2011-12-06 00:00:00',

'yyyy-mm-dd hh24:mi:ss') + 1)

and t1.deptno = '3400'

union all

select t1.id as id,

'sms' as type,

t1.empid as empid,

t1.deptno as deptno,

t1.content as title,

to_char(t1.send_time, 'yyyy-MM-dd hh24:mi:ss') as plantime,

t1.sysuid as sysuid,

t1.custid as custid,

t1.mobile as contact,

t1.mark as mark

from liantong_send t1

where 1 = 1

and t1.send_time >=

to_date('2011-11-01 00:00:00',

'yyyy-mm-dd hh24:mi:ss')

and t1.send_time <

(to_date('2011-12-06 00:00:00',

'yyyy-mm-dd hh24:mi:ss') + 1)

and t1.deptno = '3400') T

where rownum < 10001

order by T.plantime desc, T.id) row_

WHERE ROWNUM <= :b)

WHERE rownum_ > :a

这个 SQL 的内部 where 条件中有一个“ 1=1 ”的条件，www.linuxidc.com这是开发人员在条件不定时，动态添加条件的常用写法。这种写法在很多类型的应用中都出现过。

这个 SQL 很明显去掉 rownum 的条件判断使用的绑定变量，其他条件都是常量赋值。这是因为内部那个结果集的 SQL 是应用程序拼出来的，条件很灵活，不容易实现带变量的写法。因此我们让数据库系统在执行之前自动去修改这些常量为变量，从而实现不同常量的 SQL 能共享游标（ cursor ），减少硬分析。

设置 cursor_sharing=force ，就实现了这种自动转换。但占位符（“ 1=1 ”）也会被系统自动替换成 :"SYS_B_02" = :"SYS_B_03" 。

但在 cursor_sharing=exact 时，系统的优化器则是做了另一种操作。它将占位符（“ 1=1 ”）忽略掉，因为也确实不需要去判断，从而节省 CPU 执行时间

老白的经验

占位操作是开发人员常用的一种编程方法，比如WHERE条件是动态生成的，那么WHERE后面的第一个条件和更后面的条件是不同的，WHERE 后的第一个条件前面没有任何运算符，而后面的都带有运算符，因此如果WHERE变为WHERE 1=1，那么后面的所有条件都有运算符，编程就简单很多。实际上占位操作是一种很不好的编程习惯，如果条件很复杂，又大量使用占位操作，那么会导致优化器无法获得正确的执行计划。

开关操作也是编程人员常用的一种编程手段，同样也是性能杀手。比如写好一个SQL模板，其中有一个开关（1=:p1 and...），如果我们希望后面的and起作用，那么p1就赋值为1，如果不希望后面的and起作用，p1就赋非1的值。

对较为复杂情况的应用来说，占位操作和开关操作可以提高SQL代码的重用，简化SQL的逻辑关系。但是占位操作可能带来严重的数据库性能问题。由于使用了大量的开关变量和占位符，所以优化器很容易产生错误的执行计划，增加SQL的成本。实际上，占位操作完全可以通过应用程序的逻辑来替代。在应用软件开发过程中，尽量不要为了满足复杂的逻辑关系而编写过于庞大的SQL。通过程序的逻辑控制，将一个复杂的SQL分解为几个小型的SQL是一种十分好的编程习惯。由于开发团队往往过多地考虑功能上的实现以及代码的重用，而无法考虑到维护中产生的额外成本，所以维护团队中的DBA需要给开发团队的开发人员一定的培训，并且参与到各级评审中，尽早避免出现这种含有大量开关操作和占位操作的SQL

其他人的见解

例如，以下的SQL实现一个功能，
当输入参数是P的时候，使用索引的方式扫描表，
当输入参数是A的时候，使用全表扫描。

以下分析一下，这段SQL的执行计划和实际执行的行源操作
EXPLAIN PLAN FOR
SELECT *
FROM test_738_a a
WHERE DECODE(:p_mode, 'P', 1, 2) = 1
AND a.created >= SYSDATE -1
AND a.created < SYSDATE
UNION ALL
SELECT *
FROM test_738_a a
WHERE DECODE(:p_mode, 'P', 1, 2) = 2;
SELECT * FROM TABLE(dbms_xplan.display(NULL, NULL, 'ALL'));

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 356K| 31M| 1377 (100)|
| 1 | UNION-ALL | | | | |
|* 2 | FILTER | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_738_A | 19 | 1786 | 4 (0)|
|* 4 | INDEX RANGE SCAN | TEST_738_A_IND | 19 | | 3 (0)|
|* 5 | FILTER | | | | |
| 6 | TABLE ACCESS FULL | TEST_738_A | 356K| 31M| 1373 (1)|
-------------------------------------------------------------------------------------

你能看到这时候，这段执行计划的成本是很高的。

使用'P'参数，带入实际运行后，从trace文件中取得行源操作信息。
SELECT *
FROM
TEST_738_A A WHERE DECODE(:B1 , 'P', 1, 2) = 1 AND A.CREATED >= SYSDATE -1
AND A.CREATED < SYSDATE UNION ALL SELECT * FROM TEST_738_A A WHERE
DECODE(:B1 , 'P', 1, 2) = 2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 19.66 0 0 0 0
Execute 1 100.00 33.53 0 0 0 0
Fetch 1 100.00 247.35 6 15 0 43
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 200.00 300.54 6 15 0 43

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 44 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
43 UNION-ALL (cr=15 pr=6 pw=0 time=2258 us)
43 FILTER (cr=15 pr=6 pw=0 time=2207 us)
43 TABLE ACCESS BY INDEX ROWID TEST_738_A (cr=15 pr=6 pw=0 time=2147 us)
43 INDEX RANGE SCAN TEST_738_A_IND (cr=3 pr=3 pw=0 time=1811 us)(object id 770511)
0 FILTER (cr=0 pr=0 pw=0 time=4 us)
0 TABLE ACCESS FULL TEST_738_A (cr=0 pr=0 pw=0 time=0 us)

********************************************************************************
你能发现，只有索引部分的那段SQL被执行了。

使用'A'参数，带入实际运行后，从trace文件中取得行源操作信息。
SELECT *
FROM
TEST_738_A A WHERE DECODE(:B1 , 'P', 1, 2) = 1 AND A.CREATED >= SYSDATE -1
AND A.CREATED < SYSDATE UNION ALL SELECT * FROM TEST_738_A A WHERE
DECODE(:B1 , 'P', 1, 2) = 2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.43 0 0 0 0
Execute 1 0.00 0.65 0 0 0 0
Fetch 3570 22200.00 57279.65 4936 8496 0 356914
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3572 22200.00 57280.73 4936 8496 0 356914

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user id: 44 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
356914 UNION-ALL (cr=8496 pr=4936 pw=0 time=2878377 us)
0 FILTER (cr=0 pr=0 pw=0 time=10 us)
0 TABLE ACCESS BY INDEX ROWID TEST_738_A (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN TEST_738_A_IND (cr=0 pr=0 pw=0 time=0 us)(object id 770511)
356914 FILTER (cr=8496 pr=4936 pw=0 time=2521297 us)
356914 TABLE ACCESS FULL TEST_738_A (cr=8496 pr=4936 pw=0 time=2521256 us)

********************************************************************************
你能发现，只有全表扫描的那段SQL被执行了。

综上，你能发现开关操作可能会出现很高的执行计划成本，但是实际执行的时候，SQL会根据开关的指示，去运行打开的SQL，忽略关闭的SQL。

所以说，开关和性能没有必然的联系。

以上的SQL由两段子SQL组成，有开关标识，动态决定在运行的时候执行哪一段子查询。

如果想查询出部分数据，那么通过索引效率较高，所以走基于created字段的索引，即第一段子SQL。

如果想查询出全表的数据，那么走全表扫描的效率较高，即第二段子SQL。

由于SQL在运行之前，需要进行解析，生成执行计划，所以第一段子SQL和第二段子SQL的执行计划都同时生成了，并且对其cost，进行了累加。导致了，你看到的执行计划比单独的第一段子SQL和第二段子SQL都要大。这个就会给一知半解的人造成一种假象，即使用动态开关拼接SQL后，cost变大了。

但是需要注意的是，执行计划是一个理论值，就像纸上谈兵一样。真正运行的效率，是需要参考trace文件中的行源操作信息的。

在上例中，通过行源操作信息，你可以发现，在输入“P”的时候，整个这段SQL，走了索引，根本就没有去走全表扫描。所以，它执行的效率是很高的。

即，行源操作信息是你运行SQL的实际信息，而执行计划是SQL运行的理论值。

两者在大部分情况下，是一致的，但是遇到复杂的情况时，就会产生偏差，开关操作，就是一例。