Открытый вебинар «Использование индексов» 14.09.2018 в 20:00 | OTUS >
Проходил 14 сентября 2018 года в 20:00

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

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

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

Запись

О курсе

Базы данных
103 000 ₽
6 месяцев
Начало 6 марта

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

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

На курсе вы:

  • научитесь проектировать базы …
Программа курса
Модуль 1
Сущности и связи
Модуль 2
PostgreSQL
Модуль 3
MySQL
Модуль 4
NoSQL (Redis, Tarantool, MongoDB, Cassandra)
Модуль 5
Проектная работа
Сущности и связи
В данном модуле рассмотрим проблемы проектирования БД
Проектирование БД
понимать вопросы и проблемы обслуживания и сопровождения, которые важно учесть на этапе проектирования;
Домашние задания: 1
ДЗ
Разработка проекта. Реализуйте сущности:
- продукты;
- категории продуктов;
- цены;
- поставщики;
- производители:
- покупатели;
- покупки.


Свои решения для этой схемы приветствуются.

В проекте должны быть:
- схема;
- описание таблиц и полей;
- примеры бизнес-задач, которые решает база.
Компоненты современной СУБД
использовать в работе компоненты и возможности СУБД;
проектировать и создавать индексы;
устанавливать ограничения на поля и целые таблицы;
14 марта, 20:00 — 21:30
Лектор: Николай Лавлинский
Домашние задания: 1
ДЗ
Добавляем в модель данных дополнительные индексы и ограничения. 1. Проводим анализ возможных запросов\отчетов\поиска данных.
2. Предполагаем возможную кардинальность поля.
3. Создаем дополнительные индексы - простые или композитные.
4. На каждый индекс пишем краткое описание зачем он нужен (почему по этому полю\полям).

5. Думаем какие логические ограничения в БД нужно добавить - например какие поля должны быть уникальны, в какие нужно добавить условия, чтобы не нарушить бизнес логику. Пример - нельзя провести операцию по переводу средств на отрицательную сумму.
6. Создаем ограничения по выбранным полям.
Проблемы миграции
разобраться в видах миграции;
узнать основные проблемы миграции;
решить проблемы, возникшие при миграции.
19 марта, 20:00 — 21:30
Лектор: Николай Лавлинский
Теорема CAP
понимать смысл теоремы CAP;
различать BASE и ACID требования к базам;
понимать проблему выбора СУБД.
21 марта, 20:00 — 21:30
Лектор: Николай Лавлинский
Семинар: проектирование
рассказать о своем проекте;
получить фидбэк от коллег и преподавателя.
25 марта, 20:00 — 21:30
Лектор: Михаил Ржевский
PostgreSQL
В данном модуле рассмотрим архитектуру и применение PostgreSQL
Внутренняя архитектура СУБД PostgreSQL
иметь представления об устройстве СУБД PostgreSQL;
установить СУБД PostgreSQL;
создать базу данных и подключаться к ней.
28 марта, 20:00 — 21:30
Лектор: Виктор Коробков
Домашние задания: 1
ДЗ
Установка СУБД PostgreSQL. 1. Развернуть контейнер с PostgreSQL или установить СУБД на виртуальную машину.
2. Запустить сервер.
3. Создать клиента с подключением к базе данных postgres через командную строку.
4. Подключиться к серверу используя pgAdmin или другое аналогичное приложение.
DDL: создание, изменение и удаление объектов в PostgreSQL
создавать и редактировать объекты БД в СУБД PostgreSQL.
1 апреля, 20:00 — 21:30
Лектор: Анастасия Чекушина
Домашние задания: 1
ДЗ
DDL скрипты для postgres. Используя операторы DDL создайте на примере схемы интернет-магазина:
1. Базу данных.
2. Табличные пространства и роли.
3. Схему данных.
4. Таблицы своего проекта, распределив их по схемам и
табличным пространствам.
Изоляция транзакций в PostgreSQL
понять идею и применимость уровней изоляции транзакций, с какими аномалиями они помогают справляться;
разобраться, как прийти к консенсусу в выборе между высокой согласованностью данных между транзакциями и скоростью выполнения этих самых транзакций;
4 апреля, 20:00 — 21:30
Лектор: Дмитрий Тарасов
Транзакции, MVCC, ACID в PostgreSQL
разбираться в уровнях изоляции транзакций;
избегать взаимных блокировок;
разбираться в различных видах блокировок;
строить транзакции в PostgreSQL.
8 апреля, 20:00 — 21:30
Лектор: Антон Герасименко
DML: вставка, обновление, удаление, выборка данных в PostgreSQL
создавать различные типы связей между таблицами;
добавлять и обновлять данные со сложными выборками;
удалять данные с подзапросами.
11 апреля, 20:00 — 21:30
Лектор: Анастасия Чекушина
Домашние задания: 1
ДЗ
DML в PostgreSQL. 1. Напишите запрос по своей базе с регулярным выражением, добавьте пояснение, что вы хотите найти.
2. Напишите запрос по своей базе с использованием LEFT JOIN и INNER JOIN, как порядок соединений в FROM влияет на результат? Почему?
3. Напишите запрос на добавление данных с выводом информации о добавленных строках.
4. Напишите запрос с обновлением данные используя UPDATE FROM.
5. Напишите запрос для удаления данных с оператором DELETE используя join с другой таблицей с помощью using.


