Как быстрее искать миллионы записей в таблице SQL?

У меня есть таблица SQL с миллионами доменных имен. Но теперь, когда я ищу, скажем

SELECT * 
  FROM tblDomainResults 
 WHERE domainName LIKE '%lifeis%'

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

Каков наилучший способ сохранить эти миллионы записей и легко получить доступ к этой информации за короткий промежуток времени?

До сих пор около 50 миллионов записей и 5 столбцов.

Ответы

Ответ 1

Скорее всего, вы попробовали традиционный индекс, который нельзя использовать для оптимизации запросов LIKE, если шаблон не начинается с фиксированной строки (например, "lifeis%" ).

Что вам нужно для вашего запроса, это полнотекстовый индекс. В настоящее время большинство СУБД поддерживают его.

Ответ 2

Полнотекстовое индексирование - это лучший и лучший вариант здесь - как это будет сделано, будет зависеть от используемой СУБД.

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

Ответ 3

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

Я бы рекомендовал вам изучить доступные сторонние решения - например Lucene и Sphinx Они будут лучше.

Ответ 4

Предполагая, что ваша таблица из 50 миллионов строк содержит дубликаты (возможно, это часть проблемы) и предполагая SQL Server (синтаксис может измениться, но концепция похожа на большинство RDBMS), другой вариант заключается в том, чтобы хранить домены в поиске таблица, например

CREATE TABLE dbo.Domains
(
    DomainID INT IDENTITY(1,1) PRIMARY KEY,
    DomainName VARCHAR(255) NOT NULL
);
CREATE UNIQUE INDEX dn ON dbo.Domains(DomainName);

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

SELECT * -- please specify column names
FROM dbo.tblDomainResults AS dr
INNER JOIN dbo.Domains AS d
ON dr.DomainID = d.DomainID
WHERE d.DomainName LIKE '%lifeis%';

Конечно, за исключением самых маленьких таблиц, это всегда поможет избежать предложений LIKE с помощью главного шаблона.

Ответ 5

Одна вещь, которую вы, возможно, захотите рассмотреть, это наличие отдельной поисковой системы для таких поисков. Например, вы можете использовать сервер SOLR (lucene) для поиска и получения идентификаторов записей, соответствующих вашему поиску, а затем извлечения данных из базы данных по идентификатору. Даже имея два разных вызова, это, скорее всего, ускорится.

Ответ 8

Индексы замедляются, когда им приходится искать поиск ( "поиск по закладкам" ), который сам индекс не содержит. Например, если ваш индекс имеет 2 столбца, идентификатор и NAME, но вы выбираете * (который состоит из 5 столбцов), база данных должна считывать индекс для первых двух столбцов, а затем искать другие 3 столбца в менее эффективная структура данных в другом месте.

В этом случае ваш индекс нельзя использовать из-за "как". Это похоже на то, чтобы не помещать какой-либо фильтр в запрос, он вообще пропустит индекс, так как он должен прочитать всю таблицу в любом случае, он будет делать именно это ( "сканирование таблицы" ). Существует порог (я думаю, около 35-50%, когда двигатель обычно переворачивается на это).

Короче говоря, маловероятно, что для производственного приложения вам понадобятся все 50 миллионов строк из БД, но если вы это сделаете... используйте машину с большим объемом памяти и попробуйте методы, которые хранят эти данные в памяти. Может быть, нет-SQL DB будет лучшим вариантом - mongoDB, couch DB, tokyo Cabinet. Такие вещи. Удачи!

Ответ 9

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

Ответ 10

В случае Lucene или любого другого распределенного поиска, как позаботиться, если основные данные будут изменены. В основном, если название отдела меняется, и мы пытаемся найти все записи сотрудников, которые соответствуют фразе "Pro".