Create: Update:
День 2324. #TipsAndTricks
Развенчиваем Миф Производительности SQL "Сначала Фильтр Потом JOIN"
В интернете часто можно встретить описание «трюка, повышающего производительность запросов в SQL», который звучит "Сначала Фильтр Потом JOIN". В нём утверждается, что вместо того, чтобы сначала объединять таблицы, а затем применять фильтр к результатам, нужно делать наоборот.
Например, вместо:
SELECT *
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 500;
использовать:
SELECT *
FROM (
SELECT * FROM orders WHERE total > 500
) o
JOIN users u ON u.id = o.user_id;
Смысл в том, что БД сначала уберёт ненужные данные из одной таблицы, а потом выполнит соединение меньшего объёма, экономя время и память. Звучит логично. Но дело в том, что для современных БД этот совет не имеет смысла.
Вот пример плана выполнения (EXPLAIN ANALYZE) обоих запросов в PostgreSQL над таблицами с 10000 записями в users и 5000000 в orders.
«Неоптимальный» план запроса:
Hash Join (cost=280.00..96321.92 rows=2480444 width=27) (actual time=1.014..641.202 rows=2499245 loops=1)
Hash Cond: (o.user_id = u.id)
-> Seq Scan on orders o (cost=0.00..89528.00 rows=2480444 width=14) (actual time=0.006..368.857 rows=2499245 loops=1)
Filter: (total > '500'::numeric)
Rows Removed by Filter: 2500755
-> Hash (cost=155.00..155.00 rows=10000 width=13) (actual time=0.998..0.999 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 577kB
-> Seq Scan on users u (cost=0.00..155.00 rows=10000 width=13) (actual time=0.002..0.341 rows=10000 loops=1)
Planning Time: 0.121 ms
Execution Time: 685.818 ms
«Оптимальный» план запроса:
Hash Join (cost=280.00..96321.92 rows=2480444 width=27) (actual time=1.019..640.613 rows=2499245 loops=1)
Hash Cond: (orders.user_id = u.id)
-> Seq Scan on orders (cost=0.00..89528.00 rows=2480444 width=14) (actual time=0.005..368.260 rows=2499245 loops=1)
Filter: (total > '500'::numeric)
Rows Removed by Filter: 2500755
-> Hash (cost=155.00..155.00 rows=10000 width=13) (actual time=1.004..1.005 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 577kB
-> Seq Scan on users u (cost=0.00..155.00 rows=10000 width=13) (actual time=0.003..0.348 rows=10000 loops=1)
Planning Time: 0.118 ms
Execution Time: 685.275 ms
Как видите, планы запросов идентичны, и «оптимизация» ничего не добилась.
Основные операции:
- Последовательное сканирование (Seq Scan) таблицы orders с применением фильтра;
- Последовательное сканирование таблицы users;
- Операция хеширования (Hash) меньшей таблицы (users);
- Хеш-соединение по user_id.
Оптимизаторы запросов умнее вас
Современные БД используют стоимостную оптимизацию. Оптимизатор имеет статистику о таблицах: количество строк, распределение данных, наличие индекса, селективность столбцов и т.п. – и использует её для оценки стоимости различных стратегий выполнения. Современные БД, такие как PostgreSQL, MySQL и SQL Server, уже автоматически выполняют «выталкивание предикатов» и переупорядочивание соединений. Т.е. оба запроса переписываются по одному и тому же оптимальному плану. Поэтому ручная оптимизация в подзапрос не ускоряет работу, а просто затрудняет чтение SQL-кода.
Итого
Пишите понятный, читаемый SQL. Позвольте оптимизатору делать свою работу. В непонятных ситуациях используйте EXPLAIN ANALYZE, чтобы понять, что на самом деле делает БД и действительно ли один запрос быстрее другого.
Источник: https://www.milanjovanovic.tech/blog/debunking-the-filter-early-join-later-sql-performance-myth
>>Click here to continue<<
.NET Разработчик