SQL-Ex blog

Запросы UPDATE - основы

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

UPDATE demo_table 

SET [column] = ['value'] 

WHERE [specific details] [LIMIT x]

Где

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


• [specific details] задает условия, которым должны удовлетворять строки, указанные столбцы в которых будут обновляться. Например, при WHERE id > 500 будут обновляться строки, у которых значение ID превышает 500. Эти условия также называют предикатом запроса.


• [LIMIT x] позволяет обновить только заданное число строк (LIMIT 100 будет обновлять только 100 строк, значение 200 - 200 строк и т.д.). Мы можем также указать сдвиг, откуда следует начинать: LIMIT 100,200 будет обновлять 100 строк, начиная с 200-й строки. Предложение ORDER BY будет упорядочивать результаты соответствующим образом: опция ASC|DESC задает сортировку по возрастанию или убыванию соответственно.

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

Оптимизация запросов UPDATE

Администраторы баз данных MySQL знают, что индексы делают операторы UPDATE медленнее. Однако, хотя это и верно, есть еще кое-что помимо этого. При обновлении данных нам нужно держать в уме следующее:

• Индексирование - индексы оказывают как положительное, так и отрицательное влияние на оператор UPDATE. Это обусловлено тем, что оператор UPDATE логически является двухшаговым процессом. Один шаг - это поиск строки для изменения, другой - изменение данных.
  • Положительное - если вы обновляете небольшое число строк (большинство операторов UPDATE воздействует только на 1 строку, обычно получая доступ к ней по индексированному значению первичного ключа), то индексы улучшают производительность посредством ускорения доступа к строкам, которые требуется обновить.
  
  
  • Отрицательное - индексы могут замедлить операторы UPDATE, когда вы модифицируете проиндексированные столбцы. Это происходит потому, что при наличии индекса запросу UPDATE приходится обновлять не только сами данные, но и данные в индексе - этим обусловлены дополнительные накладные расходы.


• Секции - как правило, секции замедляют операции UPDATE и DELETE, ускоряя в свою очередь операторы SELECT. И они также имеют пару уникальных характерных особенностей:
  
  • Секционирование целых чисел - предположим, что таблицы секционируются по диапазону, и секция A содержит целые числа меньше 1000, а секция B содержит целые числа от 1001 до 2000. Затем предположим, что мы обновляем данные с помощью подобного запроса:
    

    UPDATE `demo_table`
    
    SET `partitioned_column` = 1500;

    
    Этот запрос означает, что если значения в столбце изначально находились в секции A, то они должны теперь переместиться в секцию B - обновление должно перевести секционированный столбец из секции A в секцию B. Кого-то это может смутить, поэтому полезно это иметь в виду.
  
  
  • Остерегайтесь NULL-значений - если вы вставляете строку, содержащую NULL в определенной секции, а затем хотите обновить ваши секции, знайте, что эта строка будет находиться в самой нижней секции. За информацией относительно обработки NULL-значений при секционировании обратитесь к документации.


• Блокировки - если мы накладываем блокировку на таблицу, выполняя ряд последовательных обновлений (мы можем использовать предложения LIMIT или WHERE для этого), а затем снимаем блокировку, скорость выполнения запросов будет, вероятно, много выше, чем выполнение единственного запроса UPDATE, который обновляет сразу много строк. Этот подход может выглядеть, например, так (замените x на имя вашей таблицы, а column на имя вашего столбца. Обновите столько строк, сколько пожелаете):
  

  LOCK TABLE [WRITE|READ] x;
  
  UPDATE x SET column = ‘value’ LIMIT 0,50000;
  
  UPDATE x SET column = ‘value’ LIMIT 50000,100000;
  
  UPDATE x SET column = ‘value’ LIMIT 100000,150000;
  
  ...
  
  UNLOCK TABLE x;

  
  Важно отметить, что блокировки бывают двух типов - таблица может блокироваться либо на запись (запросы INSERT), либо на чтение (запросы SELECT), если указываются предложения WRITE или READ. Блокировка таблиц на чтение или запись означает лишь то, что все операции INSERT или SELECT будут отвергнуты, если наложена конкретная (запись или чтение) блокировка, и все будет ОК, когда блокировка снята (как только таблица разблокирована).
  
  Также важно иметь в виду, что если вы используете движок MyISAM и используете запросы LOCK TABLE в той же манере, ваши операторы UPDATE станут быстрее, поскольку кэш ключей (эквивалент буферного пула InnoDB в MyISAM) только тогда будет сброшен, когда завершатся все запросы UPDATE.


• WHERE и LIMIT - предложения WHERE и (или) LIMIT могут также в значительной мере ускорить производительность запросов UPDATE, т.к. они могут ограничить число обновляемых строк. Никогда не забывайте указывать их, если вам не требуется обновить всю таблицу.


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

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

Использование запросов UPDATE для больших наборов данных

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

