在数据库中高效存储多个Excel文件的方法是什么？

Question

在数据库中高效存储多个Excel文件的方法是什么？

4

我们正在开发一个重要的内部项目，该项目可以让用户上传Excel文件，并最终对从这些Excel中收集的所有数据进行搜索。在开始设计之前，我正在努力做我的功课，并提出最佳解决方案。

要求如下：

1. 用户可以上传具有任意列数的Excel文件，因此没有预定义的Excel结构。 2. 与第一点相反，我们认为用户具有一些字段。例如-名字，姓氏。这些列不必存在。 3. 搜索选项将按以下方式工作-当用户搜索时，他可以搜索特定列-那些预定义并且我们期望他的Excel文件拥有的列（在我们的示例中为名字和姓氏）。他还可以在“其他”字段下搜索所有其他列。

关于“其他”搜索字段-此字段将浏览所有不适合预定义列的所有Excel文件中的所有列。即-一个文件有一个年龄列，另一个文件有一个出生地列，“其他”字段将搜索所有这些列。

最佳方法是什么？

1. 动态为每个上传的Excel创建一个新的Django模型，其中包含Excel具有的所有列吗？ 2. 动态为每个文件创建一个新的Django模型，其中包含所有预定义的列（如果存在！）和一个“其他”文本字段，它将连接所有不相关的字段吗？ 3. 有一个大的Django模型（意味着我的数据库中只有1个表），其中包含所有预定义的字段（再次，可以为空），以及一个称为“其他”的字段，它将连接所有不相关的列吗？ 4. 我可以拥有具有所有预定义列的主表，以及具有对主表的外键的另一张表，其中每行代表一个“其他”字段。

第四种解决方案的示例-

+----+--------+--------+--------+
| id | field1 | field2 | field3 |
+----+--------+--------+--------+
|  1 | val1   | val1   | val1   |
|  2 | val2   | val2   | val2   |
|  3 | val3   | val3   | val3   |
+----+--------+--------+--------+

和维度表有关 -

+----+------+------+
| fk | key  | val  |
+----+------+------+
|  1 | key1 | val1 |
|  1 | key2 | val2 |
|  1 | key3 | val3 |
|  2 | key4 | val4 |
+----+------+------+

关于扩展性 - 我们预计最终不会有超过1500个Excel文件，每个文件包含100到大约10万行（我们可能会限制每个Excel文件的行数为100k）。我们从检查的Excel中得出的统计数据表明，我们不会超过30万行。

我们将使用Django和MySQL或PostgreSQL。

希望我的问题清晰易懂，不要太难理解。

谢谢！

- thomas

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Hans Then · Accepted Answer

编辑：在您修改问题后，我已经增加了关于您的模型4的简短部分。

我强烈建议不要动态创建表。这样做很混乱，而且我怀疑它的性能也不好。您的数据库将为每个要查询的数据库表创建访问路径，因此，如果您创建多个数据库文件，则需要搜索所有这些文件。

您可能需要使用您模型3的变体。

这意味着您使用一个表，但是不是为每个字段使用列，而是创建两个列，一个用于保存Excel列名，另一个用于保存其值。您还需要一些额外的条目来确定哪些Excel列和值属于哪个Excel电子表格。

因此，从概念上讲，您的建模方式应该是：

field1 field2 field3 field4 other
------------------------------------
x       y     z       a     etc=xyz

你可以这样建模：

sheet fieldname value
------------------------------------
key   field1    x
key   field2    y
key   field3    z
key   field4    a
key   etc       xyz

这种模型的优点在于编程搜索变得更容易。你可以将任何搜索模型简单地建模为 select * from data where fieldname='%s' and value='%s'。如果在fieldname上创建数据库索引（并且可能需要在用于标识Excel表的key上创建索引），那么与您对第三个模型的原始想法相比，不应该有性能损失。

您的第四个模型也可以工作。它的优点是，对于预定义字段，用户的查询语句将很容易映射到SQL select语句。它的缺点是，您需要以与用户的其他搜索条件不同的方式处理“其它”列。您还指出，用户有时会不输入您希望存在的列。这意味着您必须使这些列可为空，从而增加了存储要求。

总体而言，我认为我的建议方法比您的选项4更好，因为它的概念更简单。您提到过它会创建太多行。的确会创建更多行，但MySQL和PostgresSQL可以轻松处理大量的行。PostgresSQL可以存储无限数量的行。MySQL可以存储4000万至亿级行（如果需要更多，可以使用--big-tables编译MySQL）。

就性能而言，只要在字段上有索引，表的大小并没有真正的影响。