Оптимизация SQL-запроса с помощью ИНДЕКСА

#mysql #sql

Вопрос:

У меня очень простой запрос

 SELECT col1, col2, col3, col4 FROM table FORCE INDEX (col2)
WHERE col2 IN ('there', 'are, 'around', 'six', 'values', 'here')

с индексом col2 для col2. У моего table есть около 10 миллионов строк. Я использовал FORCE INDEX здесь, потому что в моей таблице есть другие индексы, а MySQL использует один из других индексов вместо индекса col2 . Другой индекс очень медленный для этого запроса.

Список всех индексов в моей таблице:

 INDEX col2 (col2)
UNIQUE INDEX ind1 (col1, col2)
INDEX ind2 (col1, col2)
INDEX ind3 (col2, col1)

Этот запрос (с FORCE INDEX ) не является медленным (занимает 6 секунд на бесплатном уровне AWS RDS), но необходимо выполнить его как можно быстрее. Могу ли я что-нибудь еще сделать, чтобы ускорить этот запрос?

1. Если оптимизатор запросов не ошибается, вы не должны принудительно индексировать. Бывают случаи, когда использование индекса происходит медленнее.

2. отредактировал сообщение

3. Запрос прост. MySQL должен быть в состоянии определить правильный индекс. Каковы другие индексы, какова схема и каков реальный запрос?

4. Я мог бы представить, что сопоставление констант и в таблице по умолчанию отличается, что предотвращает использование индекса on col2 .

5. отредактировал сообщение

Ответ №1:

Во-первых, вы должны попытаться не форсировать включение индекса col2 , а вместо этого просто посмотреть на план объяснения. Вероятно, col2 здесь будет использоваться один столбец index on . Однако вы можете попробовать добавить в свою таблицу следующий составной охватывающий индекс:

 CREATE INDEX idx ON yourTable (col2, col1, col3, col4);

Этот индекс будет охватывать WHERE предложение, а также включать другие столбцы, которые появляются в SELECT предложении. При желании MySQL может использовать этот индекс для полного покрытия всего запроса без необходимости возврата к кластеризованному индексу (т. Е. Исходной таблице).

1. Я попытался опустить ПРИНУДИТЕЛЬНЫЙ ИНДЕКС, но запрос становится очень медленным (MySQL использует другой индекс). У меня нет индекса, который вы указываете здесь. Как вы думаете, этот индекс потенциально может быть быстрее, чем индекс col2 ?

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

Ответ №2:

 INDEX col2 (col2)
UNIQUE INDEX ind1 (col1, col2)
INDEX ind2 (col1, col2)
INDEX ind3 (col2, col1)

Некоторые из этих индексов являются избыточными. MySQL может использовать (col2, col1) col2 как для поиска, так и для поиска по обоим col2 и col1 . И ind2 полностью избыточен с ind1 .

Избыточность может сбивать с толку оптимизатора.

Чтобы охватить все комбинации col1 и col2 , а также обеспечить уникальность, вам нужно только…

 INDEX col2 (col2)
UNIQUE INDEX ind1 (col1, col2)

Удаление избыточных индексов ускорит вставки и сэкономит место.

См. раздел 8.3.6 Индексы с несколькими столбцами.

Планировщик запросов делает свои предположения на основе статистики таблицы. Иногда эти статистические данные устарели. Попробуйте запустить analyze table , чтобы обновить их.