#mysql #sql
Вопрос:
Сегодня у нас была серьезная проблема с производительностью, вызванная этим точным запросом (с разными именами, конечно).:
UPDATE foo LEFT JOIN bar ON bar.foo_id = foo.id SET foo.flag = 1 WHERE bar.foo_id IS NULL; Программное обеспечение является сторонним, и мы не писали код или схему базы данных.
foo имеет 42 тыс. строк, bar имеет 130 тыс. Частность foo.id происходила очень часто в bar.foo_id . ( bar является таблицей отношений, реализующей отношение m:n между foo и qux . Почти каждая строка qux будет иметь отношение к определенной foo строке, а затем к нескольким другим, уникальным для нее. Там может быть больше дубликатов, скорее всего, не важных.) Существует указатель на foo.id . bar.foo_id является частью индекса, также содержащего bar.qux_id и не связанного bar.text .
Запрос выполнялся в течение 55 минут на 100% процессоре db.t3.medium экземпляра, использующего Aurora MySQL 5.7. В slowlog указано Rows_examined: 5719954827 .
Может ли кто-нибудь объяснить, в чем именно причина этого числа и, как следствие, низкой производительности? 42k * 130k близко к этому, так что, вероятно, это как-то связано с этим.
В нашем случае мы могли бы просто GROUP BY foo_id и затем присоединиться к результату, вместо bar того, чтобы решать проблему. Однако это будет невозможно при любых обстоятельствах — и я не думаю, что создание индекса bar.foo_id должно иметь эффект, поскольку NULL значения могут возникать только из-за JOIN оператора.
EXPLAIN Результат выглядит следующим образом:
id|select_type|table |partitions|type |possible_keys|key |key_len|ref|rows |filtered|Extra | -- ----------- ----------------- ---------- ----- ------------- --------------------- ------- --- ------ -------- ------------------------ 1|UPDATE |foo | |ALL | | | | | 42921| 100.0| | 1|SIMPLE |bar | |index| |uidx_qux_foo |780 | |129465| 10.0|Using where; Using index| Насколько мне известно, EXPLAIN PLAN он недоступен в MySQL.
Комментарии:
1. Ваш запрос имеет запах. В баре должен быть пк, поэтому вы должны проверять bar.pk или любой другой ненулевой столбец.
2. Меня конкретно интересует причина, а не решение — как я уже сказал, мы не писали код, и он уже улучшен без изменения схемы. И в баре действительно есть ПК — я не вижу, как это здесь поможет. Цель запроса-пометить строки в foo, на которые нет ссылок в qux, поэтому, глядя на эти несколько битов кода, они должны были просто использоваться ПРИ УДАЛЕНИИ.
3. Это можно было бы написать лучше, используя
not exists4. Посмотрите (и напишите в своем вопросе) ПЛАН ОБЪЯСНЕНИЙ, который должен показать, что происходило
5. Как вы заметили, очень большое количество проверенных строк приближается к произведению ваших двух таблиц. Это связано с полным сканированием таблицы, вызванным отсутствием индекса на bar.foo_id
Ответ №1:
Вы записываете поля только в одну таблицу с подходящим названием foo , поэтому напишите свой запрос , чтобы искать/сканировать только один раз в этой таблице:
update foo set flag = 1 where ... Вместо этого то, что вы написали, является продуктом ваших двух (трех?) таблицы, а затем задает значение для каждой из результирующих строк. Это бесполезно расточительно, так как, опять же, вы хотите указывать данные в своей foo таблице не чаще одного раза в строке.
И как только вы исправите свою общую логику, у вас останется наиболее типичный шаблон доступа к базе данных: предоставление правильных индексов для вашего select оператора (т. Е. части после where выше). Простое explain plan должно показать вам, в чем именно заключается проблема, если таковая имеется. Ваш пост здесь совершенно не поможет.
Ответ №2:
Rows_examined: 5719954827 это сильный показатель.
Измените запрос на это «полу-соединение». Ему не нужно будет повторно устанавливать foo.флаг, но он будет выполнять int только один раз за строку в foo.
UPDATE foo SET foo.flag = 1 WHERE NOT EXISTS ( SELECT 1 FROM bar WHERE bar.foo_id = foo.id ); Это, вероятно, даст вам что-то вроде «проверено 84 тысячи строк» (84 тысячи = 2*42 тысячи).
И bar действительно нуждается INDEX в том, чтобы начать foo_id с.
Зачем вам нужен этот флаг? Зачем вам нужно менять сразу много строк? Разве вы не можете разработать приложение каким-то другим способом?
Для дальнейшего обсуждения, пожалуйста, предоставьте SHOW CREATE TABLE .