Блог

Я использую MySQL 5.6 и пытаюсь оптимизировать следующий запрос:

 SELECT t1.field1,  ...  t1.field30,  t2.field1 FROM Table1 t1  JOIN Table2 t2 ON t1.fk_int = t2.pk_int WHERE t1.int_field = ?  AND t1.enum_filed != 'value' ORDER BY t1.created_datetime desc;  

Ответ может содержать миллионы записей, и каждая строка состоит из 31 столбца.

Теперь ОБЪЯСНИТЕ дополнительно, что планировщик использует «Использование где».

Я попытался добавить следующий индекс:

 create index test_idx ON Table1 (int_field, enum_filed, created_datetime, fk_int);  

После этого ОБЪЯСНИТЕ дополнительно, что планировщик использует «Использование условия индекса; Использование сортировки файлов».

значение «строки» из ОБЪЯСНЕНИЯ с индексом меньше, чем без него. Но на практике время исполнения больше.

Итак, вопросы следующие:

1. Каков наилучший индекс для этого запроса?
2. Почему в ОБЪЯСНЕНИИ говорится, что «key_len» запроса с индексом равен 5. Разве это не должно быть 4 1 8 4=17?
3. Должны ли поля из ПОРЯДКА ПО быть в индексе?
4. Должны ли поля из ОБЪЕДИНЕНИЯ быть в индексе?

попробуйте провести рефакторинг индекса таким образом
, чтобы избежать (o переместиться вправо после fk_int) столбца created_datetime.. и переместите fk_int перед столбцом enum_filed .. чем в этом вахи, тем лучше использовать еще 3 колбы, используемые для фильтра )

 create index test_idx ON Table1 (int_field, fk_int, enum_filed);  

убедитесь, что у вас также есть определенный индекс в столбце pk_int таблицы 2. если вы еще не добавили

 create index test_idx ON Table2 (int_field, fk_int, enum_filed);  

Каков наилучший индекс для этого запроса?

• Может (int_field, created_datetime) быть (см. Следующие вопросы и ответы по причине.)

Почему в ОБЪЯСНЕНИИ говорится, что «key_len» запроса с индексом равен 5. Разве это не должно быть 4 1 8 4=17?

• enum_filed != побеждает оптимизатор. Если для этого перечисления есть только одно другое значение (и оно есть NOT NULL ), то используйте = и другое значение. И попробуй INDEX(int_field, enum_field, created_datetime) Оптимизатор гораздо счастливее = , чем с любым неравенством.
• «5» может указывать на 2 столбца, или это может указывать на один INT , который может быть обнулен. Если int_field возможно NULL , подумайте о том, чтобы изменить его на NOT NULL ; тогда «5» упадет до «4».

Должны ли поля из ПОРЯДКА ПО быть в индексе?

• Только в том случае, если индекс может полностью обрабатывать WHERE . Обычно это происходит только в том случае, если все WHERE тесты = выполнены . (Отсюда и мой предыдущий ответ.)
• Другим случаем включения этих столбцов является «охват»; см. Следующие вопросы и ответы.

Должны ли поля из ОБЪЕДИНЕНИЯ быть в индексе?

• Это зависит. Одна вещь, которая дает некоторое преимущество в производительности, — это включение всех столбцов, упомянутых в любом месте SELECT . Это называется индексом «покрытия» и указывается в EXPLAIN Using index (не Using index condition ). В t1 слишком много столбцов, чтобы добавить индекс «покрытия». Я думаю, что практический предел составляет около 5 столбцов.

Мое предположение по вашему вопросу № 1:

 create index my_idx on Table1(int_field, created_datetime desc, fk_int)  

или один из них (но ни один из них, вероятно, не будет стоить того):

 create index my_idx on Table1(int_field, created_datetime desc, enum_filed, fk_int)  

 create index my_idx on Table1(int_field, created_datetime desc, fk_int, enum_filed)  

Я предполагаю 3 вещи:

• Table2.pk_int это уже первичный ключ, судя по названию
• where Условию on Table1.int_field удовлетворяет только небольшое подмножество Table1
• Неравенство на Table1.enum_filed (я бы исправил опечатку, если бы я был вами) исключает только небольшое подмножество Table1

Вопрос № 2: key_len относится к используемым ключам. Не забывайте, что для обнуляемых ключей есть один дополнительный байт. В вашем случае, если int_field обнуляется, это означает, что это единственный используемый ключ, в противном int_field случае используются оба enum_filed и.

Что касается вопросов 3 и 4: если, как я полагаю, более эффективно запускать план запроса с where включенного условия Table1.int_field , составной индекс, в данном случае также с правильным порядком сортировки ( desc ), позволяет сканировать индекс, чтобы получить выходные строки в правильном порядке, без дополнительного шага сортировки. Кроме того, добавление также fk_int в индекс делает поиск любой записи Table1 ненужным, если соответствующая запись не присутствует в Table2 . По аналогичной причине вы также можете добавить enum_filed в индекс, но, если это не приведет к значительному уменьшению количества выходных записей, увеличение размера индекса сделает ситуацию не лучше, а хуже. В конце концов, вам придется попробовать это (с реалистичными данными!).

Обратите внимание, что если вы поместите другой столбец между int_field и created_datetime в индекс, индекс не будет предоставлять created_datetime (для данного int_field ) в нужном порядке вывода.

Проблема была устранена путем добавления дополнительных фильтров ( where предложение to) в запрос. В отношении индексов 2 предложенные индексы были полезны:

1. От @WalterTross со следующим индексом для первоначального запроса:

(int_field, created_datetime desc, enum_filed, fk_int)

С моим коротким комментарием: индексы desc не поддерживаются в MySQL 5.6 — это ключевое слово просто зарезервировано.

2. От @RickJames со следующим индексом для измененного запроса:

(int_field, created_datetime)

Спасибо всем, кто пытался помочь. Я действительно ценю это.