实例说明sql优化的重要性

原创 2007年09月22日 18:16:00
 

HTML Tags and JavaScript tutorial



实例说明sql优化的重要性





我看原文以后的感想:1.不要使数据表数据过大2.建立适当的查询索引.
原文地址:
http://blog.csdn.net/lunar2000/archive/2006/04/06/652263.aspx
原文如下:
接到报告说,某省数据库CPU长时间负载很高,很多时候还经常是cpu idle值为各位数,甚至经常为<5 的各位数,
于是赶紧登陆检查,发现主要的瓶颈是WAIT IO,初步判断是和业务中的SQL语句有关的问题造成的。
HP-UX db01 B.11.11 U 9000/800    02/14/06
09:40:17    %usr    %sys    %wio   %idle
09:40:19      13       3      72      11
09:40:21      10       2      80       8
09:40:23       9       3      71      16
09:40:25      11       3      71      16
09:40:27      12       3      74      12
Average       11       3      74      13
依据STATSPACK和一些维护经验,作出了如下调整策略:
1, 一个业务历史大表,已经将近40G多,有史以来的历史数据全部在线。
  修改策略,将改表重建为时间分区表,按月份存放,并建立相应的local index
 注:
  由于日常对于这个表的操作都是insert,只有在授理投诉的时候会这个表来查找历史数据,
 因此这个表的影响几乎从未在STATSAPCK中体现过。
 影响:这个调整使得wio降低了25%左右
 
2, 根据statspack报告:
                                                     CPU      Elapsd
 Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
      2,996,669           86       34,845.0   26.6   168.34   1363.19 1482568540
Module: JDBC Thin Client
select count(*) from operation_log where 0=0 and OPR_TIME>=to_da
te('2006-03-14','yyyy-mm-dd') and OPR_TIME<=to_date('2006-03-14'
,'yyyy-mm-dd')+1
可以看到这个语句的执行计划:
-----------------------------------------------------------------------
| Id  | Operation            |  Name          | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                |       |       |       |
|   1 |  SORT AGGREGATE      |                |       |       |       |
|*  2 |   TABLE ACCESS FULL  | OPERATION_LOG  |       |       |       |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OPERATION_LOG"."OPR_TIME"<=TO_DATE('2006-03-15
              00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "OPERATION_LOG"."OPR_TIME">=TO_DATE('2006-03-14 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss'))
俨然一个权标扫描,还是没有RBO的!!
检查发现这个表 322M,2074560行数据,只有一个主键索引:
INDEX_NAME             
------------------------
PK_OPERATION_LOG       
于是给它添加基于OPR_TIME字段的索引。
SQL> select COLUMN_NAME from dba_ind_columns where table_name = 'OPERATION_LOG'
  2  and index_name='IDX_OPERLOG_TIME';
