Блокировка данных препятствует выполнению запроса - 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. Я не уверен, имеет ли это отношение к нашей проблеме, но я прочитал кое-что о "START TRANSACTION... и т. Д.", И я думаю, что это может быть связано... не так ли?

Надеюсь, что кто-то может пролить свет на это и помочь нам. Спасибо

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;