数据库设计：一个巨大的桌子还是分开的桌子？ [英] Database design: one huge table or separate tables?
问题描述
最近，我一直担心一个表格会变得非常大。每个客户都有大约20000000行数据，数据库中即将有30个客户（如果不是更多）。在这个表上会做很多的查询。我已经注意到性能问题，用户被暂时锁定。
我的问题是，我们将来可以处理这个表，还是更好地分割为每个客户分列成较小的表格？
更新：现在已经有一半自从我们第一次创建表以来的一年。按照下面的建议，我创造了一小堆巨大的桌子。从那时起，我已经尝试索引，并决定了在我们有企业版的前两列（医院代码和部门代码）上我们将分区表的聚簇索引。这个设置很好，直到最近，正如Galwegian预测的，性能问题正在出现。重建索引需要很长时间，用户互相锁定，查询往往需要更长的时间，对于大多数查询来说，首先将相关部分数据复制到临时表中，在临时表中创建索引并运行查询。这不是应该如何。因此，我们正在考虑购买使用分区表的企业版。如果购买无法通过，我打算使用 从一个大表开始，然后应用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. 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? 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. Start out with one large table, and then apply 2008's table partitioning capabilities where appropriate, if performance becomes an issue. 这篇关于数据库设计：一个巨大的桌子还是分开的桌子？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
