mysql ВЫБЕРИТЕ против ОБНОВЛЕНИЯ производительности с индексом




performance (6)

Если я SELECT идентификаторы, а затем 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

Зачем?

Примечание: столбец индексируется.


У вас есть составной индекс или отдельные индексы?

Если это составной индекс id и столбцов,

Во втором операторе обновления индекс столбца не будет использоваться . Причина в том, что используются только самые левые префиксные индексы (если только a является PRIMARY KEY )

Поэтому, если вы хотите, чтобы индекс столбца использовался, вам нужно включить id в ваше WHERE , сначала с id затем a .

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

Вы можете попробовать это:

UPDATE table SET field = value WHERE id IN (...) AND a IS NULL LIMIT 10;

При этом этот id находится в самом левом указателе, за которым a

Кроме того, по вашим комментариям поиск выполняется намного быстрее, потому что, если вы используете InnoDB , обновление столбцов будет означать, что InnoDB хранения InnoDB должен будет переместить индексы на другой узел страницы или разделить страницу, если страница уже заполнена, InnoDB хранит индексы в последовательном порядке. Этот процесс ОЧЕНЬ медленный и дорогой, и становится еще медленнее, если ваши индексы фрагментированы , или если ваша таблица очень большая


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

Запросы SELECT обычно кэшируются, что означает, что если вы выполняете один и тот же запрос SELECT несколько раз, время выполнения первого запроса обычно больше, чем следующие запросы. Обратите внимание, что такое поведение может наблюдаться только в тех случаях, когда SELECT тяжел, а не в тех случаях, когда даже первый SELECT намного быстрее. Таким образом, в вашем примере, возможно, SELECT занял 0,00 с из-за кэширования. В запросах 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 . Хотя оба столбца проиндексированы, но это не обязательно означает, что все индексы таблиц работают одинаково. Возможно, что какой-то индекс более эффективен, чем другой, в зависимости от размера индекса или типа данных индексированного столбца или от того, является ли он индексом из одного или нескольких столбцов. Конечно, могут быть и другие причины, но я не эксперт в этом.

Кроме того, я думаю, что второе ОБНОВЛЕНИЕ выполняет больше работы в том смысле, что может поставить больше блокировок на уровне строк по сравнению с первым ОБНОВЛЕНИЕМ. Это правда, что оба ОБНОВЛЕНИЯ, наконец, обновляют одинаковое количество строк. Но если в первом обновлении заблокировано 10 строк, я думаю, что во втором обновлении UPDATE все строки со значением NULL (более 10) заблокированы перед выполнением обновления. Возможно, MySQL сначала применяет блокировку, а затем запускает предложение LIMIT для обновления только ограниченных записей.

Надеюсь, приведенное выше объяснение имеет смысл!


Комментарий Michael JV - лучшее описание. В этом ответе предполагается, a столбец не проиндексирован, а идентификатор -.

Предложение WHERE в первой команде UPDATE отрабатывает первичный ключ таблицы, id

Предложение WHERE во второй команде UPDATE отрабатывает неиндексированный столбец. Это делает обнаружение обновляемых столбцов значительно медленнее.

Никогда не стоит недооценивать силу индексов. Таблица будет работать лучше, если индексы используются правильно, чем таблица, составляющая десятую часть размера без индексации.


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

Насколько я понимаю, и я не эксперт по SQL:

Первым запросом вы ВЫБИРАЕТЕ N строк и ОБНОВЛЯЕТЕ их, используя первичный ключ.
Это очень быстро, поскольку у вас есть прямой доступ ко всем строкам на основе максимально быстрого индекса.

Во втором запросе вы ОБНОВЛЯЕТЕ N строк, используя LIMIT, который блокирует все строки и освобождает снова после завершения обновления.

Большая разница в том, что у вас есть РАСОВОЕ СОСТОЯНИЕ в случае 1) и атомное ОБНОВЛЕНИЕ в случае 2)

Если у вас есть два или более одновременных вызова запроса случая 1), у вас возникнет ситуация, когда вы выбираете те же идентификаторы из таблицы. Оба вызова одновременно обновляют одинаковые идентификаторы, перезаписывая друг друга. Это называется «состояние гонки».

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


Два запроса не идентичны. Вы только знаете, что идентификаторы уникальны в таблице.

ОБНОВЛЕНИЕ ... LIMIT 10 обновит не более 10 записей.

ОБНОВЛЕНИЕ ... ГДЕ идентификатор IN (SELECT ... LIMIT 10) может обновить более 10 записей, если есть повторяющиеся идентификаторы.


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







performance