Более быстрый способ удалить соответствие строкам?

DELETE FROM a WHERE id IN (SELECT id FROM b)

Попробуйте это:

DELETE QUICK A.* FROM A,B WHERE A.ID=B.ID

Это намного быстрее, чем обычные запросы.

См. Синтаксис: http://dev.mysql.com/doc/refman/5.0 /en/delete.html

Вы выполняете свой подзапрос для 'b' для каждой строки в 'a'.

Попробуйте:

DELETE FROM a USING a LEFT JOIN b ON a.id = b.id WHERE b.id IS NOT NULL;

Попробуйте это:

DELETE a
FROM a
INNER JOIN b
 on a.id = b.id

Использование подзапросов, как правило, медленнее, чем объединений, поскольку они запускаются для каждой записи во внешнем запросе.

Очевидно, что запрос SELECT , который составляет основу вашей операции DELETE , довольно быстрый, поэтому я думаю, что либо ограничение внешнего ключа, либо индексы являются причинами вашего крайне медленного запроса.

Try

SET foreign_key_checks = 0;
/* ... your query ... */
SET foreign_key_checks = 1;

Это отключит проверку внешнего ключа. К сожалению, вы не можете отключить (по крайней мере, я не знаю, как) обновления ключей с помощью таблицы InnoDB. С таблицей MyISAM вы могли бы сделать что-то вроде

ALTER TABLE a DISABLE KEYS
/* ... your query ... */
ALTER TABLE a ENABLE KEYS 

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

Может быть, вам следует перестроить индикаторы перед выполнением такого хью-запроса. Что ж, вам следует периодически перестраивать их.

REPAIR TABLE a QUICK;
REPAIR TABLE b QUICK;

, а затем выполнить любой из вышеперечисленных запросов (т. Е.)

DELETE FROM a WHERE id IN (SELECT id FROM b)

Это то, что я всегда делаю, когда мне приходится работать со сверхбольшими данными (здесь: примерная тестовая таблица с 150000 строками):

drop table if exists employees_bak;
create table employees_bak like employees;
insert into employees_bak 
    select * from employees
    where emp_no > 100000;

rename table employees to employees_todelete;
rename table employees_bak to employees;

В этом случае sql фильтрует 50000 строк в резервная таблица. Каскад запросов выполняется на моей медленной машине за 5 секунд. Вы можете заменить вставку в select вашим собственным фильтром запроса.

Это хитрость для массового удаления в больших базах данных!; =)

Your time of three minutes seems really slow. My guess is that the id column is not being indexed properly. If you could provide the exact table definition you're using that would be helpful.

I created a simple python script to produce test data and ran multiple different versions of the delete query against the same data set. Here's my table definitions:

drop table if exists a;
create table a
 (id bigint unsigned  not null primary key,
  data varchar(255) not null) engine=InnoDB;

drop table if exists b;
create table b like a;

I then inserted 100k rows into a and 25k rows into b (22.5k of which were also in a). Here's the results of the various delete commands. I dropped and repopulated the table between runs by the way.

mysql> DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE a.id=b.id);
Query OK, 22500 rows affected (1.14 sec)

mysql> DELETE FROM a USING a LEFT JOIN b ON a.id=b.id WHERE b.id IS NOT NULL;
Query OK, 22500 rows affected (0.81 sec)

mysql> DELETE a FROM a INNER JOIN b on a.id=b.id;
Query OK, 22500 rows affected (0.97 sec)

mysql> DELETE QUICK a.* FROM a,b WHERE a.id=b.id;
Query OK, 22500 rows affected (0.81 sec)

All the tests were run on an Intel Core2 quad-core 2.5GHz, 2GB RAM with Ubuntu 8.10 and MySQL 5.0. Note, that the execution of one sql statement is still single threaded.

---

Update:

I updated my tests to use itsmatt's schema. I slightly modified it by remove auto increment (I'm generating synthetic data) and character set encoding (wasn't working - didn't dig into it).

Here's my new table definitions:

drop table if exists a;
drop table if exists b;
drop table if exists c;

create table c (id varchar(30) not null primary key) engine=InnoDB;

create table a (
  id bigint(20) unsigned not null primary key,
  c_id varchar(30) not null,
  h int(10) unsigned default null,
  i longtext,
  j bigint(20) not null,
  k bigint(20) default null,
  l varchar(45) not null,
  m int(10) unsigned default null,
  n varchar(20) default null,
  o bigint(20) not null,
  p tinyint(1) not null,
  key l_idx (l),
  key h_idx (h),
  key m_idx (m),
  key c_id_idx (id, c_id),
  key c_id_fk (c_id),
  constraint c_id_fk foreign key (c_id) references c(id)
) engine=InnoDB row_format=dynamic;

