数据库设计的问题

Question

先说一下目前现状：
公司的系统用了一个数据库。
大部份表都是业务上的，还有一些人事信息表，和客户信息表。数据规模：
主表：1300W记录，近100个字段。
业务过程表，主要是出入库表，两个表，都是5000W左右的记录，这两个表都是6个整形字段和一个日期字段。
还有其它一些表，数据量在几十万到200W之间。每天有几个时间段业务比较集中，此时大概有300个客户端同时频繁的处理各种业务。基本需求和遇到的问题：
1，业务预警，也就是用到了很多sum,和count之类的统计。这一块开销很大，但不知道怎么优化或设计。除了索引外还有其它优化方法吗？2，查询统计，公司的财务经常要看各种报表，有的查询时还把日期跨度选得比较大，恨不得把整个数据库都翻一次。
这种需求该怎么做？3，高并发写，网上看了说是想办法读写分离，但是一般情况下，insert或update都是要先进行若干查询，再来写的，导致一些操作经常不可靠。比如写入一条记录前先查询记录是否存在，存在就修改，不存在就写入，这样的操作经常会出问题。以前并发量低的时候还好好的。数据服务器的配置，IBM，4CPU，128G内存。
运行的时候服务器的内存稳定在32G左右，cpu大多数时候都在10%以下，但就是不明白，为什么会出现那么多不可靠的操作

Answer 1

1.报表统计基本都是查询。索引是最好的优化方法。好的查询语句才能发挥最大的性能。至于怎么写比较好的查询语句。这个BAIDU一下，N多。
2.日期跨度大的这个问题。我建议你需要将一些历史数据归档。然后如果有可能，使用分区表。这样效率或许会高些。
3.并发方面。就是锁与事务这些东西。这个方面我实在是太菜了。就不说了。

Answer 2

呵呵，你的服务器配置很不多，特别是有128G内存。1.sum和count，是聚合运算，主要消耗的是cpu，而你的cpu的大多数都是在10%以下，那么从cpu资源上是没问题的。但如果你想加快速度，那么主要还是建立索引，因为建了索引，那么就能更快的找到数据，然后再进行sum和count等统计，另外，你的内存很大，那么你可以把sql server的max server memeroy内存设置大点，这样的话，很多数据都可以直接缓存在内存中，速度会更快。2.你说的查看各种报表，本质上来说就是个olap系统，也就是联机分析系统，那么要想从本质上解决，必须要建立一个数据仓库，把数据提前进行汇总，否则，像我所在的公司一样，最大的业务表有超过1.5亿条数据，如果查询时间段，放到最大，那么全表扫描的时候，cpu瞬间升到90%-100%，而响应的磁盘等待队列，上升到100还多，严重影响正常的业务。如果不建立数据仓库，那么可以考虑，建立一些日结表，把数据预先进行累计，以后的新的数据，往上累加，每年晚上运行定时作业，来做累计。3.高并发写，不是什么问题，也不是通过读写分离能解决的。读写分离是解决负载均衡，和高可用性的，技术方案，不是解决高并发写的。从你的问题中可以看出，你最大的问题在于，会导致一些问题，比如像你说的，先查有没有，有的话更新，不存在的就插入，这个必须要通过事务和隔离级别来实现。确实在操作时，会有这样的问题，比如你先select一下，发现没有，准备插入数据，但同时在另一会话中，他也发现没有，也插入数据，那么就会出现你说的这个问题。那么你可以通过这样来实现：
begin trandeclare @exists intselect @exists = count(*) from tb(updlock) where id = 100 if @exists <> 0
   update ...
else
   insert into
commit tran

Answer 3

另外，第3个问题，如果你用的是sql server 2008，可以用merge来实现，如果有的update，如果没有就insert，给个例子：
可以看这个：
http://blog.csdn.net/sqlserverdiscovery/article/details/12707157create table t_org(org_id int,
                   v1 varchar(20),
                   v2 varchar(30));                   
