Блог

Новичок в MySQL здесь.

Я пытаюсь выполнить следующую инструкцию для базы данных sakila

 EXPLAIN SELECT * FROM actor as a
INNER JOIN film_actor as fa on a.actor_id = fa.actor_id
INNER JOIN film AS f ON fa.film_id = f.film_id;
  

И результат

 id| select_type| table | partitions | type | possible_keys |             key          | key_len |        ref         |          rows        | filtered |   Extra
'1',   'SIMPLE',   'a',    NULL,       'ALL',  'PRIMARY',                  NULL,          NULL,            NULL,                  '200',         '100.00',    NULL
'1',   'SIMPLE',   'fa',   NULL,       'ref',  'PRIMARY,idx_fk_film_id',  'PRIMARY',     '2',        'sakila.a.actor_id',          '27',         '100.00',    NULL
'1',   'SIMPLE',   'f',    NULL,       'eq_ref','PRIMARY',                'PRIMARY',     '2',        'sakila.fa.film_id',           '1',         '100.00',    NULL

  

actor таблица содержит actor_id как PK.

film_actor таблица содержит actor_id и film_id в качестве составного первичного ключа плюс idx_fk_film_id в качестве индекса по film_id атрибуту.

film таблица содержит film_id как PK.

Когда я смотрю на план запроса, я заметил, что ALL под type столбцом для actor таблицы есть столбец, который означает полное сканирование таблицы, кто-нибудь знает, почему MySQL не использовал индекс actor_id для поиска? Выполняет ли MySQL запрос от первой строки до нижней строки последовательно, показанной в выходных данных (хотя id s — это все 1 )?

Индексы (и, в частности, некластеризованные индексы) имеют два основных преимущества в случае скорости

• Они могут содержать подмножество ваших данных (например, только выбранные столбцы). Когда запросу нужны только эти столбцы и никаких других, он может считывать данные из индекса. Они называются «покрывающими индексами»
• Когда вы фильтруете данные (например, с помощью предложений WHERE, очень выборочных объединений и т. Д.), А индексы уже отсортированы должным образом, их можно использовать для поиска, а не для полного сканирования

Охват индексов

На данный момент я предполагаю, что вы настроили относительно стандартный PK для actor (being actor_id ), который также создает кластеризованный индекс. Если нет, то это будет heap , но в основном означает, что хранилище будет не отсортировано.

Кластеризованный индекс также включает, для всех практических целей, все остальные столбцы из таблицы (например, он имеет actor_id, actor_first_name, actor_surname и т.д.). Однако он сортируется в соответствии с полями, определенными как кластеризованный индекс (actor_id).

Если вы настроили дополнительный (некластеризованный) индекс, обычно это подмножество столбцов (например, actor_surname ), чтобы помочь при поиске / сортировке по этим полям. Обычно не следует включать все поля таблицы в одно из них.

Поскольку вы выполняете a SELECT * , в какой-то момент ему нужно будет вернуться к таблице / кластеризованному индексу, чтобы получить данные — это означает, что у вас нет покрывающего индекса. Он не может просто получить данные из другого некластеризованного индекса.

Если у вас был некластеризованный индекс для Actor_Id и (скажем) Actor_Name, и вы просто выполняли SELECT Actor_ID, Actor_Name FROM ... , тогда он мог бы использовать индекс в качестве покрывающего индекса (но обратите внимание — если он отсортирован так, как он считает бесполезным, в то время как кластерный сортируется соответствующим образом, он может просто использовать кластеризованныйиндекс в любом случае).

Ищу помощь в фильтрации строк

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

Даже если он отсортирован правильно / etc, у него есть два варианта

• Должен ли я определить, какие строки мне нужно прочитать, а затем прочитать их 1 на 1? или
• Должен ли я просто прочитать всю таблицу и отсортировать ее в памяти?

Первый называется соединением вложенного цикла.

Если он оценивает, что больше работы вернуться к таблице (скажем) 10 раз, чтобы получить строки, а не просто прочитать их все сразу, он просто прочитает их все сразу.

Это (как предложено в комментариях) также объясняет, почему, когда таблица маленькая, она просто считывает все строки. Вероятно, предполагается, что дополнительная работа, необходимая для принятия решения, не стоит делать — просто прочитайте всю таблицу.

Есть отличное видео об индексах — я много раз упоминал об этом здесь, поскольку я много узнал о них. Речь идет о SQL Server, хотя проблема довольно фундаментальна для большинства баз данных. Это книга Брента Озара «Как думать как движок SQL Server«, и в ней в качестве примеров используются пользовательские данные и репутация из Stack Overflow.