Ответ 1
Скорее всего, второй оператор UPDATE
блокирует гораздо больше строк, в то время как первый использует уникальный ключ и блокирует только строки, которые он собирается обновить.
Если я SELECT
ID, а затем UPDATE
, используя эти идентификаторы, запрос UPDATE
выполняется быстрее, чем если бы я UPDATE
использовал условия в SELECT
.
Чтобы проиллюстрировать:
SELECT id FROM table WHERE a IS NULL LIMIT 10; -- 0.00 sec
UPDATE table SET field = value WHERE id IN (...); -- 0.01 sec
Вышеуказано, примерно в 100 раз быстрее, чем UPDATE
при тех же условиях:
UPDATE table SET field = value WHERE a IS NULL LIMIT 10; -- 0.91 sec
Почему?
Примечание: столбец a
индексируется.
Скорее всего, второй оператор UPDATE
блокирует гораздо больше строк, в то время как первый использует уникальный ключ и блокирует только строки, которые он собирается обновить.
Два запроса не идентичны. Вы знаете только, что идентификаторы уникальны в таблице.
UPDATE... LIMIT 10 будет обновлять не более 10 записей.
UPDATE... WHERE id IN (SELECT... LIMIT 10) может обновлять более 10 записей, если есть дубликаты идентификаторов.
Я не думаю, что может быть один прямой ответ на ваш вопрос "почему?". без какого-либо анализа и исследований.
Запросы SELECT обычно кэшируются, что означает, что если вы выполняете один и тот же запрос SELECT несколько раз, время выполнения первого запроса обычно больше, чем следующие запросы. Обратите внимание, что это поведение может быть испытано только тогда, когда SELECT тяжел, а не в сценариях, где даже первый SELECT намного быстрее. Таким образом, в вашем примере может быть, что SELECT занял 0.00s из-за кэширования. Запросы UPDATE используют разные предложения WHERE, и, следовательно, вероятность их выполнения различна.
Хотя колонка a
индексируется, но не обязательно, чтобы MySQL использовал индекс при выполнении SELECT или UPDATE. Изучите результаты EXPLAIN. Также см. Вывод SHOW INDEX
и проверьте, читает ли столбец "Комментарий" "отключен" для любых индексов? Вы можете прочитать больше здесь - http://dev.mysql.com/doc/refman/5.0/en/show-index.html и http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html.
Кроме того, если мы игнорируем SELECT в течение некоторого времени и фокусируемся только на запросах UPDATE, очевидно, что они не оба используют одно и то же условие WHERE - первый работает на столбце id
, а последний на a
. Хотя оба столбца индексируются, но это не обязательно означает, что все индексы таблицы работают одинаково. Возможно, что какой-либо индекс более эффективен, чем другой, в зависимости от размера индекса или типа данных индексированного столбца или если он является индексом с одной или несколькими столбцами. Конечно, могут быть и другие причины, но я не эксперт в этом.
Кроме того, я думаю, что второй UPDATE выполняет больше работы в том смысле, что может быть добавлено больше блокировок на уровне строк по сравнению с первым UPDATE. Верно, что оба UPDATES, наконец, обновляют одинаковое количество строк. Но где в первом обновлении это 10 строк, которые заблокированы, я думаю, что во втором UPDATE все строки с a
, поскольку NULL (более 10) заблокированы перед выполнением UPDATE. Возможно, MySQL сначала применяет блокировку, а затем запускает предложение LIMIT для обновления только ограниченных записей.
Надеюсь, что приведенное выше объяснение имеет смысл!
Есть ли у вас составной индекс или отдельные индексы?
Если это составной индекс столбцов id
и a
,
В операторе 2nd update индекс столбца a
не будет использоваться. Причина в том, что используются только самые левые индексы префикса (если только a
- PRIMARY KEY
)
Итак, если вы хотите использовать индекс столбца a
, вам нужно включить id
в предложение WHERE
, а затем id
сначала a
.
Также это зависит от того, какой механизм хранения вы используете, поскольку MySQL индексирует на уровне двигателя, а не на сервере.
Вы можете попробовать следующее:
UPDATE table SET field = value WHERE id IN (...) AND a IS NULL LIMIT 10;
Таким образом, id
находится в самом левом индексе, за которым следует a
Также из ваших комментариев поиск намного быстрее, потому что, если вы используете InnoDB
, обновление столбцов означает, что движок хранения InnoDB
должен будет перемещать индексы на другую страницу node или должен разделяться страницу, если страница уже заполнена, так как InnoDB
хранит индексы в последовательном порядке. Этот процесс ОЧЕНЬ медленный и дорогой и становится еще медленнее, если ваши индексы фрагментированы, или если ваша таблица очень большая
Комментарий Майкла Дж. В. - лучшее описание. Этот ответ предполагает, что a
- это столбец, который не индексируется, и "id" есть.
Предложение WHERE в первой команде UPDATE отключает первичный ключ таблицы, id
Предложение WHERE во второй команде UPDATE работает с неиндексированным столбцом. Это значительно ускоряет поиск столбцов.
Никогда не недооценивайте силу индексов. Таблица будет работать лучше, если индексы будут использоваться правильно, чем таблица, в десятом размере без индексации.
Относительно "MySQL не поддерживает обновление той же таблицы, которую вы выбираете из"
UPDATE table SET field = value
WHERE id IN (SELECT id FROM table WHERE a IS NULL LIMIT 10);
Просто сделайте следующее:
UPDATE table SET field = value
WHERE id IN (select id from (SELECT id FROM table WHERE a IS NULL LIMIT 10));
Принятый ответ кажется правильным, но он неполный, есть большие различия.
Насколько я понимаю, и я не эксперт SQL:
Первый запрос: вы выбираете N строк и ОБНОВЛЯЕТ их с помощью первичного ключа.
Это очень быстро, поскольку у вас есть прямой доступ ко всем строкам на основе максимально возможного индекса.
Второй запрос: UPDATE N строк с использованием LIMIT Это заблокирует все строки и освободится после завершения обновления.
Большая разница в том, что у вас есть СОСТОЯНИЕ RACE в случае 1) и атомное UPDATE в случае 2)
Если у вас есть два или более одновременных вызова в случае 1) запроса, у вас будет ситуация, когда вы выберете ИДЕННЫЙ идентификатор из таблицы. Оба вызова будут одновременно обновлять одни и те же идентификаторы, переписывая друг друга. Это называется "состояние гонки".
Во втором случае, избегая этой проблемы, mysql будет блокировать все строки во время обновления. Если второй сеанс выполняет ту же команду, у него будет время ожидания до тех пор, пока строки не будут разблокированы. Таким образом, никакое состояние гонки не возможно за счет потерянного времени.