MySQL. Не забудьте про мощность.

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

Через какое-то время, мы обнаружили, что дела идут не столь гладко, как мы себе представляли. Изыскания привели нас к ранее существующей проблеме. Тот же запрос, который мы недавно с такой легкостью оптимизировали, проявлял необузданную мешковатость. Выяснилось, однако,  что наше воздействие на него безотказным индексом совершенно не повлияло на производительность. Напротив, ситуация значительно ухудшилась. 

Ранее, в этом запросе, мы использовали проиндексированные текстовые поля для объединения таблиц. Теперь же мы связали их: с одной стороны целочисленным первичным ключом, а с другой — полем, репрезентирующим этот ключ в целевой таблице. Именно по этому полю был построен коварный индекс. 

Взглянув, наконец, на план запроса мы сильно удивились — вместо соединения по индексу, таблицу ждало полное сканирование. Как же так? 

Дело в мощности! Cardinality (мощность множества) —  число определяющее количество уникальных значений в индексе. Оно рассчитывается на основе статистики и не обязательно является точным, даже для небольших таблиц. Чем выше мощность, тем больше вероятность того, что MySQL использует индекс при объединении. Это свойство можно найти просмотрев статистику по индексам (SHOW INDEX FROM table_name)

Но что не так с мощностью? Все хорошо, покуда статистика обновляется. Но, операции обновления статистики, например ANALYZE TABLE, блокируют таблицу на которой они применяются и поэтому такие процедуры в рабочей БД крайне нежелательны.

В нашем случае, мы совершенно забыли про простое правило — если был применен ALTER TABLE на таблице имеющей множество записей, то стоит обновить статистику вызвав ANALYZE TABLE. Об этом свидетельствует официальная документация:

After an ALTER TABLE statement, it may be necessary to run ANALYZE TABLE to update index cardinality information

Выводы: 

- не забывать использовать EXPLAIN, чтобы удостовериться в корректности работы запросов после оптимизаций;
- помнить, что после изменения схемы может быть необходимо обновить статистику.

Ссылки:

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html 

https://dev.mysql.com/doc/refman/5.7/en/show-index.html

https://logicalread.com/mysql-index-cardinality-mc12/#.YWxCtxBBwRQ