Вопрос по mysql, sql – Оптимизация запросов MySQL LIKE '% string%' в innoDB

14

Имея эту таблицу:

<code>CREATE TABLE `example` (
`id` int(11) unsigned NOT NULL auto_increment,
`keywords` varchar(200) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB;
</code>

Мы хотели бы оптимизировать следующий запрос:

<code>SELECT id FROM example WHERE keywords LIKE '%whatever%'
</code>

Таблица InnoDB, (поэтому пока нет FULLTEXT), какой индекс лучше всего использовать для оптимизации такого запроса?

Мы попробовали простое:

<code>ALTER TABLE `example` ADD INDEX `idxSearch` (`keywords`);
</code>

Но запрос объяснения показывает, что нужно сканироватьwhole table if our queries where LIKE 'whatever%' instead, this index performs well, but otherwise has no value.

Есть ли способ оптимизировать это для innoDB?

Спасибо!

Я также рекомендую некоторые настройки производительности MySQL,mysqltuner.pl или жеtuning-primer.sh jaggedsoft

Ваш Ответ

2   ответа
42

LIKE 'whatever%' предложение типа, MySQL может использовать эти начальные индексы для поискаwhatever очень быстро.

Но переход наLIKE '%whatever%' удаляет этот якорь в начале строки. Теперь индексы, основанные на начале, не могут быть использованы, потому что ваш поисковый термин больше не привязан в начале строки - он "плавающий". где-то посередине и по всему полю должен быть поиск. любойLIKE '%... Запрос никогда не может использовать индексы.

Вот почему вы используете полнотекстовые индексы, если все, что вы делаете, являются «плавающими»; поиски, потому что они предназначены для этого типа использования.

Важное замечание: сейчас InnoDBподдерживает полный текст индексы с версии 5.6.4. Поэтому, если вы не можете обновить версию не ниже 5.6.4, ничто не мешает вам использовать InnoDB *AND полнотекстовый поиск.

Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded jmserra
Error: User Rate Limit Exceeded jmserra
0

что создание индекса также помогло ускорить запросыlike '%abc%' запросы в моем случае.

БегMySQL 5.5.50 наUbuntu (оставив все по умолчанию), я создал таблицу с большим количеством столбцов и вставил100,000 фиктивные записи. В один столбец я вставил совершенно случайные строки из 32 символов (то есть все они уникальны).

Я выполнил несколько запросов, а затем добавил индекс для этого столбца. Просто

select id, searchcolumn from table_x where searchcolumn like '%ABC%'

возвращает результат в~2 seconds without индекс и в0.05 seconds with индекс.

Это не соответствует объяснениям выше (и во многих других сообщениях). Что может быть причиной этого?

EDIT Я проверил вывод EXPLAIN. Вывод говорит, что строки100,000, но дополнительная информация & quot;Using where; Using index& Quot ;. Итак, каким-то образом СУБД должна искать все строки, но все же может использовать индекс?

Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded

Похожие вопросы