SQL编程性能优化--ORACLE

1.  

   SQL优化

    

2. ORACLE采用自后而前的顺序解析WHERE
   • 写在最后的表将做为基础驱动表
   • 表之间的连接必须写在其他WHERE条件之上
   • 可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾

    

   [执行153.3秒]

   SELECT …

   FROM emp e

   WHERE sal > 50000

   AND job = ‘MANAGER’

   AND 25 < (SELECT COUNT(*) 

   FROM emp

   WHERE mgr = e.emp_id);

    

   [执行10.6秒]

   SELECT …

   FROM emp e

   WHERE 25 < (SELECT COUNT(*) 

   FROM emp

   WHERE mgr=e.emp_id)

   AND sal > 50000

   AND job = ‘MANAGER’;

    

3. 某些表查询小量数据，但必须和别的表关联才能出数据
4. EXISTS代替关联及IN效率更高，尤其是使用Distinct时

    

5. [低效执行]

   SELECT DISTINCT d.dept_id,d.dept_name

   FROM dept d,emp e

   WHERE d.dept_id = e.dept_id;

    

   [高效执行]

   SELECT dept_id,dept_name

   FROM dept d

   WHERE EXISTS ( SELECT ‘X’

   FROM emp e

   WHERE e.dept_id = d.dept_id);

    

    

6. 一般来说，使用表联接替换EXISTS效率更高点

    

   [低效执行]

   SELECT e.c_name,e.e_name

   FROM emp e

   WHERE EXISTS(SELECT’X’

   FROM dept d

   WHERE e.dept_id = e.dept_id);

    

   [高效执行]

   SELECT e.c_name,e.e_name

   FROM emp e

   INNER JOIN dept d ON d.dept_id = d.dept_id;

    

7. 某些表查询小量数据，但必须和别的表关联才能出数据
8. NOT EXISTS代替关联及NOT IN效率更高

    

   低效执行

   SELECT …

   FROM emp

   WHERE dept_no NOT IN 

   (SELECT dept_no

   FROM dept

   WHERE dept_cat=’A’);

    

   高效执行

   SELECT ….

   FROM emp e

   WHERE NOT EXISTS (SELECT ‘X’

   FROM dept d

   WHERE d.dept_id = e.dept_id

   AND dept_cat = ‘A’);

    

9. UNION 和 UNION ALL

    

   低效执行，30秒

   SELECT ...

   FROM exam_tq_ees_bl

   UNION

   SELECT ...

   FROM exam_tq_ees_ca

   UNION

   SELECT ...

   FROM exam_tq_ees_gr;

    

   高效执行，0.06秒

   SELECT ...

   FROM exam_tq_ees_bl t

   UNION ALL

   SELECT ...

   FROM exam_tq_ees_ca

   UNION ALL

   SELECT ...

   FROM exam_tq_ees_gr;

    

10. 可恶的 LIKE 左操作

     

11. CREATE INDEX USER_INFO_IDX ON USER_INFO(UPPER(FULL_ID));

    低效执行，5秒

    SELECT ...

    FROM user_info

    WHERE UPPER(full_id) LIKE '%DP';

     

    高效执行，0.06秒：

    CREATE INDEX USER_INFO_IDX ON USER_INFO(UPPER(REVERSE(FULL_ID)));

    SELECT ...

    FROM user_info

    WHERE UPPER(full_id) LIKE REVERSE('%DP‘);

     

12. 使用Decode减少重复扫描行数据

     

    [低效执行]

    SELECT COUNT(*)，SUM(sal)

    FROMemp

    WHERE dept_id = 0020 AND ename LIKE‘SMITH%’;

    SELECT COUNT(*)，SUM(sal)

    FROMemp

    WHERE dept_id = 0030 AND ename LIKE ‘SMITH%’;

     

    [高效执行]

    SELECT COUNT(DECODE(dept_id,0020,’X’,NULL)) D0020_CNT,

    COUNT(DECODE(dept_id,0030,’X’,NULL)) D0030_CNT,

    SUM(DECODE(dept_id,0020,sal,NULL)) D0020_SUM,

    SUM(DECODE(dept_id,0030,sal,NULL)) D0030_SUM

    FROM emp 

    WHERE ename LIKE‘SMITH%’;

     

13. 使用Returning子句减少语句执行次数

     

    [获取主键值，低效执行]

    INSERT INTO employees (id,full_name,……)

    VALUES (employee_s.nextval,’Jerry’,……);

    SELECT id INTO v_id

    FROM employees

    WHERE full_name = ‘Jerry’;

     

    [获取主键值，高效执行]

    INSERT INTO employees (id,full_name,……)

    VALUES (employee_s.nextval,’Jerry’,……)

    RETURNING id INTO v_id;

     

14. 使用START WITH ...CONNECT BY获取递归数据

     

    SELECT dept_id, parent_id, dept_name

    FROM dept_test

    START WITH parent_id = 0

    CONNECT BY parent_id = PRIOR dept_id

    ORDER BY parent_id, dept_id;

     

15. 关于Transaction
    • 事务尽早提交
    • 事务提交的频度不能太细
    • 事务不要太大

     

    HOL案例：

    打开事务 → 执行和数据库相关事情 → 一堆数据库无关的事 → 关闭事务

16. 数据库连接池爆了!

    eLearning邮件发送：

    获取队列中100笔邮件 → 发送一笔邮件 → 打开事务 → 更新邮件发送状态 → 将当前数行据移到历史表 → 关闭事务 → 循环处理完所有邮件

17. 数据库被拖死了!

     

18. 慎用Order by (大数据量排序时非常耗数据库资源)