Более быстрый способ удаления совпадающих строк?

Ответ 1

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

Хотя может быть трудно понять, почему DELETEs всех случаев являются самыми медленными, есть довольно простое объяснение. InnoDB - механизм транзакционного хранения. Это означает, что если ваш запрос был прерван на полпути, все записи все равно будут на месте, как будто ничего не произошло. Как только он будет завершен, все исчезнет в одно мгновение. Во время DELETE другие клиенты, подключающиеся к серверу, будут видеть записи до завершения DELETE.

Для достижения этой цели InnoDB использует метод MVCC (Multi Version Concurrency Control). В основном это означает предоставление каждому соединению моментального снимка всей базы данных, как это было при первом запуске транзакции. Для достижения этой цели каждая запись в InnoDB внутренне может иметь несколько значений - по одному для каждого моментального снимка. Это также связано с тем, что COUNTing на InnoDB занимает некоторое время - это зависит от состояния моментального снимка, которое вы видите в это время.

Для вашей транзакции DELETE каждая запись, идентифицированная в соответствии с вашими условиями запроса, помечается для удаления. Поскольку другие клиенты могут одновременно обращаться к данным, они не могут сразу удалить их из таблицы, потому что они должны видеть их соответствующий снимок, чтобы гарантировать атомарность удаления.

Как только все записи были отмечены для удаления, транзакция успешно завершена. И даже тогда они не могут быть немедленно удалены с фактических страниц данных, прежде чем все другие транзакции, которые работали со значением моментального снимка перед вашей транзакцией DELETE, также закончились.

Таким образом, на самом деле ваши 3 минуты не так уж медленны, учитывая тот факт, что все записи должны быть изменены, чтобы подготовить их к удалению безопасным способом. Вероятно, вы будете "слышать" ваш жесткий диск, работающий во время выполнения инструкции. Это вызвано доступом ко всем строкам. Чтобы повысить производительность, вы можете увеличить размер пула буферов InnoDB для своего сервера и попытаться ограничить другой доступ к базе данных при удалении, тем самым также уменьшив количество исторических версий, которые InnoDB должен поддерживать на каждую запись. С дополнительной памятью InnoDB может прочитать вашу таблицу (в основном) в памяти и избежать некоторого времени поиска диска.

Ответ 2

Ваше время в три минуты кажется очень медленным. Я предполагаю, что столбец id не индексируется должным образом. Если бы вы могли указать точное определение таблицы, которое вы используете, это было бы полезно.

Я создал простой python script для создания тестовых данных и запускал несколько разных версий запроса на удаление с тем же набором данных. Здесь мои определения таблиц:

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;

Затем я вставил 100k строк в строки и 25k в b (22.5k из которых также были в a). Здесь приведены результаты различных команд удаления. Я упал и засекретил таблицу между проходами между прочим.

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)

Все тесты проводились на четырехъядерном процессоре Intel Core2 с тактовой частотой 2,5 ГГц, 2 ГБ оперативной памяти с Ubuntu 8.10 и MySQL 5.0. Обратите внимание, что выполнение одного оператора sql по-прежнему однопоточно.

Update:

Я обновил свои тесты, чтобы использовать его схему. Я немного изменил его, удалив автоинкремент (я генерирую синтетические данные), и кодировка набора символов (не работала - не копалась в нем).

Здесь мои новые определения таблиц:

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;

Затем я повторяю те же тесты с 100k строками в и 25k строками в b (и переполнением между прогонами).

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)

Как вы можете видеть, это довольно немного медленнее, чем раньше, вероятно, из-за нескольких индексов. Тем не менее, это не так близко к отметке в три минуты.

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

Ответ 3

Попробуйте следующее:

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

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

Ответ 4

Это то, что я всегда делаю, когда мне приходится работать со сверхбольшими данными (здесь: тестовая таблица с 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 секунд. Вы можете заменить вставку на выбор своим собственным запросом фильтра.

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

Ответ 5

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

Try:

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

Ответ 6

Попробуйте следующее:

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

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

Обратитесь к синтаксису: http://dev.mysql.com/doc/refman/5.0/en/delete.html

Ответ 7

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

Ответ 8

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

REPAIR TABLE a QUICK;
REPAIR TABLE b QUICK;

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

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

Ответ 9

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

Другим подходом будет добавление столбца deleted flag-column в вашу таблицу и корректировка других запросов, чтобы они учитывали это значение. Самый быстрый булев тип в 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 о скорости операторов удаления.

Ответ 10

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

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

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

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

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

Для более подробного обсуждения отставания репликации и способов борьбы с ним, похоже на этот, см. Объяснение MySQL Slave Lag (Delay) и 7 способов борьбы с ним

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

Ответ 11

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

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

Ответ 12

Очевидно, что запрос 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 

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

Ответ 13

Подключить базу данных с помощью терминала и выполнить команду ниже, посмотреть время каждого из них, вы обнаружите, что время удаления 10, 100, 1000, 10000, 100000 записей не умножается.

  DELETE FROM #{$table_name} WHERE id < 10;
  DELETE FROM #{$table_name} WHERE id < 100;
  DELETE FROM #{$table_name} WHERE id < 1000;
  DELETE FROM #{$table_name} WHERE id < 10000;
  DELETE FROM #{$table_name} WHERE id < 100000;

Время удаления 10 тысяч записей не в 10 раз больше, чем удаление 100 тысяч записей. Затем, за исключением того, что поиск путей удаления записей происходит быстрее, существуют некоторые косвенные методы.

1, мы можем переименовать table_name в table_name_bak, а затем выбрать записи из table_name_bak в table_name.

2, Чтобы удалить 10000 записей, мы можем удалить 1000 записей 10 раз. Для этого есть пример ruby ​​ script.

#!/usr/bin/env ruby
require 'mysql2'


$client = Mysql2::Client.new(
  :as => :array,
  :host => '10.0.0.250',
  :username => 'mysql',
  :password => '123456',
  :database => 'test'
)


$ids = (1..1000000).to_a
$table_name = "test"

until $ids.empty?
  ids = $ids.shift(1000).join(", ")
  puts "delete =================="
  $client.query("
                DELETE FROM #{$table_name}
                WHERE id IN ( #{ids} )
                ")
end

Ответ 14

Основной метод удаления нескольких строк строки MySQL в одной таблице через поле id

DELETE FROM tbl_name WHERE id <= 100 AND id >=200; Этот запрос отвечает за удаление согласованного условия между 100 И 200 из определенной таблицы