SQL LIKE Производительность с использованием только шаблона (%) в качестве значения

Мне интересно, как будет выглядеть производительность запроса с использованием ключевого слова LIKE и шаблона в качестве значения по сравнению с отсутствием предложения where.

Рассмотрим предложение where, такое как "WHERE a LIKE" % ". Это будет соответствовать всем возможным значениям столбца" a". Как это сравнивается с отсутствием предложения where.

Причина, по которой я спрашиваю об этом, заключается в том, что у меня есть приложение, в котором есть некоторые поля, которые пользователь может указать значения для поиска. В некоторых случаях пользователю нужны все возможные результаты. В настоящее время я использую один запрос:

SELECT * FROM TableName WHERE a LIKE ? AND b LIKE ?

Значения "%" и "%" могут предоставляться для соответствия всем возможным значениям для a и или b. Это удобно, так как я могу использовать один и тот же запрос в своем приложении для этого. Интересно, для чего важны соображения производительности. Оптимизатор запросов уменьшает LIKE '%', чтобы просто соответствовать всем? Я понимаю, что, поскольку я использую именованный запрос (подготовленный оператор), это также может повлиять на ответ. Я понимаю, что ответ, вероятно, зависит от конкретной базы данных. Так конкретно, как это будет работать в Oracle, MS SQL Server и Derby.

Альтернативным подходом к этому будет использование 3 отдельных запросов на основе ввода пользователем подстановочного знака.

A - это подстановочный запрос:

SELECT * FROM TableName WHERE b LIKE ?

B - это подстановочный запрос:

SELECT * FROM TableName WHERE a LIKE ?

A и B - подстановочные знаки:

SELECT * FROM TableName

Нет подстановочных знаков:

SELECT * FROM TableName WHERE a LIKE ? AND b LIKE ?

Очевидно, что один запрос является самым простым и простым в обслуживании. Я бы предпочел использовать только один запрос, если производительность все равно будет хорошей.

Ответ 1

Я надеялся, что на это будет ответ учебника, но похоже, что он будет в значительной степени отличаться для разных типов баз данных. Большинство ответов показали, что я должен запустить тест, так что это именно то, что я сделал.

Мое приложение в основном предназначено для баз данных Derby, MS SQL и Oracle. Поскольку derby можно запускать встраиваемым и легко настроить, я сначала тестировал производительность. Результаты были неожиданными. Я тестировал наихудший сценарий на довольно большой таблице. Я провел тест 1000 раз и усреднил результаты.

Запрос 1:

SELECT * FROM TableName

Запрос 2 (со значениями a = "%" и b = "%" ):

SELECT * FROM TableName WHERE a LIKE ? AND b LIKE ?

Среднее время запроса 1:178 мс

Среднее время запроса 2: 181 мс

Таким образом, производительность в дерби почти одинакова между двумя запросами.

Ответ 2

SQL Server обычно видит

WHERE City LIKE 'A%'

и обрабатывать его как

WHERE City >= 'A' AND City < 'B'

... и счастливо используйте поиск индекса, если это необходимо. Я говорю "вообще", потому что я видел, что в некоторых случаях он не делает этого упрощения.

Если кто-то пытается сделать:

WHERE City LIKE '%ville'

... тогда поиск индекса будет практически невозможным.

Но что-то простое:

WHERE City LIKE '%'

будет считаться эквивалентным:

WHERE City IS NOT NULL

Ответ 3

Вы можете использовать любой анализ запросов, предлагаемый СУБД (например, EXPLAIN для MySQL, SET SHOWPLAN_ALL ON для MS SQL (или используйте один из другие методы), EXPLAIN PLAN FOR для Oracle), чтобы увидеть, как будет выполняться запрос.

Ответ 4

Любая СУБД, достойная ее соли, будет выделять предложения LIKE '%', прежде чем пытаться выполнить запрос. Я достаточно уверен, что видел DB2/z в своих планах выполнения.

Подготовленный оператор не должен иметь значения, поскольку он должен быть превращен в реальный SQL, прежде чем он попадет в механизм выполнения.

Но, как и во всех вопросах оптимизации, измерьте, не угадайте! Администраторы баз данных существуют потому, что они постоянно настраивают СУБД на основе фактических данных (которые меняются с течением времени). На минимальном уровне вам нужно время (и получить планы выполнения) для всех вариантов с соответствующими статическими данными, чтобы увидеть, есть ли разница.

Я знаю, что такие запросы, как:

select c from t where ((1 = 1) or (c = ?))

оптимизированы для удаления всего предложения where перед выполнением (по-прежнему в DB2), и, прежде чем вы спросите, конструкция полезна, когда вам нужно удалить эффект предложения where, но при этом сохранить заполнитель параметров (используя BIRT с Javascript для изменить запросы для подстановочных знаков)).

Ответ 5

Derby также предлагает инструменты для изучения фактического плана запросов, который использовался, поэтому вы можете запускать эксперименты с помощью Derby и просматривать план запросов, выбранный Derby. Вы можете запустить Derby с -Dderby.language.logQueryPlan = true, и Derby напишет план запроса на derby.log, или вы можете использовать средство RUNTIMESTATISTICS, как описано здесь: http://db.apache.org/derby/docs/10.5/tuning/ctundepth853133.html

Я не уверен, что Derby будет лишать A LIKE '%' раньше времени, но я также не думаю, что наличие этого предложения приведет к значительному замедлению скорости выполнения.

Мне было бы очень интересно увидеть фактический результат плана запроса, который вы получите в своей среде, с предложением и предложением A LIKE '%'.

Ответ 6

Oracle 10gR2, похоже, не выполняет особую оптимизацию для этой ситуации, но он признает, что LIKE '%' исключает нули.

create table like_test (col1)
as select cast(dbms_random.string('U',10) as varchar2(10))
from dual
connect by level <= 1000
/
insert into like_test values (null)
/
commit
/

exec dbms_stats.gather_table_stats(user,'like_test')

explain plan for
select count(*)
from   like_test
/
select plan_table_output from table(dbms_xplan.display)
/
explain plan for
select count(*)
from   like_test
where  col1 like '%'
/
select plan_table_output from table(dbms_xplan.display)
/
explain plan for
select count(*)
from   like_test
where  col1 is not null
/
select plan_table_output from table(dbms_xplan.display)
/

... предоставление...

Plan hash value: 3733279756

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| LIKE_TEST |  1001 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------

... и...

Plan hash value: 3733279756

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS FULL| LIKE_TEST |  1000 | 10000 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COL1" LIKE '%')

... и...

Plan hash value: 3733279756

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS FULL| LIKE_TEST |  1000 | 10000 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COL1" IS NOT NULL)

Обратите внимание на мощность (строки) в строке TABLE ACCESS FULL

Ответ 7

В зависимости от того, как предикат LIKE структурирован и в поле, которое вы тестируете, вам может потребоваться полное сканирование таблицы. Семантически "%" может означать полное сканирование таблицы, но Sql Server делает все виды оптимизации внутри запросов. Таким образом, возникает вопрос: оптимизирует ли Sql Server предикат LIKE, сформированный с помощью "%", и выбрасывает его из предложения WHERE?

Ответ 8

Один из аспектов, который, как мне кажется, отсутствует в обсуждении, заключается в том, что OP хочет использовать подготовленный оператор. В момент подготовки заявления база данных/оптимизатор не сможет выработать упрощения, о которых говорили другие, и поэтому не сможет оптимизировать a like '%', поскольку фактическое значение не будет известно во время подготовки.

Таким образом:

при использовании подготовленных операторов есть четыре различных доступных оператора (0, только a, только b, оба) и использовать соответствующий при необходимости
посмотрите, если вы получите лучшую производительность, если вы не используете подготовленный оператор, придерживаясь только одного утверждения (хотя тогда было бы довольно легко не включать "пустые" условия).

Ответ 9

Что делать, если столбец имеет ненулевое пустое значение? Вероятно, ваш запрос будет соответствовать.

Если это запрос для приложения реального мира, попробуйте использовать функции индексирования свободного текста большинства современных SQL-баз. Проблемы с производительностью станут незначительными.

Простая инструкция if если (A B) искать в b else (A) искать еще B поиск b еще сообщите пользователю, что они ничего не указали

тривиально поддерживать и становится намного легче понять, вместо того, чтобы делать предположения относительно оператора LIKE. Вероятно, вы собираетесь это сделать в пользовательском интерфейсе, когда вы показываете результаты поиска "Поиск найденного x" или "Поиск найденного A B..."

Ответ 10

Я не уверен в значении использования подготовленного оператора с параметрами, которые вы описываете. Причина в том, что вы можете обмануть оптимизатор запросов при подготовке плана выполнения, который будет полностью неправильным в зависимости от того, какой из параметров был "%".

Например, если оператор был подготовлен с планом выполнения с использованием индекса в столбце A, но параметр для столбца A оказался "%", вы можете столкнуться с низкой производительностью.

Ответ 11

a где предложение с "like" % '", поскольку единственный предикат будет вести себя точно так же, как и предложение where.