Pull to refresh

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

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 миллионом записей.
Рассмотрим различное количество уникальных ключей.
Всего записей Уникальных записей rows_per_key Уникальных записей (по мнению оптимизатора)
1000000 100000 10 100000
1000000 200000 5 200000
1000000 300000 3 333333
1000000 400000 2 500000
1000000 500000 2 500000
1000000 600000 1 1000000
1000000 700000 1 1000000
1000000 800000 1 1000000
1000000 900000 1 1000000
1000000 1000000 1 1000000

Нравится?
НИКАКИХ причин терять точность КРОМЕ 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 и переписать с нуля.
Но это будет совсем другой продукт.
Tags:mysql performance
Hubs: MySQL
Total votes 100: ↑91 and ↓9 +82
Views28.2K

Popular right now

Программист (PHP, MySQL)
from 100,000 to 180,000 ₽WebDiscoveryRemote job
MySQL/PHP Application Developer
from 160,000 ₽Cbonds.ruСанкт-ПетербургRemote job
Backend разработчик PHP/GoLang/MySQL (Middle)
from 120,000 to 270,000 ₽Advex GroupRemote job
DBA / Database Administrator
from 4,000 $Tango LiveСанкт-Петербург
PHP-разработчик fullstack (Linux, Apache, MySQL, PHP)
from 250,000 ₽КАУССанкт-ПетербургRemote job