时间戳

首页 > 解决方案 > 大型数据插入 (> 4,000,000) 的 SQL INSERT 性能

java - 大型数据插入 (> 4,000,000) 的 SQL INSERT 性能

问题描述

我在向表中插入大量数据时遇到了问题。

我有一个 xml 文件中的数据，大小从40 MB 到 3 GB。解析数据最多需要 3 分钟。将数据插入到一个 700 MB 文件的表中大约需要 30 分钟。太长了，数据总量200GB左右，插入数据库需要一个月的时间。

如何优化插入？

重要提示：记录中的数据可能重复。我在复制时使用 UPDATE ON DUPLICATE KEY。

我试过的：

不幸的是，所有这些方法都太慢了。

数据库中的表：


CREATE TABLE as_apartments_params (
  id int NOT NULL,
  objectid int NOT NULL,
  changeid int DEFAULT NULL,
  changeidend int NOT NULL,
  typeid int NOT NULL,
  value varchar(8000) DEFAULT NULL,
  updatedate date NOT NULL,
  startdate date NOT NULL,
  enddate date NOT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;

XML 文件：


<?xml version="1.0" encoding="utf-8"?><PARAMS>
<PARAM ID="719546591" OBJECTID="40537945" CHANGEID="192698204" CHANGEIDEND="0" TYPEID="8" VALUE="83:00:050002:426" UPDATEDATE="2021-04-29" STARTDATE="2021-04-29" ENDDATE="2079-06-06" />

<PARAM ID="719547975" OBJECTID="40539484" CHANGEID="192698457" CHANGEIDEND="0" TYPEID="8" VALUE="83:00:050002:477" UPDATEDATE="2021-04-29" STARTDATE="2021-04-29" ENDDATE="2079-06-06" />

<PARAM ID="719548916" OBJECTID="40530716" CHANGEID="192698634" CHANGEIDEND="0" TYPEID="8" VALUE="83:00:050002:438" UPDATEDATE="2021-04-29" STARTDATE="2021-04-29" ENDDATE="2079-06-06" />

<PARAM ID="719548922" OBJECTID="40535413" CHANGEID="192698640" CHANGEIDEND="0" TYPEID="8" VALUE="83:00:050002:430" UPDATEDATE="2021-04-29" STARTDATE="2021-04-29" ENDDATE="2079-06-06" />
</PARAMS>

标签： javamysqlxml

解决方案

INSERT IGNOREid将根据任何唯一键（在您的情况下）跳过任何重复的行。

当你批量处理它们时，它们是什么INSERT样子的？希望它在一个单一的有数千行INSERT：

INSERT INTO t (...) VALUES (...), (...),  ... ;

该技术可能是第二快的。

最快的方法是使用 500 万行的 CSV 文件和LOAD DATA. 但这可能是最快的，因为您需要将数据转换为文件以写入以供读取。

或者，也许，最快的将是

LOAD XML LOCAL INFILE 'filename'
    REPLACE
    INTO TABLE ...
    etc.

请参阅https://dev.mysql.com/doc/refman/8.0/en/load-xml.html 这将消除您解析 XML 的需要。（我没用过LOAD XML，所以不知道速度有多快，也不知道你的XML标签能不能接受。如果需要，XSLT也许可以转换XML。）

你每天都运行这个负载吗？它会“取代”整个桌子吗？如果是这样，请执行以下操作：

CREATE TABLE new_t LIKE t;
load the data by whatever means
RENAME TABLE t TO old_t,
             new_t TO t;
DROP TABLE old_t;

t无论负载需要多长时间，停机时间几乎为零。

重复

XML中的重复项吗？如果它在 XML 中，“更好”的数据会排在最后吗？ INSERT IGNORE会照顾它。

还是介于新数据和旧数据之间？您是说每天要添加大约 4 999 900 行吗？请详细说明; 我不明白你的意思。

推荐阅读