高效存储多个Excel文件于数据库的方法？

Question

我们正在进行一个大型内部项目，目的是让用户能够上传Excel文件，最终可以对从这些Excel中收集到的数据进行搜索。在我们开始设计之前，我想先做好功课，找出最佳解决方案。

需求如下 -

1. 用户可以上传任意列数的Excel文件，也就是说，Excel文件的结构没有预定义。

2. 与第一点相反，我们假设用户会有一些特定的字段。例如：名字和姓氏。这些列不一定要存在。

3. 搜索功能的工作方式如下 - 当用户进行搜索时，可以通过特定列进行搜索，也就是我们预期用户的Excel文件中应该有的列（在我们的例子中是名字和姓氏）。用户也可以在“其他”字段中搜索所有其他列。

关于其他搜索字段的补充说明 - 这个字段会搜索所有Excel文件中不符合预定义列的所有列。比如，一个文件有年龄列，另一个文件有出生地列，“其他”字段会在这些列中进行搜索。

实现这个功能的最佳方法是什么？

1. 为每个上传的Excel动态创建一个新的Django模型，包含Excel中的所有列？

2. 为每个文件动态创建一个新的Django模型，包含所有预定义的列（如果存在的话！），以及一个“其他”文本字段，用来连接所有不相关的字段？

3. 有一个大的Django模型（意味着数据库中只有一张表），包含所有预定义的字段（这些字段可以为空），还有一个叫“其他”的字段，用来连接所有不相关的列？

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到大约100,000行（我们可能会限制每个Excel文件的行数为100,000）。我们从检查过的Excel中得到的统计数据表明，行数不会超过3000万。

我们将使用Django，数据库选择MySQL或PostgreSQL。

希望我的问题清楚明了，不会让人困惑。

谢谢！

Answer 1

编辑：在你修改了问题后，我添加了关于你模型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 上创建索引），那么性能应该不会比你原来的模型3差。

你的模型4也可以工作。它的优点是，对于预定义的字段，用户的查询语句可以很容易地映射到SQL选择语句。但缺点是，你需要以不同的方式处理“其他”列和用户的其他搜索条件。你还提到用户有时不会输入你预期的列。这意味着你必须让这些列可以为空，这会增加存储需求。

总体来说，我认为我建议的方法比你的选项4更好，因为它在概念上更简单。你提到你觉得这会创建太多行。确实，这会创建更多行，但MySQL和PostgresSQL可以轻松处理这么多行。PostgresSQL可以存储无限数量的行。MySQL可以存储大约4000万行（如果需要更多，你可以用 --big-tables 编译MySQL）。

在性能方面，只要你在字段上有索引，表的大小其实没什么实质性的影响。