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



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

цель состоит в том, чтобы удалить все строки в таблице A, которые имеют соответствующий идентификатор в таблице B.

в настоящее время я делаю следующее:

DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);

есть приблизительно 100K строк в таблице a и около 22K строк в таблице b. Столбец " id " является PK для обеих таблиц.

этот оператор занимает около 3 минут для запуска на моем тестовом поле-Pentium D, XP SP3, 2GB ram, MySQL 5.0.67. Это кажется мне медленным. Может быть, это и не так, но я надеялся ускорить события. Есть ли лучший / более быстрый способ сделать это?


EDIT:

некоторые дополнительные сведения, которые могут быть полезны. Таблицы A и B имеют ту же структуру, что и я сделал следующее Для создания таблицы Б:

CREATE TABLE b LIKE a;

таблица a (и, следовательно, таблица b) имеет несколько индексов, чтобы помочь ускорить запросы, которые сделаны против него. Опять же, я относительный новичок в работе с БД и все еще учусь. Я не знаю, насколько это влияет, если таковые имеются, на вещи. Я предполагаю, что это действительно имеет эффект, поскольку индексы должны быть очищены тоже, не так ли? Мне также было интересно, есть ли какие-либо другие настройки БД, которые могут повлиять на скорость.

кроме того, я использую INNO ДЕЦИБЕЛ.


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

таблица A имеет структуру, похожую на эту (я немного санировал это):

DROP TABLE IF EXISTS `frobozz`.`a`;
CREATE TABLE  `frobozz`.`a` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `fk_g` 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,
  PRIMARY KEY  USING BTREE (`id`),
  KEY `idx_l` (`l`),
  KEY `idx_h` USING BTREE (`h`),
  KEY `idx_m` USING BTREE (`m`),
  KEY `idx_fk_g` USING BTREE (`fk_g`),
  KEY `fk_g_frobozz` (`id`,`fk_g`),
  CONSTRAINT `fk_g_frobozz` FOREIGN KEY (`fk_g`) REFERENCES `frotz` (`g`)
) ENGINE=InnoDB AUTO_INCREMENT=179369 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

Я подозреваю, что часть проблемы заключается в том, что существует ряд индексов для этой таблицы. Таблица B похожа на таблицу B, хотя она содержит только столбцы id и h.

кроме того, результаты профилирования являются следует:

starting 0.000018
checking query cache for query 0.000044
checking permissions 0.000005
Opening tables 0.000009
init 0.000019
optimizing 0.000004
executing 0.000043
end 0.000005
end 0.000002
query end 0.000003
freeing items 0.000007
logging slow query 0.000002
cleaning up 0.000002

решить

Спасибо за все ответы и комментарии. Они определенно заставили меня задуматься о проблеме. Слава dotjoe для того, чтобы заставить меня отойти от проблемы, задав простой вопрос "есть ли ссылки на другие таблицы a.id"

проблема заключалась в том, что в таблице A был триггер DELETE, который вызывал хранимую процедуру для обновления двух других таблиц, C и D. таблица C имела FK назад к a.id и после выполнения некоторых вещей, связанных с этим идентификатором в хранимой процедуре, у него был оператор,

DELETE FROM c WHERE c.id = theId;

Я посмотрел в объясните заявление и переписал это как,

EXPLAIN SELECT * FROM c WHERE c.other_id = 12345;

Итак, я мог видеть, что это делает, и это дало мне следующую информацию:

id            1
select_type   SIMPLE
table         c
type          ALL
possible_keys NULL
key           NULL
key_len       NULL
ref           NULL
rows          2633
Extra         using where

это сказало мне, что это была болезненная операция, и поскольку она будет вызвана 22500 раз (для данного набора удаляемых данных), это была проблема. Однажды Я создал индекс на этом столбце other_id и повторно объяснил, я получил:

id            1
select_type   SIMPLE
table         c
type          ref
possible_keys Index_1
key           Index_1
key_len       8
ref           const
rows          1
Extra         

гораздо лучше, на самом деле очень здорово.

я добавил, что Index_1 и мое время удаления соответствуют времени, сообщенному mattkemp. Это была действительно тонкая ошибка с моей стороны из-за того, что в последнюю минуту появилась дополнительная функциональность. Оказалось, что большинство предложенных альтернативных операторов DELETE / SELECT, как Даниил указано, завершилась до принятия по существу такое же количество времени и как soulmerge упоминалось, что заявление было в значительной степени лучшим, что я собирался построить, основываясь на том, что мне нужно было сделать. Как только я предоставил индекс для этой другой таблицы C, мои удаления были быстрыми.

после смерти:
Из этого упражнения были извлечены два урока. Во-первых, ясно, что я не использовал силу инструкции EXPLAIN, чтобы получить лучшее представление о влиянии моего SQL запросы. Это ошибка новичка, так что я не собираюсь бить себя за это. Я буду учиться на этой ошибке. Во-вторых, оскорбительный код был результатом менталитета "сделать это быстро", и неадекватный дизайн/тестирование привели к тому, что эта проблема не появилась раньше. Если бы я создал несколько значительных наборов тестовых данных для использования в качестве тестового ввода для этой новой функциональности, я бы не тратил свое время и Ваше. Мое тестирование на стороне БД не хватало глубины, которую имеет Моя сторона приложения. Сейчас У меня есть возможность улучшить.

ссылка: объясните заявление

146   14  

14 ответов:

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

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

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

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

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

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

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

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

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 строк в a и 25k строк в b (22.5 k из которых также были в 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 по-прежнему является однопоточным.


обновление:

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

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

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 строками в A и 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)

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

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

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

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

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

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

что это трюк, чтобы выполнить массовое удаление на больших базах данных!;=)

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

попробуй:

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

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

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

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

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

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

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

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

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

Дайте мне знать, если есть ошибка в моей-то логике.

для более подробного обсуждения задержки репликации и способов борьбы с ней, аналогично этому, см. MySQL Slave Lag (Задержка) Объяснил И 7 Способов Борьбы С Ним

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

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

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

REPAIR TABLE a QUICK;
REPAIR TABLE b QUICK;

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

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

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

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

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

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

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

попробовать

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 

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

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

#!/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

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

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

    Ничего не найдено.

Добавить ответ:
Отменить.