MySQL JOIN滥用？它有多糟糕？ [英] MySQL JOIN Abuse? How bad can it get?

问题描述

我已经阅读了很多关于关系数据库在每个SELECT使用许多JOIN语句。但是，我一直在想，当滥用这种方法时，是否有任何性能问题。

例如，我们有一个 users 表。我通常添加最常用的数据，而不是做任何额外的JOIN。当我说最常用的数据，例如，将是用户名，显示图片和位置。

当显示任何用户交互时，这些数据总是需要的网站，例如：每个评论表JOIN为文章。而不是在用户& users_profiles 表以获取位置和显示，只需使用 users 表上的信息。 >

这是我的方法，但是我知道有很多优秀和有经验的程序员可以给我一个关于这件事的建议。

我的问题是：

我应该尝试保守的JOIN？或者我应该更多地使用它们？为什么？

长时间使用JOIN时，是否有任何性能问题？

注意：我必须澄清，我不是要避免JOINS。我只在需要的时候使用它们。在这个例子中，注释/文章作者，仅显示在用户个人资料页面上的额外个人资料信息...等。

解决方案

我对数据建模的建议是：

• 你应该喜欢可选（可空）列超过1：1连接 >。仍然存在1：1有意义的情况，通常围绕子类型。当涉及可空列时，对于偶然连接，人们往往更加吝啬;


• 除非真的（更多详情请见下文）;


• 加入聚合。这可能会有所不同，因此需要进行测试。请参见 Oracle vs MySQL vs SQL Server：聚合与连接的例子;


• 加入比N + 1个选择更好。例如，N + 1个选择是从数据库表中选择一个订单，然后发出单独的查询以获取该订单的所有订单项;


• 连接的可扩展性为通常只是一个问题，当你做批量选择。如果您选择单个行，然后将其连接到几个事件很少是这个问题（但有时是这样）;


• 外键应始终除非您处理的是一个小小的表格;

由AppDevelopers制作的数据库开发错误。

现在关于模型的直接性，让我举一个例子。假设您正在设计一个用于用户身份验证和授权的系统。过滤的解决方案可能如下所示：

• 别名（id，username，user_id）;


• User（id，...）;


• 电子邮件（id，user_id，电子邮件地址）;


• 登录


• 
  
• 登录角色（id，login_id，role_id）;


• 角色li> Role Privilege（id，role_id，privilege_id）;


• 特权（ID，名称）。

因此，您需要6个连接才能从输入的用户名获取实际权限。当然，这可能有一个实际的要求，但更常见的是，这种系统被放入，因为一些开发者的想法他们可能有一天需要它，即使每个用户只有一个别名，用户登录是1 ：1等。更简单的解决方案是：

• 用户（ID，用户名，电子邮件地址，用户类型）

，好吧，就是这样。也许如果你需要一个复杂的角色系统，但它也是很有可能，你不，如果你做它很容易插入（用户类型成为一个外键的用户类型或角色表）或通常可以直接映射老的新的。

这是复杂性的事情：它很容易添加，很难删除。通常它是一个对意想不到的复杂性的持续的警惕，这是足够糟糕，不去，并通过增加不必要的复杂性更糟糕。

I've been reading a lot about Relational Databases using many JOIN statements on every SELECT. However, I've been wondering if there's any performance problem on the long run when abusing this method.

For example, lets say we have a users table. I would usually add the "most used" data, instead of doing any extra JOINs. When I say the "most used" data, for instance, would be the username, display picture and location.

This data would always be needed when displaying any user interaction on the website, example: on every comments table JOIN for articles. Instead of doing a JOIN on the users & users_profiles tables to get the 'location' and 'display', just use the information on users table.

That's my approach, however I do know that there are a lot of excellent and experienced programmers that can give me a word of advice about this matter.

My questions are:

Should I try to be conservative with the JOINs? or should I use them more? Why?

Are there any performance problems on the long run when using JOIN a lot?

Note: I must clarify, that I'm not trying to avoid JOINS at all. I use them only when needed. On this example would be comment/article authors, extra profile information that only displays on user profiles pages... etc.

解决方案

My advice on data modeling is:

• You should favour optional (nullable) columns over 1:1 joins generally speaking. There are still instances where 1:1 makes sense, usually revolving around subtyping. People tend to be more squeamish when it comes to nullable columns than they do about joins oddly;
• Don't make a model too indirect unless really justified (more on this below);
• Favour joins over aggregation. This can vary so it needs to be tested. See Oracle vs MySQL vs SQL Server: Aggregation vs Joins for an example of this;
• Joins are better than N+1 selects. An N+1 select is, for example, selecting an order from a database table and then issuing a separate query to get all the line items for that order;
• The scalability of joins is usually only an issue when you're doing mass selects. If you select a single row and then join that to a few things rarely is this a problem (but sometimes it is);
• Foreign keys should always be indexed unless you're dealing with a trivially small table;

More in Database Development Mistakes Made by AppDevelopers.

Now as for directness of a model, let me give you an example. Let's say you're designing a system for authentication and authorization of users. An overengineered solution might look something like this:

• Alias (id, username, user_id);
• User (id, ...);
• Email (id, user_id, email address);
• Login (id, user_id, ...)
• Login Roles (id, login_id, role_id);
• Role (id, name);
• Role Privilege (id, role_id, privilege_id);
• Privilege (id, name).

So you need 6 joins to get from the username entered to the actual privileges. Sure there might be an actual requirement for this but more often than not this kind of system is put in because of the hand-wringing by some developer thinking they might someday need it even though every user only has one alias, user to login is 1:1 and so on. A simpler solution is:

• User (id, username, email address, user type)

and, well, that's it. Perhaps if you need a complex role system but it's also quite possible that you don't and if you do it's reasonably easy to slot in (user type becomes a foreign key into a user types or roles table) or it's generally straightforward to map the old to the new.

This is thing about complexity: it's easy to add and hard to remove. Usually it's a constant vigil against unintended complexity, which is bad enough without going and making it worse by adding unnecessary complexity.

这篇关于MySQL JOIN滥用？它有多糟糕？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！