我想弄清楚为什么我们之一
迁移脚本永远在我们正在尝试进行更新
从另一个表连接以获取相关的数据.
每个表(A,B)有大约100,000行.
# now populate the ACHIEVEMENT_INSTANCE.OBJECTIVE_INSTANCE_ID
update A a, B b
set a.INSTANCE_ID = b.INSTANCE_ID
where a.ID = b.ID;
看起来我们正在处理创建一些的INNER JOIN
笛卡尔积的类型在2表100,000 100,000之间
正在采取永远(可能很长时间).
根据MySQL更新默认使用内部联接不确定我们是否
可以使用一些其他类型的JOIN,它们不会那么糟糕.
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
The table_references clause lists the
tables involved in the join. Its
syntax is described in Section
12.2.8.1, “JOIN Syntax”. Here is an example: UPDATE items,month SET
items.price=month.price WHERE
items.id=month.id; The preceding
example shows an inner join that uses
the comma operator, but multiple-table
UPDATE statements can use any type of
join allowed in SELECT statements,
such as LEFT JOIN.
解决方法:
正如Greg在评论中所述:
Do you have an index on a.ID and b.ID?
我们没有这些列的索引.一旦我们添加它们,查询花了30秒:
create index id_idx on A(id);
create index id_idx on B(id);
标签:mysql,database,sql,performance
来源: https://codeday.me/bug/20190627/1301430.html