mysql查询普通关系表与串联关系表 [英] mysql query normal relationship table vs concatenated realtionship table

问题描述

我对两个表之间的关系有疑问.

I have a question about relationships between two tables.

假设我们有一个表用户和链接.

Let's say we have a table users, and links.

users
+++++++++
id name
1  name1
2  name2
3  name3
+++++++++

---

links
+++++++++
id link
1  link1
2  link1
3  link1
+++++++++

现在链接这两个的正常方法是使用name_links表. 例如:

Now the normal way to link these two is with a name_links table. For example:

name_links
++++++++++++
uid  lid
1    1
1    3
2    3
2    1
2    2
3    2
++++++++++++

现在我想知道制作这样的桌子是否是个好主意

Now i was wondering if it is a good idea to make a table like this

name_links
++++++++++++
uid  lid
1    1,3
2    1,2,3
3    2
++++++++++++

我能想到的优点和缺点:

Pros and cons i can think of are:

pros1:

您将始终搜索索引，查询速度更快 例如，选择uid = 1，然后选择链接1,3.两者都是索引，因此加载很快.

You will always search on indexes, faster queries example select where uid=1 and then select links 1,3. Both are indexes so it will be a fast load.

如果您有1000个用户，并且每个用户都有20个链接，这意味着您必须经过20.000条记录才能获得所有链接(我想对此不确定).使用这种方法，您只需获取一个索引就可以了.

If you have 1000 users, and they each have 20 links, this means you have to go trough 20.000 records to get all the links(i think, not sure of this). Using this method you only take one index and you are done.

cons1:

您将不得不更频繁地更新name_links表，读取，编辑和写入 示例用户2删除link2的方法将是:
+获取用户1的字符串
+从字符串中删除数字
+插入新字符串

You will have to update the name_links table more frequently, read, edit, and write example user 2 deletes link2 the method will be:
+ get the string of user 1
+ remove the number from the string
+ insert the new string

这里的所有操作都是在索引上完成的，所以我认为它会很快.

Everything here is done on an index, so i assume it will be fast.

cons2:

另一个缺点是，删除链接2时，您必须遍历所有字符串，但是可以说这并不是什么大问题，因为这种情况不会经常发生.

Another con is when you delete link 2, you have to go trough all the strings, but lets say this is not as much of a problem, as this will not happen often.

到目前为止，这是我能想到的，而我正要决定要去哪里.

This is what i can come up with so far, and I am at the point of my project where i have to decide with which to go.

我很想就选择哪种方法提供一些建议.我的优缺点正确吗?有没有我没有考虑的事情.我们将非常感谢您对此主题的任何帮助.

I would love to have some advice on which method to choose. Do i have my pros and cons right? Are there things I am not taking in considering. Any help on this topic will be highly appreciated.

谢谢你们！

推荐答案

非规范化解决方案具有以下缺点:

Denormalized solution has these drawbacks:

• 您无法有效地连接名称和链接(FIND_IN_SET不可显示)

您不能使用FOREIGN KEYs(在InnoDB中)强制实施参照完整性

You cannot enforce referential integrity using FOREIGN KEYs (in InnoDB)

删除和添加名称链接关系更为复杂

Deleting and adding a name-link relationship is more complex

如果您从不搜索给定链接的名称，并且链接的数量很少，那么摆脱多余的联接可能会受益.

If you never search for names given a link and the links are few in number, you may possibly benefit by getting rid of an extra join.

您应该确保性能收益是真实的，您确实需要它，并且您知道维护非规范化表的复杂性.

You should make sure that the performance benefit is real, you really need it and you are aware of the complications of maintaining a denormalized table.

如果links是固定的，则可以考虑使用本机SET数据类型.

If the links are fixed, you can consider using a native SET datatype instead.

这篇关于mysql查询普通关系表与串联关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！