使用 EAV 表获取动态属性的系统中的 INSERT 与 LOAD DATA
我有一个旧版 php Web 应用程序，可以执行 csv -->数据库导入到存储实体数据的“主”表和存储每个实体的动态数据的实体属性值表。
导入过程是对 csv 文件的逐行迭代，每行都会对主表执行一次插入操作，并在 EAV 表中执行多次插入操作。
这个过程很慢，对 mysql 调优的了解告诉我，一条 LOAD DATA 语句通常比一系列 INSERT 快得多；然而，由于 EAV 过程，迭代仍然必须发生，尽管基于数据库查询的结果而不是 csv 文件。
值得修改吗？
如果每个文件中有数千万条记录，并且通常只有不到 2/3 的文件字段实际映射到属性，这会产生影响吗？
I have a legacy php web app that performs csv --> database imports, into a 'master' table that stores entity data, and an entity-attribute-value table that stores dynamic data for each entity.
The import process is a line-by-line iteration through the csv file, with an INSERT into the master table and multiple INSERTs into the EAV table for each line.
This process is SLOOW, and what little know about mysql tuning tells me that a LOAD DATA statement is generally far faster than a series of INSERTs; however, because of the EAV process the iteration would still have to occur, though based off the results of a database query rather than the csv file.
Is it worth it to make the modification?
Does it make a difference if there are dozens of millions of records in each file, with generally less than 2/3 of the file fields actually being mapped to attributes?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
听起来是一个有用的修改。我要做的是将 CSV 预处理为两个文件 - master 和 eav 表。棘手的部分是在这两个文件之间建立某种链接，以便您可以使用正确的外键插入到 eav 表中。
如果出现以下情况，问题就会得到简化：
在这种情况下，您可以轻松地提前“知道”eav 外键值，并且在加载任一表的数据之前进行适当设置。
如果没有，您需要弄清楚如何获取主表记录的主键值，发布 LOAD DATA，并相应地链接到 eav 记录。
Sounds like a useful modification. What I would do is pre-process the CSV into two files - master and eav tables. The tricky part is establishing some sort of linkage between these two files so you can insert into the eav table with the correct foreign key.
The problem is simplified if:
In that case, you can easily "know" the eav foreign key value ahead of time and set appropriately before loading data for either table.
If not, you'll need to figure out how to get the primary key value for the master table records, post LOAD DATA, and link up with the eav records accordingly.