1. Выполните такой запрос:
   

   SELECT username,email,registration_date
   
   FROM demo_table
   
   INTO OUTFILE '/tmp/backup.txt';

   
   Будет сделан бэкап, который не вызовет больших накладных расходов при обратном импорте (операторы SELECT INTO OUTFILE устранят хаос, который вызывают запросы INSERT, и резервируют только сырые данные - вам может потребоваться использовать FIELDS TERMINATED TO для задания разделителя, который сообщит MySQL, где оканчивается один столбец и начинается другой. Обратитесь к документации за более подробной информацией.


2. Создайте таблицу, идентичную той, с которой вы работаете, с помощью выполнения оператора SHOW CREATE TABLE, затем установите значение по умолчанию для столбца, который необходимо обновить - здесь мы устанавливаем значение по умолчанию для столбца ip_address:
   
   


3. Обратно импортируйте частичный бэкап без указания столбца со значением по умолчанию в операторе (он будет заполнен по умолчанию). Укажите IGNORE, если в файле имеется больше полей, чем число столбцов в базе данных, и задайте опцию FIELDS TERMINATED BY в конце запроса, если столбцы тоже имеют разделительный символ:
   

   LOAD DATA INFILE '/tmp/backup.txt' IGNORE
   
   INTO TABLE users (username,email,registration_date);

   
   

Такой подход исключительно полезен в случаях, когда требуется обновить огромные объемы данных одним и тем же значением: такая операция всегда будет значительно быстрее, чем выполнение запроса UPDATE, поскольку при этом MySQL будет считать, что столбец уже заполнен по умолчанию. Кроме того, т.к. LOAD DATA INFILE способен пропустить определенные операции, имеющие высокие накладные расходы, миллионы строк можно обновить за несколько секунд. За подробностями обратитесь к документации MySQL или к нашему обсуждению LOAD DATA INFILE.

ALTER или UPDATE в MySQL

Вышеприведенный совет может стать хорошей отправной точкой для тех, кто часто обновляет данные. Однако всегда имейте в виду, что новые проблемы тоже возникают. Например, знаете ли вы, что ALTER и UPDATE - это два разных запроса, один из которых обновляет данные, а другой обновляет структуру, включая столбцы (изменение типов данных, имен и т.д.)?

Вот почему вам необходимо познакомиться также с ALTER: читателям данного блога этот запрос не должен вызывать особых проблем, но лицам, кто не вполне знаком с тем, что происходит за кадром, следует всегда помнить, что когда выполняется ALTER, создаются копии таблицы. ALTER работает примерно так (относительно этого примера мы будем предполагать, что модифицируемая таблицы называется "A", а новая таблица - "B"):

• Создается копия таблицы A - назовем ее B.


• Все данные из таблицы A копируются в таблицу B.


• Все необходимые операции (изменения) выполняются над таблицей B.


• Таблица B меняется местами с таблицей A.


• Таблица B уничтожается.

В некоторых случаях описанные выше шаги являются главной причиной происходящего с хранилищем - некоторые разработчики быстро выясняют, что им "не хватает пространства по неизвестным причинам" в процессе выполнения запроса ALTER, а вот и причина.

Запросы UPDATE в MySQL

Широко известно, что MyISAM устарел, но если вы обнаружите, что используете MyISAM вместо InnoDB или XtraDB для ваших запросов COUNT(*) (MyISAM хранит число строк в своих метаданных, что не так для InnoDB) взгляните на следующие установки:

• key_buffer_size эквивалентна innodb_buffer_pool_size - чем больше это значение, тем быстрей завершаются операторы UPDATE.


• Если вы используете LOAD DATA INFILE для обновления данных, обратите внимание на параметр bulk_insert_buffer_size. Поскольку MyISAM использует этот параметр для ускорения LOAD DATA INFILE, рекомендуется увеличить его размер. Этот параметр также используется всякий раз, когда выполняется оператор ALTER. Как объяснялось выше, такие операторы не эквивалентны запросам UPDATE, но они могут использоваться для изменения имен столбцов, а не данных в них.

Если вы должны выполнить операции UPDATE на MyISAM, вам следует беспокоиться об обновлении строк значением, превышающим их указанную длину, поскольку документация MySQL утверждает, что это может привести к расщеплению строки и потребует от вас время от времени выполнять запросы OPTIMIZE TABLE. Наконец, рассмотрите возможность перехода на движок хранилища InnoDB или XtraDB, т.к. MyISAM подходит только для выполнения простых запросов COUNT(*) из-за хранения числа строк этим хранилищем в своих метаданных, что приводит к быстрому получению результата, чего не может сделать InnoDB.

Заключение

Итак, обновление данных - это не быстрая наука; документация MySQL утверждает, что все операторы обновления могут быть оптимизированы точно так же, как и запросы SELECT, только с накладными расходами на запись плюс дополнительными записями, если модифицируются проиндексированные столбцы. Все приведенные выше советы исходят из этого - индексы замедляют операторы UPDATE. Получение данных, когда база данных не испытывает пиковых нагрузок является хорошим решением, отложенные обновления и выполнение сразу многих обновлений позже тоже хороший вариант, ну и LOAD DATA INFILE значительно быстрей, чем INSERT INTO.

Ссылки по теме

1. Оператор UPDATE
2. Введение в оператор UPDATE в MySQL
3. Резервирование в MySQL: физические и логические резервные копии
4. Оптимизация запросов в MySQL: оптимизация чтений
5. Как секционировать таблицы MySQL