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

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

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

select * from table where value like 'abc%' 

а также

 select * from table where value = 'abcdefghijklmn' 

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

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

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

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

правила использования индекса с LIKE не так похожи:

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

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

  • Если в ваших критериях фильтра используется LIKE, но с подстановочным знаком в начале (как в Name0 LIKE '% UTER), он гораздо менее вероятно использует индекс, но он по-прежнему может, по крайней мере, выполнять INDEX SCAN в полном или частичном диапазоне индекс.

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

(Также имейте в виду, что механизм SQL все еще не может использовать индекс так, как вы ожидаете, в зависимости от того, что еще происходит в вашем запросе и с какими таблицами вы присоединяетесь. Механизм SQL оставляет за собой право переписать запросите немного, чтобы получить данные таким образом, который, по его мнению, является наиболее эффективным и может включать в себя INDEX SCAN вместо INDEX SEEK)

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

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

 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 раз дороже, чем сравнение.

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

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

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

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

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

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

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

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

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

Итак, скажем, есть небольшие различия между = и LIKE . Но спрашивая, является ли один оператор или другой оператор «быстрее» в SQL, он спрашивает: «Что идет быстрее, красный автомобиль или синяя машина?». Вы должны задавать вопросы о размере двигателя и весу vechicle, а не о цвете … Чтобы подойти к вопросам оптимизации реляционных таблиц, место для поиска – это ваши индексы и ваши выражения в предложении WHERE (и другие предложения, но обычно это начинается с ГДЕ).

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

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

 where login like '12345678' 

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

введите описание изображения здесь

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

 where login ='600009' 

Используя «объяснять», я получаю:

введите описание изображения здесь

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

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

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

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

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

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

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

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

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