合并具有一对一关系的表 [英] Consolidating tables with one-to-one relationships

问题描述

我有一个成员资格系统的3个MySQL表。

• users


• user_profiles ：提供的个人信息


• user_member_profiles ：由管理员严格管理的信息（注册费已支付，会议




这些可以压缩成一个表格，从而节省了我的头痛和保持我的代码干净 - 但我觉得它

选项1：保持这种方式，继续做 JOIN 和繁琐的 UPDATE s （这一段数据转到这个表，这一段转到另一个等） em>。




选项2：将所有内容组合到一个表格中。





我假设使用一个表会更快，不需要连接表。也许这取决于数据？每个表具有大约12-20个字段，因此组合表将很大。





每个用户从每个表中不超过1个配置文件，





添加一点上下文：这是一个不断发展的CMS用PHP编写的，我将需要以对每个安装的表进行调整。管理员需要以类似材料的方式管理成员，因此我一次最多可选择200个用户。





从性能上来说，正确的方法是什么，设计或组织透视？

解决方案

宽表（很多列）要考虑的另一个因素是对RDBMS高速缓存。任何好的开发人员知道你不做'select * from table'，因为它会通过网络从RDBMS到客户端传递不必要的数据。但是磁盘和RAM之间也可能发生类似的效果，并且也影响表需要缓存的RAM空间量。





大多数RDBMS分配给定的内存量缓存数据，从而减少物理磁盘读取并加快对用户的响应。这是Oracle或SQL Server中的缓冲区缓存





如果您有一个宽表，并以select col1，col2，col3 from table的形式发出查询，RDBMS将加载完整的行到RAM（不是col1到3）。因为它会这样做，它将老化缓存的数据。如果你的表是宽的，你加载50列，你当然需要更多的RAM比相同的行数*一个窄表。这可以对RDBMS性能产生显着的影响。





许多宽表，从缓存中老化出其他表，可以看到IO统计信息通过屋顶常用表从缓存中淘汰，为宽表创造空间。





这个因素应该被添加到归一化数据的其他优点，并在表设计时考虑。实际上，如果您有一个潜在的宽表，其中一些数据将被定期访问，而一些数据很少会被访问，请考虑多个具有1对1关系的表。




I have 3 MySQL tables for a membership system.

• users: Minimum requirement to be a user, only related to account info (email, password, is_activated, etc)
• user_profiles: Personal information provided by the user (name, address, phone...)
• user_member_profiles: Information strictly managed by admins (registration fee paid, meetings attended, etc.)

These could be condensed into one table, saving me headaches and keeping my code clean - but I feel like it's better to leave them separate as they serve slightly different purposes.

Option 1: Leave it this way and keep doing JOINs and tedious UPDATEs (this piece of data goes to this table, this piece goes to another, etc.). More work for me, but maybe it makes more sense?

Option 2: Combine everything into one table.

I would assume using one table would be faster, no need to join tables. Maybe this depends on the data? Each table has about 12-20 fields, so the combined table would be large.

Each user has no more than 1 profile from each table, but may not even have a profile at all (or may have only 1 total).

To add a little context to this: It is for an ever-evolving CMS written in PHP which I will need to make tweaks to the tables for each installation. Admins need to manage members in a speadsheet-like way, so I will be selecting up to 200 users at a time.

What is the correct approach to this from a performance, design, or organization perspective?

解决方案

Another factor to consider with wide table (many columns) is the effect on the RDBMS cache. Any good developer knows that you don't do 'select * from table' as it will carry unnecessary data over the network from RDBMS to client. But a similar effect can happen betwen disk and RAM and also affect the amount of space in RAM that a table requires to cache.

Most RDBMSes allocate a given volume of memory to cache data, thus reducing physical disk reads and speeding response to the user. This is Buffer Cache in Oracle or SQL Server

If you have a wide table and issue a query in the form 'select col1, col2, col3 from table' the RDBMS will load the full rows into RAM (not col1 through 3). As it does so it will age out older cached data. If your table is wide and you load 50 columns you of course require more RAM than for the same number of rows * a narrow table. This can have a noticeable impact on RDBMS performance.

Lots of wide tables, aging out other tables from cache and it is possible to see the IO stats go thorough the roof as commonly used tables age out of cache to make room for wide tables.

This factor should be added to the other advantages of normalised data and taken into consideration at table design time. In effect if you have a potentially wide table with some data that will be regularly accessed and some that will be rarely, consider multiple tables with a 1 to 1 relationship.

这篇关于合并具有一对一关系的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！