Проходил 14 сентября 2018 года в 20:00

Открытый вебинар Реляционные СУБД
Использование индексов

Открытый вебинар онлайн

Поговорим о типах индексов (Btree, Bitmap, Hash), обсудим их преимущества и недостатки. В каких случаях какие именно применяются, как они работают при операциях SELECT/INSRT/UPDATE/DELETE, а также когда их применять не стоит.
Преподаватель
Алексей Цыкунов

Запись

О курсе

Реляционные СУБД
30 000 ₽
3 месяца
Начало 28 мая
Курс обеспечивает глубокое погружение в реляционные СУБД, чтобы проектировать базы данных так, чтобы впоследствии не приходилось тушить пожары в результате не оптимально заложенных основ. Цель курса — научить слушателя эффективно работать с любой реляционной базой данных с помощью языка структурированных запросов SQL. В результате обучения выпускники смогут уверенно работать со структурой СУБД, создавать оптимальные SQL-запросы и разбираться с некорректными, работать ...
Программа курса
Модуль 1
Сущности и связи
Модуль 2
SQL
Модуль 3
Оптимизация БД
Сущности и связи
Реляционная модель данных. Реляционная алгебра
познакомиться с реляционной теорией,
понять принципы проектирования реляционных баз данных,
освоить приемы нормализации данных,
построить свою модель
28 мая, 20:00 — 21:30
Домашние задания: 1
ДЗ
Строим модель данных. Задача: реализовать модель данных БД, определить сущности, построить связи, выполнить декомпозицию и нормализацию

За основу берем практическую структуру данных

Варианты:
1) заказ в ресторане: клиент, столик, состав заказа, цена каждого блюда в заказе, начало заказа, конец заказа, официант, счет, чем оплачивал, дисконт, чаевые - итд
2) тариф телефонии: оператор, направление (код, страна, город, мобильный оператор), стоимость за минуту, параметры округления, время суток. даты действия тарифа. комментарий
3) блог: пост. датавремя, заголовок, картинки, автор, лайки, комменты, репосты, просмотревшие. время последнего просмотра/редактирования
или любой свой вариант
4) Персональные данные: сами персональные данные, пользователи и роли имеющие права для выборки и правки, журнал изменений
5 ) любой ваш проект

Описанные сущности не являются полным и конечным ТЗ (как это и бывает в жизни). Вы как архитектор должны предусмотреть необходимые атрибуты и дополнительные сущности по необходимости. И четко представлять бизнес-задачу которую будет решать эта структура данных.

делаем декомпозицию и нормализацию
в качестве сделанной ДЗ принимается pdf с начальной и конечной моделью

Решая данное ДЗ вы тренируете навык проектирования БД, выделения сущностей и их атрибутов, построения связей, нормализации данных
Типы данных
правильно выбирать типы данных для проектирования таблиц,
осознать преимущества и недостатки типов данных
31 мая, 20:00 — 21:30
Домашние задания: 1
ДЗ
Корректируем типы данных в модели при необходимости. Взять свою структуру данных из ДЗ-1
и прописать комментарии на каждое поле с пояснением почему выбран данный тип поля и на сколько он удовлетворяет поставленной задаче


При решении данной ДЗ формируем навык документирования, и ревью собственной структуры, перспективной оценки на возможные проблемы из-за некорректного выбора типов данных

варианты как сделать
для Mysql Workbench есть плагины для формирования документации
https://github.com/d1soft/mysql-workbench-html-doc-generator
https://github.com/letrunghieu/mysql-workbench-plugin-doc-generating

то есть вы создаете таблицы
прописывается комментарии на поля
и затем этим плагином формируете документацию

для других инструментов тоже могут быть подобные инструменты
http://dbdoc.sourceforge.net
https://github.com/extronics/dbdoc
https://www.liquibase.org/documentation/dbdoc.html
Компоненты современной СУБД
понять и использовать в работе многообразие компонентов и возможностей СУБД: как управлять пользователями, какие типы коннектов бывают, как создавать индексы, как создавать ограничения (constrains), что такое триггеры, зачем нужны последовательности и очереди, для чего использовать хранимые процедуры и функции, что такое DB links, представления (view) и синонимы, Jobы
4 июня, 20:00 — 21:30
Домашние задания: 1
ДЗ
Добавляем в модель данных дополнительные индексы и ограничения. 1. Проводим анализ возможных запросов\отчетов\поиска данных.
2. Предполагаем возможную кардинальность поля.
3. Создаем дополнительные индексы - простые или композитные.
4. На каждый индекс пишем краткое описание зачем он нужен (почему по этому полю\полям).

