ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一次SQL优化的全过程

一次SQL优化的全过程

原创 Linux操作系统 作者:caleble 时间:2009-06-05 16:36:54 0 删除 编辑

最近进行了一个SQL优化,使100w数据半个小时到一个小时跑不出来到后来50s,成果比较显著。以下是过程

业务逻辑如下:

 

TB表有若干列,下面是例子

a      b

1     1

1     2

2     1

2     2

1     3

3     5

3     1

4     1

 

1,  tb表中找出a字段存在重复的记录,按照a进行分组。

a的结果集应该为 1,2,3

2,  a的结果集中每次抽取一条记录,到tb中找到所有相关记录(不止ab两列)

a1的记录应该为1 11 21 3,其他类似

3,  2中找到的所有记录进行比较,做一个N * N-1的循环,符合条件的记录保存起来

筛选后的数据可能为1 11 3

4,  把所有记录筛选后显示到页面。

PS:数据由存储过程运算,页面只需要从临时表取得数据。

 

由于tb是一张大表在1000w以上,所以进行了按月分区。

 

第一步没什么疑问,对a和其他条件建立了个组合索引,很快就出来结果

 

第二步中,因为a的结果集可能很大,比如50w,而又要对这50w条记录做循环到tb中找到所有记录。已经对a字段建立了索引,通过索引访问所需记录,但是因为a的结果集太大,要执行数十万次查询,虽然走了索引,但速度还是非常慢。

 

第三步是个比较,虽然次数比较多,但是瓶颈不在这里。

 

优化过程

尝试1:因为业务逻辑的需要,可能1000w的数据,在第二部通过索引进行命中可能有300w符合条件的记录,所以考虑进行全表扫描,一次取出所有的记录,减少第二部中的查询次数。

结果:TB表包含blob字段,100w记录有6G,所以全表扫描比走索引还慢,失败。

 

尝试2:考虑到程序中需要的值只有4个,页面是分页显示,没有必要取得所有的数据然后再分页返回。决定建立组合索引,程序判断的值都从索引出,返回页面rowid,页面再根据rowid进行取得总数和详细信息。

于是建立了4列的组合索引,对该索引采用索引快速扫描的方式,所有数据从索引中取得,避免回表,临时表中只保存rowid

存储过程改了以后发现速度很快,以前5分钟的数据量现在只需要几秒钟。联系前台程序员更改程序,根据分好页的rowidTB表中去取得所需数据。

 

经过测试,100w的数据量只需要不到1分钟,相比原来一小时也跑不出来提高了很多!

 

要点:

1,  了解索引的原理

2,  分区索引

3,  和程序员充分的沟通

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14293419/viewspace-605273/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 数据库恢复方案
请登录后发表评论 登录
全部评论

注册时间:2009-03-12

  • 博文量
    42
  • 访问量
    81227