MySQL是一个大型数据库还是许多小表的集合？

Question

我们的应用程序将为每个用户创建动态表单。

我们正在考虑两种方法：

a) 我们将所有用户表单数据存储在单个表中，作为键值对。然后使用 pivot table technique 技术获取数据并进行过滤，这种方法有效，但可能会消耗大量资源。

b) 为每个用户创建适合其设计的表格/设计表格，这样我们就不必进行数据透视，并且可以获得 MySQL 的所有优点，而且我们不需要连接超过几个表。

我们主要关注可扩展性，在情况 'a' 中，如果表格变得非常庞大（即使只考虑1000个用户注册），在计划 'b' 中，如果明天我们的表格随着流量增长而增长，我们可能需要使用 MySQL 集群，但是根据我在此处阅读到的内容，集群有表格限制 http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-limitations-database-objects.html。 更新 一种新的想法浮现在我们脑海中，为什么不创建一个适合单个表单元素（文本字段，文本区域等）的单个表格（作为键，值），使用基于用户创建的表单定义的连接（连接数=字段数），我们可以创建水平数据，而不需要进行数据透视。您认为这个想法怎么样。

更新于2011年4月9日

我们对比了透视和连接

场景-我们有一个包含7个字段的表单，数据存储为（单个表单插入的示例数据）

对于透视查询，将70000行数据透视为10000个表单插入需要0.92秒。 对于连接，展示这10000个表单插入需要17.63秒（哇.....）。

我的表格

创建表格，如果不存在则创建名为“兽医”的表格，包括以下列：id（整数类型，不为空），form_id（整数类型，不为空），key（varchar类型，长度为255，不为空），value（varchar类型，长度为255，不为空），其中id和form_id各自有一个索引。使用的引擎为MyISAM，字符集为latin1。

旋转查询

选择id，使用GROUP_CONCAT函数将k1、k2、k3、k4、k5、k6、k7的值分别合并到'key1'、'key2'、'key3'、'key4'、'key5'、'key6'、'key7'中，并且只有当'key'等于对应的k值时才会使用该函数。从vet表格中选取数据，其中form_id等于2，然后按照id进行分组。

连接查询

SELECT v.id, v1.value as key1, v2.value as key2, v3.value as key3, 
       v4.value as key4, v5.value as key5, v6.value as key6, v7.value as key7
FROM vet v
LEFT JOIN vet v1 ON v1.id = v.id AND v1.`key` = "k1"
LEFT JOIN vet v2 ON v2.id = v.id AND v2.`key` = "k2"
LEFT JOIN vet v3 ON v3.id = v.id AND v3.`key` = "k3"
LEFT JOIN vet v4 ON v4.id = v.id AND v4.`key` = "k4"
LEFT JOIN vet v5 ON v5.id = v.id AND v5.`key` = "k5"
LEFT JOIN vet v6 ON v6.id = v.id AND v6.`key` = "k6"
LEFT JOIN vet v7 ON v7.id = v.id AND v7.`key` = "k7"
WHERE v.form_id = 2
GROUP BY v.id

我认为我们现在应该坚持使用旋转表格的方法，如果巨大的差异是由于查询引起的，请查看我的查询。

请建议哪种解决方案更好，或者是否有其他更好的解决方案。

Sudesh

- Sudesh

---

通过动态表单，您的意思是应用程序将创建表单。那么，应用程序能否预测表单的数据库设计？它是否可以像两个用户拥有相同的表单一样？每个用户表单中是否有任何共同字段？ - Nitin Midha

是的，我们将根据用户创建的表单创建表格，而且不会有任何字段在用户之间共用。 - Sudesh

Answer 1

我建议使用一个单一的数据库进行设计。这样，当需要进行水平扩展时，您就可以始终使用分片技术。

- vbence

---

你认为即使有100万行的表格（我们测试了56万行，创建一个数据透视表需要10-14秒），使用数据透视表技术是否仍然可行？或者有没有其他替代方案？ - Sudesh

@Sudesh 我假设您为某个特定用户的某个表单创建了透视表。因此，使用正确的索引，您可以仅使用所选表单的提交数据开始透视步骤。- 如果我理解正确，560,000是所有自定义表单中提交的所有字段的数量。每次只需要某个表单的提交数据，索引就可以快速消除其余记录。 - vbence

是的，我们只需要转换与该表单相关的数据，但您认为旋转比连接更好（我们的新想法）？ - Sudesh

经过分析，我认为我们将坚持使用一个大表而不是连接。谢谢。 - Sudesh

Answer 2

您不想为每个用户创建的表单创建新表。
但是，您也不希望将所有数据存储在一个大表中。
最好将内容存储在几个表中，以避免重复，就像这样。

首先是用户数据表：

用户表
id: 整数自增主键
用户名: varchar(255)
其他用户数据

然后是一个将用户与表单数据链接起来的表（但实际上不保存任何表单数据）

用户表单表
id: 整数自增主键
user_id: 整数索引
... 其他固定数据字段，每个表单中只出现一次。

接下来是表单数据表
表单属性表
id: 整数自增主键
UserForm_id: 整数索引
属性名: varchar(255)
属性值: varchar(255)

现在，当您想要访问表单数据时，可以使用以下查询：

select * from FormProperties
inner join UserForm on (FormProperties.UserForm_id = UserForm.id)
inner join User on (UserForm.User_id = user.id) 
where UserForm.id = 103

这样，您就不会存储冗余数据。 请注意，如果您从未需要唯一访问单个属性，则可以在属性表上删除唯一ID。

不用担心MySQL集群，只需从基本的MySQL（5.x）和MySAM表开始，如果速度不够快，那么您可以考虑一些技巧，但在一百万条记录以下，我不会费心思。

- Johan

---

新增的问题是我们必须根据表单中输入的数据向表单所有者展示报告，我相信上述查询将给我提供垂直数据。因此，创建报告将成为一个问题，我们将不得不在PHP层编写所有聚合逻辑，或使用数据透视表技术。 - Sudesh

将数据存储在 PHP 数组中并呈现。这就是数据库应该工作的方式。 - Johan

为了创建报告，我们可能需要使用MySQL的聚合函数，并且在PHP级别进行一些过滤可能会消耗资源。 - Sudesh

Answer 3

拥有许多小型数据库的问题是众所周知的： - 当索引和表很小时，磁盘和内存的使用效率低下 - 当#数据库>100且#表>10000时，mysql服务器扩展性差 - 管理/管理噩梦 但也有一些优点： - 客户隔离提供更好的安全性 - 转储/加载/锁定/更改单个客户的表而不影响其他客户 - 更容易管理多个版本和自定义模式添加

拥有一个大型数据库，带有分片，听起来不错，但也有缺点： - 所有客户都被锁定在相同的模式中，必须同时进行升级 - 对于单个客户来说安全性较差 - 需要修改代码来管理分片 - 一旦建立了分片，非常难以更改 - “坏邻居”效应：一个过度活跃的客户会影响分片中邻居的SLA 主要优点是它具有良好的可扩展性。

完整披露：我在ParElastic工作。但我真诚地相信我们的虚拟多租户数据库是解决这个问题的唯一完整解决方案： - 每个客户都看到自己数据库的完整、隔离实例 - 独立的管理命令用于其数据库 - 可扩展的分片技术在用户内部和之间分布数据 - 每个客户的模式管理支持滚动升级和自定义扩展 - 使用普通SQL进行安全的跨客户查询

如果您感兴趣，可以在https://aws.amazon.com/marketplace（搜索“parelastic”）免费下载预打包的ParElastic环境。或者访问http://parelastic.com了解更多信息。

- D Hentchel