COLUMN_NAME
--------------------------------------------------------------------------------
OPR_TIME
SQL>
修改后的执行计划:
--------------------------------------------------------------------------
| Id  | Operation            |  Name             | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |     1 |     7 |    37 |
|   1 |  SORT AGGREGATE      |                   |     1 |     7 |       |
|*  2 |   INDEX RANGE SCAN   | IDX_OPERLOG_TIME  | 13038 | 91266 |    37 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OPERATION_LOG"."OPR_TIME">=TO_DATE('2006-03-14 00:00:00',
              'yyyy-mm-dd hh24:mi:ss') AND "OPERATION_LOG"."OPR_TIME"<=TO_DATE('2006-03-
              15 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
现在执行一次这个语句的时间:
SQL> l
  1  SELECT COUNT (*)
  2    FROM OPERATION_LOG
  3   WHERE 0 = 0
  4     AND opr_time >= TO_DATE ('2006-03-14', 'yyyy-mm-dd')
  5*    AND opr_time <= TO_DATE ('2006-03-14', 'yyyy-mm-dd') + 1
SQL> /
  COUNT(*)
----------
     11617
Elapsed: 00:00:00.01
SQL>
 
3, 根据statspack报告,发现BOSSCHARGEINFO 表有1995188行,
                                                     CPU      Elapsd
 Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
        916,933           30       30,564.4   26.7   102.74    654.58 2412605967
Module: XXXXXXX (TNS V1-V3)
select MID ,SERVID ,ACCESSMODEID ,SERVICEGRADEID ,STATUS ,FEEMON
TH ,to_char(chargedate,'YYYYMMDDHH24MISS')  from BOSSCHARGEINFO
where (status=4 and feemonth=:b0)
问题的现象和处理方法通问题2一样,这里是添加基于status和feemonth的联合索引。
修改后,该语句效率提高了一倍多(请注意改语句基本上每分钟执行一次,因此,总体效率的提高更为显著)。
 
4,
                                                     CPU      Elapsd
 Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
        427,455           20       21,372.8   12.5    50.18    215.02 1608726146
Module: XXXXXXX (TNS V1-V3)
SELECT  MID,SERVID,ACCESSMODEID, SERVICEGRADEID,STATUS,FEEMONTH,
to_char(chargedate,'YYYYMMDDHH24MISS')           FROM DELAYCHARG
EINFO              WHERE  status = 4              and feemonth =
 '200603'
问题的现象和处理方法通问题3一样,这里是添加基于status和feemonth的联合索引。
修改后,该语句效率提高了一倍多(请注意改语句基本上每分钟执行一次,因此,总体效率的提高更为显著)。
调整后的状态:
HP-UX db01 B.11.11 U 9000/800    04/05/06
 
15:45:16    %usr    %sys    %wio   %idle
15:45:18      25       3      30      42
15:45:20      21       4      35      40
15:45:22      24       5      24      47
15:45:24      23       5      27      46
15:45:26      23       3      31      43
 
Average       23       4      30      44
 


sql优化经典例子

场景 我用的数据库是mysql5.6,下面简单的介绍下场景 课程表 create table Course( c_id int PRIMARY KEY, name varchar...
  • fangqun663775
  • fangqun663775
  • 2017年05月16日 16:37
  • 812

SQL优化:索引的重要性

开篇小测验   下面这样一个小SQL 你该怎么样添加最优索引   两个表上现在只有聚集索引  bigproduct 表上已经有聚集索引 ProductID     bigtrans...
  • 3150379
  • 3150379
  • 2017年02月16日 21:40
  • 162

实例说明optimize table在优化mysql时重要性——数据库优化

今天在看CU的时候,发现有人问有关optimize来表优化的问题,当年因为这个问题,困扰我很长一段时间,今天有空我把这个问题,用实际数据来展示出来,让大家可以亲眼来看看,optimize table的...
  • u012768459
  • u012768459
  • 2017年07月05日 17:14
  • 183

优化代码的重要性

本文是在学习中的总结,欢迎转载但请注明出处:http://blog.csdn.net/pistolove/article/details/45131085 工作将近一年...
  • pistolove
  • pistolove
  • 2015年04月19日 17:55
  • 6184

sql server 2008亿万数据性能优化实例

最近在开发站长帮手网(www.links.cn)的百度权重查询工具,数据已达亿万级别,主表为关键词主表(包含百度指数,百度收录等字段),字表为网站排名表(1-100)的排名。根据设计惯例,查询的时候主...
  • caoshangfei
  • caoshangfei
  • 2013年04月05日 14:40
  • 2931

SQL语句常见优化十大案例

1、慢SQL消耗了70%~90%的数据库CPU资源; 2、SQL语句独立于程序设计逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低; 3、SQL语句可以有不同的写法; ...
  • z719725611
  • z719725611
  • 2016年10月25日 15:15
  • 3208

第十七章——配置SQLServer(4)——优化SQLServer实例的配置

前言:Sp_configure 可以用于管理和优化SQLServer资源,而且绝大部分配置都可以使用SQLServer ManagementStudio的图形化界面实现。 准备工作:为了查看SQLSe...
  • DBA_Huangzj
  • DBA_Huangzj
  • 2013年04月04日 01:55
  • 5910

性能优化-数据库

数据库优化涉及的内容比较多,并且它是一个长期的过程! 同样本篇和其他一样,不讲类似数据库三范式这样的东西,因为我认为这不算优化的范围!直接进入正文吧。 索引 ...
  • Aric_Chen
  • Aric_Chen
  • 2015年05月02日 09:30
  • 1840

SQL优化基础 使用索引(一个小例子)

按照本文操作和体会,会对sql优化有个基本最简单的了解,其他深入还需要更多资料和实践的学习:  1. 建表:  复制代码代码如下: create table site_user  ...
  • ycl295644
  • ycl295644
  • 2015年08月12日 10:58
  • 2762

数据库性能优化一:SQL索引一步到位

SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。   1.1 什么是索引?   SQL索引有两种,...
  • guochunyang
  • guochunyang
  • 2015年11月10日 12:35
  • 2322
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:实例说明sql优化的重要性
举报原因:
原因补充:

(最多只允许输入30个字)