MySQL/Rails的性能:一个表，很多行与许多表，更少的行? [英] MySQL / Rails Performance: One table, many rows vs. many tables, less rows?

问题描述

在我的Rails应用程序中，我有几种处理资产(附件，图片，徽标等)的模型.我正在使用attachment_fu，到目前为止，我有3个不同的表将信息存储在我的MySQL数据库中.

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.

我想知道如果使用STI并将所有信息放入一个表中(使用类型列并具有不同的继承类)，是否会对性能产生影响?它将更加干燥并且易于维护，因为它们都具有许多属性和特征.

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.

感谢您的意见！

推荐答案

很多表和更少的行可能更快.

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.

仅在问题域中所有事物具有完全相同的属性和语义(或可能由Liskov替代)的情况下，才执行 .

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.

否则，甚至不要尝试这样做.

或者，如果您愿意，请问为什么这比拥有一个大的Map/hash表/关联数组更好地容纳应用程序中的所有实体(还有很多功能，其中大部分都被复制，剪切和粘贴以及日期，请进行switch案例或RTTI以确定每个实体的真实类型.

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 switch cases or RTTI to figure out the real type of each entity).

这篇关于MySQL/Rails的性能:一个表，很多行与许多表，更少的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！