Как ускорить выбор SELECT.. LIKE запросов в MySQL на нескольких столбцах?

У меня есть таблица MySQL, для которой я часто встречаю запросы SELECT x, y, z FROM table WHERE x LIKE '%text%' OR y LIKE '%text%' OR z LIKE '%text%'. Может ли какой-либо индекс ускорить работу?

В таблице содержится несколько миллионов записей. Если есть что-то, что ускорит поиск, серьезно ли это повлияет на использование диска файлами базы данных и скоростью операторов INSERT и DELETE? (не выполняется UPDATE)

Обновить: быстро после публикации, я видел много информации и обсуждения о том, как LIKE используется в запросе; Я хотел бы указать, что решение должно использовать LIKE '%text%' (то есть текст, который я ищу, добавляется и добавляется с помощью группового символа%). База данных также должна быть локальной, по многим причинам, включая безопасность.

Ответ 1

Индекс не ускорит запрос, потому что для текстовых столбцов индексы работают, индексируя N символов, начиная с левого. Когда вы делаете LIKE '% text%', он не может использовать индекс, потому что перед текстом может быть переменное количество символов.

То, что вы должны делать, это не использовать такой запрос вообще. Вместо этого вы должны использовать что-то вроде FTS (Full Text Search), который MySQL поддерживает для таблиц MyISAM. Также очень легко сделать такую систему индексирования для таблиц, отличных от MyISAM, вам просто нужна отдельная таблица индексов, в которой вы храните слова и их соответствующие идентификаторы в фактической таблице.

Ответ 2

Индекс не поможет сопоставить текст с основным шаблоном, индекс можно использовать для:

LIKE 'text%'

Но я предполагаю, что это не отрежет. Для этого типа запроса вы действительно должны смотреть на полнотекстового поискового провайдера, если хотите масштабировать количество записей, которые вы можете искать. Мой предпочтительный поставщик Sphinx, очень полнофункциональный/быстрый и т.д. Lucene также заслуживает внимания. Полнотекстовый индекс в таблице MyISAM также будет работать, но в конечном итоге преследование MyISAM для любой базы данных, имеющей значительное количество записей, не является хорошей идеей.

Ответ 3

Индекс может не использоваться для ускорения запросов, в которых критерии поиска начинаются с шаблона:

LIKE '%text%'

Индекс может (и может быть, в зависимости от селективности) использоваться для поисковых терминов формы:

LIKE 'text%'

Ответ 4

Я бы добавил, что в некоторых случаях вы можете ускорить запрос, используя индекс вместе с like/rlike, если поле, которое вы просматриваете, часто пусто или содержит что-то постоянное.

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

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

SELECT * FROM objects WHERE tags RLIKE("((^|,)tag(,|$))" AND tags!=''

Если у вас есть индекс в тегах, вы увидите, что он используется для ограничения поиска строк.

Ответ 5

Возможно, вы можете попробовать обновить mysql5.1 до mysql5.7.

У меня около 70 000 записей. И запустите следующий SQL:

select * from comics where name like '%test%';

В mysql5.1 требуется 2000 мс. И он занимает 200 мс в mysql5.7 или mysql5.6.

Ответ 6

Другая альтернатива, чтобы избежать полного сканирования таблицы, - это выбрать подстроки и проверить их в операторе using:

SELECT 
    al3.article_number,
    SUBSTR(al3.article_number, 2, 3) AS art_nr_substr,
    SUBSTR(al3.article_number, 1, 3) AS art_nr_substr2,
    al1.*
FROM
    t1 al1 
    INNER JOIN t2 al2 ON al2.t1_id = al1.id
    INNER JOIN t3 al3 ON al3.id = al2.t3_id
WHERE
    al1.created_at > '2018-05-29'
HAVING 
    (art_nr_substr = "FLA" OR art_nr_substr = 'VKV' OR art_nr_subst2 = 'PBR');

Ответ 7

По-другому:

Вы можете поддерживать вычисляемые столбцы с этими строками REVERSEd и использовать

SELECT x, y, z FROM table WHERE x LIKE 'text%' OR y LIKE 'text%' OR z LIKE 'text%' OR xRev LIKE 'txet%' OR yRev LIKE 'txet%' OR zRev LIKE 'txet%'

Пример того, как ДОБАВИТЬ сохраненный постоянный столбец

ALTER TABLE table ADD COLUMN xRev VARCHAR(N) GENERATED ALWAYS AS REVERSE(x) stored;

а затем создать индексы на xRev, yRev и т.д.