5. Думаем какие логические ограничения в БД нужно добавить - например какие поля должны быть уникальны, в какие нужно добавить условия, чтобы не нарушить бизнес логику. Пример - нельзя провести операцию по переводу средств на отрицательную сумму.
6. Создаем ограничения по выбранным полям.
Транзакции. ACID
изучить особенности работы MVCC, уровни блокировок и изоляций транзакций,
проектировать оптимальные транзакционные модели без взаимных блокировок,
проектировать сложные транзакционные модели на примерах.
7 июня, 20:00 — 21:30
Домашние задания: 1
ДЗ
Описываем логику транзакций для своей модели данных. В вашем проекте базы данных опишите
транзакцию, которая будет включать в себя несколько действий
и исключать возможность взаимных блокировок и неоднозначных ситуаций

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

пример описания бизнес задачи:
Представим систему где оператор назначает специалистов ЖКХ на решение определенных проблем

задача:
назначение задачи специалисту
фазы
- выбор задачи
- выбор свободного специалиста
- назначение заданного специалиста на заданную задачу на заданное время

особенности
- назначения ведут несколько операторов
- исключить возможность того что несколько операторов назначают специалиста на одну и ту же задачу
- исключить возможность того что несколько операторов на разные задачи назначают одного и того же специалиста на одно и то же время

В случае проблем с пониманием какую транзакцию можно предоставить решение для вышеописанной задачи
Проектирование БД
разобрать вопросы и проблемы обслуживания и сопровождения, которые важно учесть на этапе проектирования
11 июня, 20:00 — 21:30
Лектор: Алексей Цыкунов
Домашние задания: 1
ДЗ
Доработка проекта. С учетом всех пройденных занятий доработать свой проект до финального представления
В проекте должны быть
- схема
- документация
- примеры бизнес-задач которые решает база
- рекомендации к использованию репликации
- рекомендации к резервному копированию

ДЗ автоматически защитывается при защите проекта на последнем занятии модуля
Проектирование БД: OLAP
понять, что такое OLAP;
зачем нужен Cube и его состав;
строить сводную таблицу по данных из куба
14 июня, 20:00 — 21:30
Лектор: Михаил Ржевский
Проектирование БД - семинар
презентовать проект,
проанализировать созданные проекты,
обсудить и внести предложения для корректировки
18 июня, 20:00 — 21:30
SQL
Изучаем SQL. Применяем на практике.
DDL: создание и редактирование объектов БД
использовать синтаксис SQL для описания структуры БД
21 июня, 20:00 — 21:30
Домашние задания: 1
ДЗ
Создаем базу из проектной модели. Цель ДЗ - научится писать скрипт создания БД и разворачивать свой дев проект в докере


Создаем Dockerfile
в котором разворачивается image нужной СУБД
и скриптом разворачивается структура вашего проекта
наружу должен быть прокинут порт для подключения
формат сдачи - github репозиторий
в README должно быть описано как подключится к вашей базе (субд, название базы, логин, пароль, порт)

* для учебной базы сделать скрипт для партиционирования таблицы CDR по месяцам (на выходе SQL  скрипт на модерацию + дамп измененной структуры)
** проанализировать базу voip и создать внешние ключи (скрипт для создания ключей + схема данных)
DML: изменение данных
писать SQL для модификации данных,
изменять данные и сохранять изменения
25 июня, 20:00 — 21:30
Домашние задания: 1
ДЗ
Создаем скрипты очистки и первичного заполнения базы. Наполняем свою базу данными!!

1. Запросы на встравку данных INSERT VALUES
2. Запросы на insert с использованием Select
3. Изменение данных UPDATE, UPDATE с использованием JOIN
4. Delete
5. Процедура со вставкой и обновлением блока
6. Merge – потренироваться и прочувствовать

формат сдачи - расширяем докерфайл - который при старте поднимает базу с данными

DML: выборка данных
использовать различные способы выборки данных из одной и нескольких таблиц,
использовать нужный тип JOIN и различные условия в WHERE
28 июня, 20:00 — 21:30
Домашние задания: 1
ДЗ
SQL выборка. Рассчитываем стоимость звонка в БД voip
Строим Select для выборки всех звонков

поля для связи
CDR.src_ip -> oper_ip.IP_OP
oper_ip.OP_ID -> SITE.ID
SITE.rate_o -> RATES.RATE_ID
RATES.DEST_ID -> DEST_CODES.CODE_ID

RATES.price - цена
DEST_CODES.CODE. - код направления

необходимо учитывать дату звонка, дату действия тарифа и дату действия IP оператора

В oper_ip также могут быть дублирующие IP. в том числе и по дате (есть условия по префиксу номера и ip ceрвера) -
для облегчения задачи можно вытащить уникальные записи в отдельную таблицу oper_ip_tmp

* сделать выборку с учетом префикса номера в oper_ip
DML: агрегация и сортировка
группировать и сортировать данные и использовать групповые функции
2 июля, 20:00 — 21:30
Домашние задания: 1
ДЗ
Создаем отчетную выборку. Построить статистический запрос и показать группировку по странам (по каждому коду необходимо вычислить страну и сгруппировать по стране)
- по суммарному времени
- по кол-ву звонков
- по кол-ву нулевых звонков

должны быть представлены 3 SQL

* Использовать RollUP для развернутого отчета по странам и направлений внутри стран
DML: вложенные запросы и представления
использовать вложенные запросы и сохранять их во view,
использовать конструкцию WITH, операции множеств UNION, EXCEPT, INTERSECT, дополнительные возможности выборки
5 июля, 20:00 — 21:30
Домашние задания: 1
ДЗ
Создаем дополнительные представления в БД. сохранить запрос из предыдущего ДЗ как представления

решить бизнес-задачу
мы хотим дать оператору Х возможность просматривать свои звонки
он должен видеть данные из таблицы CDR но не должен видеть от кого или куда звонки ушли
за исключением того что он видит что звонки пришли либо от него либо не него
те - он должен видеть свои IP в качестве SRC_IP или DST_IP, а чужие IP он в этих полях видеть не должен
финансовых полей другой стороны оператор также видеть не должен

в качестве решения принимаются одно или несколько представлений
DML: аналитические функции
понять, что такое «Окно» в аналитических функциях,
писать запросы с аналитическими функциями,
оптимизировать существующие решения через аналитические функции
9 июля, 20:00 — 21:30
Домашние задания: 1
ДЗ
Отчетная PIVOT-выборка + Аналитические функции. 1. Выберите id и имя оператора из таблицы операторов; пронумеруйте записи по названию оператора, так чтобы при изменении буквы алфавита нумерация начиналась заново
Также добавьте в выборку:
- общее количество операторов
- общее количество операторов в зависимости от первой буквы названия фильма
- следующий ид оператора
- предыдущий ид оператора
- название оператора 2 строки назад

2. Гос дума выпустила новый закон, по которому все компании занимающиеся телефонией должны подписать с операторами, с которыми есть взаимодействие, дополнительное соглашение о конфиденциальности.
У нас всего 3 менеджера, помогите сформировать 3 группы операторов, чтобы каждый мог связаться с оператором оперативно.
В результатах должен быть номер группы, ид оператора, его название, название организации и телефон

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

4. Посчитайте платежи в системе в разрезе год (строка) - месяц (колонка), на пересечении должна быть сумма платежей за этот год и месяц.

Если у вас есть проблемы с БД voip и оконными функциями, которые есть только с MySQL 8.0, ниже альтернативное ДЗ по БД Sakila - dvdrental
1. Введение в функции
Посчитайте по таблице фильмов, в вывод также должен попасть ид фильма, название, описание и год выпуска
пронумеруйте записи по названию фильма, так чтобы при изменении буквы алфавита нумерация начиналась заново
посчитайте общее количество фильмов и выведете полем в этом же запросе
посчитайте общее количество фильмов в зависимости от буквы начала называния фильма
следующий ид фильма на следующей строки и включите в выборку
предыдущий ид фильма
названия фильма 2 строки назад
Для этой задачи не обязательно писать аналог без функций

2. Вахтер Василий очень любит кино и свою работу, а тут у него оказался под рукой ваш прокат (ну представим что действие разворачивается лет 15-20 назад)
Василий хочет посмотреть у вас все все фильмы при этом он хочет начать с самых коротких и потом уже смотреть более длинные
сделайте группы фильмов для Василия чтобы в каждой группе были разные жанры и фильмы сначала были с низким рейтингом, а затем с более высоким
В результатах должен быть номер группы, ид фильма, название, и ид категории (жанра).

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

4.Нужно выбрать последний просмотренный фильм по каждому актеру
В результатах должно быть ид актера, его имя и фамилия, ид фильма, название и дата последней аренды.
Для этого задания нужно написать 2 варианта получения таких данных - с аналитической функцией и без нее.
Данные в обоих запросах (с оконными функциями и без) должны совпадать.
Семинар: практический SQL
читать план запроса,
определять, что нужно оптимизировать
12 июля, 20:00 — 21:30
Оптимизация БД
Внутренняя архитектура СУБД
настроить СУБД, влияющие на ее производительность
16 июля, 20:00 — 21:30
Индексы
разобраться в типах индексов, плюсах и минусах,
создавать, удалять и мониторить индексы для оптимизации БД
19 июля, 20:00 — 21:30
Домашние задания: 1
ДЗ
Индексы. в материалах приложен репозиторий на учебную базу с продуктами и запрос

задача - оптимизировать запрос с помощью создания нужных индексов
результат - запрос с индексами выполняется быстрее

формат сдачи -
сделать форк репозитория к себе
добавить в докерфайл создание индексов
Хранимые процедуры и триггеры
писать хранимые процедуры и функции
23 июля, 20:00 — 21:30
Домашние задания: 1
ДЗ
Добавляем в базу хранимые процедуры и триггеры. создаем в базе voip

хранимые функции для определения стоимости звонка

хранимую процедуру для рассчета данных за один день
- берется последний час которые есть в DAYSTAT
- рассчитываются данные за следующий день
- заносятся в DAYSTAT
- при следующем запуске считается следующий день
- если при первом запуске в DAYSTAT нет данных - считаются данные с того дня который первый присутствует в CDR

Оптимизация на практике
оптимизировать и профилировать запросы
30 июля, 20:00 — 21:30
Домашние задания: 1
ДЗ
Оптимизация сложного запроса. В материалах предоставлена ссылка на git репозиторий с базой с продуктами и учебным запросом

Задача - ускорить запрос
Опции хранения
спрогнозировать рост данных и количества запросов
и предложить варианты решения
26 июля, 20:00 — 21:30
Домашние задания: 1
ДЗ
Спрогнозировать рост данных и спроектировать модель хранения и архивации. Формируем документ в котором описываем
- прогноз по возможному росту базы
* рост данных
* рост количества пользователей
* всплески одновременных соединений

- описываем возможные угрозы и методы защиты от них
* предлагаем стратегии бэкапа
- репликации
- кластеризации
Проблемы миграции
разобраться в видах миграции,
узнать основные проблемы миграции,
решить проблемы, возникшие при миграции
2 августа, 20:00 — 21:30
Управление СУБД
делать бэкап и затем восстанавливаться с него
6 августа, 20:00 — 21:30
NoSQL: Redis и MongoDB
понимать, когда и для каких целей имеет смысл использовать NoSQL,
применять Redis и Mysql в качестве кешей
9 августа, 20:00 — 21:30
Обсуждение проектов
защитить проект
ознакомиться с результатами проектов одногруппников
13 августа, 20:00 — 21:30
Домашние задания: 1
ДЗ
Проектная работа.

Преподаватель

Алексей Цыкунов
Системный архитектор, Oracle DBA, разработчик perl/python, опыт в отрасли - 20 лет
В настоящее время работает на аутсорсе. Работал в интернет-провайдерах и телекоме. Имел опыт внедрения и разработки продуктов в госструктурах, таких как минздрав и соцфонд. Был главным разработчиком стартап проекта Ipstudio AMBS (биллинг для VoIP). Участвовал в проектировании и разработке OLTP систем. Проектировал и развертывал серверные системы в датацентрах.

Закончил Кыргызско-Российский Государственный Университет, 2001, Инженер автоматизированных систем.
Собственный технический блог на - dbadmins.ru
Преподаватель курсов:
Реляционные СУБД