Пользователь
0,0
рейтинг
11 декабря 2013 в 16:55

Разработка → Вы встречались с анализом леса популяции запросов SQL промышленного приложения (например, для оптимизации)?

Хочу задать этот вопрос Хабровчанам.

Современные информационные системы строятся на различных видах СУБД и все же реляционные СУБД остаются самыми распространенными и используемыми. Интересная статистика на эту тему ТУТ и ТУТ.
image

При разработке и модификации систем уровень формализации знаний аналитиков и разработчиков остается небольшим (автоматизации создания умных запросов или с учетом ряда четких правил) и чаще всего результирующие SQL запросы написаны «нормально», «как привык», «так пишут у нас на фирме», а вопросы оптимизации остаются на этап выполнения запросов в СУБД и последующие этапы оптимизации (в худшем случае ждут, когда все начинает тормозить).

Объем ручного кода остается большим даже несмотря на наличие большого количества удобных инструментов, позволяющих избежать их ручного написания (в т.ч. и ORM). Да и не всегда есть возможность использовать такие инструменты, особенно когда речь идет об очень сложных аналитических запросах, включающих сложный анализ данных. А такие инструменты как ORM используют в более менее свежих проектах и только для тривиальных запросов.

В любом случае необходимость в оптимизации запросов и, возможно, структуры СУБД постоянна на промышленной базе. Даже при «идеальном» проектировании этого не избежать т.к. окружающая среда меняется и сам заказчик не всегда знает что будет завтра (как поменяются законы, рынок, клиенты, какие новые идеи появятся и т.д.).
image

При оптимизации в СУБД возникает ряд таких же проблем, как и при оптимизации кода для обычного языка программирования — поиск и отслеживание одинакового кода, замена его во всех местах на новый, оптимизированный и т.п… И хорошо если бы все SQL запросы лежали в одном месте и можно было бы пройтись простым поиском и заменить ))) Но так уж исторически сложилось, что большинство технологий не выделяют слой запросов в отдельные структуры/объекты/файлы/ и т.д. В лучшем случае запросы действительно выделены в отдельные файлы.

Вариантом получения всех запросов может быть, например, SQL-логгер (почти во всех СУБД есть или встроенный или можно прикрутить). Но в таком случае надо выбрать период получения всех запросов например в год, что долго (для обычного предприятия за год проходят все основные операции которые могут быть… почти :) ), да и проблема определения параметров останется открытой…

Тут, на Хабре, и в интернете на рус. и англ. языках очень много информации на тему оптимизации запросов и отдельно структуры СУБД, а вот материалов на тему анализа всех запросов для последующей оптимизации действительно мало. А то что есть касается больше рекомендаций аналитических, автоматизированных средств для этого я не нашла…

Скажите, хабровчане, встречались ли вы с комплексным анализом всей популяции SQL-запросов приложения?

На мой взгляд есть три основные причины для комплексного анализа структуры SQL запросов:
1. оптимизация кода: выделение дублирующегося кода, замена дублирующегося кода при его оптимизации, выдача различных автоматических подсказок по улучшению структуры кода.
2. рефакторинг кода (оптимизация в данном случае не обязательный эффект).
3. научный интерес, например, анализ лесов SQL промышленных систем для последующего исследования, например иммитационного моделирования леса запросов SQL для анализа новых алгоритмов оптимизации работы СУБД.

На мой взгляд наличие средства такого анализа позволило бы значительно упростить работу многих программистов и некоторых ученых, работающих над алгоритмами оптимизации в СУБД.
Встречались ли вы с комплексным анализом всей популяции SQL-запросов большого приложения?
2%
(2)
Да, использовал автоматические средства оптимизации (напишите, пожалуйста, о них в комментариях)
23%
(21)
Да, но только аналитические методы оптимизации… (напишите, пожалуйста, о них в комментариях)
64%
(59)
Нет
16%
(15)
Затрудняюсь ответить

Проголосовало 92 человека. Воздержалось 48 человек.

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

Ольга @rolechka
карма
8,0
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Реклама

Самое читаемое Разработка

