СУБД. Лекция 1

Знакомство, введение в реляционные базы данных

• Обзор курса
• Краткое введение в историю баз данных
• Введение в реляционные базы данных
• Реляционная алгебра
• Типы данных
• Миграция схемы между версиями

Правила игры

• 9 лекций по реляционным СУБД (Навроцкий);
• 2 лекции по NoSQL (Ступников);
• 5 контрольных;
• 1 семестровый проект.

Слайды доступны по адресу: https://bozaro.github.io/tech-db-lectures/

Контроль знаний

• Рубежные контроли;
• Разработка проекта;
• Пересдачи.

Семестровый проект

Реализация API для "форума".

• Функциональное тестирование;
• Нагрузочное тестирование;
• Проверка на плагиат.

API сформулировано в виде Swagger-схемы.

Работа принимается в виде git-репозитория, из которого собирается Docker-контейнер.

Задание расположено по адресу: https://github.com/bozaro/tech-db-forum

Отправная точка: https://github.com/bozaro/tech-db-hello

О плагиате

• К сожалению, практика показывает, что студенты друг у друга списывают.
• В 2016 году, если работа прошла проверку на антиплагиат, он получал дополнительно 10 баллов.
• Весной 2017 года, если работа не прошла проверку на антиплагиат, то у него не засчитывался результат нагрузочного тестирования (30 баллов).
• Осенью 2017 года, если работа не прошла проверку на антиплагиат, то результат будет засчитан, если он на 20% превышет результат других студентов, использующих аналогичную работу.

Грустный слайд

Интересные факты

• Реляционная база данных Yahoo > 2 петабайт¹
• Архив с базой данных OpenStreetMap занимает порядка 55Гб²
• Практически ни одно приложение не обходится без БД
• Отсутствие готовых решений
• Установочные .msi файлы для Windows - это БД
• В большинстве вакансий разработчика упоминается SQL, по данным hh.ru

Задача курса

• Проектирование реляционных баз данных.
• Язык запросов SQL.
• Оптимизация запросов и схем данных.
• High-Load и Big Data.
• Администрирование и настройка серверов БД.
• noSQL и его использование в WEB.

Зачем ввобще нужны СУБД?

Допустим, мы реализуем приложение "Поваренная книга" для хранения рецептов.

• Восстановление после сбоев;
• Производительность;
• Многопользовательский доступ;
• Резервное копирование.

Стоимость типовых операций

Задача курса

• Проектирование реляционных баз данных.
• Язык запросов SQL.
• Оптимизация запросов и схем данных.
• High-Load и Big Data.
• Администрирование и настройка серверов БД.
• noSQL и его использование в WEB.

Проектирование и диаграммы

• Нормализация
• DDL

SQL

• CRUD
• Транзакции
• Хранимые процедуры
• Триггеры

Оптимизация

“Ну и запросы у вас”, - сказала база данных и повисла.

• EXPLAIN
• log_min_duration_statement
• Индексы
• Денормализация

Администрирование

• Конфигурирование
• Репликация
• Резервное копирование
• Пользователи и права
• SQL-injections

Кратенькая история

• 3600 до н.э. - глиняные таблички у древних шумеров;
• 1966 г. - появление MUMPS;
• 1968 г. - первая промышленная СУБД система IMS фирмы IBM;
• 1972 г. - компания IBM начала исследовательский проект по разработке РСУБД;
• 1979 г. - выход первой РСУБД Oracle;
• 1980 г. - появление dBase II;
• 1981 г. - появление IBM PC;
• 1982 г. - выход DB2 фирмы IBM;
• 1993 г. - выход Intel Pentium, Seagate Medalist 425xe 428.1MB HDD;
• 1994 г. - выход Oracle для PC;
• 1995 г. - появление MySQL;
• 1996 г. - появление PostgreSQL;
• 2005 г. - появление Hadoop;
• 2009 г. - появление MongoDB.

Реляционные БД

• Поддержка нескольких СУБД может быть полезна для тестирования;
• Стандарты позволяют использовать чужие наработки, например системы отчетов;
• Аналитики часто знают SQL.

NoSQL

• Концепция Map-Reduce
• Краткий обзор существующих решений
• Tarantool

Литература

• Дейт К. Дж. Введение в системы баз данных
• Бьюли А. Изучаем SQL
• Молинаро Э. SQL – сборник рецептов
• Заводны Дж, Шварц Б., Зайцев П.,
  Ткаченко В., Ленц А.
  MySQL. Оптимизация производительности
