Skip to content

Базы Данных

Basics: Constraints / Join / FK

constrains

  • not null
  • unique
  • primary key
  • foreign key
  • check
  • default
  • create index
  • Deferrable - проверка ограничений в конце транзакции

truncate vs delete

  • delete - удаляет конкретные строки
  • truncate - удаляет все строки

join

  • (Inner) join - Когда данные совпадение в обеих таблицах
  • Left join - вывод всех строк левой табл, правая как пойдет
  • Right - наоборот
  • Full (outer) join = left + right
  • cross-join - перемножение

FK ON DELETE

  • CASCADE - удаляет детей
  • SET NULL - выставляет null в ФК детей
  • SET DEFAULT - выставляет дефолт в ФК детей
  • RESTRICT (~Protect в Django) - запрет на удаление, если есть дели (проверка сразу)
  • NO ACTION - запрет на удаление, если есть дели (проверка в конце транзакции )

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

Нормальные формы

  • Нормализация - устранение избыточности / дублирования данных + про здравый смысл
  • Первая:
    • Атрибуты атомарны: только одно значение, а не несколько
    • Android: Phone1, Phone2 ⇒ Android: Phone1, Android: Phone2
  • Вторая: = Первая + Зависимость колонок от PK целиком
    • PK = Phone + OwnerId => ScreenSize !~ PK ⇒
      • PK = Phone => SreenSize ~ Phone , PK = OwnerId => Phone ~ OwnerId
  • Третья: = Вторая + Зависимость колонок от PK только
    • Phone, OS, OsMaintaier => OsMaintaier ~ OS ⇒ OS, Maintainer , Phone, OS

Денормализация

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

Кишки бд

WAL

  • Write-Ahead Log - журнал изменений, запись туда происходит до применения изменений;
  • полезно для надёжности
  • полезно для индексов - инкрементальное обновление

Транзакции

ACID

ACID - свойства транзакций

  • Atomicity - все или ничего
  • Consistency - данные должны быть в good-state (здравый смысл) до и после транзакции, но это обязанность аппа
  • Isolation - защита от race condition ака isolation levels
  • Durability - сохранение в бд

Уровни изоляции

  • Read Uncommited - возможность Dirty Read
    • Аномалия Dirty Read - когда можем читать данные из другой транзакции не дожидаясь коммита
  • Read Commited - защита от Dirty Read
    • Аномалия Non-repeatable Read / Phantom Read - данные изменились в другой транзакции, но первая транзакция, которая делает 2 селекта по одной табл. еще работает
  • Snapshot Isolation = Reapeatable Read - на время выполнения запросов берется слепок бд, так что параллельные изменения будут заигнорены
    • При обновлении данных, которые уже поменяли другой транзакцией, получим ошибку - оптимистичная блокировка (mvcc)
  • Аномалия Lost Update - конкурентная запись (одну и ту же инфу обновляем) или разные данные обновляем, но логика изза этого нарушается
    • Атомарные операции: val + 1
    • Select for update (2pl) - эксклюзивная блокировка - пессимистичная блокировка
    • Lost Update Detection - авто-откат
  • Serializable - последовательное выполнение, решает все проблемы/аномалии в тч в ситуациях когда нечего блочить

Индексы

Виды индексов

По сути

  • Составные индексы - по нескольким колонкам
  • кластерные индексы - строки в таблице будут отсортированы как в индексе - как результат лучше перфоманс, но вставка/обновление дороже

По структуре данных

  • B-Tree = сбалансированное дерево
    • По-умолчанию
    • O(log N)
  • Hash-Index
    • Только по =
    • Нет поддержки wal - индекс будет часто перестраиваться
    • O(1)
  • Bitmap - для разреженных данных Bitmap - для разреженных
  • Gist - геометрические данные (полигончики), текст
  • Gin - массивы, json, текст
  • Sp-Gist - телефон-номера
  • Brin - почтовые индексы, таймстампы

B-Tree подробнее

  • b-tree - дерево интервалов
  • (4, 32, 64) -> (4, 32), (32, 64), (64, 100) -> ...
  • доступен поиск по интервалу
  • b-tree = balanced-tree
  • поиск - log(N)
  • Что в листьях? - колонка + айди строки - по которым можно получить все колонки
  • хорошо заходит с order by
  • составные индексы - по нескольким колонкам
  • поиск работает до первого неравенства потому что потом будет скан

Explain / Explain Analyze / Analyze

  • Explain - Не выполняет запросы, только план запроса (scan)
  • Expain Analyze - выполняет запрос, показывая план запроса
  • Analyze - строит стату по табл, чтобы планировщик лучше робил
  • Expain - показывает поля / план запроса - че происходит во время запроса
    • Seq Scan - полный перебор
  • Индекс сделали, но запросы по-прежнему тормозят - индекс еще не создался / плохая селективность

Scan

  • Seq scan - последовательное
  • Index Scan - это про селективность
  • Index Only Scan - читаем из индекса
  • Bitmap Index Scan - сложнааа

Порядок колонок / Селективность

  • select * from employee where sex = 'female' and salary > 150 and age = 50 order by created_at
  • Составные индексы - по нескольким колонкам
    • Сначала Равенство, затем неравенство (в тч order by)
    • Если после первого равенства - неравенство, то индекс прекратит работу на первом неравенстве
    • То есть для запроса выше индекс sex, age, created_at будет только по sex, age - потому что salary - неравенство
  • Индекс на флаг - бесполезен, будет использован Seq Scan - плохая селективность индекса
  • Так что при создании индекса в первую очередь лучше выбирать колонку более селективную (напр. salary)
  • Частичный индекс - индекс на колонку + условие: (salary) salary > N

Материалы