糟糕的数据库设计 - 我的表太大了吗？
我的数据库设计很差。 最重要的表之一包含 11,000 多个条目。 我们想要扩展我们的系统，我想知道如果这个表增长到其大小的 5 倍，这会是一个问题吗？ 它的大小为 15360 kB...如果这很重要的话。
我使用的是 phpMyAdmin，服务器是 Fedora Linux 盒子（没什么花哨的），负载很轻。 它存储了我们系统使用的几乎所有内容。
I have a poorly designed database. One of the most important tables has 11,000+ entries. We would like to expand our system, and I am wondering if this table grew to 5x its size, would this be a problem? It's 15360 kB in size... if that matters.
I'm using phpMyAdmin, the server is a Fedora Linux box (nothing fancy), The load is light. It stores just about everything our system uses.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（14）
“设计不良的数据库”是什么意思？
如果设计不好，请重新设计，将信息拖出当前表，然后填充新表。
如果您关心性能，11000 个条目并不算大。 按照数据库标准，15 MB 的数据库非常小。
What do you mean by a "poorly designed database"?
If it's badly designed, redesign it, drag the information out fo the current tables, and populate the new one.
If you're concerned about performance, 11000 entries is not big. A 15 megabyte database is extremely small, by db standards.
桌子的大小并不是很重要。 键、索引和关系的设计与设计质量的关系远大于设计中包含的数据的大小。 对此显然有一些警告； 但是优化表的大小几乎是我在处理性能或设计问题时所做的最后一件事。
您可能想要更多地解释为什么您认为这是一个设计不佳的数据库以及您可以（轻松）执行哪些操作来纠正问题。 除此之外，您还应该详细说明 DBMS 的类型以及用途（Web 应用程序、自定义应用程序、报告等）。
The size of a table isn't very important. The design of the key, indexes, and relationships have much much more to do with the quality of the design than does the size of the data contained in it. There are obviously caveats to this; but optimizing the size of a table is near the last thing I do when working on a performance or design problem.
You may want to explain more about why you think this is a poorly designed database and what things you can (easily) do to correct the issues. Along with that you should detail the type of DBMS and what the usage is (web app, custom app, reporting, etc).
15MB不算什么。 还有 11k 行。 我的数据库包含 2+ GB 的数据，其中一些表包含超过 100 万行，我认为该数据库的大小介于小型和中型之间。
15MB is nothing. 11k rows as well. I have databases with 2+ GB of data, with some tables containing over 1 million rows and I consider that being somewhere between small and medium size.
您确实没有提供任何证据来支持您的说法，即这是一个设计不当的数据库。 是什么导致它设计不佳？ 该表有 876 列吗？ 这些列是否名为 Col1、Col2、Col3...？ 它是否使用浮点数和日期时间作为复合主键？ 标准化程度很差？ 我们唯一知道的是它的记录数。
You really didn't give any evidence to support your claim that this is a poorly designed database. What makes it poorly designed? does the table have 876 columns? Are the columns named Col1, Col2, Col3...? Does it use a float and a datetime as a composite primary key? It is poorly normalized? The only thing we know about is it's record count.
对于数据库而言，11K 条记录通常不算什么。
除了一张表中的记录数量之外，还有什么让您认为数据库设计不佳？
11K records is usually nothing in database terms.
What else makes you think the database is poorly designed apart from the number of records in the one table?
您需要提供有关表结构的更多信息。
一般来说，表中的 15,000 行会被认为很小，实际上很小，以至于一些设计人员甚至可能不会为其建立索引。
You need to give a lot more information about the table structure.
In general, 15,000 rows in a table would be considered small, in fact so small, that some designers might not even bother with indexing it.
基础不好将是你最昂贵的错误。 如果该表很重要，那么您需要确定修复它的重要性。 表中的行数只会影响从中提取内容的速度。 但是，如果您一开始就有一个糟糕的数据库设计，那么您的双手将会在未来的某些地方受到束缚。
A bad foundation will be your most costly mistake. If the table is important, then you need to decide how important it is to fix it. The amount of rows in a table only affects the speed at which you can pull stuff out of it. But, if you've got a bad database design to start with, your hands will be tied at certain places down the road.
如果您谈论的是 SQL Server 2005，请查看 SQL Server Profiler 并使用索引调整向导。
如果您需要额外的性能，某些数据库还支持将表固定到内存。
If you are talking about SQL Server 2005, look into SQL Server profiler and use Index Tuning wizard.
There is also support in some databases for table pinning to memory if you want additional performance.
记录的组成部分比表中有多少记录更重要。
在我工作的地方，我们拥有包含大量表的数据库，这些表的记录数达到数万或数十万。 在大多数情况下，我们的数据库被认为很小。
What a record is comprised of can matter more than how many records are in a table.
Where I work we have databases with numerous tables having record counts in the tens of thousands or hundreds of thousands. Our databases are considered small, for the most part.
如果您要扩展系统，那么现在就需要重新设计。 当您拥有 11,000 条记录时，重新设计的痛苦要比拥有 1000 万条记录时要轻松得多。 然而，你所说的一切都没有表明你需要重新设计。 拥有连接本身并没有什么问题（事实上，设计良好的数据库应该拥有连接）。 发布有关结构的一些详细信息，我们可以帮助您决定是否需要重新设计。
问题可能在于您和您的同事根本没有数据库访问经验，并且不知道如何有效且轻松地查询它们。 或者问题可能是设计不好，没有结构细节就很难说。
If you are going to expand your system, now is the time to redesign if need be. It is much less painful to redesign when you have 11,000 records than when you have 10 million. However, nothing you have said indicates to me that you need to redesign. There is nothing inherently wrong with having joins (in fact a well designed database should have them). Post some details about the struture and we can help you decide if redesign is needed.
It is possible that the problem is that you and your colleagues are simply not experienced at database access and do not know how to effectively and easily query them. Or the problem could be that the design is bad, without details of the structure, it is hard to say.
什么数据库管理系统？ 什么服务器？ 什么负载？ 什么应用？
此外：11.000 条记录真的不算什么。 即使在 MS Access 中也是如此。 ：-）
编辑：
所以我假设您使用带有 MyISAM 表的相当新的 MySQL。 理论上，您可以继续将表填充到数百万条记录中。 根据您使用它们的方式（是否有大量连接，是否有大量查询/更新/删除/是否），您不需要做任何特殊的事情。 在桌子上放一个适当的索引就可以了。
What DBMS? What server? What load? What application?
Besides: 11.000 records are nothing, really. Even in MS Access. :-)
EDIT:
So I assume you use a fairly recent MySQL with MyISAM tables. In theory, you can go ahead and fill the table into the millions of records. Depending on how you work with them (lots of joins / or not, lots of queries/updates/deletes / or not), you don't need to do anything special. Put a proper index on the table and you should be fine.
我理解您担心将“设计不良的数据库”中的记录数增加到 55.000 左右是否会影响性能。
如果您的系统现在按预期工作，我认为您应该可以处理 50.000 条记录，除非您已经遇到了一些轻微的性能问题。
正如大多数人提到的，50k 记录相对于数据库表大小来说是一个非常小的数字，即使使用未规范化的数据库，也不应该出现性能问题。
如果您计划扩展系统的功能，那么也许也是查看数据库设计的好时机，否则保持原样应该是相当安全的。
I understand that you are worried if increasing the number of records to around 55.000 in"your poorly designed database" will affect performance.
If you system works as expected now I think you should be fine with 50.000 records as well unless you have some slight performance issues already.
As most people mentioned 50k records is a very small number in relation to database table size and even with not normalized database there should be no performance issues.
If you are planning to expand the functionality of you system then perhaps it would be a good time to look at the database design as well, otherwise it should be reasonably safe to leave it as it is.
我认为您没有提供足够的信息供某人给出答案。 为什么设计不好？ 不是标准化了吗？ 你没有任何索引吗？ 它是什么数据库？ 它运行在什么操作系统上？ 现在从相关表中查询一条记录需要多长时间？
I don't think you're providing enough information for someone to give an answer. Why is it poorly designed? Is it not normalized? Do you not have any indexes? What DB is it? What OS is it running on? How long does it take now to query a record from the table in question?
11k 条目并不算多。 50K也不算大。
在表上放置索引（针对您将在其上运行的查询进行优化）将在比您预想的数量大得多的情况下提供良好的性能。
如果设计足够糟糕，您可能会考虑重新设计的成本。
11k entries is not that many. 50K is not large either.
Putting indexes on the table (optimized for the queries you will run on it) will allow good performance for much larger amounts than what you are envisioning.
If the design is poor enough though, you might look into the cost of redesigning.