Индексы и порядок MySQL

Это вопрос, который у меня был навсегда.

Насколько я знаю, порядок индексов имеет значение. Таким образом, индекс, подобный [first_name, last_name], не совпадает с [last_name, first_name], правильно?

Если я определяю только первый индекс, значит ли это, что он будет использоваться только для

SELECT * FROM table WHERE first_name="john" AND  last_name="doe";

а не для

SELECT * FROM table WHERE  last_name="doe" AND first_name="john";

Поскольку я использую ORM, я понятия не имею, в каком порядке эти столбцы будут вызываться. Означает ли это, что мне нужно добавлять индексы во все перестановки? Это выполнимо, если у меня есть индекс с двумя столбцами, но что произойдет, если мой индекс находится на 3 или 4 столбцах?

Ответ 1

Указательный порядок имеет значение, когда условия вашего запроса применимы только к ЧАСТИ индекса. Рассмотрим:

SELECT * FROM table WHERE first_name="john" AND last_name="doe"
SELECT * FROM table WHERE first_name="john"
SELECT * FROM table WHERE last_name="doe"

Если ваш индекс (first_name, last_name), запросы 1 и 2 будут его использовать, запрос №3 не будет. Если ваш индекс (last_name, first_name), запросы 1 и 3 будут его использовать, запрос №2 не будет. Изменение порядка условий в предложении WHERE не имеет эффекта в обоих случаях.

Подробности здесь

Обновление:
В случае, если выше не ясно - MySQL может использовать только индекс, если столбцы в условиях запроса образуют самый левый префикс индекса. Запрос № 2 выше не может использовать индекс (last_name, first_name), потому что он основан только на first_name и first_name НЕ - самый левый префикс индекса (last_name, first_name).

Порядок условий в запросе не имеет значения; запроС# 1 выше сможет использовать индекс (last_name, first_name) просто отлично, поскольку его условия first_name и last_name и, вместе взятые, они образуют самый левый префикс (last_name, first_name).

Ответ 2

ChssPly76 правильно, что порядок булевых выражений не должен соответствовать порядку столбцов в индексе. Булевы операторы коммутативные, а оптимизатор MySQL достаточно умен, чтобы знать, как в большинстве случаев сопоставить выражение с индексом.

Я также хочу добавить, что вы должны научиться использовать EXPLAIN функцию MySQL, чтобы вы могли сами убедиться, что индексирует оптимизатор выберет для данного запроса.

Ответ 3

Почему бы не продлить ответ немного, чтобы сразу сделать абсолютно все кристально чистое.

Если таблица имеет индекс с несколькими столбцами, любой левый префикс индекса может использоваться оптимизатором для поиска строк. Например, если у вас есть индекс с тремя столбцами на (col1, col2, col3), вы индексировали возможности поиска на (col1), (col1, col2) и (col1, col2, col3).

MySQL не может использовать индекс, если столбцы не образуют крайний левый префикс индекса. Предположим, что у вас есть инструкции SELECT, показанные здесь:

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

Если индекс существует на (col1, col2, col3), то только первые два запроса используют индекс. Третий и четвертый запросы включают индексированные столбцы, но (col2) и (col2, col3) не являются левыми префиксами (col1, col2, col3). - MySQL dev