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