It has been mentioned to me i should separate columns that are used often from columns that are not. Why?
有人曾对我说过，我应该将经常使用的列与不使用的列分开。为什么?
Is it because a table with less columns is better for the cache? Is there an optimization for caching all columns rather than selected? Is this for tables frequently read? Should I separate freq write columns into their own table?
是因为列数少的表更适合缓存吗?是否有优化缓存所有列而不是选择?这是经常读的表吗?我是否应该将freq写入到自己的表中?
4 个解决方案
#1
1
When you update a row, many databases will make a copy of the entire row. Depending on how long the copies of the row persist (which depends on many factors) you may end up with more garbage in the database than if you had separated the row into two tables. This garbage will make the database larger, and therefore may make queries slower.
当您更新一行时，许多数据库将复制整个行。根据行副本的持续时间(这取决于许多因素)，与将行分隔为两个表相比，数据库中的垃圾可能会更多。这种垃圾将使数据库更大，因此可能使查询速度变慢。
Whether you want to prematurely optimize by denormalizing (which you would have needed to do to get rows that could benefit from being split) is something you should consider in any case.
无论如何，您都应该考虑是否要通过非规范化(为了从分割中获益，您需要这样做)来提前进行优化。
#2
2
This generally has to do with not making the parent table too wide which creates problems in the paging (and possible integrity problems as you might suddenly find you can't store a record because it is wider than the number of bytes the row size will allow.). Read about how the database stores records in pages in your particular database and you will see why less wide tables are generally preferred by experienced designers. One method of creating less wide tables is to move the less used information to a table that has a one-to-one relationship with the initial table.
这通常与不使父表太宽有关，这会在分页中产生问题(以及可能出现的完整性问题，您可能会突然发现无法存储记录，因为它比行大小允许的字节数要宽)。阅读数据库如何在特定数据库中的页面中存储记录，您将了解为什么经验丰富的设计人员通常更喜欢不太宽的表。创建不太宽的表的一种方法是将较少使用的信息移动到与初始表具有一对一关系的表中。
#3
1
RL use case would be having a Details view (displaying 1 record at a time with all details) and List view for same items.
RL用例将包含一个Details视图(一次显示一条记录，包含所有细节)和列表视图。
The separation for the List view fields is recommended because it allows efficient use of disk cache feature:
推荐对列表视图字段进行分离，因为它允许有效地使用磁盘缓存特性:
- disk cache can read ahead by copying more data than what the program asked for in the assumption that the following consecutive data segment will be needed next.
- 磁盘缓存可以通过复制比程序要求的更多的数据来提前读取，假设接下来将需要后续的数据段。
#4
0
I have not heard of this, though guess it might make some sense. When a table is retrieved the data must be loaded into memory on the server. If you have columns that are frequently accessed and ones that are in-frequently accessed in the same table then it is conceivable that without well-made queries that all of this would be loaded into memory. If this does happen the amount of data loaded into memory would be decreased by moving the in-frequently accessed data into another table.
我还没听说过这个，不过我想这可能是有道理的。当检索表时，必须将数据加载到服务器上的内存中。如果您有经常被访问的列，并且在同一个表中经常访问的列，那么可以想象，如果没有良好的查询，所有这些都将被加载到内存中。如果发生这种情况，通过将经常访问的数据移动到另一个表中，将减少加载到内存中的数据量。
I don't know exactly how MySQL does all of its searches on the very base level, but I assume that if you use the names of the columns when selecting and not * then this problem would be minimized.
我不知道MySQL是如何在基本层次上执行所有搜索的，但是我假设如果您在选择而不是*时使用列的名称，那么这个问题就会最小化。
Again, I don't know the details of how all of this is coded, but I don't think it should be a problem as long as your don't select columns you don't need.
同样，我不知道这些代码是如何编码的，但我不认为它应该是一个问题，只要您不选择您不需要的列。
#1
1
When you update a row, many databases will make a copy of the entire row. Depending on how long the copies of the row persist (which depends on many factors) you may end up with more garbage in the database than if you had separated the row into two tables. This garbage will make the database larger, and therefore may make queries slower.
当您更新一行时，许多数据库将复制整个行。根据行副本的持续时间(这取决于许多因素)，与将行分隔为两个表相比，数据库中的垃圾可能会更多。这种垃圾将使数据库更大，因此可能使查询速度变慢。
Whether you want to prematurely optimize by denormalizing (which you would have needed to do to get rows that could benefit from being split) is something you should consider in any case.
无论如何，您都应该考虑是否要通过非规范化(为了从分割中获益，您需要这样做)来提前进行优化。
#2
2
This generally has to do with not making the parent table too wide which creates problems in the paging (and possible integrity problems as you might suddenly find you can't store a record because it is wider than the number of bytes the row size will allow.). Read about how the database stores records in pages in your particular database and you will see why less wide tables are generally preferred by experienced designers. One method of creating less wide tables is to move the less used information to a table that has a one-to-one relationship with the initial table.
这通常与不使父表太宽有关，这会在分页中产生问题(以及可能出现的完整性问题，您可能会突然发现无法存储记录，因为它比行大小允许的字节数要宽)。阅读数据库如何在特定数据库中的页面中存储记录，您将了解为什么经验丰富的设计人员通常更喜欢不太宽的表。创建不太宽的表的一种方法是将较少使用的信息移动到与初始表具有一对一关系的表中。
#3
1
RL use case would be having a Details view (displaying 1 record at a time with all details) and List view for same items.
RL用例将包含一个Details视图(一次显示一条记录，包含所有细节)和列表视图。
The separation for the List view fields is recommended because it allows efficient use of disk cache feature:
推荐对列表视图字段进行分离，因为它允许有效地使用磁盘缓存特性:
- disk cache can read ahead by copying more data than what the program asked for in the assumption that the following consecutive data segment will be needed next.
- 磁盘缓存可以通过复制比程序要求的更多的数据来提前读取，假设接下来将需要后续的数据段。
#4
0
I have not heard of this, though guess it might make some sense. When a table is retrieved the data must be loaded into memory on the server. If you have columns that are frequently accessed and ones that are in-frequently accessed in the same table then it is conceivable that without well-made queries that all of this would be loaded into memory. If this does happen the amount of data loaded into memory would be decreased by moving the in-frequently accessed data into another table.
我还没听说过这个，不过我想这可能是有道理的。当检索表时，必须将数据加载到服务器上的内存中。如果您有经常被访问的列，并且在同一个表中经常访问的列，那么可以想象，如果没有良好的查询，所有这些都将被加载到内存中。如果发生这种情况，通过将经常访问的数据移动到另一个表中，将减少加载到内存中的数据量。
I don't know exactly how MySQL does all of its searches on the very base level, but I assume that if you use the names of the columns when selecting and not * then this problem would be minimized.
我不知道MySQL是如何在基本层次上执行所有搜索的，但是我假设如果您在选择而不是*时使用列的名称，那么这个问题就会最小化。
Again, I don't know the details of how all of this is coded, but I don't think it should be a problem as long as your don't select columns you don't need.
同样，我不知道这些代码是如何编码的，但我不认为它应该是一个问题，只要您不选择您不需要的列。