Задание со *:
Приведите пример использования утилиты COPY
Типы данных в PostgreSQL
правильно выбирать типы данных для своих структур;
использовать функции для работы с определенными типами данных.
15 апреля, 20:00 — 21:30
Лектор: Дмитрий Кириллов
Индексы в PostgreSQL
разобраться в типах индексов, плюсах и минусах;
создавать, удалять и мониторить индексы для оптимизации БД.
18 апреля, 20:00 — 21:30
Лектор: Дмитрий Кириллов
Домашние задания: 1
ДЗ
Индексы PostgreSQL. Создать индексы на БД, которые ускорят доступ к данным.

В данном задании тренируются навыки:
- определения узких мест
- написания запросов для создания индекса
- оптимизации
Необходимо:
1) Создать индекс к какой-либо из таблиц вашей БД
2) Прислать текстом результат команды explain,
в которой используется данный индекс
3) Реализовать индекс для полнотекстового поиска
4) Реализовать индекс на часть таблицы или индекс
на поле с функцией
5) Создать индекс на несколько полей
6) Написать комментарии к каждому из индексов
7) Описать что и как делали и с какими проблемами
столкнулись
DML: агрегация и сортировка, CTE, аналитические функции в PostgreSQL
группировать и сортировать данные и использовать групповые функции;
22 апреля, 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
понимать, зачем нужны средство визуализации;
как построить табличное представление и диаграмму;
создавать свои отчеты.
25 апреля, 20:00 — 21:30
Лектор: Михаил Ржевский
Создание аналитических отчетов в Clickhouse+Superset и PostgreSQL
научиться использовать кубы данных.
29 апреля, 20:00 — 21:30
Лектор: Михаил Ржевский
Хранимые процедуры и триггеры в PostgreSQL
использовать хранимые процедуры и функций для оптимизации работы с БД.
13 мая, 20:00 — 21:30
Лектор: Михаил Ржевский
Оптимизация производительности. Профилирование. Мониторинг PostgreSQL
определять узкие места базы, оптимизировать и профилировать запросы.
16 мая, 20:00 — 21:30
Лектор: Виктор Коробков
Резервное копирование и восстановление в PostgreSQL
делать бэкап и затем восстанавливаться с него.
20 мая, 20:00 — 21:30
Лектор: Дмитрий Кириллов
Репликация в PostgreSQL
создать реплику базы;
сделать ручное переключение ролей;
переключиться после фэйловера;
настроить архивацию логов.
23 мая, 20:00 — 21:30
Лектор: Михаил Ржевский
Домашние задания: 1
ДЗ
Делаем физическую и логическую репликации. Физическая репликация:

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


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


Версия PostgreSQL на ваше усмотрение
Кластеризация: patroni для PostgreSQL
построить отказоустойчивый кластер с помощью patroni.
27 мая, 20:00 — 21:30
Лектор: Наталия Титова
Семинар по PostgreSQL
рассмотрим более подробно Json, индексы, explain;
ответить на вопросы по блоку PostgreSQL.
30 мая, 20:00 — 21:30
Лектор: Виктор Коробков
MySQL
В данном модуле рассмотрим архитектуру и применение MySQL
Внутренняя архитектура MySQL
настроить СУБД, влияющие на ее производительность.
3 июня, 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
правильно выбирать типы данных для своих структур;
использовать функции для работы с определенными типами данных.
6 июня, 20:00 — 21:30
Лектор: Дмитрий Кириллов
Домашние задания: 1
ДЗ
Типы данных. 1) проанализировать типы данных в своем проекте, изменить при необходимости. В README указать что на что поменялось и почему.
2) добавить тип JSON в структуру. Проанализировать какие данные могли бы там хранится. привести примеры SQL для добавления записей и выборки.
DDL: создание, изменение и удаление объектов в MySQL
создавать, модифицировать и удалять тейблспейсы, таблицы, пользователей;
настраивать секционирование;
настраивать политики безопасности.
10 июня, 20:00 — 21:30
Лектор: Дмитрий Кириллов
DML: вставка, обновление, удаление, выборка данных в MySQL
использовать различные способы выборки данных из одной и нескольких таблиц;
использовать нужный тип JOIN и различные условия в WHERE.
17 июня, 20:00 — 21:30
Лектор: Дмитрий Кириллов
Домашние задания: 1
ДЗ
SQL выборка. 1. Напишите запрос по своей базе с inner join
2. Напишите запрос по своей базе с left join
3. Напишите 5 запросов с WHERE с использованием разных
операторов, опишите для чего вам в проекте нужна такая выборка данных
Транзакции, MVCC, ACID в MySQL
разбираться в уровнях изоляции транзакций;
избегать взаимных блокировок;
разбираться в различных видах блокировок;
строить транзакции в mysql.
20 июня, 20:00 — 21:30
Лектор: Дмитрий Кириллов
Домашние задания: 1
ДЗ
Транзакции. 1) Описать пример транзакции из своего проекта с изменением данных в нескольких таблицах. Реализовать в виде хранимой процедуры.

