Без преувеличения можно сказать, что SQL — один из самых распространенных в мире языков. Информационные системы могут быть написаны на Java, Python, JavaScript, C#, PHP и десятке других языков, но SQL база в том или ином виде будет в абсолютном большинстве таких систем. Среди бизнес-приложений процент систем, использующих SQL, вообще стремится к 100%.
При этом большинство существующих материалов о SQL на хабре и других ресурсах сводятся к простым вопросам, вроде: «какие типы соединений бывают», «чем левое соединение отличается от правого», «что такое триггеры» и так далее. Более того, в этих материалах практически ничего не говорится о проблемах SQL (и его реализациях), которых на самом деле очень и очень немало. Поэтому мы решили восполнить оба этих пробела: с одной стороны рассказать, как многие вещи в SQL работают изнутри, а с другой стороны — почему они работают не так как нужно / хотелось бы.
При этом речь в статье пойдет не о «вкусах и цветах фломастеров». Все затрагиваемые проблемы носят фундаментальный характер: присутствуют при разработке практически любой информационной системы и не ограничиваются «красотой кода», а в той или иной степени приводят либо к критическому падению производительности, либо к существенному росту порога вхождения, либо к значительным трудозатратам со стороны разработчика.
Статья получилась достаточно объемной, и далеко не все осилят ее за один раз. Поэтому, чтобы было удобнее в ней ориентироваться, а также иметь возможность оценить «масштабы бедствия», начнем с оглавления со списком всех затрагиваемых проблем:
- Представления (View)
- Join Predicate Push Down (JPPD)
- Разделение логики условий на типы JOIN и WHERE
- Плохая оптимизация OR
- Плохая оптимизация при работе с разреженными данными
- Плохая оптимизация при работе с последними значениями
- Проблема N+1
- Высокая цена ошибки при проектировании БД
- Непредсказуемая оптимизация при работе с большим количеством JOIN
- Отсутствие наследования и полиморфизма
- Избыточно низкий уровень абстрагирования
- Адаптивная оптимизация(AO)
Тестироваться все будет на трех РСУБД — PostgreSQL, MS SQL Server и Oracle. Все СУБД последних версий — 11, 2019 и 19c соответственно.
Перед тем как начать, создадим базу с товарами, контрагентами и документами приходов и отгрузок. Наполним ее данными, причем большим количеством, так как дальше будет много примеров с демонстрацией проблем оптимизации запросов / производительности на больших объемах данных.
Скрипт заполнения базы в Oracle
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;
Скрипт заполнения базы в MSSQL
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;
Скрипт заполнения базы в PostgreSQL
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;
Уже заполненные базы данных, на которых проводилось тестирование, и параметры подключения к ним можно найти здесь:
Параметры подключения к Oracle
Hostname:116.203.61.136
Port:1521
SID:orclcdb
User/DB:test
Password:test
Port:1521
SID:orclcdb
User/DB:test
Password:test
Параметры подключения к MS SQL
Hostname:195.201.126.177
DB:test
User:guest
Password:Guest111
DB:test
User:guest
Password:Guest111
Параметры подключения к PostgreSQL
Hostname:159.69.8.94
Port:5432
DB:test
User:guest
Password:guest
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 не поддерживается вообще.
С Oracle все немного сложнее
За инкрементальную обновляемость представлений отвечает опция FAST REFRESH.
У механизма FAST REFRESH в Oracle список ограничений еще длиннее чем у MS SQL, но фактически он сводится к следующему:
Третий и пятый пункты означают, что если вы захотели материализовать какое-то представление, вам надо переписать и материализовать все используемые этим представлением подзапросы / другие представления, что, во-первых, убивает всю идею абстрагирования, а во-вторых, приводит к созданию огромного количества избыточных материализованных представлений. Вторая проблема усугубляется еще тем фактом, что все представления материализуются в отдельные таблицы, то есть нельзя, к примеру, создать одну таблицу с ключами склад и товар и материализовать туда сразу несколько представлений: например, остатков, резервов, наличия в ассортименте и т.п. Соответственно, каждое материализованное представление требует дополнительных ресурсов на хранение его ключей / индексов, а также на их обновление и соединение в запросах, где это представление используется.
Но даже если вы прорветесь через все эти ограничения, проблемы с производительностью, абстрагированием и избыточным потреблением ресурсов и все таки материализуете нужное представление, вас будет ждать еще один неприятный сюрприз: материализованное представление обновляется только в самом конце транзакции.
То есть, если у вас есть код:
то, если представление balance не материализовано, вы получите актуальный остаток (с учетом изменения строки документа), а если материализовано, вы получите остаток на начало транзакции. Как следствие:
У механизма 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'ов варианты, когда параметризованное представление (или вообще любой подзапрос) соединяется до таблицы, поля которой оно используют. И почему ни один производитель СУБД так не сделал, если честно, для меня загадка.
UPD: Пришла подсказка из зала, что такую возможность производители СУБД все же реализовали в виде специальной конструкции APPLY (или опции LATERAL в JOIN). Даже если опять-таки отбросить факт необходимости использования еще одной абстракции для реализации по сути одного частного случая, у подхода с APPLY есть два недостатка (один из которых весьма существенный):
Проблемы APPLY
- Так как в этих синтаксических конструкциях нету ON, для OUTER APPLY придется делать подзапрос (в CROSS APPLY условие соединения можно писать в WHERE):
SELECT shipmentDetail.id, b.quantity FROM shipmentDetail JOIN shipment ON shipmentDetail.shipment = shipment.id OUTER APPLY ( SELECT quantity FROM balance(shipment.date) WHERE stock = shipment.stock AND product = shipmentDetail.product ) b WHERE shipmentDetail.quantity = 5
- Но самое главное, APPLY в таких случаях — это практически гарантировано nested loop join. То есть, если вы попытаетесь выполнить верхний запрос, вы получите следующие планы:
План MS SQLSELECT shipmentDetail.id, b.quantity FROM shipmentDetail JOIN shipment ON shipmentDetail.shipment = shipment.id CROSS APPLY balance(shipment.date) b WHERE shipmentDetail.quantity = 5 AND b.stock = shipment.stock AND b.product = shipmentDetail.product OPTION (MAXDOP 1) |--Filter(WHERE:([Union1013]=[test].[dbo].[shipment].[stock])) |--Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[shipmentdetail].[product], [test].[dbo].[shipment].[date])) |--Hash Match(Inner Join, HASH:([test].[dbo].[shipment].[id])=([test].[dbo].[shipmentdetail].[shipment])) | |--Clustered Index Scan(OBJECT:([test].[dbo].[shipment].[PK__shipment__3213E83F4B7B4D07])) | |--Clustered Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[PK__shipment__3213E83F996CFFF4]), WHERE:([test].[dbo].[shipmentdetail].[quantity]=(5.000))) |--Compute Scalar(DEFINE:([Expr1016]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END)) |--Stream Aggregate(GROUP BY:([Union1013]) DEFINE:([Expr1023]=COUNT_BIG([Union1015]), [Expr1024]=SUM([Union1015]))) |--Sort(ORDER BY:([Union1013] ASC)) |--Concatenation |--Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[receiptdetail].[receipt])) | |--Index Spool(SEEK:([test].[dbo].[receiptdetail].[product]=[test].[dbo].[shipmentdetail].[product])) | | |--Clustered Index Scan(OBJECT:([test].[dbo].[receiptdetail].[PK__receiptd__3213E83FE8063B8C])) | |--Clustered Index Seek(OBJECT:([test].[dbo].[receipt].[PK__receipt__3213E83FE2F580DF]), SEEK:([test].[dbo].[receipt].[id]=[test].[dbo].[receiptdetail].[receipt]), WHERE:([test].[dbo].[receipt].[date]<[test].[dbo].[shipment].[date]) ORDERED FORWARD) |--Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[shipmentdetail].[shipment], [Expr1022]) WITH UNORDERED PREFETCH) |--Index Spool(SEEK:([test].[dbo].[shipmentdetail].[product]=[test].[dbo].[shipmentdetail].[product])) | |--Compute Scalar(DEFINE:([Expr1012]= -[test].[dbo].[shipmentdetail].[quantity])) | |--Clustered Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[PK__shipment__3213E83F996CFFF4])) |--Clustered Index Seek(OBJECT:([test].[dbo].[shipment].[PK__shipment__3213E83F4B7B4D07]), SEEK:([test].[dbo].[shipment].[id]=[test].[dbo].[shipmentdetail].[shipment]), WHERE:([test].[dbo].[shipment].[date]<[test].[dbo].[shipment].[date]) ORDERED FORWARD)
План Oracle--------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 637K| 44M| | 26G (1)|288:02:33 | | 1 | NESTED LOOPS | | 637K| 44M| | 26G (1)|288:02:33 | |* 2 | HASH JOIN | | 901K| 30M| 2736K| 15542 (1)| 00:00:01 | | 3 | TABLE ACCESS FULL | SHIPMENT | 100K| 1562K| | 137 (1)| 00:00:01 | |* 4 | TABLE ACCESS FULL | SHIPMENTDETAIL | 909K| 16M| | 13935 (1)| 00:00:01 | |* 5 | VIEW | VW_LAT_F55B5580 | 1 | 39 | | 29439 (1)| 00:00:02 | | 6 | SORT GROUP BY | | 393K| 8061K| 167M| 29439 (1)| 00:00:02 | | 7 | VIEW | | 5457K| 109M| | 15337 (1)| 00:00:01 | | 8 | UNION-ALL | | | | | | | |* 9 | HASH JOIN | | 498K| 13M| | 1252 (1)| 00:00:01 | |* 10 | TABLE ACCESS FULL| RECEIPT | 4984 | 74760 | | 15 (0)| 00:00:01 | | 11 | TABLE ACCESS FULL| RECEIPTDETAIL | 1000K| 12M| | 1234 (1)| 00:00:01 | |* 12 | HASH JOIN | | 4959K| 137M| | 14085 (1)| 00:00:01 | |* 13 | TABLE ACCESS FULL| SHIPMENT | 50001 | 781K| | 137 (1)| 00:00:01 | | 14 | TABLE ACCESS FULL| SHIPMENTDETAIL | 10M| 123M| | 13921 (1)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SHIPMENTDETAIL"."SHIPMENT"="SHIPMENT"."ID") 4 - filter("SHIPMENTDETAIL"."QUANTITY"=5) 5 - filter("B"."STOCK"="SHIPMENT"."STOCK" AND "B"."PRODUCT"="SHIPMENTDETAIL"."PRODUCT") 9 - access("RECEIPTDETAIL"."RECEIPT"="RECEIPT"."ID") 10 - filter("RECEIPT"."date"<"SHIPMENT"."date") 12 - access("SHIPMENTDETAIL"."SHIPMENT"="SHIPMENT"."ID") 13 - filter("SHIPMENT"."date"<"SHIPMENT"."date")
То есть пробег по всем shipmentDetail с quantity > 5 и для каждого расчет остатков, что ну очень неэффективно. Мне даже не удалось дождаться выполнения этих запросов поэтому привожу только estimate планы.
В любом случае, факт остается фактом, и сейчас единственный выход для разработчика в таких случаях это самостоятельно делать 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 сервера. Этот механизм позволяет вычислять результаты подзапросов не для всех данных в базе (тем самым приводя порой к катастрофической деградации производительности), а только для тех данных, которые необходимы верхнему запросу.
Как этот механизм работает в современных СУБД
Сначала рассмотрим простой пример, когда у нас есть условие, что значение поля из подзапроса должно быть равно некоторой константе (то есть случай просто predicate push down, без join):
В этом случае SQL сервер видит, что снаружи есть условие на то, что поле product подзапроса должно быть равно 345, и автоматически переносит это условие туда. Заодно, так как в этом подзапросе есть группировка по полю product, SQL сервер автоматически убирает это поле из BY (так как оно всегда равно одному значению), а так как других BY в подзапросе нет, то и весь GROUP BY целиком. Итого получается следующий запрос:
Далее SQL сервер видит такую же ситуацию с UNION подзапросами и автоматически переносит этот предикат внутрь каждого из UNION:
В итоге при наличии индекса по product в shipmentDetail и в receiptDetail такой запрос выполнится ну очень быстро.
Теперь рассмотрим более сложный случай:
Как и при выполнении любого другого запроса SQL сервер начинает перебор порядков выполнения join. Допустим он уже выбрал, что первым join'ом будет product и что доставать записи оттуда он будет при помощи индекса по group (предположим, что он есть). Далее он пытается присоединить к результату подзапрос balance, например при помощи nested loop join (то есть пробегом по уже имеющемуся результату, в нашем случае — таблице product, отфильтрованной по полю group). В этот момент SQL сервер видит, что у него есть предикат balance.product = product.id, где product.id — константа, то есть, точь-в-точь предикат из примера выше, а значит можно запустить соответствующую технику predicate push, что он собственно и делает.
Тем самым подзапрос balance рассчитывается только для товаров с группой 54, а не для всех товаров в базе (правда, нужно понимать, что в этом случае подзапрос рассчитывается несколько раз, для каждого товара с группой 54).
Тут, конечно, может показаться, что техника JPPD может работать только для nested loop join, но это не так, SQL сервера умеют проталкивать и hash join. В этом случае проталкивается не предикат balance.product = значение, а «виртуальный» предикат hash(balance.product) = значение («виртуальный», потому как у этого предиката нет синтаксического эквивалента в SQL, но тем не менее для выполнения он используется).
Вообще у механизма JPPD есть другое, более «декларативное» объяснение — через переписывание запроса. Так, верхний запрос можно переписать в виде:
И именно в таком ключе (с переписыванием запроса) механизм JPPD описан в патенте Oracle. Однако, на мой взгляд, такое описание не совсем корректно, потому как ни в одном SQL сервере (в том числе Oracle) нельзя обращаться к полям таблиц из верхнего запроса (на самом деле, непонятно почему, но на этом вопросе мы уже останавливались, когда говорили о параметризованных представлениях), а значит, понять, как именно работает JPPD, из такого описания очень сложно.
Справедливости ради, надо сказать, что SQL сервера не всегда делают описанные выше оптимизации автоматически. На самом деле, они только пытаются сделать такой predicate push down, то есть строят соответствующий план, затем рассчитывают стоимость его выполнения и сравнивают его со стоимостью выполнения запроса без проталкивания внутрь предикатов. И только если стоимость плана без проталкивания выше, выбирают план с проталкиванием.
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, что он собственно и делает.
План MSSQL
Среднее время выполнения: 128мс
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)
План Oracle
Среднее время выполнения: 80мс
-----------------------------------------------------------------------------------------------------------------------
| 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
План MS SQL
Среднее время выполнения: 1.2с
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)
План Oracle
Среднее время выполнения: 14с
----------------------------------------------------------------------------------------------
| 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 сервер будет рассчитывать сумму не для всех различных клиентов, по которым были отгрузки за эти даты, а для всех отгрузок за эти даты, то есть сумма по каждому клиенту может рассчитываться несколько раз.
Правда, эта проблема проявляется в основном, когда есть корреляция между условием отбора и условием соединения (то есть в этом примере между датой отгрузки и клиентом). На практике такое бывает очень часто (например, многим клиентам отгрузка идет только в течении какого-то периода), но конкретно в тестовой базе этой статьи мы генерировали равномерно распределенные данные, поэтому продемонстрировать описанную проблему на этой базе, к сожалению, не получится.
JPPD: Поддерживается только в коммерческих СУБД
Несмотря на простоту реализации, JPPD не поддерживается в PostgreSQL. Даже в самом примитивном случае:
SELECT income.quantity
FROM product
JOIN
(SELECT product, SUM(quantity) AS quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
GROUP BY product
) income ON income.product = product.id
WHERE product."group" = 54
План PostgreSQL
"Hash Join (cost=252901.05..254168.34 rows=49 width=32) (actual time=11580.152..11607.399 rows=48 loops=1)"
" Hash Cond: (shipmentdetail.product = product.id)"
" -> HashAggregate (cost=252763.56..253394.04 rows=50439 width=36) (actual time=11579.912..11603.696 rows=50000 loops=1)"
" Group Key: shipmentdetail.product"
" -> Hash Join (cost=2985.02..202764.28 rows=9999855 width=9) (actual time=46.117..5967.219 rows=10000001 loops=1)"
" Hash Cond: (shipmentdetail.shipment = shipment.id)"
" -> Seq Scan on shipmentdetail (cost=0.00..173528.55 rows=9999855 width=13) (actual time=0.017..1158.157 rows=10000001 loops=1)"
" -> Hash (cost=1735.01..1735.01 rows=100001 width=4) (actual time=45.798..45.798 rows=100001 loops=1)"
" Buckets: 131072 Batches: 1 Memory Usage: 4540kB"
" -> Seq Scan on shipment (cost=0.00..1735.01 rows=100001 width=4) (actual time=0.018..19.940 rows=100001 loops=1)"
" -> Hash (cost=136.88..136.88 rows=49 width=4) (actual time=0.202..0.202 rows=48 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 10kB"
" -> Bitmap Heap Scan on product (cost=4.67..136.88 rows=49 width=4) (actual time=0.045..0.181 rows=48 loops=1)"
" Recheck Cond: ("group" = 54)"
" Heap Blocks: exact=46"
" -> Bitmap Index Scan on product_group (cost=0.00..4.66 rows=49 width=0) (actual time=0.025..0.025 rows=48 loops=1)"
" Index Cond: ("group" = 54)"
"Planning Time: 0.658 ms"
"Execution Time: 11608.602 ms"
По какой причине так получилось, и почему разработчики PostgreSQL заняты чем угодно, но не решением проблемы, из-за который те же представления использовать практически невозможно, если честно, для меня загадка.
Возможно проблема в патенте на JPPD, который у Oracle действует аж до 2028 года (они его постоянно продлевают, внося небольшие изменения). Однако, как мы видели выше, Microsoft'у существование такого патента абсолютно не мешает.
Как видим, все описанные проблемы JPPD хоть и не смертельные, но очень неприятные. Решить их можно, используя более общий механизм JPPD: собирать предикаты из внешнего запроса, группировать их по условиям соединения и полученный запрос добавлять при помощи JOIN внутрь подзапроса.
Так, например, переписанный запрос оконными функциями будет выглядеть следующим образом:
Запрос
SELECT id, seqnum
FROM
(SELECT id, row_number() OVER (PARTITION BY shipment ORDER BY id) AS seqnum
FROM shipmentdetail
JOIN
(SELECT t.shipment AS ps
FROM shipmentdetail t
WHERE t.id = 6770436
) pushed ON pushed.ps = shipment
) t
WHERE t.id=6770436
План MS SQL
Среднее время выполнения: 60мс
Rows Executes Stmt Text 1 1 |--Filter(WHERE:([test].[dbo].[shipmentdetail].[id]=(6770436))) 97 1 |--Sequence Project(DEFINE:([Expr1003]=row_number)) 97 1 |--Segment 97 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[shipment])) 1 1 |--Clustered Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[PK__shipment__3213E83F2FF9E454] AS [t]), SEEK:([t].[id]=(6770436)) ORDERED FORWARD) 97 1 |--Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_shipment_fk]), SEEK:([test].[dbo].[shipmentdetail].[shipment]=[test].[dbo].[shipmentdetail].[shipment] as [t].[shipment]) ORDERED FORWARD)
План Oracle
Среднее время выполнения: 30мс
Predicate Information (identified by operation id):
—
1 — filter(«T».«ID»=6770436)
5 — access(«T».«ID»=6770436)
7 — access(«T».«SHIPMENT»=«SHIPMENT»)
--------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 106 (100)| | |* 1 | VIEW | | 99 | 2574 | 106 (1)| 00:00:01 | | 2 | WINDOW SORT | | 99 | 2178 | 106 (1)| 00:00:01 | | 3 | NESTED LOOPS | | 99 | 2178 | 105 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID | SHIPMENTDETAIL | 1 | 11 | 3 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | SYS_C007881 | 1 | | 2 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID BATCHED| SHIPMENTDETAIL | 99 | 1089 | 102 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | SHIPMENTDETAIL_SHIPMENT_FK | 99 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
—
1 — filter(«T».«ID»=6770436)
5 — access(«T».«ID»=6770436)
7 — access(«T».«SHIPMENT»=«SHIPMENT»)
План PostgreSQL
"Subquery Scan on t (cost=400.40..403.62 rows=1 width=12) (actual time=0.810..0.891 rows=1 loops=1)" " Filter: (t.id = 6770436)" " Rows Removed by Filter: 105" " -> WindowAgg (cost=400.40..402.38 rows=99 width=16) (actual time=0.688..0.869 rows=106 loops=1)" " -> Sort (cost=400.40..400.65 rows=99 width=8) (actual time=0.651..0.668 rows=106 loops=1)" " Sort Key: shipmentdetail.shipment, shipmentdetail.id" " Sort Method: quicksort Memory: 29kB" " -> Nested Loop (cost=5.63..397.12 rows=99 width=8) (actual time=0.094..0.593 rows=106 loops=1)" " -> Index Scan using shipmentdetail_pkey on shipmentdetail t_1 (cost=0.43..8.45 rows=1 width=4) (actual time=0.021..0.023 rows=1 loops=1)" " Index Cond: (id = 6770436)" " -> Bitmap Heap Scan on shipmentdetail (cost=5.19..387.69 rows=98 width=8) (actual time=0.065..0.520 rows=106 loops=1)" " Recheck Cond: (shipment = t_1.shipment)" " Heap Blocks: exact=106" " -> Bitmap Index Scan on shipmentdetail_shipment_fk (cost=0.00..5.17 rows=98 width=0) (actual time=0.029..0.029 rows=106 loops=1)" " Index Cond: (shipment = t_1.shipment)" "Planning Time: 0.671 ms" "Execution Time: 0.984 ms"
Переписанный запрос для работы с денормализованными данными:
Запрос
SELECT shipment.id, supplied.quantity
FROM shipment
JOIN (
SELECT customer, SUM(quantity) AS quantity
FROM shipmentDetail
JOIN shipment ON shipment.id = shipmentDetail.shipment
JOIN (
SELECT customer AS pcust
FROM shipment
WHERE shipment.date = DATEADD(day, -10, CAST(GETDATE() AS date))
GROUP BY customer
) pushcust ON pushcust.pcust = customer
GROUP BY customer
) supplied ON supplied.customer = shipment.customer
WHERE shipment.date = DATEADD(day, -10, CAST(GETDATE() AS date))
Ну и наконец, эту технику можно использовать для оптимизации запросов в PostgreSQL. Так, пример в описании JPPD можно переписать в:
Запрос
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
JOIN product ON product.id = receiptDetail.product
WHERE product.group = 54
UNION ALL
SELECT product, -quantity
FROM shipmentDetail
JOIN shipment ON shipmentDetail.shipment = shipment.id
JOIN product ON product.id = shipmentDetail.product
WHERE product.group = 54
) details
GROUP BY product
) balance ON balance.product = product.id
WHERE product.group = 54
План PostgreSQL
"Hash Join (cost=43596.42..43601.45 rows=1 width=32) (actual time=93.861..93.898 rows=48 loops=1)"
" Hash Cond: (receiptdetail.product = product.id)"
" -> HashAggregate (cost=43458.93..43461.43 rows=200 width=36) (actual time=93.685..93.707 rows=48 loops=1)"
" Group Key: receiptdetail.product"
" -> Append (cost=9.54..43405.03 rows=10780 width=34) (actual time=0.056..89.656 rows=10672 loops=1)"
" -> Nested Loop (cost=9.54..4448.35 rows=980 width=9) (actual time=0.056..7.524 rows=913 loops=1)"
" -> Nested Loop (cost=9.26..4151.50 rows=980 width=13) (actual time=0.043..3.946 rows=913 loops=1)"
" -> Bitmap Heap Scan on product product_1 (cost=4.67..136.88 rows=49 width=4) (actual time=0.017..0.124 rows=48 loops=1)"
" Recheck Cond: ("group" = 54)"
" Heap Blocks: exact=46"
" -> Bitmap Index Scan on product_group (cost=0.00..4.66 rows=49 width=0) (actual time=0.011..0.011 rows=48 loops=1)"
" Index Cond: ("group" = 54)"
" -> Bitmap Heap Scan on receiptdetail (cost=4.59..81.72 rows=21 width=13) (actual time=0.017..0.072 rows=19 loops=48)"
" Recheck Cond: (product = product_1.id)"
" Heap Blocks: exact=910"
" -> Bitmap Index Scan on receiptdetail_product_fk (cost=0.00..4.58 rows=21 width=0) (actual time=0.012..0.012 rows=19 loops=48)"
" Index Cond: (product = product_1.id)"
" -> Index Only Scan using receipt_pkey on receipt (cost=0.29..0.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=913)"
" Index Cond: (id = receiptdetail.receipt)"
" Heap Fetches: 913"
" -> Nested Loop (cost=10.93..38794.98 rows=9800 width=36) (actual time=0.170..80.715 rows=9759 loops=1)"
" -> Nested Loop (cost=10.64..35728.52 rows=9800 width=13) (actual time=0.151..35.242 rows=9759 loops=1)"
" -> Bitmap Heap Scan on product product_2 (cost=4.67..136.88 rows=49 width=4) (actual time=0.030..0.170 rows=48 loops=1)"
" Recheck Cond: ("group" = 54)"
" Heap Blocks: exact=46"
" -> Bitmap Index Scan on product_group (cost=0.00..4.66 rows=49 width=0) (actual time=0.020..0.020 rows=48 loops=1)"
" Index Cond: ("group" = 54)"
" -> Bitmap Heap Scan on shipmentdetail (cost=5.97..724.38 rows=198 width=13) (actual time=0.071..0.678 rows=203 loops=48)"
" Recheck Cond: (product = product_2.id)"
" Heap Blocks: exact=9745"
" -> Bitmap Index Scan on shipmentdetail_product_fk (cost=0.00..5.92 rows=198 width=0) (actual time=0.037..0.037 rows=203 loops=48)"
" Index Cond: (product = product_2.id)"
" -> Index Only Scan using shipment_pkey on shipment (cost=0.29..0.31 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=9759)"
" Index Cond: (id = shipmentdetail.shipment)"
" Heap Fetches: 9759"
" -> Hash (cost=136.88..136.88 rows=49 width=4) (actual time=0.166..0.166 rows=48 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 10kB"
" -> Bitmap Heap Scan on product (cost=4.67..136.88 rows=49 width=4) (actual time=0.041..0.151 rows=48 loops=1)"
" Recheck Cond: ("group" = 54)"
" Heap Blocks: exact=46"
" -> Bitmap Index Scan on product_group (cost=0.00..4.66 rows=49 width=0) (actual time=0.028..0.028 rows=48 loops=1)"
" Index Cond: ("group" = 54)"
"Planning Time: 1.553 ms"
"Execution Time: 94.071 ms"
Конечно, в таком случае JOIN с product будет выполнен три раза, но это куда меньшее из зол, чем расчет подзапроса для всей базы.
Разделение логики условий на типы JOIN и WHERE
Немногие это замечают, но логика, влияющая на то, какие записи окажутся в результирующей таблице в SQL, разделена на 2 части:
- Тип JOIN, который определяет будут ли выбираться записи из левой таблицы, из правой таблицы, из обеих таблиц или из одной из таблиц.
- WHERE — логическое выражение с AND, OR и NOT, которое также определяет, какие записи нужно выбрать.
Что реально непонятно в этом разделении, так это то, почему логику JOIN не включили в WHERE, ведь типы JOIN отлично трансформируются в обычную булеву логику:
- A INNER JOIN B соответствует IN JOIN A AND IN JOIN B
- A LEFT JOIN B — IN JOIN A
- A RIGHT JOIN B — IN JOIN B
- A FULL JOIN B — IN JOIN A OR IN JOIN B
Таким образом, запрос:
SELECT COALESCE(X.id, D.id)
FROM
(SELECT A.id
FROM A
INNER JOIN B ON A.id = B.id
LEFT JOIN C ON C.id = A.id
) X
FULL JOIN D ON X.id = D.id
Выглядел бы как:
SELECT A.id
FROM A
AUTO JOIN B ON A.id = B.id
AUTO JOIN C ON C.id=A.id
AUTO JOIN D ON D.id=A.id
WHERE (IN JOIN A AND IN JOIN B) OR IN JOIN D
Хуже всего в этом разделении на самом деле дела обстоят именно с FULL JOIN, так как для результирующих колонок приходится использовать COALESCE, что сразу убивает возможность PPD оптимизаций. Поэтому на практике вместо FULL JOIN чаще используют UNION, то есть вместо:
SELECT COALESCE(A.id, B.id)
FROM A
FULL JOIN B ON A.id=B.id
Пишут:
SELECT id FROM A
UNION
SELECT id FROM B
Правда, если нам необходимо рассчитать сумму двух колонок из A и B, все становится заметно хуже (приходится делать несколько LEFT JOIN). Так, запрос:
SELECT COALESCE(A.id, B.id), COALESCE(A.f,0)+COALESCE(B.f,0)
FROM A
FULL JOIN B ON A.id=B.id
С UNION выглядит как-то так:
SELECT A.id, A.f + COALESCE(B.f,0)
FROM A
LEFT JOIN B ON A.id = B.id
UNION
SELECT B.id, B.f + COALESCE(A.f,0)
FROM B
LEFT JOIN A ON A.id = B.id
А если нам надо из таблиц A, B, C, D выбрать записи по условию (A.f=1 OR B.f=2) AND (C.f=3 OR D.f=4), то все становится совсем печально. Так, с FULL JOIN мы опять-таки несколькими COALESCE убьем все возможные оптимизации, а с UNION разработчику придется самому приводить условие к ДНФ и писать что-то вроде такого:
SELECT A.id
FROM A
JOIN C ON A.id = C.id
WHERE A.f=1 AND C.f=3
UNION
SELECT A.id
FROM A
JOIN D ON A.id = D.id
WHERE A.f=1 AND D.f=4
UNION
SELECT B.id
FROM B
JOIN C ON B.id = C.id
WHERE B.f=2 AND C.f=3
UNION
SELECT B.id
FROM B
JOIN D ON B.id = D.id
WHERE B.f=2 AND D.f=4
При этом, если нам надо будет еще вычислить какую то формулу от полей из A, B, C и D, то из-за LEFT JOIN'ов запрос вырастет еще в два раза.
Тут, конечно, многие скажут: «что это у вас за такая странная физическая модель», но, во-первых, очень часто физическую модель не выбирают (то есть она достается как есть), а во-вторых, как мы увидим в следующем разделе, даже если все эти поля будут в одной таблице, проблемы все равно будут (правда, немного по другой причине).
В любом случае, если бы тип JOIN задавался логическим предикатом IN JOIN, а особенно, если бы он мог выводится из условия (скажем, из A.f = 1 следует IN JOIN A), то запрос можно было писать как:
SELECT A.id
FROM A
AUTO JOIN B ON A.id = B.id
AUTO JOIN C ON A.id = C.id
AUTO JOIN D ON A.id = D.id
WHERE (A.f=1 OR B.f=2) AND (C.f=3 OR D.f=4)
И дальше SQL сервер уже сам мог бы решить, как именно разбить этот запрос на UNION / FULL JOIN и какие типы JOIN подставить в полученных подзапросах. Сейчас же эту работу приходится выполнять непосредственно разработчику.
Плохая оптимизация OR
Вернемся к примеру из предыдущего раздела. Допустим, у нас не четыре разные таблицы A, B, C, D, а одна таблица mytable и четыре поля A, B, C, D:
Скрипт по созданию и заполнению таблицы mytable MS SQL
CREATE TABLE mytable (cid INT, A INT, B INT, C INT , D INT, data VARCHAR(400));
WITH
numbers ( n ) AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM numbers
WHERE n < 10000000
)
INSERT INTO mytable (cid, A, B, C, D, data)
SELECT n, ABS(checksum(NewId())) % 100 + 1, ABS(checksum(NewId())) % 100 + 1, ABS(checksum(NewId())) % 100 + 1, ABS(checksum(NewId())) % 100 + 1, CONCAT('data',n) FROM numbers option (maxrecursion 0);
CREATE INDEX AC ON mytable(A,C);
CREATE INDEX AD ON mytable(A,D);
CREATE INDEX BC ON mytable(B,C);
CREATE INDEX BD ON mytable(B,D);
EXEC sp_updatestats;
Скрипт по созданию и заполнению таблицы mytable Oracle
CREATE TABLE mytable (cid INT, A INT, B INT, C INT , D INT, "data" VARCHAR(400));
INSERT INTO mytable (cid, A, B, C, D, "data")
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() * 100) + 1, round(dbms_random.value() * 100) + 1, round(dbms_random.value() * 100) + 1, round(dbms_random.value() * 100) + 1, CONCAT('data',n) FROM numbers;
CREATE INDEX AC ON mytable(A,C);
CREATE INDEX AD ON mytable(A,D);
CREATE INDEX BC ON mytable(B,C);
CREATE INDEX BD ON mytable(B,D);
EXEC DBMS_STATS.GATHER_DATABASE_STATS;
Скрипт по созданию и заполнению таблицы mytable PostgreSQL
CREATE TABLE mytable (cid INT, A INT, B INT, C INT , D INT, "data" VARCHAR(400));
INSERT INTO mytable (cid, A, B, C, D, "data")
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() * 100) + 1, floor(random() * 100) + 1, floor(random() * 100) + 1, floor(random() * 100) + 1, CONCAT('data',n) FROM numbers;
CREATE INDEX AC ON mytable(A,C);
CREATE INDEX AD ON mytable(A,D);
CREATE INDEX BC ON mytable(B,C);
CREATE INDEX BD ON mytable(B,D);
ANALYZE;
Соответственно запрос в этом случает будет выглядеть следующим образом:
SELECT COUNT(*) FROM mytable WHERE (A=1 OR B=2) AND (C=3 OR D=4)
Чтобы этот запрос выполнялся быстро, при создании таблиц мы добавили индексы по различным комбинациям этих колонок: AC, BC, AD, BD.
CREATE INDEX AC ON mytable(A,C);
CREATE INDEX AD ON mytable(A,D);
CREATE INDEX BC ON mytable(B,C);
CREATE INDEX BD ON mytable(B,D);
Выполним запрос:
План Oracle
Среднее время выполнения: 320мс
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 12436 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| MYTABLE | 3882 | 62112 | | 12436 (1)| 00:00:01 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 4 | BITMAP OR | | | | | | |
| 5 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
| 6 | SORT ORDER BY | | | | 1568K| | |
|* 7 | INDEX RANGE SCAN | AC | | | | 241 (1)| 00:00:01 |
| 8 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
| 9 | SORT ORDER BY | | | | 1568K| | |
|* 10 | INDEX RANGE SCAN | BC | | | | 235 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("C"=3 OR "D"=4))
7 - access("A"=1)
filter("A"=1)
10 - access("B"=2)
filter("B"=2)
План PostgreSQL
"Aggregate (cost=81577.07..81577.08 rows=1 width=8) (actual time=255.927..255.928 rows=1 loops=1)" " -> Bitmap Heap Scan on mytable (cost=3863.84..81567.12 rows=3980 width=0) (actual time=60.368..255.477 rows=3886 loops=1)" " Recheck Cond: ((a = 1) OR (b = 2))" " Filter: ((c = 3) OR (d = 4))" " Rows Removed by Filter: 195540" " Heap Blocks: exact=68751" " -> BitmapOr (cost=3863.84..3863.84 rows=208664 width=0) (actual time=43.859..43.859 rows=0 loops=1)" " -> Bitmap Index Scan on ad (cost=0.00..1924.43 rows=103999 width=0) (actual time=32.954..32.954 rows=100496 loops=1)" " Index Cond: (a = 1)" " -> Bitmap Index Scan on bd (cost=0.00..1937.42 rows=104665 width=0) (actual time=10.902..10.902 rows=100014 loops=1)" " Index Cond: (b = 2)" "Planning Time: 0.603 ms" "Execution Time: 256.078 ms"
Как видим, SQL сервер даже не попытался поработать с условием, а просто выполнил то, что увидел. То есть взял первую скобку, увидел в ней OR, разбил его на индексы, а вторую скобку просто применил сверху.
Больше всех, конечно, отличился MS SQL:
План MS SQL
Среднее время выполнения: 1.8с
Rows Executes Stmt Text 0 0 |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0))) 1 1 |--Stream Aggregate(DEFINE:([Expr1006]=Count(*))) 3918 1 |--Table Scan(OBJECT:([test2].[dbo].[mytable]), WHERE:(([test2].[dbo].[mytable].[A]=(1) OR [test2].[dbo].[mytable].[B]=(2)) AND ([test2].[dbo].[mytable].[C]=(3) OR [test2].[dbo].[mytable].[D]=(4))))
Он решил вообще не использовать индексы. Но я проверил, если разновидностей колонок больше, MS SQL умеет строить план с индексами аналогичный Oracle и PostgreSQL, так что спишем это на особенности его настройки.
При этом даже если раскрыть вторую скобку:
SELECT COUNT(*) FROM mytable WHERE ((A=1 OR B=2) AND C=3) OR ((A=1 OR B=2) AND D=4)
Это не помогает — планы остаются такими же.
Единственный вариант, когда этот запрос выполнится нормально, — это преобразовать исходное условие к ДНФ:
SELECT COUNT(*) FROM mytable WHERE (A=1 AND C=3) OR (B=2 AND C=3) OR (A=1 AND D=4) OR (B=2 AND D=4)
План Oracle
Среднее время выполнения: 30мс
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | BITMAP CONVERSION COUNT | | 3010 | 48160 | 18 (0)| 00:00:01 |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | BC | | | 5 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | BD | | | 5 (0)| 00:00:01 |
| 8 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 9 | INDEX RANGE SCAN | AC | | | 4 (0)| 00:00:01 |
| 10 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 11 | INDEX RANGE SCAN | AD | | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("B"=2 AND "C"=3)
7 - access("B"=2 AND "D"=4)
9 - access("A"=1 AND "C"=3)
11 - access("A"=1 AND "D"=4)
План MS SQL
Среднее время выполнения: 60мс
Rows Executes Stmt Text 0 0 |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0))) 1 1 |--Stream Aggregate(DEFINE:([Expr1006]=Count(*))) 3918 1 |--Stream Aggregate(GROUP BY:([Bmk1000])) 3951 1 |--Merge Join(Concatenation) 2986 1 |--Merge Join(Concatenation) 1978 1 | |--Merge Join(Concatenation) 941 1 | | |--Index Seek(OBJECT:([test2].[dbo].[mytable].[AC]), SEEK:([test2].[dbo].[mytable].[A]=(1) AND [test2].[dbo].[mytable].[C]=(3)) ORDERED FORWARD) 1037 1 | | |--Index Seek(OBJECT:([test2].[dbo].[mytable].[BC]), SEEK:([test2].[dbo].[mytable].[B]=(2) AND [test2].[dbo].[mytable].[C]=(3)) ORDERED FORWARD) 1008 1 | |--Index Seek(OBJECT:([test2].[dbo].[mytable].[AD]), SEEK:([test2].[dbo].[mytable].[A]=(1) AND [test2].[dbo].[mytable].[D]=(4)) ORDERED FORWARD) 965 1 |--Index Seek(OBJECT:([test2].[dbo].[mytable].[BD]), SEEK:([test2].[dbo].[mytable].[B]=(2) AND [test2].[dbo].[mytable].[D]=(4)) ORDERED FORWARD)
План PostgreSQL
"Aggregate (cost=13171.55..13171.56 rows=1 width=8) (actual time=18.442..18.443 rows=1 loops=1)" " -> Bitmap Heap Scan on mytable (cost=93.97..13161.50 rows=4020 width=0) (actual time=3.493..17.545 rows=3886 loops=1)" " Recheck Cond: (((a = 1) AND (c = 3)) OR ((b = 2) AND (c = 3)) OR ((a = 1) AND (d = 4)) OR ((b = 2) AND (d = 4)))" " Heap Blocks: exact=3793" " -> BitmapOr (cost=93.97..93.97 rows=4020 width=0) (actual time=1.773..1.774 rows=0 loops=1)" " -> Bitmap Index Scan on ac (cost=0.00..22.76 rows=1033 width=0) (actual time=0.516..0.516 rows=993 loops=1)" " Index Cond: ((a = 1) AND (c = 3))" " -> Bitmap Index Scan on bc (cost=0.00..22.83 rows=1040 width=0) (actual time=0.440..0.440 rows=960 loops=1)" " Index Cond: ((b = 2) AND (c = 3))" " -> Bitmap Index Scan on ad (cost=0.00..22.14 rows=971 width=0) (actual time=0.224..0.224 rows=958 loops=1)" " Index Cond: ((a = 1) AND (d = 4))" " -> Bitmap Index Scan on bd (cost=0.00..22.20 rows=977 width=0) (actual time=0.592..0.592 rows=1020 loops=1)" " Index Cond: ((b = 2) AND (d = 4))" "Planning Time: 0.296 ms" "Execution Time: 18.539 ms"
Как можно увидеть из этих примеров, SQL сервер особо даже не пытается оптимизировать логические выражения. И это, в общем-то, понятно, так как такая оптимизация — NP-полная задача и возится с ней создателям SQL серверов, видимо, не очень хотелось. Поэтому, как и с типами JOIN, они просто решили переложить эту задачу на разработчика.
Плохая оптимизация при работе с разреженными данными
Теперь представим такую ситуацию. У нас есть большая таблица (shipmentdetail) и мы решили добавить туда новую колонку (sid) и индекс по ней (shipment_sd). Эта колонка по умолчанию null и заполнена для очень незначительного процента данных. Нам необходимо найти все дубликаты sid, для этого делаем следующий запрос:
SELECT COUNT(*) FROM ShipmentDetail s1, ShipmentDetail s2 WHERE s1.sd=s2.sd AND s1.id <> s2.id
План MS SQL
Среднее время выполнения: 3.5с
Rows Executes Stmt Text 1 1 |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1008],0))) 1 1 |--Hash Match(Aggregate, HASH:() DEFINE:([Expr1008]=COUNT(*))) 0 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([s1].[id], [s1].[sd], [Expr1007]) WITH UNORDERED PREFETCH) 10000001 1 |--Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_sd] AS [s1])) 0 10000001 |--Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_sd] AS [s2]), SEEK:([s2].[sd]=[test].[dbo].[shipmentdetail].[sd] as [s1].[sd]), WHERE:([test].[dbo].[shipmentdetail].[id] as [s1].[id]<>[test].[dbo].[shipmentdetail].[id] as [s2].[id]) ORDERED FORWARD)
Как видим, MS SQL, когда пытается бежать по индексу shipmentdetail_sd, не догадывается, что нужно ставить фильтр на IS NOT NULL, как следствие, производительность этого запроса очень низкая.
Если же добавить явные условия на то, что s1.sd и s2.sd IS NOT NULL картина резко улучшается:
SELECT COUNT(*) FROM ShipmentDetail s1, ShipmentDetail s2 WHERE s1.sd=s2.sd AND s1.id <> s2.id AND s1.sd IS NOT NULL AND s2.sd IS NOT NULL
План MS SQL
Среднее время выполнения: 100мс
Rows Executes Stmt Text 0 0 |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0))) 1 1 |--Stream Aggregate(DEFINE:([Expr1005]=Count(*))) 0 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([s1].[id], [s1].[sd])) 100 1 |--Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_sd] AS [s1]), SEEK:([s1].[sd] IsNotNull) ORDERED FORWARD) 0 100 |--Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_sd] AS [s2]), SEEK:([s2].[sd]=[test].[dbo].[shipmentdetail].[sd] as [s1].[sd]), WHERE:([test].[dbo].[shipmentdetail].[id] as [s1].[id]<>[test].[dbo].[shipmentdetail].[id] as [s2].[id] AND [test].[dbo].[shipmentdetail].[sd] as [s2].[sd] IS NOT NULL) ORDERED FORWARD)
С PostgreSQL все сложнее, базовый запрос у него работает и работает хорошо, но не потому что он добавляет фильтр, а потому что использует merge join и, видимо, умеет пропускать в нем NULL значения (тут странно, что если MS SQL добавить соответствующие хинты, это не помогает):
План PostgreSQL
"Aggregate (cost=1216467.81..1216467.82 rows=1 width=8) (actual time=1.021..1.021 rows=1 loops=1)" " -> Merge Join (cost=0.87..1216467.81 rows=1 width=0) (actual time=1.017..1.017 rows=0 loops=1)" " Merge Cond: (s1.sd = s2.sd)" " Join Filter: (s1.id <> s2.id)" " Rows Removed by Join Filter: 100" " -> Index Scan using shipmentdetail_sd on shipmentdetail s1 (cost=0.43..583234.26 rows=9999855 width=8) (actual time=0.047..0.136 rows=101 loops=1)" " -> Index Scan using shipmentdetail_sd on shipmentdetail s2 (cost=0.43..583234.26 rows=9999855 width=8) (actual time=0.015..0.757 rows=101 loops=1)" "Planning Time: 0.332 ms" "Execution Time: 1.089 ms"
Хотя, если посмотреть на estimate и cost, то видно, что PostgreSQL реально планирует пробежать по всем записям таблицы. Поэтому, если чуть-чуть изменить запрос и добавить, скажем, условие — найти дубликаты с количеством > 7
SELECT COUNT(*) FROM shipmentDetail s1, shipmentDetail s2 WHERE s1.sd=s2.sd AND s1.id <> s2.id AND s1.quantity > 7 AND s2.quantity > 7;
То получим ту же проблему, что и в MS SQL:
План PostgreSQL
"Aggregate (cost=1165667.15..1165667.16 rows=1 width=8) (actual time=5203.586..5203.586 rows=1 loops=1)" " -> Merge Join (cost=1128001.01..1165667.15 rows=1 width=0) (actual time=5203.580..5203.580 rows=0 loops=1)" " Merge Cond: (s1.sd = s2.sd)" " Join Filter: (s1.id <> s2.id)" " Rows Removed by Join Filter: 29" " -> Sort (cost=564000.51..571533.73 rows=3013290 width=8) (actual time=2802.648..2802.658 rows=30 loops=1)" " Sort Key: s1.sd" " Sort Method: external merge Disk: 41120kB" " -> Seq Scan on shipmentdetail s1 (cost=0.00..198528.19 rows=3013290 width=8) (actual time=0.051..2193.193 rows=2999089 loops=1)" " Filter: (quantity > '7'::numeric)" " Rows Removed by Filter: 7000912" " -> Materialize (cost=564000.51..579066.96 rows=3013290 width=8) (actual time=2400.884..2400.894 rows=30 loops=1)" " -> Sort (cost=564000.51..571533.73 rows=3013290 width=8) (actual time=2400.876..2400.882 rows=30 loops=1)" " Sort Key: s2.sd" " Sort Method: external merge Disk: 41120kB" " -> Seq Scan on shipmentdetail s2 (cost=0.00..198528.19 rows=3013290 width=8) (actual time=0.033..1833.562 rows=2999089 loops=1)" " Filter: (quantity > '7'::numeric)" " Rows Removed by Filter: 7000912" "Planning Time: 0.398 ms" "Execution Time: 5233.873 ms"
которая лечится все тем же добавлением явных условий на то, что s1.sd и s2.sd IS NOT NULL:
SELECT COUNT(*) FROM shipmentDetail s1, shipmentDetail s2 WHERE s1.sd=s2.sd AND s1.id <> s2.id AND s1.quantity > 7 AND s2.quantity > 7 AND s1.sd IS NOT NULL AND s2.sd IS NOT NULL
План PostgreSQL
"Aggregate (cost=16.93..16.94 rows=1 width=8) (actual time=4.624..4.624 rows=1 loops=1)" " -> Nested Loop (cost=0.87..16.93 rows=1 width=0) (actual time=4.617..4.617 rows=0 loops=1)" " Join Filter: ((s1.id <> s2.id) AND (s1.sd = s2.sd))" " Rows Removed by Join Filter: 841" " -> Index Scan using shipmentdetail_sd on shipmentdetail s1 (cost=0.43..8.46 rows=1 width=8) (actual time=0.055..0.201 rows=29 loops=1)" " Index Cond: (sd IS NOT NULL)" " Filter: (quantity > '7'::numeric)" " Rows Removed by Filter: 71" " -> Index Scan using shipmentdetail_sd on shipmentdetail s2 (cost=0.43..8.46 rows=1 width=8) (actual time=0.013..0.142 rows=29 loops=29)" " Index Cond: (sd IS NOT NULL)" " Filter: (quantity > '7'::numeric)" " Rows Removed by Filter: 71" "Planning Time: 0.570 ms" "Execution Time: 4.705 ms"
Oracle — единственный, кто не сплоховал в данном случае и догадался добавить такие предикаты сам:
План Oracle
Среднее время выполнения: 30мс
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 201 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | NESTED LOOPS | | 99 | 1584 | 201 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 100 | 1584 | 201 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| SHIPMENTDETAIL | 100 | 800 | 101 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | SHIPMENTDETAIL_SD | 100 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | SHIPMENTDETAIL_SD | 1 | | 0 (0)| |
|* 7 | TABLE ACCESS BY INDEX ROWID | SHIPMENTDETAIL | 1 | 8 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("S1"."SD" IS NOT NULL)
6 - access("S1"."SD"="S2"."SD")
filter("S2"."SD" IS NOT NULL)
7 - filter("S1"."ID"<>"S2"."ID")
Плохая оптимизация при работе с последними значениями
Самые часто используемые агрегирующие функции в группирующих запросах в OLTP бизнес-приложениях — это сумма и последнее значение (например, в 1С это регистры накопления и сведений соответственно). С суммой все более-менее понятно, а вот с последним значением есть много вопросов, как в плане поддержки в SQL серверах такого функционала вообще, так и его дальнейшей оптимизации.
Итак, допустим, мы хотим получить для товара последнюю его отгрузку. Для начала будем считать, что последнюю отгрузку надо искать по внутреннему номеру этой отгрузки (а не, скажем, по дате). Первое, что нам предложит поиск по stackoverflow — это запрос:
SELECT MAX(shipment) AS ls FROM ShipmentDetail s WHERE s.product = 123
Чтобы быстро выполнить верхний запрос, очевидно напрашивается индекс по product, shipment, и действительно, если мы его построим и выполним наш запрос, получим достаточно красивый и эффективный план выполнения (что удивительно, даже в PostgreSQL):
План в MSSQL
Среднее время выполнения: 70мс
Rows Executes Stmt Text 1 1 |--Stream Aggregate(DEFINE:([Expr1001]=MAX([test].[dbo].[shipmentdetail].[shipment] as [s].[shipment]))) 1 1 |--Top(TOP EXPRESSION:((1))) 1 1 |--Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_p_s] AS [s]), SEEK:([s].[product]=(123)) ORDERED BACKWARD)
План в Oracle
Среднее время выполнения: 30мс
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | FIRST ROW | | 1 | 10 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| SHIPMENTDETAIL_P_S | 1 | 10 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S"."PRODUCT"=123)
План в PostgreSQL
"Result (cost=4.47..4.48 rows=1 width=4) (actual time=0.173..0.174 rows=1 loops=1)" " InitPlan 1 (returns $0)" " -> Limit (cost=0.43..4.47 rows=1 width=4) (actual time=0.165..0.166 rows=1 loops=1)" " -> Index Only Scan Backward using shipmentdetail_p_s on shipmentdetail s (cost=0.43..808.43 rows=200 width=4) (actual time=0.163..0.163 rows=1 loops=1)" " Index Cond: ((product = 123) AND (shipment IS NOT NULL))" " Heap Fetches: 1" "Planning Time: 0.949 ms" "Execution Time: 0.241 ms"
В частности, в этом плане все SQL сервера догадываются вставить проверку, что достаточно считать ровно один ряд (FIRST ROW — Oracle, LIMIT — MS SQL и PostgreSQL).
А теперь попробуем чуть усложнить запрос и выполнить его не для одного товара, а для множества товаров, например, всех товаров, начинающихся на Product 86 (PostgreSQL сразу выбывает из гонки, так как JPPD не поддерживает в принципе).
Начнем с Oracle.
SELECT SUM(cc.ls)
FROM Product pr
LEFT JOIN (SELECT MAX(shipment) AS ls, s.product
FROM shipmentDetail s
GROUP BY s.product) cc ON cc.product=pr.id
WHERE pr.name LIKE 'Product 86%';
План в Oracle
Среднее время выполнения: 60мс
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 120 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | VM_NWVW_1 | 4898 | 63674 | 120 (2)| 00:00:01 |
| 3 | HASH GROUP BY | | 4898 | 157K| 120 (2)| 00:00:01 |
| 4 | NESTED LOOPS | | 4898 | 157K| 118 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| PRODUCT | 25 | 575 | 68 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | SHIPMENTDETAIL_P_S | 198 | 1980 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("PR"."NAME" LIKE 'Product 86%')
6 - access("S"."PRODUCT"="PR"."ID")
Запрос по прежнему выполняется быстро, но настораживает тот факт, что проверка на один ряд исчезла, а это очень важно, когда записей станет много.
Ну а теперь выполним запрос для всех товаров.
SELECT MAX(cc.ls)
FROM Product pr
LEFT JOIN (SELECT MAX(shipment) AS ls, s.product
FROM shipmentDetail s
GROUP BY s.product) cc ON cc.product=pr.id
План в Oracle
Среднее время выполнения: 2.6с
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 22222 (100)| |
| 1 | SORT AGGREGATE | | 1 | 31 | | | |
| 2 | NESTED LOOPS SEMI | | 50001 | 1513K| | 22222 (2)| 00:00:01 |
| 3 | VIEW | | 50536 | 1283K| | 22219 (2)| 00:00:01 |
| 4 | HASH GROUP BY | | 50536 | 493K| 191M| 22219 (2)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| SHIPMENTDETAIL_P_S | 10M| 95M| | 7871 (1)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C007890 | 49472 | 241K| | 0 (0)| |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("CC"."PRODUCT"="PR"."ID")
Как видим, Oracle, не догадавшись вставить проверку на один ряд, вообще переходит на Hash Group By без JPPD (что, впрочем, логично без проверки на один ряд) и выполняет этот запрос уже несколько секунд. В то же время, если переписать этот запрос на subquery expressions с ORDER BY и rownum=1 (то есть по сути явно указав Oracle, что нужно выбирать одну запись), план становится значительно лучше:
SELECT MAX(l) FROM (SELECT (SELECT shipment FROM (SELECT shipment FROM ShipmentDetail s WHERE s.product = pr.id ORDER BY s.shipment DESC) s WHERE rownum = 1) AS l FROM product pr)
План в Oracle
Среднее время выполнения: 300мс
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 139K(100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 2 | 26 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN DESCENDING| SHIPMENTDETAIL_P_S | 198 | 1980 | 3 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 5 | | |
| 5 | INDEX FAST FULL SCAN | SYS_C007890 | 50001 | 244K| 27 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - access("S"."PRODUCT"=:B1)
И запрос выполняется за несколько сотен миллисекунд, то есть в 10 раз быстрее. Почему Oracle по разному оптимизируют запросы, когда у него в предикате равенства константа задана явно и когда она приходит из JPPD — загадка. Но на практике разработчику для нормальной производительности придется вставлять вот такие вот костыли как в запросе выше. Причем, если в базе нет нужного индекса или база «пустая» (то есть когда отгрузок еще нет, а товары есть и их много), запрос с такими костылями будет выполняться гораздо хуже, по сравнению с базовым запросом и тем, как его выполняет Oracle. То есть по хорошему такая оптимизация должна быть решением самой СУБД, а не разработчика, который может не знать ни статистики, ни того, какие индексы есть в системе.
Что касается MS SQL, то если в прошлом разделе у MS SQL была проблема, а у Oracle нет, то здесь все наоборот. MS SQL догадывается вставлять Top 1 в план:
План MS SQL
Среднее время выполнения: 300мс
Rows Executes Stmt Text 1 1 |--Hash Match(Aggregate, HASH:() DEFINE:([Expr1005]=MAX([Expr1003]))) 50001 1 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([pr].[id])) 50001 1 |--Index Scan(OBJECT:([test].[dbo].[product].[product_group] AS [pr])) 0 0 |--Compute Scalar(DEFINE:([Expr1003]=[test].[dbo].[shipmentdetail].[shipment] as [s].[shipment])) 50000 50001 |--Top(TOP EXPRESSION:((1))) 50000 50001 |--Index Seek(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_p_s] AS [s]), SEEK:([s].[product]=[test].[dbo].[product].[id] as [pr].[id]) ORDERED BACKWARD)
И выполняет этот запрос так как надо. Правда, если заменить JOIN с LEFT на INNER, то Top 1 магическим образом пропадает (хотя непонятно в чем разница, и зачем SQL серверу может понадобится больше чем одна запись), и мы имеем ту же проблему, что и в Oracle:
План MS SQL
Среднее время выполнения: 2.8с
Rows Executes Stmt Text 1 1 |--Hash Match(Aggregate, HASH:() DEFINE:([Expr1003]=MAX([Expr1002]))) 50000 1 |--Stream Aggregate(GROUP BY:([s].[product]) DEFINE:([Expr1002]=MAX([test].[dbo].[shipmentdetail].[shipment] as [s].[shipment]))) 10000001 1 |--Index Scan(OBJECT:([test].[dbo].[shipmentdetail].[shipmentdetail_p_s] AS [s]), ORDERED FORWARD)
То есть фактически в MS SQL все INNER JOIN подзапросов с MAX необходимо преобразовывать в LEFT JOIN. Но это все же меньшее из зол по сравнению с Oracle (где, как мы видели, все JOIN подзапросов с MAX надо преобразовывать в subquery expression с rownum=1).
В любом случае, несмотря на описанные выше оптимизации, у подхода с MAX / MIN есть два очень существенных недостатка. А именно, непонятно что делать:
- когда нужно вернуть не последнее значение, а другое поле записи с последним значением (скажем поставку последнюю по дате)
- когда нужно найти последнюю запись сразу по нескольким полям: то есть сначала по одному полю, а если это поле совпадает, то по второму (например по дате, а уже потом по внутреннему идентификатору).
В этом случае есть два варианта:
- использовать оконные функции, как предлагает нам тот же stackoverflow, что уже не очень удобно, когда надо получить значения именно для группы
- создать custom aggregate функцию LAST, что тоже не очень удобно, особенно в том же MS SQL, где это надо делать на C#, с необходимостью последующей компиляции созданного файла перед его загрузкой на сервер (но это было давно, возможно, в последних версиях в этом плане что-то изменилось)
Но самое главное, что у обоих подходов будут большие проблемы с производительностью. Так, для оконных функций JPPD не поддерживается в принципе. А с custom aggregate функциями не будет использоваться описанная выше оптимизация FIRST ROW / LIMIT 1. Конечно, проблема ее отсутствия может показаться несколько преувеличенной, но на самом деле ее нельзя недооценивать. Эта проблема становится особенно важной по мере роста количества данных. То есть после месяца работы системы все может работать хорошо, но когда пройдет пять-десять лет, при выполнении запроса вычисления последнего значения каких-нибудь часто изменяющихся данных (например приходов какой-нибудь позиции популярного молока, которая приходит каждый день) СУБД придется обращаться к очень старым данным, что, во-первых, создаст дополнительную нагрузку не только на процессор, но и на СХД, а во-вторых, может приводить к ротации кэшей данных на SQL сервере, а это, в свою очередь, может значительно повлиять на общую производительность системы.
Проблема N+1
Есть распространенное мнение, что если система написана на SQL (а точнее его расширениях PL/SQL, T-SQL и т.п.), то она автоматически не имеет проблемы с многократным выполнением одних и тех же запросов, но с разными параметрами. А это, скажем так, не совсем верно. По большому счету в расширениях SQL проблема N+1 не сильно отличается от аналогичной проблемы в тех же ORM-фреймворках.
Например, у нас есть хранимая процедура:
--Создаем процедуру
CREATE PROCEDURE TestProcedure
(
--Входящие параметры
@CategoryId INT,
@ProductName VARCHAR(100),
@Price MONEY = 0
)
AS
BEGIN
--Инструкции, реализующие Ваш алгоритм
--Обработка входящих параметров
--Удаление лишних пробелов в начале и в конце текстовой строки
SET @ProductName = LTRIM(RTRIM(@ProductName));
--Добавляем новую запись
INSERT INTO TestTable(CategoryId, ProductName, Price)
VALUES (@CategoryId, @ProductName, @Price)
--Возвращаем данные
SELECT * FROM TestTable
WHERE CategoryId = @CategoryId
END
GO
А теперь нам нужно выполнить эту процедуру для 1000 записей. И тут у нас два варианта:
- запустить хранимую процедуру в цикле и получить 1000 INSERT и 1000 SELECT.
- сделать еще одну процедуру, но с использованием временных таблиц / табличных переменных и тем самым потратить на это драгоценное время разработчика, а заодно нарастить технический долг, нарушив DRY принцип.
Понятно, что можно все хранимые процедуры проектировать таким образом, что все параметры будут передаваться во временных таблицах / табличных переменных, но это, мягко говоря, не сильно удобно.
Однако если с хранимыми процедурами еще есть какой-то workaround, то с триггерами во многих SQL серверах все еще хуже. Здесь мы не знаем, когда триггер будет вызван и, соответственно, переписывать нечего. Теоретически для решения проблемы N+1 в SQL серверах есть триггеры per statement (в противовес per row), но:
- В Oracle и PostgreSQL в них нельзя обращаться к новым и предыдущим значениям, то есть такие триггеры бесполезны чуть меньше чем полностью.
- В MS SQL в per statement триггерах есть виртуальные таблицы inserted и deleted, где можно узнать, что именно изменилось. Но с точки зрения проблемы N+1, это поможет, только если изменения первоначально будут выполняться одной командой.
Хотя, справедливости ради, если в MS SQL все триггеры делать с использованием inserted и deleted, и везде следовать правилу, что все изменения одной таблицы должны выполняться ровно одной командой (правда, непонятно, как это можно обеспечить в контексте описанной выше проблемы N+1 с хранимыми процедурами), то можно практически полностью избавиться от проблемы N+1 и обеспечить поддержку всех существующих в системе бизнес-правил минимальным количеством SQL-запросов. И в этом смысле MS SQL дает фору Oracle (честно говоря, я так и не понял, в чем проблема у Oracle была поддержать аналогичные виртуальные таблицы).
UPD: Еще одна подсказка из зала, в PostgreSQL начиная с 10 версии есть так называемые transition таблицы (new_table и old_table), функционал которых аналогичен inserted и deleted в MS SQL.
Высокая цена ошибки при проектировании БД
Если показать большинство описанных выше проблем разработчику БД, первое, что вы, скорее всего, услышите в ответ будет: «да у вас неправильная модель БД». Причем будет предполагаться, что эта «неправильная модель» одновременно и причина, и средство решения всех ваших проблем.
Если посмотреть на «неправильную модель» как на причину всех проблем, то тут вообще непонятно использование термина «неправильная». Обычно при создании системы невозможно предугадать не то что, какая там будет статистика, а какой в принципе будет функционал этой системы через пять-десять лет. Постоянно меняться — одна из ключевых особенностей любого бизнеса (особенно на конкурентных рынках), а вместе с самим бизнесом необходимо изменять и его ИТ-системы (как зеркало этого бизнеса). И то, что было правильно сначала, может быстро стать неправильным потом. Так что, если кто-то вас упрекнет в том, что вы неправильно спроектировали БД, можете смело кидать в этого человека камень. Уверен, что при изменении требований к его БД нужным образом (а это неизбежно) его модель тоже будет неправильной.
С «неправильной моделью» как средством решения проблем все еще сложнее. Как мы увидели в первом разделе, нормализация / денормализация БД в современных SQL-серверах — не такой уж простой и прозрачный процесс. От слова совсем. Но даже если вам надо просто переместить несколько полей из разных таблиц в одну таблицу или наоборот разложить некоторые поля одной таблицы по разным таблицам, вы, скорее всего, столкнетесь с не меньшими трудностями и вам, возможно, придется переписать довольно значительное число запросов, как записи, так и чтения. Тут, конечно, вам на помощь, скорее всего, придут все те же представления, но, как мы видели в остальных разделах, представления поддерживают далеко не все то, что поддерживают таблицы (в частности, ограничения, триггеры и индексы), плюс имеют проблемы с производительностью при использовании FULL JOIN и т.п. Поэтому так просто взять и заменить таблицу на представление, скорее всего, не получится.
Так что, как говорится, работа разработчика БД и опасна и трудна, и требует не только хорошее аналитическое мышление, но и развитые экстрасенсорные способности, так как любая ошибка в проектировании БД может впоследствии очень дорого стоить.
Непредсказуемая оптимизация при работе с большим количеством JOIN
В современных SQL серверах для построения планов запросов (в частности, определения порядков JOIN) используется так называемый Cost-Based Optimizer (CBO). Часто его преподносят как очень сложный и умный механизм, и он действительно внутри учитывает огромное количество информации, от индексов по функциям до партиционирования таблиц и индексов, но именно это количество информации и играет с ним злую шутку — алгоритмически это не более чем обычный перебор. А значит, как и в любом переборе, сложность работы CBO растет экспоненциально от количества join'ов. Более того, так как это перебор перестановок, а не подмножеств, сложность у этого перебора вообще космическая — O(n!). То есть даже для двенадцати join'ов вариантов их перестановок будет около 48 миллионов. Понятно, что у алгоритма поиска планов есть отсечения, но с таким количеством вариантов даже они не помогут. Поэтому почти все SQL сервера при большом количестве join'ов переходят на различные эвристики. И вот тут у некоторых SQL серверов начинаются проблемы. Так, в PostgreSQL GEQO алгоритм умудряется пропускать чересчур очевидные варианты. К примеру, в запросе может быть одна единственная маленькая таблица, которая находится в запросе на расстоянии больше 8 join'ов от таблицы, с которой у нее общее условие, и при этом в результирующем плане она будет соединяться в самом конце.
Вообще, опыт показал, что при работе с большим количеством join эффективнее всего следующий подход: жадняком с минимальным lookahead'ом определить самый очевидный порядок join, после чего перебор в cost-based оптимизации начинать именно с этого порядка join (а не порядка join, заданного в запросе). Возможно, MS SQL и Oracle так и делают, но найти какую-то конкретную информацию по используемым ими эвристикам очень тяжело (в отличии от PostgreSQL). Везде написано что-то в стиле:
This heuristic uses sophisticated methods for instantaneously finding particularВживую, к сожалению, протестировать эти сценарии в MS SQL и Oracle пока не удалось (так как эти сценарии требуют одновременно и большое количество данных, и сложную логику), но в будущем, я надеюсь, это все-таки удастся сделать и тогда я дополню статью этой информацией.
plans in the search space which are likely to be nearly optimal or, at least, very
good execution plans.
Отсутствие наследования и полиморфизма
Наследование и полиморфизм (здесь и далее речь пойдет о subtype полиморфизме) появились задолго до появления SQL и, за счет возможности эффективно декомпозировать задачи, а также не наращивать технический долг по мере роста сложности системы, совершили небольшую революцию в программировании, позволив создавать системы значительно более сложные, чем существовавшие ранее (такую же революцию в программировании, кстати, в свое время совершило абстрагирование, дав миру, в частности, структурное программирование).
И если в простом проекте польза этих двух механизмов не настолько очевидна, то по мере роста этого проекта, именно наследование и полиморфизм являются одними из основных барьеров от превращения его кода в один большой «спагетти-код».
Посмотрим, что в этом плане умеют современные SQL сервера.
У MS SQL все просто — они даже не пытались поддержать ни наследование, ни тем более полиморфизм.
В PostgreSQL формально наследование таблиц есть, но не более того. А учитывая, что смысла в наследовании без полиморфизма нет практически никакого, зачем в PostgreSQL наследование вообще добавляли — неясно.
Вообще, если провести аналогию со структурным программированием, полиморфизм в SQL, по идее, должен был выглядеть как возможность создания абстрактного представления, в который можно добавлять различные UNION'ы в качестве реализации, то есть что-то вроде:
CREATE ABSTRACT VIEW detail (document LONG, quantity NUMBER);
EXTEND VIEW Detail
SELECT receipt AS document, quantity FROM receiptDetail;
…
EXTEND VIEW X
SELECT shipment AS document, quantity FROM shipmentDetail;
Но такой возможности ни в PostgreSQL, ни в других SQL серверах нет.
В Oracle решили скрестить «ежа с ужом»:
- добавить в язык классическое ООП с классами, объектами и инкапсуляцией
- сделать возможность создавать таблицы как списки объектов заданного класса.
Получилось, на мой взгляд, очень неоднозначно. Во-первых, в методах (MEMBER FUNCTION / PROCEDURE) мы имеем проблему N+1. Во-вторых, непонятно, что делать с таблицами с несколькими ключами (например, Товар и Склад). Можно, конечно, создавать суррогатные классы вроде ТоварСклад, но тогда непонятно, что делать с их наследованием. Создавать классы ТоварМагазин и ТоварРЦ? Тогда с классами в отсутствии generics вообще неразбериха получится, так как при связывании Магазин и ТоварСклад, должен получиться ТоварМагазин, а как об этом должен узнать SQL сервер — неясно. В общем, в получившемся механизме вопросов больше, чем ответов. Как следствие, с типами и их наследованием в Oracle ситуация примерно такая же, как и с материализованными представлениями — много кто слышал, но использовали на практике очень немногие и то в частных случаях.
Избыточно низкий уровень абстрагирования
Как известно, SQL — язык реляционной алгебры, то есть язык работы с таблицами. При этом абсолютное большинство таблиц в БД, как правило, находятся во второй нормальной форме (имеют ключи), а значит, любую таблицу можно рассматривать как множество функций, то есть отображений ключей (параметров функции) на колонки (значения функции). При этом все операции над таблицами все равно выполняются в реляционной алгебре, то есть при помощи операций соединения и объединения, создавая тем самым дополнительную ненужную сложность. К примеру, операция соединения — это декартово произведение двух таблиц, что, скажем так, не слишком очевидно для обычного человека (во всяком случае, в жизни люди с такой операцией практически не сталкиваются). И я много раз пытался объяснять людям, не имевшим опыта работы с SQL, эту операцию формально, но безуспешно. В итоге все сводилось к «обезьянному методу» обучения: надо получить такую информацию — используй вот такой запрос, другую информацию — другой запрос и так далее. Впрочем, справедливости ради, даже если меня спросить что будет, если сделать LEFT JOIN таблицы и указать в условии соединения не все ключи, мне придется серьезно поломать мозг. То есть построить таблицу я смогу, но логически для меня это будут бессмысленные данные. Как говорилось в одном культовом сериале: «She understands, she doesn't comprehend». А у меня опыт работы с SQL, скажем так, не самый маленький, мне приходилось работать с настолько сложными запросами, что они приводили к багам в PostgreSQL, причем таким, что меня даже лично упоминали в Release Notes (потому как запросы были просто из join'ов и =, то есть баг центральнее некуда)
Альтернативой, позволяющей решить проблему избыточной сложности, было бы использование в SQL не реляционной алгебры — с таблицами, соединениями, а функциональной — с функциями и композициями. Впрочем, эта тема уже подробно разбиралась в отдельной статье, поэтому здесь я приведу лишь пример задачи с 2* (для наглядной демонстрации различия этих двух подходов)
Функциональная алгебра
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
(friends(a, b) OR friends(b, a)) AND
(friends(b, c) OR friends(c, b));
Реляционная алгебра
WITH PersonRelationShipCollapsed AS (
SELECT pl.PersonAID
,pl.PersonBID
,pl.Relation
FROM #PersonRelationShip AS pl
UNION
SELECT pl.PersonBID AS PersonAID
,pl.PersonAID AS PersonBID
,pl.Relation
FROM #PersonRelationShip AS pl
)
SELECT
pl.PersonAID
,pf.PersonBID
,pff.PersonBID
FROM #Persons AS p
--Лайки
JOIN PersonRelationShipCollapsed AS pl ON pl.PersonAID = p.PersonID
AND pl.Relation = 'Like'
--Друзья
JOIN PersonRelationShipCollapsed AS pf ON pf.PersonAID = p.PersonID
AND pf.Relation = 'Friend'
--Друзья Друзей
JOIN PersonRelationShipCollapsed AS pff ON pff.PersonAID = pf.PersonBID
AND pff.PersonBID = pl.PersonBID
AND pff.Relation = 'Friend'
--Ещё не дружат
LEFT JOIN PersonRelationShipCollapsed AS pnf ON pnf.PersonAID = p.PersonID
AND pnf.PersonBID = pff.PersonBID
AND pnf.Relation = 'Friend'
WHERE pnf.[PersonAID] IS NULL
А уже читатель сам может решить, какой из этих подходов проще.
Адаптивная оптимизация(AO)
Одним из ключевых условий построения эффективных планов выполнения является правильная оценка статистики результатов соединения таблиц / подзапросов. Так, если в обеих соединяемых таблицах мало записей, или их мало в одной из таблиц, а во второй есть индекс по условию соединения, для соединения этих таблиц можно (и нужно) использовать цикл (nested loop join), в остальных случаях, как правило, эффективнее использовать hash или merge join. Но что будет, если планировщик думал, что записей мало, а их на самом деле оказалось много? В общем-то, ничего хорошего. Причем самые разрушительные последствия будут в случае, если планировщик думал что в обеих таблицах мало записей, а оказалось что в каждой из них, к примеру по 10к записей. В этом случае в процессе выполнения запроса SQL серверу придется пробежаться по 100млн записей (или даже построить такую таблицу), что создаст большую нагрузку как на процессор (особенно если включен параллелизм), так и на память (в том числе постоянную, то есть СХД). А ошибаться со статистикой SQL сервера могут во многих случаях, самыми распространенными из которых являются неравномерная статистика и корреляция данных. Конечно с этими явлениями SQL сервера борются как могут (так, к примеру, они хранят наиболее часто встречающиеся значения, cross-column статистику, всяческие гистограммы и т.п.), но даже со всеми этими техниками при наличии в запросе большого количества таблиц вероятность ошибки в статистике все равно достаточно высока.
Чтобы сделать сервер более устойчивым к таким ошибкам, SQL сервера используют следующую технику: если прогнозируемое количество записей меньше некоторого порога, и, по идее, должен был бы использоваться nested loop join, они вставляют в план альтернативную ветку (так называемый adaptive join), которая активируется, если реальное количество записей значительно превысит прогнозируемое. В частности, такая техника позволяет исключить упомянутый выше самый «разрушительный» сценарий — соединения двух огромных таблиц при помощи nested loop join. Но, как и у JPPD, у AO есть ряд недостатков.
AO: Поддерживаются только в коммерческих СУБД
Этот механизм не поддерживается в PostgreSQL. Собственно, даже в MSSQL его поддержка появилась всего 2 года назад, поэтому ожидать его появление в PostgreSQL в ближайшее время не приходится. Усугубляет ситуацию еще то, что PostgreSQL редкостный оптимист. Так, если PostgreSQL не знает selectivity, то считает ее равной 0.3 (там реально в коде такая константа захардкожена), плюс, если он ничего не знает про два условия соединения, то считает их некоррелированными и просто перемножает selectivity. Как следствие, он очень часто думает, что в промежуточной таблице будет одна запись, и если, например, запрос содержит несколько подзапросов (информация о которых как раз часто попадает в класс «неизвестной»), стреляет себе в ногу с завидной регулярностью.
С другой стороны, с учетом отсутствия JPPD и непредсказуемостью GEQO, возможно изначально и не планировалось, что PostgreSQL должен эффективно работать со сложными запросами. Так что отсутствие AO, как и излишний оптимизм отлично вписываются в эту парадигму.
AO: Вероятность ошибки все равно достаточно высока
Описанная техника адаптивных join'ов устраняет крайние случаи, но все равно обладает определенными уязвимостями:
This means that a threshold depends on the estimates, so accurate estimates are still important.То есть если оптимизатор ошибется не в нижнюю, а в верхнюю сторону, то адаптивный join не создастся, а значит SQL сервер, к примеру, может не использовать индекс, когда это было нужно.
Также при такой схеме возможна ситуация, когда join с ошибочно маленькой статистикой будет неправильно поставлен в начало, и тем самым воспрепятствует попаданию в начало «правильного» join. При этом, когда SQL сервер обнаружит ошибку, будет уже поздно:
Adaptive plans do not allow the join order to be altered, so even if a better join method or parallel distribution method is used, the execution plan may still be sub-optimal.Для хотя бы частичного решения этих проблем в Oracle помимо adaptive join также поддерживается так называемая адаптивная статистика (adaptive statistics). Она позволяет собирать для выполняемого запроса реальную статистику каждого join, а затем использовать ее при следующем выполнении этого же запроса, при необходимости перепланировав его. Впрочем, у этого механизма тоже есть ряд проблем:
- Он включается только при втором выполнении запроса (при этом, если в запросе будет, к примеру, использована другая временная таблица, то он будет считаться другим запросом, а значит, весь этот механизм не сработает)
- Так как нужно собирать дополнительную статистику для каждого выполняющегося в системе запроса (а с 99% из них обычно проблем нет), у него достаточно большой оверхед. Причем настолько большой, что сам Oracle по умолчанию его выключает и вообще не рекомендует использовать в OLTP системах.
The reasoning for this is many of these optimizations are more appropriate for data warehousing, where there optimization time is is a small proportion of the query runtime. In OLTP environments, where SQL runtime is typically smaller, the additional optimization time may become a significant part of the elapsed time, for little extra benefit.
В то же время есть более простая и во многом даже более эффективная техника адаптивной оптимизации. Ее смысл заключается в том, чтобы определить общее предполагаемое время выполнения запроса (с определенным запасом), и если оно будет превышено, отменить запрос, материализовать в нем часть подзапросов (скорингом определив наиболее подходящие), а затем попытаться выполнить этот запрос еще раз. Так как подзапросы будут сохранены во временные таблицы и по ним будет достаточно подробная статистика, оптимизатору будет гораздо проще построить эффективный план по сравнению с базовым запросом. Естественно, всю информацию о новом времени выполнения, материализация каких подзапросов помогла быстро выполнить запрос и т.п. необходимо кэшировать и использовать при последующих выполнениях.
Тут, правда, надо сказать, что такой подход требует выполнения нескольких дополнительных условий:
- Перед материализацией подзапроса для него нужно выполнить «JPPD в общем случае» (то есть со сбором проталкиваемых предикатов верхнего запроса, их группировкой и так далее), иначе подзапрос материализуется для всей базы, а не только для тех данных, которые нужны верхнему запросу. Соответственно, необходимо как минимум поддерживать возможность такого JPPD.
- Архитектура системы должна поддерживать отмену запроса, а значит и, возможно, всей транзакции, с последующим ее перезапуском. Впрочем, поддержка такой операции нужна, в том числе, для работы с update conflict и dead lock, так что реализовывать ее все равно придется (если вам, конечно, не хочется мучаться с ручными блокировками, но об этом в следующей статье).
Отметим, что описанная выше техника позволяет уточнять статистику именно подзапросов. Ее, конечно, можно пытаться использовать и для уточнения статистики промежуточных результатов соединения join, но это будет существенно сложнее и менее надежно. Тем более, что чаще всего SQL сервера, ошибаются именно в статистике подзапросов, и, как показала практика, материализация подзапросов, как механизм адаптивной оптимизации, работает и работает очень даже хорошо. С другой стороны, в связке с адаптивными join'ами этот механизм работает еще лучше, а лучшее, как известно, враг хорошего.
Заключение
Подводя итог, коммерческие SQL сервера поддерживают достаточно много сложного функционала и оптимизаций, но практически всегда это делают в таком виде, что использовать эти возможности на практике — себе дороже. Выстрелить в ногу можно практически на каждом шагу, а использование некоторых оптимизаций напоминает анекдот про установку вируса под Linux — там где эти оптимизации должны подстраховывать разработчика, нужно наоборот заставлять их работать.
PostgreSQL же не поддерживает даже базовые возможности по оптимизации (в частности JPPD), поэтому использовать его «как есть» для разработки основной части бизнес-логики, как это часто делают с MS SQL и Oracle, затея, скажем прямо, весьма трудоемкая и рискованная. Еще более забавно, когда кто-то заявляет о кроссплатформенности по СУБД (включая туда PostgreSQL), потому как тут два варианта:
- СУБД используется просто как подложка под ORM, и тогда да, фиолетово, какая СУБД будет, система будет тормозить на больших объемах на любой из них.
- на PostgreSQL все работает, пока там в каждой таблице до 100к записей, а когда данных становится больше, заказчику говорится: “ну а что вы хотели от бесплатной СУБД, покупайте MS SQL или Oracle”. Из-за этого, кстати, сформировался набор достаточно забавных стереотипов. Например, у нас была ситуация, когда мы переводили одного заказчика с Oracle на PostgreSQL и у нас требования к серверу были меньше чем у них было с Oracle (при гораздо большем функционале). И этот заказчик нас еще долго мучал вопросом: «Как это? Вы же бесплатный PostgreSQL предлагаете использовать, а значит нужен сервер раз в пять мощнее»
И это все, если смотреть на проблему с позиции разработчика. С точки зрения DBA все еще хуже — изменять физическую модель (то есть нормализовать / денормализовать данные, переносить данные между таблицами) самостоятельно он не может. Максимум, что может DBA — это достроить дополнительные индексы, в остальном ему нужно обращаться к разработчику, а, учитывая, что у последнего другие KPI и вообще «он все правильно написал, а это ваш MS SQL/Oracle тормозит, настройте его как-нибудь» ситуация очень быстро становится тупиковой. И в лучшем случае приводит к скандалу, а в худшем — к смене / переписыванию всей системы.
Здесь конечно может возникнуть вопрос, к чему вообще эта статья, критиковать может любой дурак. И, если честно, я не писал бы эту статью, если бы не знал, что все описанные проблемы можно решить. Да, непросто, я бы даже сказал, очень и очень непросто, но ведь за коммерческими СУБД стоят одни из крупнейших в мире корпораций. И у них вроде как работают «лучшие в мире инженеры». Поэтому, когда приходится решать проблемы за них, невольно ловишь себя на мысли, что «что-то здесь не так». Но оставим это на их совести.
А теперь, как говорится, минутка рекламы. Все вышеизложенные проблемы удалось решить в lsFusion, причем сделать это в общем случае. Прозрачная материализация (денормализация) данных, ограничения и триггеры на любые вычисляемые данные, JPPD в общем случае, динамическая физическая модель, компактный и одновременно быстрый оптимизатор булевой логики, компиляция циклов в запросы и наоборот, оптимизация работы с разреженными данными и последними значениями, множественные наследование и полиморфизм — и это только вершина айсберга. Помимо всего этого есть еще логика представлений и множество других чисто языковых возможностей, но это все к SQL напрямую не относится и будет рассмотрено в следующей статье.
Нас, кстати, постоянно упрекают, почему мы lsFusion сравниваем в том числе с DBMS, и, я надеюсь, эта статья дала хотя бы часть ответов на этот вопрос. Да, последнюю милю (определение типов выполнения JOIN — loop/hash/merge, поддержку ACID и т.п.) lsFusion не закрывает и использует для этого RDBMS, но по большому счету это особенности реализации lsFusion, разработчик этого не видит (если не хочет, конечно). Плюс, важным моментом является то, что, так как ответственность за оптимизацию всех запросов лежит на lsFusion, в качестве RDBMS можно использовать самую примитивную из них — PostgreSQL. И в этом смысле lsFusion можно рассматривать как своеобразный костюм железного человека для PostgreSQL, дающий ему «суперспособности» MS SQL и Oracle, во всяком случае, в плане оптимизации запросов. При этом, если последние в нормальных редакциях стоят по 14 тысяч долларов за ядро, то связка lsFusion + PostgreSQL абсолютно бесплатна. Впрочем, у этого факта есть и обратная сторона, мы так и не убедили ни одного заказчика поставить себе MS SQL или Oracle в промышленную эксплуатацию, поэтому многие вещи в коммерческих СУБД проверялись исключительно на тестовых стендах, а значит, вполне возможно, в статье могут быть некоторые ошибки. Так что если кто-нибудь где-нибудь такую ошибку найдет, просьба написать про нее в комментариях, и мы ее обязательно исправим.
angel_zar
Начал читать, дошел до этого пункта и возникли вопросы.
Это можно реализовать на MS SQL с использованием CROSS APPLY (нет блока ON придется функцию с 3 параметрами делать), на PostgreSQL LATERAL в помощь.
Дальше читать не стал, может быть большая часть и имеет место быть.
Veidt Автор
Спасибо, что-то про CROSS APPLY с LATERAL я забыл написать, сейчас дополню статью. Хотя с CROSS APPLY все равно проблемы будут, потому как если вы сделаете функцию от трех параметров, как тогда скажем вернуть остатки на дату по всем товарам / складам? То есть:
Получается что это уже не виртуальная таблица (представление), а именно функция будет.
unfilled
Как вариант — передавать в качестве товара / склада NULL и проверять xxx = xxx or xxx is null.
Veidt Автор
Я слабо представляю, как при этом надо будет переписать balance. А еще непонятнее как это все будет выполняться, учитывая что СУБД все эти OR'ы и IS NULL / IS NOT NULL не очень то любят (только в этой статье два примера есть).
InChaos
Тут все правильно сказано, на Null проверяется не поле а переменная, с индексами все ок будет. Но есть одно но, не знаю не тестировал с функциями, но с процедурами обязательно WITH RECOMPILE, если переменная может быть Null, т.к. план на 1 значение переменной и на весь диапазон будет абсолютно противоволожный (Scan vs Seek).
unfilled
Мне лень лезть в структуру таблиц, выглядеть будет примерно так
В SQL Server эти OR'ы не помешают использованию индекса, он поймёт что от него хотят.
Вообще, меня удивил выбор СУБД — SQL Server 2019 — это даже не RTM, его ещё пилят во всю.
NitroJunkie
В принципе вы правы с параметрами IS NULL он разберется наверное.
Но тут конечно нюанс не начнет ли он CROSS APPLY безусловно nested loop'ом выполнять.
То есть если вы сделаете:
SELECT FROM Product pr CROSS APPLY balance('01.01.2019',pr.id,NULL).
Не начнет ли он бежать по Product и рассчитывать подзапрос для каждого товара, сумеет ли он из product=@productid hash probe сделать. Надо будет потестировать.
Но в любом случае вы же понимаете, что это жесткий хак и чтобы его нормально использовать, вам придется все представления такими хаками делать. Плюс возможно с WITH RECOMPILE, чтобы MS SQL не дурел с планами.
Veidt Автор
Ну это чтобы не было комментариев: «в последней версии это скорее всего исправили» :) Просто у Oracle 19c у MS SQL тоже хотелось 19.
Про остальное сверху вроде ответили.
unfilled
Ну да, у Оракла релиз, у МС — CTP, красивые цифры, грамотный выбор. И не то, чтобы я был уверен, что в релизных версиях точно всё будет иначе, просто это выглядит странно.
Выше есть одна здравая мысль — надо протестировать. Пожалуйста, тестируйте.
Остальное (как, например, функцию с WITH RECOMPILE) я комментировать там не буду, чтобы остаться в рамках приличий.
Veidt Автор
Протестировал и изменил статью. Сможете прокомментировать? Вообще как я понял CROSS APPLY это практически всегда Nested Loop. То есть план с Hash построить SQL сервера не могут, и на таких запросах сразу кладут всю базу. Или я что-то не так делаю.
unfilled
Пока не могу прокомментировать, статья слишком большая, я уже путаюсь.
Покажите, пожалуйста, запрос в комментариях — с cross apply и без.
Veidt Автор
Вот ссылка на раздел.
Там внутри есть UPD выделен жирным там и запрос и план MS SQL есть в частности (хотя исходный запрос чуть выше UPD).
Чуть ниже под спойлером запрос с ручным JPPD. Вот его план (выполняется 12 секунд без параллелизма).
unfilled
Veidt Автор
Не, я не спорю что с этим «хаком» и CROSS APPLY он выполнится нормально. Вопрос догадается ли SQL сервер этот хак сделать самостоятельно, если ему дать ему базовый запрос (с balance, я такой view уже создал на тестовой базе). Как я проверил нет, можете тоже проверить, если хотите (balance там ровно как с статье). А UDF с этим хаком не имеет смысла, так как там внутри по сути контест внешнего запроса.
Вообще этот хак, это не более чем «JPPD в общем случае» контурно описанный в статье.
unfilled
Вроде ваши слова в статье, нет?
И почему такая UDF не будет иметь смысла? Вы сможете передавать ей нужные параметры и использовать её как «представление», если нужно. Как это сделать писал выше.
Veidt Автор
Я про то что UDF возвращает просто остатки. А смысл хака внутрь группировки добавить JOIN с контекстом запроса (в данном случае shipmentDetail.quantity = 5), но этот контекст запроса может быть любым (например product.lastSuppliedDate WHERE product.name LIKE 'dfDFFD'). То есть непонятно какую UDF вообще создавать.
unfilled
В «моём» запросе quantity внутрь CROSS APPLY не передаётся, внутри только таблица дат.
Veidt Автор
Так у вас подзапрос тогда для всех дат выполняется, а не только для тех где quantity=5 будет выполняться. Но не суть. Вы то все равно контекст внутрь протолкнули (пусть и его часть)
А в другом запросе в контексте могут совсем другие таблицы быть (например product). Какую UDF для остатков на дату соответственно создавать?
unfilled
Я протолкнул внутрь дату и только.
Вы написали, что при CROSS APPLY «вариант с hash join SQL сервера даже не рассматривают». Я показал, что это не так на вашем же примере.
Если выполнить:
Veidt Автор
Да, я это исправил сразу, когда сообразил, что JOIN это по большому счету частный случай APPLY, и фраза действительно странная.
А речь в разделе шла именно о случае, когда есть параметр и внутри нет ни одного предиката эквивалентности с ним, а только >, < (когда эквивалентность есть, можно обойтись обычными VIEW и JOIN). И у SQL сервер два варианта либо nested loop, либо JPPD в общем случае («хак»), который он делать не умеет.
nitrosbase
Шикарная статья, спасибо! Но я бы всё-таки различал три вещи: абстрактная реляционная модель, язык запросов к ней, реализация всего этого в современных СУБД. Из названия статьи хочется заподозрить, что речь пойдет о втором пункте, но на самом деле речь идёт о всех трёх. Как-то бы переименовать статью, быть может (но не знаю как).
Veidt Автор
Спасибо. Да вы правы, наверное можно было бы так разделить, но это имело бы смысл, если бы какие-то из этих трех вещей могли использоваться друг без друга.
На практике же дело приходится иметь с ними всеми вместе взятыми + на самом деле обертками еще из логики представлений, ORM. То есть и так общей картины у большинства людей нет, а если еще раздробить информацию, то совсем какой-то информационный шум получится.
Berd90
Не совсем понял, какие конкретно у автора поста претензии к SQL.
На мой взгляд, сейчас SQL базы в целом очень хороши — их больше 20 лет отлаживали.
Бывают правда некоторые притензии к синтаксису (в разных базах он разный, бывает что в одной базе есть функции, которых в принципе нет в других. Например, в MySQL аналог синтаксиса WITH только недавно добавили — и ключевые слова почему-то другие сделали)
ИМХО, сейчас главная проблема баз — что в СУБД нет реализации аналогов «объектов» из коробки. Если хочется процедуры/функции привязать к конкретной таблице, чтобы удобнее было с ними работать — нужно поверх базы ещё «словарь» организовывать.
И ещё важная проблема — не все базы хорошо переводятся на «кластерный» способ работы, как в NoSQL. Вроде только в Vertica и PostgreeSQL это хорошо реализовано — в MS SQL же и MySQL — насколько мне известно, с этим проблемы. Про Oracle не скажу — просто не углублялся в этот вопрос применительно к нему.
Veidt Автор
Кстати это модный тренд сейчас — Distributed SQL с ACID. Google Spanner, Yugabyte'ы всякие. Но они к сожалению даже window функций вроде не поддерживают, не то что рекурсивных CTE. Но надеюсь это пока.
VlK
Есть еще, например, Exasol, у них довольно сильная поддержка стандарта. Не уверен, правда, насчет рекурсивных CTE.
Veidt Автор
Я у них ACID'а что-то не вижу, а учитывая, что они позиционируются как analytics database, то возможно его там и нет. А я имел ввиду Distributed SQL сервера именно с ACID.
alexxz
Что касается Exasol… Тут граница проходит не по ACID, а по типу нагрузки. ACID в Exasol идеальный, более того, уровень изоляции транзакций serializable. Но база адаптирована под OLAP профиль (редкие большие записи и много сложных вычиток), а не под OLTP, когда идёт высоко-конкуррентное чтение-изменение данных в одной таблице.
Veidt Автор
Del
pankraty
О да, с "проталкиванием" параметров во VIEW буквально на днях столкнулся в полный рост.
Есть, скажем, таблица с историей статусов, и на ее основе создана VIEW, показывающая последний статус для каждой сущности. И если запрашивать данные по ID, план получается хороший, с index scan, запрос быстрый:
Но если это же условие применяется в join-е, и даже если запись, к которой происходит join, будет строго одна в силу других условий, планировщик не парится, и делает full table scan, по всем миллионам записей:
Получается, что VIEW призвана абстрагировать разработчика от особенностей хранения данных, но, как и многие другие абстракции, норовит "протечь", заставляя разработчика идти на разнообразные ухищрения.
Veidt Автор
Вообще чаще всего, если представления простые, обычно проблемы со статистикой. Тот же Oracle очень чувствителен к ней (и к примеру при добавлении индекса, не всегда пересчитывает ее по этому индексу, это вообще крышу сносит). Но да если у вас там внутри функции от колонок, громоздкие Or'ы, аналитические функции и хоть что-то нестандартное, готовьтесь к удару по iowait вашего сервера.
pankraty
VIEW простая, в ней "всего лишь" фильтрация по результату оконной функции.
Что-то вроде
Как же не хватает агрегатных функций типа FIRST и LAST. Все способы обойти это выглядят костылями в той или иной степени...
Veidt Автор
Это да. Я сам 100 раз перечитывал документацию не веря, что таких функций нет. Но это видимо потому что в MS SQL есть оптимизация SELECT MAX, и предполагается что именно его надо использовать. Но там совсем жесткие запросы получаются.
Кстати в Posgres эти агрегатные функции не очень сложно создаются:
Но это потому что в PostgreSQL в custom aggregate функциях ORDER clause поддерживают.
В MS SQL бодаться с созданием таких функцией куда больше. Там и ORDER'а нет и их на C# надо писать.
Что там в Oracle если честно не помню.
unfilled
А можно немного подробнее? Что должны делать LAST и FIRST? И где в SQL Server нет ORDER?
Вообще, там есть FIRST_VALUE/LAST_VALUE — они делают что-то другое?
pankraty
Это оконные функции, а речь про агрегатные. Они немного по-разному работают и не всегда взаимозамеяемы.
unfilled
Ну, т.е., условно, смысл в том, чтобы написать
select e, last(status)
from t
group by e
и получить последний по какому-то принципу в таблице t статус? А как должен определяться этот принцип?
pankraty
Да нет, я прекрасно понимаю, почему их нет. Просто неоднократно сталкивался с тем, что их наличие теоретически могло бы сделать запрос намного проще/нагляднее/понятнее.
Про отсутствие STRING_AGG до версии 2017 и связанные с этим костыли даже упоминать не хочется...
Veidt Автор
Дело не в том. В Postgres можно создать функцию LAST как я приводил выше и сделать что-то вроде:
SELECT LAST(id ORDER BY date,id) FROM shipment
В MSSQL ЕМНИП WITHIN GROUP есть но у конкретных агрегирующих функций. У custom aggregate нет. Во всяком случае лет 5 назад вроде так было.
Про FIRST_VALUE и LAST_VALUE уже ответили. Это оконные функции.
os9
Разобраться бы и посмотреть планы.
«абстрагировать разработчика от особенностей хранения данных» — sql-разработчик должен понимать, что под капотом view — сканы или поиски по индексу.
В вашем случае вместо тяжелой оконной конструкции почему бы не применить cross apply или табличную функцию —
NitroJunkie
Так, а зачем тогда view вообще нужен?
os9
Для уменьшения размера кода.
Berd90
Ещё хотел добавить:
В MS SQL как раз для этого и придумали Columnstore индексы.
У них правда есть ещё применения — но конкретно в этом случае должно помочь их использование.
unfilled
Эм, columnstore индексы там придумали для другого — больше для поддержки здоровенных аналитических запросов в DWH.
Для того, что описано в цитате намного больше подходят filtered индексы.
Berd90
Поправлю себя — ещё лучше для такого случая подходят SPARSE столбцы (разреженные).
Буквально вчера проверял. У таблицы в трети столбцов было много значений NULL.
Для всех таких колонок поменял тип столбца на SPARSE — освободил почти четверть места у таблицы.
Если в колонке подавляющее большинство значений NULL — добавление такого свойства для столбца очень хорошо оптимизирует место на диске, а значит и быстродействие (операции чтения из долговременной памяти — одна из самых долгих операций, меньший объём читаем с диска — быстрее работает).
mmMike
Из свежего…
Две структуры. В одной 3 параметра VARCHAR2, в другой два.
Использование не той Array структур (Oracle) в параметре — выдает четкое предупреждение, что не те параметры.
Использование не той Array структур внутри другой структуры как параметра приводит к неопределенному поведение хоста Oracle (стек или память портится на ХОСТОВОЙ части) и любимой ошибке ORA-00600: internal error code или вообще Listener не отвечает Jdbc.
Из за одной опечатки в коде пол дня потрачено на поиск. Причем на на разных версиях Oracle проявлялось по разному.
Чем, кстати, не дырка для атаки на хостовую часть...
Ну и еще до кучи всякого в реализации JDBC встречалось… Лень находить артефакты для комментария. И код JDBC при трассировке оставляет странное впечатление (возможно он еще с java 1.4… но стиль написания забавен)
Veidt Автор
Ну как пишут Oracle это известная штука. Гулял с год назад соответствующий пост.
Собственно, я когда тестировал матпредставления, если честно так и не смог их заставить работать с UPDATE при FAST REFRESH ON COMMIT. При INSERT обновляются, а при UPDATE нет. Но я не стал уже это в статье писать, уверен что где-то есть секретное знание, как это нужно делать. Просто мне не удалось его найти.
Ну и вообще если погуглить, про проблемы с производительностью FAST REFRESH матпредставлений, там такие веселые костыли выплывают, но я с Oracle в какой-то момент уже перестал чему-то удивляться.
os9
Впечатление от статьи — по верхушкам.
Veidt Автор
Так, а как поддерживать остатки в таком случае? Ну и разработчик может изначально не знать, нужно ли материализовывать данное представление или нет.
Это как? Можно пример?
Так а что делать если у вас написана хранимка, и ее надо для 1000 записей вызвать?
Тут вопрос есть на stackoverflow.
Есть transition tables, но это как я понял не совсем то. Во всяком случае примеры там какие то совсем странные.
StrangerInTheKy
1. Если у вас есть хранимка, и ее надо один раз выполнить для 1000 записей, то берете и выполняете.
2. Если у вас была хранимка, которая выполнялась для одной записи, а теперь нужно будет регулярно выполнять ее для 1000 записей, значит, у вас изменились требования и код надо дописывать/переписывать. А раз все равно надо переписывать, то переписываем хранимку так, чтобы ее можно было выполнить для любого числа записей от 1 до 1000 (в идеале — до плюс бесконечности).
Veidt Автор
Ну я это и написал, что надо переписывать. А мысль была в том, что SQL сам мог бы компилировать циклы в запросы. Ну теоретически в идеальном мире. Мы, к примеру, это реализовали во всяком случае.
StrangerInTheKy
Veidt Автор
Строго говоря и хранимок в SQL нет. Это тоже процедурные решения.
Понятно, что если есть какие-то сложные рекурсивные зависимости: когда команда изменяет данные, которые читает следующая команда в процедуре, тогда да такую, штуку тяжело оптимизировать. Но очень большое количество простых процедур (без таких зависимостей) можно и нужно оптимизировать. Проверено на практике.
norguhtar
SQL декларативен, какие циклы? Те же полиморфизмы и наследования из той же оперы. Зачем они там?
Veidt Автор
В пункте Проблема N+1 есть уточнение про расширения вроде.
А полиморфизм с наследованием это очень даже декларативные концепции. Я бы даже сказал более декларативные чем SQL, если декларативностью вообще меряться можно. И я в статье написал зачем:
norguhtar
В пункте Проблема N+1 есть уточнение про расширения вроде.
Они не входят в стандарт, про что вам сразу и сказали. И более того они не декларативны по своей сути.
А полиморфизм с наследованием это очень даже декларативные концепции.
В каком месте и каким боком они к SQL подходят и как они туда ложатся? Единственное применение наследование которое я использовал в том же PostgreSQL это для секционирования таблиц, для другого оно как-то плохо применимо. Приведите хоть один вариант использования когда это прям дает какой-то внятный профит. Я вот не припомню ни одного применения где полиморфизм и наследование давали какой-то внятный эффект. Более того чем дальше тем больше в нем разочаровываются и откатываются к функцинальщине.
Veidt Автор
Стандарт SQL вообще очень забавная штука. Они UPDATE и агрегирующие функции стандартизировать не могут. Ну я нигде и не говорил про стандарт, это чисто практическая статья, а хранимки и триггеры при разработке на SQL используются очень часто.
Функциональщина перпендикулярна наследованию и полиморфизму. А вообще возьмите крупный проект на C++ или Java и посчитайте там количество extends /: и abstract / virtual.
norguhtar
Стандарт SQL вообще очень забавная штука. Они UPDATE и агрегирующие
функции стандартизировать не могут.
update и агрегирующие функции хотя бы ложатся в канву.
А вообще возьмите крупный проект на C++ или Java и посчитайте там количество extends /: и abstract / virtual.
Угу весьма здорово привести ООП языки как довод. Давайте уж тогда ORM еще припомним и что уши N+1 оттуда торчат.
transcengopher
Зачем полиморфизм в Java мы и так знаем. А вот в SQL он зачем? Какую задачу в SQL вы хотите решить при помощи полиморфизма, которая без полиморфизма решается плохо или не решается, например, нормализацией?
Veidt Автор
Затем же зачем и в Java. Модульность и расширяемость. То есть нужен новый функционал, добавляем новые классы, наследуем, добавляем в абстрактные представления реализацию для этих классов. Иначе спагетти-код как в 1С.
А можно все сделать нормально классами, наследованием и полиморфизмом. Вот пример кусочка дерева классов например:
transcengopher
А какой ещё, простите, функционал вы хотели бы добавить к объекту "Запись Таблицы SKU"? Других-то объектов в большинстве RDBMS нет, потому что именно тип "Запись Таблицы<T>" лучше всего решает основную задачу базы данных — хранения данных/фактов о <T>.
Полиморфизм самих данных в духе
решается нормализацией данных — имеем отдельно таблицы Location и Airport, и в Airport также имеем foreign key к Location. Просто, оптимизируемо, и — внимание! — логически верно, так как
Или вы бы хотели хранить как значение произвольные объекты? Ну так и этого у DBMS тоже есть, и даже с полиморфизмом объектов (и иногда с неявным приведением типов, как в Oracle).
UPD: Пригляделся к вашей картинке. Судя по названиям, неправильно вы домен готовите. Если заменить наследование композицией, иерархия будет заметно проще. Всем этим документам всего-лишь нужно обладать некой общей частью, а выглядит будто вы заставляете их ещё и вести себя одинаково только по факту наличия этой общей части, но не по факту действительного наличия общего поведения. Сужу исключительно по наличию нескольких типов с одинаковым названием.
Veidt Автор
Так Airport это не Location. А вот Capital это City. А Роза — цветок.
Я не совсем понял, вы считаете что наследование не нужно и всегда можно обойтись композицией? Очень смелое утверждение.
То почему произвольными объектами плохо я писал в статье в разделе про наследование и полиморфизм. И как бы я хотел тоже там писал. И уже несколько раз тут отвечал, почему наследование таблиц не подходит.
В lsFusion просто множественное наследование есть. Это одинаковые классы. Просто так граф в виде дерева выглядит.
transcengopher
И всё же, так и нет ответа на исходный вопрос: какие задачи вы хотите решить полиморфизмом в RDBMS? Какие части реляционной модели полиморфизм улучшит?
А в чём функциональное различие capital и city, кроме единственного флага, который, к тому же, меняет своё значение во времени? Какие свойства есть у столицы? Почему, вы считаете, недостаточно сделать
type Capital {CityId, ...},или даже вообще по-рабоче-крестьянски
type City { bool IsCapital, ... },а вот обязательно надо иметь
type Capital extends City?Что улучшится при втором подходе? Как при наследовании быть если столицу перенесли? Будем удалять город? Или всё-таки только статус предадим от одного города другому?
То же самое с розами. При моделировании растений вообще намного проще иметь Traits, потому что на практически любое свойство роз, кроме ботанического определения (которое есть натуральный ключ), найдётся вид-другой роз, у которых это свойство отсутствует.
С точки зрения (под)системы хранения данных — да, определённо. В общем для ООП — зависит от доменной модели.
Veidt Автор
То что вы говорите, это отдельный вопрос Наследование vs Композиция. И напрямую к теме не относится.
Я лично без наследования и полиморфизма не могу. Но есть к примеру 1С: УТ, где весь код привет 70-е и ничего люди покупают и дописывают.
transcengopher
Я так-то вообще всего лишь пытаюсь выяснить, для чего вам в SQL (или PL/SQL?) понадобился полиморфизм, и полиморфизм чего именно вы вообще имеете в виду.
А вместо этого вижу то столицы, то розы, то код из семидесятых.
Veidt Автор
Чтобы работало быстро
Полиморфизм вычислений (в SQL читай представлений).
Чтобы если мне нужно было добавить новый вид цветка, я бы добавил класс Роза, унаследовал от класса Цветок, во все необходимые абстрактные представления добавил реализации (подзапросы) для этого класса. И все запросы с цветами начали бы учитывать эти подзапросы.
transcengopher
Ну смотрите:
В данном случае подзапрос определяет подтип строки таблицы, выглядящий примерно как
И обе части UNION'a приводятся к этому типу, в итоге основное тело запроса работает с супертипом, а не с каждым подтипом отдельно. Вот это — вполне полиморфизм, с точки зрения хранилища фактов, коим SQL является, но вы же вроде бы чего-то другого добиваетесь, не так ли? Вот и объяснили бы, чего. Эти вот притянутые примеры, которыми вы продолжаете кидаться, картину не проясняют, в них слишком мало анализа с вашей стороны недостатков вашего подхода и преимуществ других подходов. Например, в чём конкретно преимущество подхода, когда вы храните все розы отдельно от остальных цветов, так что вам приходится объединять подклассы вручную через представления?
Так а где тут полиморфизм-то? Так вы в этих представлениях по сути наплодите в итоге кучу тех самых if-ов (подзапросов, если точнее, но не суть), о которых так нелестно отзываетесь в соседней ветке комментариев. И после каждого нового класса будете плодить ещё больше "if-ов".
Тогда как при решении композицией у вас для вашей розы будет самодостаточная запись каталога цветов и рядом вспомогательная таблица атрибутов именно роз, и никаких абстрактных представлений переписывать будет не нужно. Хотите цветы — читаете из цветов. Хотите розы — читаете из роз, объединяете с цветами (и вот для этого-то какое-нибудь представление прямо идеально подходит).
os9
www.postgresql.org/docs/10/plpgsql-trigger.html
посмотрите пример 42.7 про триггер уровня оператора. Да, transition tables, все работает.
Veidt Автор
Ок, спасибо, сейчас дополню статью.
maxim_ge
Например, так. Нужен журнал операций, отдельный код асинхронно «бежит» по журналу и по операциям «материализует» остатки в отдельной таблице.
Veidt Автор
Ну то есть остатки будут приблизительные? Интересно что скажут пользователи на такое? Особенно, если их надо в транзакции использовать.
maxim_ge
Если надо в транзакциях использовать, можно сразу в транзакциях и считать остатки, триггерами или их аналогами.
Veidt Автор
Так и как именно это делать? Например даже в таком простом случае с остатками как в статье. На какие таблицы и какие триггеры делать?
Mur466
Есть реестр остатков, он напрямую из бизнес-логики не меняется. Есть журнал проводок. Остатки меняются только через добавление записей в таблицу проводок, в проводке ссылка на документ-основание.
Изменение реестра остатков делается либо триггером на таблице проводок, либо в API функциях, которые выполняют запись в таблицу проводок
Veidt Автор
А таблицу проводок кто и какими триггерами меняет? И что в ней лежит?
Я так понимаю вы 1Скую схему продвигаете (я знаю как она работает). Вы же в курсе, что они редактирование по сути удалением \ добавлением предполагают делать (что я собственно и писал в статье)?
Mur466
Я об устройстве 1С уже довольно смутно помню. Но терминология устоявшаяся, понятная широким массам.
В таблице проводок каждая запись — это: артикул, количество-дельта изменения остатка, ссылка на документ-основание.
Суммирование всех записей в таблице проводок с group by по артикулу приводит к получению актуальных остатков. Но делать это никогда не приходится, так как эта информация хранится в реестре остатков — таблице, которая в общем по структуре напоминает матвью такого запроса.
Сам факт изменения остатков (в плюс или минус) фиксируется добавлением записи в таблицу проводок. Как и откуда это инициируется — более широкая тема. Ну допустим, в терминах 1С при «проведении документа-основания». Но ничто не мешает делать это индивидуально по каждой строке документа. В зависимости от правил системы можно запретить на таблице любые dml кроме insert: откат проводок только через сторнирование. А можно и разрешить удаление и редактирование. В любом случае, в триггере уровня записи на таблице проводок мы знаем без лишних запросов, какой update выполнить на реестр, чтобы поддержать там актуальные остатки.
Ну и естественно, в эти таблицы можно добавить внешний ключ на склады, типы остатков или другие разрезы, кому что необходимо в учете
Veidt Автор
Ну то есть, вы немного декомпозировали проблему, сделав таблицу проводок, и тем самым чуть-чуть упростив базовую проблему. Теперь у нас есть проблема обновления таблицы проводок. Она по вашему сильно проще?
Ну и плюс вы возможно создали лишнюю материализацию (таблицу).
Mur466
Вчера в 17:18 вы спросили: «Как именно это делать?»
Я привел рабочую схему. И да, я сделал материализацию, которая НА ПРАКТИКЕ решает проблемы с производительностью, которые вызывает ваша очень теориетически правильная вьюха остатков.
Не вижу никакой проблемы с обовлением таблицы проводок. Блокиурется запись реестра, выполняется добаление/изменение/удаление проводки, снимается блокировка с записи реестра.
Veidt Автор
Как раз теоретически правильная никаких проблем с производительностью не вызывает.
Это только если количество меняется к примеру. А если к примеру у вас внутреннее перемещение и в нем меняется склад прихода, то вам нужно руками к примеру найти и удалить все приходные и расходные проводки старого склада прихода и добавить все приходные и расходные проводки нового склада прихода. И как вы вообще собираетесь определять ситуации изменения и что именно там изменилось (количество или склад)? Триггерами?
Плюс если усложнить логику остатков (скажем двойным учетом) или логику каких-нибудь задолженностей взять (со всякими отсрочками). Там знаете сколько таких сценариев будет (например когда тип договора меняется)?
Mur466
>И как вы вообще собираетесь определять ситуации изменения и что именно там изменилось (количество или склад)? Триггерами?
Именно. В триггере из :old берем старую строку реестра по ключам артикул/склад, вычитаем старое кол-во. Из :new берем новую строку и увеличиваем количество.
Но вообще такая операция, как изменение склада в «выполненном» документе (документе в финальном статусе, в терминах 1С можно сказать в проведенном) — недопустима. Так как на разных складах разные мат.ответственные лица. И это должно делаться в два этапа: откат документа из финального статуса делает мат отвестветвенный старого склада, а проставление нового склада и перевод в финальный статус — мат.ответственный нового склада. Таким образом с точки зрения СУБД все сводится у удалению старых проводок и созданию новых. Изменение проводок не требуется.
По поводу двойного учета и у типа договора не готов дискутировать, потому что не понимаю, какая предметная область и как реализовано. На первый взгляд, изменение типа договора вообще не должно влиять на реестр остатков, так как договор не является документом товародвижения, то есть основанием для проводок.
Veidt Автор
Не, это как по таблице проводок остатки обновлять. Это отдельная дополнительная задача (хотя тут даже INDEXED VIEW в MS SQL возможно справится).
Вопрос как по тем же документам проводки обновлять и как изменения в документе отслеживать? Там же нужно изменения и shipmentDetail и shipment и receiptDetail и receipt отслеживать.
А если остатки например в разрезе отделов магазина. Там одинаковые ответственные. И они тоже люди и тоже ошибаются. Или они должны дрожащими руками каждое действие делать?
И ввод тоже? Но опять таки это уже вопрос конкретного бизнеса, у всех бизнесов разные процессы. А вы сейчас задачу под ответ подгоняете.
А причем тут товародвижение, я говорил про представление (регистр) текущей задолженности поставщика (по аналогии с остатками, только в деньгах)
Veidt Автор
Кстати изучаю эту тему сейчас. С CROSS APPLY (вместо INNER JOIN) действительно вроде прокатывает (даже вроде не руша JPPD), а что с OUTER APPLY (вместо LEFT JOIN) делать, там то условие соединения в WHERE не перенесешь.
xtender
Касательно структуры статьи:
1. Лучше бы разбили статью на несколько отдельных частей по разным пунктам, т.к. сейчас получится каша с кучей комментариев к разным частям вразброс,
2. Еще лучше было бы разбить и по разным СУБД, т.к. уже вижу кучу неточностей по Oracle.
Далее постараюсь (насколько будет хватать времени и не будет мешать лень) прокомментировать по пунктам:
1. View: Материализация представлений поддерживается в очень частных случаях
Это очевидная неточность. Материализация представление поддерживается всегда, но не всегда поддерживаются те или иные опциональные возможности, как например FAST REFRESH.
2. Касательно FAST REFRESH: если вы подумаете и сами попробуете проанализировать как можно реализовать инкрементальные обновления, то поймете, что список ограничений абсолютно адекватен текущей сложности SQL.
3. «вас будет ждать еще один неприятный сюрприз: материализованное представление обновляется только в самом конце транзакции.»
Сюрприз?! При создании мвью вы сами выбираете «on commit», так что должны знать, что это происходит при коммите.
4. «абсолютно непонятно, как в принципе получить актуальные данные для материализованного представления внутри транзакции» — вы прямо напрашиваетесь на холивар об актуальности «грязных чтений». Не путайте функциональность и целевое назначение view и mview. MView, в первую очередь, это таблицы со всеми сопутствующими свойствами.
5. «один из достаточно авторитетных экспертов Oracle Donald Burleson в одной из своих книг.» — Что?! На таком серьезном ресурсе как хабр и упоминать бурлесоновщину?!
6. «View: В параметризованные представления во FROM можно передавать только константы»
В Oracle можно использовать контексты, функции, можно создать FGAC и тд и тп.
7. «В MS SQL для решения таких задач есть так называемые table inlined функции, в них можно объявить параметры и использовать их внутри запроса» — в оракле тоже есть и, кроме того, туда спокойно можно передавать параметры из других таблиц
8. «JPPD: Не работает с оконными функциями и рекурсивными CTE»
Работает, просто оконные функции вы не умеете готовить:
вы указываете «row_number() OVER (PARTITION BY shipment ORDER BY id)» — то есть сами инструктируете СУБД сначала посчитать ROW_NUMBER для всего дата сета из таблицы, а фильтруете на другом уровне, после этого подсчета. Если СУБД сначала отфильтровала по вашему предикату, то результат ROW_NUMBER был бы неверным.
9. «JPPD: Низкая эффективность при работе с денормализованными данными»
Ничего не понятно… Касательно выбранных планов оптимизатора, надо приводить точные данные и, например для Оракла, трассировку 10053. Тут важно даже умение собирать статистику.
10. «Так, например, переписанный запрос оконными функциями будет выглядеть следующим образом» — что вы тут хотели и почему запросы не эквивалентны? Вообще во всех указанных СУБД есть LATERALS/CROSS APPLY, которые легко решают проблемы с JPPD
11. «Разделение логики условий на типы JOIN и WHERE» — кстати, советую прочитать про Oracle vs ANSI синтаксис, и пре- и пост- предикаты в отличной книге «The Power of Oracle SQL» habr.com/en/post/461971
12. «Плохая оптимизация при работе с последними значениями»
Оракл может построить хороший план:
Проверьте свои актуальность и свойства статистик по этим таблицам.
… /// to be continued…
Veidt Автор
Ну под материализацией я имел ввиду естественно обновляемую материализацию. Не обновляемая нужна только для задач аналитики. Но тут вопрос чья терминология первична (вон в MS SQL они вообще индексированными называются), если считать SQL(а точнее Oracle) первичной то да, может написать online или что-то типа того.
Я не то что подумал, я учавствовал в реализации этого функционала, причем работающего на OLTPых террабайтных базах систем класса ERP.
Я заметил, и почему это не удобно написал в статье.
Тут согласен, надо тщательнее изучить список экспертов. Не поделитесь ссылкой?
Если вы про контексты, о которых я думаю, то это адский ад. То что в Oracle есть аналог table inlined функций возможно, я и не утверждал обратное. Причем тут FGAC не совсем понял.
То есть запрос который я привел в Oracle заработает? А можно пример? Тогда дополню статью.
Я там внизу привел пример как надо было делать JPPD (и как мы его делаем).
Не уверен что CROSS APPLY сработают с оконными функциями, но это и не важно. Вопрос, что Oracle сам этого не делает. По такой логике зачем вообще оптимизатор нужен, если можно все самому сделать. Собственно я для этого и начал с представлений, так как именно с ними задача оптимизаций запросов особенно актуальна.
И какое отношение это имеет к тому что я написал?
Я там 100 раз все пересчитывал. Но в любом случае там вроде COUNT STOPKEY должен быть, а не FILTER(COUNT(*)>0). Но тут не уверен. Вы можете скриптом который вначале статьи сгенерить базу (он минут 10 выполняется максимум) и выполнить запрос.
StrangerInTheKy
А потом оказалось, что сложные запросы, написанные кем-то другим, а то и самим собой, но очень давно, (ВНЕЗАПНО!) нужно отлаживать и рефакторить. И вот в длинной трехстраничной портянке начинаешь разбираться — где условия соединения, где логика, где чё. Потом условия соединения сами собой кучкуются вместе, а потом уползают в секцию FROM, чтобы не мешались.
Потом появляется вишенка на торте. Оказывается, что записей какое-то не такое количество, поэтому нужно закомментить все, кроме одной таблицы, а потом приджойнивать остальные по одной. И оказывается, что если все условия соединения описаны во FROM, то решается эта проблема исключительно просто:
То есть добавить/убрать таблицу к запросу — это всего один комментарий. Сравните с тем, что будет, когда все условия идут вперемешку в WHERE.
Короче, я лично транклюкирую ту гадину, которая такую возможность у меня отнимет [сатанинский хохот за кадром]
Veidt Автор
Этот пункт не про разделение на JOIN и WHERE. Это пункт про разделение типов JOIN и WHERE. То есть INNER, LEFT, RIGHT и FULL и WHERE.
В остальном согласен условия соединения в WHERE на сложных запросах — это ад.
shorokhovs
1.
Начиная с MSSQL 2017 наконец-то присутствует функция TRIM
2.
В PostgreSQL можно начиная с версии 10 (клауза REFERENCING)
Единственное ограничение — триггер не должен быть мультисобытийным
Veidt Автор
Я этот пример, если честно в гугле нашел.
Да, уже подсказали выше. Спасибо. Сейчас подправлю статью.
StanislavL
Отличная статья! Мир несовершенен.
Мне не хватает еще возможность передачи параметра во FROM
Например:
Хорошо бы добавить возможность получить N строк просто для запроса.
Добавить бы ограничения на группу. Скажем запрос «Топ 3 самых дорогих товара для всех категорий» это тот еще квест.
Нарастающий итого (running total) еще один вызов. Сделать то можно, но всегда непросто. Сильно не хватате обращений к предыдущей и следующей строке.
unfilled
Без упоминания конкретной СУБД непонятно в чём проблема. Насколько я понимаю, во всех трёх СУБД из статьи проблем быть не должно.
dynamic sql
fetch...offset
sum(x) over(order by ...)
PRECEDING / FOLLOWING
StanislavL
Это вынесено но уровень надстроек — в каждой СУБД свое (а в некоторых и нету).
michael_vostrikov
Это не проблема N+1. Она ведь не просто так так называется. Попробуйте определить, где тут N, а где 1.
Veidt Автор
1 есть неявно. Когда вы цикл будете делать, там вам запрос для получения 1000 записей скорее всего понадобится (FOR row IN (SELECT ...)). Но да, базовая проблема вообще из области ORM.
Другое дело, это самая близкая аналогия из всех существующих, и конечные последствия у нее такие же.
michael_vostrikov
Из области ORM, и ORM же ее и решает. Она связана с чтением. Насколько я знаю, вставку 1000 записей не называют N+1. При работе с базой из приложения, в тех местах, где она может возникнуть, она легко оптимизируется до массовых вставок.
В примере все параметры вводятся извне и добавляются в таблицу при вызове процедуры, поэтому непонятно, откуда там может что-то выбираться до вызова.
Veidt Автор
Ну как решает, пытается решить. Точнее разработчик должен это делать. Но это в следующих статьях про ORM/ERP фреймворки.
Что значит легко? Переписыванием процедуры по сути. Или дублированием что еще хуже.
Там имелось ввиду что нужно вызвать эту процедуру для всех записей какой-то таблицы.
michael_vostrikov
Все нормально решается, автоматически делается второй запрос с
WHERE foreign_key IN (...)по ключам из основного запроса. Разработчик только указывает что-то типаEntity::find()->where(...)->with('relationship').При работе с базой из приложения таких процедур вообще не будет. И аналогов тоже не будет. Изменение параметров сущностей отдельно, сохранение отдельно. Вызываем логику для каждой сущности, обработанные сущности накапливаем в массив, по достижении N записей сохраняем и массив сбрасываем. Потребность в массовых вставках обычно возникает при каких-нибудь выгрузках/загрузках, которые делаются консольными приложениями и к работе пользователей не имеют отношения, а значит на соответствующий код не влияют.
Veidt Автор
Ну так я и говорю разработчик делает, а не ORM.
Ну так вы когда вызываете логику каждой сущности у вас N+1 и будет.
Или еще хуже вам эту логику дублировать придется. Сначала чтобы избежать проблемы N+1 докинуть в первый запрос все нужные данные, а потом еще раз повторить эту логику в ORM с обработкой этих данных (дублирование конечно не полное будет, но процентов на 80). Ну и вариантов выстрелить в ногу, если забыли, не те данные считали и т.п.
Но вообще конкретно эта статья не про ORM, тут как раз про то когда бизнес-логику на SQL делают — в банках и других финансах, ритейле это очень распространено.
michael_vostrikov
Так она сама и не может знать, какие части из всего графа сущностей надо загрузить из базы. Написать вызов одной функции это не какая-то большая сложность.
Ага, только не запросов к базе, а вызовов функций внутри приложения. Это будет примерно то же самое, что будет делать база для каждой строки.
Ничего не надо дублировать, и первый запрос менять не надо. Зачем? Логика находится в одном месте, ее и вызываем.
Вот и надо говорить о проблемах и альтернативах. Возможно для кого-то некоторое снижение производительности будет приемлемо для уменьшения вероятности ошибки и увеличения удобности разработки.
NitroJunkie
Ну как бы из кода обработки могла бы догадаться. Ну и там может быть много данных использоваться, так что функция не одна будет.
Подразумевалось, если вы предварительно не укажите какие данные считать (то что вы писали сверху).
Имелось ввиду, допустим вам надо в строку записать текущую цену по товару, ну и надбавку в зависимости от классификатора. Вам надо сначала orm'у сказать что их надо предварительно загрузить, а потом в логике обработки опять к ним же обратиться и выполнить непосредственно обработку. Хотя ORM'у что-то предварительно говорить это monkey job (а точнее ручная оптимизация, то есть accidental complexity).
michael_vostrikov
Нет. Данные из одной и той же таблицы могут использоваться в разных частях приложения, в коде одной обработки надо загружать связанные сущности, в коде другой нет.
Ага,
->with('relationship1')->with('relationship2')->with('relationship3'), очень сложный код)Ну тогда и ваше решение от N+1 не избавляет, и вообще от нее избавиться нельзя. База же для всех N строк делает обработку.
Все равно никакого дублирования логики нет.
Логика может находиться и не в Sku, но все равно в одном месте.
В таком общем виде это утверждение неверно. Вы о каких-то своих представлениях говорите, и скорее всего неправильных.
NitroJunkie
Смотрите, у вас просто достаточно примитивные примеры получаются. Например текущая цена она для склада, товара. Склад лежит в документе, товар в строке документа, текущая цена это представление — SELECT LAST (price ORDER BY date,id) FROM prices GROUP BY product, store.
Как это все будет выглядеть?
michael_vostrikov
Код обновления тогда тоже напишите.
michael_vostrikov
Если я правильно понял связи, можно вот так сделать. Не уверен, что из коробки заработает в Yii (я в примерах его подразумеваю), но в целом это решаемо.
Но такая архитектура с обновлением из истории выглядит странно. Я бы сделал обновление цены там же, где добавляется строка в таблицу
prices. Если надо строго раз в сутки цены менять, то можно добавлять в отдельную таблицуnew_prices (product_id, price)и обновлять только нужные строки по id, не перелопачивая при этом всю историю изменения цен на всех складах.NitroJunkie
Я имел ввиду вот что:
lastPrice = GROUP LAST price(Price p) BY product(p), stock(p);
WHEN CHANGED(product(DocumentDetail d)) DO
price(d) <- lastPrice(product(d), stock(d)) * (100 + markup(product(d), stock(d))) / 100;
Ну или можно так:
WHEN CHANGED(product(DocumentDetail d)) AND Product pr = product(d) AND Stock s = stock(d) DO
price(d) <- (GROUP LAST price(Price p) IF product(p) = pr AND stock(p) = pr ORDER date(p),p) * (100 + markup(p, s)) / 100;
У вас я так и не понял откуда она relation lastPrice возьмет (ну и вы код с описанием этих relation'ов не привели). Хотя видимо по самой задаче мы друг друга не допоняли.
michael_vostrikov
Весь код для описания связей там есть.
joinWith('lastPrice')обращается к функцииgetLastPrice(), которая возвращает настроенный объектQuery. После запроса заполняются все указанные связи, во всех$productбудет заполнен$product->lastPrice.Почему изменение цены товара не попадает в таблицу изменения цен?
Ну я там наценку не добавил, да, там будет так:
А можно просто
$productпередавать.NitroJunkie
А где привязка что product именно по product_id, а не store_id. Ну и тоже самое про documentRow.
Так тут цена просто отгрузки из текущей цены заполняется. Или я чего-то не понял в вопросе.
Так а как будет выглядеть код по его предварительному чтению?
michael_vostrikov
Код при джойне превращается в такой SQL:
store_idберется изdocument, без джойнов естественно работать не будет. Возможно там надо будет указать джойн в самой связи и поля с названиями таблиц, сейчас точно не скажу, но принцип будет такой же.А, и в
LastPriceу меня толькоselect('LAST(price)'), полейproduct_idиstore_idнет, надо братьselect('LAST(id)')и джойнить полную запись.По чтению кого? Все чтение это результат запроса в первой строчке. Читаются товары и их связи, причем так как там джойны, то вроде бы даже запросов с IN не будет, связи заполнятся из данных основного запроса.
Разговор же был про цену товара, причем тут цена отгрузки?
В общем получается так, у вас в коде названия не соответствуют терминам предметной области, архитектура тоже выглядит слегка странно. Можем обсудить подробнее, как это делается при бизнес-логике в приложении, если напишете исходные требования к системе на русском языке, типа небольшое ТЗ на пару абзацев. Код на вашем языке для меня не настолько понятный.
NitroJunkie
Так я так и не понял. То что сверху это весь код или нет?
Я про calculatePriceWithMarkup, ему же тоже нужны данные из базы. Если написать так как вы сделали, и в
$products = Product::find()
->joinWith('documentRow')
…
ничего не добавить, то она же N запросов делает.
В строку документа (отгрузки) имелось ввиду записать цену товара с надбавкой.
Так если его буквально с английского переводить будет:
Когда изменится товар в строке документа:
Записать в цену строки документа, текущую цену товара этой строки документа, склада этого документа с добавленной надбавкой для товара, склада.
Этот код и есть по сути ТЗ.
michael_vostrikov
Это весь код, но я его не запускал. В реальном приложении могут быть незначительные отличия, не влияющие на общую концепцию, но возможно вызывающие непонимание сейчас, поэтому я пишу уточнения.
У вас в примере туда передается только товар и склад. В моем коде все они достаются одним запросом. Откуда там N запросов, если данные уже загружены из базы?
В данном тексте нет термина "отгрузка". Поэтому это никакое не ТЗ, это просто описание работы вашего кода. Что за документы, какие документы, при каких условиях изменение появляется, откуда тут 1000 запросов возьмется.
NitroJunkie
Так в том то и вопрос, что непонятно насколько они небольшие будут. У вас и так уже мягко говоря не мало кода. А вы еще говорите будет что-то.
Так в одном документе много товаров. А у вас код считает наценку для одного товара.
Ну в данном случае я имел ввиду, что документ — это документ отгрузки. В любом случае можно считать что это просто документ. Давайте упростим задачу и забудем про изменение — то есть просто пересчитать для всех строк документа:
lastPrice = GROUP LAST price(Price p) ORDER date(p),p BY product(p), stock(p); FOR document(DocumentDetail d) = myDocument DO price(d) <- lastPrice(product(d), stock(d)) * (100 + markup(product(d), stock(d))) / 100;michael_vostrikov
Это кстати уже UPDATE, а не INSERT, который был в примере про N+1.
NitroJunkie
Если документ новый, то это будет INSERT, если старый — то UPDATE. Но разработчику это фиолетово на самом деле в lsFusion.
michael_vostrikov
И как вы предлагаете делать массовый UPDATE? Мы же про N+1 говорим.
Код, относящийся к обсуждаемой логике находится до многоточия. Остальное это аналог объявления внешних ключей в CREATE TABLE, и этот код общий для всего приложения. Вы вообще код функций не привели, только запрос.
Нет такого вопроса. Я показываю принцип, как это делается из приложения и почему это удобнее. Незначительные синтаксические отличия типа
table.fieldвместоfieldзначения не имеют, принцип от них не изменится.Ну так и у вас код считает наценку только для одного товара. Наценка зависит от одного товара товара и одного склада, а не от всех товаров в документе.
price(d) <- lastPrice(product(d), stock(d)) * (100 + markup(product(d), stock(d))) / 100;У меня товары уже загружены из базы в количестве
$limitштук, для каждого также загружены связи "строка документа -> документ -> склад". Для всех делаем расчет, потом все сохраняем в базу. Во время расчетов никаких запросов в базу не идет.Я говорю о том, что делать так не надо. Не надо ничего пересчитывать и перезаписывать. Это требуется крайне редко и обычно запускается по расписанию, а не по событию. Чтобы разобраться, как правильно, я предложил обсудить исходные требования.
NitroJunkie
А в чем проблема? UPDATE с запросами тоже работает. То есть мой пример можно скомпилировать в один запрос.
Нет сверху FOR идет. И lsFusion скомпилирует его в
Который в свою очередь скомпилируется в один SQL MERGE запрос.
Так а с prices и markup что будет? По сути вы будете эту логику на SQL (а точнее на функциональном псевдо-SQL) писать, а обработку в ORM?
Я уж молчу про то, что данные зачем то гоняются туда- сюда, с сервера БД на сервер приложений, а потом обратно.
В ERP/CRM/BPM, банковских системах и вообще любых сложных системах это вообще-то стандартный кейс, их тысячи там. И там не по расписанию, а в процессе ввода это все запускается.
michael_vostrikov
А, ну да, я про значения извне думал.
У меня код "считает наценку только для одного товара" ровно в той же мере, в какой движок базы "считает эту наценку для одной строки" при выполнении вашего запроса.
У меня тоже один запрос, а не N. Ну вернее 2, выборка и сохранение.
А что с ними не так? Последняя цена тоже загружена через связи, через JOIN с подзапросом.
markup, как я понимаю, содержит алгоритм наценки, который рассчитывается по product и stock, у меня он находится вcalculatePriceWithMarkup(). В SQL ничего не пишется, вся логика находится в приложении.Затем, что так удобнее управлять кодом. Нет тех проблем, которые вы пытаетесь решить, используя логику в хранимых процедурах.
Я вообще-то про то и говорю, что это стандартный кейс в первую очередь потому что "бизнес-логику на SQL делают". Если бы логика была в приложении, в большинстве случаев это было бы не нужно.
NitroJunkie
Так подождите, вы же по сути и пишете на SQL только в специфичной синтаксической обертке (с JOIN'ами, GROUP BY только в три раза длиннее) и с суррогатными классами (вроде LastPrice). И чем тогда удобнее управлять кодом? При этом данные гоняются туда-сюда.
michael_vostrikov
На каком SQL? На SQL я достаю данные из базы и сохраняю обратно, это протокол общения с хранилищем, без него в принципе невозможно обойтись. А вся логика находится в приложении, написана на языке программирования, разбита на классы и методы, которые соответствуют терминам предметной области, можно использовать средства переиспользования кода (наследование, композиция, трейты), подключать любые сторонние технологии (кеши, хранилища), запускать многопоточную обработку. Ничего из этого в SQL нет, либо довольно сложно использовать.
NitroJunkie
public function getLastPrice() { $this->hasOne(LastPrice::class, [['product_id', 'store_id'], ['id', 'store_id']]); }public function find() { $subQuery = parent::find()->select('LAST(price)') ->groupBy(['product_id', 'store_id']) ->orderBy(['date', 'id']); return (new Query())->select('*')->from($subQuery); }Вы же понимаете, что это обычные Join'ы и Group by, только в таком весьма вывернутом синтаксисе. И в нормальной жизни, если бы не SQL, так бы на PHP никто не писал.
С наследованием, вы уверены что joinWith работает работает с абстрактными методами. Ну и со всякими классами в стиле LastPrice, будут проблемы, я в статье приводил пример когда писал про наследование в оракл
michael_vostrikov
Нет, это обычные foreign key в синтаксисе ORM. В join их превращает ORM, может в отдельный запрос в IN превратить, может использовать эту информацию при выводе интерфейса, или при обработке запроса к API. Это просто связи между сущностями, отражение связей предметной области.
groupBy да, это часть запроса, но без него нельзя получить данные, с которыми работает логика. И как я уже сказал, он там нужен только потому что вы сделали такую таблицу.
Еще раз, PHP это не хранилище данных, и без обращений к хранилищу данных работать с данными принципиально нельзя. Если бы не SQL, писали бы на каком-нибудь другом синтаксисе, начиная с банального fread/fwrite по нужным смещениям.
joinWith и with просто вызывают функцию по имени, добавляя 'get' к аргументу. Если метод не будет реализован, будет соответствующая ошибка в рантайме.
Вы не приводили пример, вы сказали, что это почему-то суррогатные классы, хотя эта связь многие-ко-многим явно присутствует в бизнес-логике, что их почему-то надо наследовать, и что будут какие-то проблемы. Тем более что LastPrice не является такой связью, соответственно и описанных там проблем с ней не будет. Тем более что LastPrice есть у вас в системе в виде представления, и непонятно, почему в приложении где никаких представлений нет оно не может быть в виде класса, как и любая другая таблица. Тем более что я бы сделал без нее, но по другим причинам.
Наследование бизнес-сущностей это вообще отдельный вопрос. Я согласен, что это было бы неплохо, но для контроля типов в этом случае нужна особая поддержка на уровне языка, которая выходит за рамки данного обсуждения. В пределах текущих возможностей языков это крайне спорное решение, обычно можно сделать по-другому.
Veidt Автор
Foreign key логически и есть join, то есть таблица, ключевое поле и поле связывания.
Это SQL замаскированный под PHP. А тощая корова — не газель.
Я вообще не понимаю этих суррогатов многие-ко-многим. А если четыре ключа в таблице, то это многие-ко-многим-ко-многим-ко-многим?
Потому что логически такого объекта как последняя цена нет. Есть функция последняя цена от двух объектов (склада и товара).
Это всегда можно было делать по другому. С тех пор как появились If'ы. Но если все делать If'ами получается адский лапше-код.
michael_vostrikov
Нет. Логически foreign key это связь между сущностями. Это даже по последнему слову в вашей фразе заметно. Оно может использоваться для присоединения, а может не использоваться. Для контроля целостности ключей например используется.
Нет. Никто ничего не маскирует, запрос это явное обращение к хранилищу. Ну невозможно обрабатывать данные, не имея данных. Значит их надо как-то получить. Может через SQL, может через протокол MongoDB, может через URL-запрос из внешнего API. Логика их обработки останется та же самая.
Эм, связь многие-ко-многим это вообще-то один из стандартных терминов теории баз данных.
Ну как это нет, если вы его используете. Оно даже в вашей терминологии существительное. И это не техническая терминология, я терминология предметной области.
На всякий случай еще раз повторю — в приложении я сделал такой класс исключительно потому что у вас в базе есть такое представление. Мне он не нужен, я бы сделал по-другому. ORM это mapping, вот ваше представление мапится на этот класс, а строки из этого представления на объекты этого класса, так же как таблица products мапится на класс Product. В базе есть встроенные типы "таблица" и "представление", а в языке программирования для создания пользовательских типов данных нет ничего кроме классов. Если вы считаете, что это лишняя сущность, уберите ее из вашей архитектуры, тогда и в приложении ее не будет.
Так и не надо все делать if-ами, есть другие способы. Композиция называется. Связь один-к-одному вполне нормально работает.
Veidt Автор
Так и join это логически связь между таблицами (сущностями в вашей терминологии).
А вот тут можно поподробнее. Как это join'ы и group by в MongoDB, это вроде как «документная» база.
Да я знаю, и она очень дырявая на мой взгляд.
Под объектом я имею ввиду бизнес-объект. То, что явно создается при помощи new, имеет идентификатор (один) и т.п.
И как бы вы сделали?
Вообще основная и практически единственная функция наследования это использование в механизмах полиморфизма (именно полиморфизм это замена if, такой «модульный» if). И композиция вам тут никак не поможет.
michael_vostrikov
Нет. Джойн это соединение двух множеств по некоторому условию. Он может быть по foreign key, а может по другому условию. И наоборот, связь между сущностями есть, а никаких джойнов может не быть.
А причем тут именно join'ы и group by? Они мне сами по себе не нужны, мне нужны данные из хранилища. Я уже несколько раз повторил — это просто способ получения данных. Будет другое хранилище, будет другой протокол их получения.
Какая разница, дырявая она или нет. Термин "многие-ко-многим" обозначает определенный тип взаимосвязей между сущностями. Как правило при хранении данных в базе для такой связи требуется промежуточная таблица. Когда говорят этот термин, подразумевают эти особенности. Всё, причем тут какие-то суррогаты?
Вот есть у вас тип "Склад", и есть более конкретный тип "Магазин". Вы когда "Склад" создаете при помощи new, у вас создается экземпляр сущности "Магазин"? Вот так и тут, вы создаете объект, обозначающий изменение цены, и он же становится экземпляром более конкретного типа "Текущая цена". Это кстати частично связано с наследованием, я там даже хотел унаследовать от класса Price, но потом решил попроще сделать.
Один идентификатор есть только у самостоятельных сущностей, у связей между ними идентификаторов как минимум 2, потому что у связи между чем-то и чем-то как минимум 2 конца. Связи это тоже полноценные понятия предметной области. Если вы будете заметать их под ковер, у вас могут быть проблемы с архитектурой. Возможно вы с ними не сталкивались, потому что в движке базы данных уже есть особые типы данных "таблица", "представление", и остальные, поэтому вам свои классы для них создавать не надо.
Класс Client, таблица client. Класс PhysicalClient с дополнительными полями и специфичным методами, таблица physical_client, в классе Client свойство physicalClient, которое может быть равно null. Там может будет пара if где-нибудь в начале обработки на сравнение с null, ну и всё.
Описывал тут, последний абзац.
Veidt Автор
Нет. Не создается. Это объект класса Магазин является объектом класса Склад. Но не наоборот.
Вот я про эти самостоятельные сущности и говорю. Это объекты и классы. А «несамостоятельные» сущности это — костыли ORM.
Их пара, когда у вас два класса. Будет больше, будет винегрет if'ов, как собственно и было до появления ООП.
То есть ручной материализацией (триггерами по сути). Кстати, а куда бы вы это поле последней цены добавили и как таблицу назвали?
michael_vostrikov
Откуда же он тогда взялся? Мы же объект "Склад" создавали.
Причем тут ORM, если связи есть и в вашем коде, и вообще в предметной области?
Не будет. Приводите примеры пожалуйста для своих утверждений.
Причем тут триггеры? Триггеры там ровно в той же мере, что и в вашем коде. Вы же откуда-то данные берете для вставки, и добавляете по какому-то событию. Таблица изменения цен никуда не девается, просто не используется для расчета текущей цены. Состояние отдельно, логи отдельно.
Это цена товара на складе? Вот в параметры этой сущности бы и добавил. Есть Product с характеристиками, есть Sku(product_id) на конкретном складе, вот у этого Sku есть текущая цена на этом складе. Связь со складом у него даже в названии отражена. Никакого перелопачивания миллионов данных по всем складам не требуется.
michael_vostrikov
Какой же он суррогатный, если у вас специальное представление для него написано? Он там присутствует только потому что у вас в требованиях крайне спорное решение с доставанием последней записи из истории изменений. Как я уже сказал, обычно это делается по-другому.
NitroJunkie
Суррогатный класс потому что последняя цена — это функция (от товара и склада), а не объект (объекты это товар и склад). А вы создаете по сути суррогатные объекты и суррогатные классы под них.
michael_vostrikov
Функция, которая возвращает что? Запись в таблице prices это тоже объект.
Veidt Автор
Возвращает число — цену.
Да, но это объект изменения цены — PriceChange, но никак не последняя цена — LastPrice.
michael_vostrikov
Цитата:
"текущая цена это представление — SELECT LAST (price ORDER BY date,id) FROM prices GROUP BY product, store"
Ну не LastPrice, а CurrentPrice, это ничего не меняет. У вас есть конкретный бизнес-термин, который обозначает конкретную бизнес-сущность. От того, что вы будете делать вид, что ее нет, она никуда не денется. А при разработке могут быть проблемы из-за недостаточной модели предметной области.
Veidt Автор
Так представление это и есть функция, точнее набор функций. Но в данном случае так как представление из одной функции, можно считать, что это и есть функция.
Тут вопрос что такое сущность. Если вы имеете ввиду под этим класс (для которого создаются объекты), то последняя цена — не класс.
michael_vostrikov
Функция это отображение 'A' в 'B'. Все строки множества 'B' это записи о текущей цене некоторого товара. После CREATE VIEW вы какое имя напишете для этого представления? Вот оно и будет в названии класса в приложении.
Инфологическая модель данных «Сущность-связь»
Entity–relationship model
Veidt Автор
Если определять сущность как в википедии, то это все что угодно. Количество в документе тоже тогда сущность. Как и ограничение, что остаток больше 0. То есть абсолютно бесполезное понятие.
У меня более конкретное определение объекта — то, что явно создается при помощи new, имеет идентификатор (один) и т.п.
michael_vostrikov
"independent existence" означает состояние и поведение, что означает необходимость определять объект как один и тот же в разные моменты изменения поведения. Если мы хотим выделять количество как один и тот же объект в разные моменты времени, то да, это будет экземпляр сущности, а его тип сущностью. В соответствующих предметных областях выделяют такие классы. Например в каких-нибудь анализаторах кода будет объект, соответствующий интовой переменной в анализируемом коде.
У количества в документе мы состояние и поведение не выделяем, поэтому оно не сущность. А у документа выделяем, поэтому оно сущность.
Ну вы же уточняли, что я подразумеваю, я подразумеваю общепринятое определение. То, что вы используете другое определение, не делает мои рассуждения неправильными.
Если общепринятое определение вам не подходит, вам надо создать полностью свою непротиворечивую систему терминов и предупреждать об этом, чтобы не вводить в заблуждение собеседников. Например вам нужно дать название тому, что создается при помощи new, но имеет 2 поля для идентификатора, пояснить, откуда в логической предметной области взялся какой-то оператор new, либо не использовать термины "сущность" и "объект" вне контекста языка программирования, и т.д.
Veidt Автор
Не понимаю в чем разница количества в документе и цены товара на складе. Кроме того что первый показатель от одного объекта, а второй от двух.
Мое соответствует классическому ООП. Там есть классы и их можно делать new. Все остальное методы / процедуры.
То что создается при помощи new имеет идентификатор — в C++ это адрес в памяти, в java внутренний id и т.п… А 2 поля идентификатора — это костыли ORM.
А вообще надо про это статью написать. В смысле Сущность vs Класс.
michael_vostrikov
Не знаю, это у вас надо спросить, зачем вы сделали отдельную таблицу с изменением этой цены, да еще и отдельное представление по этой таблице. И почему для количества не сделали.
И-и? Вот у нас в приложении не было строки из представления last_prices в базе данных, а вот появилась. Какой операцией она создается в оперативной памяти?
Да причем тут ORM-то?) У вас в коде нет ORM, а таблицы с двумя идентификаторами есть.
michael_vostrikov
Сущность с двумя идентификаторами это связь, ее существование автоматически вытекает из существования нескольких сущностей. Если у нас есть 2 сущности, мы можем создать между ними связь. Будет ли она отражать реальную связь из предметной области, это другой вопрос.
Более того. Обычные связи один-ко-многим типа product.category_id, это частный случай связи с отдельной таблицей. И их точно так же можно моделировать отдельными таблицами с двумя столбцами, вообще все связи можно так моделировать, задавая соответствущие ограничения на столбцы. Просто так никто не делает по техническим причинам, просто помещают в ту таблицу, которая со стороны "много", и на другом конце связи получается всегда "один" объект. А для связи многие-ко-многим так упростить не получится.
По поводу C++. Покажите, как будет выглядеть структура классов, где есть авторы, книги, и их взаимосвязь, без работы с БД. Естественно с возможностью получить все книги конкретного автора и всех авторов конкретной книги.
Veidt Автор
Проблемы и альтернативы это разные вещи. Хотя в статье как раз есть много и про альтернативы (и как это сделано в lsFusion).
Пока писал уже ответили на остальное.
os9
О проталкивании условий в подзапрос — у меня работает (pg 11), если изменить запрос, чтоб использовался индекс:
то есть, оно в принципе есть.
А как оно должно в вашем запросе работать, какой предикат протолкнется в подзапрос, если условие по имени продукта, а подзапрос про имена продуктов не знает?
Вот для сравнения запрос с lateral и план, работает только по отфильтрованным строкам.
Veidt Автор
Так она не join протолкнула, а значение. То есть PPD там есть, JPPD нет.
Я в общем то в статье описал как это делают другие субд. Но по сути да, они вставляют что-то вроде lateral, как вы и написали.
Что кстати весьма забавно, что lateral PostgreSQL поддерживает, а JPPD нет.
Veidt Автор
Тут фишка видимо в том, что lateral это гарантировано nested loop join.
То есть вот такой запрос:
Дает вот такой план:
А его то как раз эффективнее вот так выполнять:
То есть PostgreSQL не принимает никакого решения, а что сказали то и делаю.
vsespb
Да есть пример в документации, где описано зачем
www.postgresql.org/docs/10/tutorial-inheritance.html
Храните базовую таблицу document. С полем amount например. От неё наследуйте receipt и shipment. В них будут уникальные поля для этих типов документов.
Так что всё делается, только «наоборот». И делается правильнее. т.к. БД это про хранение, и нечего делать «интерфейсы» выдавая одни поля за другие.
Veidt Автор
Так можно делать только с первичными данными (то есть с таблицами). Запросы (то есть VIEW) в реализацию добавлять нельзя (в смысле что VIEW наследовать от таблиц / друг от друга). То есть это как если бы вам в классическом ООП дали бы abstract, и сказали что в реализации можно только return field делать.
Плюс так можно делать при индуктивном задании логики. При дедуктивном, когда создаются таблицы со своими именами полей, а потом функционал надо обобщить в абстрактный так тоже не сработает.
Но вообще надо было конечно в примере GROUP BY написать или что-то по сложнее, чтобы вопросов не было.
vsespb
Ну может это для кого-то недостаток. Но вы пишите " зачем в PostgreSQL наследование вообще добавляли — неясно."
Хотя тут как раз наследование — отличная и незаменимая фича.
Есть люди, которые БД используют не так хардкорно. Может даже используют ORM. И их большинство.
View им в этом не нужны. А эта фича — как раз возможность хранить данные с наследованием. И именно структурированное хранение данных первичная фича базы. View же для тех, кто пытается и бизнеслогику реализовать на SQL, что часто тупиковый путь.
Veidt Автор
Ну об этом в общем-то и статья.
vsespb
… ну и возвращаясь к 1му моему сообщению в этом треде — ваше замечание «зачем в PostgreSQL наследование вообще добавляли — неясно.» — несправедливо.
Veidt Автор
Я имел ввиду, что ок наследование первичных данных (таблиц) вы сделаете, а что с наследованием вычислений (когда не просто return field) предполагается надо делать?
VolCh
Вот с ORM как раз очень часто view нужны для статистики.
mad_nazgul
ORM — ЗЛО :-)
Я понимаю использование ORM для простейших CRUD операций, это как-то оправдано.
То для отчетов, статистики использовать ORM?!
Как минимум это не удобно.
alexxz
С тезисами статьи про особенности SQL я более или менее согласен. Действительно есть такие операции с данными, которые не ложатся на синтаксис SQL достаточно гладко или красиво. Как правило, в таких случаях применяются заметно более общие механизмы, чем оптимальные. И потому СУБД предоставляют некоторые расширения синтаксиса, чтобы решать частные задачи более подходящими алгоритмами.
Но я категорически не согласен с выводами статьи про то, что коммерческие системы не соответствуют чему-то там. Нельзя обобщать два неудачных случайных примера до всех коммерческих систем.
Veidt Автор
Там в том то и дело что их больше чем 2. Я специально все собрал в одну статью, чтобы было видно что их гораздо больше, и что производители СУБД везде смогли накосячить.
Ну и я не говорил, что они чему-то не соответствуют. Тут вот какая ситуация. Смотрите мы когда делали платформу над SQL и компилировали запросы, к нам постоянно приходили разработчики и говорили вот мы тут такую логику нахерачили и у нас что-то тормозит. Ты им отвечаешь, смотрите вот видите запрос, видите план, видите как СУБД косячит. Знаете что они отвечали, а нам какая разница, чьи это проблемы, формируйте такие запросы, чтобы они выполнялись быстро. Понятно что это вопрос бренда. Будь у нас бренд Oracle или MS SQL мы бы всех посылали на три буквы и говорили бы, что «просто вы не так держите», крутитесь сами как хотите. Но нет, нам приходилось решать проблемы СУБД, мы конечно в конце концов все их решили, но сам факт почему это приходилось делать нам, хотя мы могли бы сфокусироваться именно на верхнем уровне, конечно дико раздражал. Собственно так и родилась эта статья.
alexxz
Просто ради интереса, вы не оставили разработчикам возможности "нахерачить" тормозящую логику? Или оставили, но теперь у вас есть возможность предлагать оптимизации для этих случаев, потому что внутри больше нет "чужих" систем?
PS. Где бы почитать про систему менее маркетинговые материалы, а более технические? Пошарился на сайте и что-то не нашёл, где прочитать спеку вашего внутреннего аналога SQL.
DAleby
alexxz
Спасибо, не сразу понял, что это и есть документация. Больше похоже на форум с сообщением от 2012 года. Еще приметил статейку с примерами в блоге компании. Почитаю 8)
LeshaLS
Вот тут еще можете посмотреть простые примеры.
DAleby
PerlPower
Имхо слишком DBA-ориентировано все написано. Часто проблемы производительности решаются за счет внешних инструментов и архитектурных подходов.
1) Большой кэш для предвычисленных затратных вещей на Redis. Обслуживание кэша не на SQL, а на прикладном языке.
2) Запуск длительных запросов в виде отдельных скриптов на других языках. Добавление прогрессбаров в конце концов.
3) Решение проблемы наследования на уровне ORM — в PHP в Doctrine, точно можно делать красивое наследование. Затем же из PHP работать с сущностями и построитель запросов ORM сам соберет данные из нужных таблиц.
SQL не идеален, но он уже давно под капотом у других языков. И в целом системы, где используется деревянный SQL, сгенерированный ORM или неискушенными в ORM программистами легче поддерживать, чем базу со вьюшками, хранимками и прочими радостями.
Veidt Автор
ACID'а не будет, то есть подойдет только для аналитики по большому счету, во всяком случае в бизнес-приложениях.
Тоже самое что и в пункте 1). Хотя во многих случаях действительно помогает (в lsFusion это асинхронные события).
Там в ORM те же и проблемы что в Oracle будут (в статье есть), то есть N+1 и непонятно что делать с таблицами скажем с 3 ключами.
Он все же обычно не под капотом, а сбоку. То есть у большинства других языков есть трансляторы в SQL, типа как в LINQ, Hibernate(HQL) или 1С.
Но и на голом SQL где очень важна производительность (банки, финансы, ритейл), тоже много чего пишут. Откуда по вашему у Oracle оборот 10млрд, не как у подложки же под SQL.
arturpanteleev
Я с опытом понял лично для себя одну вещь — реляционные хранилища, на многих своих проектах я использовал скорее по тому что «так принято» а потом по привычке. На самом деле если нам не нужна жёская нормализация(а она почти никогда и не нужна) обычно гораздо лучше подойдёт какое-нибудь noSQL хранилище — будет более дешевым, удобным в разработке и простым в эксплуатации решением.
Veidt Автор
Это обычно если логика достаточно простая (то есть немного сложнее CRUD). Если вам нужно делать группировки, композиции, разбиения, упорядочивания и у вас будут десятки таких показателей на каждой форме, на noSQL это будет очень тяжело сделать.
Собственно в свое время было много холиваров на тему SQL vs noSQL, и, соответственно, много историй, когда людям с noSQL на SQL приходилось переходить из-за невозможности сделать простейший JOIN.
Kwisatz
По поводу JPPD в Postgre конечно нужно проверять, но гложат меня определенные сомнения. Я только вчера наблюдал план с большим количеством вложенных запросов, в котором выбирались только необходимые верхнему уровню данные
NitroJunkie
Там в начале есть параметры подключения к уже готовой баз, можете PgAdmin4 подключиться и сами проверить запрос из статьи. Хотя я тоже, когда начинал работать с Postgre, сначала не верил.
nApoBo3
Очень странное ощущение от статьи, вроде автор явно обладает высоким уровнем компетенции в базах данных и достаточно обширным опытом с различными продуктами. Но использует инструмент не по назначению, профессионально, но не для того.
Взять хоть задачу по пересчету остатков. Да ее можно решить и так. Но так делать можно только на очень низконагруженых системах. Даже на системе со средней нагрузкой это может породить проблемы. При это умалчивается, что в принципе в задаче пересчета остатков заложен конфликт, который приходится разрешать в любой системе. Или мы строго учитываем остатки, или быстро, при этом в быстро еще заложено или быстро пишем, или быстро читаем. Если нам нужно и строго и быстро, то необходимо вводить отдельную «подсистему», которая денормализует данные и будет работать именно с остатками. Но и тут можно придумать кейс при котором возникнут проблемы, которые на SQL не имеют качественного решения, это распределенное управление остатками, когда у вас множество источников остатков, часть с которых вообще могут быть вне вашего контроля( например системы поставщиков ), т.е. одну проблему, мы поменяли на другую, которая тоже рано или поздно потребует решения.
Veidt Автор
А что в вашем понятии низконагруженные системы? Системы класса ERP с террабайтными базами и почти тысячей одновременных пользователей это какие системы? Вот у меня сейчас перед глазами с пяток таких систем, использующих описанный в статье подход.
Но тут вопрос то в другом. Остаток вам все равно надо хранить и обновлять инкрементально. Иначе система просто ляжет из-за нарушения баланса чтения и записи. Весь вопрос, как это делать. Задать декларативно представление и сказать серверу — теперь это твои проблемы, крутись как хочешь. Или самому разработчику императивно руками обновлять соответствующую таблицу. Не говоря о том что поддержать все случаи изменений очень тяжело, а значит нужно прибегать к разным хакам вроде запретов и перепроведений, о чем я и писал в статье.
Вы про update conflict'ы? Ну так с ними то как раз SQL сервера более менее сами справляются. От разработчика нужно только перестарт транзакции поддержать (хотя это тоже не такая тривиальная задача). Но это перпендикулярно задаче материализации представлений.
Проблема в том что пользователю надо и то и другое. А дальше вопрос балансов. Грубо говоря пользователь обычно согласен лишние 500мс подождать на проведении документа, но чтобы все остальное летало.
Почему поменяли? Решили одну. Ну а вторую значительно упростили. Так, достаточно обновлять поле «остаток у поставщика» как первичные данные в своей таблице, а дальше уже внутри сервер включает свою магию с материализациями, событиями и т.п.
SergeyUstinov
Хорошая статья, действительно интересные вопросы рассматриваются.
OrmEugensson
по моему мнению, идея языка, компилирующегося в SQL — правильная идея. В статье описаны несколько проблем, которые встречаются регулярно. Ну и опциональная материализация, на самом деле, может решить очень много проблем с базой данных.
У меня есть практический вопрос. Предположим, у меня есть проект с бизнес логикой на MS SQL. Возможно ли 'вместо MS SQL' использовать lsFusion + PostgreSQL (только как бек енд сервер)?
Veidt Автор
Тут нюанс в том, что lsFusion работает на более высоком уровне абстрагирования — «функциональном». Теоретически его конечно можно было «опустить» вниз на реляционный, именно с целью подменять MS SQL и Oracle, но тогда растеряется куча других преимуществ, вроде логики представлений, и например явной типизации. Да и насильно «деградировать» это странновато (хотя с точки зрения market fit может и имеет смысл).
Вообще, как мне видится, у lsFusion есть два способа облегчить вход на рынок:
1. Подключить в качестве фронтенда популярные фреймворки вроде React и выйти на рынок чистой классической веб-разработки (как более мобильный рынок и восприимчивый к инновациям)
2. Сделать возможно подключаться к существующим БД: генерить код на lsFusion на основе структуры БД (тем самым давая логику представлений для существующей бд) + добавлять триггеры в БД для обеспечения инфраструктуры событий, материализаций, ограничений, агрегаций и т.п. То есть по сути заходить как как вирус постепенно пожирая старую логику на SQL и переводя ее на lsFusion. Эта задача как и первая тоже техническая и не такая уж и сложная.
Другое дело, что эти две задачи надо доделывать, в то же время текущий продукт уже достаточно цельный и мы пока попробуем прощупать рынок с ним. Сначала русскоязычный, но что важнее англоязычный. Второй возможно более мобильный и восприимчивый к инновациям (но туда непонятно точно как заходить), и только после того как оценим feedback на нем, будем решать что делать дальше и в каком направлении из верхних двух развиваться. Ну или вообще сфокусироваться на решениях (вроде как 1С выходил на рынок через решения для бухгалтерии, заходить через рынок FMCG розницы где у нас достаточно большой опыт и экспертиза и там на самом деле очень слабая конкуренция)
michael_vostrikov
Это очень правильная идея. Люди смогут взять существующий проект и сравнить реализацию одних и тех же задач. Возможно они не будут переделывать старый, но задумаются о том, чтобы на этой архитектуре сделать новый.
Veidt Автор
Там дело не только в сравнении. По сути lsFusion сможет работать параллельно. То есть можно сбоку добавлять свойства, действия, формы, события, ограничения в lsFusion, обращающиеся в том числе к старой логике, и работать практически бесшовно.
Технически это будет выглядеть как реализация FAST REFRESH в Oracle. Там на самом деле это как реализовано. Создаются таблицы Таблица$mlog (причем они именно что обычные таблицы, в частности видны в каталогах, в sql developer), и триггеры записывают туда старые значения при изменении. А затем в конце транзакции, генерятся запросы по обновлению таблиц представлений.
Соответственно здесь можно сделать также, lsFusion генерит триггеры для всех используемых таблиц и сохраняет изменения в Таблица$llog. Затем в конце каждой транзакции (тут надо смотреть есть ли такая точка входа у SQL серверов) вызывает цикл обработки событий lsFusion с «виртуальной» сессией изменений из этих $llog таблиц (тут тоже вопрос можно ли из sql сервера синхронно обратиться к внешнему серверу и по какому протоколу, но почти уверен что такая возможность есть). Соответственно lsFusion выполняет всю работу по обновлению материализаций, проверке ограничений (если ограничение нарушится то вся транзакция откатится с сообщением), обработке событий и т.п. При этом даже в старой логике можно спокойно обращаться к таблицам из lsFusion как родным (правда будет проблема что материализации обновляются в конце транзакции, и это нужно будет учитывать). Плюс lsFusion может создавать поля / обновлять данные в существующих таблицах. То есть интеграцию реально можно сделать практически бесшовной. И технически все выглядит достаточно просто.
Но это позволит обойти legacy только при «SQL-разработке». На рынке «ERP-разработки» (SAP/Axapta/1С) такой фокус не прокатит. И там придется идти напролом.