有更多的列或更多的行更有效? [英] More efficient to have more columns or more rows?

问题描述

我目前正在重新设计一个可能包含大量数据的数据库-我可以选择在数据库中包含许多不同的列，也可以使用很多行.如果我在下面做一些概述，可能会更容易:

I'm currently redesigning a database which could contain a lot of data - I have the option to either include a number of different columns in the database or use a lot of rows instead. It's probably easier if I did some kind of outline below:

item_id | user_id | title | description | content | category | template | comments | status
-------------------------------------------------------------------------------------------
1       | 1       | ABC   | DEF         | GHI     | 1        | default  | 1        | 1
2       | 1       | ZYX   |             | QWE     | 2        | default  | 0        | 1
3       | 1       | A     |             | RTY     | 2        | default  | 0        | 0
4       | 2       | ABC   | DEF         | GHI     | 3        | custom   | 1        | 1
5       | 2       | CBA   |             | GHI     | 3        | custom   | 1        | 1

相对于以下结构:

item_id | user_id | attribute   | value
---------------------------------------
1       | 1       | title       | ABC
1       | 1       | description | DEF
1       | 1       | content     | GHI
...     | ...     | ...         | ...

我将来可能要创建其他属性(出于参数目的，请创建50个属性)-因此，如果使用多列，则可能会有很多空单元格.在可能的情况下，可以在不同类型的内容之间重用属性名称，例如博客条目，事件和画廊，title将很容易重用.

I may want to create additional attributes in the future (50 for arguments sake) - so there could be a lot of empty cells if using multiple columns. The attribute names would be reused, where possible, across different types of content - say a blog entry, event, and gallery - title would easily be reused.

所以我的问题是，就查询速度和磁盘空间而言，使用多列或多行是否更有效?还是您会推荐关系表，所以有一个用于博客的表，一个用于事件的表等.我只是想提出一个易于扩展的解决方案，理想情况下，我不想为每种类型的表创建一个表.内容，因为我正在考虑开发人员通过app/API系统(属性受到严格控制)创建新的内容.

So my question is, is it more efficient to use multiple columns or multiple rows - in terms of query speed and disk space. Or would you instead recommend relationship tables, so there's a table for blogs, a table for events, etc. I'm just trying to come up with an easily expandable solution, where I ideally do not want to create a table for every kind of content as I'm thinking of developers creating new kinds of content via an app/API system (with attributes being tightly controlled).

是否有多行的补充问题

在MySQL中，我如何将多行转换为可用的列格式(我想是临时表)-因此，我可以按内容类型进行一些过滤，例如.

How could I, in MySQL, convert multiple rows into a usable column format (I guess temporary tables) - so I could do some filtering by content type, as an example.

推荐答案

基本上，mysql的行长是可变的，只要不改变每个表级别的行长即可.因此，空的cols将不会使用任何空间(嗯，差不多).

Basically, mysql has a variable row length as long as one does not change the on a per table level. Thus, empty cols will not use any space (well, almost).

但是对于blob或text列，将它们标准化可能会更好，因为它们可能要存储大量数据，并且每次扫描表时都需要读取/跳过.即使该列不在结果集中，并且您正在索引之外进行查询，也要花大量时间在行上.

But with blobs or text columns, it might be better to normalize those, as these may have large data to store and this needs to be read / skipped every time a table is scanned. Even if the column is not in the result set and you're doing queries outside of an index, it will take it's time on a large amount of rows.

作为一个好的实践，我认为将所有管理和经常使用的cols放在一个表中，并对其余所有表进行规范化将很快.第二个示例中的一种垂直"设计将很难读取，一旦使用临时表，您迟早会遇到性能问题.

As a good practice I think it will be fast to put all administrative and often used cols in one table and normalize all the rest. A kind of "vertical" design as in your second example will be complex to read and as soon as you work with temporary tables you will run in to performance issues sooner or later.

这篇关于有更多的列或更多的行更有效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！