Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Пример 21: вставка данных

Решение 2.3.1.b{185}.

Формально мы можем свести решение этой задачи к выполнению трёх отдельных запросов, аналогичных представленным в решении задачи 2.3.1.a{182}, но существует более эффективный способ выполнения вставки набора данных, который включает три действия:

•временную приостановку работы индексов;

•вставку данных одним большим блоком;

•возобновление работы индексов.

Наличие индексов может сильно снизить скорость модификации данных, т.к. СУБД будет вынуждена тратить много ресурсов на постоянное обновление индексов для поддержания их в актуальном состоянии. Также выполнение множества отдельных запросов вместо одного (пусть и большого) приводит к дополнительным накладным расходам на управление транзакциями и иными внутренними механизмами работы СУБД.

Отключение и повторное включение индексов имеет смысл только при модификации действительно большого набора данных (десятки тысяч записей и более), но соответствующие команды мы всё равно рассмотрим.

Обязательно изучите соответствующие разделы документации к вашей СУБД. Рекомендации по использованию тех или иных команд (и даже сама применимость команд) могут очень сильно отличаться в зависимости от множества факторов.

В MySQL есть специальная команда по отключению и повторному включе-

нию индексов (ALTER TABLE ... DISABLE KEYS и ALTER TABLE ... ENABLE

KEYS), но она действует только на таблицы, работающей с методом доступа MyISAM. На повсеместно распространённый ныне метод доступа InnoDB она не действует.

Что можно сделать в методе доступа InnoDB?

•Отключить и затем включить контроль уникальности (фактически, выключить и включить уникальные индексы).

•Отключить и затем включить контроль внешних ключей.

•Отключить и затем снова включить автоматическое подтверждение транзакций (если мы собираемся выполнить несколько отдельных запросов).

•Можно «поиграть» с автоинкрементируемыми первичными ключами5, но здесь нет универсальных рекомендаций.

Отключение уникальных индексов и контроля внешних ключей — крайне опасная операция, которая в перспективе может привести к катастрофическим повреждениям данных. Выполняйте её только как последнее средство, если ничто другое не помогает повысить производительность, и вы на 101% уверены в том, что передаваемые вами данные полностью удовлетворяют требованиям, соблюдение которых призваны контролировать уникальные индексы и внешние ключи.

Если теперь отбросить все нюансы и неочевидные рекомендации, остаётся один основной вывод: выполняйте вставку одним запросом (вставляйте сразу несколько строк) — это более быстрый вариант в сравнении с несколькими отдельными запросами, каждый из которых вставляет по одной строке.

5 https://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html#innodb-auto-increment-lock-mode-usage-implica- tions

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 185/545

Пример 21: вставка данных

MySQL Решение 2.3.1.b

1-- Актуально для MyISAM, не актуально для InnoDB:

2ALTER TABLE `subscriptions` DISABLE KEYS; -- Отключение индексов.

3

4-- Следующие две команды -- ОЧЕНЬ опасное решение!

5SET foreign_key_checks = 0; -- Отключение проверки внешних ключей.

6SET unique_checks = 0; -- Отключение уникальных индексов.

10-- Сам запрос на вставку:

11INSERT INTO `subscriptions`

37SET unique_checks = 1; -- Включение уникальных индексов.

38SET foreign_key_checks = 1; -- Включение проверки внешних ключей.

39

40-- Актуально для MyISAM, не актуально для InnoDB:

41ALTER TABLE `subscriptions` ENABLE KEYS; -- Включение индексов.

Ещё раз отметим, что в общем случае можно и нужно ограничиться запросом, показанным в строках 10-16. Остальные идеи приведены как справочная информация.

В MS SQL Server вы тоже можете временно выключить и затем снова вклю-

чить индексы командами ALTER INDEX ALL ON ... DISABLE и ALTER INDEX ALL ON ... REBUILD.

Обязательно ознакомьтесь хотя бы с двумя6, 7 соответствующими разделами документации — у этих операций могут быть крайне неожиданные и опасные побочные эффекты. Их выполнение может поставить под серьёзную угрозу способность СУБД контролировать консистентность данных.

6https://msdn.microsoft.com/en-us/library/ms177456.aspx

7https://msdn.microsoft.com/en-us/library/ms190645.aspx

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 186/545

Пример 21: вставка данных

MS SQL Решение 2.3.1.b

1-- ОЧЕНЬ опасное решение!

2ALTER INDEX ALL ON [subscriptions] DISABLE;

3

4-- Обязательно включите кластерный индекс

5-- (в нашем случае -- первичный ключ) перед дальнейшими

6-- операциями, иначе запросы будут завершаться ошибкой.

7ALTER INDEX [PK_subscriptions] ON [subscriptions] REBUILD;

8

9-- Сам запрос на вставку:

10INSERT INTO [subscriptions]

