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

Пример 27: выборка данных с использованием кэширующих представлений и таблиц

3.1.2.Пример 27: выборка данных с использованием кэширующих представлений и таблиц

Кэширующие (материализованные, индексированные) представления в отличие от своих классических аналогов{210} формируют и сохраняют отдельный подготовленный набор данных. Поскольку такие представления поддерживаются не всеми СУБД и/или на них налагается ряд серьёзных ограничений, их аналог может быть реализован с помощью кэширующих или агрегирующих таблиц и триггеров{272}.

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

Задача 3.1.2.a{216}: создать представление, ускоряющее получение информации о количестве экземпляров книг: имеющихся в библиотеке, выданных на руки, оставшихся в библиотеке.

Задача 3.1.2.b{232}: создать представление, ускоряющее получение всей информации из таблицы subscriptions в человекочитаемом виде (где идентификаторы читателей и книг заменены на имена и названия).

Ожидаемый результат 3.1.2.a.

Выполнение запроса вида SELECT * FROM {представление} позволяет получить результат следующего вида:

Ожидаемый результат 3.1.2.b.

Выполнение запроса вида SELECT * FROM {представление} позволяет получить результат следующего вида:

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

Пример 27: выборка данных с использованием кэширующих представлений и таблиц

Решение 3.1.2.a{215}.

Традиционно мы начнём рассматривать первым решение для MySQL, и тут есть проблема: MySQL не поддерживает т.н. «кэширующие представления». Единственный способ добиться в данной СУБД необходимого результата — создать настоящую таблицу, в которой будут храниться нужные нам данные.

Эти данные придётся обновлять, для чего могут применяться различные под-

ходы:

•однократное наполнение (для случая, когда исходные данные не меняются);

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

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

Мы будем реализовывать именно последний вариант — работу через триггеры (подробнее о триггерах см. в соответствующем разделе{272}). Этот подход также распадается на два возможных варианта решения: триггеры могут каждый раз обновлять все данные или реагировать только на поступившие изменения (что работает намного быстрее, но требует изначальной инициализации данных в агрегирующей / кэширующей таблице).

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

Создадим агрегирующую таблицу:

MySQL Решение 3.1.2.a (создание агрегирующей таблицы)

1CREATE TABLE `books_statistics`

