某交通项目，tb_case 2万多条记录，tb_carout

1 使用原查询语句：

 SELECT TB_CASE.CASENO AS CASENO,TB_CASE.LAWGROUP AS LAWGROUP,S1.ROLE_NAME AS LGNAME,TB_CASE.CARPARK AS CARPARK,S2.ROLE_NAME AS CARPARKNAME,TB_CASE.LAWTIME AS LAWTIME,

TB_CASE.CARTYPE AS CARTYPE,TB_CASE.CARNO AS CARNO,TB_CASE.STATUS AS STATUS,TB_CASE.IS_CLONE AS IS_CLONE,TB_CASE.BCFS AS BCFS FROM TB_CASE,SYS_ROLE S1, SYS_ROLE S2

WHERE

((NOT(TB_CASE.CASENO IN (SELECT TB_CAROUT.CASENO FROM TB_CAROUT WHERE ((TB_CAROUT.STATUS = '25') AND (TB_CAROUT.SYMBOL <> 9))))) AND (TB_CASE.LAWGROUP = S1.ROLE_ID) AND (TB_CASE.CARPARK = S2.ROLE_ID))；

查询时间：为88s。

 查询语句都使用了not in语句过滤外层查询表（父表）。

 查询计划如下：

QUERY PLAN 
Loop arti Join 7 om time=oeeoi 
 Join Filter. = TB_CPROUT.CASENO) OR (18_cgse CASENO IS NULL) OR UB_CAROUTCASENO IS NULL)) 
   ->Nested Loop (cost=O.OO .12636.41 rows-17466 width-520) (actual time-0_025 84.242 rows-1745B loops-I) 
       ->Nested Loop (actual timezo.01g .11D116 loops-I) 
          ->Seq scan on TB_CASE (cost=o.oo..36X6.66 rows-1 7466 (actual 2.169 rows-17466 loops-I) 
          ->Index scan using on sys_ROLE Sl rows-I Width2436) (actual time-0 003_.0.003 rows=l loops:17466) 
            Index Sl 
       ->Index scan using on SYS_ROLES2 (cost-0.00_0.27 rows-I width-436) (actual time-0.002_.0.003 rcwvs=l loops-1 7466) 
            Index Cond_ (TB_CASE CARPARK= 82 ROLE_IO) 
  ->Materialize (cost=61 ,gg..787.go rows—I aggl width-1 (actual time=O.OOO..I .882 rows=8723 loops—17458) 
         ->seq scan on TB_CAROUT (cost-0 00.601.00 rcnws=laggl width—1 7) (actual time-DOI rows=16gg1 loops-I) 
            Filter ((STATUg AND (SYMBOL g)) 
Total runtime. 88648.766 ms 

    nested loop

      从tb_carout表抽一条记录，遍历tb_case表查找匹配记录，然后从tb_carout表抽下一条，遍历ta_case表,就是一个二重循环

3 使用left join 改写sql

SELECT T1.CASENO AS CASENO,T1.LAWGROUP AS LAWGROUP,S1.ROLE_NAME AS LGNAME,T1.CARPARK AS CARPARK,S2.ROLE_NAME AS CARPARKNAME,

T1.LAWTIME AS LAWTIME,T1.CARTYPE AS CARTYPE,T1.CARNO AS CARNO,T1.STATUS AS STATUS,T1.IS_CLONE AS IS_CLONE,

T1.BCFS AS BCFS FROM TB_CASE T1

left join TB_CAROUT T2 on T1.CASENO=T2.CASENO AND T2.STATUS='25' AND T2.SYMBOL!=9

left join SYS_ROLE S1 ON T1.LAWGROUP=S1.ROLE_ID

left join SYS_ROLE S2 ON T1.CARPARK=S2.ROLE_ID

where T2.CASENO IS NULL;

查询计划如下：

使用left join优化之后，查询时间为78ms

Hash join

      将tb_case表按连接键计算出一个hash表，然后从tb_carout表一条条抽取记录，计算hash值，根据hash到tb_case表的hash来匹配符合条件的记录,t1.caseno=t2.caseno

1 嵌套循环(nest loop):

          对于被连接的数据子集较小的情况，嵌套循环连接是较好的选择。在嵌套循环中，外表驱动内表，外表返回的每一行都要在内表中检索找到它匹配的行，因此整个查询返回的结果集不能太大（大于10000不合适），要把返回子集较小的表作为外表（驱动表），而且在内表的连接字段上一定要有索引。

2）哈希连接(hash join):

         哈希连接是大数据集连接时常用的方式，优化器使用两个表中较小的表，利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行.对优化器有很大要求。