mysql - MySQL-如何一点一点而不是一次全部地执行事务(如循环)

我需要传输数据（我需要的列是n.inchi,n.info,n.accessions,n.chebiid从一个表到另一个表，两个表都有超过20000行（heroku_chemical_chemical有50000行，这就是我要传输数据的表）。
我试过这个问题：

UPDATE heroku_chemical_chemical AS h, new_compounds_filtered AS n
SET
h.inchi_identifier=n.inchi,
h.info=n.info,
h.accessions=n.accessions,
h.chebi_id=n.chebiid
WHERE h.name = n.name
AND (h.inchi_identifier = '' OR h.inchi_identifier IS NULL);

所以基本上，表1中出现的化学名称（heroku_chemical_chemical）可能出现在表2中，如果是的话，我需要从表2中获取该化学物质的数据。如果h.inchi_identifier里面有数据，那么我知道化学反应已经完成了。问题是执行查询需要非常长的时间，我让它运行了一夜，但第二天早上它仍然没有完成，所以我不得不取消它。既然是交易，就什么也没转移。如果它改为逐位执行传输，那么它将工作。
我可以将此添加到查询中：

AND n.id BETWEEN 1 AND 500

要将查询限制为500行（从第二个表中传输数据），请将查询分成小块进行，但之后我必须继续使用BETWEEN子句的不同值手动重新运行查询。它也很慢。我更愿意用纯SQL来完成这项工作，而不是设置PHP脚本。
有没有一种方法可以做到这一点，即它在运行时插入数据，而不是等待整个事务完成？还有，有没有办法更快地做到这一点？
下表定义如下：
化学工业

CREATE TABLE `heroku_chemical_chemical` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8mb4_unicode_ci,
  `synonyms` text COLLATE utf8mb4_unicode_ci,
  `associated_from` text COLLATE utf8mb4_unicode_ci,
  `category_associated_from` text COLLATE utf8mb4_unicode_ci,
  `chemical_number` text COLLATE utf8mb4_unicode_ci,
  `parent_chemical_numbers` text COLLATE utf8mb4_unicode_ci,
  `category_id` text COLLATE utf8mb4_unicode_ci,
  `slug` text COLLATE utf8mb4_unicode_ci,
  `cas_rn` text COLLATE utf8mb4_unicode_ci,
  `definition` text COLLATE utf8mb4_unicode_ci,
  `drug_bank_ids` text COLLATE utf8mb4_unicode_ci,
  `foodb_id` text COLLATE utf8mb4_unicode_ci,
  `itis_id` text COLLATE utf8mb4_unicode_ci,
  `name_scientific` text COLLATE utf8mb4_unicode_ci,
  `picture_content_type` text COLLATE utf8mb4_unicode_ci,
  `picture_file_name` text COLLATE utf8mb4_unicode_ci,
  `picture_file_size` text COLLATE utf8mb4_unicode_ci,
  `wikipedia_id` text COLLATE utf8mb4_unicode_ci,
  `actor_id` text COLLATE utf8mb4_unicode_ci,
  `bio_cyc_id` text COLLATE utf8mb4_unicode_ci,
  `chebi_id` text COLLATE utf8mb4_unicode_ci,
  `chem_spider_id` text COLLATE utf8mb4_unicode_ci,
  `chembl_id` text COLLATE utf8mb4_unicode_ci,
  `ctd_id` text COLLATE utf8mb4_unicode_ci,
  `hmdb_id` text COLLATE utf8mb4_unicode_ci,
  `inchi_identifier` text COLLATE utf8mb4_unicode_ci,
  `inchi_key` text COLLATE utf8mb4_unicode_ci,
  `kegg_compound_id` text COLLATE utf8mb4_unicode_ci,
  `omim_id` text COLLATE utf8mb4_unicode_ci,
  `pdb_id` text COLLATE utf8mb4_unicode_ci,
  `pubchem_compound_id` text COLLATE utf8mb4_unicode_ci,
  `stitch_di` text COLLATE utf8mb4_unicode_ci,
  `t3db_id` text COLLATE utf8mb4_unicode_ci,
  `uni_prot_id` text COLLATE utf8mb4_unicode_ci,
  `iupac_name` text COLLATE utf8mb4_unicode_ci,
  `formula` text COLLATE utf8mb4_unicode_ci,
  `smiles` text COLLATE utf8mb4_unicode_ci,
  `chemspider_id` text COLLATE utf8mb4_unicode_ci,
  `molecular_weight` text COLLATE utf8mb4_unicode_ci,
  `accessions` text COLLATE utf8mb4_unicode_ci,
  `chebi_info` text COLLATE utf8mb4_unicode_ci,
  `chebi_name` text COLLATE utf8mb4_unicode_ci,
  `compound_type` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`)
)
ENGINE=InnoDB
AUTO_INCREMENT=379336
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

过滤的新化合物

CREATE TABLE `new_compounds_filtered` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8mb4_unicode_ci,
  `chebiid` text COLLATE utf8mb4_unicode_ci,
  `info` text COLLATE utf8mb4_unicode_ci,
  `smiles` text COLLATE utf8mb4_unicode_ci,
  `inchi` text COLLATE utf8mb4_unicode_ci,
  `inchikey` text COLLATE utf8mb4_unicode_ci,
  `parent_id` text COLLATE utf8mb4_unicode_ci,
  `accessions` text COLLATE utf8mb4_unicode_ci,
  `synonyms` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`)
)
ENGINE=InnoDB
AUTO_INCREMENT=85432
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci ;