Комментарии (14)

  • 0
    У нас скорее реактивный подход — раз в месяц примерно мы вытаскиваем самые тяжелые запросы с группировкой по execution plan и занимаемся ими. Чаще всего бывает что есть 2-3 проблемы, которые требуют ресурсов на порядок больше чем все остальное.
    • 0
      Оо, спасибо за ответ, интересны разные мнения. Я такой вариант отношу к «аналитическим методам».
      • 0
        Его и выбрал в опросе.
  • +1
    Для Oracle использовались встроенные средства, которые анализировали работу сервера и выдавали подробные отчеты по проблемным местам как в запросах и структурах данных, так и в настройках самого сервера. После чего проверялись и изменялись соответствующие SQL-запросы. Но все это делалось только тогда, когда на сервере появлялись какие-либо проблемы, либо когда пользователи жаловались на медленную работу приложений.

    Для MySQL использовался MONyog, который подключался к серверу, собирал статистику по его работе и составлял отчеты с рекомендациями по оптимизации.
  • 0
    У меня MS SQL.
    Я использую: Отчет — производительность – запросы с наибольшим общим временем цп.

    И там думаю, оптимизировать запросы, кешировать данные и прочее. Просто, но эффективно.
  • 0
    • 0
      +1, у нас ежесуточно приходит отчет по query digest
      если кстати появится инструментарий который позволит смотреть такие отчеты более наглядно, я его пожалуй куплю =)
  • +1
    Мы смотрим запросы, которые долго выполняются и оптимизируем их. Обычно это 2-3 самых «тяжёлых» запроса, а вообще их количество для исправления зависит от сложности запроса и выделенного времени. Бывало и по 5-7 запросов в день оптимизировали.

    Чуток ИМХО. О наболевшем...
    Я пишу на PHP, в качестве СУБД используем PostgreSQL.

    ORM — не помощник в оптимизации, скорее наоборот. Не знаю как дела обстоят в других языках, но, если взять пару-тройку популярных фреймворков для PHP с реализованным ORM, то замечаем, что они заставляют нас делать лишние запросы! На сколько я понял, причина — заточенность этих самых ORM в основном под MySQL.

    Нет поддержки RETURNING. Иногда работает (не на MySQL) метод аля-getLastInsertId, но работает только в случае если в качестве PK используется счётчик (тип данных INTEGER). Поэтому, если вы используете что-то отличное от счётчика (например, UUID), то прощай getLastInsertId и здравствуй дополнительный запрос перед нужным запросом аля SELECT uuid_generate_v4() и предопределённый id вместо автогенерируемого в запросе с INSERT. А если у вас несколько автогенерируемых полей, которые вам нужно вернуть (RETURNING field1, field2), то понадобится ещё один запрос после INSERT аля-SELECT field1, field2 FROM table WHERE id=:id
    Итого 3 запроса вместо 1-го…

    Нельзя реализовать непопулярное решение для отличных СУБД от MySQL. В PG при использовании ORM, например, нельзя реализовать запрос:
    WITH sel AS (SELECT item_id FROM store WHERE category = 'mobile') SELECT * FROM items WHERE id IN (SELECT item_id FROM sel) AND price BETWEEN 100 AND 200

    Я конечно, понимаю, что данный пример не отражает всех нюансов и его можно абсолютно безболезненно заменить на:
    SELECT * FROM items WHERE id IN (SELECT item_id FROM store WHERE category = 'mobile') AND price BETWEEN 100 AND 200

    И даже ещё лучше:
    SELECT i.* FROM items AS i INNER JOIN store AS s ON s.item_id = i.id AND s.category = 'mobile' AND i.price BETWEEN 100 AND 200

    Но всё же бывают случаи, когда тебе приходится делать выборку из таблицы store несколько раз в запросе, тут WITH приходится как нельзя кстати. У нас были очень тяжёлые запросы, которые выполнялись секунд по 10-15, после подобной оптимизации они стали выполняться значительно быстрее.

    А заточенных ORM под PG я ещё не встречал, думаю, что иначе в них просто теряется смысл, ибо теряется эта независимость от выбранной СУБД…

    Поэтому ORM — это штука только для простых запросов, если вам нужно что-то реально хорошо оптимизировать, то приходится работать с «голыми» запросами, мимо ORM. Либо, есть ещё вариант, инкапуслировать все нужные вам запросы в хранимые процедуры и оптимизировать запросы не со стороны кода и запросов, которые генерирует вам ORM, а со стороны самой СУБД. Но последний вариант, ИМХО, не очень кошерный…

    Но и не всегда проблема кроется в том как построен запрос, иногда проблема в отсутствии/не оптимальности какой-то мелочи или индекса.
    Например, LIKE работает значительно быстрее, чем ILIKE в PG. Поэтому для полнотекстового поиска лучше использовать LIKE + функциональный индекс:
    LOWER("name") LIKE LOWER(:name)

    И соответствующий функциональный индекс аля-USING btree («name» pg_table.text_pattern_ops).
    В итоге видим значительное увеличение скорости выполнения запроса.

    Спасибо за внимение.

    P.S.: давно не работал с MySQL, поэтому некоторые сравнения могут быть не актуальными… к сожалению…
  • 0
    Мне кажется, в данной статье немного смешаны 2 проблемы.
    1-я, техническая на стороне кода — дублирование кода SQL-запросов в приложении.
    Сразу же возникает запрос — действительно ли нужен сырой SQL в этих местах? На моей практике он был нужен для действительно тяжелых мест (получение первичных данных для расчета ЗП по подразделению, например), ну и для отчетов. И тех и тех мест мало, они известны, и с ними можно разбираться индивидуально.
    Если принять, что в остальных местах SQL генерируется через ORM, то можно избежать дублирования, на мой взгляд. Попробуйте посмотреть в сторону паттерна Specification применительно к построению DAO/Repository слоя — он позволит, в целом, вынести «атомарные» бизнес-условия для сущностей в отдельные спецификации, компонуемые и реюзабельные далее везде в коде. Разумеется, это идеализированно, но тем не менее, может серьезно сократить дублирование кода.
    В случае все-таки ручного написания SQL — проблему с повторением кода в целом никак не решить. Равно как и то, что каждый будет писать так, как привык. Остается принять это как данное, и перейти ко второй проблеме :)

    2-я проблема, административная, на стороне СУБД — получение и анализ медленных запросов. Поможет slow-лог, очевидно, в той или иной реинкарнации для вашей СУБД. Кто-то (MS) умеет получать эти данные на лету с хорошей разверткой, и выдавать подсказки по индексам, для PostgreSQL/MySQL нужно смотреть именно slow log и далее анализировать запросы поштучно. В общем, зависит от специфики и удобства тулз для СУБД.
    • 0
      В статье я обсуждаю вопрос более концептуальный — анализ всех запросов в целом и структуры СУБД, которое может использоваться для оптимизации, рефакторинга и исследований. Дублирование кода и анализ медленных запросов — это некоторые из подзадач этой более глобальной задачи. Решая только локальные задачи оптимизации можно через пару итераций столкнуться с тем что сколько запрос не оптимищируй все равно будт медленно потому что проблема в другом, например в уже неправильной структуре базы.

      В целом рассматривая какой-то такой общий анализатор будет уже все равно написан код вручную или с помощью ORM. ORM-ы многие генерируют код далеко не оптимальный. Вы привели очень правильный пример расчета ЗП. Во всех системах ERP-класса или похожих подобных запросов очень много и современные ORM непомогут. Нужны более интеллектуальные ORM, которые бы могли включать како-йто такой универсальный алгоритм анализа и оптимизации или хотя бы СППР.
  • 0
    Полностью поддерживаю тезис о том, что «наличие средства такого анализа позволило бы значительно упростить работу многих программистов и некоторых ученых, работающих над алгоритмами оптимизации в СУБД».
    Для средств разработки приложений, подобные средства уже есть и, в умелых руках, очень помогают разработчикам. Например — Pascal Analyzer для проектов написанных на Delphi.

    В работе не раз сталкивались с необходимостью оптимизации быстродействия работы приложения, узким местом которого было взаимодействие с БД. Достаточно часто это было приложение написанное другими разработчиками.

    Как правило, использовали аналитические методы и действовали по алгоритму:
    — с помощью трейсеров, консоли Оракл и т.п. инструментальных средств, определяем самые «медленные» запросы, которые с точки зрения пользователей мешают работе системе;
    — максимально оптимизируем запросы и структуру БД;
    — проводим нагрузочное тестирование;
    — если результат все еще не устраивает возвращаемся на шаг 1 :-)

    Типовые причины проблем с быстродействием с которыми сталкивались:
    — неоптимальная структура БД.
    Т.е. БД может быть спроектирована в 3НФ, «по классике», однако не учитывать популяцию запросов приложения.
    Варианты решения — изменение структуры (с или без нарушением 3НФ), создание объектов material view или таблиц обновляемых триггерами специально под определенные задачи.

    — множество точечных запросов вместо одного/двух.
    При этом основное время уходит не на сам запрос, а на взаимодействие приложения и БД по каждому запросу.
    Самый простой пример — много запросов вида select… where id =…
    Вариант решения — замена на один запрос вида select… where id in ( ..., ..., ...).
    В случае, если каждый следующий запрос требует данные предыдущего (например «вытягиваем» дерево узлов из БД) — можно использовать иерархические запросы или заранее предсохраненные, обновляемые триггерами данные.

    — неоптимальный план запроса.
    Либо изначально, либо в какой-то момент СУБД меняет план и запрос начинает работать в несколько раз медленнее.
    Варианты решения — создание/удаление индексов, ручное изменение плана запроса средствами СУБД (например, консоли Оракл), изменение текста запроса так, чтобы СУБД «поняла как лучше» выполнять этот запрос (последний вариант — на грани шаманства :-) ).
    На практике также встречаются ошибки даже в таких СУБД как Оракл, которые могут не только менять план запроса, но и приводить к некорректным данным, возвращаемым таким запросом. Здесь может помочь только чтение документации, переписка с саппортом самого СУБД, эксперименты.

    неоптимально написанный запрос (часто для профессионала явно неоптимально), который на небольшом количестве данных работал нормально, а в реальной системе внезапно (!) начал тормозить.
    Вариант решения — книги «Основы SQL-для чайников/проффесионалов» для разработчика, который написал этот запрос, и «Почему плохо давать задачи по написанию запросов новичкам, а потом ленится их почитать» ведущему разработчику/тим-лиду/ПМ по проекту. Может помочь еще книга «Зачем нужен Code Review».

    «слишком много» данных в таблицах и предыдущие варианты решений не актуальны.
    Варианты решения — технический: партирование, структурый: отделение актуальных данных от архивных.

    Думаю, для разработчиков было бы полезно, чтобы средство автоматического анализа:
    — отрабатывало все перечисленные и много других подобных ситуаций;
    — предоставляло возможность разработчику выделять «приоритетные» запросы, поскольку только одним автоанализом неэффективные, с точки зрения пользователя, запросы не выявить. Например, для какого-то запроса, который часто выполняется, нормальным временем может быть 1000 секунд (если это регламентная задача или часть задачи по печати отчета занимающего 1000 страниц). В то же время, для более «редкого» запроса 2 секунды может быть уже недопустимым временем (например, кассовые операции, работа с банкоматом);
    — как дополнение содержало инструкцию с описанием как отрабатывать рекомендации и детальным разбором «анти-патернов», их примерами.

    Как первый шаг к написанию такого средства, возможно, полезно было бы хорошую книгу или Интернет ресурс по SQL где описываются патерны/анти-патерны наиболее часто встречающихся ситуаций в приложениях, работающих с БД.

    Если будут желающие – было бы интересно поучаствовать в написании такой книги, возможно, в зависимости от загрузки по работе – разработке такого средства.
    • 0
      Подобные книги уже есть. Например «MySQL. Оптимизация производительности» и другие, также и на англ непереведенные.

      (разработкой такого средства уже занимаюсь, все в одну статью складывать совсем не хорошо и не готова пока представить результаты, как только будет дойстойный материал так сразу.)
  • 0
    www.mysqlperformanceblog.com/2012/08/31/visualization-tools-for-pt-query-digest-tables/
    только написал и оказывается есть целый пласт инструментов! надо пробовать…
  • 0
    Работаем с Oracle. Сталкивались с некоторым анализом популяции запросов при апгрейде с версии 10.2 до 11.2. Перед миграцией были сохранены baseline всех запросов, а теперь решаем что с ними сделать. Теперь их планы сравниваем с тем, что предлагает новая версия Oracle — с Oracle это делается без особого труда. Честно говоря, не слежу за этой работой, хотя тема интересная.
    Обычно «лечим» все тормоза по месту. Но такие средства, как Oracle Tuning Advisor и Oracle Partition Advisor, наверное, за ростом объема и изменения структуры данных придется пощупать.

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