sql查询中尽量减少的使用
来源:互联网 发布:企业营收数据分析表 编辑:程序博客网 时间:2024/05/29 10:57
在数据库中抓到了下面的sql,每小时执行6次,查询条件中大部分是status字段,我推断应该是监控程序,每10分钟运行一次。监控程序一般都是提取异常数据,数据量很少,要不然监控就失去意义了,像这种sql一般是要通过索引迅速定位到数据的。
原始sql:
SQL> select *
2 from tc_xx_trade t
3 where t.xx_status <> 4
4 and t.xx_status <> 5
5 and t.cs_status = 1
6 and t.timeout_type <> 'STOP_TIMEOUT'
7 and t.gmt_xiaoer_inter is not null
8 and t.gmt_xiaoer_inter < sysdate
9 and rownum < 50;
Execution Plan
--------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=9655 Card=49 Bytes=15092)
COUNT (STOPKEY)
TABLE ACCESS (FULL) OF 'TC_xx_TRADE' (Cost=9655 Card
=263124 Bytes=81042192)
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
109922 consistent gets
101640 physical reads
120 redo size
5552 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
2 from tc_xx_trade t
3 where t.xx_status <> 4
4 and t.xx_status <> 5
5 and t.cs_status = 1
6 and t.timeout_type <> 'STOP_TIMEOUT'
7 and t.gmt_xiaoer_inter is not null
8 and t.gmt_xiaoer_inter < sysdate
9 and rownum < 50;
Execution Plan
--------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=9655 Card=49 Bytes=15092)
COUNT (STOPKEY)
TABLE ACCESS (FULL) OF 'TC_xx_TRADE' (Cost=9655 Card
=263124 Bytes=81042192)
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
109922 consistent gets
101640 physical reads
120 redo size
5552 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
sql走全表扫描,根据上面的判断我认为cs_status=1的数据量应该很小,走索引会很快。
加上hint:
SQL> select/*+index(t IDX_TC_REF_TRA_GMT_XIAOER)*/ *
2 from tc_xx_trade t
3 where t.xx_status <> 4
4 and t.xx_status <> 5
5 and t.cs_status = 1
6 and t.timeout_type <> 'STOP_TIMEOUT'
7 and t.gmt_xiaoer_inter is not null
8 and t.gmt_xiaoer_inter < sysdate
9 and rownum < 50;
Execution Plan
---------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=113996 Card=49 Bytes =15092)
0 COUNT (STOPKEY)
1 TABLE ACCESS (BY INDEX ROWID) OF 'TC_xx_TRADE' (Cost
=113996 Card=263208 Bytes=81068064)
2 INDEX (RANGE SCAN) OF 'IDX_TC_REF_TRA_GMT_XIAOER' (NON
-UNIQUE) (Cost=32246 Card=273964)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
61745 consistent gets
34586 physical reads
65124 redo size
11553 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
28 rows processed
2 from tc_xx_trade t
3 where t.xx_status <> 4
4 and t.xx_status <> 5
5 and t.cs_status = 1
6 and t.timeout_type <> 'STOP_TIMEOUT'
7 and t.gmt_xiaoer_inter is not null
8 and t.gmt_xiaoer_inter < sysdate
9 and rownum < 50;
Execution Plan
---------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=113996 Card=49 Bytes =15092)
0 COUNT (STOPKEY)
1 TABLE ACCESS (BY INDEX ROWID) OF 'TC_xx_TRADE' (Cost
=113996 Card=263208 Bytes=81068064)
2 INDEX (RANGE SCAN) OF 'IDX_TC_REF_TRA_GMT_XIAOER' (NON
-UNIQUE) (Cost=32246 Card=273964)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
61745 consistent gets
34586 physical reads
65124 redo size
11553 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
28 rows processed
逻辑读还是很大,这表示cs_status=1的数据量应该是很多的,过滤性很差。
监控一般只会取很少的一部分数据,哪个条件具有过滤性呢,不了解业务的情况下我们只能去推测,把xx_status<>转换为 in()试试看。
<>转化为in:
SQL> select *
2 from tc_xx_trade t
3 where t.xx_status in (1, 2, 3, 6)
4 and t.cs_status = 1
5 and t.timeout_type <> 'STOP_TIMEOUT'
6 and t.gmt_xiaoer_inter is not null
7 and t.gmt_xiaoer_inter < sysdate
8 and rownum < 50;
Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=541550 Card=49 Bytes =15092)
0 COUNT (STOPKEY)
1 INLIST ITERATOR
2 TABLE ACCESS (BY INDEX ROWID) OF 'TC_xx_TRADE' (Co
st=541550 Card=1462696 Bytes=450510368)
3 INDEX (RANGE SCAN) OF 'IDX_TC_REF_TRA_GMT_XIAOER' (N
ON-UNIQUE) (Cost=4475 Card=1462696)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
898 consistent gets
0 physical reads
0 redo size
15366 bytes sent via SQL*Net to client
689 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49 rows processed
2 from tc_xx_trade t
3 where t.xx_status in (1, 2, 3, 6)
4 and t.cs_status = 1
5 and t.timeout_type <> 'STOP_TIMEOUT'
6 and t.gmt_xiaoer_inter is not null
7 and t.gmt_xiaoer_inter < sysdate
8 and rownum < 50;
Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=541550 Card=49 Bytes =15092)
0 COUNT (STOPKEY)
1 INLIST ITERATOR
2 TABLE ACCESS (BY INDEX ROWID) OF 'TC_xx_TRADE' (Co
st=541550 Card=1462696 Bytes=450510368)
3 INDEX (RANGE SCAN) OF 'IDX_TC_REF_TRA_GMT_XIAOER' (N
ON-UNIQUE) (Cost=4475 Card=1462696)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
898 consistent gets
0 physical reads
0 redo size
15366 bytes sent via SQL*Net to client
689 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49 rows processed
把<>转化成in字段,效果好了很多,原理我就不多解释了,实际上和用<>不走索引,会去扫描整个表是一样的道理。
--EOF--
- sql查询中尽量减少的使用
- 执行sql语句中 为什么应该尽量减少“SELECT *”这类语句的使用
- 尽量减少union all 的使用
- 使用asp尽量减少服务器端的工作量
- 尽量减少使用~/
- 关于Sql中尽量避免用的查询语句(in....
- 个人经验:使用asp尽量减少服务器端的工作量(转贴)
- sql语句中尽量不要使用复杂的函数运算
- 在动态方法中如何尽量减少定义的 LocalBuilder 数量 (使用.net 中的动态方法编程备忘录8)
- 用来减少SQL的查询类库
- 《JVM垃圾回收相关内容》开发中,如何尽量减少GC开销,减轻垃圾回收的负担
- 设计模式——使用模板方法模式尽量减少重复相似的代码段
- 尽量避免在SQL语句的WHERE子句中使用函数
- Oracle 尽量避免在 SQL语句的WHERE子句中使用函数
- 尽量避免在SQL语句中使用OR
- 尽量避免在SQL语句中使用LIKE
- Oracle 尽量避免在SQL语句中使用 OR
- Oracle 尽量避免在 SQL语句中使用 LIKE
- Mysql Limit操作
- 神奇的双色球
- 数据库:反规范化
- 关于 MySql 的 SQL 语言
- 搜索引擎重复网页发现技术分析
- sql查询中尽量减少的使用
- Dreamweaver CS3 Spry 试应用 与Spry框架
- 表单的验证 Validator
- 开博第一篇
- DataGridView回车换行
- java网络编程-Ajax+servlet实例
- AJAX+servlet实例入门
- AJAX在servlet中返回中文乱码问题的解决
- 如何用java和access连接