Chinaunix首页 | 论坛 | 博客
  • 博客访问: 834370
  • 博文数量: 167
  • 博客积分: 7173
  • 博客等级: 少将
  • 技术积分: 1671
  • 用 户 组: 普通用户
  • 注册时间: 2009-08-04 23:07
文章分类

全部博文(167)

文章存档

2018年(1)

2017年(11)

2012年(2)

2011年(27)

2010年(88)

2009年(38)

分类: 数据库开发技术

2009-11-29 16:17:22

今天在itput上看了一篇文章,是讨论一个语句的优化:

     原贴地址: http://www.itpub.net/viewthread.php?tid=1015964&extra=&page=1

  一,发现问题

   优化的语句:     

请问以下语句如何优化:
CREATE TABLE aa_001
   (    ip 
VARCHAR2(28), 
        name 
VARCHAR2(10), 
        password 
VARCHAR2(30)           )

select * from aa_001 where ip in (1,2,3order by name desc;
--目前表中记录有一千多万条左右,而且in中的值个数是不确定的。

  以上就是优化的需要优化的语句和情况。

 

   不少人在后面跟帖:有的说没办法优化,有的说将IN该为EXISTS,有的说在ip上建立索引复合索引(ip,name)等等。

  二,提出问题

     那这样的情况,能优化吗,如何优化?今天就来讨论这个问题。

  三,分析问题

        1,数据量1千万多条。

        2,in中的值个数是不确定

     3.1 分析数据分布

      这里作者没有提到ip列的数据的分布情况,目前ip列的数据分布可能有以下几种:

         1,ip列(数据唯一,或者数据重复的概率很小)

         2,ip列 (数据不均匀,可能有些数据重复多,有些重复少)

         3,ip列 (数据分布比较均匀,数据大量重复,主要就是一些同样的数据(可能只有上万级别不同的ip数据等)

 

     解决问题:

         1,对于第一种数据分布情况,只要在ip列建立一个索引即可。这时不管表有多少行, in个数是不确定的情况下,都很快。

         2,对应第二中数据分布情况,在ip列建立索引,效果不好。因为数据分布不均匀,可能有些快,有些慢

         3,对应第三种数据分布情况,在ip列建立索引,速度肯定慢。

        注意:这里的 order by name desc 是在取出数据后再排序的。而不是取数据前排序 

 

     对于2,3两个情况,因为都是可能需要取出大量的数据,优化器就采用表扫描(table scan),而不是索引查找(index seek) ,速度很慢,因为这时表扫描效率要优于索引查找,特别是高并发情况下,效率很低。

 

    那对应2,3中情况,如何处理。是将in改成exists。其实在sql server 2005和oracle里的优化器在in后面数据少时,效率是一样的。这时采用一般的索引效率很低。这时如果在ip列上建立聚集索引,效率会比较高。我们在SQL server 2005中做个测试。

 

   表:[dbo].[[zping.com]]]中有约200万条数据。包含列Userid, id, Ruleid等列。按照上面的情况查询一下类似语句: 

select  * from [dbo].[[zping.com]]] where 
userid 
in ('402881410ca47925010cb329c7670ffb','402881ba0d5dc94e010d5dced05a0008'
,
'4028814111a735e90111a77fa8e30384'order by Ruleid desc

   我们先看userid的数据分布情况,执行下面语句:

select userid,count(*from [dbo].[[zping.com]]] group by userid order by 2

   这时我们看看数据分布:总共有379条数据,数据两从1到15万都有,数据分布倾斜严重。下图是其中一部分。

 

 

   这时如果在ip上建立非聚集索引,效率很低,而且就是强行索引扫描,效率也很低,会发现IO次数比表扫描还高。这时只能在ip上建立聚集索引。这时看看结果。

  这时发现,搜索采用了(clustered index seek)聚集搜索扫描。

  在看看查询返回的结果: 

(156603 行受影响)
表 
'[zping.com]'。扫描计数 8,逻辑读取 5877 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 
'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

    返回15万行,才不到6千次IO。效率较高,因为这15万行要排序,查询成本里排序占了51%。当然可以建立(userid,Ruleid)复合聚集索引,提高性能,但这样做DML维护成本较高。建议不采用。

 

   从上面的测试例子可以看出, 优化的解决办法:

     数据分布为1:建立ip索引即可

     数据分布为2,3:在ip列建立聚集索引。

阅读(1175) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~