insert into t_org
select 1,'org1',''
union all
select 2,'org2','name2'
union all
select 3,'org3','name3'
union all
select 4,'org4','name4'
union all
select 5,'org5','name5'
create table t_store(org_id int,
                     v1 varchar(20),
                     v2 varchar(30));
insert into t_store
select 1,'org1',''
union all
select 2,'org2-t','name2-t'
union all
select 3,'org3-t','name3-t'
union all
select 4,'org4-t','name4-t'
union all
select 5,'org5-t','name5-t'
union all
select 6,'org6-t','name6-t'
union all
select 7,'org7-t','name7-t'--生成临时表
select * into #t_org from t_org
select * into #t_store from t_store
;with mm   --作为merge语句中using的内部派生表
as
(
     select m.org_id,
            m.v1,
            m.v2
     from #t_store m
     where m.org_id >1
) --注意：表 with(tablock)，另外通过top关键字只是处理3条记录
merge top (3) into #t_org with (tablock) as b  
using (
         select *
         from mm with (tablock)  --引用上面CTE公用表表达式产生的内部派生表
      ) m 
   on m.org_id = b.org_id      --为了区分是否需要修改，可以增加一个字段来区分，
                               --但是这个字段不应该作为关联条件，
                               --因为会导致接下来运行的merge分块语句把刚才目标表中update过的那条记录，
                               --重复插入目标表中，而是写在when的条件中
                                   
when matched and b.v1 <> m.v1 and isnumeric(m.org_id) = 1  --可以在这里写：区分字段过滤条件
     then update set v1 = m.v1,v2 = m.v2 when not matched by target  --目标表中没有
     then insert (org_id,v1,v2) values(m.org_id,m.v1,m.v2)  --不可通过values关键字一次添加多列
          
when not matched by source  --源表中没有
     then delete 

Answer 4

三个大表是有分区的，索引也加了，看了一下sql2008的性能监视器，一般高频操作的语句，都没有什么性能问题。关键是这个sum和count每个客户端都要执行，而且条件基本上都不一样，也不好缓存，我现在做的就是一小时统计一次，所以程序整点都会卡一下。用户经常抱怨这个结果不准。而且有些组员以为数据库优化后用什么条件查都快。
关键是查询导出的问题，有些用户总是把时间跨度选得很大，跟他们说把时间范围选小点，多查几次会快些，但是没人听。现在有两个难点：如果实时更新统计数据？如何处理用户大批量数据查询？说服他们或者想办法实现？

Answer 5

三个大表是有分区的，索引也加了，看了一下sql2008的性能监视器，一般高频操作的语句，都没有什么性能问题。关键是这个sum和count每个客户端都要执行，而且条件基本上都不一样，也不好缓存，我现在做的就是一小时统计一次，所以程序整点都会卡一下。用户经常抱怨这个结果不准。而且有些组员以为数据库优化后用什么条件查都快。
关键是查询导出的问题，有些用户总是把时间跨度选得很大，跟他们说把时间范围选小点，多查几次会快些，但是没人听。现在有两个难点：如果实时更新统计数据？如何处理用户大批量数据查询？说服他们或者想办法实现？1.比较简单的办法是，通过限制客户端，让他只能最多查询1个月的数据，这个要和用户说明，需要和他们沟通清楚。2.另外，这个和我原来的公司有点像，报表有开始时间和结束时间，有些用户要是选的时间跨度特别大，那么肯定是非常慢的，你要想快，那只能是建立一些汇总表，按照各个维度，比如时间、客户、产品、组织等来汇总，提前进行sum和count，这样查询的时候，直接去汇总表里查，然后根据过滤条件进行过滤，然后再次进行汇总。但是这个工作量非常大，你设计表结构，还需要满足各种查询条件，所以是比较复杂的。

Answer 6

