SQL 'like' vs '=' производительность

Этот вопрос обходит вокруг того, что мне интересно, но ответы не совсем решают его.

Казалось бы, в общем случае при использовании подстановочных знаков "=" быстрее, чем "нравится". Это, по-видимому, обычная мудрость. Однако давайте предположим, что у меня есть столбец, содержащий ограниченное число различных фиксированных, жестко закодированных идентификаторов varchar, и я хочу выбрать все строки, соответствующие одному из них:

select * from table where value like 'abc%'

select * from table where value = 'abcdefghijklmn'

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

Это предназначено как общий академический вопрос, поэтому не имеет значения, какая БД, но возникла с использованием SQL Server 2005.

Ответ 1

См. http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx

Цитата оттуда:

правила использования индекса с помощью LIKE так свободно:

Если в ваших критериях фильтра используется equals = и поле индексируется, то большинство вероятно, он будет использовать INDEX/CLUSTERED INDEX SEEK

Если в ваших критериях фильтра используется LIKE, без подстановочных знаков (например, если у вас есть параметра в веб-отчете, который МОЖЕТ имеют%, но вместо этого вы используете полный строка), это примерно так же, как # 1 для использования индекса. Увеличение стоимости почти ничего.

Если в ваших критериях фильтра используется LIKE, но с подстановочным знаком в начале (как в Name0 LIKE '% UTER') это намного меньше вероятно, использовать индекс, но он все еще может, по крайней мере, выполнить СКАНИРОВАНИЕ INDEX полный или частичный диапазон индекса.

ОДНАКО, если используются ваши критерии фильтрации LIKE, но начинается с STRING FIRST и имеет подстановочные знаки где-то ПОСЛЕ того, что (как в Name0 LIKE 'COMP% ER), затем SQL может просто использовать INDEX SEEK для быстрого найдите строки, которые имеют тот же самый первый стартовые символы, а затем посмотрите через эти строки для точного соответствия.
(Также имейте в виду, что механизм SQL все еще может не использовать индекс так вы ожидаете, в зависимости от того, что другое происходит в вашем запросе и к каким таблицам вы присоединяетесь. Механизм SQL оставляет за собой право перепишите свой запрос немного, чтобы получить данные, которые, по его мнению, эффективными и которые могут включать INDEX SCAN вместо INDEX SEEK)

Ответ 2

Это измеримая разница.

Выполните следующее:

Create Table #TempTester (id int, col1 varchar(20), value varchar(20))
go

INSERT INTO #TempTester (id, col1, value)
VALUES
(1, 'this is #1', 'abcdefghij')
GO

INSERT INTO #TempTester (id, col1, value)
VALUES
(2, 'this is #2', 'foob'),
(3, 'this is #3', 'abdefghic'),
(4, 'this is #4', 'other'),
(5, 'this is #5', 'zyx'),
(6, 'this is #6', 'zyx'),
(7, 'this is #7', 'zyx'),
(8, 'this is #8', 'klm'),
(9, 'this is #9', 'klm'),
(10, 'this is #10', 'zyx')
GO 10000

CREATE CLUSTERED INDEX ixId ON #TempTester(id)CREATE CLUSTERED INDEX ixId ON #TempTester(id)

CREATE NONCLUSTERED INDEX ixTesting ON #TempTester(value)

Тогда:

SET SHOWPLAN_XML ON

Тогда:

SELECT * FROM #TempTester WHERE value LIKE 'abc%'

SELECT * FROM #TempTester WHERE value = 'abcdefghij'

Итоговый план выполнения показывает, что стоимость первой операции, сравнение LIKE, примерно 10 раз дороже, чем сравнение =.

Если вы можете использовать сравнение =, сделайте это.

Ответ 3

Вы также должны помнить, что при использовании like некоторые sql-вкусы будут игнорировать индексы, и это убьет производительность. Это особенно верно, если вы не используете шаблон "начинается с", как ваш пример.

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

Как сказано, шаблон "начинается с" может и оптимизируется на сервере sql. Он будет использовать индекс таблицы. EF 4.0 по этой причине переключился на like для StartsWith.

Ответ 4

Если value неиндексировано, оба результата приводят к сканированию таблицы. Разница в производительности в этом сценарии будет незначительной.

Если value индексируется, как отмечает Даниил в своем комментарии, = приведет к поиску индекса, который является производительностью O (log N). LIKE (скорее всего, в зависимости от того, насколько он избирателен) приводит к частичной проверке индекса >= 'abc' и < 'abd', для чего потребуется больше усилий, чем =.

Обратите внимание, что я говорю о SQL Server здесь - не все СУБД будут хорошими с LIKE.

Ответ 5

Вы задаете неправильный вопрос. В базах данных не важна операционная производительность, всегда является SARGability выражения, а coverability общего запроса. Производительность самого оператора в значительной степени не имеет значения.

Итак, как сравнивать LIKE и = с точки зрения SARGability? LIKE, когда используется с выражением, которое не начинается с константы (например, когда используется LIKE '%something'), по определению является не-SARGabale. Но делает ли это = или LIKE 'something%' SARGable? Нет. Как и при любом вопросе о производительности SQL, ответ не связан с запросом текста, а с развернутой схемой. Это выражение может быть SARGable , если существует индекс для их удовлетворения.

Итак, правда, есть небольшие различия между = и LIKE. Но спрашивая, является ли один оператор или другой оператор "быстрее" в SQL, он спрашивает: "Что идет быстрее, красный автомобиль или синяя машина?". Вы должны задавать вопросы о размере двигателя и весу vechicle, а не о цвете... Чтобы подойти к вопросам оптимизации реляционных таблиц, место для поиска - это ваши индексы и ваши выражения strong > в предложении WHERE (и другие предложения, но обычно он начинается с WHERE).

Ответ 6

Личный пример с использованием mysql 5.5: у меня было внутреннее соединение между двумя таблицами, одна из 3 миллионов строк и одна из 10 тысяч строк.

При использовании подобной информации по индексу, указанному ниже (без подстановочных знаков), потребовалось около 30 секунд:

where login like '12345678'

используя "объяснять", я получаю:

При использовании '=' в том же запросе потребовалось около 0,1 секунды:

where login ='600009'

Используя 'explain', я получаю:

Как вы можете видеть, like полностью отменил поиск индекса, поэтому запрос занял в 300 раз больше времени.

Ответ 7

Возможно, вы смотрите Полнотекстовый поиск.

В отличие от полнотекстового поиска предикат LIKE Transact-SQL работает на характерные шаблоны. Кроме того, вы не можете использовать предикат LIKE для отформатированные двоичные данные. Кроме того, запрос LIKE к большому количество неструктурированных текстовых данных намного медленнее, чем эквивалент полнотекстовый запрос по тем же данным. Запрос LIKE против миллионов строк текстовых данных может занять несколько минут; тогда как полнотекстовый запрос может занимать всего несколько секунд или меньше по тем же данным, в зависимости от на число возвращаемых строк.

Ответ 8

Прежде всего,

они не всегда равны

    select 'Hello' from dual where 'Hello  ' like 'Hello';

    select 'Hello' from dual where 'Hello  ' =  'Hello';

когда вещи не всегда равны, говорить об их эффективности не так важно.

Если вы работаете с строками и только переменными char, вы можете говорить о производительности. Но не используйте, как и "=", как обычно взаимозаменяемые.

Как вы могли бы видеть во многих сообщениях (выше и на других вопросах), в случаях, когда они равны, производительность подобного медленнее из-за сопоставления шаблонов (сопоставление)