🌿 Весенние старты — весенние скидки!
-10% на все курсы апреля до 27.04! →

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

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

Name: Открытый вебинар Реляционные СУБД Использование индексов
Start: 2018-09-14T17:00+0000
End: 2018-09-14T19:00+0000
Location: OTUS

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

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

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

Алексей Цыкунов

Запись

О курсе

Базы данных

110 000 ₽

6 месяцев

Начало 27 мая

Курс предназначен для разработчиков и администраторов, готовых освоить работу с базами данных

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

На курсе вы:

научитесь проектировать базы …

Подробнее о курсе Пройти тестирование

Программа курса

Модуль 1
Сущности и связи

Модуль 2
PostgreSQL

Модуль 3
MySQL

Модуль 4
NoSQL (Redis, Tarantool, MongoDB, Cassandra)

Модуль 5
Проектная работа

Сущности и связи

В данном модуле рассмотрим проблемы проектирования БД

Проектирование БД

понимать вопросы и проблемы обслуживания и сопровождения, которые важно учесть на этапе проектирования;

27 мая, 20:00 — 21:30

Лектор: Михаил Ржевский

Домашние задания: 1

Компоненты современной СУБД

использовать в работе компоненты и возможности СУБД;
проектировать и создавать индексы;
устанавливать ограничения на поля и целые таблицы;

3 июня, 20:00 — 21:30

Лектор: Дмитрий Кириллов

Домашние задания: 1

ДЗ

Добавляем в модель данных дополнительные индексы и ограничения. 1. Проводим анализ возможных запросов\отчетов\поиска данных.
2. Предполагаем возможную кардинальность поля.
3. Создаем дополнительные индексы - простые или композитные.
4. На каждый индекс пишем краткое описание зачем он нужен (почему по этому полю\полям).

5. Думаем какие логические ограничения в БД нужно добавить - например какие поля должны быть уникальны, в какие нужно добавить условия, чтобы не нарушить бизнес логику. Пример - нельзя провести операцию по переводу средств на отрицательную сумму.
6. Создаем ограничения по выбранным полям.

Проблемы миграции

разобраться в видах миграции;
узнать основные проблемы миграции;
решить проблемы, возникшие при миграции.

6 июня, 20:00 — 21:30

Лектор: Анастасия Чекушина

Теорема CAP

понимать смысл теоремы CAP;
различать BASE и ACID требования к базам;
понимать проблему выбора СУБД.

10 июня, 20:00 — 21:30

Лектор: Антон Герасименко

Семинар: проектирование

рассказать о своем проекте;
получить фидбэк от коллег и преподавателя.

17 июня, 20:00 — 21:30

Лектор: Михаил Ржевский

PostgreSQL

В данном модуле рассмотрим архитектуру и применение PostgreSQL

Внутренняя архитектура СУБД PostgreSQL

иметь представления об устройстве СУБД PostgreSQL;
установить СУБД PostgreSQL;
создать базу данных и подключаться к ней.

20 июня, 20:00 — 21:30

Лектор: Виктор Коробков

Домашние задания: 1

DDL: создание, изменение и удаление объектов в PostgreSQL

создавать и редактировать объекты БД в СУБД PostgreSQL.

24 июня, 20:00 — 21:30

Лектор: Виктор Коробков

Домашние задания: 1

Изоляция транзакций в PostgreSQL

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

27 июня, 20:00 — 21:30

Лектор: Дмитрий Тарасов

Транзакции, MVCC, ACID в PostgreSQL

разбираться в уровнях изоляции транзакций;
избегать взаимных блокировок;
разбираться в различных видах блокировок;
строить транзакции в PostgreSQL.

1 июля, 20:00 — 21:30

Лектор: Антон Герасименко

DML: вставка, обновление, удаление, выборка данных в PostgreSQL

создавать различные типы связей между таблицами;
добавлять и обновлять данные со сложными выборками;
удалять данные с подзапросами.

4 июля, 20:00 — 21:30

Лектор: Анастасия Чекушина

Домашние задания: 1

Типы данных в PostgreSQL

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

8 июля, 20:00 — 21:30

Лектор: Дмитрий Кириллов

Индексы в PostgreSQL

разобраться в типах индексов, плюсах и минусах;
создавать, удалять и мониторить индексы для оптимизации БД.

11 июля, 20:00 — 21:30

Лектор: Дмитрий Кириллов

Домашние задания: 1

ДЗ

Индексы PostgreSQL. Создать индексы на БД, которые ускорят доступ к данным.

