нахождение оптимального порядка многоколоночного индекса

Итак, допустим, у меня есть таблица с двумя индексами – одна на столбце a и одна на столбцах a, b и c.

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

Этот вопрос задает вопрос … как определить идеальный порядок столбцов? Вам просто нужно переборщить его?

Порядок столбцов в индексе не имеет значения. Существует метод.

  • Во-первых, один или несколько столбцов, которые участвуют в терминах равенства в сочетании с И.

     WHERE a = 1 AND b = 2 
  • Во-вторых, один столбец участвует в диапазоне сроков. Либо это, либо один или несколько столбцов, участвующих в сортировке.

     WHERE a = 1 AND b = 2 AND c > 3 

    или

     WHERE a = 1 AND b = 2 ORDER BY c, d 
  • В-третьих, столбцы, на которые ссылаются в списке выбора, но на которые не ссылаются поиск или сортировка.

     SELECT x, y, z . . . WHERE a = 1 AND b = 2 AND c > 3 

Это приведет к индексу на (a, b, c, x, y, z) .

Я объясняю это более подробно в своей презентации. Как правильно проектировать индексы .

Как и когда MySQL использует индекс, он основан на статистике таблицы. Используя статистику, он может решить одно или другое. Вы можете заставить его использовать определенный индекс, но это, как правило, не очень хорошая идея. См. Эту ссылку для получения дополнительной информации.

Вот что нужно помнить, когда таблица растет и меняет изменение статистики. Таким образом, запрос может работать с одним индексом сегодня завтра. MySQL определит, какой из них лучше использовать. Вот почему форсирование индекса – это просто стрелять в ногу в какой-то момент.

Вы не можете создать идеальный индекс навсегда, поэтому вам нужно создать индекс, который поможет ускорить запрос, когда он станет медленным. Поэтому включите медленный журнал и выполните мониторинг. Когда запрос начинает работать медленно, выполните EXPLAIN, а затем выясните, как оптимизировать. О, и в какой-то момент вам, возможно, придется удалить индексы, поскольку они больше не могут использоваться.

Спросите, нужна ли вам дополнительная информация.