У меня десять миллионов записей. База данных MySQL выглядит следующим образом:
tagline: varchar(255)
location: varchar(80)
experience: text (normally about 600 characters)
country varchar(50)
Раньше я выполнял подобные запросы, что приводило к полному сканированию таблицы, что занимало слишком много времени:
SELECT COUNT(*) FROM `mytable` WHERE
`tagline` LIKE '%sales%'
AND `location` LIKE '%texas%'
AND `experience` LIKE '%software%'
Люди здесь говорят мне, что я должен применять индексы FULLTEXT к каждому полю. Они говорят мне, что я должен выполнять такие запросы:
SELECT COUNT(*) FROM `mytable` WHERE
MATCH(tagline) AGAINST("sales")
AND MATCH(location) AGAINST("texas")
AND MATCH(experience) AGAINST("software")
Я просто хочу подтвердить, что:
а) результаты должны быть одинаковыми
б) скорость будет выше
(c) единственным недостатком будет увеличенный размер базы данных и время, которое потребуется моей системе для реализации индексов FULLTEXT для каждого поля.
Пожалуйста, не обвиняйте меня в излишней конкретике. Просто привожу свою установку в качестве примера. Ответы принесут пользу всем, кому нужна функция поиска и есть много текстовых данных.
Джоэл прав насчет границ слов. В этом конкретном примере вы можете обойти проблему «продажи» и «продавец», выполнив поиск «продажи *». Но не может использовать ведущие подстановочные знаки в полнотекстовом формате. Таким образом, поиск MATCH() AGAINST ("man")
не вернет «продавец», а LIKE '%man%'
вернет.
Я уверен, что иногда эти различия являются улучшением. Например, поскольку в исходном посте предлагался запрос местоположения для texas
, что, если вместо этого кто-то ищет kansas
? Исходный запрос также будет соответствовать arkansas
, что совсем не входит в намерения пользователя. Полнотекстовое совпадение в этом случае намного лучше.
Насколько я помню, MySQL может использовать только один индекс для каждой ссылки на таблицу в заданном запросе, и это относится как к полнотекстовым индексам, так и к обычным индексам. Таким образом, вы обнаружите, что только одно из ваших условий MATCH() AGAINST()
использует индекс для сокращения набора совпадающих строк. Другие условия должны будут проверять каждую строку вручную. По крайней мере, набор проверяемых строк будет уменьшен на один поиск по полнотекстовому индексу.
@BillKarwin Да, именно этого я и боялся. Пожалуйста, предоставьте больше информации.
AGAINST("sales*")
найдёт "продавца". Но Fulltext обрабатывает только подстановочный знак тянущийся.
Как упоминалось в моем комментарии выше, если вы индексируете каждый из трех столбцов отдельно, запрос MySQL должен выбрать один индекс для каждой ссылки на таблицу. Он не может использовать все три индекса в одном запросе, если вы не используете разные ссылки на таблицы.
Вы должны создать один полнотекстовый индекс для всех трех столбцов:
ALTER TABLE mytable ADD FULLTEXT INDEX(tagline, location, experience);
Затем выполните поиск с одним предикатом:
SELECT COUNT(*)
FROM `mytable`
WHERE MATCH(tagline, location, experience)
AGAINST("+sales +texas +software" IN BOOLEAN MODE);
Но при этом теряется ассоциация того, какое ключевое слово находится в каждом столбце. Затем вы можете применить свои старые условия LIKE
для уточнения поиска, и им нужно будет только проверить строки, соответствующие полнотекстовому поиску.
SELECT COUNT(*)
FROM `mytable`
WHERE MATCH(tagline, location, experience)
AGAINST("+sales +texas +software" IN BOOLEAN MODE)
AND tagline LIKE '%sales%'
AND location LIKE '%texas%'
AND experience LIKE '%software%';
Дорогостоящая часть — это когда запрос должен выполнить сканирование таблицы для проверки миллионов строк. Если вы используете полнотекстовый индекс, чтобы сузить количество совпадений-кандидатов до нескольких строк, дополнительное сравнение строк с использованием LIKE
с небольшим подмножеством строк, вероятно, не будет слишком дорогостоящим.
Повторите ваш комментарий:
Вот что я получаю, когда запускаю EXPLAIN на тестовой таблице:
mysql> create table mytable (
id serial primary key,
tagline text,
location text,
experience text,
fulltext index(tagline, location, experience)
);
mysql> explain SELECT COUNT(*)
-> FROM `mytable`
-> WHERE MATCH(tagline, location, experience)
-> AGAINST("+sales +texas +software" IN BOOLEAN MODE)
-> AND tagline LIKE '%sales%'
-> AND location LIKE '%texas%'
-> AND experience LIKE '%software%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mytable
partitions: NULL
type: fulltext
possible_keys: tagline
key: tagline
key_len: 0
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Ft_hints: no_ranking
type: fulltext
указывает, что будет использоваться полнотекстовый индекс.
Красивое решение. Вы уверены, что это сработает? Он обязательно сделает матч первым, а лайки вторым?
Используйте EXPLAIN
, чтобы убедиться, что он будет использовать индекс.
Результаты близки, но не обязательно точно такие же. По умолчанию ПОЛНОТЕКСТОВЫЙ поиск будет соответствовать только границам полных слов, так что «продажи» могут не соответствовать тексту со словом «продавец». Вы можете приспособиться к этому, но вы должны быть осторожны, и есть соображения производительности, которые могут сделать это не стоящим усилий.