Проблемы MySQL оптимизатора

Что я успел понять про MySQL за несколько лет его разработки:

• развивать не ломая обратной совместимости его нельзя
• MySQL со сломанной обратной совместимостью никому не нужен.

Я опишу две серьёзные проблемы ДНК MySQL, с которыми косвенно сталкивается любой пользователь MySQL 5.1 и 5.5 (насчёт 5.6 не проверял, но не думаю, что этот момент поменялся).

Как вообще MySQL работает?

Есть сам MySQL — это

• парсер
• оптимизатор запросов
• репликация
• системный каталог

Есть и другие вещи, но основные — именно эти.

Есть Storage Engine: плагин, реализующий следующую функциональность:

• транзакции
• хранение таблиц на диске
• индексы
• статистика

Список не исчерпывающий, да и не очень корретно поделённый на обязанности, но для нашего рассмотрения удовлетворительный.

API для Storage Engine — это такая святая корова, которую нельзя трогать.
Я хочу привести один прекрасный, замечательный пример того, как этот Storage Engine API «прекрасно» сделан и какие с ним возникают проблемы.

Конкретно данные проблемы я изучил пока боролся с вот этим багом: bugs.mysql.com/bug.php?id=63320
Если кто чувствует в себе силы и желание разобраться как с багом, так с исходным кодом — могу пожелать удачи.
Кто ленивый и доверяет моему рассказу, могут просто читать дальше.

Итак, какую информацию оптимизатору может/умеет сообщать Storage Engine?

• количество записей в таблице (пусть это будет row_count)
• целочисленное значение rows_per_key (отношение количество_записей_всего к количество_уникальных_записей).

Обратите внимание — количество уникальных записей узнать напрямую нельзу.
MySQL считает его как количество_записей в таблице / rows_per_key

Запишем это в виде кода (как это в результате вычисляется):

int unique_row_count_mysql = row_count / rows_per_key;
int rows_per_key = row_count / unique_row_count_engine;
int unique_row_count_mysql = row_count / ((int) (row_count / unique_row_count_engine));

Поясняю:

a2 = (int)b / ((int) (b / a));

Просто из-за отсутствия данной функции мы теряем в точности округляя число два раза подряд.
Поясню пример. Пусть есть таблица c миллионом записей.
Рассмотрим различное количество уникальных ключей.

Нравится?
НИКАКИХ причин терять точность КРОМЕ Storage Engine API — нету.
Трогать API нельзя.
Данная проблема применима для любого Storage Engine.

Это полдела. Едем дальше.
Для выбора эффективного плана оптимизатору недостаточно знать количество уникальных записей и количество записей всего.
Часто* ему требуется знать количество NULL'ов, количество не NULL'ов, в случае составных ключей — различные сочетания.
Часто — это в следующих случаях:

• inner join («просто» join)
• outer join (left/right/full)
• subquery (in/not in/all/any/exists/not exists)

Имея статистику по null'ам, можно строить различные эффективные оптимизации.
Про это чуть позже, сначала опишем проблему.

В Storage Engine API _нет_ возможности различать null'ы от остальных записей.
Почти нет.
InnoDB и MyISAM реализуют две глобальных переменных: innodb_stats_method и myisam_stats_method.
Это глобальные переменные имеют следующие значения:

• nulls ignore
• nulls equal
• nulls not equal.

Когда я в первый раз это увидел в исходном коде, я не поверил собственным глазам.
Эти переменные влияют на сбор статистики для ВСЕХ таблиц и запросов имеющих null'ы в ключах join'ов и подзапросах.

Чем это плохо?
Вот есть простой запрос:

select * from a join b on a.id=b.id

Если стоит nulls ignore — всё отлично
Если стоит nulls equal — все отлично
Если стоит nulls not equal — всё очень плохо, оптимизатор выберёт пессимистичный план — предполагая, что записей на выходе джойна будет много.

Другой запрос

select * from a left join b on a.id=b.id

Если стоит nulls not equal — всё отлично
Если стоит nulls equal или nulls ignore — все очень плохо оптимизатор выберёт оптимистичный план — предполагая, что записей на выходе джойна будет мало.

С подзапросами ситуация бывает и так, и эдак.
Собственно говоря, баг, на который я ссылался — это про особенности вычисления числа записей исходя из настройки innodb_stats_method.
В архитектуре изначально не было предусмотрено разделение null'ов от всего остального, и теперь разработчики мучаются, придумывая более хитрые костыли и эвристики, чтобы промахиваться поменьше.

С моей точки зрения — это мёртвому припарки.
Оптимизатор MySQL, построенный поверх AST (abstract syntax tree) — мёртвый, его невозможно развивать и улучшать. Это огромная дыра, которая пожирает кучу времени и не даёт возможности улучшить производительность запросов.

Сами по себе Storage Engine быстрые, но вот пользоваться ими оптимизатор не умеет. Нет API, нет возможности, нет нормальной статистики.

Я не верю, что эта ситуация принципиально изменится. По сути нужно выкинуть значительный кусок MySQL и переписать с нуля.
Но это будет совсем другой продукт.

Автор: zabivator

Источник