create table b like a;

I then reran the same tests with 100k rows in a and 25k rows in b (and repopulating between runs).

mysql> DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE a.id=b.id);
Query OK, 22500 rows affected (11.90 sec)

mysql> DELETE FROM a USING a LEFT JOIN b ON a.id=b.id WHERE b.id IS NOT NULL;
Query OK, 22500 rows affected (11.48 sec)

mysql> DELETE a FROM a INNER JOIN b on a.id=b.id;
Query OK, 22500 rows affected (12.21 sec)

mysql> DELETE QUICK a.* FROM a,b WHERE a.id=b.id;
Query OK, 22500 rows affected (12.33 sec)

As you can see this is quite a bit slower than before, probably due to the multiple indexes. However, it is nowhere near the three minute mark.

Something else that you might want to look at is moving the longtext field to the end of the schema. I seem to remember that mySQL performs better if all the size restricted fields are first and text, blob, etc are at the end.

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

Другой подход - добавить в таблицу удаленный флаг-столбец и настроить другие запросы таким образом, чтобы они принимали это. значение во внимание. Самый быстрый логический тип в mysql - CHAR (0) NULL (true = '', false = NULL). Это была бы быстрая операция, после которой вы можете удалить значения.

Те же мысли, выраженные в выражениях sql:

ALTER TABLE a ADD COLUMN deleted CHAR(0) NULL DEFAULT NULL;

-- The following query should be faster than the delete statement:
UPDATE a INNER JOIN b SET a.deleted = '';

-- This is the catch, you need to alter the rest
-- of your queries to take the new column into account:
SELECT * FROM a WHERE deleted IS NULL;

-- You can then issue the following queries in a cronjob
-- to clean up the tables:
DELETE FROM a WHERE deleted IS NOT NULL;

Если это тоже не то, что вам нужно, вы можете взглянуть на то, что есть в документах mysql. сказать о скорости удаления операторов .

Вы можете включить их позже, если они вам не нужны немедленно.

Другой подход - добавить в таблицу удаленный флаг-столбец и настроить другие запросы таким образом, чтобы они принимали это. значение во внимание. Самый быстрый логический тип в mysql - CHAR (0) NULL (true = '', false = NULL). Это была бы быстрая операция, после которой вы можете удалить значения.

Те же мысли, выраженные в выражениях sql:

ALTER TABLE a ADD COLUMN deleted CHAR(0) NULL DEFAULT NULL;

-- The following query should be faster than the delete statement:
UPDATE a INNER JOIN b SET a.deleted = '';

-- This is the catch, you need to alter the rest
-- of your queries to take the new column into account:
SELECT * FROM a WHERE deleted IS NULL;

-- You can then issue the following queries in a cronjob
-- to clean up the tables:
DELETE FROM a WHERE deleted IS NOT NULL;

Если это тоже не то, что вам нужно, вы можете взглянуть на то, что есть в документах mysql. сказать о скорости удаления операторов .

Вы можете включить их позже, если они вам не нужны немедленно.

Другой подход - добавить в таблицу удаленный флаг-столбец и настроить другие запросы таким образом, чтобы они принимали это. значение во внимание. Самый быстрый логический тип в mysql - CHAR (0) NULL (true = '', false = NULL). Это была бы быстрая операция, после которой вы можете удалить значения.

Те же мысли, выраженные в выражениях sql:

ALTER TABLE a ADD COLUMN deleted CHAR(0) NULL DEFAULT NULL;

-- The following query should be faster than the delete statement:
UPDATE a INNER JOIN b SET a.deleted = '';

-- This is the catch, you need to alter the rest
-- of your queries to take the new column into account:
SELECT * FROM a WHERE deleted IS NULL;

-- You can then issue the following queries in a cronjob
-- to clean up the tables:
DELETE FROM a WHERE deleted IS NOT NULL;

Если это тоже не то, что вам нужно, вы можете взглянуть на то, что есть в документах mysql. сказать о скорости удаления операторов .

Самый быстрый логический тип в mysql - CHAR (0) NULL (true = '', false = NULL). Это была бы быстрая операция, после которой вы можете удалить значения.

Те же мысли, выраженные в выражениях sql:

ALTER TABLE a ADD COLUMN deleted CHAR(0) NULL DEFAULT NULL;

