SQL技巧与优化

来源：互联网发布：大宗商品交易软件编辑：程序博客网时间：2024/05/21 09:57

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索引
. 索引结构

. 索引查询示例

MySQL索引

如何捕获问题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';

MySQL

如何分析SQL

MySQL

ORACLE

SQL>set autot traceonly

案例分析

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
           );

0 0