В данном задании тренируются навыки:
- определения узких мест
- написания запросов для создания индекса
- оптимизации
Необходимо:
1) Создать индекс к какой-либо из таблиц вашей БД
2) Прислать текстом результат команды explain,
в которой используется данный индекс
3) Реализовать индекс для полнотекстового поиска
4) Реализовать индекс на часть таблицы или индекс
на поле с функцией
5) Создать индекс на несколько полей
6) Написать комментарии к каждому из индексов
7) Описать что и как делали и с какими проблемами
столкнулись

DML: агрегация и сортировка, CTE, аналитические функции в PostgreSQL

группировать и сортировать данные и использовать групповые функции;

15 июля, 20:00 — 21:30

Лектор: Михаил Ржевский

Домашние задания: 1

ДЗ

Посчитать кол-во очков по всем игрокам за текущий год и за предыдущий. . 1) Создайте таблицу и наполните ее данными
CREATE TABLE statistic(

```
player_name VARCHAR(100) NOT NULL,
player_id INT NOT NULL,
year_game SMALLINT NOT NULL CHECK (year_game > 0),
points DECIMAL(12,2) CHECK (points >= 0),
PRIMARY KEY (player_name,year_game)
```

);

2) заполнить данными
```

```
INSERT INTO
statistic(player_name, player_id, year_game, points)
VALUES
('Mike',1,2018,18),
('Jack',2,2018,14),
('Jackie',3,2018,30),
('Jet',4,2018,30),
('Luke',1,2019,16),
('Mike',2,2019,14),
('Jack',3,2019,15),
('Jackie',4,2019,28),
('Jet',5,2019,25),
('Luke',1,2020,19),
('Mike',2,2020,17),
('Jack',3,2020,18),
('Jackie',4,2020,29),
('Jet',5,2020,27);
```

3) написать запрос суммы очков с группировкой и сортировкой по годам

4) написать cte показывающее тоже самое

5) используя функцию LAG вывести кол-во очков по всем игрокам за текущий код и за предыдущий.

Apache Superset и PostgreSQL

понимать, зачем нужны средство визуализации;
как построить табличное представление и диаграмму;
создавать свои отчеты.

18 июля, 20:00 — 21:30

Лектор: Михаил Ржевский

Создание аналитических отчетов в Clickhouse+Superset и PostgreSQL

научиться использовать кубы данных.

22 июля, 20:00 — 21:30

Лектор: Михаил Ржевский

Хранимые процедуры и триггеры в PostgreSQL

использовать хранимые процедуры и функций для оптимизации работы с БД.

25 июля, 20:00 — 21:30

Лектор: Михаил Ржевский

Оптимизация производительности. Профилирование. Мониторинг PostgreSQL

определять узкие места базы, оптимизировать и профилировать запросы.

29 июля, 20:00 — 21:30

Лектор: Виктор Коробков

Резервное копирование и восстановление в PostgreSQL

делать бэкап и затем восстанавливаться с него.

1 августа, 20:00 — 21:30

Лектор: Михаил Ржевский

Репликация в PostgreSQL

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

5 августа, 20:00 — 21:30

Лектор: Михаил Ржевский

Домашние задания: 1

ДЗ

Делаем физическую и логическую репликации. Физическая репликация:

Весь стенд собирается в Docker образах или ВМ. Необходимо:
- Настроить физическую репликации между двумя кластерами базы данных
- Репликация должна работать использую "слот репликации"
- Реплика должна отставать от мастера на 5 минут

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

Версия PostgreSQL на ваше усмотрение

Кластеризация: patroni для PostgreSQL

построить отказоустойчивый кластер с помощью patroni.

8 августа, 20:00 — 21:30

Лектор: Антон Герасименко

Семинар по PostgreSQL

рассмотрим более подробно Json, индексы, explain;
ответить на вопросы по блоку PostgreSQL.

12 августа, 20:00 — 21:30

Лектор: Михаил Ржевский

MySQL

В данном модуле рассмотрим архитектуру и применение MySQL

Внутренняя архитектура MySQL

настроить СУБД, влияющие на ее производительность.

22 августа, 20:00 — 21:30

Лектор: Дмитрий Кириллов

Домашние задания: 1

ДЗ

Создаем базу данных MySQL в докере. 1) забрать стартовый репозиторий https://github.com/aeuge/otus-mysql-docker
2) прописать sql скрипт для создания своей БД в init.sql
3) проверить запуск и работу контейнера следую описанию в репозитории
_____________________________________________________________________________________________________________________________________________________

Задания повышенной сложности*
- прописать кастомный конфиг - настроить innodb_buffer_pool и другие параметры по желанию
- протестить сисбенчем - результат теста приложить в README

Возможные проблемы:

не подключается к БД - https://stackoverflow.com/questions/19101243/error-1130-hy000-host-is-not-allowed-to-connect-to-this-mysql-server

на m1 не запускается - https://stackoverflow.com/questions/65456814/docker-apple-silicon-m1-preview-mysql-no-matching-
manifest-for-linux-arm64-v8

Типы данных в MySQL

26 августа, 20:00 — 21:30

Лектор: Дмитрий Кириллов

Домашние задания: 1

DDL: создание, изменение и удаление объектов в MySQL

создавать, модифицировать и удалять тейблспейсы, таблицы, пользователей;
настраивать секционирование;
настраивать политики безопасности.

29 августа, 20:00 — 21:30

Лектор: Дмитрий Кириллов

DML: вставка, обновление, удаление, выборка данных в MySQL

использовать различные способы выборки данных из одной и нескольких таблиц;
использовать нужный тип JOIN и различные условия в WHERE.

2 сентября, 20:00 — 21:30

Лектор: Дмитрий Кириллов

Домашние задания: 1

ДЗ

SQL выборка. Мы подготовили для вас 2 типа задний. Выберите одно из них или выполните оба, если у вас есть желание.

**Задание в сфере администрирования и разработки**

1. Напишите запрос по своей базе с inner join
2. Напишите запрос по своей базе с left join
3. Напишите 5 запросов с WHERE с использованием разных
операторов, опишите для чего вам в проекте нужна такая выборка данных

**Задание в сфере тестирования**

Для этого домашнего занятия вам понадобятся две пустые таблицы:

```
CREATE TABLE categories IF NOT EXISTS (
category_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(32) NOT NULL
);

