IN vs OR в предложении SQL WHERE
При работе с большими базами данных, которая работает лучше, IN или же OR в SQL Where-clause?
Есть ли разница в том, как они выполняются?
8 ответов
Я предполагаю, что вы хотите знать разницу в производительности между следующими:
WHERE foo IN ('a', 'b', 'c')
WHERE foo = 'a' OR foo = 'b' OR foo = 'c'
Согласно инструкции для MySQL, если значения постоянны IN сортирует список, а затем использует бинарный поиск. Я полагаю, что OR оценивает их один за другим в произвольном порядке. Так IN быстрее в некоторых обстоятельствах.
Лучший способ узнать это - профилировать обе базы данных с вашими конкретными данными, чтобы увидеть, что быстрее.
Я пробовал оба на MySQL с 1000000 строк. Когда столбец индексируется, нет заметной разницы в производительности - оба практически мгновенные. Когда столбец не проиндексирован, я получил следующие результаты:
SELECT COUNT(*) FROM t_inner WHERE val IN (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000);
1 row fetched in 0.0032 (1.2679 seconds)
SELECT COUNT(*) FROM t_inner WHERE val = 1000 OR val = 2000 OR val = 3000 OR val = 4000 OR val = 5000 OR val = 6000 OR val = 7000 OR val = 8000 OR val = 9000;
1 row fetched in 0.0026 (1.7385 seconds)
Таким образом, в этом случае метод, использующий OR, примерно на 30% медленнее. Добавление большего количества терминов увеличивает разницу. Результаты могут отличаться для других баз данных и других данных.
Лучший способ узнать это - посмотреть на План выполнения.
Я попробовал это с Oracle, и это было точно так же.
CREATE TABLE performance_test AS ( SELECT * FROM dba_objects );
SELECT * FROM performance_test
WHERE object_name IN ('DBMS_STANDARD', 'DBMS_REGISTRY', 'DBMS_LOB' );
Даже если запрос использует INПлан выполнения говорит, что он использует OR:
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 163 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| PERFORMANCE_TEST | 8 | 1416 | 163 (2)| 00:00:02 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DBMS_LOB' OR "OBJECT_NAME"='DBMS_REGISTRY' OR
"OBJECT_NAME"='DBMS_STANDARD')
Оператору OR требуется гораздо более сложный процесс оценки, чем конструкции IN, поскольку он допускает множество условий, не только равных, как IN.
Вот пример того, что вы можете использовать с ИЛИ, но которые не совместимы с IN: больше. больше или равно, меньше, меньше или равно, LIKE, а некоторые больше похожи на оракула REGEXP_LIKE. Кроме того, учтите, что условия не всегда могут сравнивать одно и то же значение.
Для оптимизатора запросов проще управлять оператором IN, потому что это только конструкция, которая определяет оператор OR для нескольких условий с оператором = для одного и того же значения. Если вы используете оператор ИЛИ, оптимизатор может не учитывать, что вы всегда используете оператор = для одного и того же значения, и, если он не выполняет более глубокую и гораздо более сложную разработку, он, вероятно, может исключить, что может быть только = операторы для одинаковых значений во всех задействованных условиях с последующим исключением оптимизированных методов поиска, таких как уже упоминавшийся двоичный поиск.
[EDIT] Вероятно, оптимизатор может не реализовать оптимизированный процесс оценки IN, но это не исключает, что это может произойти один раз (с обновлением версии базы данных). Так что если вы используете оператор ИЛИ, то оптимизированная разработка не будет использоваться в вашем случае.
Я думаю, что оракул достаточно умен, чтобы преобразовать менее эффективный (какой бы он ни был) в другой. Поэтому я думаю, что ответ должен скорее зависеть от читабельности каждого (где я думаю, что IN явно побеждает)
Я добавлю информацию для PostgreSQL версии 11.8 (выпущена 14 мая 2020 г.).
может быть значительно быстрее. Например, таблица с ~ 23 млн строк.
Запрос сOR:
explain analyse select sum(mnozstvi_rozdil)
from product_erecept
where okres_nazev = 'Brno-město' or okres_nazev = 'Pardubice';
-- execution plan
Finalize Aggregate (cost=725977.36..725977.37 rows=1 width=32) (actual time=4536.796..4540.748 rows=1 loops=1)
-> Gather (cost=725977.14..725977.35 rows=2 width=32) (actual time=4535.010..4540.732 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=724977.14..724977.15 rows=1 width=32) (actual time=4519.338..4519.339 rows=1 loops=3)
-> Parallel Bitmap Heap Scan on product_erecept (cost=15589.71..724264.41 rows=285089 width=4) (actual time=135.832..4410.525 rows=230706 loops=3)
Recheck Cond: (((okres_nazev)::text = 'Brno-město'::text) OR ((okres_nazev)::text = 'Pardubice'::text))
Rows Removed by Index Recheck: 3857398
Heap Blocks: exact=11840 lossy=142202
-> BitmapOr (cost=15589.71..15589.71 rows=689131 width=0) (actual time=140.985..140.986 rows=0 loops=1)
-> Bitmap Index Scan on product_erecept_x_okres_nazev (cost=0.00..8797.61 rows=397606 width=0) (actual time=99.371..99.371 rows=397949 loops=1)
Index Cond: ((okres_nazev)::text = 'Brno-město'::text)
-> Bitmap Index Scan on product_erecept_x_okres_nazev (cost=0.00..6450.00 rows=291525 width=0) (actual time=41.612..41.612 rows=294170 loops=1)
Index Cond: ((okres_nazev)::text = 'Pardubice'::text)
Planning Time: 0.162 ms
Execution Time: 4540.829 ms
Запрос сIN:
explain analyse select sum(mnozstvi_rozdil)
from product_erecept
where okres_nazev in ('Brno-město', 'Pardubice');
-- execution plan
Aggregate (cost=593199.90..593199.91 rows=1 width=32) (actual time=855.706..855.707 rows=1 loops=1)
-> Index Scan using product_erecept_x_okres_nazev on product_erecept (cost=0.56..591477.07 rows=689131 width=4) (actual time=1.326..645.597 rows=692119 loops=1)
Index Cond: ((okres_nazev)::text = ANY ('{Brno-město,Pardubice}'::text[]))
Planning Time: 0.136 ms
Execution Time: 855.743 ms
OR имеет смысл (с точки зрения читабельности), когда сравнивается меньше значений.IN полезно особенно когда у вас есть динамический источник, с которым вы хотите сравнить значения.
Другой альтернативой является использование JOIN с временной таблицей.
Я не думаю, что производительность должна быть проблемой, если у вас есть необходимые индексы.
Я сделал запрос SQL в большом количестве ИЛИ (350). Postgres делает это 437.80мс.
Теперь используйте IN:
23.18ms
Даже если вы используете оператор IN, сервер MS SQL автоматически преобразует его в оператор OR. Если вы проанализируете планы выполнения, сможете это увидеть. Так что лучше использовать его ИЛИ, если его длинный список операторов IN. это, по крайней мере, сэкономит несколько наносекунд операции.