Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 26: выборка данных с использованием некэширующих представлений
MySQL I Решение 3.1.1.b |
1CREATE OR REPLACE VIEW 'authors with more than one book'
2AS
3SELECT 'a id',
4 |
'a name', |
5 |
COUNT('b id') AS 'books in library' |
6 |
FROM 'authors' |
7 |
JOIN 'm2m books authors' USING ('a id') |
8GROUP BY 'a id'
9HAVING 'books in library' > 1
MS SQL І Решение 3.1.1.b
1CREATE VIEW [authors_with_more_than_one_book]
2AS
3SELECT [authors] [a_id],
4 |
|
[a name], |
|
||
5 |
|
COUNT 1 [b id] ) AS [books in library] |
|||
6 |
FROM |
[authors] |
|
||
7 |
|
JOIN [m2m books authors] |
|||
8 |
|
ON [authors] [a id] = [m2m books authors] [a id] |
|||
9 |
GROUP |
BY [authors] [a id], |
|
||
10 |
|
[a name] |
|
||
11 |
HAVING COUNT 1 [b id] ) |
> 1 |
|||
|
Oracl |
|
|
|
|
|
e |
1 Решение 3.1.1.b |
1 |
|
|
CREATE OR REPLACE VIEW |
"authors_w_more_than_one_book" |
||||
2AS
3SELECT "a_id",
4"a_name",
5COUNT("b_id" AS "books_in_library"
6FROM "authors"
|
JOIN "m2m_books_authors" USING ("a_id") |
8 |
GROUP BY "a_id", "a_name" |
9 |
HAVING COUNT("b id" > 1 |
Задание 3.1.1 .TSK.A: упростить использование решения задачи 2.2.8.b{127} &так, чтобы для получения нужных данных не приходилось использовать
представленные в решении{129} объёмные запросы.
& Задание 3.1.1.TSK.B: создать представление, позволяющее получать список читателей с количеством находящихся у каждого читателя на руках книг, но отображающее только таких читателей, по которым имеются задолженности,
т.е. на руках у читателя есть хотя бы одна книга, которую он должен был вернуть до наступления текущей даты.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 225/545
Пример 27: выборка данных с использованием кэширующих представлений и таблиц
3.1.2.Пример 27: выборка данных с использованием кэширующих представлений и таблиц
Кэширующие (материализованные, индексированные) представления в отличие от своих классических аналогов*210* формируют и сохраняют отдельный подготовленный набор данных. Поскольку такие представления поддерживаются не всеми СУБД и/или на них налагается ряд серьёзных ограничений, их аналог может быть реализован с помощью кэширующих или агрегирующих таблиц и
триггеров*272*.
Использование решений, подобных представленным в данном примере, в реальной жизни может совершенно непредсказуемо повлиять на производительность — как резко увеличить её, так и очень сильно снизить. Каждый случай требует своего отдельного исследования. Потому представленные ниже задачи и их решения стоит воспринимать лишь как демонстрацию возможностей СУБД, а не как прямое руководство к действию.
Задача 3.1.2.a*216*: создать представление, ускоряющее получение информации о количестве экземпляров книг: имеющихся в библиотеке, выданных на руки, оставшихся в библиотеке.
Задача 3.1.2.b*232*: создать представление, ускоряющее получение всей информации из таблицы subscriptions в человекочитаемом виде (где идентификаторы читателей и книг заменены на имена и названия).
Ожидаемый результат 3.1.2.a.
Выполнение запроса вида SELECT * FROM {представление} позволяет получить результат следующего вида:
total |
given |
rest |
33 |
5 |
28 |
Ожидаемый результат 3.1.2.b.
Выполнение запроса вида SELECT * FROM {представление} позволяет получить результат следующего вида:
sb_id |
sb_subscriber |
sb_book |
sb_start |
sb_finish |
sb_is_active |
2 |
Иванов И.И. |
Евгений Онегин |
2011-01-12 |
2011-02-12 |
N |
42 |
Иванов И.И. |
Сказка о рыбаке и рыбке |
2012-06-11 |
2012-08-11 |
N |
61 |
Иванов И.И. |
Искусство программирования |
2014-08-03 |
2014-10-03 |
N |
95 |
Иванов И.И. |
Психология программирования |
2015-10-07 |
2015-11-07 |
N |
100 |
Иванов И.И. |
Основание и империя |
2011-01-12 |
2011-02-12 |
N |
3 |
Сидоров С.С. |
Основание и империя |
2012-05-17 |
2012-07-17 |
Y |
62 |
Сидоров С.С. |
Язык программирования С++ |
2014-08-03 |
2014-10-03 |
Y |
86 |
Сидоров С.С. |
Евгений Онегин |
2014-08-03 |
2014-09-03 |
Y |
57 |
Сидоров С.С. |
Язык программирования С++ |
2012-06-11 |
2012-08-11 |
N |
91 |
Сидоров С.С. |
Евгений Онегин |
2015-10-07 |
2015-03-07 |
Y |
99 |
Сидоров С.С. |
Психология программирования |
2015-10-08 |
2025-11-08 |
Y |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 226/545
Пример 27: выборка данных с использованием кэширующих представлений и таблиц
Решение 3.1.2.a{215}.
Традиционно мы начнём рассматривать первым решение для MySQL, и тут есть проблема: MySQL не поддерживает т.н. «кэширующие представления». Единственный способ добиться в данной СУБД необходимого результата — создать настоящую таблицу, в которой будут храниться нужные нам данные.
Эти данные придётся обновлять, для чего могут применяться различные под-
ходы:
•однократное наполнение (для случая, когда исходные данные не меняются);
•периодическое обновление, например, с помощью хранимой процедуры (подходит для случая, когда мы можем позволить себе периодически получать не самую актуальную информацию);
•автоматическое обновление с помощью триггеров (позволяет в каждый момент времени получать актуальную информацию).
Мы будем реализовывать именно последний вариант — работу через триггеры (подробнее о триггерах см. в соответствующем разделе{272}). Этот подход также распадается на два возможных варианта решения: триггеры могут каждый раз обновлять все данные или реагировать только на поступившие изменения (что работает намного быстрее, но требует изначальной инициализации данных в агрегирующей / кэширующей таблице).
Итак, мы реализуем самый производительный (пусть и самый сложный) вариант — создадим агрегирующую таблицу, напишем запрос для инициализации её данных и создадим триггеры, реагирующие на изменения агрегируемых данных.
Создадим агрегирующую таблицу:
MySQL Решение 3.1.2.a (создание агрегирующей таблицы)
1CREATE TABLE 'books_statistics'
2(
3'total' INTEGER'given' UNSIGNED NOT NULL
4INTEGER 'rest' UNSIGNED NOT NULL
5 |
INTEGER UNSIGNED NOT NULL |
6 |
|
Легко заметить, что в этой таблице нет первичного ключа. Он и не нужен, т.к.
вней предполагается хранить ровно одну строку.
Вреальных приложениях обязательно должен быть механизм реакции на
случаи, когда в подобных таблицах оказывается либо ноль строк, либо более одной строки. Обе такие ситуации потенциально могут привести к
краху приложения или его некорректной работе.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 227/545
Пример 27: выборка данных с использованием кэширующих представлений и таблиц
Проинициализируем данные в созданной таблице:
MySQL I Решение 3.1.2.a (очистка таблицы и инициализация данных) |
| |
|||
1 |
-- Очистка таблицы: |
|
|
|
2 |
TRUNCATE TABLE 'books_statistics'; |
|
||
4 |
-- Инициализация данных: |
|
||
5 |
INSERT INTO 'books statistics' |
|
||
6 |
|
('total', |
|
|
|
|
'given', |
|
|
|
|
'rest') |
|
|
9 |
SELECT IFNULL('total', 0 , |
|
||
10 |
|
IFNULL('given', 0 , |
|
|
11 |
|
IFNULL('total' - 'given', 0) AS 'rest' |
||
12 |
FROM |
(SELECT (SELECT SUM('b quantity') |
|
|
13 |
|
FROM |
'books') |
AS 'total' |
14 |
|
(SELECT COUNT('sb book') |
|
|
15 |
|
FROM |
'subscriptions' |
|
16 |
|
WHERE |
'sb is active' = 'Y') AS 'given') |
|
17 |
|
AS 'prepared data'; |
|
|
|
|
|
|
|
Напишем триггеры, модифицирующие данные в агрегирующей таблице. Агрегация происходит на основе информации, представленной в таблицах books и subscriptions, потому придётся создавать триггеры для обеих этих таблиц.
Изменения в таблице books влияют на поля total и rest, а изменения в таблице subscriptions — на поля given и rest. Данные могут измениться в результате всех трёх операций модификации данных — вставки, удаления, обновления — потому придётся создавать триггеры на всех трёх операциях.
Важно! В MySQL триггеры не активируются каскадными операциями, потому изменения в таблице subscriptions, вызванные удалением читателей, останутся «незаметными» для триггеров на этой таблице. В задании 3.1.2.TSK.D{240} вам предлагается доработать данное решение, устранив эту проблему.
|
|
.2.a |
для |
1 |
-- Удаление |
старых версий триггеров |
|
2 |
-- (удобно в |
процессе разработки и отладки): |
|
|
DROP TRIGGER 'upd_bks_sts_on_books_ins'; |
||
4DROP TRIGGER 'upd_bks_sts_on_books_del';
5DROP TRIGGER 'upd_bks_sts_on_books_upd';
7-- Переключение разделителя завершения запроса,
8-- т.к. сейчас запросом будет создание триггера,
9 |
-- |
внутри которого есть |
свои, классические запросы: |
10 |
DELIMITER $$ |
|
|
11 |
|
|
|
12-- Создание триггера, реагирующего на добавление книг: CREATE TRIGGER
13'upd_bks_sts_on_books_ins' BEFORE INSERT
14ON 'books'
1516 FOR 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 Стр: 228/545
Пример 27: выборка данных с использованием кэширующих представлений и таблиц
MySQL I |
Решение 3.1.2.a (триггеры для таблицы books) (продолжение) |
| |
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') |
|
32 |
FROM |
'subscriptions' |
33 |
WHERE |
'sb book'=OLD.'b id' |
34 |
AND 'sb is active' = 'Y'), |
|
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) со-
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 229/545