Подтвердить что ты не робот

Использование SQLServer для частичных слов

Мы запускаем поиск многих продуктов в огромном каталоге с частично согласованными штрих-кодами.

Мы начали с простого запроса

select * from products where barcode like '%2345%'

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

select * from products where contains(barcode, '2345')

Но, похоже, что contains не поддерживает поиск слов, которые частично содержат текст, но только полное совпадение слов или префикс. (Но в этом примере мы ищем "123456" ).

4b9b3361

ОТВЕТЫ

Ответ 1

Отвечаю: @DenisReznik был прав:)

ok, давайте посмотрим.
Я много лет работал со штрих-кодами и большими каталогами, и мне был интересен этот вопрос.

Итак, я сделал несколько тестов самостоятельно.

Я создал таблицу для хранения тестовых данных:

CREATE TABLE [like_test](
    [N] [int] NOT NULL PRIMARY KEY,
    [barcode] [varchar](40) NULL
)

Я знаю, что существует много типов штрих-кодов, некоторые содержат только числа, другие содержат также буквы, а другие могут быть даже очень сложными.

Предположим, что наш штрих-код является случайной строкой.
Я заполнил его 10 миллионами записей случайных альфа-цифровых данных:

insert into like_test
select count(*) from like_test)+n, REPLACE(convert(varchar(40), NEWID()), '-', '') barcode 
from FN_NUMBERS(10000000)

FN_NUMBERS() - это просто функция, которую я использую в своих БД (своего рода tally_table) чтобы быстро получить записи.

Я получил 10 миллионов записей:

N   barcode
1   1C333262C2D74E11B688281636FAF0FB
2   3680E11436FC4CBA826E684C0E96E365
3   7763D29BD09F48C58232C7D33551E6C9

Пусть объявит var для поиска:

declare @s varchar(20) = 'D34F15' -- random alfanumeric string

Возьмем базовую попытку с LIKE, чтобы сравнить результаты с:

select * from like_test where barcode like '%'[email protected]+'%'

На моей рабочей станции для полного сканирования с кластерным индексом требуется 24,4 секунды. Очень медленно.

SSMS предлагает добавить индекс в столбец штрих-кода:

CREATE NONCLUSTERED INDEX [ix_barcode] ON [like_test] ([barcode]) INCLUDE ([N])

500 Мб индекса, я повторю выбор, на этот раз 24,0 с для поиска без кластеризации индекса. Менее чем на 2% лучше, почти такой же результат. Очень далеко от 75%, предположительно SSMS. Мне кажется, этот индекс действительно не стоит. Возможно, мой SSD Samsung 840 имеет значение.
На данный момент активен индекс.

Попробуйте выполнить CHARINDEX:

select * from like_test where charindex(@s, barcode) > 0

На этот раз потребовалось 23,5 секунды, но на самом деле не намного лучше, чем LIKE.

Теперь позвольте проверить предложение @DenisReznik, что использование Binary Collation должно ускорить работу.

select * from like_test
where barcode collate Latin1_General_BIN like '%'[email protected]+'%' collate Latin1_General_BIN

WOW, похоже, работает! Это всего лишь 4,5 секунды! 5 раз лучше..
Итак, что относительно CHARINDEX и Collation toghether? Попробуем:

select * from like_test
where charindex(@s collate Latin1_General_BIN, barcode collate Latin1_General_BIN)>0

Unbelivable! 2,4 секунды, в 10 раз лучше..

Хорошо, до сих пор я понял, что CHARINDEX лучше, чем LIKE, и что Binary Collation лучше, чем обычная сортировка строк, поэтому теперь я буду продолжать только с CHARINDEX и Collation.

Теперь, можем ли мы сделать что-нибудь еще, чтобы получить еще лучшие результаты? Возможно, мы можем попытаться уменьшить наши очень длинные строки. Сканирование всегда является сканированием.

Сначала попробуйте, логическая строка, вырезанная с использованием SUBSTRING, фактически работает на штрих-кодах из 8 символов:

select * from like_test
where charindex(
        @s collate Latin1_General_BIN, 
        SUBSTRING(barcode, 12, 8) collate Latin1_General_BIN
      )>0

Fantastic! 1,8 секунды. Я пробовал как SUBSTRING(barcode, 1, 8) (глава строки), так и SUBSTRING(barcode, 12, 8) (середина строки) с одинаковыми результатами.

Затем я попытался физически уменьшить размер столбца штрих-кода, почти никакой разницы, чем использование SUBSTRING()

