Существует ли лучший способ индексировать несколько столбцов, чем создание индекса для каждой перестановки?

Чтобы использовать индексы для всех возможных условий равенства в N столбцах, вам понадобятся C ([N / 2], N) индексов, которые это N! / ([N / 2]! * (N - [N / 2])!)

См. Эту статью в моем блоге для подробных объяснений:

• Создание индексов

Вы также можете прочитать строгий математический ] доказательство русского математика Егора Тимошенко ( обновление: теперь на английском языке)

Однако можно получить достойную производительность с меньшим количеством индексов, используя следующие методы:

Объединение индексов

Если столбцы col1 , col2 и col3 являются выборочными, то этот запрос

SELECT  *
FROM    mytable
WHERE   col1 = :value1
        AND col2 = :value2
        AND col3 = :value3

может использовать три отдельных индекса на col1 , col2 и col3 , выберите ROWID ' установка битов на 1 , если они удовлетворяют первому условию.

Затем он просканирует второй индекс, И , найдя биты, которые удовлетворяют второму условию, с 1 . Это оставит 1 только для тех битов, которые удовлетворяют обоим условиям.

То же самое для третьего индекса.

Наконец, он просто выберет строки с tid , соответствующими в установленный бит.

tid будут извлекаться последовательно, так что это очень эффективно.

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

Да, вы можете использовать индексы с несколькими столбцами. Что-то вроде

CREATE TABLE temp (
    id         INT NOT NULL,
    a          INT NULL,
    b          INT NULL,
    c          INT NULL,
    PRIMARY KEY (id),
    INDEX ind1 (a,b,c),
    INDEX ind2 (a,b)
);

Этот тип индекса, например, ind1, наверняка поможет вам в таких запросах, как

SELECT * FROM temp WHERE a=2 AND b=3 AND c=4;

. Точно так же ind2 поможет вам в таких запросах, как

SELECT * FROM temp WHERE a=2 AND b=3;

. Но эти индексы не будут использоваться, если запрос похож на

SELECT * FROM temp WHERE a=2 OR b=3 OR c=4;

] Здесь вам понадобятся отдельные индексы для a, b и c.

Поэтому вместо того, чтобы иметь так много индексов, я бы согласился с тем, что сказал Джон, т.е. иметь индексы для a, b, c, и если вы чувствуете, что ваша рабочая нагрузка покрывает больше запросов с несколькими столбцами, тогда вы можете переключиться на индексы с несколькими столбцами.

Учитывая, что ваши столбцы на самом деле являются городом, государством и почтовым индексом, я бы предложил только следующие индексы:

ИНДЕКС (ZipCode)

Если я прав, почтовые индексы не дублируются в США, поэтому бессмысленно добавлять информацию о городе или штате в индекс, потому что они будут иметь одинаковое значение для всех почтовых индексов. Например, 90210 всегда означает Лос-Анджелес, Калифорния.

ИНДЕКС (Город (5)) или ИНДЕКС (Город (5)), Штат)

Это просто индекс первых пяти букв. названия города. Во многих случаях это будет достаточно конкретным, чтобы индексирование состояния не t обеспечить какую-либо полезную фильтрацию. Например, «Los A» почти наверняка будут записями из Лос-Анджелеса, Калифорния. Может быть, в США есть еще один небольшой городок, начинающийся с «Лос-А», но записей будет так мало, что не стоит загромождать индекс данными штата. С другой стороны, названия некоторых городов появляются во многих штатах (на ум приходит Спрингфилд), поэтому в таких случаях лучше также проиндексировать штат. Вам нужно будет выяснить для себя, какой индекс больше всего подходит для вашего набора данных. Если есть сомнения, я бы выбрал второй индекс (Город и штат).

ИНДЕКС (Штат, sort_field )

Штат - довольно широкий индекс (вполне возможно, Нью-Йорк и Калифорния только будет 30% записей). Если вы планируете отображать эту информацию пользователю, скажем, по 30 записей за раз, тогда у вас будет запрос, заканчивающийся на

... WHERE STATE = "NY"
ORDER BY <sort_field>
LIMIT <number>, 30

. Чтобы сделать этот запрос эффективным, вам необходимо включить столбец сортировки в индекс состояния. Поэтому, если вы показываете страницы, упорядоченные по фамилии (при условии, что у вас есть этот столбец), вы должны использовать INDEX (State, LastName (3)) , иначе MySQL должен отсортировать все записей "NY", прежде чем он сможет дать вам желаемые 30.

Это зависит от вашего sql-запроса.

index (a, b, c) отличается от index (b, c, a) или index (a, c, b)