У меня есть база данных имен людей, которая имеет (в настоящее время) 35 миллионов строк. Я должен знать то, что является лучшим методом для того, чтобы быстро искать эти имена. Существующей системе (не разработанный мной), просто индексировали столбцы имени и фамилии и использует "КАК" запросы с дополнительной опцией использования SOUNDEX (хотя я не уверен, что это на самом деле используется очень). Производительность всегда была проблемой с этой системой, и поэтому в настоящее время поиски ограничены 200 результатами (который все еще занимает слишком много времени работать). Так, у меня есть несколько вопросов:
Только для ссылки, я использую Быстрый NHibernate для доступа к данным, таким образом, методы, что работа будет с этим, будут предпочтены. Я использую SQL Server 2008 в настоящее время.
РЕДАКТИРОВАНИЕ я хочу добавить, что я очень интересуюсь решениями, которые будут иметь дело с вещами как обычно имена с орфографической ошибкой, например, 'smythe', 'кузнецом', а также именами, например, 'tomas', 'thomas'.
План запросов
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER REFERENCES:([testdb].[dbo].[Test].[Id], [Expr1004]) OPTIMIZED WITH UNORDERED PREFETCH)
|--Hash Match(Inner Join, HASH:([testdb].[dbo].[Test].[Id])=([testdb].[dbo].[Test].[Id]))
| |--Bitmap(HASH:([testdb].[dbo].[Test].[Id]), DEFINE:([Bitmap1003]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([testdb].[dbo].[Test].[Id]))
| | |--Index Seek(OBJECT:([testdb].[dbo].[Test].[IX_Test_LastName]), SEEK:([testdb].[dbo].[Test].[LastName] >= 'WHITDþ' AND [testdb].[dbo].[Test].[LastName] < 'WHITF'), WHERE:([testdb].[dbo].[Test].[LastName] like 'WHITE%') ORDERED FORWARD)
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([testdb].[dbo].[Test].[Id]))
| |--Index Seek(OBJECT:([testdb].[dbo].[Test].[IX_Test_FirstName]), SEEK:([testdb].[dbo].[Test].[FirstName] >= 'THOMARþ' AND [testdb].[dbo].[Test].[FirstName] < 'THOMAT'), WHERE:([testdb].[dbo].[Test].[FirstName] like 'THOMAS%' AND PROBE([Bitmap1003],[testdb].[dbo].[Test].[Id],N'[IN ROW]')) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([testdb].[dbo].[Test].[PK__TEST__3214EC073B95D2F1]), SEEK:([testdb].[dbo].[Test].[Id]=[testdb].[dbo].[Test].[Id]) LOOKUP ORDERED FORWARD)
SQL для вышеупомянутого:
SELECT * FROM testdb.dbo.Test WHERE LastName LIKE 'WHITE%' AND FirstName LIKE 'THOMAS%'
На основе совета от Mitch я создал индекс как это:
CREATE INDEX IX_Test_Name_DOB
ON Test (LastName ASC, FirstName ASC, BirthDate ASC)
INCLUDE (and here I list the other columns)
Мои поиски теперь невероятно быстры для моего типичного поиска (в последний раз, во-первых, и дата рождения).
Зависит от того, как выглядят ваши запросы LIKE.
Если вы ищете LIKE '%abc%'
, то никакой индекс не может быть использован, тогда как при поиске LIKE 'abc%'
индекс может быть использован. Также, если индекс(ы) по имени и фамилии не "покрывает" запрос, то будет выполнен поиск по ключу (Bookmark Lookups), что значительно повлияет на производительность.
Регулярно ли перестраиваются ваши индексы?
Есть ли у вас пример плана запроса?
Обновление: Покрывающий индекс для запроса - это индекс, который может использоваться для выполнения критерия WHERE, а также содержит все столбцы, необходимые для выполнения остальной части запроса, например список столбцов SELECT.
Использование покрывающих индексов для повышения производительности запросов
Обновление: Даже если вы создадите составной индекс на (Lastname, Firstname)
(поскольку lastname должен быть более селективным), поиск всех остальных столбцов (список столбцов '*') все равно потребуется в кластерном индексе таблиц.
Мне не очень нравится саундекс. Я думаю, что новые итерации алгоритма стали лучше, но вы хэшируете каждое слово английского языка до довольно маленького хэша. Это имеет тенденцию генерировать тонну ложных совпадений с течением времени. Я читал, что metaphone и его преемник double metaphone лучше, но у меня нет прямого опыта работы с ними.
Митч довольно подробно описал like
, поэтому я не буду повторяться.
Если вы создадите индекс на столбцах имени и фамилии, то поиск по точному совпадению и поиск по префиксу с помощью LIKE станет молниеносным.
(В MySQL, "Индекс также может использоваться для сравнения LIKE, если аргумент LIKE является постоянной строкой, которая не начинается с символа подстановки". Я думаю, что в MS SQL действует аналогичное правило, но для уверенности проверьте документацию по MS SQL.)
Чтобы ускорить поиск в SoundEx, сохраните версию SoundEx имени и фамилии в новых столбцах и создайте индексы на эти столбцы.