html текст
All interests
  • All interests
  • Design
  • Food
  • Gadgets
  • Humor
  • News
  • Photo
  • Travel
  • Video
Click to see the next recommended page
Like it
Don't like
Add to Favorites

Проблемы 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 и переписать с нуля.
Но это будет совсем другой продукт.
Читать дальше
Twitter
Одноклассники
Мой Мир

материал с habrahabr.ru

1

      Add

      You can create thematic collections and keep, for instance, all recipes in one place so you will never lose them.

      No images found
      Previous Next 0 / 0
      500
      • Advertisement
      • Animals
      • Architecture
      • Art
      • Auto
      • Aviation
      • Books
      • Cartoons
      • Celebrities
      • Children
      • Culture
      • Design
      • Economics
      • Education
      • Entertainment
      • Fashion
      • Fitness
      • Food
      • Gadgets
      • Games
      • Health
      • History
      • Hobby
      • Humor
      • Interior
      • Moto
      • Movies
      • Music
      • Nature
      • News
      • Photo
      • Pictures
      • Politics
      • Psychology
      • Science
      • Society
      • Sport
      • Technology
      • Travel
      • Video
      • Weapons
      • Web
      • Work
        Submit
        Valid formats are JPG, PNG, GIF.
        Not more than 5 Мb, please.
        30
        surfingbird.ru/site/
        RSS format guidelines
        500
        • Advertisement
        • Animals
        • Architecture
        • Art
        • Auto
        • Aviation
        • Books
        • Cartoons
        • Celebrities
        • Children
        • Culture
        • Design
        • Economics
        • Education
        • Entertainment
        • Fashion
        • Fitness
        • Food
        • Gadgets
        • Games
        • Health
        • History
        • Hobby
        • Humor
        • Interior
        • Moto
        • Movies
        • Music
        • Nature
        • News
        • Photo
        • Pictures
        • Politics
        • Psychology
        • Science
        • Society
        • Sport
        • Technology
        • Travel
        • Video
        • Weapons
        • Web
        • Work

          Submit

          Thank you! Wait for moderation.

          Тебе это не нравится?

          You can block the domain, tag, user or channel, and we'll stop recommend it to you. You can always unblock them in your settings.

          • habrahabr.ru
          • домен habrahabr.ru

          Get a link

          Спасибо, твоя жалоба принята.

          Log on to Surfingbird

          Recover
          Sign up

          or

          Welcome to Surfingbird.com!

          You'll find thousands of interesting pages, photos, and videos inside.
          Join!

          • Personal
            recommendations

          • Stash
            interesting and useful stuff

          • Anywhere,
            anytime

          Do we already know you? Login or restore the password.

          Close

          Add to collection

             

            Facebook

            Ваш профиль на рассмотрении, обновите страницу через несколько секунд

            Facebook

            К сожалению, вы не попадаете под условия акции