2) Загрузить данные из приложенных в материалах csv.
Реализовать следующими путями:
- LOAD DATA


Задание со *: загрузить используя
- mysqlimport
DML: агрегация и сортировка в MySQL
группировать и сортировать данные и использовать групповые функции.
24 июня, 20:00 — 21:30
Лектор: Дмитрий Кириллов
Домашние задания: 1
ДЗ
Создаем отчетную выборку. Предоставить следующий результат:
1) группировки с ипользованием CASE, HAVING, ROLLUP, GROUPING() :
2) для магазина к предыдущему списку продуктов добавить максимальную и минимальную цену и кол-во предложений
3) сделать выборку показывающую самый дорогой и самый дешевый товар в каждой категории
4) сделать rollup с количеством товаров по категориям
Индексы в MySQL
разобраться в типах индексов, плюсах и минусах;
создавать, удалять и мониторить индексы для оптимизации БД;
27 июня, 20:00 — 21:30
Лектор: Дмитрий Кириллов
Домашние задания: 1
ДЗ
Индексы. Пересматриваем индексы на своем проекте. По необходимости меняем.


Задача - сделать полнотекстовый индекс, который ищет по свойствам, названию товара и описанию. Если нет аналогичной задачи в проекте - имитируем.


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

в README пропишите какие индексы были изменены или добавлены.

explain и результаты выборки без индекса и с индексом.

Реализация полнотекстового индекса.
CTE и аналитические функции в MySQL
строить сложные отчетные запросы с применением CTE и аналитических функций.
1 июля, 20:00 — 21:30
Лектор: Дмитрий Кириллов
Хранимые процедуры и триггеры в MySQL
использовать хранимые процедуры и функций для оптимизации работы с БД.
4 июля, 20:00 — 21:30
Лектор: Михаил Ржевский
Домашние задания: 1
ДЗ
Добавляем в базу хранимые процедуры и триггеры. 1. Создать пользователей client, manager.
2. Создать процедуру выборки товаров с использованием различных фильтров: категория, цена, производитель, различные дополнительные параметры

Также в качестве параметров передавать по какому полю сортировать выборку, и параметры постраничной выдачи
- дать права да запуск процедуры пользователю client
3. Создать процедуру get_orders - которая позволяет просматривать отчет по продажам за определенный период (час, день, неделя) с различными уровнями группировки (по товару, по категории, по производителю)
4. Права дать пользователю manager
Оптимизация производительности. Профилирование. Мониторинг в MySQL
определять узкие места базы;
оптимизировать и профилировать запросы.
8 июля, 20:00 — 21:30
Лектор: Николай Лавлинский
Домашние задания: 1
ДЗ
Анализ и профилирование запроса. возьмите сложную выборку из предыдущих ДЗ с несколькими join и подзапросами

постройте EXPLAIN в 3 формата

оцените план прохождения запроса, найдите самые тяжелые места


Задание со *:
оптимизировать запрос (можно использовать индексы, хинты, сбор статистики, гистограммы)


все действия и результаты опишите в README.md
Резервное копирование и восстановление MySQL
делать бэкап и затем восстанавливаться с него.
18 июля, 20:00 — 21:30
Лектор: Виталий Попов
Домашние задания: 1
ДЗ
Восстановить таблицу из бэкапа. В материалах приложен файл бэкапа backup_des.xbstream.gz.des3 и дамп структуры базы world-db.sql

Бэкап выполнен с помощью команды:

sudo xtrabackup --backup --stream=xbstream \
--target-dir=/tmp/backups/xtrabackup/stream \
| gzip - | openssl des3 -salt -k "password" \
> stream/backup_des.xbstream.gz.des3

Требуется восстановить таблицу world.city из бэкапа и выполнить оператор:
select count(*) from city where countrycode = 'RUS';

Результат оператора написать в чат с преподавателем.
Репликация MySQL
построить репликацию;
использовать оркестрацию;
переключать реплики.
22 июля, 20:00 — 21:30
Лектор: Виталий Попов
Домашние задания: 1
ДЗ
Спрогнозировать рост данных и спроектировать модель хранения и архивации. Необходимо запустить репликацию двух серверов MySQL по топологии Source-Replica (Master-Slave), также можно использовать MariaDB или Percona Server for MySQL.

