Определяет ли порядок полей многоколоночного индекса в MySQL

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

Мне любопытно, если я делаю индекс с несколькими столбцами, если порядок, в котором они указаны, имеет значение вообще. Я предполагаю, что это не так, и что двигатель будет относиться к ним как к группе, где упорядочение не имеет значения. Но я хочу подтвердить.

Например, с сайта mysql (http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html)

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

Будет ли какое-либо пособие в любых случаях, когда следующее будет лучше или эквивалентно?

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (first_name,last_name)
);

Specificially:

INDEX name (last_name,first_name)

против

INDEX name (first_name,last_name)

Ответ 1

При обсуждении многоколоночных индексов я использую аналогию с телефонной книгой. Телефонная книга - это в основном индекс по фамилии, затем имя. Таким образом, порядок сортировки определяется тем, что "столбец" является первым. Поиски попадают в несколько категорий:

Если вы посмотрите людей, чья фамилия Смит, вы можете легко найти их, потому что книга сортируется по фамилии.
Если вы посмотрите людей, чье имя - Джон, телефонная книга не поможет, потому что Джонс разбросаны по всей книге. Вам нужно отсканировать всю телефонную книгу, чтобы найти все.
Если вы посмотрите на людей с определенной фамилией Смит и с конкретным именем Джона, книга поможет, потому что вы обнаружите, что Смиты отсортированы вместе, и внутри этой группы Смит Джонс также найдены в отсортированном порядке.

Если у вас была телефонная книга, отсортированная по имени, а затем по фамилии, сортировка книги поможет вам в вышеуказанных случаях № 2 и № 3, но не в случае № 1.

Это объясняет случаи поиска точных значений, но что, если вы просматриваете диапазоны значений? Скажите, что вы хотели найти всех людей, чье имя - Джон и чья фамилия начинается с "S" (Смит, Сондерс, Стонтон, Шерман и т.д.). Джонс сортируется под "J" в пределах каждой фамилии, но если вы хотите, чтобы все Джонсы для всех фамилий, начинающихся с "S", Джонс не сгруппированы. Они снова разбросаны, поэтому вам придется сканировать все имена с фамилией, начинающейся с "S". Если бы телефонная книга была организована по имени, то по фамилии, вы бы нашли всех Джона вместе, то в пределах Джонса все фамилии "S" были бы сгруппированы вместе.

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

Вы можете прочитать мою презентацию Как создать индексы, действительно для получения дополнительной информации.

Ответ 2

Оба индекса различны. Это верно в MySQL и других базах данных. MySQL делает довольно хорошую работу по объяснению разных в документации.

Рассмотрим два индекса:

create index idx_lf on name(last_name, first_name);
create index idx_fl on name(first_name, last_name);

Оба они должны работать одинаково хорошо:

where last_name = XXX and first_name = YYY

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

where last_name = XXX
where last_name like 'X%'
where last_name = XXX and first_name like 'Y%'
where last_name = XXX order by first_name

idx_fl будет оптимальным для следующего:

where first_name = YYY
where first_name like 'Y%'
where first_name = YYY and last_name like 'X%'
where first_name = XXX order by last_name

Для многих из этих случаев оба индекса могут быть использованы, но один из них оптимален. Например, рассмотрите idx_lf с запросом:

where first_name = XXX order by last_name

MySQL может прочитать всю таблицу с помощью idx_lf, а затем выполнить фильтрацию после order by. Я не думаю, что это вариант оптимизации на практике (для MySQL), но это может произойти в других базах данных.

Ответ 3

Общее правило заключается в том, что вы хотите поставить наиболее избирательный - то есть тот, который даст вам наименьшие результаты - во-первых. Поэтому, если вы создаете индекс с несколькими столбцами в таблице с столбцом status, например, 10 возможных значений, а также столбец dateAdded, и вы обычно пишете запросы типа

SELECT * FROM myTable WHERE status='active' and dateAdded='2010-10-01'

... тогда вам нужно сначала dateAdded, потому что это ограничило бы сканирование всего несколькими строками, а не 10% (или любой другой пропорцией "активными" ) ваших строк.

Это требует немного размышлений и настройки; вы должны проверить книгу Лахденмаки и Лича.