Индексы и порядок 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, я понятия не имею, в каком порядке будут вызываться эти столбцы. Означает ли это, что я должен добавлять индексы ко всем перестановкам? Это выполнимо, если у меня индекс из 2 столбцов, но что произойдет, если мой индекс будет состоять из 3 или 4 столбцов?


mysql indexing sql-order-by
person Julien Genestoux    schedule 09.08.2009    source источник

Ответы (3)


arrow_upward
59
arrow_downward

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

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

  2. SELECT * FROM table WHERE first_name="john"

  3. 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).

person ChssPly76    schedule 09.08.2009
comment
Итак, я думаю, SELECT * FROM table WHERE last_name=doe AND first_name=john не будет? Если это так, то, поскольку все построение запросов скрыто моей ORM, мне, вероятно, придется определить 2 индекса... что действительно отстой, потому что мне нужно определить индекс по 3 столбцам... - person Julien Genestoux; 10.08.2009
comment
Нет, вы неправильно поняли. WHERE last_name=doe AND first_name=john запрос будет использовать индексы (first_name, last_name) или (last_name, first_name) просто отлично. - person ChssPly76; 10.08.2009
comment
Вопрос: для запроса с 2 условиями И имеет ли значение скорость, если индекс перевернут по сравнению с условиями в запросе или нет? Или это зависит от кардинальности этих столбцов? - person Mihai; 13.11.2014
comment
Отличное резюме для общего вопроса - person Andrew Cetinic; 03.09.2015
comment
WHERE last_name = 'doe' and first_name LIKE 'j%' -- в этом случае INDEX(last_name, first_name) лучше других вариантов. Это из-за диапазона на first_name. Поваренная книга - person Rick James; 19.08.2016
comment
что, если у меня есть индекс (first_name, last_name, full_name) и запрос: где first_name like"doe" and full_name like "john_doe" это использует индекс - person Chinh Nguyen; 09.07.2021

arrow_upward
5
arrow_downward

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

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

person Community    schedule 09.08.2009
comment
MySQL Workbench показывает графическое представление информации EXPLAIN под Execution Plan. - person Sayo Oladeji; 07.04.2016

arrow_upward
3
arrow_downward

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

Если таблица имеет индекс с несколькими столбцами, оптимизатор может использовать любой крайний левый префикс индекса для поиска строк. Например, если у вас есть индекс с тремя столбцами для (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

person Berky    schedule 06.04.2014
comment
SELECT * FROM tbl_name WHERE col1=val1 AND col3=val3; этот запрос тоже использует индекс? - person Chinh Nguyen; 09.07.2021