Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 49
А вы не пробовали проводить те же тесты с объёмом буфера innodb бОльшим размера индексов? По-крайней мере так рекомендуют спецы Percona в своём блоге по оптимизации мускула. Всё-таки, для продакшн БД в 125 кк записей предполагается и выделение мощного сервера с соответствующим объёмом ОЗУ — тот же хецнер 12GB RAM — 69 euro/mo).
Именно для того чтобы MySQL не мог читать данные из буфера я и генерировал такой большой объем записей, ибо это просто пример, неудачных решений используемых MySQL для оптимизации запросов. Наиболее характерно данные ошибки проявляются именно при такой настройке БД.
На промышленной БД у вас далеко не одна таблица и вы далеко не один пользователь. Так что все буфера будут делиться между ними в каких-то пропорциях.
Если все будут сканировать таблицу размером 10Gb то никаких ресурсов не хватит.
Где-то на хабре был замечательный пост, где всю БД засунули в оперативную память. Это конечно выход, но далеко не самый удачный.
Этой статьей и последующим циклом (если конечно попрет) я попытаюсь описать как можно заставить MySQL работать с большими базами данных, на совершенно мизерных объемах доступных ресурсов.
И вообще я как разработчик ужасно жадный до ресурсов особенно таких дорогих как ОЗУ, и пытаюсь выкраивать каждый байт.
На промышленной БД у вас далеко не одна таблица и вы далеко не один пользователь. Так что все буфера будут делиться между ними в каких-то пропорциях.
Если все будут сканировать таблицу размером 10Gb то никаких ресурсов не хватит.
Где-то на хабре был замечательный пост, где всю БД засунули в оперативную память. Это конечно выход, но далеко не самый удачный.
Этой статьей и последующим циклом (если конечно попрет) я попытаюсь описать как можно заставить MySQL работать с большими базами данных, на совершенно мизерных объемах доступных ресурсов.
И вообще я как разработчик ужасно жадный до ресурсов особенно таких дорогих как ОЗУ, и пытаюсь выкраивать каждый байт.
Прёт — описывайте :)
К сожалению на написание данной статьи я потратил 3 дня, ввиду того что каждый запрос выполняетя далеко не за 10 минут, с учетом того, что я знал о чем писать и как. Так что думаю через недельку напишу как сделать быстрый paging для отображения результатов многокритериального поиска по обеим множеством сущностей со связями один ко многим, при наличии миллинов записей в обеих таблицах, там постараюсь осветить проблемы limit, distinct и order by а так же партиционирования и денормализации.
вы понимаете разницу между O(N) и O(lg N)?
разница между фуллсканом и обращением к индексу как раз в этом и заключается. понимаете, фуллскан вместо обращения к индексу это очень-очень плохо. если вы будете делать запрос 5 раз вместо одного, то у вас будет медленней в 5 раз. можно купить в 5 раз больше серверов и всё будет работать с той же скоростью. но если вы делаете фуллскан, и у вас много данных/они растут, то вы никогда не добьётесь такой же скорости, которую даёт индекс.
предлагать исправлять косяк в алгоритме с помощью настроек и буферов — это феноменальная глупость. прошу прощения за прямоту.
разница между фуллсканом и обращением к индексу как раз в этом и заключается. понимаете, фуллскан вместо обращения к индексу это очень-очень плохо. если вы будете делать запрос 5 раз вместо одного, то у вас будет медленней в 5 раз. можно купить в 5 раз больше серверов и всё будет работать с той же скоростью. но если вы делаете фуллскан, и у вас много данных/они растут, то вы никогда не добьётесь такой же скорости, которую даёт индекс.
предлагать исправлять косяк в алгоритме с помощью настроек и буферов — это феноменальная глупость. прошу прощения за прямоту.
вот тут сложно не согласиться!
думаю demark имел ввиду особенность движка InnoDB, которая позволяет ему работать быстро только в том случае — когда все данные помещаются в оперативную память (я вот к примеру слышал что PostgeSQL работает по иным алгоритмам и ему не надо так много ОЗУ)
Во всех статьях по оптимизации СУБД MySQL как раз делаеться упор на то, что надо выделить БОЛЬШЕ ресурсов, ибо при низких значениях inno_db пулов этот engine начитнает работать из рук вон плохо.
думаю demark имел ввиду особенность движка InnoDB, которая позволяет ему работать быстро только в том случае — когда все данные помещаются в оперативную память (я вот к примеру слышал что PostgeSQL работает по иным алгоритмам и ему не надо так много ОЗУ)
Во всех статьях по оптимизации СУБД MySQL как раз делаеться упор на то, что надо выделить БОЛЬШЕ ресурсов, ибо при низких значениях inno_db пулов этот engine начитнает работать из рук вон плохо.
> при низких значениях inno_db пулов этот engine начитнает работать из рук вон плохо.
справедливо.
но на оптимизаторе это никак не сказывается, и план запроса не зависит от того, влезли ли индексы в память или не влезли.
а конкретное время выполнение запросов уже не так важно, по-моему. вернее, оно тоже важно, но про оптимизацию буферов есть десятки статей, а про особенности оптимизатора их как-то меньше. вообще первая на моей памяти, если точнее.
справедливо.
но на оптимизаторе это никак не сказывается, и план запроса не зависит от того, влезли ли индексы в память или не влезли.
а конкретное время выполнение запросов уже не так важно, по-моему. вернее, оно тоже важно, но про оптимизацию буферов есть десятки статей, а про особенности оптимизатора их как-то меньше. вообще первая на моей памяти, если точнее.
Поставила плюсик за «но на оптимизаторе это никак не сказывается, и план запроса не зависит от того, влезли ли индексы в память или не влезли.»
А по поводу статей: если читаете на английском могу порекомендовать блог Sergey Petrunia. Он бывший разработчик MySQL Optimizer, там много чего интересного про оптимизацию запросов есть.
А по поводу статей: если читаете на английском могу порекомендовать блог Sergey Petrunia. Он бывший разработчик MySQL Optimizer, там много чего интересного про оптимизацию запросов есть.
действительно есть пара интересных статей, и что немаловажно с картинками, что сильно улучшает восприятие.
жаль половина статей там только про MySQL 6.0 в котором полностью переписали оптимизатор (который вроде как уже свернули), PostgreSQL и MariDB.
Еще подобных ресурсов не знаете? По MySQL напрочь отсутствуют данные подобного рода в internet. И все изыскания приходится делать методом проб и ошибок, что конечно частенько сказывается на качестве получаемого результата.
жаль половина статей там только про MySQL 6.0 в котором полностью переписали оптимизатор (который вроде как уже свернули), PostgreSQL и MariDB.
Еще подобных ресурсов не знаете? По MySQL напрочь отсутствуют данные подобного рода в internet. И все изыскания приходится делать методом проб и ошибок, что конечно частенько сказывается на качестве получаемого результата.
> жаль половина статей там только про MySQL 6.0 в котором полностью переписали оптимизатор (который вроде как уже свернули)
Его не совсем свернули, а потихоньку бэкпортят в 5.6.
Не знаю больше подобных ресурсов. Можно на planet.mysql.com подписаться: периодически кто-то пишет на эту тему, но не постоянно. Вообще про оптимизацию в мануале достаточно хорошо написано. Я периодически пишу на тему отладки приложений, но мне казалось, что про оптимизацию как раз свободной информации достаточно.
Его не совсем свернули, а потихоньку бэкпортят в 5.6.
Не знаю больше подобных ресурсов. Можно на planet.mysql.com подписаться: периодически кто-то пишет на эту тему, но не постоянно. Вообще про оптимизацию в мануале достаточно хорошо написано. Я периодически пишу на тему отладки приложений, но мне казалось, что про оптимизацию как раз свободной информации достаточно.
Вы уверены, что это ответ мне? Я как раз и пишу, что нужно использовать индексы, но как они в конкретном примере могут быть эффективны, если размер индексов big_table.ibd почти 3ГБ, а выдлено под буфер innodb всего 300МБ?
это совершенно точно ответ вам.
> Тут, я думаю, каждый девелопер должен сам для себя решить: «Стоит ли овчинка выделки». Лично я считаю, что нет.
> Тут, я думаю, каждый девелопер должен сам для себя решить: «Стоит ли овчинка выделки». Лично я считаю, что нет.
Тогда пожалуйста процитируйте, где я утверждаю обратное вашему:
разница между фуллсканом и обращением к индексу как раз в этом и заключается. понимаете, фуллскан вместо обращения к индексу это очень-очень плохо
статья — про поиск фуллсканом и про поиск по индексу
вы посоветовали поменять настройки и купить сервер у хетцнера за 69 евро в месяц (кстати, впаривать — неприлично!)
это всё не имеет никакого отношения к статье, потому что фуллскан всё равно будет фуллсканом, даже если памяти много, и поиск по индексу всё равно останется поиском по индексу.
ваш совет, безусловно, полезен. и он довольно универсальный. но эта статья — не о том, как выбирать и конфигурировать сервер для mysql, а об асимптотиках и о работе оптимизатора запросов mysql при работе с одной конкретной конструкцией, содержащейся в запросе.
вы посоветовали поменять настройки и купить сервер у хетцнера за 69 евро в месяц (кстати, впаривать — неприлично!)
это всё не имеет никакого отношения к статье, потому что фуллскан всё равно будет фуллсканом, даже если памяти много, и поиск по индексу всё равно останется поиском по индексу.
ваш совет, безусловно, полезен. и он довольно универсальный. но эта статья — не о том, как выбирать и конфигурировать сервер для mysql, а об асимптотиках и о работе оптимизатора запросов mysql при работе с одной конкретной конструкцией, содержащейся в запросе.
Алексей, неприлично безаргументированно в чём-то упрекать.
У любого профессионала по БД, я полагаю, после просмотра параграфа «З.Ы.» появился бы закономерный вопрос: почему такой маленький объём буфера innodb по сравнению с размером индексов. Раз индексы есть, значит, разумно предположить, их используют. Мы не рассматриваем случай, когда оные есть и они не используются.
Поэтому и последовал вопрос про буфер (не совет), основываясь на советах спецов из Перконы по оптимизации, при использовании движка innodb.
Во-вторых, впаривают, когда указывают реф-ссылку. А тут, пример низкой стоимости сервера с большим объёмом ОЗУ.
В-третих, я задал вопрос, стоят ли эта экономия затраченных сил на такую оптимизацию и не будет ли более эффективным привести настройки сервера в соответствии с задачами.
У любого профессионала по БД, я полагаю, после просмотра параграфа «З.Ы.» появился бы закономерный вопрос: почему такой маленький объём буфера innodb по сравнению с размером индексов. Раз индексы есть, значит, разумно предположить, их используют. Мы не рассматриваем случай, когда оные есть и они не используются.
Поэтому и последовал вопрос про буфер (не совет), основываясь на советах спецов из Перконы по оптимизации, при использовании движка innodb.
Во-вторых, впаривают, когда указывают реф-ссылку. А тут, пример низкой стоимости сервера с большим объёмом ОЗУ.
В-третих, я задал вопрос, стоят ли эта экономия затраченных сил на такую оптимизацию и не будет ли более эффективным привести настройки сервера в соответствии с задачами.
вопрос об экономии сил и денег можно ставить, если стоит конкретная, реальная, могущая существовать в жизни задача.
её не стоит. обсуждаются асимптотики и работа оптимизатора запросов mysql при работе с одной конкретной конструкцией, содержащейся в запросе.
обсуждается оптимизатор запросов. это очень важно, поэтому я так часто это повторяю. нам важно, как поведёт себя оптимизатор, нам на самом деле всё равно, сколько будет выполняться запрос. и совершенно всё равно, какой размер буферов. собственно, бенчмарки автор статьи привёл, как я понимаю, только чтобы можно было их сравнивать между собой.
конкретное время выполнения запросов зависит от настроек, и настройки важны. но статья — совершенно не об этом. про буферы есть уже много статей. эта статья про другое. если вы её не поняли, то не нужно писать в комментарии про буферы, только по той причине, что здесь что-то меряется у mysql. статья про другое. что ещё вам сказать, я не знаю.
это как если бы статье про определитель матрицы было бы написано про вычисление по пределению, а потом через ступенчатую матрицу, и вот в одном случае у нас n!, а в другом n в какой-то константной степени. а вы бы пришли и стали говорить о том, что при вычислении через n! можно хорошо распараллелить, и что памяти добавить, и что MPI нужно использовать. всё это справедливо, но не имеет никакого отношения к обсуждаемой теме.
я честное слово не хотел вас обидеть. видимо, я не очень хорошо выражаю свои мысли, но я на самом деле не знаю как объяснить, почему комментарий про буферы или сервер в этой статье кажется мне неуместным.
её не стоит. обсуждаются асимптотики и работа оптимизатора запросов mysql при работе с одной конкретной конструкцией, содержащейся в запросе.
обсуждается оптимизатор запросов. это очень важно, поэтому я так часто это повторяю. нам важно, как поведёт себя оптимизатор, нам на самом деле всё равно, сколько будет выполняться запрос. и совершенно всё равно, какой размер буферов. собственно, бенчмарки автор статьи привёл, как я понимаю, только чтобы можно было их сравнивать между собой.
конкретное время выполнения запросов зависит от настроек, и настройки важны. но статья — совершенно не об этом. про буферы есть уже много статей. эта статья про другое. если вы её не поняли, то не нужно писать в комментарии про буферы, только по той причине, что здесь что-то меряется у mysql. статья про другое. что ещё вам сказать, я не знаю.
это как если бы статье про определитель матрицы было бы написано про вычисление по пределению, а потом через ступенчатую матрицу, и вот в одном случае у нас n!, а в другом n в какой-то константной степени. а вы бы пришли и стали говорить о том, что при вычислении через n! можно хорошо распараллелить, и что памяти добавить, и что MPI нужно использовать. всё это справедливо, но не имеет никакого отношения к обсуждаемой теме.
я честное слово не хотел вас обидеть. видимо, я не очень хорошо выражаю свои мысли, но я на самом деле не знаю как объяснить, почему комментарий про буферы или сервер в этой статье кажется мне неуместным.
Собственно, весь каламбур наверное получился из-за того, что комментарий про «стоит ли овчинка выделки» предназначался на другую ветку (1-ю), в ответ на комментарий автора:
Видно, что автор тчательно рассмотрел работу оптимизатора на конктерном примере и вопрос (подчеркну ещё раз — вопрос) лишь заключался в том, как та же работа оптимизатора была бы при полной загрузке индексов в память, т.к. не занимаюсь серверсайдом и тюнингом бд на таком уровне.
Этой статьей и последующим циклом (если конечно попрет) я попытаюсь описать как можно заставить MySQL работать с большими базами данных, на совершенно мизерных объемах доступных ресурсов.
И вообще я как разработчик ужасно жадный до ресурсов особенно таких дорогих как ОЗУ, и пытаюсь выкраивать каждый байт.
Видно, что автор тчательно рассмотрел работу оптимизатора на конктерном примере и вопрос (подчеркну ещё раз — вопрос) лишь заключался в том, как та же работа оптимизатора была бы при полной загрузке индексов в память, т.к. не занимаюсь серверсайдом и тюнингом бд на таком уровне.
А где тут fullscan? Здесь индекс целиком читается. Другое дело, что статья вроде про другое =)
Тут, я думаю, каждый девелопер должен сам для себя решить: «Стоит ли овчинка выделки». Лично я считаю, что нет. Никто не говорит о запихивании всей БД в память, но индексы — точно должны влезать. Тем более, ОЗУ сейчас не так дорога как время разработчика, необходимое на такие убер-оптимизации (которые всё-равно имеют потолок и всё-равно не сравнятся с выборкой по индексам из памяти).
Anyway, инфа интересна и, как минимум, будет полезна «про запас», поэтому ждём следующих статей ;-)
Anyway, инфа интересна и, как минимум, будет полезна «про запас», поэтому ждём следующих статей ;-)
В общем согласен, но у меня есть христоматийный пример, из личной практики, на то стоит овчинка выделки или нет.
Таблица состящая из 4-х записей была в топе по логическим чтениям (более 50% от всей БД) на HP сервере в 32 процессора и 40Gb ОЗУ.
Разработчики на СУБД Oracle (хорошая СУБД не так ли?) не закэшировали в пакеты таблицу в которой день разбивался по часам на час пик и не час пик. После оптимизации (нашел косяк не я, а мой босс, я тогда маленьким ещё был и глупым) скорость тарификации увеличилась на порядок…
Так что кривые руки железом — не исправить.
Таблица состящая из 4-х записей была в топе по логическим чтениям (более 50% от всей БД) на HP сервере в 32 процессора и 40Gb ОЗУ.
Разработчики на СУБД Oracle (хорошая СУБД не так ли?) не закэшировали в пакеты таблицу в которой день разбивался по часам на час пик и не час пик. После оптимизации (нашел косяк не я, а мой босс, я тогда маленьким ещё был и глупым) скорость тарификации увеличилась на порядок…
Так что кривые руки железом — не исправить.
Прошу прощения. Вас не затруднит запустить тесты на 5.1.58?
Я столкнулся с _очень_ сильной просадкой 5.5 по сравнению с 5.1, особенно, если используются процедуры, отчего мы живём по-прежнему на 5.1.
Я столкнулся с _очень_ сильной просадкой 5.5 по сравнению с 5.1, особенно, если используются процедуры, отчего мы живём по-прежнему на 5.1.
Странно, честно говоря не знаю при чем тут процедуры, мы переехали без проблем. Пересоздание процедур — да возможно, запуск — все быстро.
Я выложил все скрипты. Они успешно справлялись с задачей оптимизации как на 5.1 так и на 5.5. Попробуйте поэксперементировать самостоятельно, может заодно и разберетесь где собака зарыта.
Так же пому посоветовать set profiling = 1; и настройку performance_schema — там можно получить приличную информацию по блокировкам и дисковым чтениям.
Я выложил все скрипты. Они успешно справлялись с задачей оптимизации как на 5.1 так и на 5.5. Попробуйте поэксперементировать самостоятельно, может заодно и разберетесь где собака зарыта.
Так же пому посоветовать set profiling = 1; и настройку performance_schema — там можно получить приличную информацию по блокировкам и дисковым чтениям.
По последнему абзацу — можно поподробнее?
Можно, но если только в другой статье, так как вопрос большой, я просто сикнул наводки на темы которые можно почитать в гугле, если не понятно с чего начинать изучение оптимизации запросов. Если в кратце; в MySQL к сожалению нет такого инструмента как trace и любую информацию необходимую для понимая того как же именно выполняется запрос можно получить 3-мя способами
— explain (extended!)
— set profiling
— performance_schema
Первый показывает план выполнения а так же то как именно был переписан запрос. Второй поясняет на что в основе было затрачено время: фетч, передача данных и т.д. Это первые две секции из trace файла Oracle. Но вот дальше все обстоит гораздо хуже. Трейс восьмого (вроде бы) уровня для Oracle дает подробную информацию по событиям ожидания, которая показывет физические и логические чтения, ожидание блокирововк и так далее. performance_schema, которая появилась в MySQL 5.5 позволяет посмотреть ряд событий, но далеко не все, а именно основной упор сделан на дисковые чтения и мьютексы. К сожалению в целом по базе, с разбивкой по событиям. Т.е. если БД тормозит, то есть шанс, что причина может быть найдена после изучения performance_schema, но не факт.
— explain (extended!)
— set profiling
— performance_schema
Первый показывает план выполнения а так же то как именно был переписан запрос. Второй поясняет на что в основе было затрачено время: фетч, передача данных и т.д. Это первые две секции из trace файла Oracle. Но вот дальше все обстоит гораздо хуже. Трейс восьмого (вроде бы) уровня для Oracle дает подробную информацию по событиям ожидания, которая показывет физические и логические чтения, ожидание блокирововк и так далее. performance_schema, которая появилась в MySQL 5.5 позволяет посмотреть ряд событий, но далеко не все, а именно основной упор сделан на дисковые чтения и мьютексы. К сожалению в целом по базе, с разбивкой по событиям. Т.е. если БД тормозит, то есть шанс, что причина может быть найдена после изучения performance_schema, но не факт.
Кто тут говорил, что Хабр не торт? Еще тот торт!
Спасибо за статью!
Спасибо за статью!
>> Логично предположить, что так как данные распределены одинаково для всех значений аттрибутов, то при соединении с таблицей attributes заместо явного указания бинд переменных время запроса должно увеличится незначительно
С чего вы это взяли? В первом случае в запросе участвуют константы. Во втором — данные берутся из таблицы A. Очевидно, что искать в индексе каждую из пар дорого и mysql выбирает полное объединение сначала, затем скан и сравнение. Не?
С чего вы это взяли? В первом случае в запросе участвуют константы. Во втором — данные берутся из таблицы A. Очевидно, что искать в индексе каждую из пар дорого и mysql выбирает полное объединение сначала, затем скан и сравнение. Не?
Искать в индексе каджую из пар — как раз недорого что доказывает явный запрос с указанием бинд переменных и конечный результат в 5 секунд с использованием курсора по всей таблице, а вот mysql как раз ошибается именно в этом месте о чем и гласит указанный баг.
таблица big_table будет сканироваться по нужному индексу, но индекс будет задействован неполностью, то есть из него будет использоваться только первая колонка — вот он источник зла
Интересно, а как oracle 11gr2 повёл бы себя в такой ситуации?
Почему-то мне кажется, что точно так же.
Почему-то мне кажется, что точно так же.
Oracle 10 оптимизировал данный запрос успешно, используя индекс целиком, при наличии гистограмм по указанным колонкам, при чем там можно явно указать хинт /*+ cardinality(1)*/ (вроде так пишется) и оптимизатор все сделает нормально. Я понимаю ваш вопрос могу на него ответить вот такими запросами:
Первый отрабатывает мгновенно — второй бесконечно время, хотя время выполнения должно вызрасти всего в 50 раз…
Не зря эту ошибку завели как багу оптимизатора :) хотя конечно сам MySQL так не считает.
select b.attr_attr_id,
max(b.record_date),
min(b.record_date),
max(b.record_value),
count(1)
from (select *
from attributes where attr_id > 499) a
straight_join
big_table b force index (idx_big_table_attr_date)
on b.attr_attr_id = a.attr_id and b.record_date between a.start_date and a.end_date
group by b.attr_attr_id;
select b.attr_attr_id,
max(b.record_date),
min(b.record_date),
max(b.record_value),
count(1)
from (select *
from attributes where attr_id > 450) a
straight_join
big_table b force index (idx_big_table_attr_date)
on b.attr_attr_id = a.attr_id and b.record_date between a.start_date and a.end_date
group by b.attr_attr_id;
* This source code was highlighted with Source Code Highlighter.Первый отрабатывает мгновенно — второй бесконечно время, хотя время выполнения должно вызрасти всего в 50 раз…
Не зря эту ошибку завели как багу оптимизатора :) хотя конечно сам MySQL так не считает.
С чего вы, кстати, взяли, что только первая колонка (uint), когда key_len = 14?
Могу посоветовать почитать темы связанные с багом #5982 там много всего интересного, однако это выяняеся просто если посмотреть в performance_schema в момент выполнеяния запроса там будет идти многоблочное чтение именно этого файла данных (если конечно у вас выставлен параметр innobd_file_per_table если не ошибаюсь)
MySQL использует rule based оптимизатор. Зачатки cost based оптимизации в нем конечно присутствуют, но не в должной мере, в какой их хотелось бы видеть
Можно попродробнее? Насколько я знаю, оптимизатор строит все возможные планы выполнения и выбирает из них самый «дешевый» (с минимальным execution cost). Разве это не есть cost based?
Целью данной фразы было не указать на то что cost based оптимизатор в MySQL отсутствует, а то что он из рук вон плохо оптимизирует запросы. Думаю то, что в оптимизатор Oracle, прекрасно работающий с гистограммами и динамически анализирующий результат запроса полученный после применения предикатов доступа и даже фильтрации, не идет ни в какое сравнение с оптимизатором MySQL и так понятно.
Примеры неудачной оптимизации MySQL я как раз и постарался сдесь привести и описать что не так.
Примеры неудачной оптимизации MySQL я как раз и постарался сдесь привести и описать что не так.
> Посмотрим план выполнения:
> id select_type table type possible_keys key key_len ref rows Extra
> 1 SIMPLE b index idx_big_table_attr_date idx_big_table_attr_date 14 > 125096048
> 1 SIMPLE a eq_ref PRIMARY PRIMARY 4 test.b.attr_attr_id 1 Using where
>
> План явно показывает, что идет полное сканирование таблицы в 125 миллионов записей. Странное решение. Исправить ситуацию не поможет и straight_join для смены порядка джойна ни force index для явного указания использовать индекс. Все дело в том что в лучшем случае мы получим план вида:
> PRIMARY b ref idx_big_table_attr_date idx_big_table_attr_date 5 a.attr_id 6949780 Using where
У меня вывод EXPLAIN в FF неполностью отражается, хотя полностью скопировался. Если соберётесь писать следующую статью, лучше используйте \G — вертикальный вывод.
И по делу: а где тут fullscan? Тут читается индекс целиком, но не вся таблица. Хорошего всё равно мало, но разница может быть существенная.
> id select_type table type possible_keys key key_len ref rows Extra
> 1 SIMPLE b index idx_big_table_attr_date idx_big_table_attr_date 14 > 125096048
> 1 SIMPLE a eq_ref PRIMARY PRIMARY 4 test.b.attr_attr_id 1 Using where
>
> План явно показывает, что идет полное сканирование таблицы в 125 миллионов записей. Странное решение. Исправить ситуацию не поможет и straight_join для смены порядка джойна ни force index для явного указания использовать индекс. Все дело в том что в лучшем случае мы получим план вида:
> PRIMARY b ref idx_big_table_attr_date idx_big_table_attr_date 5 a.attr_id 6949780 Using where
У меня вывод EXPLAIN в FF неполностью отражается, хотя полностью скопировался. Если соберётесь писать следующую статью, лучше используйте \G — вертикальный вывод.
И по делу: а где тут fullscan? Тут читается индекс целиком, но не вся таблица. Хорошего всё равно мало, но разница может быть существенная.
Да именно это и и мелось ввиду, что идет полное сканирование таблицы по индексу, что и показывает план. Но дело в том, что запрос выбирает max(record_value), которое отсутствует в индексе, так что это не чистый index_ffs это сканирование по индексу с соответствующим заходом в таблицу, что ИМХО ещё хуже чем fullscan. Хотя конечно что лучше, а что хуже для MySQL не всегда понятно.
Писал в 3 утра — не все мысли сформулированы точно. Спасибо за указание на ошибку — подправлю в статье. И заодно проверю, что за план там вставлен, как-то на свежую голову он мне не внушает доверия :)
Писал в 3 утра — не все мысли сформулированы точно. Спасибо за указание на ошибку — подправлю в статье. И заодно проверю, что за план там вставлен, как-то на свежую голову он мне не внушает доверия :)
Да точно не тот план вставил, дело в том что примеры готовил пару дней, складывая всю нужную информацию в файл запросов, а статью написал за пару тройку часов ночью, я бы даже сказал ранним утром, и запостил в ней не тот план исполнения.
Подправил план. Теперь там fullscan :)
А написанный ранее план будет работать в том случае если все колонки запроса содержаться в индексе (это был один из неудачных экпериментов по оптимизации я его тут описывать не стал и так слишком объемная статья вышла, тем более index_ffs MySQL тоже делает крайне медленно)
Подправил план. Теперь там fullscan :)
А написанный ранее план будет работать в том случае если все колонки запроса содержаться в индексе (это был один из неудачных экпериментов по оптимизации я его тут описывать не стал и так слишком объемная статья вышла, тем более index_ffs MySQL тоже делает крайне медленно)
Знаком с синтаксисом SQL, видимо на недостаточном уровне, поэтому хотел задать пару вопросов:
declare continue handler for not found set ex_no_records_found = 1; — нормально ли ставить перехватчик после запроса? каким образом он будет отработан? или фактически он отрабатывается в секции open attr;… close attr;?
declare continue handler for sqlstate '42S01' begin end; — зачем объявляется перехватчик, если ниже создается таблица лишь в том случае, если она отсутствует ( create temporary table if not exists ). Возможно есть какие-либо русскоязычные источники, которые помогут лучше понять принципы этих разделов: dev.mysql.com/doc/refman/5.0/en/declare-handler.html?
declare continue handler for not found set ex_no_records_found = 1; — нормально ли ставить перехватчик после запроса? каким образом он будет отработан? или фактически он отрабатывается в секции open attr;… close attr;?
declare continue handler for sqlstate '42S01' begin end; — зачем объявляется перехватчик, если ниже создается таблица лишь в том случае, если она отсутствует ( create temporary table if not exists ). Возможно есть какие-либо русскоязычные источники, которые помогут лучше понять принципы этих разделов: dev.mysql.com/doc/refman/5.0/en/declare-handler.html?
1. это не запрос а декларация курсоров, если поставить декларацию перехвата исключения в другом месте — вы не соберетесь. Перехват исключения отрабатывает если запрос не вернул данных, а не только при окончании курсора, и с ним есть пара подводных камней.
2. это просто best practice чтоб при вызове процедуры какждый раз на стороне драйвера JDBC не возникало надоедливое предупреждение table already exists, вроде в MySQL 5.5 поменяли механизм проброски исключений, чтобы иметь возможность обрабатывать множественные исключения в каскаде процедур, и вполне возможно этот код работает уже не так четко, как раньше.
По русским источникм я к сожалению не специалист.
2. это просто best practice чтоб при вызове процедуры какждый раз на стороне драйвера JDBC не возникало надоедливое предупреждение table already exists, вроде в MySQL 5.5 поменяли механизм проброски исключений, чтобы иметь возможность обрабатывать множественные исключения в каскаде процедур, и вполне возможно этот код работает уже не так четко, как раньше.
По русским источникм я к сожалению не специалист.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
MySQL: оптимизация конструкции between