Ответ 1
Как вы получаете MySQL для использования индекса для запроса вида? Короткий ответ, укажите индекс, который может использовать MySQL.
В этом случае оптимальный индекс, вероятно, является "охватывающим" индексом:
... ON highscores (player, happened_in, score)
Вероятно, MySQL будет использовать этот индекс, а EXPLAIN будет показывать: "Using index" из-за WHERE player = 24 (предикат равенства в ведущем столбце индекса. GROUP BY happened_id (второй столбец в индексе), может позволить MySQL оптимизировать это, используя индекс, чтобы избежать операции сортировки. Включение столбца score в индекс позволит полностью выполнить запрос из индекса, не посещая (поиск) страницы данных, на которые ссылается индекс.
Это быстрый ответ. Более длинный ответ заключается в том, что MySQL вряд ли использует индекс с ведущим столбцом happened_id для запроса вида.
Почему представление вызывает проблему с производительностью
Одна из проблем, с которой вы сталкиваетесь с представлением MySQL, заключается в том, что MySQL не "нажимает" предикат из внешнего запроса в запрос вида.
В вашем внешнем запросе указывается WHERE happened_in = 2006. Оптимизатор MySQL не учитывает предикат, когда он запускает внутренний запрос на просмотр. Этот запрос для представления выполняется отдельно, перед внешним запросом. Результат от выполнения этого запроса "материализуется"; то есть результаты сохраняются как промежуточная таблица MyISAM. (MySQL называет это "производной таблицей", и имя, которое они используют, имеет смысл, когда вы понимаете операции, выполняемые MysQL.)
Суть в том, что индекс, который вы определили на happened_in, не используется MySQL, когда он обрабатывает запрос, который формирует определение представления.
После создания промежуточной "производной таблицы" THEN выполняется внешний запрос, используя эту "производную таблицу" в качестве источника строк. Это, когда выполняется этот внешний запрос, оценивается предикат happened_in = 2006.
Обратите внимание, что все строки из запроса вида хранятся, что (в вашем случае) является строкой для КАЖДОГО значения happened_in, а не только того, который вы указываете в предикате равенства во внешнем запросе.
Способ обработки запросов просмотра может быть "неожиданным" для некоторых, и это одна из причин, по которой использование "представлений" в MySQL может привести к проблемам с производительностью, по сравнению с тем, как запросы запросов обрабатываются другими реляционными базами данных.
Повышение производительности запроса вида с подходящим индексом покрытия
Учитывая ваше определение вида и ваш запрос, о том, как лучше всего вы собираетесь получить, будет метод доступа "Использование индекса" для запроса вида. Чтобы получить это, вам понадобится индекс покрытия, например.
... ON highscores (player, happened_in, score).
Скорее всего, это самый полезный индекс (с точки зрения производительности) для вашего существующего определения определения и вашего существующего запроса. Столбец player является ведущим столбцом, потому что у вас есть предикат равенства в этом столбце в запросе представления. Столбец happened_in следующий, потому что у вас есть операция GROUP BY в этом столбце, и MySQL сможет использовать этот индекс для оптимизации операции GROUP BY. Мы также включаем столбец score, потому что это единственный столбец, на который ссылается ваш запрос. Это делает индекс "закрывающим" индексом, потому что MySQL может удовлетворить этот запрос непосредственно с страниц индекса, без необходимости посещать какие-либо страницы в базовой таблице. И это так хорошо, как мы собираемся выбраться из этого плана запросов: "Использовать индекс" без "Использование filesort".
Сравнение производительности с автономным запросом без производной таблицы
Вы можете сравнить план выполнения вашего запроса с представлением против эквивалентного автономного запроса:
SELECT player
, MAX(score) AS highest_score
, happened_in
FROM highscores
WHERE player = 24
AND happened_in = 2006
GROUP
BY player
, happened_in
В автономном запросе также может использоваться индекс покрытия, например.
... ON highscores (player, happened_in, score)
но без необходимости материализовать промежуточную таблицу MyISAM.
Я не уверен, что любой из предыдущих дает прямой ответ на вопрос, который вы задавали.
В: Как заставить MySQL использовать запрос INDEX для просмотра?
A: Определите подходящий индекс, который может использовать запрос вида.
Короткий ответ - это "индекс покрытия" (индекс включает все столбцы, на которые ссылается запрос запроса). Ведущими столбцами в этом индексе должны быть столбцы, на которые ссылаются предикаты равенства (в вашем случае столбец player будет ведущим столбцом, потому что в запросе есть предикат player = 24. Также столбцы, на которые ссылаются в GROUP BY должен быть ведущими столбцами в индексе, что позволяет MySQL оптимизировать операцию GROUP BY, используя индекс, а не используя операцию сортировки.
Ключевым моментом здесь является то, что запрос вида в основном представляет собой автономный запрос; результаты этого запроса сохраняются в промежуточной "производной" таблице (таблица MyISAM, создаваемая при запуске запроса к представлению.
Использование представлений в MySQL не обязательно является "плохой идеей", но я бы настоятельно предостерег тех, кто решил использовать представления в MySQL, чтобы быть ЗНАЕМ того, как MySQL обрабатывает запросы, которые ссылаются на эти представления. И способ обработки запросов запросов MySQL отличается (значительно) от того, как запросы просмотра обрабатываются другими базами данных (например, Oracle, SQL Server).