数以亿计的数据记录优化查询(转)

4亿多条数据不可怕，问题是你的数据量是多少，如果是OLTP的话，一天百万条数据，1个月也能到这个数据的，而且听你说只跑报表的吗

呵呵，80%的问题在SQL上，好好优化一下报表的SQL。<br /><br />分析数据库应用的特点，将那些重要的表、系统过程列出来，重点分析。

1、数据量大的表

2、运行频繁的系统过程、触发器；用 sp_depends 数据量大的表查找

3、用户容易报告慢的界面相关的系统过程。

由于系统过程的 TXT 文本大约有2M,一个个看是不可能的，因此需要做这些工作。

首先，有一句话要认识 ： 80%的性能问题由SQL语句引起。 经过看 SYBASE 的书，结合从 MSSQL 迁移过来的系统过程 ，发现以下几个问题比较重要：

经验一、where 条件左边最好不要使用函数，比如 select ... where datediff(day,date1,getdate())  这样即使在 date1 列上建立了索引，也可能不会使用索引，而使用表扫描。 这样的语句要重新规划设计，保证不使用函数也能够实现。通过修改，一个系统过程的运行效率提高大约100倍！

经验二、两个比较字段最好使用相同数据类型，而不是兼容数据类型。比如 int 与 numeric（感觉一般不是太明显）

经验三、复合索引的非前导列做条件时，基本没有起到索引的作用。

比如 create index idx_tablename_ab on tablename(a, b)  update tablename set c = XX where b= XXX and ... 在这个语句中，基本上索引没有发挥作用。 导致表扫描引起blocking 甚至运行十几分钟后报告失败。 一定要认真检查 改正措施： 在接口中附加条件 update tablename set c = XX where a = XXX and b= XXX  或者建立索引类似于create index idx_tablename_ba on tablename(b,a)

经验四、 多个大表的关联查询，如果性能不好，并且其中一个大表中取的数据比较少，可以考虑将查询分两步执行。先将一个大表中的少部分数据 select * into #1 from largetable1 where ... 然后再用 #1 去做关联，效果可能会好不少。（前提：生成 #1表应该使用比较好的索引，速度比较快） 

经验五、 tempdb 的使用。

最好多用 select into ，这样不记日志 ，尤其是有大量数据的报表时。虽然写起来麻烦，但值得。 create table #tmp1 (......)这样写性能不好。尤其是大量使用时，容易发生tempdb 争用。

经验六、 系统级别的参数设置 

一定要估计一下，不要使用太多，占用资源 ，太少，发生性能问题。 连接数，索引打开个数、锁个数 等、 当然 ，内存配置不要有明显的问题，比如，procedure cache  不够 （一般缺省20%，如果觉得太多，可以减少一些）。如果做报表经常使用大数据量读，可以考虑使用  16Kdata cache

经验七、索引的建立。很重要。

clustered index /nonclustered index 的差异，自己要搞清楚。各适用场合，另外如果 clustered index 不允许 重复数，也一定要说明。 索引设计是以为数据访问快速为原则的，不能 完全参照数据逻辑设计的，逻辑设计时的一些东西，可能对物理访问不起作用

经验八、统计数据的更新：大约10天进行 update statistics ,sp_recompile table_name

经验九、强制索引使用

如果怀疑有表访问时不是使用索引，而且这些条件字段上建立了合适的索引,可以强制使用  select * from tableA (index idx_name) where ... 这个对一些报表程序可能比较有用。

经验十、找一个好的监视工具

工欲善其事，比先利其器，一点都不错呀。我用 DBartisian 5.4 ,监视哪些表被锁定时间长， blocking 等还有 sp_object_status 20:00:00 ， sp_sysmon 20:00:00 等以上是我的一点经验，在不到一个月的时间内，我修改了20个左右的语句和系统过程 ，系统性能明显改善，cpu利用 高峰时大约50% 平时 不到30%IO 明显改善。所有月报表能顺利完成 5min 以内。 另外，系统中确认不使用的中间数据，可以进行转移。这些要看系统的情况哦 最后祝你好运气。 以上为个人经验，欢迎批评指正！

呵呵 写完后忘记一个 一定要注意热点表 ，这是影响并发问题的一个潜在因素。

解决方法： 行锁模式 如果表的行比较小，可以故意增加一些不用的字段，比如 char(200) 让一页中存放的行不要太多。