一条"简单"的sql语句和小兔子买面包的故事

有时候开发人员写sql语句的时候,接触的性能问题越多,可能对sql语句的结构，性能考虑会多一些，这也是一件好事，不过如果考虑不当，本来原本想做的的一些优化却使得问题变得更加严重。

在生产环境中我们对指定的客户端都有一定的监控，在下午的时候发现一个sql语句执行的时间太长了，抓到语句，一看倒不复杂，是一个开发人员运行的。

从他的sql语句可以看出他在尝试自连接account表（account表示百万级别的）,l9_id不是account的主键，ban这个字段是主键字段。

select *

  from account a

 where l9_id = 'XX'

   and l9_id is not null

   and exists (select 1

          from account

         where l9_id = a.l9_id

           and ban a.ban

           and rownum = 1)

 order by a.l9_id

他要做的查询从结构上来看类似下面的形式，比如表account里的数据如下

 ban     l9_id

1         1001

2         1001

3         1002

4         1001

5         1002

每个account对应一个l9_id字段，是不唯一的，比如根据account ban=1得到l9_id=1001,现在要查的是除了ban=1之外的l9_id为1001的ban列表。

从sql结构中可以看到，开发人员还专门使用了exists rownum，看起来好像还不错。

来看看oracle生成的执行计划。

你会看到cost已经到1886M，时间是999:59:59,从计划来看,oracle都不知道什么时候能执行完成。

的确从生成环境来看，这条语句执行很慢，用了2个小时。

SQL> @plan

Plan hash value: 2418382151

--------------------------------------------------------------------------------------------

| Id  | Operation            | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT     |             |     1 |   204 |       |  1886M  (1)|999:59:59 |

|   1 |  SORT ORDER BY       |             |     1 |   204 |   537M|  1886M  (1)|999:59:59 |

|*  2 |   FILTER             |             |       |       |       |            |          |

|*  3 |    TABLE ACCESS FULL |     ACCOUNT |  1651K|   321M|       | 33945   (1)| 00:06:48 |

|*  4 |    COUNT STOPKEY     |             |       |       |       |            |          |

|*  5 |     TABLE ACCESS FULL|     ACCOUNT |     2 |    26 |       |  1146   (1)| 00:00:14 |

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter( EXISTS (SELECT 0 FROM "ACCOUNT" "ACCOUNT" WHERE

              ROWNUM=1 AND "L9_ID"=:B1 AND "BAN":B2))

   3 - filter("L9_COMPANY_CODE"='XX' AND "L9_ID" IS NOT NULL)

   4 - filter(ROWNUM=1)

   5 - filter("L9_ID"=:B1 AND "BAN":B2)

可以举个笑话来说明一下，这个逻辑的问题，

有一天，小白兔到面包房买面包，问老板，“老板啊，你有100个面包吗？",老板说没有啊，小白兔第二天又去买面包，又问老板有没有100个面包啊。老板还是说没有，第三天的时候老板忙了很久终于做好了100个面包，小白兔又来买面包了，问老板你又100个面包吗，老板高兴的说有啊，小白兔也高兴的说，给我买一个！

如果仔细分析逻辑，就会发现那个rownum是画蛇添足，本来可以顺利得到ban的列表，但是反复循环，循环几百万次，每次都是一个全表扫描，还不一定能够查到对应的ban值。

对于这个语句，其实不用那么复杂。写成下面的形式就好。基本没有什么特别的地方。

 select t1.ban from account t1,account t2

 where t1.id = 'TD' 

 and  t1.id is not null and t2.l9_id is not null

 and t1.l9_id=t2.l9_lid

 and t1.ban!=t2.ban

执行计划来看确实是一个可以达到目标的计划。

Plan hash value: 1286362100

------------------------------------------------------------------------------------------

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT   |             |    48M|  1353M|       | 78038   (2)| 00:15:37 |

|*  1 |  HASH JOIN         |             |    48M|  1353M|    44M| 78038   (2)| 00:15:37 |

|*  2 |   TABLE ACCESS FULL|     ACCOUNT |  1651K|    25M|       | 33924   (1)| 00:06:48 |

|*  3 |   TABLE ACCESS FULL|     ACCOUNT |  6605K|    81M|       | 33915   (1)| 00:06:47 |

所以,有些东西还是大道至简的道理，其实oracle内部也做了很多的优化，对于exists和in已经没有那么明显的差别了。而且它会从数据的分布角度进行计划的解析。从第一个执行计划来看，数据库分析的还是很合理的。