Исследуем производительность JOIN в MySQL

    Я думаю, ни для кого не секрет, что JOIN считается достаточно дорогой операцией, и многих начинающих программистов (которые юзают MySQL) любят запугивать, что JOIN — это плохо, и лучше всего обойтись без них, если есть возможность.

    Давайте исследуем этот вопрос более подробно и посмотрим, действительно ли JOIN — это плохо, и когда вообще стоит задумываться об этом.

    О чём я не буду писать


    Для начала я бы хотел сразу сказать, что я не буду делать:

    — тюнинг MySQL: все настройки берутся по умолчанию (в том числе innodb_buffer_pool_size = 8 Мб и прочее)
    — интеграцию с языками программирования: все запросы будут делаться через MySQL клиент Sequel Pro, и время будет замеряться исходя из его показаний
    — очевидные вещи, вроде джойна при выборке 3х строк: вопрос, экономить на спичках, или нет, я рассматривать не хочу — мы будем рассматривать экономию в десятки раз, а не десятки процентов

    Начальные условия


    У нас будет две простых до безобразия таблички (таблицы были сделаны просто для примера и заполнены случайными данными):

    Calls — 10 млн строк:
    id INT PRIMARY KEY AUTO_INCREMENT
    user_id INT
    cost INT
    call_dt DATETIME
    tariff_id INT

    Users — 100 тыс строк:
    id INT PRIMARY KEY AUTO_INCREMENT
    birthdate DATE
    name VARCHAR(10)
    sex ENUM('M','F')


    Названия, я думаю, говорят сами за себя, индексы есть только по первичному ключу (id). Чисто в принципе, мы бы могли создать какие-нибудь индексы, которые бы нам помогли выполнять те запросы, которые мы будем исследовать, но у нас цель другая, а именно — исследовать, насколько быстро работает JOIN.

    Типы таблиц


    Для целей исследования, таблица Calls бралась двух видов — MyISAM и InnoDB, а таблица Users, к которой мы делаем JOIN, трёх видов — MyISAM, InnoDB и MEMORY

    First blood


    Все тесты проводились на моём ноутбуке, с MySQL версии 5.5.9 на Mac OS X с дефолтными настройками от MAMP. Все таблицы вмещались в память полностью, запросы прогонялись несколько раз, чтобы убедиться, что всё попадает в кеш.

    Для начала, давайте просто посмотрим на скорость просмотра строк в MyISAM и InnoDB, выполнив такой запрос (напомню, что индексов ни по цене, ни по user_id нет — мы измеряем скорость FULL SCAN в MySQL):

    SELECT MAX(cost) FROM calls WHERE user_id = 5000; -- запрос №1

    И такой запрос:

    SELECT MAX(cost) FROM calls; -- запрос №2


    Результаты (погрешность менее 5%):
    InnoDB, ms MyISAM, ms
    1 5 360 862
    2 5 390 1 150

    Не хочу заниматься более подробным изучением, почему на аггрегирующие выборки в MyISAM так влияет кол-во попавших под WHERE строк, но факт остается фактом — при полном последовательном просмотре таблицы, MyISAM быстрее InnoDB в 4.5 раза. Отсюда и мнение о том, что InnoDB «тормоз», и о том, что сама MySQL (с MyISAM) очень шустра.

    Мини-вывод: при полном последовательном просмотре MyISAM в 5 раз быстрее InnoDB

    JOIN


    Давайте теперь подключим к делу таблицу Users — не зря же мы её создавали.
    Мы будем исследовать запросы такого плана:

    SELECT MAX(calls.cost) FROM calls
    JOIN users ON calls.user_id = users.id
    WHERE calls.cost > %d AND users.sex = 'M'

    Параметр для cost подбирается таким образом, чтобы под выборку попал определенный процент записей в таблице Calls

    Если мы сделаем запрос, приведенный выше, к мускулю, с параметром cost, который будет соответствовать N% строк, то MySQL сделает джойн всего-лишь в N% случаев, а для остальных (100-N)% строк он JOIN делать не будет. Вот такой MySQL умница.

    Давайте же приступим к результатам тестирования с JOIN:

    FULL SCAN + JOIN 0.1% строк

    Users \ Calls InnoDB, ms (только JOIN, ms) MyISAM, ms (только JOIN, ms)
    InnoDB 5 450 (~0) 857 (~0)
    MyISAM 5 450 (~0) 937 (~100)
    MEMORY 5 350 (~0) 845 (~100)

    Пока что времена отличаются очень несущественно от FULL SCAN. Оно и понятно — ведь JOIN делается для мизерного количества строк.

    FULL SCAN + JOIN 1% строк

    Users \ Calls InnoDB, ms (только JOIN, ms) MyISAM, ms (только JOIN, ms)
    InnoDB 5 660 (300) 999 (140)
    MyISAM 6 530 (1 200) 1 810 (950)
    MEMORY 5 460 (100) 911 (65)

    Забавно, да? Всего-лишь 1% строк джойнится, а результаты для MyISAM + MyISAM больше в 2 раза, чем для MyISAM + InnoDB. Довольно забавно, что JOIN к InnoDB в данном случае оказывается быстрее, чем JOIN к MyISAM. И это мы ещё не начали тестировать :)!

    FULL SCAN + JOIN 10% строк

    Users \ Calls InnoDB, ms MyISAM, ms
    InnoDB 7 230 (1 900) 2 190 (990)
    MyISAM 16 100 (8 800) 10 200 (9 000)
    MEMORY 6 080 (700) 1 440 (580)

    За державу (MyISAM) обидно, а что поделать… Выходит, MyISAM не такой уж шустрый… Или нет? Давайте посмотрим на результаты финального тестирования

    FULL SCAN + JOIN 100% строк

    Users \ Calls InnoDB, ms MyISAM, ms
    InnoDB 18 000 (14 650) 12 500 (11 655)
    MyISAM 100 000 (96 650) 91 600 (90 750)
    MEMORY 10 500 (7 150) 5 280 (4 435)

    Обратите внимание на чудовищные (!) времена выборок при JOIN с MyISAM. А вот InnoDB приятно удивил — благодаря своей архитектуре, JOIN не является слишком дорогой операцией для InnoDB. Если говорить честно, то я был сильно удивлен, когда получил такой результат, что второй по скорости JOIN вариант — это когда к MyISAM джойнят InnoDB.

    Ну а с MEMORY, я думаю, всё ясно — MEMORY дает оверхед в 525% (4 435 ms) на джойн по PK, InnoDB дает оверхед в 1 380% (11 655 ms), а про MyISAM стыдно говорить.

    Замена JOIN на IN(...)


    Зоркий глаз мог заметить, что для нашего сценария (когда мы делаем JOIN к users, чтобы отсеять всех женщин из таблицы звонков) есть способ без JOIN, а с простым перечислением всех user_id в IN():

    
    SET group_concat_max_len = 10000000;
    SELECT GROUP_CONCAT(id) FROM users WHERE sex = 'M'; -- запрос отработает за 50 мс, выдав много-много килобайт текста
    SELECT MAX(cost) FROM calls WHERE user_id IN(%s); -- %s из предыдущего запроса
    


    Такой запрос к таблице типа MyISAM отработает за 3 730 мс, а к InnoDB — за 8 290 мс. Зоркий глаз может заметить, что этот способ быстрее, чем JOIN к MEMORY, хоть и не намного. Этот способ подходит в случаях, если у вас очень быстрое подключение к MySQL (например UNIX socket). В остальных случаях, ИМХО, при таких количествах записей очевидно, что гонять огромное количество трафика между MySQL сервером и сервером приложений — не самая лучшая идея.

    Выводы можете сделать сами: если нужно много джойнить, и почему-то нужна высокая производительность, используйте комбинацию MyISAM + InnoDB, или просто InnoDB + InnoDB, если джойнов больше одного. Ну а если вы — экстремал, можете использовать MyISAM + MEMORY для получения максимальной производительности при любых сценариях. Есть ещё вариант с MEMORY+MEMORY, но для большого количества записей я бы не стал так делать ;).

    UPD: Хочется поблагодарить хабраюзера homm за очень полезные комментарии, например за этот. В общем, очень рекомендую прочитать комментарии, в них разъясняется много вещей, которые почему-то для читающих не были очевидны:
    — кэш запросов отключен
    — JOIN делается по первичному ключу
    — индексы на таблицу Calls не создаются, потому что мы не ставим задачу оптимизировать какой-то конкретный запрос
    Поделиться публикацией

    Похожие публикации

    Комментарии 90

      +4
      Масштабировать проще, если не используется join.
        0
        Зависит от задач… Хотя именно масштабировать проще, я согласен. А вот сохранить при этом высокую производительность — это отдельный вопрос (см. последний пункт к статье, который я в последний момент добавил :))
          +1
          сохранять высокую производительность тоже проще
          тесты немного оторваны от жизни. когда в пуле стоит сотни запросов, то каждый джоин — это выделение доп буферов под промежуточные результаты, запуск нового потока на каждую часть джоина, синхронизация и прочу доп. действий, которые не заметны — если нет нагрузки, нет гонки за ресурсы…
          да, в статье получается микрооптимизация…
          хотя статья интересна и в каком-то смысле даже позновательна. Спасибо автору за исследовательскую работу.
          0
          А табуретки проще выпускать квадратные и без ножек…
            +1
            Это другое, если подразумевается большая нагрузка об этом лучше сразу подумать, если нагрузка не планируется, конечно, удобнее использовать JOIN-ы.
            0
            Как раз делал сравнение PostgreSQL и MongoDb на примере join.
            Монго где то на 40-50% быстрее в среднем.
            Но вот по Insert проигрывает очень много- от 2-х до 5-ти раз (1к-1М записей).
            Возможно, такие результаты связаны еще и с тем, что в качестве оболочки для запросов использовался Python 2.6.

            И тем не менее, join (точнее его имитация, ведь как такового join в mongo нет) очень дорогая операция для любой реляционной БД.
              0
              А как эмулировали джойн?
            +15
            Из написанного стало понятно: use the index, Luke
              –1
              Не всегда возможно поставить индексы по колонкам, по которым производится поиск. К тому же, статья вообще не об этом. В тексте я специально указал, что индексы в данном случае можно было бы использовать, но я ставил целью измерить скорость работы JOIN в случае, если у нас много данных, а не что-то другое.
                +5
                Я бы согласился. Как обстоит скорость join`ов у разных движков с учётом адекватных индексов?
                  +1
                  В данном случае users присоединяется к calls по первичному ключу. Какие бы адекватные индексы вы бы хотели видеть еще?
                    0
                    хотелось-бы видеть индекс как минимум на столбце calls.user_id. не?
              0
              Ну и ещё, хотел бы заметить: посмотрите на времена выполнения запросов. И это к таблице с 10 млн записей! По-моему, очевидно, что бывают случаи, когда FULL SCAN будет быстрее, чем, скажем, INDEX MERGE или что-нибудь ещё с участием индекса.
                +3
                У MySql есть анализатор, который сам определяет такие случаи, и не использует индексы.
                Хотя он и может ошибаться, но делает он это реже нас, так что я доверяю.
                  0
                  Кто бы спорил, что он есть :). Но иногда бывает так, что он не понимает, как быстрее будет выполнить запрос, ибо у него просто не может быть какой-нибудь информации о структуре, которая, при этом, может быть у вас.
              +1
              А разве mysql не умеет подзапросы в IN(): SELECT MAX(cost) FROM calls WHERE user_id IN( SELECT id FROM users WHERE sex = 'M' )

              Зачем эти килобайты текста гонять, их ведь еще парсить потом.
                0
                Если внутри IN подзапрос то индекс не будет использоваться.
                  +4
                  Откуда дровишки?
                    0
                    dev.mysql.com/doc/refman/5.6/en/in-subquery-optimization.html

                    Consider the following subquery comparison:

                    outer_expr IN (SELECT inner_expr FROM… WHERE subquery_where)

                    MySQL evaluates queries “from outside to inside.” That is, it first obtains the value of the outer expression outer_expr, and then runs the subquery and captures the rows that it produces.
                    0
                    Присоединяюсь к zerkms. Если не сложно, поясните свой коммент. Или хотя бы ссылки на почитать.
                      0
                      По-моему, в этом случае проще всего посмотреть самому, «EXPLAIN SELECT MAX(cost) FROM calls WHERE user_id IN( SELECT id FROM users WHERE sex = 'M' )», не?
                        0
                        Для этого надо создавать таблички и наполнять их данными.
                        Кроме того, меня не очень интересует конкретный пример, больше теория, почему «Если внутри IN подзапрос то индекс не будет использоваться.»
                    0
                    MySQL перепишет это как JOIN, я проверял :)
                    +27
                    Тестирование производительности join без индексов чем-то похоже на тестирование скорости болидов формулы один, с незаведенными двигателями. Самую главную фишку выключили и вперед:)
                    Без обид, но правда, джоины в первую очередь тем и хороши, что позволяют все сшивать по индексам, а не делать тупой фуллскан. Статья несомненно представляет интерес, особенно в меру сравнения InnoDB и MyISAM, но уж очень теоретический, в меру редкости подобных вещей.

                    Новичкам действительно часто не советуют джоины, т.к. расставлять индексы и правильно джоинить это скилл определённого уровня. Но если новичек дорос до разговоров о производительности, то неплохо бы ему вначале научиться этому искусству.
                    Если Вы сможете добавить индексы и прогнать те же тесты на том же конфиге, то имхо будет намного интереснее. Особенно если Вы добавите еще хотя бы одну ситуацию, когда фуллскан быстрее использования индексов, как Вы упоминали тут. Заодно это было бы полезно как линк для новичков, в плане что почитать о пользе индексов.

                    p.s.: Конфигов mysql не осталось? Было бы интересно глянуть.
                      0
                      > p.s.: Конфигов mysql не осталось? Было бы интересно глянуть.
                      все настройки берутся по умолчанию (в том числе innodb_buffer_pool_size = 8 Мб и прочее)
                        +1
                        категорически согласен: смысла использовать SQL и при том не использовать индексы — ноль.

                        и вместо чтения исследований производительности без использования индексов (которое возможно и имеет какой-то сугубо теоретический смысл), новичкам лучше почитать про использование этих самых индексов. этот способ оптимизации гораздо лучшие, чем конвертирование табличек MyISAM <=> InnoDB.
                          +13
                          Ух, сколько людей не поняли сути происходящего, судя по плюсам. Объясняю.
                          Запрос выглядит так: FROM calls JOIN users ON calls.user_id = users.id Т.е. Join делается с индексом — первичным ключем таблицы users.

                          Индексы намеренно не используются для выборки значений из таблицы calls. Это сделано для того, чтобы померить только скорость джойна, а не выборки нужных строк из calls. Автор замерил скорость full scan для этой таблицы, и затем замерял скорость того же full scan + join для какого-то процента записей. Т.е. время чистого джойна можно найти вычитанием одного времени из другого.

                          Если бы для поля, по которому делается выборка в таблице calls, использовались бы индексы, то на маленьком проценте строк мы бы получили быстрый выбор строк и быстрый join, но все равно почти все время можно было би считать временем join. А вот на большом проценте строк все было бы не так очевидно, ведь с определенного момента mysql перестал бы использовать индексы для поиска строк таблицы calls, и мы бы вернулись к full scan, т.е. время запроса было бы уже суммой full scan + join. При этом на в промежутках было бы непонятно, сколько собственно времени занимает join, а сколько выборка строк.
                            0
                            возможно вы и правы, но все же неплохо было бы увидеть цифорки.
                              +13
                              4 8 15 16 23 42

                              В топике полно цифр, что бы вы еще хотели увидеть?
                                0
                                Время выполнения с индексом по calls.user_id.

                                Хотя подозреваю, что этот индекс ничего не даст. Но всё же.
                                  +1
                                  Этот индекс действительно ничего не даст, это поле не используется для выбора записей. Вот индекс по calls.cost мог бы помочь в первом случае, для 0.1% строк.
                              –1
                              спасибо за пояснение. лично я, при таком сравнении циферок, как приводится в статье, этого сразу не понял (тупой, да).

                              в таком представлении подобное тестирование имеет смысл для случаев, когда не представляется возможным добавить такие индексы, чтобы join их использовал.

                              я попытался воспроизвести тест и попробовал добавить индекс на calls.user_id. и от него стало только хуже (глядя на результаты explain становится понятно почему).

                              однако составной индекс на столбцы user_id + cost решает проблему для данного запроса и он начинает выполняться существенно быстрее (тоже понятно почему). у меня время выполнения запроса с 100% для MyISAM уменьшилось в 40 раз (с ~130 сек до ~3 сек).

                              т.е. я продолжаю считать, что нужно думать над теми запросами, которые будут выполняться и создавать необходимые индексы, которые будут помогать выполнять запросы (и это касается не только join-ов).
                              если есть запросы, которые не могут быть оптимизированы добавлением индексов, и при этом данные запросы приводят к полному сканированию таблиц с миллионами записей — самое время вмешиваться в архитектуру.

                              P.S. ничего против InnoDB я не имею. сказанное мной выше относится и к ним.
                                +7
                                Нет, нет, нет, все не так, все не о том.

                                Смотрите, как называется топик: «Исследуем производительность JOIN в MySQL». Не строим оптимальные запросы, ни учимся ставить индексы, исследуем производительность JOIN. Что делает автор? Он берет огромную таблицу и заставляет mysql пройтись по ней всей. Это называется full scan. Потом, он в эту же таблицу подмешивает join по первичному ключу из другой таблицы. Время выборки дофига строк нам известно, теперь нам известно время подключения другой таблицы.

                                Берем для разного количества строк вычитаем время на full scan, откладываем по оси y, а по оси x — количество строк, для которых был сделан join. Получаем:

                                twitpic.com/5e6und

                                Видим, что график линейный, а значит скорость join для одной строки не зависит от количества строк и mysql (я взял данные для innodb) способен делать join для 800к строк в секунду.

                                Не знаю, как вам, а для меня это полезная информация.
                                  –2
                                  OK, OK, я влез в дискуссию не о том.

                                  P.S. когда обсуждается, «микроскопом А или микроскопом Б лучше забивать гвозди?», то с бредовым предложением забыть про микроскоп (тип таблиц) и воспользоваться молотком (индексами) в дискуссию лучше не влезать, ибо она тут не к месту.

                                    0
                                    Слушайте, ну это уже не смешно. Для JOIN используются индексы, я честно вам говорю.
                                      0
                                      я вам верю.
                                      мы просто говорим про разные вещи.
                                      предлагаю завязать.
                                        –1
                                        Вы говорите, что вместо того, чтобы мерить скорость джойнов для разных типов таблиц нужно использовать индексы. Но они уже используются. Почему о разных то?
                              0
                              Я рассмотрю любые предложения, какие индексы поставить для данного запроса с FULL SCAN к Calls, с учетом того, что доп. поля в таблицу calls добавлять нельзя.
                                –2
                                У вас такой запрос, что хоть заиндексируйся — прироста не будет. Из users вы дергаете примерно половину записей (предполагаю, что мужчин и женщин там поровну), а потом все это соединяете со своими 10 миллионами записей в calls.
                                Имхо, от такого исследования толку нет — в рабочей системе таким запросам явно не место. И начинающим программистам (для которых, как я понимаю, вы это все и делали) лучше дать совет использовать соединения там где они нужны, используя при этом правильные индексы и думать над тем, что реально выполняет их запрос.
                                  +2
                                  ну почему-же… чуть лучше станет если добавить, например, вот такой составной индекс:
                                  create index ix_calls_0 on calls (cost, user_id);

                                  а ещё у меня чуть быстрее получилось, если запрос переписать так:
                                  SELECT COUNT(cost) FROM calls INNER JOIN (SELECT id FROM users WHERE sex = 'M') a ON (calls.user_id = a.id) WHERE calls.cost > %d;

                                  последнее ускорило и join MyISAM+MyISAM и InnoDB+InnoDB (извращения типа MyISAM+InnoDB я не проверял)
                                    0
                                    не совсем верно выразился: индексы на users к увеличению производительности не приведут. Просто я его писал в контексте того комментария, на который давал ответ. А там было условие оставлять FULL SCAN на calls.
                                      0
                                      Поставил плюс за оригинальный INNER JOIN
                                      +2
                                      > И начинающим программистам лучше дать совет использовать
                                      > соединения там где они нужны
                                      Дайте! С своей статье, например. Эта же статья преследует другие цели и весьма полезна.
                                  –1
                                  MyISAM + InnoDB, который работает в 2 раза быстрее, чем MyISAM + MyISAM — странный способ оптимизации. Ведь связей много разных в т.ч. часто приходится делать и обратную связь ( `users` join `calls` ) — в результате получится InnoDB + MyISAM.

                                  Кроме того, из статью очевидно, что при джоинах используются какие-то внутренние алгоритмы позволяющие максимально использовать внутреннюю архитектуру движков. В таком случае, добавление индексов может полностью всё изменить.
                                    +24
                                    Я честно признаюсь — поставил статье минус.

                                    Тут просто ну совсем не в кассу — InnoDB и MyISAM настолько разные, что на дефолтных настройках тут даже говорить нечего. InnoDB на 99% зависит от innodb_buffer_pool. Это его пулл данных, а данные он любит иметь в памяти, поэтому все кто работают хоть сколько-то с InnoDB — всегда подстраивают. Потому что это главная фишка самого InnoDB. А когда у вас 10 млн. записей и вы даёте буффер в 2% от объёма базы — конечно вы будете жуть как упираться в джоины, подгрузку индексов и.т.д. В отличии от MyISAM — у InnoDB индексы и данные идут в innodb_buffer_pool — у вас даже primary key индекс в память не влазит — приходится с диска сканировать. А у MyISAM всё это дело настраивается отдельно и имеет свои отдельные буфферы и в таком варианте конечно работает быстрее.
                                    В общем как по мне — обсалютно мимо.
                                      +1
                                      Для таблицы Calls не влазит, а вот для Users — влазит целиком. Поэтому, ИМХО, действительно стоит вычеркнуть результаты InnoDB + InnoDB (что, в общем-то, итак видно по получившимся цифрам)
                                        0
                                        В любом случае данные приходится постоянно читать с диска мизерными пачками и все оптимизации InnoDB улетают в трубу. А поскольку это транзакционный движок (в отличии от MyISAM), то и накладных расходов в этом случае у него много.
                                      0
                                      join без индекса — это ужас )

                                      напомню, что вариантов join просто немалое количество (full scan, hash, merge, как минимум).
                                        +2
                                        Join с индексами, без индексов просмотр таблицы calls, все правильно.
                                        0
                                        В силу разности архитектуры этих поисковых движков интереснее было бы сравнивать не MyISAM и InnoDB, а каждый из них с со своими пропатченными версиями. Я имею ввиду InnoDB vs XtraDB и Aria против MyISAM. Имхо это было бы более объективно и полезно.
                                          +2
                                          > запросы прогонялись несколько раз, чтобы убедиться, что всё попадает в кеш.

                                          Если хочется тестировать скорость JOIN (пусть и сферическую, без индексов, в вакууме), то правильнее было-бы отключить кэширование (опциями в запросе), чтобы тестировать именно скорость JOIN, а не то, какой именно запрос кэшируется и как быстро работает кэш в разных случаях.
                                            +1
                                            Кеширование запросов, очевидно, отключено, иначе были бы совсем другие цифры.
                                          • НЛО прилетело и опубликовало эту надпись здесь
                                              +1
                                              тем более что, скорее всего результаты:
                                              двух MyISAM скорее всего не влезли в буфер,
                                              как и результаты двух InnoDB.
                                              а их пересечение использовало два буфера и они влезли. хотя это все еще надо тестировать…
                                            • НЛО прилетело и опубликовало эту надпись здесь
                                                +6
                                                1. Использование InnoDB на дефолтных настройках.
                                                2. Во-вторых, использование JOIN без индексов.
                                                3. При включенном кэше была измерена скорость именно кэша, а не JOIN.
                                                4. Во всех ваших таблицах MyISAM рвет InnoDB, а в комментах вы пишете обратное.
                                                5. Использование

                                                В общем вердикт такой: вы сделали неправильно все, что только можно было сделать при тестировании. А потом еще умудрились перепутать результаты.
                                                  0
                                                  5. Использование далеко не лучшего инструмента для замеров.
                                                    +3
                                                    2. JOIN с индексами!
                                                    3. Кеш выключен (согласен, автору нужно было указать)
                                                    4. MyISAM рвет в FULL SCAN. Вычитаем из обоих время FULL SCAN, получаем что в InnoDB JOIN действительно быстрее (согласен, автору нужно было сразу написать и абсолютные цифры и за вычетом FULL SCAN)
                                                    5. Это действительно важно, когда счет идет на секунды, а не на микросекунды?
                                                    +4
                                                    JOIN С ИНДЕКСАМИ! Люди, где ваши глаза?
                                                      +2
                                                      В первой таблице идет MyISAM потом InnoDB, во всех остальных — наоборот, что сбивает
                                                        0
                                                        Спасибо за замечание, поправил :)
                                                        +4
                                                        Товарищи, которые считают, что innodb_buffer_pool_size влияет на скорость FULL SCAN, поставьте пожалуйста плюс этому комментарию. Таблица Users в пул InnoDB, при этом, влезала целиком.

                                                        Если найдется много «верующих» в то, что результаты FULL SCAN зависят от этой опции — я сделаю отдельный тест. Пожалуйста всем, кто считает, что что-то изменится, пожалуйста, приложите предлагаемые опции для InnoDB.

                                                        Кто знает, как заставить MyISAM делать более быстрый JOIN — тоже буду очень рад услышать.

                                                        P.S. Размер таблицы Calls в InnoDB — около 500 Мб, в MyISAM — примерно столько же (с учетом размера индексов). Users — 5 Мб
                                                        • НЛО прилетело и опубликовало эту надпись здесь
                                                            0
                                                            Ээээ…
                                                            1) Какие индексы вы предлагаете поставить???
                                                            2) кеш запросов MySQL выключен, а про какие ещё кеши вы говорите, я не очень понимаю
                                                          –1
                                                          Можно же поставить дополнительные индексы — и ощутить разницу.
                                                            0
                                                            Насчёт траффика тут двоякая ситуация. Когда таблицы джойнятся с отношениями многих к одному, траффика в обратную сторону больше выйдет.
                                                              0
                                                              1. Хотелось бы видеть планы всех запросов.
                                                              2. Сколько мегабайт суммарно весят все ваши данны (и сколько индексы?) Хотелось бы видеть результаты для больших объемов. Для случаев, когда все данные в память запихать не удается, и джойт делается на диске.
                                                                –1
                                                                Весьма печально видеть, как пост о тестировании производительности чего-то, каких-то запросов в СУБД, в котором НИ СЛОВА не сказано о планах запросов, автор измеряет только время, измеряет его на своем железе, конфиг которого мы не знаем, набирает столько плюсов от некомпетентных читателей.
                                                                  0
                                                                  Я уж не спрашиваю, а не запускал ли автор часом свои запросы один после другого, когда данные все лежат в кеше (например, кеше файловой системы, ибо я ОЧЕНЬ сомневаюсь, что у автора на ноуте стоит raw storage device).
                                                                    0
                                                                    План запросов не очевиден? Конкретные характеристики играют роль, при сравнении на одной машине?

                                                                    Расскажите, на примере, что мы могли бы теоретически узнать из железа и плана запросов такого, что в корне изменило бы выводы статьи?
                                                                      +1
                                                                      А вы когда-нибудь слышали такое понятие «инжерерный подход к измерению и повышению производительности»? Видите ли, я навидался кода людей, которым «очевиден план запроса». И людей, которые полагают что знают, каким он должен быть (правда, это было на оракле).

                                                                      Конкретные характеристики — например, размер оперативки, размер кеша процессора, сколько памяти выделено под мускул? Таблица все загружается в память? Индексы все загружаются в память? Cache hit ratio какой? Общее потребление памяти в процессе джойна? IO — узкое место любой нагруженной БД, если только она не игрушечного размера и не держится вся в памяти. Что про IO? Какой жесткий диск? Какая нагрузка на него идет в процессе джойна?

                                                                      Если копнуть, то я могу много чего сказать. Например вот — «Результаты (погрешность менее 5%):». Это откуда? «Я гарантирую это!»?

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

                                                                      если нужно много джойнить, и почему-то нужна высокая производительность, используйте комбинацию MyISAM + InnoDB, или просто InnoDB + InnoDB, если джойнов больше одного. Ну а если вы — экстремал, можете использовать MyISAM + MEMORY для получения максимальной производительности при любых сценариях. Есть ещё вариант с MEMORY+MEMORY, но для большого количества записей я бы не стал так делать ;).
                                                                        0
                                                                        > Таблица все загружается в память?
                                                                        Судя по её размеру — да.

                                                                        > Cache hit ratio какой?
                                                                        Кеш отключен, 10 раз сказано.

                                                                        > Общее потребление памяти в процессе джойна?
                                                                        По-моему запрос специально выбран такой, чтобы память для результатов вообще не требовалась. Это видно же.

                                                                        Итого: во всех тестах измерялась скорость работы с данными, а не скорость подгрузки их с диска, что и являлось первоначальной задачей. Разве нет?
                                                                          0
                                                                          Кеш чего? Буферный кеш таблиц? Кеш индексов? Кэш распарсенных планов? Query result cache?
                                                                            –1
                                                                            А зачем что-то кроме Query result cache отключать для теста?
                                                                              0
                                                                              Видите ли, если вы выполните один и тот же запрос с отключенным резалт кешем, но включанным буферным кешем (так это называется в оракле, как в мускуле не помню), два раза после старта сервера, то второй будет внезапно, гораздо быстрее. Внимание! Почему?
                                                                                0
                                                                                Ну вы уж вспомните, пожалуйста, или где-нибудь посмотрите, а то мы вообще не узнаем, о чем речь.
                                                                                  0
                                                                                  Кеш, в котором лежат блоки данных, из таблиц, индексов, и прочих объектов. Не result cache.
                                                                                    +2
                                                                                    В общем, ваша надменность немного огорчает. Если вы знаете Oracle, то пожалуйста, перед тем, как лезть в темы про MySQL, постарайтесь ознакомиться с матчастью. В MySQL нет такого кеша — см. мой комментарий ниже.
                                                                                  +1
                                                                                  В MyISAM, например, нет кеша для данных, а для InnoDB есть, а для MEMORY это понятие вообще не имеет смысла. Запросы прогонялись несколько раз, чтобы всё попало в мифический «буферный кэш в MySQL», и времена брались для запросов, которые уже прокешировались.
                                                                              0
                                                                              Таблица все загружается в память?
                                                                              Судя по её размеру — да.

                                                                              А вот чтоб не СУДИТЬ, а ЗНАТЬ, я и спросил характеристики.

                                                                              По-моему запрос специально выбран такой, чтобы память для результатов вообще не требовалась. Это видно же. Итого: во всех тестах измерялась скорость работы с данными, а не скорость подгрузки их с диска, что и являлось первоначальной задачей. Разве нет?

                                                                              Нда. Память нужна для самой операции джойна, ну да ладно.

                                                                              Если измеряется скорость работы с данными, без учета скорости диска, тогда зачем эта загрузка с диска происходит вообще, зачем отключен кеш?
                                                                              Именно IO — часто узкое место. У вас может быть 80% времени это чтение блоков данных из датафайла, и 10% времени работа с данными в памяти. Вы это хотите мерять?
                                                                                0
                                                                                > Память нужна для самой операции джойна
                                                                                По первой таблице fullscan, результат не накапливается, сохраняется только одна переменная — максимальное число. Поиск строки для присоединения происходит по ключу для каждой (или не каждой) строки. Что я упускаю?

                                                                                > тогда зачем эта загрузка с диска происходит вообще
                                                                                Какая загрузка?

                                                                                > зачем отключен кеш?
                                                                                Чтобы на 2-й такой-же запрос база не ответила за 0,2 мс, и можно было посчитать среднее время выполнения запроса. Уж такой-то профи должен знать.

                                                                                > Именно IO — часто узкое место.
                                                                                Спасибо, капитан.
                                                                          +1
                                                                          Так.

                                                                          1) EXPLAIN я могу показать, но из него вы мало чего увидите. Именно по причине, что EXPLAIN этих SELECT'ов не давал никакой полезной информации, я и не стал их приводить. Мускул это вам не оракл. Какой план был выбран для выполнения запроса, можно легко увидеть по временам исполнения запросов.
                                                                          2) Зачем вам конфиг моего железа? Я написал, что это мой ноутбук с Mac OS X. Памяти (ОЗУ) достаточно, чтобы всё влезало в дисковый кеш. Результаты прогонялись несколько раз, загрузка CPU всё время была >=100% на всё время выполнения запроса.
                                                                          3) Конфиг мускуля выбран дефолтный: я согласен, я мог бы и потюнить базу, и в следующий раз так и сделаю, чтобы результаты для InnoDB (и, возможно, MyISAM) были более объективные. Я проводил тесты и на базе, затюненной под MyISAM, и результаты для MyISAM и MEMORY получились примерно такие же (InnoDB я не тестировал).

                                                                          Далее.

                                                                          Я не делал тестов по Oracle, ибо я не разбираюсь в том, как работает Oracle. Но зато я разбираюсь в том, как работает MySQL, по моему мнению, достаточно хорошо, чтобы давать такие тесты, которые дают ПРИМЕРНУЮ оценку производительности различных типов JOIN.

                                                                          «Погрешность менее 5%» основана на воспроизводимости результатов. Запросы прогонялись несколько раз. Результат первого запроса игнорировался (чтобы всё попало в кеш (не Query Cache, который отключен) MySQL и ФС), и бралось среднее значение от нескольких следующих запросов. Времена укладывались в 5%.

                                                                          Ещё раз повторяю про IO — таблица и индексы попадали целиком в кэш ФС. Т.е. это означает, что ВСЁ В ПАМЯТИ, НИКАКОГО I/O.
                                                                          0
                                                                          Понятно. youROCK, homm — спасибо за ваши комментарии и пояснения. Дальше читатали пусть делают из комментариев выводы сами. Не хочу выглядеть надменным.
                                                                          • НЛО прилетело и опубликовало эту надпись здесь
                                                                              0
                                                                              Эти условия при тестировании были итак соблюдены.
                                                                              0
                                                                              а как вы заполняли таблицу? просто рандомом?
                                                                              innoDB же может использовать локальность данных.
                                                                              к тому же у innoDB быстрее в других, не связанных с полным сканированием таблицы (работа с диапазоном значений, к примеру, что гораздо чаще используется).
                                                                              к тому же, наличие транзакций по-моему перекрывает всё остальное в любом случае.
                                                                                0
                                                                                Да, рандомом. Ну и также понятно, что у InnoDB есть куча своих сильных сторон — тот же быстрый JOIN. Но я не ставил целью вообще сравнивать движки хранения данных, а исследовал скорость JOIN, чтобы каждый мог сам для себя решить, нужно им использовать эту функциональность СУБД :)
                                                                                0
                                                                                а как вы заполняли таблицу? просто рандомом?
                                                                                innoDB же может использовать локальность данных.
                                                                                к тому же у innoDB быстрее в других, не связанных с полным сканированием таблицы (работа с диапазоном значений, к примеру, что гораздо чаще используется).
                                                                                к тому же, наличие транзакций по-моему перекрывает всё остальное в любом случае.
                                                                                  0
                                                                                  пардон, что-то у меня подвисло. камент выше

                                                                                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.