Всем привет, меня зовут Сяолинь.
Вчера вечером, когда я греб в группе, я увидел, как читатель сказал такую вещь.
Вероятно, когда оператор обновления выполняется онлайн для изменения данных базы данных, условие where не индексируется, что приводит к краху бизнеса, и босс преподал ему урок.
Давайте посмотрим на этот раз:
- Почему происходит такая авария?
- Как можно избежать такой аварии?
В качестве предпосылки следующие случаи основаны на механизме хранения InnoDB, а уровень изоляции транзакции — повторяемое чтение.
Почему происходит такая авария?
Уровень изоляции транзакций по умолчанию механизма хранения InnoDB — «повторяемое чтение», но при этом уровне изоляции, когда несколько транзакций выполняются одновременно, возникает проблема фантомного чтения.Так называемое фантомное чтение относится к непрерывному выполнению в одном и том же Один и тот же оператор запроса дважды, второй оператор запроса может возвращать строки, которых раньше не было.
Таким образом, механизм хранения InnoDB сам реализует блокировки строк и блокирует «пробел» между самой записью и записями с помощью блокировок следующего ключа (комбинация блокировок записей и блокировок промежутков), предотвращая вставку новых записей другими транзакциями между этими записями. запись. , чтобы избежать явления фантомного чтения.
Когда мы выполняем оператор обновления, мы фактически добавляем к записи эксклюзивную блокировку (блокировку X. Если другие транзакции изменяют запись, содержащую эксклюзивную блокировку, она будет заблокирована. Кроме того, эта блокировка не снимается после выполнения оператора обновления, а снимается после завершения транзакции.
В транзакции InnoDB основной единицей блокировки записи является блокировка следующего ключа, но из-за некоторых условий она вырождается в блокировку промежутка или блокировку записи. Расположение блокировки точное, блокировка добавляется в индекс, а не в строку.
Например, если в условии оператора update используется уникальный индекс, блокировка следующего ключа выродится в блокировку записи, то есть будет заблокирована только одна строка записей.
Например, вот таблица базы данных с идентификатором в качестве индекса первичного ключа.
Предположим, что есть две транзакции, выполняемые в следующем порядке:
Видно, что в операторе обновления транзакции A, где эквивалентный запрос, а id — уникальный индекс, блокируется только запись с id = 1. Следовательно, операция обновления транзакции B не будет заблокирована.
но,Если в условии where оператора обновления не используется индекс, будет выполнено полное сканирование таблицы, поэтому ко всем записям будет добавлена блокировка следующего ключа (блокировка записи + блокировка промежутка), что эквивалентно блокировке всей таблицы..
Предположим, что есть две транзакции, выполняемые в следующем порядке:
Как видите, оператор обновления транзакции B на этот раз заблокирован.
Это связано с тем, что в условии where оператора обновления транзакции A нет столбца индекса, и все записи будут заблокированы, то есть этот оператор обновления генерирует 4 блокировки записей и 5 блокировок промежутков, что эквивалентно блокировке всей таблицы. .
Поэтому, когда оператор обновления выполняется для таблицы базы данных с очень большим объемом данных, если индекс не используется, блокировка следующего ключа будет добавлена ко всей таблице, и блокировка будет длиться долгое время, пока не будет транзакция заканчивается. период кромеselect ... from Заявления и другие заявления будут заблокированы и не могут быть выполнены, а бизнес будет стоять в застое Следующее, что вас ждет, это нагоняй начальства.
Можно ли использовать индекс where оператора обновления, чтобы избежать полной блокировки записей таблицы?
Не совсем.
Ключ зависит от процесса выполнения этого оператора.Оптимизатор, наконец, выбирает, выполнять ли сканирование индекса или полное сканирование таблицы.Если выполняется полное сканирование таблицы, записи полной таблицы будут заблокированы..
Как можно избежать такой аварии?
Мы можем преобразовать MySQLsql_safe_updatesУстановите для параметра значение 1, чтобы включить безопасный режим обновления.
Официальное объяснение:
If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. (Specifically, UPDATE statements must have a WHERE clause that uses a key or a LIMIT clause, or both. DELETE statements must have both.) This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is 0.
Грубо говоря, когда для sql_safe_updates установлено значение 1.
Оператор обновления должен соответствовать одному из следующих условий для успешного выполнения:
- Используйте where, и в условии where должен быть индексный столбец;
- лимит использования;
- Используйте where и limit одновременно, в настоящее время в условии where не может быть индексного столбца;
Оператор удаления должен соответствовать одному из следующих условий для успешного выполнения:
- Используйте where, и в условии where должен быть индексный столбец;
- Используйте where и limit одновременно, в настоящее время в условии where не может быть индексного столбца;
Если в условии where есть индексный столбец, но оптимизатор наконец сканирует всю таблицу вместо индекса, мы можем использоватьforce index([index_name])Вы можете указать оптимизатору, какой индекс использовать, чтобы избежать скрытых опасностей, вызванных возможностью блокировки всей таблицы.
Суммировать
Не стоит недооценивать заявление об обновлении. Неправильное использование на производственной машине может привести к стагнации бизнеса или даже к краху.
Когда мы хотим выполнить оператор обновления, убедитесь, что столбец индекса включен в условие where, и подтвердите, является ли оператор сканированием индекса на тестовой машине, чтобы предотвратить блокировку всех записей в таблице из-за сканирует всю таблицу.
Мы можем включить параметр MySQL sql_safe_updates, который может помешать тому, чтобы условие where не включало столбец индекса во время операции обновления.
Если обнаруживается, что даже если столбец индекса столбца включен в условие where, оптимизатор все равно выполняет полное стандартное сканирование, то нам нужно использоватьforce index([index_name])Вы можете указать оптимизатору, какой индекс использовать.
На этом пока все, в следующий раз будь осторожен, не дай начальству снова отругать.