Эволюция моих SQL запросов

Всем привет! Я тимлид и Senior Oracle Developer, 12 лет работаю с OeBS и в основном пишу SQL запросы. Хотел бы рассказать, как за это время менялся мой подход в написании SQL запросов.

Вначале было слово, а точнее запрос. Скажем

select name from user where id = 1

Написать такой запрос как-то не так практически невозможно. Он одинаково хорошо работает во всех известных мне базах данных. А знаю я только oracle :З Но подозреваю что и в других реляционных тоже всё будет ок.

Так что же произошло? Проблемы начались, когда таблиц стало две:

select u.name from user u, rest r where u.id = 1 and u.id = r.user_id

Этот код вызывал у меня больше вопросов. Например, как должны быть соединены эти таблицы? Казалось бы что проще  id = user_id, но мне что-то не нравилось. В блоке where мне не хватало четкого разделения между условиями фильтрации и соединениями таблицам. Когда запрос содержал 2 таблицы всё ещё было норм, но когда кол-во таблиц доходило до 5 - всё рассыпалось. Взглянув на запрос, я не мог сразу понять как соединены таблицы и не пропущена ли какая-то связка. И с этим все прекрасно жили, но я не мог. Однажды мне, молодому джуну, на глаза попался ANSI синтаксис.

select u.name from user u inner join rest r on u.id = r.user_id where u.id = 1

букв стало немного больше, но я намного лучше стал понимать, как связаны таблицы в моих SQL выражениях. Мир запросов расцвёл для меня новыми красками, и я больше не писал запросы как-то иначе. А ещё распространял эту весть среди других джунов. Это был мой первый шаг в эволюции SQL. Я вырвался от привычных шаблонов легаси кода и сделал что-то своё. Но была одна проблема. Когда используется скажем левостороннее соединение ANSI синтаксис заставляет переносить в связки, и все прочие ограничения для таблицы.

select u.name, r.resp_name 
from user u 
left join resp r on u.id = r.user_id  and r.end_date > sysdate 
where id = 1

Это меня жутко бесило, так как опять связи и параметры сливались в одну кучу.  Помимо этого, наступал момент, когда запрос разрастался до гигантских размеров и становился практически не читаемым.  К тому времени я уже дорос до мидла и хотел рассказывать истории своими селектами.  И это подтолкнуло меня на второй шаг эволюции.  И имя ему with.

with resp_q as (
  select resp_name, userid  
  from resp where r.end_date > sysdate)
 ,main_q as (
   select u.name, r.respname
   from user u 
   left join resp_q r on u.id = r.userid
   where id = 1)
 select * from main_q

Кода стало опять больше, но запросы в with позволили мне разбить монолитный запрос и группировать разные кусочки запроса по “историям”, а потом сплетать их вместе. Я мог рассказать про свой запрос так: “Получаем список пользователей. Список ролей. Объединяем их в одну выборку и отсекаем тех кто нам не нравится. С оставшимися идём дальше, взявшись за руки.”  И за каждый шаг отвечала свой небольшой именованный запрос. Это также помогло мне бороться с моим злейшим врагом WET, т.к. одни и те же истории я мог использовать в разных частях своего запроса, не дублируя код.  Ко всему прочему, упростилась отладка. Знай в блок from подставляй разные именованные запросы и отлаживай их по отдельности.  А ещё, как выяснилось позже, с помощью with можно оптимизировать запросы, используя hint MATERIALIZE. Он материализует именованный подзапрос и данные при запросе из него берутся из темпового пространства. До этого я использовал обычные темповые таблицы. Это было более грубое решение, т.к. создавались лишние объекты БД + надо было помнить про очистку. Как итог, теперь, если запрос сложнее 10 строк, я почти всегда использую with.

Но чего-то не хватало. По своей природе я люблю кодить, но, когда приходит время тестировать, весь мой энтузиазм куда-то пропадает. Как итог, я часто отдавал не до конца протестированный код. Мне регулярно приходилось слышать про unit тесты, автотесты и прочее. Но сложно было это применить к БД. Сегодня сумма за период равна 100р, а завтра 120р. И как ты тут напишешь тест? Так и жил… Но, уже став тимлидом, мне попалась задача, в которой надо было найти отмененные документы. Условие отмены было достаточно сложным и собиралось из множества нюансов (спрятал под функцию).

select * from document where xxstorno(id) = 'Y'

У меня было порядка 10 примеров документов. И завершая условие для одного документа, что-то ломалось в другом. А так как тестировал руками и глазами, времени уходило просто море.  Я уже думал этому не будет конца. Пока не понял, что вокруг моего запроса можно написать обертку, которая будет за меня проверять все мои кейсы и говорить какие документы прошли проверку, а какие нет. Потратив на обертку несколько минут, я сократил время тестирования с 5-7 минут, до нескольких секунд.