• Саймон А. Р. Стратегические технологии баз данных
• https://wiki.postgresql.org/wiki/Russian
• https://postgrespro.ru/docs/
• http://citforum.ru/database/
• http://www.sql.ru/
• http://www.mysql.com/

Определение БД

База данных (БД)

Зто взаимосвязанная информация (данные) об объектах, которая организованна специальным образом и хранится на каком-либо носителе.

Реляционная модель данных

Терминология

Домен

Тип данных, то есть допустимое множество значений.

Кортеж

Множество пар {имя атрибута, значение}, которое содержит одно вхождение каждого имени атрибута, принадлежащего схеме отношения.

Отношение

Множество кортежей (не упорядоченное).

Целостность базы данных

Соответствие имеющейся в базе данных информации её внутренней логике, структуре и всем явно заданным правилам.

Реляционная модель данных

Реляционная алгебра

• Выборка
• Проекция
• Объединение
• Пересечение
• Разность
• Произведение
• Деление
• Соединение

Выборка (A WHERE c)

Персоны

`sigma_("Возраст" >= 34)("Персоны")`

SELECT * FROM "Персоны" WHERE "Возраст" >= 34

Проекция (PROJECT A {x, y, …, z})

Персоны

`Pi_("Возраст", "Вес")("Персоны")`

SELECT DISTINCT "Возраст", "Вес" FROM "Персоны"

Объединение (A UNION B)

`"Персоны" uu "Персонажи"`

SELECT * FROM "Персоны" UNION SELECT * FROM "Персонажи"

Пересечение (A INTERSECT B)

`"Персоны" nn "Персонажи"`

SELECT * FROM "Персоны"
NATURAL JOIN "Персонажи"

Разность (A MINUS B)

`"Персоны" \\ "Персонажи"`

SELECT * FROM "Персоны"
NATURAL LEFT JOIN "Персонажи"
WHERE "Персонажи" IS NULL

Произведение (A TIMES B)

`"Мультфильмы" xx "Каналы"`

SELECT * FROM "Персоны", "Персонажи"

Деление (A DIVIDEBY B)

SELECT "Код_Мульта", "Название_Мульта"
FROM "Мультфильмы"
JOIN "Каналы" USING ("Название_Канала")
GROUP BY "Код_Мульта", "Название_Мульта"
HAVING COUNT(DISTINCT "Название_Канала") = (
    SELECT COUNT(DISTINCT "Название_Канала") FROM "Каналы"
)

Соединение ((A TIMES B) WHERE P)

`"Мультфильмы" ⋈ "Каналы"`

SELECT *
FROM "Мультфильмы"
JOIN "Каналы" ON ("Название_Канала" = "Код_Канала")

Первичный ключ

Потенциальный ключ

В реляционной модели данных — подмножество атрибутов отношения, удовлетворяющее требованиям уникальности и минимальности (несократимости).

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

Минимальность (несократимость) означает, что в составе потенциального ключа отсутствует меньшее подмножество атрибутов, удовлетворяющее условию уникальности. Иными словами, если из потенциального ключа убрать любой атрибут, он утратит свойство уникальности.

Первичный ключ (англ. primary key)

В реляционной модели данных один из потенциальных ключей отношения, выбранный в качестве основного ключа (или ключа по умолчанию).

Если в отношении имеется единственный потенциальный ключ, он является и первичным ключом. Если потенциальных ключей несколько, один из них выбирается в качестве первичного, а другие называют «альтернативными».

Суррогатный ключ

Даже при наличии естественного ключа добавление суррогатного в большинстве случаев оправдано.

Типы данных PostgreSQL

• Числовые
• Строковые
• Календарные
• NULL

NULL

SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

SELECT 1 IS NULL, 1 IS NOT NULL, NULL IS NULL, NULL IS NOT NULL;

Числовые

SMALLSERIAL, SERIAL, BIGSERIAL - то же, но с автоинкрементом.

INT1 и беззнаковые есть в расширении https://github.com/petere/pguint

Бинарные

Строки

Дата и время

Время хранится с точностью до микросекунд.

P - кол-во цифр для хранения дробной части секунд.

WITH TIMEZONE - внутри базы хранится UTC.

Пример схемы базы данных (кортежи)