-- The following query should be faster than the delete statement:
UPDATE a INNER JOIN b SET a.deleted = '';

-- This is the catch, you need to alter the rest
-- of your queries to take the new column into account:
SELECT * FROM a WHERE deleted IS NULL;

-- You can then issue the following queries in a cronjob
-- to clean up the tables:
DELETE FROM a WHERE deleted IS NOT NULL;

Если это тоже не то, что вам нужно, вы можете взглянуть на то, что есть в документах mysql. сказать о скорости удаления операторов .

Самый быстрый логический тип в mysql - CHAR (0) NULL (true = '', false = NULL). Это была бы быстрая операция, после которой вы можете удалить значения.

Те же мысли, выраженные в выражениях sql:

ALTER TABLE a ADD COLUMN deleted CHAR(0) NULL DEFAULT NULL;

-- The following query should be faster than the delete statement:
UPDATE a INNER JOIN b SET a.deleted = '';

-- This is the catch, you need to alter the rest
-- of your queries to take the new column into account:
SELECT * FROM a WHERE deleted IS NULL;

-- You can then issue the following queries in a cronjob
-- to clean up the tables:
DELETE FROM a WHERE deleted IS NOT NULL;

Если это тоже не то, что вам нужно, вы можете взглянуть на то, что есть в документах mysql. сказать о скорости удаления операторов .

Кстати, после публикации вышеизложенного в моем блоге Барон Шварц из Percona обратил мое внимание, что в его maatkit уже есть инструмент для этого назначение - мк-архиватор. http://www.maatkit.org/doc/mk-archiver.html .

Скорее всего, это ваш лучший инструмент для работы.

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

I лично сталкивались с необходимостью удаления большого количества строк из одной таблицы, существующих в другой, и, по моему опыту, лучше всего сделать следующее, особенно если вы ожидаете, что будет удалено много строк. Этот метод наиболее важно улучшит задержку подчиненного устройства репликации, так как чем дольше выполняется каждый отдельный запрос мутатора, тем хуже будет задержка (репликация однопоточная).

Итак, вот оно: сначала выполните SELECT, так как отдельный запрос , запомнив идентификаторы, возвращенные в вашем скрипте / приложении, а затем продолжить удаление партиями (скажем, по 50 000 строк за раз). Это позволит достичь следующего:

• каждый из операторов удаления не будет блокировать таблицу слишком долго, таким образом не позволяя задержке репликации выйти из-под контроля . Это особенно важно, если вы полагаетесь на репликацию для получения относительно актуальных данных. Преимущество использования пакетов состоит в том, что если вы обнаружите, что каждый запрос DELETE по-прежнему занимает слишком много времени, вы можете уменьшить его размер, не затрагивая какие-либо структуры БД.
• Еще одним преимуществом использования отдельного SELECT является то, что сам SELECT запуск может занять много времени, особенно если по какой-либо причине он не может использовать лучшие индексы БД. Если SELECT является внутренним по отношению к DELETE, когда весь оператор переносится на ведомые устройства, ему придется выполнить SELECT снова и снова, потенциально отставая от ведомых устройств, потому что ему придется снова делать длинный выбор. Рабское отставание, опять сильно страдает. Если вы используете отдельный запрос SELECT, эта проблема исчезнет, ​​поскольку все, что вы передаете, - это список идентификаторов.

Сообщите мне, есть ли где-то ошибка в моей логике.

Для более подробного обсуждения задержки репликации и способы борьбы с ним, аналогичные этому, см. в MySQL Slave Lag (Delay) Explained And 7 Ways to Battle It

PS Одна вещь, о которой следует быть осторожной, - это, конечно, возможные изменения в таблице между временами SELECT завершается и начинается DELETE. Я позволю вам обрабатывать такие детали, используя транзакции и / или логику, относящуюся к вашему приложению.

похожий на этот, см. MySQL Slave Lag (Delay) Explained And 7 Ways to Battle It

PS Одна вещь, о которой нужно быть осторожной, - это, конечно, возможные изменения в таблице между завершением SELECT и DELETE Начало. Я позволю вам обрабатывать такие детали, используя транзакции и / или логику, относящуюся к вашему приложению.

похожий на этот, см. MySQL Slave Lag (Delay) Explained And 7 Ways to Battle It

PS Одна вещь, о которой нужно быть осторожной, - это, конечно, возможные изменения в таблице между завершением SELECT и DELETE Начало. Я позволю вам обрабатывать такие детали, используя транзакции и / или логику, относящуюся к вашему приложению.