数据库设计:一张大表还是单独的表? [英] Database design: one huge table or separate tables?

问题描述

目前我正在设计一个供我们公司使用的数据库.我们使用的是 SQL Server 2008.该数据库将保存从多个客户那里收集的数据.该数据库的目标是获取多个客户的汇总基准数据.

Currently I am designing a database for use in our company. We are using SQL Server 2008. The database will hold data gathered from several customers. The goal of the database is to acquire aggregate benchmark numbers over several customers.

最近，我特别担心一张桌子会变得非常大.每个客户大约有 20.000.000 行数据，数据库中很快就会有 30 个客户(如果不是更多的话).很多查询都会在这个表上完成.我已经注意到性能问题和用户被暂时锁定.

Recently, I have become worried with the fact that one table in particular will be getting very big. Each customer has approximately 20.000.000 rows of data, and there will soon be 30 customers in the database (if not more). A lot of queries will be done on this table. I am already noticing performance issues and users being temporarily locked out.

我的问题是，我们将来是否能够处理这张桌子，还是将这张桌子拆分为每个客户的小桌子更好?

My question, will we be able to handle this table in the future, or is it better to split this table up into smaller tables for each customer?

更新:距离我们第一次创建表到现在已经大约半年了.按照下面的建议，我创建了一些大表.从那时起，我一直试验索引 并决定在前两列(医院代码和部门代码)上使用聚集索引，如果我们有企业版，我们将在这些列上对表进行分区.正如 Galwegian 预测的那样，这种设置直到最近才运行良好，性能问题不断涌现.重建索引需要很长时间，用户相互锁定，查询的时间通常比他们应该的要长，对于大多数查询，首先将数据的相关部分复制到临时表中，在临时表上创建索引并运行询问.这不是它应该的样子.因此，我们正在考虑购买企业版以使用分区表.如果购买无法通过我打算使用 在标准版中完成分区的解决方法.

Update: It has now been about half a year since we first created the tables. Following the advices below, I created a handful of huge tables. Since then, I have been experimenting with indexes and decided on a clustered index on the first two columns (Hospital code and Department code) on which we would have partitioned the table had we had Enterprise Edition. This setup worked fine until recently, as Galwegian predicted, performance issues are springing up. Rebuilding an index takes ages, users lock each other out, queries frequently take longer than they should, and for most queries it pays off to first copy the relevant part of the data into a temp table, create indices on the temp table and run the query. This is not how it should be. Therefore, we are considering to buy Enterprise Edition for use of partitioned tables. If the purchase cannot go through I plan to use a workaround to accomplish partitioning in Standard Edition.

推荐答案

从一张大表开始，然后在适当的情况下应用 2008 年的表分区功能，如果性能成为问题.

Start out with one large table, and then apply 2008's table partitioning capabilities where appropriate, if performance becomes an issue.

这篇关于数据库设计:一张大表还是单独的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！