2(

3`total` INTEGER UNSIGNED NOT NULL,

4`given` INTEGER UNSIGNED NOT NULL,

Легко заметить, что в этой таблице нет первичного ключа. Он и не нужен, т.к.

вней предполагается хранить ровно одну строку.

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

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

Пример 27: выборка данных с использованием кэширующих представлений и таблиц

Проинициализируем данные в созданной таблице:

MySQL Решение 3.1.2.a (очистка таблицы и инициализация данных)

1-- Очистка таблицы:

2TRUNCATE TABLE `books_statistics`;

3

4-- Инициализация данных:

5INSERT INTO `books_statistics`

9SELECT IFNULL(`total`, 0),

10IFNULL(`given`, 0),

11IFNULL(`total` - `given`, 0) AS `rest`

Напишем триггеры, модифицирующие данные в агрегирующей таблице. Агрегация происходит на основе информации, представленной в таблицах books и subscriptions, потому придётся создавать триггеры для обеих этих таблиц.

Изменения в таблице books влияют на поля total и rest, а изменения в таблице subscriptions — на поля given и rest. Данные могут измениться в результате всех трёх операций модификации данных — вставки, удаления, обновления — потому придётся создавать триггеры на всех трёх операциях.

Важно! В MySQL триггеры не активируются каскадными операциями, потому изменения в таблице subscriptions, вызванные удалением читателей, останутся «незаметными» для триггеров на этой таблице. В задании 3.1.2.TSK.D{240} вам предлагается доработать данное решение, устранив эту проблему.

MySQL Решение 3.1.2.a (триггеры для таблицы books)

1-- Удаление старых версий триггеров

2-- (удобно в процессе разработки и отладки):

3DROP TRIGGER `upd_bks_sts_on_books_ins`;

4DROP TRIGGER `upd_bks_sts_on_books_del`;

5DROP TRIGGER `upd_bks_sts_on_books_upd`;

6

7-- Переключение разделителя завершения запроса,

8-- т.к. сейчас запросом будет создание триггера,

9-- внутри которого есть свои, классические запросы:

10DELIMITER $$

11

12-- Создание триггера, реагирующего на добавление книг:

13CREATE TRIGGER `upd_bks_sts_on_books_ins`

14BEFORE INSERT

15ON `books`

16FOR EACH ROW

17BEGIN

18UPDATE `books_statistics` SET

19`total` = `total` + NEW.`b_quantity`,

20`rest` = `total` - `given`;

21END;

22$$

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

Пример 27: выборка данных с использованием кэширующих представлений и таблиц

23-- Создание триггера, реагирующего на удаление книг:

24CREATE TRIGGER `upd_bks_sts_on_books_del`

25BEFORE DELETE

26ON `books`

27FOR EACH ROW

28BEGIN

29UPDATE `books_statistics` SET

30`total` = `total` - OLD.`b_quantity`,

31`given` = `given` - (SELECT COUNT(`sb_book`)

35`rest` = `total` - `given`;

36END;

37$$

38

39-- Создание триггера, реагирующего на

40-- изменение количества книг:

42CREATE TRIGGER `upd_bks_sts_on_books_upd`

42BEFORE UPDATE

43ON `books`

44FOR EACH ROW

45BEGIN

46UPDATE `books_statistics` SET

47`total` = `total` - OLD.`b_quantity` + NEW.`b_quantity`,

48`rest` = `total` - `given`;

49END;

50$$

51

52-- Восстановление разделителя завершения запросов:

53DELIMITER ;

Переключение разделителя (признака) завершения запроса (строки 7-10) необходимо для того, чтобы MySQL не воспринимал символы ;, встречающиеся в конце запросов внутри триггера как конец самого запроса по созданию триггера. После всех операций по созданию триггеров этот разделитель возвращается в исходное состояние (строка 53).

Выражение FOR EACH ROW (строки 16, 27, 44) означают, что тело триггера будет выполнено для каждой записи (по-другому триггеры в MySQL и не работают), которую затрагивает операция с таблицей (добавляться, изменяться и удаляться может несколько записей за один раз).

Ключевые слова NEW и OLD позволяют обращаться:

•при операциях вставки через NEW к новым (добавляемым) данным;

•при операциях обновления через OLD к старым значениям данных и через NEW к новым значениям данных;

•при операциях удаления через OLD к значениям удаляемых данных.

MySQL (в отличие от MS SQL Server) «на лету» вычисляет новые значения полей таблицы, что позволяет нам во всех трёх триггерах производить все необходимые действия одним запросом и использовать выражение `rest` = `total` - `given`, т.к. значения `total` и/или `given` уже обновлены ранее встретившимися в запросах командами. В MS SQL Server же придётся выполнять отдельный запрос, чтобы вычислить значение `rest`, т.к. значения `total` и/или `given` не меняются до завершения выполнения первого запроса.

В триггерах на таблице books сами запросы (строки 18-20, 29-35, 46-48) совершенно тривиальны, и единственная их непривычность заключается в использовании ключевых слов OLD и NEW, которые мы только что рассмотрели.

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

Пример 27: выборка данных с использованием кэширующих представлений и таблиц

1-- Удаление старых версий триггеров

2-- (удобно в процессе разработки и отладки):

3DROP TRIGGER `upd_bks_sts_on_subscriptions_ins`;

4DROP TRIGGER `upd_bks_sts_on_subscriptions_del`;

5DROP TRIGGER `upd_bks_sts_on_subscriptions_upd`;

6

7

8-- Переключение разделителя завершения запроса,

9-- т.к. сейчас запросом будет создание триггера,

10-- внутри которого есть свои, классические запросы:

11DELIMITER $$

12

13-- Создание триггера, реагирующего на добавление выдачи книг:

14CREATE TRIGGER `upd_bks_sts_on_subscriptions_ins`

15BEFORE INSERT

16ON `subscriptions`

17FOR EACH ROW

18BEGIN

26UPDATE `books_statistics` SET

27`rest` = `rest` - @delta,

28`given` = `given` + @delta;

29END;

30$$

31

32-- Создание триггера, реагирующего на удаление выдачи книг:

33CREATE TRIGGER `upd_bks_sts_on_subscriptions_del`

34BEFORE DELETE

35ON `subscriptions`

36FOR EACH ROW

37BEGIN

45UPDATE `books_statistics` SET

46`rest` = `rest` + @delta,

47`given` = `given` - @delta;

48END;

49$$

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