SQL: индекс FULLTEXT для скорости

Более десяти миллионов строк, мучительно медленно. В настоящее время используется 'LIKE' для подсчета количества просмотров:

SELECT COUNT(*) FROM `table` WHERE `job` LIKE '%sales%' AND `location` LIKE '%New York%'

Правильно ли я понимаю, что хочу:

(1) Применить индекс FULLTEXT к каждому полю

(2) Используйте оператор CONTAINS для увеличения скорости:

SELECT COUNT(*) FROM `table` WHERE contains('location', '"New York"') and contains ('job',"sales")

Будет ли это давать результаты, идентичные моему первоначальному запросу LIKE?

Будет ли это во много раз быстрее, с единственным недостатком, заключающимся в огромном увеличении размера хранилища базы данных?

Пример: я хочу подсчитать такие данные: «менеджер по продажам», «Баффало, Нью-Йорк».

Стандартным индексом реляционной базы данных является btree. B-деревья нельзя использовать для любого запроса, включающего подстановочный знак + слово, как в LIKE '%sales'. Индексы можно использовать для LIKE word%. Итак, то, что вы испытываете, - это полное сканирование таблицы. Полезно ознакомиться с EXPLAIN, чтобы вы могли самостоятельно просмотреть и понять эту проблему. Здесь можно использовать полнотекстовые индексы MySQL, но они имеют свои недостатки. Вам действительно нужно прочитать о том, как они работают, и о таких понятиях, как «стоп-слова» и минимальная длина слова.

gview 28.03.2022 06:04

Спасибо за ваш отзыв. Поэтому, если я хочу воспроизвести поиск «LIKE», но просто сделать его быстрее и избежать полного сканирования таблицы. Мне просто нужно создать FULLTEXT индекс для каждого из десяти полей, которые у меня есть. Возможно ли это, учитывая, что у меня 10 миллионов строк и что в некоторых полях более 100 слов/1000+ символов?

Ned Hulton 28.03.2022 06:29

Как уже писал Рик Джеймс, ваш поиск может использовать только один индекс. Если ваше требование просто не может использовать преимущества реляционной базы данных, возможно, вам лучше использовать одну из существующих полнотекстовых поисковых систем. Полнотекстовые индексы MySQL — это хорошо, но это скорее аддон, когда большая часть данных структурирована, но у вас есть исключение, которое вы все еще хотите найти. Elastic Search, Lucene/Solr и Sphinx могут подойти лучше. Другой вариант, на который стоит обратить внимание, — это MongoDB, которая представляет собой базу данных документов. Он имеет хороший полнотекстовый формат, но представляет собой базу данных документов, которая может лучше соответствовать исходным данным.

gview 28.03.2022 07:12

@gview Спасибо, но вы уверены, что у меня не может быть более одного индекса? То, что я делаю, очень стандартно: у меня есть десять полей с текстовыми данными, похожими на LinkedIn (работа, местоположение, страна, электронная почта...) У меня около десяти миллионов строк. То, что я хочу, это в основном функциональность в стиле LinkedIn, где вы можете искать по названию, стране и т. д. Любые дополнительные разъяснения очень ценятся.

Ned Hulton 28.03.2022 08:21

Не то чтобы у вас не могло быть более одного полнотекстового индекса, но для одного запроса mysql будет использовать один. Еще раз, проверьте EXPLAIN query

gview 29.03.2022 00:08
Освоение архитектуры микросервисов с 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
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
0
5
55
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Q: Правильно ли я думаю, что я хочу применить FULLTEXT индекс к каждому полю и заменить запрос чем-то вроде этого, чтобы увеличить скорость?

А: Да, вы правы при добавлении индекса для повышения производительности.


В: Правильно ли я думаю, что это приведет к результатам, идентичным моему запросу LIKE?

А: Я не уверен, потому что не смог найти ни одного пункта, связанного с CONTAINS в MySQL. Но, пожалуйста, обратитесь к это как к решению для полнотекстовых индексов.


В: Правильно ли я думаю, что это будет во много раз быстрее, а единственным недостатком будет огромное увеличение размера хранилища базы данных?

А: Ну, честно говоря, это не очень хорошая идея. Поскольку текст непредсказуем, не рекомендуется использовать текст в качестве индекса.


Вы можете уже выбрать свой разум независимо от моего мышления. Но я надеюсь, что вы найдете другую колонку с меньшими рисками и меньшими затратами на индексирование. Спасибо.

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

Добавьте этот индекс в свою таблицу:

FULLTEXT (location, job)

Используйте это предложение WHERE:

WHERE MATCH(location, job) AGAINST("sales manager new york")

Это будет работать значительно быстрее.

Более

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

Имейте в виду, что FULLTEXT не обрабатывает числа или неравенства. Он также не обрабатывает «короткие» слова или «стоп-слова».

Спасибо, мистер Джеймс, значит, вы говорите, что мне нужен один FULLTEXT-индекс для нескольких полей, а не десять FULLTEXT-индексов (у меня десять полей).

Ned Hulton 28.03.2022 05:52

@NedHulton - Да. MySQL будет [обычно] использовать только один индекс.

Rick James 28.03.2022 06:01

@NedHulton - И я добавил больше к своему ответу, так как вы Теперь подразумеваете, что для поиска есть более двух столбцов.

Rick James 28.03.2022 06:03

Какова структура вашей базы данных и какова природа данных. Зачем вам 10 полей, в каждом из которых есть фразы, которые нужно искать? См. мой предыдущий комментарий для вас о стандартных индексированных поисковых запросах LIKE, которые по-прежнему будут использовать индекс.

gview 28.03.2022 06:06

Имя: Джон Смит, Местонахождение: Саратога, Нью-Йорк, Страна: США, Должность: Продавец, Опыт: В течение двадцати лет я занимался тем и этим бла-бла-бла, я работал в Алабаме, Нью-Йорке и Флориде.

Ned Hulton 28.03.2022 06:09

@Rick-James Могу ли я иметь несколько индексов FULLTEXT, если я хочу искать «Нью-Йорк» в качестве местоположения и не допускать, чтобы «Журналист в New York Times» и т. д. И другие данные из поля «работа» загрязняли результаты?

Ned Hulton 28.03.2022 06:21

@NedHulton - Похоже, это необработанная стенограмма резюме в формате ascii? Похоже, это уже один столбец TEXT?

Rick James 28.03.2022 06:28

@NedHulton - Да, отдельные индексы. Но вы будем получаете загрязнение от других аномалий.

Rick James 28.03.2022 06:30

Нет, это не база данных резюме, это похоже на LinkedIn, с местоположением, страной, именем, адресом электронной почты, номером телефона, опытом. Все поля в VARCHAR, в основном короткие, с более длинным полем «опыт». Может быть, я смогу облегчить себе жизнь, убрав поле большого опыта и сделав его доступным только для чтения.

Ned Hulton 28.03.2022 06:31

"только чтение"????

Rick James 28.03.2022 06:33

Нет, я просто имел в виду, сделать доступными для поиска только короткие поля, такие как должность, местоположение, страна, и убрать большое поле «опыт», потому что в среднем оно составляет около 1000 символов. Возможно, создание FULLTEXT-индекса заняло бы вечность. Отображайте его для пользователей, когда другие поля совпадают, но удаляйте функцию поиска.

Ned Hulton 28.03.2022 06:35

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