sqlserver数据统计一

 

        Csdn2010 优秀博文中有一篇数据库方面的文章很好（用csdn的站内搜索也没有找到，幸好这篇博文地址保持起来了）， http://blog.csdn.net/yzsind/archive/2010/12/06/6059209.aspx ，这几天会做一些数据库的操作，记录下操作步骤。以下操作只是在考虑怎么能尽快的把数据取出来，是特定环境下的。以下操作会涉及索引，标量子查询，分区几个方面。

    

数据库为之前系统的为 sqlserver 。此次操作涉及到表有 mobile （ 218 条数据， 9 列） ,service(149 条数据， 12 列 ) ， channel(42 条数据， 12 列 ) ， Spinfo(21 条数据， 8 列 ) ， service_log （ service ， mobile ， url ， access_date ， ip 共 5 字段，约 4 千万条数据，按月大概有 500 多万条数据）。

目标是需要统计 service_log ，按照月进行统计分析汇总。并且其他表中取出关联数据。导出了建表语句放在附件中。

    用 select count(*) from service_log 发现很慢，需要接近 20 分钟以上。那么任何类似的 sql:select …… from table1,table2…… where …… ，就不用写了。

    建立索引，对时间建立索引，建完有性能有了很大提升，差不多要 1 分钟。做关联之前毫无疑问，我们需要以时间段缩小数据量， select … from service_log where access_date between '2010-05-01' and '2010-05-30' .

    对于关联，首先试用了标量子查询，得到的结论是，标量子查询无法使性能得到改善（至少 sqlserver2005 是这样的）。这里将使用标量子查询和没有使用标量子查询的 2 段 sql 贴出来，不过使用标量子的 sql 可读性会好些。

 

没有使用标量子查询的sql： Select m.name,s.name,s.url,sp.name ,num.n from dbo.service s,dbo.service_log sl,(select count(*) as n ,sl.service as id from dbo.service_log sl where sl.access_date between '2011-02-01' and '2011-03-01' group by sl.mobile,sl.service) num, (select sp.id ,sp.name from dbo.spinfo sp) as sp, mobile m where sl.service=s.id and s.id = num.id and s.sp = sp.id and m.did=sl.mobile

 

使用标量子查询的sql： select (select name from dbo.mobile m where m.did=sl.mobile) as mobile, sl.service,s.name ,s.url, (select c.name from dbo.channel c where c.id = s.channel) as c, (select sp.name from dbo.spinfo sp where s.sp = sp.id) as sp, sl.num from dbo.service as s, (select sl.service ,sl.mobile as mobile,count(*) as num from dbo.service_log sl where sl.access_date between '2011-02-01' and '2011-03-01' group by sl.mobile,sl.service WITH CUBE) as sl where s.id = sl.service

 

    对于索引而言，分为聚集索引和非聚集索引。这 2 个差别，以字典按照拼音和偏旁来作解释比较好理解（详细见上面连接中的解释）。读了这个后，应该会对聚集索引和非聚集索引有一个了解 . 显然对于此处时间来讲，聚集索引和非聚集索引而言，该处建立聚集索引会使查询快些。 变为聚集索引后，可以发现上面的 sql 的查询，有 1 分钟变为 2 秒了。

   

   服务器上约 4000 万数据接近 2 秒，在本地数据库中有部分数据，大概有 1000 万条数据，用时不到一秒。更进一步的优化，可以用到表的分区，对于 sqlserver 表的分区操作。这有几篇篇博客写的很详细， http://blog.csdn.net/Sky_666/category/522973.aspx 。其中也讲到，对于已有数据库，可以对聚集索引进行表分区。

   

   同时可以结合缓存进行优化，之前做个一个项目进行过尝试。将所有基础表全部缓存，涉及到关联的操作，直接调用缓存的数据，不做表关联。大部分数据库操作只是针对一个表，这样所带来的是代码量会增大，可以借助ehcache 来处理这方面的工作。不过实际运用之中还是要依据情况来选择处理方式，这种几千万条数据的操作，直接用sql来操作就可以了。