Несколько и единственные индексы
Я довольно стыжусь выяснения у этого, так как я работал с 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);
Спасибо (отмечают, что мы говорим о не уникальные индексы),
4 ответа
Да, по меньшей мере, один случай значительно медленнее. Если вы определяете только следующий индекс:
ALTER TABLE ... ADD INDEX (a, b);
... Затем запрос выберите * из ... где B = 1; не будет использовать этот индекс.
Когда вы создаете индекс с композитной клавишей, важен порядок столбцов ключа. Рекомендуется попытаться заказать столбцы в клавише, чтобы улучшить селективность, с самыми выборочными столбцами влево - большую часть ключа. Если вы этого не сделаете, и поместите не избирательный столбец в качестве первой части ключа, вы рискуете вообще не использовать индекс. (Источник: Советы по оптимизации композиционного индекса SQL Server
SQL выберет индекс, который лучше всего охватывает запрос. Индекс на A, B будет покрывать запрос для обоих случаев 1 и 3, но не для 2 (поскольку основной столбец индекса a)
так, чтобы покрыть все три запроса, вам нужны две индексы:
ALTER TABLE ... ADD INDEX (a, b); ALTER TABLE ... ADD INDEX (b)
Для примера у вас есть индексный набор № 3, является оптимальным. MySQL выберет один индексы A и B для одного столбца, где пункты, и используют сложный индекс для предложения A & B, где.
Весьма маловероятно, что простое существование индекса замедляет запрос SELECT: он просто не будет использован.
Теоретически оптимизатор может неправильно выбрать более длинный индекс на (a, b), а не на (a) для обслуживания запроса, который ищет только a.
На практике я никогда его не видел: MySQL обычно делает обратную ошибку, беря более короткий индекс, когда существует более длинный.
Обновление:
В вашем случае для всех запросов будет достаточно одной из следующих конфигураций:
(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);
отлично для всех трех запросов.