Эволюция моих SQL запросов
Всем привет! Я тимлид и Senior Oracle Developer, 12 лет работаю с OeBS и в основном пишу SQL запросы. Хотел бы рассказать, как за это время менялся мой подход в написании SQL запросов.
Вначале было слово, а точнее запрос. Скажем
select name from user where id = 1
Написать такой запрос как-то не так практически невозможно. Он одинаково хорошо работает во всех известных мне базах данных. А знаю я только oracle :З Но подозреваю что и в других реляционных тоже всё будет ок.
Так что же произошло? Проблемы начались, когда таблиц стало две:
select name from user u, rest r where u.id = 1 and u.id = r.user_id
Этот код вызывал у меня больше вопросов. Например, как должны быть соединены эти таблицы? Казалось бы что проще id = user_id
, но мне что-то не нравилось. В блоке where мне не хватало четкого разделения между условиями фильтрации и соединениями таблицам. Когда запрос содержал 2 таблицы всё ещё было норм, но когда кол-во таблиц доходило до 5 – всё рассыпалось. Взглянув на запрос, я не мог сразу понять как соединены таблицы и не пропущена ли какая-то связка. И с этим все прекрасно жили, но я не мог. Однажды мне, молодому джуну, на глаза попался ANSI синтаксис.
select name from user inner join rest 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.
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 позволили мне разбить монолитный запрос и группировать разные кусочки запроса по “историям”, а потом сплетать их вместе. Я мог рассказать про свой запрос так: “Получаем список пользователей. Список ролей. Объединяем их в одну выборку и отсекаем тех кто нам не нравится. С оставшимися идём дальше, взявшись за руки.” И за каждый шаг отвечала свой небольшой именованный запрос. Это также помогло мне бороться с моим злейшим врагом 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 не происходит почти ничего нового уже много лет, тем не менее всегда приятно найти возможность улучшить свои запросы.