Как интерпретировать вывод MySQL EXPLAIN?

Я хочу выбрать содержимое столбца text из entrytable.

EXPLAIN SELECT text
FROM entrytable
WHERE user = 'username' &&
`status` = '1' && (
    `status_spam_user` = 'no_spam'
    || (
        `status_spam_user` = 'neutral' &&
        `status_spam_system` = 'neutral'
    )
)
ORDER BY datum DESC
LIMIT 6430 , 10

Таблица имеет три индекса:

  • index_user (пользователь)
  • index_datum (datum)
  • index_status_mit_spam (status, status_spam_user, status_spam_system)

Результат EXPLAIN:

id  select_type     table       type    possible_keys                       key         key_len     ref     rows    Extra
1   SIMPLE          entrytable  ref     index_user,index_status_mit_spam    index_user  32          const   7800    Using where; Using filesort
  • Являются ли possible_keys индексы MySQL, которые могут захотеть использовать, и keys индексы MySQL фактически используют?
  • Почему индекс index_status_mit_spam не используется? В запросе столбцы имеют тот же порядок, что и в индексе,...
  • Почему индекс index_datum не используется для ORDER BY?
  • Как я могу оптимизировать свои таблицы-индексы или запрос? (Запрос выше требует до 3 секунд, имея около миллиона записей в таблице).

Ответы

Ответ 1

Отвечая на ваши вопросы:

  • Является possible_keys индексов MySQL, возможно, захотите использовать и keys индексов MySQL фактически использует? Да, это правильно.

  • Почему индекс index_status_mit_spam не используется? В запросе столбцы имеют тот же порядок, что и в индексе. SQL использует статистику по индексам таблиц, чтобы определить, какой индекс использовать. Порядок полей в операторе выбора никак не влияет на то, какой индекс использовать. Статистика вокруг индексов включает такую информацию, как уникальность индекса и другие вещи. Вероятно, будут использованы более уникальные индексы. Подробнее об этом читайте здесь: http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-statistics-estima.html или здесь: http://dev.mysql.com/doc/refman/5.0/ru//myisam-index-statistics.html. Эти факторы определяют, как MySQL выберет один индекс для использования. Он будет использовать только один индекс.

  • Почему индекс index_datum не используется для ORDER BY? MySQL будет использовать только один индекс, а не два во время запроса, так как использование второго индекса еще больше замедлит запрос. Чтение индекса НЕ читает таблицу. Это связано с операционной эффективностью запроса. Вот ответы, которые могут объяснить некоторые концепции: https://dba.stackexchange.com/questions/18528/performance-difference-between-clustered-and-non-clustered-index/18531#18531 или Добавление предельного предложения в запрос MySQL замедляется это резко или MySQL индексы и когда их сгруппировать. Эти ответы содержат много деталей, которые помогут вам понять индексацию MySQL.

  • Как я могу оптимизировать свои табличные индексы или запрос? (Приведенный выше запрос требует до 3 секунд, имея около миллиона записей в таблице). Ну, здесь есть сортировка файлов, которая, вероятно, замедляет вас. Возможно, в таблице слишком много индексов, а MySQL выбирает неправильный.

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

Ответ 2

  • possible_keys обозначает все индексы вашей таблицы (ключи или столбцы индекса)
  • Оптимизатор MySQL решает наилучший способ EXECUTE запроса, он может использовать любой индекс (необязательный первичный ключ) или none
  • Чтобы заставить MySQL использовать или игнорировать индекс, указанный в столбце possible_keys, используйте FORCE INDEX, USE INDEX или IGNORE INDEX в вашем запросе

  • Отметьте эту ссылку - http://dev.mysql.com/doc/refman/5.1/en/index-hints.html.

    Вы можете указать область подсказки индекса, добавив предложение FOR в подсказку. Это обеспечивает более мелкомасштабный контроль над выбором оптимизатора плана выполнения для различных этапов обработки запросов. Чтобы повлиять только на индексы, используемые, когда MySQL решает, как найти строки в таблице и как обрабатывать объединения, используйте FOR JOIN. Чтобы повлиять на использование индекса для сортировки или группировки строк, используйте FOR ORDER BY или FOR GROUP BY. (Однако, если для таблицы есть индекс покрытия и он используется для доступа к таблице, оптимизатор будет игнорировать подсказки IGNORE INDEX FOR {ORDER BY | GROUP BY}, которые запрещают этот индекс.)

  • Попробуйте использовать другой индекс - проверьте эту ссылку наверняка - Использование MySQL` FORCE INDEX`?

  • Понять выходной формат EXPLAIN - http://dev.mysql.com/doc/refman/5.1/en/explain-output.html