三个大表是有分区的，索引也加了，看了一下sql2008的性能监视器，一般高频操作的语句，都没有什么性能问题。关键是这个sum和count每个客户端都要执行，而且条件基本上都不一样，也不好缓存，我现在做的就是一小时统计一次，所以程序整点都会卡一下。用户经常抱怨这个结果不准。而且有些组员以为数据库优化后用什么条件查都快。
关键是查询导出的问题，有些用户总是把时间跨度选得很大，跟他们说把时间范围选小点，多查几次会快些，但是没人听。现在有两个难点：如果实时更新统计数据？如何处理用户大批量数据查询？说服他们或者想办法实现？1.比较简单的办法是，通过限制客户端，让他只能最多查询1个月的数据，这个要和用户说明，需要和他们沟通清楚。2.另外，这个和我原来的公司有点像，报表有开始时间和结束时间，有些用户要是选的时间跨度特别大，那么肯定是非常慢的，你要想快，那只能是建立一些汇总表，按照各个维度，比如时间、客户、产品、组织等来汇总，提前进行sum和count，这样查询的时候，直接去汇总表里查，然后根据过滤条件进行过滤，然后再次进行汇总。但是这个工作量非常大，你设计表结构，还需要满足各种查询条件，所以是比较复杂的。

那请问一下你们现在是怎么做的？数据库前天出现在拒绝连接的情况，看了一下日志：
Timeout occurred while waiting for latch: class 'ACCESS_METHODS_DATASET_PARENT', id 0000000421BEA3E8, type 4, Task 0x00000003FCEF6088 : 55, waittime 900, flags 0x1a, owning task 0x00000006157C7DC8. Continuing to wait.
网上有解释说是并行计算造成的资源等待，不是很懂。

Answer 7

三个大表是有分区的，索引也加了，看了一下sql2008的性能监视器，一般高频操作的语句，都没有什么性能问题。关键是这个sum和count每个客户端都要执行，而且条件基本上都不一样，也不好缓存，我现在做的就是一小时统计一次，所以程序整点都会卡一下。用户经常抱怨这个结果不准。而且有些组员以为数据库优化后用什么条件查都快。
关键是查询导出的问题，有些用户总是把时间跨度选得很大，跟他们说把时间范围选小点，多查几次会快些，但是没人听。现在有两个难点：如果实时更新统计数据？如何处理用户大批量数据查询？说服他们或者想办法实现？1.比较简单的办法是，通过限制客户端，让他只能最多查询1个月的数据，这个要和用户说明，需要和他们沟通清楚。2.另外，这个和我原来的公司有点像，报表有开始时间和结束时间，有些用户要是选的时间跨度特别大，那么肯定是非常慢的，你要想快，那只能是建立一些汇总表，按照各个维度，比如时间、客户、产品、组织等来汇总，提前进行sum和count，这样查询的时候，直接去汇总表里查，然后根据过滤条件进行过滤，然后再次进行汇总。但是这个工作量非常大，你设计表结构，还需要满足各种查询条件，所以是比较复杂的。

那请问一下你们现在是怎么做的？数据库前天出现在拒绝连接的情况，看了一下日志：
Timeout occurred while waiting for latch: class 'ACCESS_METHODS_DATASET_PARENT', id 0000000421BEA3E8, type 4, Task 0x00000003FCEF6088 : 55, waittime 900, flags 0x1a, owning task 0x00000006157C7DC8. Continuing to wait.
网上有解释说是并行计算造成的资源等待，不是很懂。就是你的sql语句的执行计划是并行计划，也就是有多个线程，同时执行，比如有3个线程同时执行，那么肯定会有一部分资源是，这3个线程要同时访问的，这个时候为了数据的一致性，必须要实现线程间的同步，于是就用了latch，也就闩锁，通过闩锁来同步3个线程的访问操作。如果有一个线程由于没有操作完，而没有释放闩锁，而其他的2个线程有需要访问数据，也需要闩锁，越是就开始等待，等待时间长了，就会time out，就会报错

Answer 8

如果实时更新统计数据？如何处理用户大批量数据查询？
-->
建议: 1查询时在表名之后加(nolock),减少并发时阻塞.
      2查询的相关字段上应有索引.
      3若仍有问题,可考虑使用视图索引.说服他们或者想办法实现？
--> 可以在前端程序中限制时间范围的最大值,当用户选择的时间范围超过此值时,弹出提示框,不予查询.