Оптимизация SQL запросов с multiple LIKE в MySQL

Быстрый ответ

Если в MySQL вам требуется смоделировать поведение конструкции LIKE IN(), рекомендуется использовать сочетание нескольких операторов LIKE, объединённых оператором OR, или обратиться к REGEXP для более компактного выражения. К примеру, указанный ниже код с применением REGEXP позволит найти соответствующие 'pattern1', 'pattern2', 'pattern3':

SELECT * FROM table_name WHERE column_name REGEXP 'pattern1|pattern2|pattern3';

Данный подход упрощает синтаксис запроса, делая его более понятным и удобным в обслуживании.

В следующих разделах мы подробно проанализируем принципы работы с шаблонами в MySQL, обратив внимание на оптимизацию производительности, способы преодоления общих ошибок, особенности поиска по сложным шаблонам и другие важные моменты.

Преимущества использования REGEXP в MySQL

Поиск по шаблонам – одна из важнейших возможностей SQL. Оператор REGEXP предоставялет удобство объединения нескольких шаблонов, тем самым выполняя роль аналога множественного LIKE. Но прежде чем выбрать REGEXP, следует протестировать его эффективность в сравнении с простым применением LIKE, так как он может быть более требовательным к ресурсам.

Выбор между REGEXP и LIKE

Выбор между REGEXP и LIKE в большой степени зависит от требуемой производительности, особенно при работе с большими объемами данных. Ниже приведены несколько рекомендаций для оптимизации:

• Проведите тестирование обоих подходов, чтобы определить, какой лучше соответствует задачам вашего проекта.
• С учётом использования индексов: REGEXP, как правило, не предусматривает их использования.
• Размер таблиц, распределение данных и сложность шаблонов также значительно влияют на выбор между REGEXP и LIKE.

Как избежать проблем при использовании REGEXP

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

• Для точного соответствия начала и конца строки используйте якори '^' и '$'.
• Чтобы исключить нежелательные шаблоны, используйте отрицание NOT REGEXP дополнительно к REGEXP.
• В сложных ситуациях можно сочетать REGEXP с LIKE или NOT LIKE.

Как создать сложные поисковые запросы

Если одного REGEXP недостаточно, можно использовать его в комбинации:

• Составить сложные выражения, аналогичные LIKE IN(), поможет CONCAT_WS.
• Символ % в LIKE служит для нечеткого поиска.
• В случаях, когда REGEXP неприменим, можно вернуться к разделению запроса с помощью LIKE и OR.

Визуализация

Представим, как оператор LIKE выполняет поиск по нескольким ключевым словам, подобно LIKE IN() в SQL:

SELECT * FROM books WHERE title LIKE '%JavaScript%';
SELECT * FROM books WHERE title LIKE '%Python%';
SELECT * FROM books WHERE title LIKE '%Java%';

Это можно сравнить с методичным просмотром трех различных полок библиотеки в поиске нужных книг:

Полка 1 (📚📙): Ищем книги о 'JavaScript'
Полка 2 (📚📘): Ищем книги о 'Python'
Полка 3 (📚📗): Ищем книги о 'Java'

Нам же требуется один запрос, который одновременно проверяет все полки:

🚁: Провести поиск книг о 'JavaScript', 'Python' и 'Java' на всем пространстве полок одновременно!

Так как LIKE IN() некорректен, формируем основной запрос для решения задачи:

SELECT * FROM books
WHERE title LIKE '%JavaScript%'
   OR title LIKE '%Python%'
   OR title LIKE '%Java%';

Теперь у вас есть единый дрон-запрос (🚁🔑), который способен быстро перемещаться между нужными полками.

Как создать сложные запросы с помощью подзапросов

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

SELECT b.*
FROM books b
JOIN (SELECT 'JavaScript' AS pattern UNION ALL 
      SELECT 'Python' UNION ALL 
      SELECT 'Java') patterns
ON b.title LIKE CONCAT('%', patterns.pattern, '%');
-- Не судите о книге по ее обложке, судите по SQL-запросам! 😉

Такой подход позволяет сократить объем кода и обеспечивает уникальность каждой выбранной записи, даже если она соответствует нескольким шаблонам.

Профилирование запросов с использованием EXPLAIN

Анализ производительности запросов играет достаточно значимую роль. Используйте команду EXPLAIN для анализа плана выполнения запроса:

EXPLAIN SELECT * FROM books WHERE title REGEXP 'Java|Python|JavaScript';
-- EXPLAIN? Не пугайтесь, это всего лишь инструмент для SQL! 😆

REGEXP: не всегда лучший выбор

Несмотря на мощь REGEXP, существуют случаи, когда другие методы будут предпочтительнее:

• При работе с индексированными столбцами LIKE с фиксированными шаблонами может оказаться более подходящим, чем REGEXP.
• Если вы наблюдаете значительное снижение производительности по сравнению с использованием LIKE.

Полезные материалы

1. MySQL :: Руководство по MySQL 8.0 :: 3.3.4.7 Поиск по шаблону — официальная документация MySQL о шаблонном поиске.
2. Оператор SQL LIKE — вводный материал по оператору SQL LIKE.
3. MySQL search on multiple items with LIKE — обсуждение ведётся на Stack Overflow о механизме поиска нескольких элементов с помощью LIKE в MySQL.
4. SQL query using LIKE with multiple values — другое обсуждение на Stack Overflow об использовании SQL-запросов с LIKE для работы со множеством значений.