SQL技巧与优化

亦凉 2022-08-13 14:46 223阅读 0赞

SQL查询相关数据库基础

如何捕获问题SQL

如何分析SQL

案例分析

查询语句执行顺序

(7 )SELECT (8 )DISTINCT   <select list>  
(1 )FROM \[left\_table\]  
(3 )<join\_type> JOIN <right\_table>  
(2 )ON <join\_condition>  
(4 )WHERE <where\_condition>  
( 5)GROUP BY <group\_by\_list>  
( 6)HAVING <having\_condition>  
(9 )ORDER BY <order\_by\_list>

ORACLE索引  
. 索引结构

![20141101142724158][]

. 索引查询示例

![20141101142848773][]

MySQL索引

![20141101143022186][]

![20141101143117566][]

如何捕获问题SQL  
ORACLE  
  
SELECT (SYSDATE - SQL\_EXEC\_START) \* 24 \* 60 \* 60 runtime,  
       sid,  
       serial\#,  
       username,  
       machine,  
       SQL\_ID,  
       (SELECT sql\_text  
          FROM v$sql s  
         WHERE s.sql\_id = sess.sql\_id  
           and ROWNUM = 1)  
  FROM v$session sess  
 WHERE username IS NOT NULL AND username<>'SYS'  
   AND status = 'ACTIVE';

![20141101143212415][]

MySQL

![20141101143348671][]

如何分析SQL

MySQL  
![20141101143427137][]

ORACLE  
  
SQL>set autot traceonly  
                                                                                                                                                ![20141101143618937][]

![20141101143546401][]

案例分析

MySQL  
  
DELETE t FROM (  
  SELECT RESOURCE\_ID, COLUMN\_ID,MIN(column\_ref\_RESOURCE\_ID) minvalue FROM T\_RES\_COLUMN\_MAP  
   GROUP BY RESOURCE\_ID, COLUMN\_ID  
  HAVING COUNT(\*)>1  
) a  
   INNER JOIN T\_RES\_COLUMN\_MAP t ON a.RESOURCE\_ID=t.RESOURCE\_ID  
                AND a.COLUMN\_ID=t.COLUMN\_ID  
               AND t.column\_ref\_RESOURCE\_ID>minvalue  
  
ORACLE  
DELETE T\_RES\_COLUMN\_MAP  
 where rowid in (select rid  
                   from (select map.rowid rid,  
                                row\_number() over(partition by RESOURCE\_ID, COLUMN\_ID order by resource\_rank) rn  
                           from T\_RES\_COLUMN\_MAP map)  
                  where rn > 1);  
  
优化案例分析

优化前(ORACLE)  
  
SELECT COUNT(1)  
  FROM (SELECT t.resource\_id  
          FROM t\_iepg\_asset   t,    t\_iepg\_goods   g,  
               t\_po\_resource  pr,       t\_prodoffering p,  
               t\_city         tc  
         WHERE sysdate - t.sys\_date <= 100  
           AND pr.resource\_id = t.resource\_id  
           AND p.prodoffering\_code = g.prodoffering\_code  
           AND p.prodoffering\_id = pr.prodoffering\_id  
           AND t.STATUS in (1, 2)   AND g.status in (0, 1)  
           AND p.status = 0             AND g.charge\_mode IN (0, 5)  
           AND t.record\_type != 1  
           AND NOT EXISTS (SELECT m.resource\_id  
                  FROM t\_res\_cloumn\_map m  
                 WHERE t.resource\_id = m.resource\_id)  
           AND tc.city\_id = t.city\_id  AND t.media\_type != 3      
        UNION ALL  
        SELECT t.resource\_id  
          FROM t\_iepg\_asset   t,  t\_iepg\_goods   g,  
               t\_po\_resource  pr,    t\_prodoffering p,  
               t\_city         tc  
         WHERE sysdate - t.sys\_date <= 100  
           AND pr.resource\_id = t.resource\_id  
           AND p.prodoffering\_code = g.prodoffering\_code  
           AND p.prodoffering\_id = pr.prodoffering\_id  
           AND t.STATUS in (1, 2)    AND g.status in (0, 1)  
           AND p.status = 0              AND t.record\_type = 1  
           AND NOT EXISTS (SELECT m.resource\_id  
                  FROM t\_res\_cloumn\_map m  
                 WHERE t.resource\_id = m.resource\_id)  
           AND tc.city\_id = t.city\_id    AND t.media\_type != 3)     
  
优化后  
  
SELECT sum(flag)  
  FROM (  
  SELECT (case when t.record\_type=1 and t.media\_type !=3 then 1  
               when t.record\_type!=1  and t.media\_type !=3  and g.charge\_mode IN (0, 5) then 1  
           end) flag  
          FROM t\_iepg\_asset   t,  
               t\_iepg\_goods   g,  
               t\_po\_resource  pr,  
               t\_prodoffering p,  
               t\_city         tc  
         WHERE t.sys\_date >= sysdate - :1  
           AND pr.resource\_id = t.resource\_id  
           AND p.prodoffering\_code = g.prodoffering\_code  
           AND p.prodoffering\_id = pr.prodoffering\_id  
           AND t.STATUS in (1, 2)  
           AND g.status in (0, 1)  
           AND p.status = 0  
           AND NOT EXISTS (SELECT m.resource\_id  
                  FROM t\_res\_cloumn\_map m  
                 WHERE t.resource\_id = m.resource\_id)  
           AND tc.city\_id = t.city\_id  
           );

[20141101142724158]: /images/20220810/cae3ac52f7334b869d5ca63f3c3164ba.png
[20141101142848773]: /images/20220810/41a3229ec2cc420c841c84a6f883e887.png
[20141101143022186]: /images/20220810/732484b7271d4a1884eefb3bd814b426.png
[20141101143117566]: /images/20220810/6278c51fc47a4673a8ba927b63379014.png
[20141101143212415]: /images/20220810/bc4ff1fad4234b38b127f1c91aa550f3.png
[20141101143348671]: /images/20220810/a15f3ccf3eba4ba69f85c8c3df6390fc.png
[20141101143427137]: /images/20220810/e755cb143c3b48d097f51ce59679a812.png
[20141101143618937]: /images/20220810/deba4f28e69e4e879ace831062c3f716.png
[20141101143546401]: /images/20220810/3d8b037d7ac14a68b9ca2fe9146c9b33.png