CREATE TABLE products IF NOT EXISTS (
product_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(32) NOT NULL,
category_id VARCHAR(32) REFERENCES categories (category_id),
price INT,
rating INT,
status VARCHAR(32) NOT NULL -- "В наличии" или "Распродан"
);
```

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

2. Напишите хранимую процедуру (или скрипт на любом языке программирования) со следующей функциональностью:

- сгенерировать 20 категорий
- после каждой вставки в таблицу категорий получить текущий category_id с помощью LAST_INSERT_ID() и сгенерировать 10000 товаров в текущей категории
- ВАЖНО: все цены товаров должны быть уникальными
- суммарно должно получиться 200 тысяч товаров (по 10 тысяч в каждой категории)

3. Напишите запрос, который выведет товары в следующем порядке:

- сначала все товары в наличии, отсортированные по возрастанию цены
- затем все распроданные товары, тоже отсортированные по возрастанию цены

Ваш запрос должен поддерживать постраничную выдачу (по 50 товаров на страницу). Продумайте наиболее эффективный способ организации такой выдачи с учётом особенностей LIMIT в MySQL. Обратите внимание, что все цены в таблице products уникальные, это поможет вам оптимизировать запрос.

Транзакции, MVCC, ACID в MySQL

разбираться в уровнях изоляции транзакций;
избегать взаимных блокировок;
разбираться в различных видах блокировок;
строить транзакции в mysql.

5 сентября, 20:00 — 21:30

Лектор: Дмитрий Кириллов

Домашние задания: 1

DML: агрегация и сортировка в MySQL

группировать и сортировать данные и использовать групповые функции.

9 сентября, 20:00 — 21:30

Лектор: Дмитрий Кириллов

Домашние задания: 1

Индексы в MySQL

разобраться в типах индексов, плюсах и минусах;
создавать, удалять и мониторить индексы для оптимизации БД;

12 сентября, 20:00 — 21:30

Лектор: Дмитрий Кириллов

Домашние задания: 1

CTE и аналитические функции в MySQL

строить сложные отчетные запросы с применением CTE и аналитических функций.

16 сентября, 20:00 — 21:30

Лектор: Дмитрий Кириллов

Хранимые процедуры и триггеры в MySQL

использовать хранимые процедуры и функций для оптимизации работы с БД.

19 сентября, 20:00 — 21:30

Лектор: Михаил Ржевский

Домашние задания: 1

ДЗ

Добавляем в базу хранимые процедуры и триггеры. 1. Создать пользователей client, manager.
2. Создать процедуру выборки товаров с использованием различных фильтров: категория, цена, производитель, различные дополнительные параметры

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

Оптимизация производительности. Профилирование. Мониторинг в MySQL

определять узкие места базы;
оптимизировать и профилировать запросы.

23 сентября, 20:00 — 21:30

Лектор: Николай Лавлинский

Домашние задания: 1

Резервное копирование и восстановление MySQL

делать бэкап и затем восстанавливаться с него.

26 сентября, 20:00 — 21:30

Лектор: Виталий Попов

Домашние задания: 1

Репликация MySQL

построить репликацию;
использовать оркестрацию;
переключать реплики.

30 сентября, 20:00 — 21:30

Лектор: Виталий Попов

Домашние задания: 1

MySQL - Percona XtraDB Cluster

настроить Percona XtraDB Cluster
использовать proxySQL для балансировки.

3 октября, 20:00 — 21:30

Лектор: Виталий Попов

MySQL - InnoDB Cluster

настроить InnoDB cluster.

7 октября, 20:00 — 21:30

Лектор: Виталий Попов

Домашние задания: 1

ДЗ

Строим модель данных. Задача: реализовать модель данных БД, определить сущности, построить связи, выполнить декомпозицию и нормализацию

За основу берем практическую структуру данных с заказчиками интернет магазина (файл some_customers.csv.gz).

Текущая структура данных неоптимальна:
- нет типизации - почти все поля хранятся как строки
- данные не нормализованы - данные о адресе и человеке хранятся в одной таблице, на одного человека может приходится несколько адресов

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

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

делаем декомпозицию и нормализацию

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

Решая данное ДЗ вы тренируете навык проектирования БД, выделения сущностей и их атрибутов, построения связей, нормализации данных

_____________________________________________________________________________________________________________________________________________________

Задание повышенной сложности*

плюс 10 баллов загрузить данные из CSV в вашу модель

плюс 3 балла за развернутый кластер innodb с роутером и проверкой работоспособности

MySQL NDB Cluster - шардинг

построить NDB cluster.

10 октября, 20:00 — 21:30

Лектор: Антон Герасименко

Семинар по MySQL

получить рекомендации по проектной работе.

14 октября, 20:00 — 21:30

Лектор: Николай Лавлинский

NoSQL (Redis, Tarantool, MongoDB, Cassandra)

В данном модуле рассмотрим архитектуру и применение различных NoSQL решений

Архитектура Tarantool

использовать возможности Tarantool.

24 октября, 20:00 — 21:30

Лектор: Дмитрий Гурьянов

Масштабирование Tarantool

масштабировать Tarantool и настраивать репликацию.

28 октября, 20:00 — 21:30

Лектор: Дмитрий Гурьянов

Redis

понимать, когда и для каких целей имеет смысл использовать NoSQL;
использовать Redis с учётом специфических особенностей.

31 октября, 20:00 — 21:30

Лектор: Дмитрий Кириллов

Greenplum

понять, почему так популярен greenplum как DWH;
понять, что такое MPP;
провести базовые настройки green plum;
отличать секционирование от партиционирования;

7 ноября, 20:00 — 21:30

Лектор: Михаил Ржевский

Базовые возможности mongodb

развернуть БД mongo разными способами;
писать простейшие запросы к данным.

11 ноября, 20:00 — 21:30

Лектор: Виктор Коробков

Домашние задания: 1

Архитектура и применение cassandra

описать основы работы с Сassandra, архитектуру и возможности;
знать плюсы и минусы Cassandra.

14 ноября, 20:00 — 21:30

Лектор: Дмитрий Гурьянов

Clickhouse

понимать плюсы и минусы Clickhouse;
развернуть Clickhouse для своего проекта;
использовать интеграционные и управляющие интерфейсы Clickhouse.

18 ноября, 20:00 — 21:30

Лектор: Михаил Ржевский

Проектная работа

В данном модуле создадим свой проект и проведем его защиту

Выбор темы и организация проектной работы

выбрать и обсудить тему проектной работы;
спланировать работу над проектом;
ознакомиться с регламентом работы над проектом.

21 ноября, 20:00 — 21:30

Лектор: Михаил Ржевский

Домашние задания: 1

Семинар по созданным проектам

более глубоко рассмотрим архитектурные паттерны при построении БД.

28 ноября, 20:00 — 21:30

Лектор: Михаил Ржевский

Защита проектных работ

защитить проект и получить рекомендации экспертов.

16 декабря, 20:00 — 21:30

Лектор: Михаил Ржевский

Подведение итогов курса

узнать, как получить сертификат об окончании курса, как взаимодействовать после окончания курса с OTUS и преподавателями, какие вакансии и позиции есть для выпускников и на какие компании стоит обратить внимание.

23 декабря, 20:00 — 21:30

Лектор: Михаил Ржевский

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

Алексей Цыкунов

Со-основатель и технический директор Hilbert Team

Более 20 лет опыта в проектировании и реализации отказоустойчивых и высоконагруженных информационных систем в таких отраслях как телеком и FinTech

Автор курсов по Linux в Otus.ru

Более 8 лет опыта оптимизации работы продуктовых команд и R&D департаментов с помощью DevOps инструментов и методик (Kubernetes, CI/CD, etc.) и облачных технологий (AWS, GCP, Azure, Yandex.Cloud)

Преподаватель курсов: