Smart Data

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

Жизненный цикл запросов и план выполнения запроса на примере PostgreSQL - Часть 1 Всем привет. Давно не публиковал полезности на канале. Сегодня хочу немного рассказать о жизненном цикле запросов и плане их выполнения. Если не сильно усложнять, то жизненный цикл запроса можно разделить на 3 основных этапа: 1) Парсинг запроса. На этом этапе парсер проверяет синтаксис запроса, компилирует SQL в более подходящий для компьютера вид на основе хранимых в системе правил и отправляет запрос к базе данных. 2) Оптимизация и построение плана выполнения запроса. На этом этапе формируются планы-кандидаты, для которых расчитывается стоимость (query cost). На основе стоимости планировщик выбирает оптимальный план для выполнения запроса. 3) Непосредственное выполнение запроса. На этом этапе запрос выполняется, согласно выбранному плану, и возвращает результат пользователю. Переходим непосредственно к плану выполнения запроса. Чтобы увидеть план выполнения запроса, в PostgreSQL существует команда EXPLAIN. Важно отметить, что EXPLAIN не выполняет запрос, а только показывает план его выполнения. Для того, чтобы отобразить план выполнения запроса, достаточно просто указать EXPLAIN перед основным запросом. Например:

EXPLAIN 
SELECT * FROM retail.orders_fact;

Запрос вернёт нам результат следующего вида:

Seq Scan on orders_fact  (cost=0.00..195.86 rows=9186 width=55)

Как мы видим, наш план выполнения запроса состоит из 1 шага Seq Scan (Sequential Scanning), что означает последовательное сканирование всех строк таблицы. В скобках указаны различные оценки (estimates) выполнения запроса: - первое число в cost обозначает время, которое проходит, прежде чем начнётся этап вывода данных; - второе число в cost обозначает общую стоимость выполнения. Общая стоимость выполнения = время начала вывода данных + время окончания вывода данных; - число в rows указывает на число строк, которое должен обработать запрос; - число в width отображает количество байт, которое обработает запрос для вывода нужных строк. Давайте теперь рассмотрим другой пример:

EXPLAIN 
SELECT * FROM retail.product_dim 
WHERE category IN ('Furniture', 'Technology');

Запрос вернёт нам такой результат:

Seq Scan on product_dim  (cost=0.00..58.14 rows=862 width=91) 
-> Filter: ((category)::text = ANY ('{Furniture,Technology}'::text[]))

Как мы видим, оператор WHERE добавил шаг Filter. Здесь важно сказать, что план выполнения запроса, как правило, читается снизу-вверх или справа-налево. В данном случае мы читаем наш план снизу вверх: 1) Первым выполняется Filter, который отбирает только те строки, где категория товара равна или Furniture, или Technology. 2) После фильтрации происходит последовательное сканирование таблицы (отфильтрованных строк). Теперь давайте сделаем трюк и создадим индекс на столбец category:

CREATE INDEX category_idx on retail.product_dim (category);

И снова выполним запрос:

EXPLAIN 
SELECT * FROM retail.product_dim 
WHERE category IN ('Furniture', 'Technology');

Теперь этот же запрос выдаёт нам такой результат:

Index Scan using category_idx on product_dim  (cost=0.28..41.71 rows=862 width=91) 
   Index Cond: ((category)::text = ANY
   ('{Furniture,Technology}'::text[]))

Как мы видим, теперь вместо 2-х этапов в плане выполнения запроса только один - Index Scan (сканирование индекса). Index Cond объясняет, почему происходит сканирование индекса, указывая, что в WHERE мы фильтруемся по полю, на которое и создали индекс. Также можно заметить, что добавление индекса сократило общую стоимость выполнения запроса с 58.14 до 41.71. Здесь мы имеем наглядный пример того, как индексы могут оптимизировать скорость выполнения запроса. Но не забываем, что индексами нужно пользоваться с умом (об этом я писал здесь). На сегодня всё. Ждите 2 часть🙂

Стань дата-инженером с Яндекс Практикумом Сервис онлайн-обучения цифровым профессиям Яндекс Практикум запускает программу обучения по специальности «Инженер данных». Курс предназначен для студентов с как минимум базовым знанием SQL и Python – перед стартом необходимо пройти тест. Авторы и преподаватели – практикующие эксперты ведущих российских IT-компаний. Длительность – 6,5 месяцев. Курс на 75% состоит из практических занятий – по окончании программы в вашем портфолио будет не менее 10 проектов. Вы научитесь: - работать с технологиями Python, SQL, Metabase, Airflow, PostgreSQL, MongoDB, ClickHouse, Celery, Kafka, Hadoop, Apache Spark, Spark Streaming и Yandex.Cloud - извлекать, очищать и сохранять данные - создавать и поддерживать хранилища типов Data Warehouse и Data Lake - работать со стриминговой обработкой данных и облаками. Претендовать на работу по новой специальности студенты курса смогут уже в ходе обучения – с поиском вакансии помогут специалисты карьерного центра Яндекс Практикум. Запись на курс уже открыта, старт занятий для первого потока студентов – 21 марта. Стоимость курса: 95 000 рублей при разовой оплате, при оплате в рассрочку – 17 000 рублей в месяц. По завершении программы студенты получат сертификат о повышении квалификации. Запись на бесплатную вводную часть и подробности по ссылке.

Как я и ожидал, результаты опроса получились далеко неоднозначными. Около половины опрошенных считает, что нужно глубоко погружаться в теорию Computer Science, чтобы быть хорошим специалистом, и что без этой базы никуда. Другая половина либо считает по-другому, либо сомневается, либо имеет более развёрнутое мнение на этот счёт. Спасибо всем за мнение и комментарии! Теперь хочу выразить своё видение по этому поводу и написать несколько тезисов, к которым я пришёл на базе своего опыта, опыта коллег, результатов опроса и ваших комментариев. - Уровень зарплаты зависит от меры влияния на бизнес. Т.е. не так важно, как много вы знаете и умеете с технической стороны, более важно - какую бизнес-ценность вы приносите. Вы можете хорошо знать теорию Computer Science, но плохо представлять конечную бизнес-задачу. А именно решение бизнес-задач приносит деньги компаниям и, соответственно, вам. Поэтому, я считаю, что знания Computer Science уходят на второй план по сравнению с навыком бизнес-ориентированности. Не забываем про soft-skills😉 - Понравился комментарий @dimoobraznii, что начинающему специалисту самое важное - побыстрее найти работу и получить опыт на реальных проектах. После того, как уже понял, как всё устроено и закрепился в индустрии - начинать учить что-то сложнее по типу алгоритмов, структур данных и внутренностей баз данных. Со своей стороны, сделаю ещё несколько уточнений. Считаю, что сразу начать копаться в Computer Science - хороший вариант для студентов, которые после школы поступили на соответствующий факультет в ВУЗ. Если нет проблем с финансами и хочется спокойно закончить универ, то это вариант в 21-22 года с хардкорными фундаментальными знаниями прийти в IT и надолго здесь остаться. Если же вы не студент Computer Science, хотите перейти в IT из другой сферы и вам нужно побыстрее найти работу и зарабатывать деньги - с углублением в Computer Science на первом этапе вы потратите очень много времени, за которое индустрия и требования в ней могут сильно поменяться. - Знания Computer Science более ценны для on-premise решений и менее ценны в облаке. Как мы знаем, on-premise решение - это когда мы всё делаем самостоятельно: покупаем сервера, устанавливаем OS, строим физическую сеть, устанавливаем нужный софт, настраиваем репликацию, шардирование, мониторинг и т.д. Всё это требует больших усилий, и знания архитектуры ЭВМ, внутренностей баз данных, компьютерных сетей и распределённых систем могут очень помочь. В облаке, напротив, огромную часть задач с нас снимает облачный провайдер, и нам, преимущественно, достаточно знать особенности отдельных сервисов и как ими пользоваться. Главный вывод: знать Computer Science круто и полезно, но учить его стоит тогда, когда вы уже умеете решать бизнес-задачи, на верхнем уровне понимаете, из каких компонентов состоит IT-решение (будь-то аналитическое или какое-то другое) и какие инструменты можно использовать. Разберитесь, как строятся архитектуры для аналитических решений, научитесь хорошо писать SQL-запросы и оптимизировать их. Разберитесь, как строятся DWH и какие best-practices есть в ETL. Научитесь писать рабочий код. Поковыряйте какое-то облако (AWS, Azure, GCP, Yandex Cloud, что угодно). С этим всем можно решить большинство бизнес-задач. А дальше можно углубляться в теорию и разбирать, как работает процессор:)

Хочу немного отвлечься от "техники" и затронуть достаточно философский вопрос, о котором я частенько задумываюсь: Нужно ли сидеть за теорией из Computer Science, разбирая алгоритмы, структуры данных, внутренности баз данных и копать вплоть до того, как на физическом уровне работает процессор? Либо не стоит так заморачиваться и нужно просто брать разные инструменты, которые решают определённую задачу, и просто их "щупать"? Особенно на фоне постоянно появляющихся новых тулзов. Некоторые будут говорить, что основы Computer Science - это база, без которой никуда, а некоторые, что они не заканчивали Computer Science, но при этом успешно работают в индустрии. Решил сделать опрос, чтобы посмотреть, в каком отношении распределяются мнения) После опроса напишу своё видение на этот счёт.

Индексы - на логическом* уровне это отсортированные ключи колонки таблицы (для которой и создаётся индекс), которые имеют указатели на местонахождение строк основной таблицы со значением колонки в индексе. Если сильно упростить, то индекс в базах данных очень похож на индекс в конце книг. Когда вы открываете индекс на последних страницах книги, у вас есть список ключевых слов, отсортированных от А до Я. Для каждого слова есть номера страниц, где это слово упоминается, что облегчает поиск. По такому же принципу работают индексы и в БД. Важно сказать о том, что индексы в большей степени используются в традиционных реляционных СУБД (таких как PostgreSQL, MySQL, Microsoft SQL Server и т.д.). Современные колоночные решения, такие как Google BigQuery, Snowflake или Amazon Redshift используют специальный слой метаданных, который решает задачу повышения эффективности поиска и скорости выполнения запросов. Когда использовать: - на больших таблицах; - когда увеличение скорости выполнения запросов за счёт индексов имеет больший профит, чем затраты на хранение и поддержку индексов; - на часто используемых полях в фильтрах для часто используемых запросов. Предположим, у нас есть большая таблица orders, и мы часто, запрашивая данные и з неё, фильтруем данные по полю segment (сегмент клиента, который совершил заказ). Например, мы пишем такой запрос: SELECT * FROM orders WHERE segment = 'Corporate'; Мы можем создать индекс на столбец "segment", чтобы увеличить скорость выполнения запроса. Например, в PostgreSQL индекс можно создать так: CREATE INDEX segment_idx on orders (segment); Т.е. создавать индексы - быстро и просто. Индексы могут быть также составными - включать несколько столбцов. Когда не использовать: - на небольших таблицах. Индексы требуют дополнительного пространства для их хранения. Поэтому, если скорость выполнения запросов без индекса вас вполне устраивает, то и не нужно использовать дополнительные ресурсы для хранения индексов; - на колонках с большим количеством null-значений; - на часто обновляющихся таблицах. Частые операции вставки и обновления полей таблиц повышают нагрузку на СУБД, так как значения нужно записать/обновить в двух местах - в основной таблице и в индексе. Если вы часто производите удаления в основной таблице, то индекс становится фрагментированным (т.е. включает в себя пустые "листы"), что приводит к его неэффективности; - на колнках с низкой кардинальностью (низким количеством уникальных значений). Например, если ваша колонка, на которую вы создаёте индекс, включает в себя только True/False значения, создание индекса не принесёт вам много профита. * Я не просто в определении индекса написал "на логическом уровне". Логический уровень - это абстракция, которая позволяет описать объект обобщённо и как можно проще без углубления в детали. Есть ещё физический уровень, который описывает, чем на самом деле "под капотом" являются индексы. На физическом уровне для создания индексов используются специальные структуры данных: как правило, это B+tree или hash-таблицы. О структурах данных я тоже напишу, но сейчас я хочу двигаться от логического уровня к физическому для упрощения восприятия.

Сегодня хочу рассказать про партиционирование и индексы в базах данных. Партиционирование - это метод разделения одной большой таблицы (родительской) на много маленьких таблиц (партиций). Разделение родительской таблицы происходит по заданному условию: например, у нас есть таблица с заказами, каждый заказ произошёл в определённую дату. Мы можем партиционировать эту таблицу по дате заказа. Когда использовать: - для более гибкого использования физического хранилища данных. Например, мы можем хранить отдельные партиции на разных серверах и для хранения редко запрашиваемых данных использовать более дешёвую технологию хранения на отдельном сервере; - для гибкого управления данных в таблицах. Мы можем быстро добавлять и удалять данные на уровне партиций; - для ускорения выполнения SQL-запросов к таблице. Партиционирование может существенно повысить скорость запросов, так как движок будет сканировать отдельные партиции, в которых лежат нужные для запроса данные, а не всю таблицу. Вернёмся к нашему примеру с датой заказа: например, у нас есть таблица orders, и мы пишем запрос к ней, запрашивая только заказы за 2021-12-15:

SELECT * FROM orders WHERE order_date = '2021-12-15';

Так как наша таблица партиционирована по дате заказа, движок просканирует только одну партицию "2021-12-15", а не всю таблицу (в случае, если партиционирование не используется). Скорость выполнения такого запроса вырастет во много раз. Когда не использовать: не нужно использовать партиционирование на небольших таблицах. Если говорить о традиционных реляционных СУБД, создание и поддержка партиций является не самой тривиальной задачей, и, если таблица небольшая, выгода от партиций будет намного меньше затрат на их создание и поддержку. Более того, на небольших таблицах партиционирование может даже снижать производительность запросов.

Materialized views - специальный тип представлений, который так же, как и стандартное представление является хранимым запросом. Но в отличие от стандартного представления материализованное хранит уже данные, а не просто направление к ним. Т.е. результат запроса материализуется и становится похожим на обычную таблицу, к которой мы можем писать запросы. Процесс материализации и обновления представления происходит в заданный интервал времени на основе изменений в базовых таблицах, к которым это представление обращается. Когда использовать: материализованные представления хорошо подходят в случаях, когда запросы к стандартным представлениям становятся слишком медленными и снижают эффективность работы. Когда не использовать: нужно понимать, что любая материализация данных требует дополнительных ресурсов хранения, поэтому не нужно, например, создавать материализованные представления, чтобы повысить скорость выполнения запроса по сравнению со стандартным представлением, если использование стандартного представления вполне приемлемо и не снижает вашу эффективность. Также нужно отталкиваться от особенностей конкретной СУБД. Например, в Snowflake обновление материализованного представления, является автоматическим процессом, который требует дополнительных расходов. Поэтому, чем чаще изменяется базовая таблица, тем чаще происходит автоматическое обновление представления и тем больше расходов вы понесёте на их поддержку. Поэтому, ко всему нужно подходить с умом. В следующий раз сделаю похожий пост про партиции и индексы.