MySQL / Rails 性能：一张表多行 vs.多表少行？
在我的 Rails 应用程序中，我有几个处理资产（附件、图片、徽标等）的模型。 我正在使用 Attachment_fu，到目前为止，我有 3 个不同的表用于在 MySQL 数据库中存储信息。
我想知道如果我使用 STI 并将所有信息放入一张表中，使用类型列并具有不同的继承类，这是否会对性能产生影响。 它将更加 DRY 并且更易于维护，因为它们共享许多属性和特征。
但什么更快呢？ 许多表，每个表的行数较少，还是只有一个表有很多行？ 还是根本没有区别？ 我每秒必须处理大量信息和许多查询。
感谢您的意见！
In my Rails App I've several models dealing with assets (attachments, pictures, logos etc.). I'm using attachment_fu and so far I have 3 different tables for storing the information in my MySQL DB.
I'm wondering if it makes a difference in the performance if I used STI and put all the information in just 1 table, using a type column and having different, inherited classes. It would be more DRY and easier to maintain, because all share many attributes and characteristics.
But what's faster? Many tables and less rows per table or just one table with many rows? Or is there no difference at all? I'll have to deal with a lot of information and many queries per second.
Thanks for your opinion!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
许多表和更少的行可能会更快。
但这不是您应该这样做的原因：您的数据库应该对您的问题域进行建模。 一张表是许多实体类型的糟糕模型。 因此，您最终将编写大量代码来查找表示您当前关注的实体类型的表的子集。
常规的、可接受的、干净的数据库和前端客户端代码将无法工作，因为你的一张表就是所有东西，而且根本没有东西。
它更慢，更脆弱，会在您的应用程序中增加您的代码，并且会产生一个糟糕的模型。
仅如果所有事物在您的问题域中具有完全相同的属性并且相同（或可能是 Liskov 可替换）语义，则执行此操作。
否则，就不要尝试这样做。
或者如果你这样做了，问问为什么这比用一个大的 Map/哈希表/关联数组来保存应用程序中的所有实体更好（并且很多函数，其中大多数是重复的、剪切和粘贴的，并且已经过时，需要进行
switchcase 或 RTTI 来找出每个实体的真实类型）。Many tables and fewer rows is probably faster.
That's not why you should do it, though: your database ought to model your Problem Domain. One table is a poor model of many entity types. So you'll end up writing lots and lots of code to find the subset of that table that represents the entity type you're currently concerned with.
Regular, accepted, clean database and front-end client code won't work, because of your one-table-that-is-all-things-and-no-thing-at-all.
It's slower, more fragile, will multiply your code all over you app, and makes a poor model.
Do this only if all the things have exactly the same attributes and the same (or possibly Liskov substitutable) semantic meaning in your problem domain.
Otherwise, just don't even try to do this.
Or if you do, ask why this is any better than having one big Map/hash table/associative array to hold all entities in your app (and lots of functions, most of them duplicared, cut and paste, and out of date, doing
switchcases or RTTI to figure out the real type of each entity).唯一确定的方法是尝试这两种方法并衡量性能。
一般来说，这取决于您是否在这些表之间进行联接，如果是，则取决于表的索引方式。 一般来说，数据库连接的成本很高，这就是为什么数据库模式有时会被非规范化以提高性能。 除非您处理大量数据（即数百万条记录），否则通常不会发生这种情况。 您可能还没有这个问题，也许永远不会。
The only way to know for sure is to try both approaches and measure the performance.
In general terms, it depends if you're doing joins across those tables and if you are, how the tables are indexed. Generally speaking, database joins are expensive which is why database schemas are sometimes denormalized to improve performance. This doesn't usually happen until you're dealing with a serious amount of data though i.e. millions of records. You probably don't have that problem yet and maybe never will.
如果行具有相同的属性，那么，是的，一张表会更好，并且只有一行来指定数据类型，否则，使用不同的表来处理，这在性能、代码量甚至代码的可读性方面都更好。
If rows have same attributes then, yes, one table is very better, and just one row to specify type of data, otherwise, use differents tables to deal with, that better in performance, code amount and even in the lisibility of code aswell.