Почему пакетные вставки / обновления быстрее? Как работают пакетные обновления?
Почему пакетные вставки быстрее? Это потому, что накладные расходы на подключение и настройку для вставки одной строки одинаковы для набора строк? Какие еще факторы ускоряют пакетную вставку?
Как работают пакетные обновления? Предполагая, что таблица не имеет ограничений уникальности, операторы вставки на самом деле не влияют на другие операторы вставки в пакете. Однако во время пакетных обновлений обновление может изменить состояние таблицы и, следовательно, может повлиять на результат других запросов на обновление в пакете.
Я знаю, что запросы на пакетную вставку имеют синтаксис, в котором у вас есть все значения вставки в одном большом запросе. Как выглядят запросы на пакетное обновление? Например, если у меня есть отдельные запросы на обновление формы:
update <table> set <column>=<expression> where <condition1>
update <table> set <column>=<expression> where <condition2>
update <table> set <column>=<expression> where <condition3>
update <table> set <column>=<expression> where <condition4>
Что происходит, когда они используются в партии. Как будет выглядеть отдельный запрос?
Являются ли пакетные вставки и обновления частью стандарта SQL?
4 ответа
Я искал ответ на ту же тему об обновлении "навальный / пакетный". Люди часто описывают проблему, сравнивая ее с предложением вставки с несколькими наборами значений ("объемная" часть).
INSERT INTO mytable (mykey, mytext, myint)
VALUES
(1, 'text1', 11),
(2, 'text2', 22),
...
Четкий ответ все еще избегал меня, но я нашел решение здесь: http://www.postgresql.org/docs/9.1/static/sql-values.html
Чтобы было понятно:
UPDATE mytable
SET
mytext = myvalues.mytext,
myint = myvalues.myint
FROM (
VALUES
(1, 'textA', 99),
(2, 'textB', 88),
...
) AS myvalues (mykey, mytext, myint)
WHERE mytable.mykey = myvalues.mykey
Он обладает тем же свойством, что является "объемным", то есть содержит много данных с одним оператором.
Почему пакетные вставки быстрее?
По многим причинам, но основными из них являются следующие:
- Запрос не нужно повторять.
- Значения передаются за один прием на сервер
- Команды находятся внутри одной транзакции
Это потому, что накладные расходы на подключение и настройку для вставки одной строки одинаковы для набора строк?
Частично да, см. Выше.
Как работают пакетные обновления?
Это зависит от RDBMS,
В Oracle Вы можете передать все значения в виде коллекции и использовать эту коллекцию в виде таблицы в JOIN,
В PostgreSQL а также MySQLВы можете использовать следующий синтаксис:
INSERT
INTO mytable
VALUES
(value1),
(value2),
…
Вы также можете подготовить запрос один раз и вызвать его в каком-то цикле. Обычно есть способы сделать это в клиентской библиотеке.
Предполагая, что таблица не имеет ограничений уникальности, операторы вставки на самом деле не влияют на другие операторы вставки в пакете. Но во время пакетных обновлений обновление может изменить состояние таблицы и, следовательно, может повлиять на результат других запросов на обновление в пакете.
Да, и вы можете или не можете извлечь выгоду из этого поведения.
Я знаю, что запросы на пакетную вставку имеют синтаксис, в котором у вас есть все значения вставки в одном большом запросе. Как выглядят запросы на пакетное обновление?
В Oracle, вы используете коллекцию в соединении:
MERGE
INTO mytable
USING TABLE(:mycol)
ON …
WHEN MATCHED THEN
UPDATE
SET …
В PostgreSQL:
UPDATE mytable
SET s.s_start = 1
FROM (
VALUES
(value1),
(value2),
…
) q
WHERE …
В других статьях объясняется, почему массовые операторы быстрее и как это делать с буквальными значениями.
Я думаю, что важно знать, как это сделать с заполнителями. Отказ от использования заполнителей может привести к появлению гигантских командных строк, цитированию / удалению ошибок и, как следствие, к приложениям, склонным к внедрению SQL.
Массовая вставка с заполнителями в PostgreSQL >= 9.1
Чтобы вставить произвольное количество строк в таблицу "mytable", состоящую из столбцов "col1", "col2" и "col3", все в одном получено (один оператор, одна транзакция):
INSERT INTO mytable (col1, col2, col3)
VALUES (unnest(?), unnest(?), unnest(?))
Вы должны предоставить три аргумента для этого утверждения. Первый должен содержать все значения для первого столбца и так далее. Следовательно, все аргументы должны быть списками / векторами / массивами одинаковой длины.
Массовое обновление с заполнителями в PostgreSQL >= 9.1
Допустим, ваш стол называется "mytable". Он состоит из столбцов "ключ" и "значение".
update mytable
set value = data_table.new_value
from
(select unnest(?) as key, unnest(?) as new_value) as data_table
where mytable.key = data_table.key
Я знаю, это нелегко понять. Это похоже на запутанный SQL. С другой стороны: он работает, он масштабируется, он работает без объединения строк, он безопасен и невероятно быстр.
Вы должны предоставить два аргумента для этого утверждения. Первым должен быть список / вектор / массив, содержащий все значения для столбца "ключ". Конечно, второй должен содержать все значения для столбца "значение".
Если вы достигнете предела размера, возможно, вам придется COPY INTO ... FROM STDIN (PostgreSQL).
При пакетном обновлении база данных работает с набором данных, при построчном обновлении она должна выполнять ту же команду, что и раз, когда есть строки. Таким образом, если вы вставляете миллион строк в пакет, команда отправляется и обрабатывается один раз, а в построчном обновлении она отправляется и обрабатывается миллион раз. Именно поэтому вы никогда не хотите использовать курсор в SQL Server или коррелированный подзапрос.
Пример обновления на основе набора в SQL-сервере:
update mytable
set myfield = 'test'
where myfield is null
Это обновит все 1 миллион записей, которые являются нулевыми за один шаг. Обновление курсора (то есть, как вы обновляете миллион строк не в пакетном режиме) будет перебирать каждую строку по одному и обновлять ее.
Проблема с пакетной вставкой - размер партии. Если вы попытаетесь обновить слишком много записей одновременно, база данных может заблокировать таблицу на время процесса, заблокировав всех остальных пользователей. Поэтому вам может потребоваться выполнить цикл, который принимает только часть пакета за раз (но почти любое число, превышающее одну строку за раз, будет быстрее, чем одна строка за раз). Это медленнее, чем обновление, вставка или удаление весь пакет, но быстрее, чем операции строка за строкой, и может потребоваться в производственной среде со многими пользователями и небольшим временем простоя, когда пользователи не пытаются просматривать и обновлять другие записи в той же таблице. Размер пакета в значительной степени зависит от структуры базы данных и от того, что именно происходит (таблицы с триггерами и множеством ограничений работают медленнее, чем таблицы с большим количеством полей и поэтому требуют меньших пакетов).