Как заставить MySQL использовать запрос INDEX для просмотра?

Я работаю над веб-проектом с базой данных MySql на Java EE. Нам понадобилось представление для суммирования данных из 3 таблиц с более чем 3М строками в целом. Каждая таблица была создана с индексом. Но я не нашел способ воспользоваться преимуществами индексов в извлечения условного выбора из представления, которое мы создали с помощью [group by].

У меня есть предложения от людей, что использование просмотров в MySql - не очень хорошая идея. Потому что вы не можете создать индекс для представлений в mysql, как в oracle. Но в некоторых тестах, которые я взял, индексы можно использовать в предложении select 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;

Я написал условное утверждение, чтобы увидеть наивысший результат, который kobe получил в 2006;

select * from v_kobe_highscores where happened_in = 2006;

Когда я объясню это в toad для mysql, я обнаружил, что mysql имеет проверку всех строк, чтобы сформировать представление, а затем найти данные с условием в нем, не используя индекс на [used_in].

explain select * from v_kobe_highscores where happened_in = 2006;

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

@zerkms Вот результат, который я тестировал в реальной жизни. Я не вижу больших различий между ними. Я думаю, что @spencer7593 имеет правильную точку. Оптимизатор MySQL не "нажимает" этот предикат в запросе представления.

Ответ 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).

Ответ 2

Создание индекса составной с столбцами player + happened_in (в этом конкретном порядке) - это лучшее, что вы можете сделать в этом случае.

PS: не проверяйте поведение оптимизатора mysql на таком небольшом количестве строк, потому что он скорее всего предпочтет fullscan над индексами. Если вы хотите увидеть, что произойдет в реальной жизни, заполните его реальной жизнью - таким же количеством данных.

Ответ 3

Это напрямую не отвечает на вопрос, но это связанное с общением решение для других, сталкивающихся с этой проблемой. Это обеспечивает те же преимущества использования представления, минимизируя при этом недостатки.

Я настраиваю функцию PHP, на которую я могу отправлять параметры, вещи, которые нужно вставлять внутрь, чтобы максимизировать использование индекса, а не использовать их в предложении join или where вне представления. В этой функции вы можете сформулировать синтаксис SQL для производной таблицы и вернуть этот синтаксис. Затем в вызывающей программе вы можете сделать что-то вроде этого:

$table = tablesyntax(parameters);
select field1, field2 from {$table} as x... + other SQL

Таким образом, вы получаете преимущества инкапсуляции для представления, возможность называть его так, как если бы это было представление, но не ограничения индекса.