Почему не SQL?
Без преувеличения можно сказать, что SQL — один из самых распространенных в мире языков. Информационные системы могут быть написаны на Java, Python, JavaScript, C#, PHP и десятке других языков, но SQL база в том или ином виде будет в абсолютном большинстве таких систем. Среди бизнес-приложений процент систем, использующих SQL, вообще стремится к 100%.
При этом большинство существующих материалов о SQL на хабре и других ресурсах сводятся к простым вопросам, вроде: «какие типы соединений бывают», «чем левое соединение отличается от правого», «что такое триггеры» и так далее. Более того, в этих материалах практически ничего не говорится о проблемах SQL (и его реализациях), которых на самом деле очень и очень немало. Поэтому мы решили восполнить оба этих пробела: с одной стороны рассказать, как многие вещи в SQL работают изнутри, а с другой стороны — почему они работают не так как нужно / хотелось бы.
При этом речь в статье пойдет не о «вкусах и цветах фломастеров». Все затрагиваемые проблемы носят фундаментальный характер: присутствуют при разработке практически любой информационной системы и не ограничиваются «красотой кода», а в той или иной степени приводят либо к критическому падению производительности, либо к существенному росту порога вхождения, либо к значительным трудозатратам со стороны разработчика.
Статья получилась достаточно объемной, и далеко не все осилят ее за один раз. Поэтому, чтобы было удобнее в ней ориентироваться, а также иметь возможность оценить «масштабы бедствия», начнем с оглавления со списком всех затрагиваемых проблем:
Тестироваться все будет на трех РСУБД — PostgreSQL, MS SQL Server и Oracle. Все СУБД последних версий — 11, 2019 и 19c соответственно.
Перед тем как начать, создадим базу с товарами, контрагентами и документами приходов и отгрузок. Наполним ее данными, причем большим количеством, так как дальше будет много примеров с демонстрацией проблем оптимизации запросов / производительности на больших объемах данных.
CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE stock (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE product (
id INT PRIMARY KEY,
name VARCHAR(100),
"group" INT
);
CREATE INDEX product_group ON product ("group");
CREATE TABLE receipt (
id INT PRIMARY KEY,
customer INT NOT NULL,
"date" DATE NOT NULL,
CONSTRAINT receipt_customer_fk
FOREIGN KEY (customer) REFERENCES customer (id),
stock INT NOT NULL,
CONSTRAINT receipt_stock_fk
FOREIGN KEY (stock) REFERENCES stock (id),
data VARCHAR(400)
);
CREATE INDEX receipt_customer_fk ON receipt (customer);
CREATE INDEX receipt_stock_fk ON receipt (stock);
CREATE TABLE shipment (
id INT PRIMARY KEY,
customer INT NOT NULL,
"date" DATE NOT NULL,
CONSTRAINT shipment_customer_fk
FOREIGN KEY (customer) REFERENCES customer (id),
stock INT NOT NULL,
CONSTRAINT shipment_stock_fk
FOREIGN KEY (stock) REFERENCES stock (id),
data VARCHAR(400)
);
CREATE INDEX shipment_customer_fk ON shipment (customer);
CREATE INDEX shipment_stock_fk ON shipment (stock);
CREATE TABLE receiptdetail (
id INT PRIMARY KEY,
receipt INT NOT NULL,
product INT NOT NULL,
quantity NUMERIC(16,3),
CONSTRAINT receiptdetail_receipt_fk
FOREIGN KEY (receipt) REFERENCES receipt (id),
CONSTRAINT receiptdetail_product_fk
FOREIGN KEY (product) REFERENCES product (id),
data VARCHAR(400)
);
CREATE INDEX receiptdetail_receipt_fk ON receiptdetail (receipt);
CREATE INDEX receiptdetail_product_fk ON receiptdetail (product);
CREATE TABLE shipmentdetail (
id INT PRIMARY KEY,
shipment INT NOT NULL,
product INT NOT NULL,
quantity NUMERIC(16,3),
CONSTRAINT shipmentdetail_shipment_fk
FOREIGN KEY (shipment) REFERENCES shipment (id),
CONSTRAINT shipmentdetail_product_fk
FOREIGN KEY (product) REFERENCES product (id),
sd INT,
data VARCHAR(400)
);
CREATE INDEX shipmentdetail_shipment_fk ON shipmentdetail (shipment);
CREATE INDEX shipmentdetail_product_fk ON shipmentdetail (product);
CREATE INDEX shipmentdetail_sd ON shipmentdetail (sd);
CREATE INDEX shipmentdetail_p_s ON shipmentdetail (product,shipment);
INSERT INTO stock (id, name)
WITH
numbers ( n ) AS (
SELECT 1 AS n FROM dual
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 10
)
SELECT n, 'Stock '||n FROM numbers;
INSERT INTO customer (id, name)
WITH
numbers ( n ) AS (
SELECT 1 AS n FROM dual
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 1000
)
SELECT n, 'Customer '||n FROM numbers;
INSERT INTO product (id, name, "group")
WITH
numbers ( n ) AS (
SELECT 1 AS n FROM dual
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 50000
)
SELECT n, 'Product '||n, round(dbms_random.value() * 1000) + 1 FROM numbers;
INSERT INTO receipt (id, customer, stock, "date", data)
WITH
numbers ( n ) AS (
SELECT 1 AS n FROM dual
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 10000
)
SELECT n, round(dbms_random.value() * 1000) + 1, round(dbms_random.value() * 10) + 1, CURRENT_DATE - round(dbms_random.value() * 300), 'data'||n FROM numbers;
INSERT INTO receiptdetail (id, receipt, product, quantity, data)
WITH
numbers ( n ) AS (
SELECT 1 AS n FROM dual
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 1000000
)
SELECT n, round(dbms_random.value() * 10000) + 1, round(dbms_random.value() * 50000) + 1, round(dbms_random.value() * 100) + 1, 'data'||n FROM numbers;
INSERT INTO shipment (id, customer, stock, "date", data)
WITH
numbers ( n ) AS (
SELECT 1 AS n FROM dual
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 100000
)
SELECT n, round(dbms_random.value() * 1000) + 1, round(dbms_random.value() * 10) + 1, CURRENT_DATE - round(dbms_random.value() * 300), 'data'||n FROM numbers;
INSERT INTO shipmentdetail (id, shipment, product, quantity, data, sd)
WITH
numbers ( n ) AS (
SELECT 1 AS n FROM dual
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 10000000
)
SELECT n, round(dbms_random.value() * 100000) + 1, round(dbms_random.value() * 50000) + 1, round(dbms_random.value() * 10) + 1, 'data'||n, CASE WHEN REMAINDER(n, 100000) = 0 THEN n+1 ELSE NULL END FROM numbers;
exec DBMS_STATS.GATHER_DATABASE_STATS;
CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE stock (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE product (
id INT PRIMARY KEY,
name VARCHAR(100),
[group] INT
);
CREATE INDEX product_group ON product ([group]);
CREATE TABLE receipt (
id INT PRIMARY KEY,
customer INT NOT NULL,
date DATE NOT NULL,
CONSTRAINT receipt_customer_fk
FOREIGN KEY (customer) REFERENCES customer (id),
stock INT NOT NULL,
CONSTRAINT receipt_stock_fk
FOREIGN KEY (stock) REFERENCES stock (id),
data VARCHAR(400)
);
CREATE INDEX receipt_date ON receipt (date);
CREATE INDEX receipt_customer_fk ON receipt (customer);
CREATE INDEX receipt_stock_fk ON receipt (stock);
CREATE TABLE shipment (
id INT PRIMARY KEY,
customer INT NOT NULL,
date DATE NOT NULL,
CONSTRAINT shipment_customer_fk
FOREIGN KEY (customer) REFERENCES customer (id),
stock INT NOT NULL,
CONSTRAINT shipment_stock_fk
FOREIGN KEY (stock) REFERENCES stock (id),
data VARCHAR(400)
);
CREATE INDEX shipment_date ON shipment (date);
CREATE INDEX shipment_customer_fk ON shipment (customer);
CREATE INDEX shipment_stock_fk ON shipment (stock);
CREATE TABLE receiptdetail (
id INT PRIMARY KEY,
receipt INT NOT NULL,
product INT NOT NULL,
quantity NUMERIC(16,3),
CONSTRAINT receiptdetail_receipt_fk
FOREIGN KEY (receipt) REFERENCES receipt (id),
CONSTRAINT receiptdetail_product_fk
FOREIGN KEY (product) REFERENCES product (id),
data VARCHAR(400)
);
CREATE INDEX receiptdetail_receipt_fk ON receiptdetail (receipt);
CREATE INDEX receiptdetail_product_fk ON receiptdetail (product);
CREATE TABLE shipmentdetail (
id INT PRIMARY KEY,
shipment INT NOT NULL,
product INT NOT NULL,
quantity NUMERIC(16,3),
CONSTRAINT shipmentdetail_shipment_fk
FOREIGN KEY (shipment) REFERENCES shipment (id),
CONSTRAINT shipmentdetail_product_fk
FOREIGN KEY (product) REFERENCES product (id),
sd INT,
data VARCHAR(400)
);
CREATE INDEX shipmentdetail_shipment_fk ON shipmentdetail (shipment);
CREATE INDEX shipmentdetail_product_fk ON shipmentdetail (product);
CREATE INDEX shipmentdetail_sd ON shipmentdetail (sd);
CREATE INDEX shipmentdetail_p_s ON shipmentdetail (product,shipment);
WITH
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 10
)
INSERT INTO stock (id, name)
SELECT n, CONCAT('Stock ',n) FROM numbers option (maxrecursion 0);
WITH
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 1000
)
INSERT INTO customer (id, name)
SELECT n, CONCAT('Customer ',n) FROM numbers option (maxrecursion 0);
WITH
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 50000
)
INSERT INTO product (id, name, [group])
SELECT n, CONCAT('Product ',n), ABS(checksum(NewId()))% 1000 + 1 FROM numbers option (maxrecursion 0);
WITH
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 10000
)
INSERT INTO receipt (id, customer, stock, date, data)
SELECT n, ABS(checksum(NewId()))% 1000 + 1, ABS(checksum(NewId()))% 10 + 1, DATEADD(day, -ABS(checksum(NewId()))% 300, GETDATE()), CONCAT('data',n) FROM numbers option (maxrecursion 0);
WITH
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 1000000
)
INSERT INTO receiptdetail (id, receipt, product, quantity, data)
SELECT n, ABS(checksum(NewId()))% 10000 + 1, ABS(checksum(NewId()))% 50000 + 1, ABS(checksum(NewId()))% 100 + 1, CONCAT('data',n) FROM numbers option (maxrecursion 0);
WITH
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 100000
)
INSERT INTO shipment (id, customer, stock, date, data)
SELECT n, ABS(checksum(NewId()))% 1000 + 1, ABS(checksum(NewId()))% 10 + 1, DATEADD(day, -ABS(checksum(NewId()))% 300, GETDATE()), CONCAT('data',n) FROM numbers option (maxrecursion 0);
WITH
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 10000000
)
INSERT INTO shipmentdetail (id, shipment, product, quantity, data, sd)
SELECT n, ABS(checksum(NewId()))% 100000 + 1, ABS(checksum(NewId()))% 50000 + 1, ABS(checksum(NewId()))% 10 + 1, CONCAT('data',n), CASE WHEN n % 100000 = 0 THEN n+1 ELSE NULL END FROM numbers option (maxrecursion 0);
EXEC sp_updatestats;
CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE stock (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE product (
id INT PRIMARY KEY,
name VARCHAR(100),
"group" INT
);
CREATE INDEX product_group ON product ("group");
CREATE TABLE receipt (
id INT PRIMARY KEY,
customer INT NOT NULL,
"date" DATE NOT NULL,
CONSTRAINT receipt_customer_fk
FOREIGN KEY (customer) REFERENCES customer (id),
stock INT NOT NULL,
CONSTRAINT receipt_stock_fk
FOREIGN KEY (stock) REFERENCES stock (id),
data VARCHAR(400)
);
CREATE INDEX receipt_customer_fk ON receipt (customer);
CREATE INDEX receipt_stock_fk ON receipt (stock);
CREATE TABLE shipment (
id INT PRIMARY KEY,
customer INT NOT NULL,
"date" DATE NOT NULL,
CONSTRAINT shipment_customer_fk
FOREIGN KEY (customer) REFERENCES customer (id),
stock INT NOT NULL,
CONSTRAINT shipment_stock_fk
FOREIGN KEY (stock) REFERENCES stock (id),
data VARCHAR(400)
);
CREATE INDEX shipment_customer_fk ON shipment (customer);
CREATE INDEX shipment_stock_fk ON shipment (stock);
CREATE TABLE receiptdetail (
id INT PRIMARY KEY,
receipt INT NOT NULL,
product INT NOT NULL,
quantity NUMERIC(16,3),
CONSTRAINT receiptdetail_receipt_fk
FOREIGN KEY (receipt) REFERENCES receipt (id),
CONSTRAINT receiptdetail_product_fk
FOREIGN KEY (product) REFERENCES product (id),
data VARCHAR(400)
);
CREATE INDEX receiptdetail_receipt_fk ON receiptdetail (receipt);
CREATE INDEX receiptdetail_product_fk ON receiptdetail (product);
CREATE TABLE shipmentdetail (
id INT PRIMARY KEY,
shipment INT NOT NULL,
product INT NOT NULL,
quantity NUMERIC(16,3),
CONSTRAINT shipmentdetail_shipment_fk
FOREIGN KEY (shipment) REFERENCES shipment (id),
CONSTRAINT shipmentdetail_product_fk
FOREIGN KEY (product) REFERENCES product (id),
sd INT,
data VARCHAR(400)
);
CREATE INDEX shipmentdetail_shipment_fk ON shipmentdetail (shipment);
CREATE INDEX shipmentdetail_product_fk ON shipmentdetail (product);
CREATE INDEX shipmentdetail_sd ON shipmentdetail (sd);
CREATE INDEX shipmentdetail_p_s ON shipmentdetail (product,shipment);
INSERT INTO stock (id, name)
WITH RECURSIVE
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 10
)
SELECT n, 'Stock '||n FROM numbers;
INSERT INTO customer (id, name)
WITH RECURSIVE
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 1000
)
SELECT n, 'Customer '||n FROM numbers;
INSERT INTO product (id, name, "group")
WITH RECURSIVE
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 50000
)
SELECT n, 'Product '||n, floor(random() * 1000) + 1 FROM numbers;
INSERT INTO receipt (id, customer, stock, "date", data)
WITH RECURSIVE
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 10000
)
SELECT n, floor(random() * 1000) + 1, floor(random() * 10) + 1, CURRENT_DATE-CAST(floor(random() * 300) AS int), 'data'||n FROM numbers;
INSERT INTO receiptdetail (id, receipt, product, quantity, data)
WITH RECURSIVE
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 1000000
)
SELECT n, floor(random() * 10000) + 1, floor(random() * 50000) + 1, floor(random() * 100) + 1, 'data'||n FROM numbers;
INSERT INTO shipment (id, customer, stock, "date", data)
WITH RECURSIVE
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 100000
)
SELECT n, floor(random() * 1000) + 1, floor(random() * 10) + 1, CURRENT_DATE-CAST(floor(random() * 300) AS int), 'data'||n FROM numbers;
INSERT INTO shipmentdetail (id, shipment, product, quantity, data, sd)
WITH RECURSIVE
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n <= 10000000
)
SELECT n, floor(random() * 100000) + 1, floor(random() * 50000) + 1, floor(random() * 10) + 1, 'data'||n, CASE WHEN n % 100000 = 0 THEN n+1 ELSE NULL END FROM numbers;
ANALYZE;
Уже заполненные базы данных, на которых проводилось тестирование, и параметры подключения к ним можно найти здесь:
Hostname:116.203.61.136
Port:1521
SID: orclcdb
User/DB: test
Password: test
Hostname:195.201.126.177
DB: test
User: guest
Password: Guest111
Hostname:159.69.8.94
Port:5432
DB: test
User: guest
Password: guest
Представления (View)
Первоначально SQL задумывался как набор команд для непосредственного общения пользователя с системой. Эта идея достаточно быстро и вполне предсказуемо провалилась, после чего SQL превратился в инструмент разработки, а значит, как и у любого другого инструмента разработки, у него в первую очередь появилась потребность в абстрагировании. Так на свет появились представления (View).
Представления дали SQL возможность отделять объявление функционала от его реализации. Так, например, разработчик может создать представление остатки:
CREATE VIEW balance AS
SELECT stock, product, SUM(quantity) AS quantity
FROM
(SELECT receipt.stock, product, quantity
FROM receiptDetail
JOIN receipt ON receiptDetail.receipt = receipt.id
UNION ALL
SELECT shipment.stock, product, -quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
) details
GROUP BY stock, product
И дальше обращаться к этому представлению как к обычной таблице.
SELECT product.name, balance.stock, balance.quantity
FROM product
JOIN balance ON product.id = balance.product
WHERE product.name = 'Product 8693'
При этом, если ему понадобится изменить логику вычисления остатков, он может легко это сделать, просто поменяв само представление остатков, после чего все запросы, которые использовали это представление, будут работать уже с новой его реализацией.
Как и в структурном программировании, абстрагирование дало SQL две очень важных возможности: упрощение и повторное использование. Казалось бы — вот оно счастье, но дальше, как говорится, что-то пошло не так.
View: Материализация представлений поддерживается в очень частных случаях
Если вы начнете использовать описанное выше представление остатков в реальной базе данных, вы, скорее всего, очень быстро обнаружите деградацию производительности. Дело в том, что остатки обычно используются очень часто, а значит, вычислять остатки при каждом обращении к ним будет очень накладно. Соответственно, чтобы избежать этих расходов, логично такое представление «материализовать», то есть сохранить в таблицу и автоматически обновлять ее при изменении данных, которые это представление использует. Это чуть замедлит запись, но очень сильно ускорит чтение. Казалось бы, что может быть проще. Но проблема в том, что поддерживать такую материализацию, мягко говоря, не так легко, как кажется. Во всяком случае, чтобы это работало эффективно на больших объемах. В вышеприведенным представлении остатков, например, при изменении склада в документе нужно от остатков по старому складу для всех товаров в документе отнять количество в этом документе, после чего добавить это количество к остаткам по новому складу. А при изменении количества для строки в документе нужно к остаткам по складу добавить разницу между старым и новым количеством. Если же, к примеру, в логику остатков добавить внутренние перемещения, все совсем запутается.
Посмотрим, что в этом плане умеют современные СУБД.
У PostgreSQL все просто, материализованные представления он поддерживает, но никаких инкрементальных обновлений, тем более выполняемых сразу при применении транзакции он не поддерживает.
В MS SQL есть так называемые индексированные представления (по сути те же материализованные представления), которые умеют инкрементально обновляться, но список ограничений там впечатляет. Собственно, поддерживаются только SUM GROUP BY и INNER JOIN, то есть даже вышеупомянутый элементарный пример с остатками в MS SQL работать не будет. Плюс в MS SQL нельзя ссылаться на другие представления, поэтому можно смело считать, что материализация представлений в MS SQL не поддерживается вообще.
У механизма FAST REFRESH в Oracle список ограничений еще длиннее чем у MS SQL, но фактически он сводится к следующему:
- Из агрегирующих функций для GROUP BY поддерживается только SUM и ее производные (например COUNT, AVERAGE)
- Не поддерживаются оконные функции, рекурсивные функции, табличные функции и любые другие «нестандартные» возможности.
- Все подзапросы (и представления) с GROUP BY, UNION ALL, OUTER JOIN также нужно материализовать.
- У OUTER JOIN и формул есть какие-то проблемы с производительностью (правда, не уточняется, какие именно)
- Несколько специфических требований, что если есть SUM, то должен быть COUNT, для всех таблиц должны быть включены логи материализованных представлений, UNION’ы не поддерживаются и их нужно переписывать в UNION ALL и т.п.
Третий и пятый пункты означают, что если вы захотели материализовать какое-то представление, вам надо переписать и материализовать все используемые этим представлением подзапросы / другие представления, что, во-первых, убивает всю идею абстрагирования, а во-вторых, приводит к созданию огромного количества избыточных материализованных представлений. Вторая проблема усугубляется еще тем фактом, что все представления материализуются в отдельные таблицы, то есть нельзя, к примеру, создать одну таблицу с ключами склад и товар и материализовать туда сразу несколько представлений: например, остатков, резервов, наличия в ассортименте и т.п. Соответственно, каждое материализованное представление требует дополнительных ресурсов на хранение его ключей / индексов, а также на их обновление и соединение в запросах, где это представление используется.
Но даже если вы прорветесь через все эти ограничения, проблемы с производительностью, абстрагированием и избыточным потреблением ресурсов и все таки материализуете нужное представление, вас будет ждать еще один неприятный сюрприз: материализованное представление обновляется только в самом конце транзакции.
То есть, если у вас есть код:
START TRANSACTION ;
…
UPDATE receiptDetail SET quantity = quantity + 1;
...
SELECT quantity FROM balance WHERE stock = 14 AND sku=65
…
COMMIT;
то, если представление balance не материализовано, вы получите актуальный остаток (с учетом изменения строки документа), а если материализовано, вы получите остаток на начало транзакции. Как следствие:
- процесс материализации становится непрозрачным — при материализации / разматериализации необходимо просмотреть все использования представления на предмет изменения поведения в транзакции
- абсолютно непонятно, как в принципе получить актуальные данные для материализованного представления внутри транзакции
Таким образом, в Oracle поддержка материализаций вроде как лучше, чем в MSSQL, но все равно очень далека от жизнеспособности. Поэтому большинство разработчиков, когда их спрашиваешь про материализованные представления, отвечают, что они вроде и слышали о такой возможности, но реально на практике никогда ей не пользовались. С другой стороны, если вы спросите этих же разработчиков, как именно надо решать задачу материализации тех же остатков, скорее всего услышите что-то про триггеры, общие точки изменения остатков, неправильную физическую модель и так далее. А на уточняющие вопросы — триггеры на что, как именно организовывать эти общие точки и физическую модель, скорее всего, услышите ответ: «я стратег, а не тактик» и вообще «доктор, откуда у вас такие картинки», я с такой задачей никогда не сталкивался. Хотя в то, что человек, который разрабатывает ИС, никогда не сталкивался с задачей хранения и обновления остатков (ну или задолженностей и других похожих показателей), как-то не очень верится.
На самом деле задачи материализации обычно решаются именно что «как-то». Логика вычисления и обновления представления дублируются. Часть сценариев обновления запрещается (например, полностью запрещают изменять документ). Еще иногда используют такой трюк как «перепроведение»: сначала эмулируют полное удаление старых данных (например всего документа), а потом добавление новых данных (то есть создание документа заново). В таком случае достаточно реализовать только логику удаления и добавления, что значительно проще, но куда менее производительно (например, в случаях, когда изменяется только одна строка документа).
View: Для представлений не поддерживаются ограничения и триггеры
Допустим, вы создали представление остатков как в примере выше, используете его для различных вычислений, и тут у вас появляется новое бизнес-требование: остаток должен быть больше 0. Если бы эти данные были первичными и хранились в таблице, вы могли бы легко решить эту проблему, создав соответствующее ограничение на нужное поле, но это представление, а для представлений возможность создавать ограничения ни одна из существующих современных СУБД не поддерживает.
Единственное, что могут предложить в этом плане некоторые коммерческие СУБД (MS SQL и Oracle) — это материализовать представление и создать ограничение для него. Но здесь мы опять-таки возвращаемся к огромному количеству ограничений материализованных представлений. Тут, правда, стоит отметить, что проблема не обновления материализованных представлений в транзакции для ограничений не актуальна. Зато есть другая проблема: ограничения проверяются в самом конце транзакции. Это, в свою очередь, означает, что сначала выполняется вся бизнес-логика системы (а это может быть достаточно большой объем работы), и, если в самом конце вдруг нарушится какое-то ограничение, то вся выполненная работа будет отменена, а значит ресурсы сервера и время пользователя будут потрачены впустую.
Вообще тему ограничений на материализованные представления (и материализованные представления вообще) относительно подробно разбирал один из достаточно авторитетных экспертов Oracle Donald Burleson в одной из своих книг. И, в общем-то, пришел к тому же выводу, что и я:
This is pointless from a practical perspective, however.
Правда следующий абзац:
Sooner or later incremental refresh limitations will be lifted. For the purpose of further constraint study in this book, let’s continue pretending as if it already happened.
получился у него весьма ироничным c учетом того, что книга писалась в 2005 году, а сейчас уже 2019 год, и за последние 14 лет список ограничений практически не изменился.
Но если для ограничений такой workaround с материализованными представлениями еще как-то может работать, то с триггерами вообще забавно:
If you create a trigger on a base table of a materialized view, then you must ensure that the trigger does not fire during a refresh of the materialized view. During refresh, the DBMS_MVIEW procedure I_AM_A_REFRESH returns TRUE.
То есть триггеры создавать можно, но делать в них ничего нельзя, так как для материализованных представлений они ведут себя очень непредсказуемо. Такой кот Шредингера, вроде триггер есть, а вроде его и нет. Тут можно найти чуть более подробный разбор этой темы на Ask Tom.
View: В параметризованные представления во FROM можно передавать только константы
Теперь представим, что нам нужно получить не просто остаток, а остаток на дату. Если бы у нас была таблица со «всеми» датами (например dates), то мы могли бы создать следующее представление:
CREATE VIEW balance AS
SELECT stock, product, dates.value AS date, SUM(quantity) AS quantity
FROM
(SELECT receipt.stock, product, receipt.date, quantity
FROM receiptDetail
JOIN receipt ON receiptDetail.receipt = receipt.id
UNION ALL
SELECT shipment.stock, product, shipment.date, -quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
) details
JOIN dates ON details.date < dates.value
GROUP BY stock, product, dates.value
Но такой таблицы в SQL не существует, соответственно, для решения этой задачи придется использовать что-то другое.
В MS SQL для решения таких задач есть так называемые table inlined функции, в них можно объявить параметры и использовать их внутри запроса:
CREATE FUNCTION balance (
@date DATE
)
RETURNS TABLE
AS
RETURN
SELECT stock, product, SUM(quantity) AS quantity
FROM
(SELECT receipt.stock, product, quantity
FROM receiptDetail
JOIN receipt ON receiptDetail.receipt = receipt.id
WHERE receipt.date < @date
UNION ALL
SELECT shipment.stock, product, -quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
WHERE shipment.date < @date
) details
GROUP BY stock, product
В свою очередь, во FROM этим функциям можно передавать аргументы, но только не в ON, а в скобках:
SELECT product.name, balance.stock, balance.quantity
FROM product
JOIN balance('07.07.2019') ON product.id = balance.product
WHERE product.name = 'Product 8693'
В остальном же эти функции ведут себя точно так же, как и представления (часто их и называют параметризованными представлениями).
Даже если оставить в стороне тот факт, что для работы с параметрами примитивных типов в SQL нужна отдельная абстракция, у этого механизма есть одно очень неприятное ограничение, которое делает его применение весьма ограниченным. Так, при использовании во FROM этим функциям нельзя передавать в качестве аргументов колонки других таблиц из FROM. То есть, если попытаться выполнить следующий запрос:
SELECT shipmentDetail.id, b.quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
JOIN balance(shipment.date) b ON b.stock = shipment.stock AND b.product = shipmentDetail.product
WHERE shipmentDetail.quantity = 5
SQL сервер выдаст ошибку, что таблица shipment не найдена. Тут, конечно, можно подумать, что так и надо, потому как использование таблицы из FROM в аргументах параметризованного представления нарушает идеологию свободной перестановки JOIN (то есть, что JOIN’ы можно переставить в любом порядке, в том числе соединять shipment после balance) и поэтому такой возможности в SQL не может быть теоретически. Но на самом деле многие SQL сервера умеют использовать в подзапросах поля из уже соединенных таблиц (мы увидим это в следующем разделе про Join Predicate Push Down), поэтому они вполне могли бы разрешить это делать разработчику, просто убирая при перестановке JOIN’ов варианты, когда параметризованное представление (или вообще любой подзапрос) соединяется до таблицы, поля которой оно используют. И почему ни один производитель СУБД так не сделал, если честно, для меня загадка.
В любом случае, факт остается фактом, и сейчас единственный выход для разработчика в таких случаях это самостоятельно делать pushdown верхнего контекста внутрь представления:
SELECT shipmentDetail.id, b.quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
JOIN (SELECT stock, product, dates.date, SUM(quantity) AS quantity
FROM
(SELECT receipt.stock, product, receipt.date, quantity
FROM receiptDetail
JOIN receipt ON receiptDetail.receipt = receipt.id
UNION ALL
SELECT shipment.stock, product, shipment.date, -quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
) details
JOIN
(SELECT shipment.date
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
WHERE shipmentDetail.quantity = 5
GROUP BY shipment.date
) dates ON details.date < dates.date
GROUP BY stock, product, dates.date
) b ON b.stock = shipment.stock AND b.product = shipmentDetail.product AND b.date = shipment.date
WHERE shipmentDetail.quantity = 5
а значит, неоднократно повторять логику вычисления этого представления в различных запросах, и, тем самым, нарушать один из ключевых принципов программирования — Don’t repeat yourself.
Подытоживая все вышесказанное касательно представлений, можно сделать вывод, что представления, хоть и выглядят как таблицы, но по факту не умеют и четверти того, что умеют таблицы, поэтому, к сожалению, в состоянии обеспечить в SQL лишь самый базовый уровень абстрагирования (а точнее, очень низкий, по сравнению с тем, который мог бы быть, если бы все вышеописанные возможности поддерживались в общем случае)
Join Predicate Push Down (JPPD)
Проталкивание предикатов внутрь подзапросов является одной из самых важных возможностей оптимизатора SQL сервера. Этот механизм позволяет вычислять результаты подзапросов не для всех данных в базе (тем самым приводя порой к катастрофической деградации производительности), а только для тех данных, которые необходимы верхнему запросу.
SELECT balance.quantity
FROM (SELECT product, SUM(quantity) AS quantity
FROM
(SELECT product, quantity
FROM receiptDetail
JOIN receipt ON receiptDetail.receipt = receipt.id
UNION ALL
SELECT product, -quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
) details
GROUP BY product
) balance
WHERE balance.product = 345
В этом случае SQL сервер видит, что снаружи есть условие на то, что поле product подзапроса должно быть равно 345, и автоматически переносит это условие туда. Заодно, так как в этом подзапросе есть группировка по полю product, SQL сервер автоматически убирает это поле из BY (так как оно всегда равно одному значению), а так как других BY в подзапросе нет, то и весь GROUP BY целиком. Итого получается следующий запрос:
SELECT balance.quantity
FROM (SELECT SUM(quantity) AS quantity
FROM
(SELECT product, quantity
FROM receiptDetail
JOIN receipt ON receiptDetail.receipt = receipt.id
UNION ALL
SELECT product, -quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
) details
WHERE details.product = 345
) balance
Далее SQL сервер видит такую же ситуацию с UNION подзапросами и автоматически переносит этот предикат внутрь каждого из UNION:
SELECT balance.quantity
FROM (SELECT SUM(quantity) AS quantity
FROM
(SELECT product, quantity
FROM receiptDetail
JOIN receipt ON receiptDetail.receipt = receipt.id
WHERE product = 345
UNION ALL
SELECT product, -quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
WHERE product = 345
) details
) balance
В итоге при наличии индекса по product в shipmentDetail и в receiptDetail такой запрос выполнится ну очень быстро.
Теперь рассмотрим более сложный случай:
SELECT balance.quantity
FROM product
JOIN
(SELECT product, SUM(quantity) AS quantity
FROM
(SELECT product, quantity
FROM receiptDetail
JOIN receipt ON receiptDetail.receipt = receipt.id
UNION ALL
SELECT product, -quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
) details
GROUP BY product
) balance ON balance.product = product.id
WHERE product.group = 54
Как и при выполнении любого другого запроса SQL сервер начинает перебор порядков выполнения join. Допустим он уже выбрал, что первым join’ом будет product и что доставать записи оттуда он будет при помощи индекса по group (предположим, что он есть). Далее он пытается присоединить к результату подзапрос balance, например при помощи nested loop join (то есть пробегом по уже имеющемуся результату, в нашем случае — таблице product, отфильтрованной по полю group). В этот момент SQL сервер видит, что у него есть предикат balance.product = product.id, где product.id — константа, то есть, точь-в-точь предикат из примера выше, а значит можно запустить соответствующую технику predicate push, что он собственно и делает.
Rows Executes Stmt Text 41 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[product].[id])) 41 1 |--Index Seek(OBJECT:([test].[dbo].[product].[product_group]), SEEK:([test].[dbo].[product].[group]=(54)) ORDERED FORWARD) 0 0 |--Compute Scalar(DEFINE:([Expr1013]=CASE WHEN [globalagg1020]=(0) THEN NULL ELSE [globalagg1022] END)) 41 41 |--Stream Aggregate(DEFINE:([globalagg1020]=SUM([partialagg1019]), [globalagg1022]=SUM([partialagg1021]))) 82 41 |--Concatenation 41 41 |--Stream Aggregate(DEFINE:([partialagg1019]=COUNT_BIG([test].[dbo].[receiptdetail].[quantity]), [partialagg1021]=SUM([test].[dbo].[receiptdetail].[quantity]))) 809 41 | |--Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[receiptdetail].[id])) 809 41 | |--Index Seek(OBJECT:([test].[dbo].[receiptdetail].[receiptdetail_product_fk]), SEEK:([test].[dbo].[receiptdetail].[product]=[test].[dbo].[product].[id]) ORDERED FORWARD) 809 809 | |--Clustered Index Seek(OBJECT:([test].[dbo].[receiptdetail].[PK__receiptd__3213E83F930DCBDA]), SEEK:([test].[dbo].[receiptdetail].[id]=[test].[dbo].[receiptdetail].[id]) LOOKUP ORDERED FORWARD) 41 41 |--Stream Aggregate(DEFINE:([partialagg1019]=COUNT_BIG([Expr1010]), [partialagg1021]=SUM([Expr1010]))) 0 0 |--Compute Scalar(DEFINE:([Expr1010]= -[test].[dbo].[shipmentdetail].[quantity])) 8383 41 |--Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[shipmentdetail].[id], [Expr1023]) WITH UNORDERED PREFETCH) 8383 41 |--Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_product_fk]), SEEK:([test].[dbo].[shipmentdetail].[product]=[test].[dbo].[product].[id]) ORDERED FORWARD) 8383 8383 |--Clustered Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[PK__shipment__3213E83F2FF9E454]), SEEK:([test].[dbo].[shipmentdetail].[id]=[test].[dbo].[shipmentdetail].[id]) LOOKUP ORDERED FORWARD)
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11247 (100)| |
| 1 | NESTED LOOPS | | 50 | 1100 | 11247 (1)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | PRODUCT | 50 | 450 | 45 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PRODUCT_GROUP | 50 | | 1 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | | 1 | 13 | 224 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | SORT AGGREGATE | | 1 | 26 | | |
| 7 | VIEW | | 218 | 5668 | 224 (0)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| RECEIPTDETAIL | 20 | 180 | 23 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | RECEIPTDETAIL_PRODUCT_FK | 20 | | 3 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED| SHIPMENTDETAIL | 198 | 1584 | 201 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | SHIPMENTDETAIL_PRODUCT_FK | 198 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PRODUCT"."group"=54)
5 - filter(COUNT(*)>0)
10 - access("RECEIPTDETAIL"."PRODUCT"="PRODUCT"."ID")
12 - access("SHIPMENTDETAIL"."PRODUCT"="PRODUCT"."ID")
Тем самым подзапрос balance рассчитывается только для товаров с группой 54, а не для всех товаров в базе (правда, нужно понимать, что в этом случае подзапрос рассчитывается несколько раз, для каждого товара с группой 54).
Тут, конечно, может показаться, что техника JPPD может работать только для nested loop join, но это не так, SQL сервера умеют проталкивать и hash join. В этом случае проталкивается не предикат balance.product = значение, а «виртуальный» предикат hash (balance.product) = значение («виртуальный», потому как у этого предиката нет синтаксического эквивалента в SQL, но тем не менее для выполнения он используется).
Вообще у механизма JPPD есть другое, более «декларативное» объяснение — через переписывание запроса. Так, верхний запрос можно переписать в виде:
SELECT balance.quantity
FROM product
JOIN
(SELECT SUM(quantity) AS quantity
FROM
(SELECT quantity
FROM receiptDetail
JOIN receipt ON receiptDetail.receipt = receipt.id
WHERE receiptDetail.product = product.id
UNION ALL
SELECT -quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
WHERE shipmentDetail.product = product.id
) details
) balance ON TRUE
WHERE product.group = 54
И именно в таком ключе (с переписыванием запроса) механизм JPPD описан в патенте Oracle. Однако, на мой взгляд, такое описание не совсем корректно, потому как ни в одном SQL сервере (в том числе Oracle) нельзя обращаться к полям таблиц из верхнего запроса (на самом деле, непонятно почему, но на этом вопросе мы уже останавливались, когда говорили о параметризованных представлениях), а значит, понять, как именно работает JPPD, из такого описания очень сложно.
Справедливости ради, надо сказать, что SQL сервера не всегда делают описанные выше оптимизации автоматически. На самом деле, они только пытаются сделать такой predicate push down, то есть строят соответствующий план, затем рассчитывают стоимость его выполнения и сравнивают его со стоимостью выполнения запроса без проталкивания внутрь предикатов. И только если стоимость плана без проталкивания выше, выбирают план с проталкиванием.
Отметим, что механизм JPPD особенно важен для описанного выше механизма представлений. И если бы JPPD не существовало, то и в механизме представлений тоже было бы очень мало смысла, потому как представления рассчитывались бы для всей (!) базы при каждом обращении к ним, а значит производительность представлений была бы просто ужасная.
Итак, как мы видим, механизм JPPD в СУБД реализуется относительно просто, но у этой простоты есть и обратная сторона медали.
JPPD: Не работает с оконными функциями и рекурсивными CTE
Допустим, мы хотим получить порядковый номер строки в документе и напишем следующий запрос:
SELECT id, seqnum
FROM
(SELECT id, row_number() OVER (PARTITION BY shipment ORDER BY id) AS seqnum
FROM shipmentdetail
) t
WHERE t.id=6770436
Rows Executes Stmt Text 1 1 |--Filter(WHERE:([test].[dbo].[shipmentdetail].[id]=(6770436))) 10000001 1 |--Window Aggregate(DEFINE:([Expr1002]=row_number), PARTITION COLUMNS:([test].[dbo].[shipmentdetail].[shipment]), ROWS BETWEEN:(UNBOUNDED, CURRENT ROW)) 10000001 1 |--Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_shipment_fk]), ORDERED FORWARD)
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 57555 (100)| |
|* 1 | VIEW | | 10M| 247M| | 57555 (1)| 00:00:03 |
| 2 | WINDOW SORT | | 10M| 104M| 191M| 57555 (1)| 00:00:03 |
| 3 | TABLE ACCESS FULL| SHIPMENTDETAIL | 10M| 104M| | 13841 (1)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."ID"=6770436)
Как можно увидеть из плана, SQL сервер рассчитывает номера строк для всех документов в базе и, как следствие, запрос выполняется целую секунду (вместо нескольких миллисекунд).
JPPD: Низкая эффективность при работе с денормализованными данными
Допустим, мы хотим получим получить все отгрузки с даты по дату вместе с общими суммами отгрузки клиентам (немного надуманный случай, тут правильнее было бы говорить о, скажем, сумме задолженности клиента, но базовый пример очень простой, а за его пределы выходить не хочется, поэтому будем использовать то, что есть):
SELECT shipment.id, supplied.quantity
FROM shipment
JOIN (
SELECT customer, SUM(quantity) AS quantity
FROM shipmentDetail
JOIN shipment ON shipment.id = shipmentDetail.shipment
GROUP BY customer
) supplied ON supplied.customer = shipment.customer
WHERE shipment.date = DATEADD(day, -10, CAST(GETDATE() AS date))
В этом случае SQL сервер будет рассчитывать сумму не для всех различных клиентов, по ко