32-- Включение индексов после их отключения:

33ALTER INDEX ALL ON [subscriptions] REBUILD;

Вотличие от MySQL и MS SQL Server в Oracle нет готового решения для выключения и включения всех индексов. Существует много алгоритмических решений8 этой задачи, но подавляющее большинство авторов совершенно справедливо предупреждают о множестве потенциальных проблем, рекомендуют этого не делать и даже высказывают предположения о том, что удаление и повторное создание индекса может оказаться более выгодным, чем его выключение и включение.

Единственный индекс на таблице subscriptions — это её первичный ключ,

потому в приведённом ниже примере именно его мы будем выключать и включать.

Снова и снова напоминаем: это очень опасная операция, вы рискуете фатально повредить вашу базу данных, если что-то пойдёт не так, как вы запланировали или предполагали. Ни в коем случае не пытайтесь проводить подобные эксперименты на реальных работающих базах данных, тренируйтесь только на их копиях, которые вам совершенно не жалко безвозвратно потерять.

Ещё один важный нюанс решения для Oracle состоит в том, что эта СУБД не поддерживает классический синтаксис вставки одним запросом нескольких записей, потому приходится использовать альтернативную запись.

8 http://johnlevandowski.com/oracle-disable-constraints-and-make-indexes-unusable/

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 187/545

Пример 21: вставка данных

Oracle Решение 2.3.1.b

1-- ОЧЕНЬ опасное решение!

2ALTER TABLE "subscriptions" MODIFY CONSTRAINT "PK_subscriptions" DISABLE;

3

4-- Сам запрос на вставку:

5INSERT ALL

6 INTO "subscriptions"

7("sb_subscriber",

8"sb_book",

9"sb_start",

10"sb_finish",

11"sb_is_active")

12VALUES (2,

131,

14TO_DATE('2016-01-25', 'YYYY-MM-DD'),

15TO_DATE('2016-04-30', 'YYYY-MM-DD'),

16'N')

17 INTO "subscriptions"

18("sb_subscriber",

19"sb_book",

20"sb_start",

21"sb_finish",

22"sb_is_active")

23VALUES (2,

243,

25TO_DATE('2016-01-25', 'YYYY-MM-DD'),

26TO_DATE('2016-04-30', 'YYYY-MM-DD'),

27'N')

28 INTO "subscriptions"

29("sb_subscriber",

30"sb_book",

31"sb_start",

32"sb_finish",

33"sb_is_active")

34VALUES (2,

355,

36TO_DATE('2016-01-25', 'YYYY-MM-DD'),

37TO_DATE('2016-04-30', 'YYYY-MM-DD'),

38'N')

39SELECT 1 FROM "DUAL";

40

41-- Включение индекса после его отключения:

42ALTER TABLE "subscriptions" MODIFY CONSTRAINT "PK_subscriptions" ENABLE;

Исследование 2.3.1.EXP.A: сравним скорость вставки данных, основанной на циклическом повторении (1000 итераций) запроса, вставляющего одну строку, и выполнении одного запроса, вставляющего за один раз 1000 строк. Выполним по сто раз каждый вариантов поочерёдно.

Значения медиан времени, затраченного на вставку тысячи записей каждым из вариантов, таковы:

Числа для первого (циклического) варианта имеют такие аккуратные значения потому, что время выполнения каждого отдельного запроса считалось с точностью до тысячной доли секунды, затем полученное значение медианы умножалось на тысячу.

В каждой СУБД соотношение времени работы двух представленных решения разное, но везде по скорости выигрывает вариант со вставкой большого количества строк одним запросом.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 188/545

Пример 21: вставка данных

В Oracle такая небольшая разница во времени работы двух представленных вариантов обусловлена тем, что, фактически, это один и тот же вариант, только записанный разными способами. В этой СУБД тоже можно добиться очень высокой производительности вставки данных, но это достигается более сложными способами9.

Для повышения надёжности операций вставки рекомендуется после их выполнения получать на уровне приложения информацию о том, какое количество рядов было затронуто операцией. Если полученное число не совпадает с числом переданных на вставку строк, где-то произошла ошибка.

Задание 2.3.1.TSK.A: добавить в базу данных информацию о троих новых читателях: «Орлов О.О.», «Соколов С.С.», «Беркутов Б.Б.»

Задание 2.2.1.TSK.B: отразить в базе данных информацию о том, что каждый из добавленных в задании 2.3.1.TSK.A читателей 20-го января 2016го года на месяц взял в библиотеке книгу «Курс теоретической физики».

Задание 2.2.1.TSK.C: добавить в базу данных пять любых авторов и десять книг этих авторов (по две на каждого); если понадобится, добавить в базу данных соответствующие жанры. Отразить авторство добавленных книг и их принадлежность к соответствующим жанрам.

9 http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 189/545