我正在规范化数据库，需要运行约630k个更新。以下是我的表格的基本结构：
域
身份证件
名称
统计
域
域id
以前，数据库没有域表，域存储在多个表中，有时作为列表（JSON文本）。我将每个域迁移到domains表，现在需要建立与stats表的关系，该表有domain列。我添加了domain_id列，并尝试以某种方式更新它以匹配id表中域的domains。stats表有23m多行，具有约630k个唯一域（统计数据是每小时一次）。我试过运行for each，但每个域大约需要2秒，加上大约14天运行所有域。
这是我目前的代码：
首先，我从stats表中找到domains表中缺少的所有域，并将它们保存到domains表中。
$statDomains = Stat::select('domain')->groupBy('domain')->lists('domain');
$domains = [];
foreach(array_chunk($statDomains , 1000) as $domains1k){
$domains = array_merge($domains, Domain::whereIn('name', $domains1k)->lists('name'));
}
$missingDomains = [];
foreach(array_diff($statDomains , $domains) as $missingDomain){
$missingDomains[] = ['name' => $missingDomain];
}
if(!empty($missingDomains)){
Domain::insert($missingDomains);
}
接下来，我从
domains表中获取stats表中存在的所有域，并用该域更新stats表中的所有行。$domains = [];
foreach(array_chunk($statDomains, 1000) as $domains1k){
$domains +=Domain::whereIn('name', $domains1k)->lists('name', 'id');
}
foreach($domains as $key => $domain){
Stat::where('domain', $domain)->update(['domain_id' => $key]);
}
我希望有一些雄辩的东西，查询生成器或只是原始的SQL，这将做更新更快（最多两个小时？）我做了一些谷歌搜索，发现了类似的问题，但无法适用于我的案件。
编辑
我正在运行建议的解决方案。与此同时，我发现我的迁移过程中还有两部分需要大约50分钟。在第一个例子中，我有一个表
domain_lists。它有一个文本列domains和JSON编码的域。我正在将这些域移到domain表并在domain_lists_domains_map表上创建记录。代码如下：foreach(DomainList::all() as $domainList){
$attach = [];
$domains = json_decode($domainList->domains, true);
foreach($domains as $domain){
$model = Domain::where('name', '=', $domain)->first();
if(is_null($model) && !is_null($domain)){
$model = new Domain();
$model->name = $domain;
$model->save();
}
if(!is_null($model)){
$attach[] = $model->id;
}
}
if(!empty($attach)){
foreach(array_chunk(array_unique($attach), 1000) as $attach1k){
$domainList->domains()->attach($attach1k);
}
}
}
我已经注意到，我可能应该先找到所有唯一的域并将它们插入到domains表中，但是给出了前一个问题的解决方案，我觉得可能有一种更好的方法在raw SQL中完成这一切。第二部分非常相似，我可能可以通过查看第一部分的代码来找出解决方法。该表是categories，它还有一个带有JSON编码域的domains文本列。非常感谢您的帮助。
编辑2
Hees查询，我将现有的表复制到一个新的带有“ccc>列”的表中：
CREATE TABLE "stats_new" AS SELECT
"s"."domain",
"d"."id" AS "domain_id"
FROM
"stats" "s"
JOIN "domains" "d" ON ("s"."domain" = "d"."name")
最佳答案
原始SQL应该快几个数量级。
第一步：INSERT
将所有域名插入表domains中，除非它们已经存在：
INSERT INTO domains (name)
SELECT DISTINCT s.domain
FROM stats s
LEFT JOIN domains d ON d.name = s.domain
WHERE d.name IS NULL;
Select rows which are not present in other table
如果您有并发写访问，则可能存在竞争条件。最简单的解决方案是专门针对事务lock the table
domains。否则，在操作的中途可能会遇到一个唯一的冲突，因为并发事务在其间提交了相同的域名。所有的事情都会卷土重来。BEGIN;
LOCK TABLE domains IN EXCLUSIVE MODE;
INSERT INTO domains (name)
SELECT DISTINCT s.domain
FROM stats s
LEFT JOIN domains d ON d.name = s.domain
WHERE d.name IS NULL;
COMMIT;
domains.name应该是UNIQUE。该约束通过列上的索引实现，这将有助于下一步的性能。How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
Does a Postgres UNIQUE constraint imply an index?
第二步：
UPDATE要更新某些行而不是全部行，请执行以下操作：
全部更新
domain_id使其成为domains.name的外键。但是不要使用相关的子查询，使用
UPDATE with a FROM clause。快多了。UPDATE stats s
SET domain_id = d.id
FROM domains d
WHERE d.name = s.domain
AND domain_id IS NULL; -- assuming existing ids are correct.
然后您可以删除现在多余的列：
ALTER TABLE stats DROP column domain;
那是非常便宜的。该列在系统目录中标记为“已停用”。在更新或清空行之前，不会删除实际的列值。
为了进一步提高性能，请直接删除操作不需要的所有索引，然后在同一事务中创建它们。
或者，要成批更新n行：
How to mark certain nr of rows in table on concurrent access
或者，由于您在注释中澄清了您正在更新所有行，因此如果约束和访问模式允许的话，创建一个新表要便宜得多。
创建一个全新的表，删除旧表并重命名新表：
like @Tim3880 also suggested
或者，如果需要保留现有表（由于并发访问或其他限制）：
Best way to populate a new column in a large table?
旁白：不要使用非描述性术语，如
stats.domain或name作为列名这是一种普遍的反模式。架构实际上应该类似于：CREATE TABLE domain (
domain_id serial PRIMARY KEY
, domain text UNIQUE NOT NULL -- guessing it should be UNIQUE
);
CREATE TABLE stats (
stats_id serial PRIMARY KEY
, domain_id int REFERENCES domain
-- , domain text -- can be deleted after above normalization.
);
关于php - 将列更新为联接列中的值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30223839/