mysql - 数据锁定阻止查询运行 - MySQL InnoDB

Question

我们需要每 24 小时运行一个脚本，计算 1 个大日志表中数据的总和和平均值，并更新 Main_Table (Innodb) 中的数据，该表始终有许多选择/更新查询(大约每秒 1 次)

我在本地数据库上运行了一个测试查询，大约花费了 10 分钟:

Update Main_Table Set Steps = (Select count(*) From Log_Activity Where Log_Activity.MainID = Main_Table.ID)

因为这会更新 Main_Table 中的所有行，所以由于表锁定，所有内容都会陷入实时项目中 - 我必须终止查询进程才能使其再次正常工作 - 没有数据被更新。

我做了一些挖掘，正在考虑以下 3 个选项:

1. 更新临时表中的所有数据，然后以某种方式合并到 Main_Table 中。
2. 在 php 循环中运行脚本，而不是 1 个 MySql 查询(需要花费很多时间)更多时间，但一次更新 1 行)。
3. 我不确定这是否与我们的问题有关，但我读过关于“开始交易...等”的事情，我认为这可能有关联...是吗？

希望有人能够阐明这一点并帮助我们。谢谢

Answer 1

1. 在 Log_Activity.MainID 上创建索引(如果尚不存在):

   ALTER TABLE Log_Activity ADD INDEX (MainID);
   

2. 在 Main_Table.ID 上创建索引(如果尚不存在):

   ALTER TABLE Main_Table ADD INDEX (ID);
   

3. 重写查询以使用联接，而不是相关子查询:

   UPDATE Main_Table JOIN (
     SELECT   MainID ID, COUNT(*) c
     FROM     Log_Activity
     GROUP BY MainID
   ) t USING (ID)
   SET Main_Table.Steps = t.c;