Множественные и одиночные индексы

Мне немного стыдно спрашивать об этом, так как я много лет работаю с MySQL, но хорошо.

У меня есть таблица с двумя полями, a и b. Я буду запускать в нем следующие запросы:

  • SELECT * FROM ... WHERE A = 1;
  • SELECT * FROM ... WHERE B = 1;
  • SELECT * FROM ... WHERE A = 1 AND B = 1;

С точки зрения производительности по меньшей мере одна из следующих конфигураций индексов медленнее, по крайней мере, для одного запроса? Если да, пожалуйста, уточните.

  • ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b);
  • ALTER TABLE ... ADD INDEX (a, b);
  • ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b); ALTER TABLE ... ADD INDEX (a, b);

Спасибо (обратите внимание, что речь идет о не уникальных индексах)

Ответы

Ответ 1

Да, хотя бы один случай значительно медленнее. Если вы определяете только следующий индекс:

ALTER TABLE ... ADD INDEX (a, b);

... тогда запрос SELECT * FROM ... WHERE B = 1; не будет использовать этот индекс.

Когда вы создаете индекс с составным ключом, порядок столбцов ключа важен. Рекомендуется попытаться упорядочить столбцы в ключе, чтобы повысить избирательность, причем наиболее избирательные столбцы находятся в самой левой части ключа. Если вы этого не сделаете и поставьте неселективный столбец в качестве первой части ключа, вы рискуете вообще не использовать этот индекс. (Источник: Советы по оптимизации индекса Composite SQL Server)

Ответ 2

Очень маловероятно, что простое существование индекса замедляет запрос SELECT: он просто не будет использоваться.

В теории оптимизатор может неправильно выбрать более длинный индекс на (a, b), а не один на (a), чтобы обслуживать запрос, который ищет только a.

На практике я никогда не видел его: MySQL обычно делает противоположную ошибку, принимая более короткий индекс, когда существует более длинный.

Update:

В вашем случае для всех запросов будет достаточно одной из следующих конфигураций:

(a, b); (b)

или

(b, a); (a)

MySQL также может использовать два отдельных индекса с index_intersect, поэтому создание этих индексов

(a); (b)

также ускорит запрос с помощью a = 1 AND b = 1, хотя и в меньшей степени, чем любое из вышеперечисленных решений.

Вы также можете прочитать эту статью в своем блоге:

Обновление 2:

Кажется, я наконец понял ваш вопрос:)

ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b);

Отлично подходит для a = 1 и b = 1, достаточно хорош для a = 1 AND b = 1

ALTER TABLE ... ADD INDEX (a, b);

Отлично подходит для a = 1 AND b = 1, почти отлично подходит для a = 1, для b = 1

ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b); ALTER TABLE ... ADD INDEX (a, b);

Отлично подходит для всех трех запросов.

Ответ 3

SQL будет выбирать индекс, который наилучшим образом охватывает запрос. Индекс на A, B будет охватывать запрос как для случая 1, так и для 3, но не для 2 (поскольку столбец первичного индекса равен A)

Итак, чтобы охватить все три запроса, вам нужны два индекса:

ALTER TABLE ... ADD INDEX (a, b); ALTER TABLE ... ADD INDEX (b)

Ответ 4

Для примера у вас есть индексный набор №3, оптимальный. Mysql выберет одиночные индексы A и B для одного столбца, где предложения, и используйте составной индекс для предложения A и B where.