Как мне заставить MySQL использовать INDEX для просмотра запроса?
Я работаю над веб-проектом с базой данных MySql на Java EE. Нам требовалось представление для суммирования данных из 3 таблиц с общим количеством строк более 3М. Каждая таблица была создана с индексом. Но я не нашел способа воспользоваться преимуществами индексов при извлечении условного оператора select из представления, которое мы создали с помощью [group by].
Я получаю предложения от людей, что использование представлений в MySql не очень хорошая идея. Потому что вы не можете создать индекс для представлений в MySQL, как в Oracle. Но в некотором тесте, который я взял, индексы могут использоваться в представлении выбора select. Может быть, я создал эти взгляды неправильно.
Я буду использовать пример, чтобы описать мою проблему.
У нас есть таблица, в которой записываются данные о рекордах в играх NBA с индексом в столбце [happend_in]
CREATE TABLE `highscores` (
`tbl_id` int(11) NOT NULL auto_increment,
`happened_in` int(4) default NULL,
`player` int(3) default NULL,
`score` int(3) default NULL,
PRIMARY KEY (`tbl_id`),
KEY `index_happened_in` (`happened_in`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
вставить данные (8 строк)
INSERT INTO highscores(happened_in, player, score)
VALUES (2006, 24, 61),(2006, 24, 44),(2006, 24, 81),
(1998, 23, 51),(1997, 23, 46),(2006, 3, 55),(2007, 24, 34), (2008, 24, 37);
затем я создаю представление, чтобы увидеть самый высокий балл, который Коби Брайант получил в каждом году
CREATE OR REPLACE VIEW v_kobe_highScores
AS
SELECT player, max(score) AS highest_score, happened_in
FROM highscores
WHERE player = 24
GROUP BY happened_in;
Я написал условное заявление, чтобы увидеть самый высокий балл, который Кобе получил в 2006 году;
select * from v_kobe_highscores where happened_in = 2006;
Когда я объяснил это в toad для mysql, я обнаружил, что mysql просканировал все строки, чтобы сформировать представление, а затем нашел данные с условием в нем, без использования индекса для [случается_ин].
explain select * from v_kobe_highscores where happened_in = 2006;
Представление, которое мы используем в нашем проекте, построено среди таблиц с миллионами строк. Сканирование всех строк из таблицы в каждом представлении поиска данных недопустимо. Пожалуйста помоги! Спасибо!
@zerkms Вот результат, который я тестировал в реальной жизни. Я не вижу большой разницы между. Я думаю, что @spencer7593 имеет правильную точку. Оптимизатор MySQL не "толкает" этот предикат вниз в запросе представления.
3 ответа
Как заставить 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 не используется, когда он выполняет запрос, формирующий определение представления.
После создания промежуточной "производной таблицы", ТО выполняется внешний запрос, использующий эту "производную таблицу" в качестве источника строк. Когда этот внешний запрос выполняется, happened_in = 2006 Предикат оценивается.
Обратите внимание, что все строки из запроса представления сохраняются, что (в вашем случае) является строкой для КАЖДОГО значения happened_inа не только тот, на который вы указываете предикат равенства во внешнем запросе.
То, как обрабатываются запросы к представлениям, может быть "неожиданным" для некоторых, и это одна из причин того, что использование "представлений" в MySQL может привести к проблемам с производительностью по сравнению с тем, как запросы к представлениям обрабатываются другими реляционными базами данных.
Улучшение производительности запроса представления с подходящим индексом покрытия
Учитывая ваше определение представления и ваш запрос, лучшим вариантом будет метод доступа "Использование индекса" для запроса представления. Чтобы получить это, вам нужен индекс покрытия, например,
... ON highscores (player, happened_in, score).
Вероятно, это будет наиболее выгодный индекс (с точки зрения производительности) для существующего определения представления и существующего запроса. player Столбец является ведущим столбцом, потому что у вас есть предикат равенства для этого столбца в запросе представления. happened_in Следующий столбец, потому что у вас есть операция GROUP BY для этого столбца, и MySQL сможет использовать этот индекс для оптимизации операции GROUP BY. Мы также включаем score столбец, потому что это единственный другой столбец, указанный в вашем запросе. Это делает индекс "покрывающим" индексом, потому что MySQL может удовлетворить этот запрос непосредственно со страниц индекса, без необходимости посещать какие-либо страницы в базовой таблице. И это так же хорошо, как мы собираемся выйти из этого плана запроса: "Использование индекса" без "Использование сортировки файлов".
Сравните производительность с автономным запросом без производной таблицы
Вы можете сравнить план выполнения вашего запроса с представлением и эквивалентным автономным запросом:
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.
Я не уверен, что что-либо из предыдущего дает прямой ответ на вопрос, который вы задавали.
Q: Как мне заставить MySQL использовать INDEX для запроса представления?
A: Определите подходящий INDEX, который может использовать запрос представления.
Краткий ответ: "индекс покрытия" (индекс включает все столбцы, на которые есть ссылки в запросе представления). Ведущие столбцы в этом индексе должны быть столбцами, на которые ссылаются предикаты равенства (в вашем случае, столбец player будет ведущей колонкой, потому что у вас есть player = 24 Предикат в запросе. Кроме того, столбцы, на которые ссылается GROUP BY, должны быть ведущими столбцами в индексе, что позволяет MySQL оптимизировать GROUP BY операция, используя индекс, а не с помощью операции сортировки.
Ключевым моментом здесь является то, что запрос представления в основном является отдельным запросом; результаты этого запроса сохраняются в промежуточной "производной" таблице (таблице MyISAM, которая создается при выполнении запроса к представлению.
Использование представлений в MySQL не обязательно является "плохой идеей", но я настоятельно рекомендую тем, кто решит использовать представления в MySQL, быть в курсе того, как MySQL обрабатывает запросы, которые ссылаются на эти представления. И способ, которым MySQL обрабатывает запросы представления, отличается (значительно) от того, как запросы представления обрабатываются другими базами данных (например, Oracle, SQL Server).
Создание составного индекса с player + happened_in (в этом конкретном порядке) столбцы - лучшее, что вы можете сделать в этом случае.
PS: не тестируйте поведение оптимизатора mysql на таком небольшом количестве строк, потому что он, скорее всего, предпочтет fullscan по сравнению с индексами. Если вы хотите увидеть, что произойдет в реальной жизни - заполните ее реальным жизненным объемом данных.
Это не дает прямого ответа на вопрос, но это напрямую связанный обходной путь для других, сталкивающихся с этой проблемой. Это дает те же преимущества использования представления, минимизируя недостатки.
Я настраиваю функцию PHP, в которую я могу отправлять параметры, вещи, которые нужно вставить внутрь, чтобы максимизировать использование индекса, а не использовать их в соединении или предложении where вне представления. В функции вы можете сформулировать синтаксис SQL для производной таблицы и вернуть этот синтаксис. Затем в вызывающей программе вы можете сделать что-то вроде этого:
$table = tablesyntax(parameters);
select field1, field2 from {$table} as x... + other SQL
Таким образом, вы получаете преимущества инкапсуляции представления, возможность вызывать его, как если бы оно было представлением, но не ограничения индекса.