由于某些原因，使用join进行更新需要9到30个小时

Question

我有两个表，称它们为table_A和table_B。Table_A大约有1000万行。Table_B有3亿行。我在Table_B的X和Y列上创建了索引。

按照我的行数，更新会花这么长时间，这正常吗？考虑到我有一个索引，这对我来说似乎非常长

以下是的外观

表A:

ID   BCODE 
1    A1 
2    B1  
3    C1  
4    D1  
5    F1 

表B:

X    Y    IDX  IDY
A1   D1  
D1   F1  
C1   B1  

Table_B的列"X"one_answers"Y"的值在Table_A的列BCODE中找到。

我运行了两个需要9到30个小时才能完成的更新语句。

 Update Table_B
join table_A a on table_B.X = a.BCODE
set
   table_B.IDX=a.Id ;
Update Table_B
join Table_A aa on table_B.Y = aa.BCODE
set
   table_B.IDY = aa.Id  ;

有没有办法让我加快速度？我应该补充一点，BCODE、X、Y列最多可以有300个字符长的

以下是Explain:的结果

+----+-------------+-----------+------+---------------+-------------+---------+--------------+----------+-------------+
| id | select_type | table     | type | possible_keys | key         | key_len |  ref         | rows     | Extra       |
+----+-------------+-----------+------+---------------+-------------+---------+--------------+----------+-------------+
|  1 | SIMPLE      | a         | ALL  | BCODE         | NULL        | NULL    | NULL         | 10238784 | NULL        |
|  1 | SIMPLE      | table_B   | ref  | relateIndex   | relateIndex | 632     | test.a.BCODE |       15 | Using where |
+----+-------------+-----------+------+---------------+-------------+---------+------------+----------+-------------+
2 rows in set (0.00 sec)

Answer 1

创建覆盖索引：

create index table_a_bcode_id on table_A(bcode, id);

覆盖索引提供了在索引中查找的值，从而避免了对表的访问，从而使仅索引查询成为可能。

您的查询看起来不错，但请尝试组合它们：

update Table_B
left join table_A a1 on a1.BCODE = table_B.X
left join table_A a2 on a2.BCODE = table_B.Y
set table_B.IDX = a1.Id
    table_B.IDY = a2.Id

这样就避免了必须更新该行两次。

Answer 2

从EXPLAIN输出来看，Table_A.BCODE在BCODE上似乎没有索引。如果您想执行JOIN，两个表都应该在JOIN字段上进行索引，否则由于查找的O（N）复杂性，未索引的表会减慢速度。

除此之外，您已经提到这两列都是长达300个字符的字符串。此类列上的索引不是很有效。如果你能找到一种方法来散列这些列中的值，然后通过散列值执行联接，这可以加快速度。

另一方面，对于这么大的表，您应该检查索引是否适合内存。如果他们不这样做，那么MySQL将开始将您的索引交换到磁盘，这将极大地降低速度。

我们可以做一个估计：600（您的索引键长度）x 300M（行数）=200 GB。。。很可能这就是问题所在。

可能的解决方法：尝试对table_B进行分区（例如，通过PK），以便索引的每个部分都可以放入内存（这可能意味着25个分区或更多，假设MySQL实例最多可以使用8GB RAM）。