обновление столбца, если его нет в другой таблице [большой набор данных]

#mysql #sql #optimization

Вопрос:

У меня есть две таблицы entries , users в которых есть следующие столбцы

 create table entries (  id int(11) unsigned auto_increment primary key,  user_id int unsigned null,  status enum ('active', 'inactive', 'blocked') )  create index user_id on entries (user_id);  ------------------------  create table users (  id int(11) unsigned auto_increment primary key,  email varchar(255) not null,  name varchar(255) not null,  phone varchar(255) not null )

В таблице 5 миллионов записей users и около 20 миллионов записей в entries таблице, и многие из них имеют висячие user_id значения, что означает user_id указание на несуществующее значение в users таблице.

Я хотел бы обновить эти значения entries как можно более эффективно, не блокируя всю таблицу для обновления в течение многих минут.

Я пробовал использовать пакетные обновления, каждый раз указывая другой статус, т. е.

 UPDATE entries SET user_id = null WHERE user_id IS NOT NULL  AND status = 'active'  AND NOT EXISTS(SELECT id  FROM users  WHERE id = entries.user_id);

но пришлось убить его через пару минут. есть какие-нибудь предложения?

1. Может ли пользователь иметь более одного статуса? Звучит странно, поэтому мне пришлось спросить

2. У пользователей @RiggsFolly нет статуса, только записи, и у них может быть только один статус

3. Извините за мою терминологическую ошибку. Похоже, что вы создаете сирот с помощью этого запроса. Так вот как у вас получилось так много записей по сравнению с пользователями

4. да, к сожалению, это устаревшая система, и мы должны работать с тем, что у нас есть…

5. Разве не было бы полезно в долгосрочной перспективе удалить этих сирот из записей

Ответ №1:

Вы можете обнаружить, что добавление индекса в entries таблицу ускоряет обновление:

 CREATE INDEX idx ON entries (user_id, status);