- Запустить и показать работу асинхронной репликации на основе GTID.
- Загрузить данные на Master-сервер.
- Реплика (Slave) должен быть в режиме read-only.

Задание со звездочкой* Запустить реплику сразу с начальным данными (БД).

Задание со звездочкой* Настроить выборочную репликацию — исключить несколько БД или таблиц.
MySQL - Percona XtraDB Cluster
настроить Percona XtraDB Cluster
использовать proxySQL для балансировки.
25 июля, 20:00 — 21:30
Лектор: Виталий Попов
MySQL - InnoDB Cluster
настроить InnoDB cluster.
29 июля, 20:00 — 21:30
Лектор: Виталий Попов
Домашние задания: 1
ДЗ
Строим модель данных. Задача: реализовать модель данных БД, определить сущности, построить связи, выполнить декомпозицию и нормализацию


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


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


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


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


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

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


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

_____________________________________________________________________________________________________________________________________________________

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

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

плюс 3 балла за развернутый кластер innodb с роутером и проверкой работоспособности
MySQL NDB Cluster - шардинг
построить NDB cluster.
1 августа, 20:00 — 21:30
Лектор: Николай Лавлинский
Семинар по MySQL
получить рекомендации по проектной работе.
5 августа, 20:00 — 21:30
Лектор: Николай Лавлинский
NoSQL (Redis, Tarantool, MongoDB, Cassandra)
В данном модуле рассмотрим архитектуру и применение различных NoSQL решений
Архитектура Tarantool
использовать возможности Tarantool.
8 августа, 20:00 — 21:30
Лектор: Дмитрий Гурьянов
Масштабирование Tarantool
масштабировать Tarantool и настраивать репликацию.
12 августа, 20:00 — 21:30
Лектор: Дмитрий Гурьянов
Redis
понимать, когда и для каких целей имеет смысл использовать NoSQL;
использовать Redis с учётом специфических особенностей.
15 августа, 20:00 — 21:30
Лектор: Дмитрий Кириллов
Greenplum
понять, почему так популярен greenplum как DWH;
понять, что такое MPP;
провести базовые настройки green plum;
отличать секционирование от партиционирования;
19 августа, 20:00 — 21:30
Лектор: Михаил Ржевский
Базовые возможности mongodb
развернуть БД mongo разными способами;
писать простейшие запросы к данным.
22 августа, 20:00 — 21:30
Лектор: Виктор Коробков
Домашние задания: 1
ДЗ
MongoDB. Необходимо:
- установить MongoDB одним из способов: ВМ, докер;
- заполнить данными;
- написать несколько запросов на выборку и обновление данных


Сдача ДЗ осуществляется в виде миниотчета.

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

создать индексы и сравнить производительность.
Архитектура и применение cassandra
описать основы работы с Сassandra, архитектуру и возможности;
знать плюсы и минусы Cassandra.
26 августа, 20:00 — 21:30
Лектор: Дмитрий Гурьянов
Clickhouse
понимать плюсы и минусы Clickhouse;
развернуть Clickhouse для своего проекта;
использовать интеграционные и управляющие интерфейсы Clickhouse.
29 августа, 20:00 — 21:30
Лектор: Михаил Ржевский
Проектная работа
В данном модуле создадим свой проект и проведем его защиту
Выбор темы и организация проектной работы
выбрать и обсудить тему проектной работы;
спланировать работу над проектом;
ознакомиться с регламентом работы над проектом.
2 сентября, 20:00 — 21:30
Лектор: Михаил Ржевский
Домашние задания: 1
ДЗ
Проектная работа. 1. Выбрать тему проектной работ
2. Согласовать ее с ркуоводителем курса
3. Реализовать свой проект на 60%+
4. Сделать презентацию своего проекта и защитить его на защите
Семинар по созданным проектам
более глубоко рассмотрим архитектурные паттерны при построении БД.
9 сентября, 20:00 — 21:30
Лектор: Михаил Ржевский
Защита проектных работ
защитить проект и получить рекомендации экспертов.
26 сентября, 20:00 — 21:30
Лектор: Михаил Ржевский
Подведение итогов курса
узнать, как получить сертификат об окончании курса, как взаимодействовать после окончания курса с OTUS и преподавателями, какие вакансии и позиции есть для выпускников и на какие компании стоит обратить внимание.
3 октября, 20:00 — 21:30
Лектор: Михаил Ржевский

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

Алексей Цыкунов
Со-основатель и технический директор Hilbert Team
Более 20 лет опыта в проектировании и реализации отказоустойчивых и высоконагруженных информационных систем в таких отраслях как телеком и FinTech

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

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