Наконец, я попытался сбросить индекс на столбец штрихкода и повторил ВСЕ тесты выше... Я был очень удивлен, получив почти одинаковые результаты, с очень небольшими различиями. Индекс работает на 3-5% лучше, но при стоимости 500 МБ дискового пространства и стоимости обслуживания, если каталог обновлен.

Естественно, что для прямого поиска ключа, такого как where barcode = @s с индексом, он занимает 20-50 миллисекунд, без индекса мы не можем получить меньше 1,1 сек с использованием синтаксиса Collation where barcode collate Latin1_General_BIN = @s collate Latin1_General_BIN

Это было интересно.
Надеюсь, это поможет

Ответ 2

Я часто использую charindex и так же часто встречаюсь в этой самой дискуссии.

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

http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex

Ответ 3

Хороший вариант для вашего дела - создание индекса FTS. Вот как это можно реализовать:

1) Создать таблицу Условия:

CREATE TABLE Terms
(
    Id int IDENTITY NOT NULL,
    Term varchar(21) NOT NULL,
    CONSTRAINT PK_TERMS PRIMARY KEY (Term),
    CONSTRAINT UK_TERMS_ID UNIQUE (Id)
)

Примечание: объявление индекса в определении таблицы является функцией 2014. Если у вас более низкая версия, просто выведите ее из инструкции CREATE TABLE и создайте отдельно.

2) Отрежьте штрих-коды до граммов и сохраните каждый из них в таблице "Условия". Например: barcode = '123456', для вашей таблицы должно быть 6 строк: '123456', '23456', '3456', '456', '56', '6'.

3) Создать таблицу BarcodeIndex:

CREATE TABLE BarcodesIndex
(
    TermId int NOT NULL,
    BarcodeId int NOT NULL,
    CONSTRAINT PK_BARCODESINDEX PRIMARY KEY (TermId, BarcodeId),
    CONSTRAINT FK_BARCODESINDEX_TERMID FOREIGN KEY (TermId) REFERENCES Terms (Id),
    CONSTRAINT FK_BARCODESINDEX_BARCODEID FOREIGN KEY (BarcodeId) REFERENCES Barcodes (Id)
)

4) Сохраните пару (TermId, BarcodeId) для штрих-кода в таблице BarcodeIndex. TermId был сгенерирован на втором этапе или существует в таблице "Условия". BarcodeId - это идентификатор штрих-кода, который хранится в штрих-кодах (или любом другом имени, которое вы используете для него). Для каждого из штрих-кодов в таблице BarcodeIndex должно быть 6 строк.

5) Выберите штрих-коды по их частям, используя следующий запрос:

SELECT b.* FROM Terms t
INNER JOIN BarcodesIndex bi
    ON t.Id = bi.TermId
INNER JOIN Barcodes b
    ON bi.BarcodeId = b.Id
WHERE t.Term LIKE 'SomeBarcodePart%'

Это решение заставляет все похожие части штрих-кодов храниться поблизости, поэтому SQL Server будет использовать стратегию сканирования диапазона индексов для извлечения данных из таблицы условий. Условия в таблице "Условия" должны быть уникальными, чтобы сделать эту таблицу как можно меньшей. Это можно сделать в логике приложения: проверить существование → вставить новое, если термин не существует. Или, установив опцию IGNORE_DUP_KEY для кластеризованного индекса таблицы "Условия". Таблица BarcodesIndex используется для ссылки на термины и штрих-коды.

Обратите внимание, что внешние ключи и ограничения в этом решении являются точками рассмотрения. Лично я предпочитаю иметь внешние ключи, пока они не причинят мне боль.

Ответ 4

После дальнейшего тестирования и чтения и общения с @DenisReznik я думаю, что лучшим вариантом может быть добавление виртуальных столбцов в таблицу штрих-кодов для разделения штрих-кода.

Нам нужны только столбцы для стартовых позиций со 2-го по 4-й, потому что для 1-го мы будем использовать оригинальный столбец штрих-кода, а последний, по-моему, не полезен вообще (какое частичное соответствие 1 char на 6, когда 60 % записей будет соответствовать?):

CREATE TABLE [like_test](
    [N] [int] NOT NULL PRIMARY KEY,
    [barcode] [varchar](6) NOT NULL,
    [BC2]  AS (substring([BARCODE],(2),(5))),
    [BC3]  AS (substring([BARCODE],(3),(4))),
    [BC4]  AS (substring([BARCODE],(4),(3))),
    [BC5]  AS (substring([BARCODE],(5),(2)))
)

а затем добавить индексы в эти виртуальные столбцы:

CREATE NONCLUSTERED INDEX [IX_BC2] ON [like_test2] ([BC2]);
CREATE NONCLUSTERED INDEX [IX_BC3] ON [like_test2] ([BC3]);
CREATE NONCLUSTERED INDEX [IX_BC4] ON [like_test2] ([BC4]);
CREATE NONCLUSTERED INDEX [IX_BC5] ON [like_test2] ([BC5]);
CREATE NONCLUSTERED INDEX [IX_BC6] ON [like_test2] ([barcode]);

теперь мы можем просто найти частичные совпадения с этим запросом

declare @s varchar(40) 
declare @l int

set @s = '654'
set @l = LEN(@s)

select N from like_test 
where 1=0
OR ((barcode = @s) and (@l=6)) -- to match full code (rem if not needed)
OR ((barcode like @s+'%') and (@l<6)) -- to match strings up to 5 chars from beginning
or ((BC2 like @s+'%') and (@l<6)) -- to match strings up to 5 chars from 2nd position
or ((BC3 like @s+'%') and (@l<5)) -- to match strings up to 4 chars from 3rd position
or ((BC4 like @s+'%') and (@l<4)) -- to match strings up to 3 chars from 4th position
or ((BC5 like @s+'%') and (@l<3)) -- to match strings up to 2 chars from 5th position

это HELL быстро!

  • для строк поиска из 6 символов 15-20 миллисекунд (полный код)
  • для строк поиска из 5 символов 25 миллисекунд (20-80)
  • для строк поиска из 4 символов 50 миллисекунд (40-130)
  • для строк поиска из 3 символов 65 миллисекунд (50-150)
  • для строк поиска из 2 символов 200 миллисекунд (190-260)

В таблице не будет дополнительного пространства, но каждый будет занимать до 200 МБ (для 1 миллиона штрих-кодов)

ОПЛАТА ВНИМАНИЯ
Протестировано на Microsoft SQL Server Express (64-разрядная версия) и Microsoft SQL Server Enterprise (64-разрядная версия) оптимизатор последнего немного лучше, но главное отличие в том, что:

в экспресс-редакции вам нужно извлечь ТОЛЬКО первичный ключ при поиске в вашей строке, если вы добавите другие столбцы в SELECT, оптимизатор больше не будет использовать индексы, но он пойдет для полного кластеризованного индекса сканирование, чтобы вам понадобилось что-то вроде

;with
k as (-- extract only primary key
    select N from like_test
    where 1=0
    OR ((barcode = @s) and (@l=6))
    OR ((barcode like @s+'%') and (@l<6))
    or ((BC2 like @s+'%') and (@l<6))
    or ((BC3 like @s+'%') and (@l<5))
    or ((BC4 like @s+'%') and (@l<4))
    or ((BC5 like @s+'%') and (@l<3))
)
select N 
from like_test t
where exists (select 1 from k where k.n = t.n)

на стандартном (корпоративном) издании вы HAVE, чтобы перейти на

    select * from like_test -- take a look at the star
    where 1=0
    OR ((barcode = @s) and (@l=6))
    OR ((barcode like @s+'%') and (@l<6))
    or ((BC2 like @s+'%') and (@l<6))
    or ((BC3 like @s+'%') and (@l<5))
    or ((BC4 like @s+'%') and (@l<4))
    or ((BC5 like @s+'%') and (@l<3))

Ответ 5

Полнотекстовый текст предназначен для больших текстов, пусть тексты содержат более 100 символов. Вы можете использовать LIKE '% string%'. (Однако это зависит от того, как определяется столбец штрих-кода.) У вас есть индекс для штрих-кода? Если нет, то создайте его, и он улучшит ваш запрос.

Ответ 6

ОБНОВЛЕНО:

Мы знаем, что поиск FULL-TEXT может быть использован для следующего:

Полнотекстовый поиск - MSDN

  • Один или несколько конкретных слов или фраз (простой термин)
  • Слово или фраза, где слова начинаются с указанного текста (префиксный термин)
  • Флективные формы определенного слова (термин генерации)
  • Слово или фраза, близкие к другому слову или фразе (термин близости)
  • Синонимичные формы определенного слова (тезауруса)
  • Слова или фразы с использованием взвешенных значений (взвешенный термин)

Выполнены ли какие-либо из ваших запросов? Если вам нужно искать шаблоны, как вы описали, без согласованного шаблона (например, "1%" ), то, возможно, SQL-код не может использовать SARG.

  • Вы можете использовать операторы Boolean

Начиная с перспективы C++, доступ к B-Trees осуществляется из предзаказов, заказов и заказов после traversals и используется Boolean для поиска B-Tree. Обработанные намного быстрее, чем сравнение строк, boolean предлагает как минимум улучшенную производительность.

