Мы, разработчики, большую часть времени ведем борьбу с базами данных по поводу производительности. Нередко бывает так, что корень проблемы с производительностью глубоко укоренился в вашем запросе. Это судьба, с которой мы должны бороться каждый день. На этой неделе мне пришлось бороться с этим и у меня было несколько интересных результатов и новых впечатлений, о которых можно было поговорить. Именно тогда я открыл для себя волшебство, называемое полнотекстовым индексом MySQL.
Проблема
Когда вы работаете в небольшой компании, мы в конечном итоге начинаем работать в областях, которые не обязательно являются вашей специальностью, это я как разработчик, занимающийся базами данных. Я не администратор базы данных, и по этой причине я не знаю всех тонкостей такой базы данных, как MySQL. Когда возникает проблема, все помогают, высказывая свои предложения, проводя исследования и т. Д. На этот раз проблема заключалась в производительности функции поиска приложения.
Приводя аналогичный пример варианта использования, у нас есть функция поиска, которая ищет товары. Как и ожидалось, в продукте есть много полей, в которых вам нужно выполнить поиск, чтобы сделать его максимально точным, некоторые из них – название продукта, его описание, информационные поля, некоторые ключевые слова, название производителя и т.д. Только с этим описанием у нас может быть три таблицы:
- Продукты, не требующие объяснений
- Ключевые слова, в нем есть отдельная таблица, потому что мы можем использовать ее повторно для нескольких продуктов.
- Производитель, также не требующий пояснений.
Однако проблема здесь заключалась не в количестве соединений (их много, больше, чем тех, которые я описал в более простом примере выше), а в способе поиска полей для данного поискового ввода. Первоначально мы использовали функцию в базе данных, которая использовала регулярное выражение для поиска строк, полностью содержащих данные поиска, т.Е. Если ввод был “скидка”, в столбце должно быть все слово “скидка”, которое должно быть где-то приведено базой данных. Ладно, выглядит безобидно, пока это не так.
Я до сих пор не знаю почему, я думаю, что это было бы хорошей причиной для исследования и создания другого поста в другой раз (если у вас есть какие-либо идеи, почему, дайте мне знать!), Но после разделения каждой части запроса и тестирования всех из них мы пришли к выводу, что использование этого метода поиска значительно замедляло запрос, например, их запрос занимал много секунд в производственной среде, очень плохие новости.
Чтобы дать больше контекста, чтобы не выглядело так, будто это была большая ошибка с нашей стороны, система перестраивается, она работает уже много лет, и сейчас мы меняем макет и практически переписываем бэкэнд с помощью новых технологий и решений, но мы все еще используем ту же базу данных, и многие запросы еще не были затронуты. Кроме того, проблема появилась недавно, возможно, из-за возросшего трафика. Во время тестов и первых месяцев производства он не был обнаружен, поиск выполнялся достаточно хорошо, хотя у нас были планы ускорить его еще больше.
Решение
Решить проблему было не так просто из-за наших временных и бюджетных ограничений, было бы невозможно сделать что-то за пределами того, что у нас уже было в инфраструктуре, например, добавить еще одну базу данных только для этого: мы подумали об Elasticsearch или даже о любой другой базе данных NoSQL, такой как MongoDB, некоторые тесты показали, что это даст хорошую производительность. Однако добавление еще одной базы данных к комплексу усложнило задачу, не говоря уже о стоимости. Поскольку мы перестраиваемся, мы готовы попробовать новые технологии, но мы добавили Redis для целей кэширования, а добавление другой базы данных приведет к слишком большому увеличению затрат.
Учитывая это ограничение, единственное, что мы могли, – это попытаться каким-то образом улучшить запрос. Первое, что мы попробовали, это заменить регулярное выражение на LIKE, и, хотя производительность улучшилась, это не принесло ожидаемых результатов поиска. В то время я не знал о существовании ПОЛНОТЕКСТОВОГО индекса и не думал, что в MySQL есть такая функция, я всегда связываю полнотекстовый поиск с Elasticsearch и другими базами данных NoSQL, которые имеют эту основную функцию. Это было, когда я искал “полный текст mysql” в другом Google, и он дал интересные результаты.
Как и любой другой индекс, вы начинаете с его создания, например:
CREATE FULLTEXT INDEX product_search_index ON product(name, description)
И тогда начинается волшебство. Чтобы использовать возможности ПОЛНОТЕКСТОВОГО ИНДЕКСА, вы можете использовать СОВПАДЕНИЕ ... против... функция, есть несколько режимов, и вы выбираете тот, который лучше всего подходит для ваших нужд. Я все еще изучаю все режимы, их преимущества и недостатки, но вы можете найти все объяснения в документации , пока я буду придерживаться В РЕЖИМЕ ЕСТЕСТВЕННОГО ЯЗЫКА , по умолчанию и тот, который лучше всего подходит для моего варианта использования, по совпадению. Цитируя документацию, этот режим работает следующим образом:
Поиск на естественном языке интерпретирует строку поиска как фразу на естественном человеческом языке (фраза в свободном тексте). Специальных операторов нет, за исключением символов двойной кавычки (“). Применяется список стоп-слов.
Он ищет слова, которые мы ищем, как фразу в свободном тексте. В MySQL есть список стоп-слов, который можно настроить в соответствии с вашими потребностями для улучшения результатов. Затем вы можете сделать простой запрос, чтобы получить результаты:
SELECT * FROM product WHERE MATCH(name, description) AGAINST ('notebook off discount')
Затем запрос приведет результаты поиска в порядке релевантности. Кроме того, это быстро, действительно быстро по сравнению с тем, что у нас было раньше. В большинстве случаев мы сократили время отклика на запрос со многих секунд до менее чем 1 секунды. Все были удивлены, включая меня, результатами. Это было похоже на волшебство, и это заставляет меня думать, что мне нужно больше изучать это, чтобы понять, как они это делают.
Вывод
Это впечатляет, как будто каждый день вы можете узнать что-то новое, и иногда это может оказаться на удивление тем решением, которое вы искали. Благодаря этому я смог решить проблему, используя только те ресурсы, которые у меня уже были, и, в конечном счете, не переусердствовал с решением.
Оригинал: “https://dev.to/leoat12/the-magic-known-as-mysql-fulltext-index-3npj”