Полнотекстовые индексы для скорости, дизайн базы данных SQL

У меня десять миллионов записей. База данных 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 для каждого поля.

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

Результаты близки, но не обязательно точно такие же. По умолчанию ПОЛНОТЕКСТОВЫЙ поиск будет соответствовать только границам полных слов, так что «продажи» могут не соответствовать тексту со словом «продавец». Вы можете приспособиться к этому, но вы должны быть осторожны, и есть соображения производительности, которые могут сделать это не стоящим усилий.

Joel Coehoorn 29.03.2022 18:12

Джоэл прав насчет границ слов. В этом конкретном примере вы можете обойти проблему «продажи» и «продавец», выполнив поиск «продажи *». Но не может использовать ведущие подстановочные знаки в полнотекстовом формате. Таким образом, поиск MATCH() AGAINST ("man") не вернет «продавец», а LIKE '%man%' вернет.

Stephan 29.03.2022 18:26

Я уверен, что иногда эти различия являются улучшением. Например, поскольку в исходном посте предлагался запрос местоположения для texas, что, если вместо этого кто-то ищет kansas? Исходный запрос также будет соответствовать arkansas, что совсем не входит в намерения пользователя. Полнотекстовое совпадение в этом случае намного лучше.

Joel Coehoorn 29.03.2022 18:43

Насколько я помню, MySQL может использовать только один индекс для каждой ссылки на таблицу в заданном запросе, и это относится как к полнотекстовым индексам, так и к обычным индексам. Таким образом, вы обнаружите, что только одно из ваших условий MATCH() AGAINST() использует индекс для сокращения набора совпадающих строк. Другие условия должны будут проверять каждую строку вручную. По крайней мере, набор проверяемых строк будет уменьшен на один поиск по полнотекстовому индексу.

Bill Karwin 29.03.2022 19:45

@BillKarwin Да, именно этого я и боялся. Пожалуйста, предоставьте больше информации.

Ned Hulton 29.03.2022 20:21
AGAINST("sales*") найдёт "продавца". Но Fulltext обрабатывает только подстановочный знак тянущийся.
Rick James 29.03.2022 21:03
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
1
6
45
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Как упоминалось в моем комментарии выше, если вы индексируете каждый из трех столбцов отдельно, запрос 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 указывает, что будет использоваться полнотекстовый индекс.

Красивое решение. Вы уверены, что это сработает? Он обязательно сделает матч первым, а лайки вторым?

Ned Hulton 30.03.2022 04:04

Используйте EXPLAIN, чтобы убедиться, что он будет использовать индекс.

Bill Karwin 30.03.2022 04:06

Другие вопросы по теме