更新：
所以我正在运行一个新的查询，它需要将ID（已编制索引）加载到关系表的列中。

UPDATE chemical_organism_relations AS O2, heroku_chemical_chemical AS H1
SET O2.compound_id = H1.id, O2.substance_type = 'compound'
WHERE O2.foodb_compound_id = H1.foodb_id;

同样的问题，查询似乎没有完成。我无法对H1.foodb_id设置唯一索引，因为表中只有大约5%的化学品具有foodb id。所以我遇到了同样的问题。因为它的所有字段现在都是空的，所以没有索引，除非在每个字段中插入一个临时的唯一数字，否则无法索引它们。
以下是performance_schema.events_statements_current表中的内容：

我一直在寻找跟踪查询运行进度的方法。我想知道的是这些专栏是什么意思：

有没有办法看看幕后发生了什么？如果我能看到服务器正在做什么，以及它在运行查询方面取得了多大的进展，那么我就知道查询是否会完成，但现在我不知道它是崩溃了还是发生了什么。
我刚做了一个非常简单的查询：
mysql>更新化学有机体关系集合物质类型=
'复合'；查询OK，受影响的740672行（1分钟2.95秒）行
匹配：740672更改：740672警告：0
74000，但需要一分钟来处理不涉及其他表的简单更新查询。
我的查询有大问题
在我将答案与主线合并之前，有人对答案发表了评论，他们说是因为：

O2.compound_id is unindexed because all of its fields are blank

我的问题很大。我无意中发布了错误的查询，但我现在发布了正确的查询，查询是否仍然有大问题？

最佳答案

如前所述，5万条线路真的不算什么：1米后事情变得严重，如果你想毫无问题地超过10米，你需要考虑一下。。。
除此之外，当你做一个大的事务时，你需要小心一点，innoDB没有真正配置成默认的，并且会有一些RAM&I.O磁盘瓶颈（看看MySQL transaction size - how big is too big?如果你必须经常这样做，一些DB调优可能是个好主意）
无论如何，让我们从一些基础开始：

WHERE
    h.name = n.name
    AND (
        h.inchi_identifier = ''
        OR h.inchi_identifier IS NULL
    );

大查询中WHERE之后的每个字段都必须有一个索引，如果不能放一个索引，那么您的DB模式就不好（例如，如果您在0.01%中，可能是99.99%，那么您已经知道原因了）
What Index you ask ?好吧，你有一些选择，我只解释最有用的：
PRIMARY KEY
是的，这是第一个也是最重要的。只能有一个by table，它也是一个UNIQUE约束，如果有和identifier字段，就用它作为主键，不需要添加一个专用的id int auto_increment约束。
UNIQUE INDEX
如果您的表中已经有一个主键，但在另一个字段中有一些唯一的id，UNIQUE INDEX可以帮助您快速读取/更新该字段（但请放慢插入速度，因为它将检查唯一性）
INDEX，多道
“但是我已经有了一个PK，而且我的字段不是唯一的，所以没有索引？”
在大多数数据库中通常都是这样的，你有一个外文键字段（1-N），你会在上面生成许多JOIN和WHERE，但不能使用前面的索引之一，然后就有了魔力：
INDEX允许您加快查询速度，即使字段中有重复项或空值。
它会减慢插入的速度，但是您将获得SELECT、UPDATE、DELETE、WHERE、JOIN和这个字段。
综合指数
这是一种制作索引的方法。这比前面的更复杂，因为如果希望使用此查询，您确实需要了解查询的工作方式。
[以书面形式]
回到您的查询，这可能是您应该做的（请记住，它可能会减慢某些操作的速度，并且可能需要一些时间来构建）：

    -- speed the null / "" detection
CREATE INDEX idx_hcc_inchi_id_1char
    ON heroku_chemical_chemical (inchi_identifier(1));

    -- uniqueness of name
CREATE UNIQUE INDEX idx_hcc_name_u
    ON heroku_chemical_chemical (name);
CREATE UNIQUE INDEX idx_ncf_name_u
    ON new_compounds_filtered (name);

关于你问题的第二部分：
“我无法将UNIQUE INDEX置于foodb_id上，因为只有5%的行具有FooDB\u ID”
没问题：
ALTER TABLE column allow null（如果我查看您的架构，已经可以）
将空字符串更新为''：NULL
在列上添加一个UPDATE table SET column=NULL WHERE column='';，因为UNIQUE INDEX是允许的

关于mysql - MySQL-如何一点一点而不是一次全部地执行事务(如循环)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40942805/