city

Пример схемы базы данных (ER-модель)

Пример схемы базы данных (DDL)

CREATE TABLE country (
  id bigserial NOT NULL PRIMARY KEY,
  name varchar(128) NOT NULL
);

CREATE TABLE region (
  id bigserial NOT NULL PRIMARY KEY,
  country_id bigint NOT NULL,
  name varchar(128) NOT NULL,
  FOREIGN KEY (country_id) REFERENCES country (id)
);

CREATE TABLE city
(
  id bigserial NOT NULL PRIMARY KEY,
  region_id bigint NOT NULL,
  name varchar(128) NOT NULL,
  FOREIGN KEY (region_id) REFERENCES region (id)
);

Версионирование схемы БД

Хотелки:

• Любую версию базы данных можно обновить до любой (обычно, самой последней) версии.
• Набор SQL-запросов, реализующих миграцию между любыми двумя версиями, можно было получить как можно быстрее и проще.
• Всегда можно создать с нуля базу данных со структурой самой последней версии.
• В случае работы над разными ветками, при последующем их слиянии ручное редактирование файлов БД было сведено к минимуму.
• Откатить БД на более раннюю версию так же просто, как и обновить на более новую.

Генерация БД на базе исходного кода

• Удобно наблюдать изменения в структуре между версиями при помощи средств системы контроля версий;
• Как и любой исходный код, структуру БД удобно комментировать;
• Для того, чтобы с нуля создать чистую базу данных последней версии, нужно выполнить всего лишь один файл;
• Скрипты-миграции более надежны, чем в других методах, так как генерируются автоматически;
• Мигрировать с новых версий на старые почти так же просто, как со старых на новые (проблемы могут возникнуть только с пресловутыми изменениями данных);
• В случае слияния двух веток репозитория, merge структуры БД осуществляется проще, чем при использовании других подходов;
• Изменения данных придется хранить отдельно, и затем вручную вставлять в сгенерированные скрипты-миграции;
• Вручную выполнять миграции очень неудобно, необходимы автоматизированные средства.

Метод инкрементных изменений

Database
|- Baseline.sql
|- 0001.03.01.sql
|- 0002.03.01.sql
|- 0003.03.01.sql
|- 0004.03.02.sql
|- 0005.03.02.sql
|- 0006.03.02.sql
|- 0007.03.02.sql

CREATE TABLE MigrationHistory (
    Id INT AUTO_INCREMENT PRIMARY KEY,
    MajorVersion VARCHAR(2),
    MinorVersion VARCHAR(2),
    FileNumber   VARCHAR(4),
    Description  VARCHAR(255),
    DateApplied  TIMESTAMP
);

INSERT INTO MigrationHistory (MajorVersion, MinorVersion, FileNumber, Description, DateApplied)
VALUES ('03', '01', '0000', 'Baseline', NOW());

INSERT INTO MigrationHistory (MajorVersion, MinorVersion, FileNumber, Description, DateApplied)
VALUES ('03', '01', '0001', 'Update users table', NOW());

Метод инкрементных изменений

• Быстрое и удобное выполнение миграции до последней версии;
• Механизм нумерации версий. Номер текущей версии хранится прямо в БД;
• Для максимального удобства нужны средства автоматизации выполнения миграций;
• Неудобно добавлять комментарии к структуре БД;
• Если хранить только "версию базы", возникают проблемы в процессе параллельной разработки в нескольких ветках репозитория.

Метод идемпотентных изменений

Database
 |- 3.01
 |   |- Baseline.sql
 |   | - Changes.sql
 |
 | - 3.02
     |- Baseline.sql
     |- Changes.sql

IF NOT EXISTS
(
    SELECT *
    FROM information_schema.tables
    WHERE table_name = 'myTable'
        AND table_schema = 'myDb'
)
THEN
    CREATE TABLE myTable
    (
        id INT(10) NOT NULL,
        myField VARCHAR(255) NULL,
        PRIMARY KEY(id)
    );
END IF;

Метод идемпотентных изменений

• Очень удобное выполнение миграций с любой промежуточной версии до последней — нужно всего лишь выполнить на базе данных один файл (Changes.sql)
• Потенциально возможны ситуации, в которых будут теряться данные, за этим придется следить.
• Для того, чтобы изменения были идемпотентными, нужно потратить больше времени (и кода) на их написание.