Мы видим это в следующих двух вариантах:

PATINDEX

  • Только если ваш столбец не является числовым, поскольку PATINDEX предназначен для строк.
  • Возвращает целое число (например, CHARINDEX), которое легче обрабатывать, чем строки.

CHARINDEX - это решение

  • CHARINDEX не имеет проблем с поиском INT и снова возвращает число.
  • Может потребоваться создание некоторых дополнительных случаев (т.е. первое число всегда игнорируется), но вы можете добавить их так: CHARINDEX('200', barcode) > 1.

Доказательство того, что я говорю, вернемся к старому [AdventureWorks2012].[Production].[TransactionHistory]. У нас есть TransactionID, который содержит количество элементов, которые мы хотим, и позволяет для развлечения предполагать, что вы хотите, чтобы каждый идентификатор транзакции имел 200 в конце.

-- WITH LIKE
SELECT TOP 1000 [TransactionID]
      ,[ProductID]
      ,[ReferenceOrderID]
      ,[ReferenceOrderLineID]
      ,[TransactionDate]
      ,[TransactionType]
      ,[Quantity]
      ,[ActualCost]
      ,[ModifiedDate]
  FROM [AdventureWorks2012].[Production].[TransactionHistory]
  WHERE TransactionID LIKE '%200'

-- WITH CHARINDEX(<delimiter>, <column>) > 3
SELECT TOP 1000 [TransactionID]
      ,[ProductID]
      ,[ReferenceOrderID]
      ,[ReferenceOrderLineID]
      ,[TransactionDate]
      ,[TransactionType]
      ,[Quantity]
      ,[ActualCost]
      ,[ModifiedDate]
  FROM [AdventureWorks2012].[Production].[TransactionHistory]
  WHERE CHARINDEX('200', TransactionID) > 3

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

Amazing Awesomeness

  • Ясно, что булевы и числа - это более быстрые сравнения.
  • LIKE использует сравнения строк, которые снова обрабатываются намного медленнее.

Я был немного удивлен величиной разницы, но основы были одинаковыми. Операторы Integers и Boolean быстрее всегда, чем сравнение строк.

Ответ 7

Вы не включаете множество ограничений, что означает, что вы хотите искать строку в строке - и если бы был оптимизирован индекс для поиска строки в строке, она была бы просто построена!

Другие вещи, которые затрудняют конкретный ответ:

  • Не ясно, что означают "огромные" и "слишком длинные".

  • Не понятно, как работает ваше приложение. Вы ищете в пакете, когда добавляете 1000 новых продуктов? Вы разрешаете пользователю вводить частичный штрих-код в поле поиска?

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

Ускорить некоторые запросы

У меня есть база данных с большим количеством номерных знаков; иногда офицер хочет обыскать последние 3-х значки тарелки. Чтобы поддержать это, я сохраняю номерной знак в обратном порядке, затем используйте LIKE ('ZYX%') для соответствия ABCXYZ. При выполнении поиска у них есть опция "содержит" поиск (например, у вас), который медленный, или вариант "Начало/Конец с", который является супер из-за индекса. Это может решить вашу проблему некоторое время (что может быть достаточно хорошим), особенно если это обычная потребность.

Параллельные запросы

Индекс работает, потому что он организует данные, индекс не может помочь со строкой в ​​строке, потому что нет организации. Скорость, по-видимому, является вашей целью оптимизации, поэтому вы можете хранить/запрашивать данные таким образом, чтобы они выполнялись параллельно. Пример: если требуется 10 секунд для последовательного поиска 10-миллионных строк, то с 10-параллельными процессами (так что процесс ищет 1 миллион) будет от 10 секунд до 1 секунды (kind'a-sort'a), Подумайте об этом как о масштабировании. Существуют различные варианты этого, в пределах вашего единственного экземпляра SQL (попробуйте разбиение данных) или на нескольких серверах SQL (если это опция).

БОНУС: Если вы не настроены на RAID, это может помочь с чтением, поскольку оно эффективно просматривается параллельно.

Уменьшить узкое место

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

Ответ 8

Сначала сделайте индекс в столбце, на котором вы должны поставить предложение where.

Во-вторых, для типа данных столбца, которые используются в where, делают их как Char вместо Varchar, который сохранит вам некоторое пространство, в таблице и в индексах, которые будут включать этот столбец. В столбце varchar (1) требуется еще один байт над char (1)

Выбирайте только количество столбцов, которые вы пытаетесь избежать *, укажите количество столбцов, которые вы хотите выбрать. Не пишите как

выберите * из продуктов

Вместо этого напишите как

 Select Col1, Col2 from products with (Nolock)