Блог

Главная - Вопросы по программированию - Почему этот запрос MySQL занимает непропорционально больше времени при добавлении 1 или 2 символов в строку поиска?

Почему этот запрос MySQL занимает непропорционально больше времени при добавлении 1 или 2 символов в строку поиска?

#mysql #sql #query-optimization #innodb #database-performance

Вопрос:

У меня есть следующий SQL-запрос:

 SELECT * FROM table WHERE text LIKE '%'
                      AND text LIKE '%'
                      AND text LIKE ' 2%';

В таблице содержится около 40 000 записей с текстовым содержанием около 60 000 символов.

Когда я выполняю запрос, он выполняется довольно быстро… продолжительность составляет около 0,05 секунды.

Дело в том, что когда я добавляю всего два символа в последнюю строку, это занимает уже 15 полных секунд:

 SELECT * FROM table WHERE text LIKE '%'
                      AND text LIKE '%'
                      AND text LIKE ' 20i%';

Обратите внимание на разницу между 202 и 2020i .

Самое забавное, что это как-то связано с объединением цифр и букв.

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

 SELECT * FROM table WHERE text LIKE '%'
                      AND text LIKE '%'
                      AND text LIKE '%information%';

Используя information2 вместо information «хотя»… время выполнения увеличивается до 17 секунд.

Может ли быть так, что некоторые, если это вызвано предопределенными наборами символов ? И определенные шаблоны, такие как «2i» или «i2», интерпретируются БД определенным образом ? Просто одна мысль… Набор символов во всех столбцах utf8 и параметры сортировки utf8_general_ci .

Спасибо за любую помощь !

Редактировать:

Вот таблица, о которой идет речь !

 CREATE TABLE `table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `text` mediumtext CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  FULLTEXT KEY `text` (`text`)
) ENGINE=MyISAM AUTO_INCREMENT=889823 DEFAULT CHARSET=latin1

Комментарии:

1. Возможно, это не связано с вашим вопросом, но, когда вы запускаете шаблон, % вы не используете индексы. И я думаю, что вам следует использовать FTS (полнотекстовый поиск) dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

2. пожалуйста, отредактируйте свой вопрос, чтобы показать (в виде текста, а не изображения) вывод show create table yourtablename;

3. вы должны проверить EXLAIN вашего запроса, так как он не найдет 2020i в ваших таблицах, а idex не поможет, и mysql все равно попытается найти его с помощью сканирования полной таблицы, вы думали о регулярных выражениях или полнотекстовых индексах

4. Если цель состоит в том, чтобы найти «15» в середине «слова», то FULLTEXT это бесполезно.

5. Я отредактировал вопрос ! Я знаю о полнотекстовом поиске. Спасибо за все ваши ответы. @ysth Если он не может найти » 2%», он, естественно, также не может найти » 20i% » … все равно одна строка занимает около 0,05 секунды, а другая-15 секунд.

Ответ №1:

Полнотекстовые индексы создаются на основе текстовых столбцов (CHAR, VARCHAR или ТЕКСТОВЫЕ столбцы) для ускорения запросов и операций DML с данными, содержащимися в этих столбцах.

ПОЛНОТЕКСТОВЫЙ индекс

 ALTER TABLE your_table_name ADD FULLTEXT INDEX `FullText` ('col1', 'col2', 'col3', 'etc');

Комментарии:

1. Полнотекстовые индексы-это инструмент для поиска на естественном языке. Вероятно, их будет бесполезно искать в том, что выглядит как коды или ссылки.

2. Спасибо вам за ваши ответы. В таблице уже есть полнотекстовый индекс. (Обратите внимание на движок MyISAM) Однако я использую ключевое слово LIKE, так как я также пытаюсь найти определенные шаблоны внутри подстрок слов. Вопрос больше связан с тем, почему поиск на 1-2 символа больше занимает так много времени !

Ответ №2:

Похоже, что ответ на мой вопрос, почему запросы занимали так много времени, связан с настройками сортировки конкретного text столбца. Параметры сортировки utf8 , по-видимому, оказывают огромное влияние на производительность при сравнении строк. Параметры сортировки базы данных по умолчанию таковы latin1 , что я не уверен, влияет ли это на производительность, поскольку база данных внутренне преобразует любые данные внутри столбца, который не является кодировкой по умолчанию, в кодировку по умолчанию ( latin1 ) или потому, что само представление просто неоптимально при выполнении сопоставления строк, тем не менее, я предполагаю, что некоторые шаблоны символов просто непропорционально занимают гораздо больше времени при использовании utf8 сортировки. Я изменил параметры сортировки text столбца на latin1 , и запрос снова работал довольно быстро. Это может быть не фактический ответ на мой вопрос, а решение моей проблемы слишком медленных запросов.

Метки: