程序员对sql语句的执行顺序的透彻掌握,是避免编程中各种bug和错误,歧义语句的不二法则。
SQL中的优化方法,与SQL内部执行顺序详解
优化:1、系统层面 1、sga 内存分配+pga(share pool/ cache/DB_BLOCK_SIZE/等)网络、服务器硬件配置、操作系统配置、Oracle服务器配置、数据结构组织、然后才是具体的调整
(1) SORT_AREA_SIZE:默认的用来排序的SORT_AREA_SIZE大小是32K,通常显得有点小,一般可以考虑设置成1M(1048576)。这个参数不能设置过大,因为每个连接都要分配同样的排序内存。
2、sql层面
定期analyze(如果没有使用执行计划稳定性,则应该把表和索引都分析一下,这样可能直接会使查询速度大幅提升。分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令。对于少于100万的表,可以考虑分析整个表,对于很大的表,可以按百分比来分析,但是百分比不能过低,否则生成的统计信息可能不准确。可以通过DBA_TABLES的LAST_ANALYZED列来查看表是否经过分析或分析时间,索引可以通过DBA_INDEXES的LAST_ANALYZED列。)
(8)SELECT (9)DISTINCT (11)<Top Num> <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)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>
FROM:对FROM子句中的前两个表执行笛卡尔积(Cartesian product)(交叉联接),生成虚拟表VT1
ON:对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入VT2。
OUTER(JOIN):如 果指定了OUTER JOIN(相对于CROSS JOIN 或(INNER JOIN),保留表(preserved table:左外部联接把左表标记为保留表,右外部联接把右表标记为保留表,
完全外部联接把两个表都标记为保留表)中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表,则对上一个联接生成的结果表和下一个表重复
执行步骤1到步骤3,直到处理完所有的表为止。
WHERE:对VT3应用WHERE筛选器。只有使<where_condition>为true的行才被插入VT4.
GROUP BY:按GROUP BY子句中的列列表对VT4中的行分组,生成VT5.
CUBE|ROLLUP:把超组(Suppergroups)插入VT5,生成VT6.
HAVING:对VT6应用HAVING筛选器。只有使<having_condition>为true的组才会被插入VT7.
SELECT:处理SELECT列表,产生VT8.
DISTINCT:将重复的行从VT8中移除,产生VT9.
10. ORDER BY:将VT9中的行按ORDER BY 子句中的列列表排序,生成游标(VC10).
11. TOP:从VC10的开始处选择指定数量或比例的行,生成表VT11,并返回调用者。
(1) Oracle的解析器按照从右到左的顺序处理SQL中WHERE执行顺序;
(2)WHERE子句中的连接顺序:
Oracle采用自下而上(从右往左)的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
PQO(并行查询选项)是 Oracle 的一个强大的特性,为了正确地使用它,一定要合理配置数据库。并行查询允许多CPU 系统把数据库任务(通常是全表扫瞄)划分为能同时(并行)执行的一些片。为执行该任务要求如下:
Cours Student SC 查找语文考100分的考生
select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )
-
SELECT s.* from
-
Student s
-
INNER JOIN SC sc
-
on sc.s_id = s.s_id
- where sc.c_id=0 and sc.score=100
正常情况下是先join再where过滤,但是我们这里的情况,如果先join,将会有70w条数据发送join做操,因此先执行where
过滤是明智方案,
-
SELECT
-
s.*
-
FROM
-
(
-
SELECT
-
*
-
FROM
-
SC sc
-
WHERE
-
sc.c_id = 0
-
AND sc.score = 100
-
) t
-
INNER JOIN Student s ON t.s_id = s.s_id
结论
1. in()适合B表比A表数据小的情况
2. exists()适合B表比A表数据大的情况
当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用.
EXISTS与IN的使用效率的问题,通常情况下采用exists要比in效率高,因为IN不走索引,但要看实际情况具体使用:
IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
RACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。
(b) WHERE后面的条件顺序影响
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响。如:
1 2 |
|
以上两个SQL中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,所以执行的时候都是全表扫描,第一条SQL的dy_dj = ’1KV以下’条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较,而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较,以此可以得出第二条SQL的CPU占用率明显比第一条低。
(c) 查询表顺序的影响
在FROM后面的表中的列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下,ORACLE会按表出现的顺序进行链接,由此可见表的顺序不对时会产生十分耗服物器资源的数据交叉。(注:如果对表进行了统计分析,ORACLE会自动先进小表的链接,再进行大表的链接)
用Where子句替换HAVING子句:
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中,on是最先执行,where次之,having最后,因为on是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的,where也应该比having快点的,因为它过滤数据后才进行sum,在两个表联接时才用on的,所以在一个表的时候,就剩下where跟having比较了。在这单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,只是where可以使用rushmore技术,
(19) sql语句用大写的;因为oracle总是先解析sql语句,把小写的字母转换成大写的再执行。