with test_case as (
  select 10 id, 'Y' storno from dual 
  union all 
  select 5 id, 'N' storno from dual)
  , run_test as (
    select tc.id, decode(xxstorno(d.id), tc.storno, 'OK', 'Error') result
    from test_case  tc
    left join document d on d.id = tc.id)
 select * from run_test

После правки функции, я просто запускал тест-запрос и смотрел сколько документов прошло тестирование, а сколько нет. В процессе тестирования я накидывал туда ещё кейсов, при том что про старые тесты тоже не забывались. И тогда я понял, как же это здорово! Как можно легко тестировать свой запрос, повышать надёжность и при этом не нужно ничего делать руками. Это может показаться элементарным, но до этого мне не встречались подобные конструкции. Обычно я видел конструкции типа and id = 5--6 7 10 135 1345  в которой просто перебором подставлялись разные значения и руками смотрелось что и как оно должно возвращать. С того дня я написал несколько разработок, и к каждой из них я уже готовил свой тестовый скрипт.  Данный стиль мне очень понравился и теперь я пытаюсь привить его и своим разработчикам.   Чтобы им не пришлось проделать путь в 12 лет, чтобы писать красивые SQL запросы.

По итогу в мире SQL не происходит почти ничего нового уже много лет, тем не менее всегда приятно найти возможность улучшить свои запросы.

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

    +3
    Условие отмены было достаточно сложным и собиралось из множества нюансов (спрятал под функцию)

    Это называется плохой дизайн.
    Сделайте флаг is_canceled Int(1) not null default 0 check (is_canceled in (0,1)) и повесьте на него чек на ваши достаточно сложные условия. Наглядность, удобство и бонусом чек будет контролировать еще если где то ошибетесь.
      +2
      Угу. Очень плохой. Использование функции в предикате, если по такому выражению нет FBI-индекса, приведет к full table scan. И если нужно выбрать только документы со сторнированием, то перебирать тогда вообще все записи нет никакой пользы акромя вреда:
      select * from document where xxstorno(id) = 'Y'
        +3
        Действительно, но тут хотябы индекс построить можно (хотя у ораклистов мода на функции которые нельзя объявить deterministic), а вот бд где нужно с хрустальным шаром данные рассматривать я вижу постоянно и это просто запредельное зло.

        Почему то сколько встречаю архитекторов и программистов бд, именно у ораклистов мания писать вагонами процедуры и функции, вкладывать их друг в друга и присваивать дебильные имена, орды их. А раз у них есть процедуры и функции давайте еще и данные запутаем дальше некуда, ура!

        ЗЫ Раньше очень хотело поработать с ораклом, такая знаменитая бд. Поработал, да не с одной, теперь хочется развидеть все это.
          0

          Да, функции — это убийцы производительности, если их неправильно готовить. А в случае, если это ещё и легко заменить простым составным выражением, то уж лучше добавить виртуальное поле и на нем уже создать индекс.

          0
          Конечно, реализация вышла несколько другой и в таком виде запрос никогда бы не отработал за приемлемое время. Но я не хотел прикладывать конечный запрос на 150 строк, т.к. он вносил ненужные усложнения, не имеющие отношения к данной статье. Я хотел показать как можно тестировать, а не как писать запросы. Думаете стоило бы выложить?
            +2
            Думаю не стоит называть так функцию: «xxstorno»
            Так же думаю что не стоит проектировать базу так, что нужна функция, чтобы найти отмененные документы, я выше написал как этого избежать. Вообще вычисления при селектах — зло.

            И раз уж пошла такая пьянка, «resp_q», «main_q» вы очень пожалеете о таких именах частей CTE, ну и преемники ваши вам спасибо точно не скажут.
            «resp_name, userid» — два разных стиля именования, почему не user_id, плюс, что такое resp вот приходится догадываться, сейчас же у всех автокомплитеры, зачем вы экономите. Вы же сами пишете «хотел рассказывать истории своими селектами», ну дак рассказывайте, запрос должен читаться от начала до конца.

            По своей природе я люблю кодить, но, когда приходит время тестировать, весь мой энтузиазм куда-то пропадает.

            Потому что не пользуетесь check. Когда у вас база обвязана внешними ключами, стоят ограничения на значения везде и всюду, частичные индексы, контроли уникальности, и тд итп, тестирование становится очень легким, а иногда вообще не нужным.

            ЗЫ плюс еще и избавляетесь от запоминания не нужных нюансов и всякой шелухи, например: ой, мне вот нужно выбрать только опубликованные, а не нужно ли проверять являются ли они удаленными, допишу ка еще условия… или
            constraint is_deleted_publish_check check ((is_deleted = false) OR (is_published = false))
          +9
          Какая-то не сеньерская статья, извините, но ощущение что это радостные ощущения джуниора прослушавшего первый курс по sql и спешащего поделиться результатами:) Простите, написано приятно, здравые мысли есть, но уровень статьи (не Ваш разумеется, статьи) реально sql для dummies:)
          p.s.: За неэкранированные названия таблиц и полей надо бить канделябром. Имхо.
            0
            Сейчас любой может назвать себя сеньером-помидором и никто ничего не докажет.
              0
              Спасибо за обратную связь. Писал как есть. Надеюсь мои работодатели будут оценивать мой уровень не только по моим статьям)))
                +2
                > p.s.: За неэкранированные названия таблиц и полей надо бить канделябром. Имхо.
                Поясните, почему? У меня например от лишних (= не меняющих ничего в работе запроса) кавычек начинает рябить в глазах.
                  –1
                  Боязнь наступить на ногу тупому парсеру, привычка из глубины времен. Хотя и сейчас, после достаточно умного парсера пг, mysql или оракл иногда удивляют.
                    +1
                    кавычки в оракл в именах объектов не нужны и даже вредны имхо.
                    не нужно создавать таблицы/столбцы в lowercase — потом замаяться можно это все прописывать в SQL-запросах
                    + не весь софт с ними потом сможет работать
                      –1
                      Вы говорите о создании таблиц а я о запросах к ним.
                        +2
                        не нужно создавать таблицы/столбцы в lowercase — потом замаяться можно это все прописывать в SQL-запросах

                        А что за проблемы у Оракла с lowercase названиями таблиц и столбцов?

                          +1
                          по умолчанию имена объектов регистронезависимы, но если имя объекта заключить в двойные кавычки, то оно станет регистрозависимым.
                          Если не ошибаюсь то как то так
                          MY_TABLE = my_table = My_Table = "MY_TABLE"
                          "MY_TABLE" != "my_table" != "My_Table"

                  –1
                  Я бы сказал — статья по духу совершенно не SQLвская.
                  SQL ведь по своему духу не похож на обычные императивные языки: там надо написать что нужно получить, но не надо(в идеале, жизнь сложнее) описывать как это получить. Поэтому расказывать истории — это IMHO не в духе SQL.
                  Аналогично с тестированием — тестировать лучше на специально подобранных данных. Для теста стоит завести тестовую БД со специальным наполнением и проверять запрос на ней. Правда, не знаю как там будет интеграцией с используемой IDE — можно ли «не отходя от кассы» сразу увидеть зелененькие огоньки прошедших тестов.
                    0

                    Бывает что нужно именно по шагам расписать весь алгоритм получения данных. Вот хороший пример (для Postgres, но это сути не меняет):
                    https://www.endpoint.com/blog/2020/10/02/postgresql-binary-search-correlated-data-cte

                      0
                      Ну, я именно по этому поводу и написал — жизнь сложнее.
                      В плане SQL я с этим столкнулся ещё лет двадцать назад, когда мы обнаружили, что планировщик запросов Interbase 4.0 (была такая СУБД, в те времена весьма популярная, ибо шла в комплекте с Delphi) совершенно не умеет оптимизирвать запросы с LEFT OUTER JOIN, скатываясь в последовательную выборку по левой таблице (естественно — самой большой). Нам тогда пришлось разделить запрос на два (благо OUTER JOIN был нужен ровно один) и объединять их через UNION.

                      Но вот пример по ссылке — это то, как делать не надо (ну, если вообще есть возможность так не делать, конечно ;-), ещё раз повторю: жизнь сложнее ): сначала там отказались от использования совершенно стандартной возможности СУБД — индекса, а потом героически написали фактически свой планировщик запроса на SQL, чтобы получить хоть сколь-нибудь эффективную выборку на тех возможностях, что у них остались. Ну да, не спорю: для написания планировщика запроса продумывать последовательность действий, естественно, необходимо. Но такие задачи — редкость (мягко говоря): чаще всего хватает возможностей планировщика, который уже есть в СУБД.
                      Я даже понимаю (вроде бы) резоны владельца СУБД, куда пишется лог — почему он мог запретить создавать индекс: обновление индекса может быть затратным, а новые записи в логи иногда сыпятся так быстро… Но, наверное там надо было бы что-то поменять в архитектуре.
                      К примеру, хранение лога, вообще говоря, вовсе не требует такой сложной штуки, как СУБД. В частности, для задачи последовательной записи лога и бинарного поиска в нем номеру записи хватило бы применения такого артефакта древних времен, как «набор данных произвольного доступа с записями переменной длины»: данные совершенно последовательно пишутся в конец лога, а бинарный поиск делается чтением этого набора, открытого с произвольным доступом к записям по их номерам. Когда-то, во времена IBM System/360, поддержка такой штуки опиралась даже на аппаратные возможности самих дисков (CKD Devices). Сейчас со штатной поддержкой такого, конечно, стало сложнее, но даже на базе обычного плоского файла сделать что-то можно — правда, границы записей придется искать самим в программе.
                      Но ещё лучше бы подошел другой артефакт тех же врмен: «индексно-последовательный набор данных» (который тогда обслуживался утилитой с примечательным именем, которое я помню до сих пор: IEBISAM): данные в нем располагались последовательно в порядке некоего ключа, и записи по этому кючу можно было искать. поэтому для задачи поиска по ключу в виде даты/времени записи он подошел бы лучше. И вот у у этого древнего артефакта есть прямой аналог — clustered index — в современных СУБД, по крайней мере — в некоторых (точно знаю, что есть в MS SQL, но вот за Postgress не скажу). То есть для приема логов в БД создается таблица с первичным кластерным индексом по дате/времени (лучше — с повышенным фактором его заполнения, чтобы не терять зря много места). Запись в нее фактически идет последовательно, без особых потерь на поддержку индекса (разделения блоков дерева там не будет, а поворот, чтобы дерево сбалансировать, может быть отложен и до момента, когда нагрузка снижается). А запрос для выборки по диапазону дат отлично оптимизируется по первичному индексу шатным планировщиком. Вот как-то примерно так можно было бы сделать.
                        0
                        сначала там отказались от использования совершенно стандартной возможности СУБД — индекса, а потом героически написали фактически свой планировщик запроса на SQL, чтобы получить хоть сколь-нибудь эффективную выборку на тех возможностях, что у них остались.

                        Там в первом же абзаце написано, что опции изменить приложение или дизайн базы у них не было, так что пришлось пораскинуть мозгами. В результате они устранили аварийное завершение запросов по таймауту на вебсервисе, и запрос стал выполняться за доли секунды. Кстати, они консультировались на IRC канале #postgresql с экспертами.


                        Так часто бывает у приглашённых консультантов, я и сам оказывался в такой ситуации не раз. Помню как на большом проекте OeBS ужасно тупил Oracle Application Server (конкретно его компонент Concurrent Program Manager), и не было совершенно никакой возможности исправить SQL запрос внутри исполнимого файла сервера (бинарника), который постоянно нагружал базу и вылезал в топ AWR. Проблему решили при помощи stored outlines (это коллекция сохранённых хинтов оптимизатора для конкретного хэша запроса). Сотрудники Oracle подтвердили что других вариантов нет, и патч для сервера выпускать не захотели.

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

                          PS По то, что непосредственно у авторов статьи по ссылки не было возможности менять что-то в базе — это я понял. Но такая возможность была у других, тех кто базой владеет (а у авторов — убедить владельцев в пользе этого). Добавление индекса к таблице, в отличие от изменения запроса — операция почти бесплатная и почти не влияющая на работу других приложений (но опять-таки, почти: обновление добавленного индекса добавляет нагрузку, например).
                          Потому и написал — отказались (и не написал — кто).
                          –1
                          Для хранения данных у пг берется TimescaleDB делается гипертаблица и погнали писать туда ведрами.
                        +1
                        Для теста стоит завести тестовую БД со специальным наполнением и проверять запрос на ней...

                        Пробовали как-то ещё в лохматых 2010-2011 годах в компании внедрить такой подход — с треском провалился. Проекты по построению DWH и отчетности над ним. Очень трудозатратно генерировать синтетические данные или специально отбирать «нужные». Кроме того данные быстро устаревают.
                        В итоге самым эффективным (с точки зрения скорости внедрения функционала в ПРОД, поддержки, внесения изменений, трудозатрат) оказался подход, когда тестировщик по ТЗ пишет «прототип» (т.е. набор SQL-скриптов, которые в итоге собирают целевую витрину/таблицу в виде временной) и потом результат этого прототипа сравнивается с результатом разработанного разработчиком ETL. Дополнительно, если у заказчика есть «эталон» (отчета, собранный вручную например или альтернативным способом), то сверяем с ним.

                        +1
                        Автор не зная о джойнах устроился разрабом SQL? oO Только на уровне мидла узнал о CTE? Серьезно? Если так, то я тогда почти сеньор
                          +1
                          Помимо этого, наступал момент, когда запрос разрастался до гигантских размеров и становился практически не читаемым.

                          Вы меня извините, конечно, но ВСЕ запросы в этой статье оформлены абсолютно нечитаемо.
                            0
                            Посоветуйте пожалуйста, как стоило бы оформить запросы?
                              –1
                              Давайте я попробую. Хотя автоформатер у меня сейчас переснастраивать надо но он выдаст нечто вроде:

                              with user_groups_actual as (select id,
                                                                 name
                                                          from acl.user_groups
                                                          where validity_period @> curdate),
                                   users as (select ut.id   as user_id,
                                                    ut.name as user_name,
                                                    gt.id   as group_id,
                                                    gt.name as group_name
                                             from acl.users as ut
                                             left join user_groups_actual as gt
                                                  on ut.group_id = gt.id
                                                 and ut.pretty_field = 1
                                             where ut.id = :user_id)
                              select *
                              from users;
                              


                              ЗЫ не смог придумать зачем тут CTE но оставил все же, хотя выборка ВСЕХ групп — глупость. И да, я не экранирую имена полей, у pg замечательный парсер.
                              ЗЫЗЫ where r.end_date > sysdate — это не верно, вы так выберете и не вступившие в силу.
                                +2
                                По мне такое вообще не читается. Я форматирую так:
                                WITH user_groups_actual AS (
                                  SELECT id, name
                                  FROM acl.user_groups
                                  WHERE validity_period @> curdate
                                )
                                , users AS (
                                  SELECT ut.id AS user_id
                                  , ut.name AS user_name
                                  , gt.id AS group_id
                                  , gt.name AS group_name
                                  FROM acl.users AS ut
                                  LEFT JOIN user_groups_actual AS gt
                                   ON ut.group_id = gt.id AND ut.pretty_field = 1
                                  WHERE ut.id = :user_id
                                )
                                SELECT *
                                FROM users
                                ;
                                
                                  0
                                  ну ведущую запяятую я просто не люблю, а условия переносятся не просто так, сделано чтобы их можно было взглядом быстро просматривать.
                                  Ну перенос тела CTE на новую строку тоже надо настроить да но у меня datagrip очень любит сопротивляться настройке отступов, надо наверное сносить и новый ставить а там багрепорты писать пачками.
                                  +1
                                  Давайте я попробую.

                                  Вот всё хорошо, но после select, from и where мне кажется нужно делать новую строку с отступом.


                                  Да и после открывающей скобки. Тогда отступ у селектов после as будет одинаковый и не надо будет напрягаться, чтобы понять, что это запросы одного уровня.


                                  Лично я ещё и после with перехожу на новую строку и не делаю на этой строке отступ.

                                    0
                                    Вот всё хорошо, но после select, from и where мне кажется нужно делать новую строку с отступом.

                                    Пробовал, плохо получается когда большой каскад идет.

                                    Да и после открывающей скобки. Тогда отступ у селектов после as будет одинаковый и не надо будет напрягаться, чтобы понять, что это запросы одного уровня.

                                    Это да, донастроить надо, почему то датагрип очень любит мозги мне потрогать когда отсупы настраиваешь.
                              0
                              И имя ему with
                              select resp_q as (
                              select resp_name, userid  
                              from resp where r.end_date > sysdate)
                              ,main_q as (
                              select u.name, r.respname
                              from user u 
                              left join resp_q r on u.id = r.userid
                              where id = 1)
                              select * from main_q

                              Вопрос на засыпку. А где тут, собственно, with?
                              Я вижу только вложенный запрос. Или это он имеется ввиду (если что, такой трактовки with в SQL еще не встречал)?

                                0
                                Спасибо) Поправил
                                –1

                                Как бы там ни было, мне статья принесла пользу. Пару лет не работал с SQL и забыл про тот же with. На днях снова с ним столкнулся и вот как раз нужно было оптимизировать довольно крупный запрос со всякими вложенностями. Понятно, что хорошо бы почитать перед этим книги и статьи, для освежения памяти, но времени мало. Наткнулся на статью и вспомнил про with, теперь копну в нужном направлении и сделаю что требуется. Так что спасибо автору, такие обзоры несут реальную пользу на моем примере, для джунов или нет :)

                                  +1
                                  Аккуратней с CTE, помните что его части выполняются параллельно и в зависимости от базы там есть нюансы с материализацией.
                                  –1
                                  Спасибо, тоже про with копать буду.
                                    0
                                    главное что платят

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

                                    Самое читаемое