1. 只返回需要的列, 避免用 select * from t1
2. 加过滤条件限制返回的行数: select name, dep_name from t1 where age>30
3. 避免笛卡尔乘积, select * from a,b
-- 使用参数化查询, where col1=?, 减少编译时间
4. 避免对查询条件计算, where salary*2>xx 改为 salary > xx/2
5. 尽量避免在索引列上使用 not,!= 和 <>, 索引只能告诉什么在表中, 而不能告诉什么不在表中,
当数据库遇上以上几种符号时, 将不再使用索引, 使用全表扫描
- 6.
- in/exist, not in/not exist
可以用 exists 代替 in, 可以提高查询的效率. 其实也是分情况的:
in 与 exists 的使用取决于子查询集合大小, IN 适合于外表大而内表小的情况;
EXISTS 适合于外表小而内表大的情况 (前提是内表字段有索引).
所以结论: 如果子查询得出的结果集记录较少, 外层主查询中的表较大且又有索引时应该用 in,
反之如果外层的主查询记录较少, 子查询中的表大, 又有索引时使用 exists
举例, 以下两个 sql 是高效的:
select * from big_tab where id in (select id from small_tab); -- 内表 small_tab 是小表, 而外表 big_tab 是大表且有索引
select * from small_tab a where exists(select 1 from big_tab b where b.id = s.id); -- 内表 big_tab 是大表且有索引, 而外表 small_tab 是小表
原因: in 是把外表和内表作 hash 连接, 而 exists 是对外表作 loop 循环, 每次 loop 循环再对内表进行查询
一直以来认为 exists 比 in 效率高的说法是不准确的
7. 无论任何情况: not exists > not in;
原因:
如果查询语句使用了 not in 那么内外表都进行全表扫描, 没有用到索引;
而 not extsts 的子查询依然能用到表上的索引所以无论那个表大, 用 not exists 都比 not in 要快
8. 如果可能, 尽量避免使用 order by 和 distinct 等排序操作
9. 任何在 where 子句中使用 is null 或 is not null 的语句优化器是不允许使用索引的
10. 注意 LIKE 模糊查询的使用, 避免 %%
-- 使用 for read only 或 for fetch only
11. 避免数字类型转换, 避免数据类型不匹配
-- 减少数据库访问次数
12. 注意 SQL 的 where 条件书写顺序:
对于大部分数据库而言, sql 语句的解析都是有顺序的
比如 Oracle 数据库采用自下而上的顺序解析 where 字句, 所以那些可以滤过大量纪录的条件应该写在 where 字句的末尾, 例如:
(DB2 就不需要, 因为 DB2 的优化器足够智能, 能够根据实际情况来对 sql 进行优化来决定合理的执行顺序)
- select * from table e
- where 25<(select count(*)
- from table
- where count=e.count);
- and h>500
- and d='001';
说明: d='001'能够过滤掉绝大多数数据, 所以这样写更高效
-- 避免使用 HAVING 字句
12. 尽量多使用 commit
13. 有条件的使用 union-all 代替 union 提高效率
14. 用 UNION 替换 OR (适用于索引列) , 如:
select dep_name, emp_name from tab1 where age>34 or gdp<'C';
如果 age 和 gdp 都是索引列, 那么可以用 union 更高效:
select dep_name, emp_name from tab1 where age>34
union
select dep_name, emp_name from tab1 where gdp<'C';
15.用 >= 替代 >
高效:
SELECT * FROM EMP WHERE DEPTNO >=4
低效:
SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于, 前者 DBMS 将直接跳到第一个 DEPT 等于 4 的记录而后者将首先定位到 DEPTNO=3 的记录并且向前扫描到第一个 DEPT 大于 3 的记录
来源: http://blog.csdn.net/liujinwei2005/article/details/79364591