Что такое данные, информация, база данных? Что «под капотом» БД?

Данные – это информация в формализованном виде, т.е. пригодном для интерпретации,

обработки, передачи. Информация – это структурированные данные.

База данных — это набор данных, хранящиеся в структурированном виде.

Система управления базами данных СУБД — это совокупность языковых и

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

Семь из десяти самых популярных СУБД — реляционные (связанные, relation/связь)

Это Oracle, MySQL, Microsoft SQL Server, PostgreSQL, IBM Db2, Microsoft Access, SQLite.

Есть также: MongoDB – документ- ориентированная СУБД; Redis - хранилище по типу «ключ-значение»; Elasticsearch -
поисковой движок

1
Что такое SQL?

Язык программирования структурированных

запросов Structured Query Language, SQL.

Внешние программы формируют запрос к СУБД

на языке SQL

2
Основные SQL операторы и пример реляционной базы данных (связи между таблицами):

Что такое Query? Это запрос.

Java Persistence Query Language, JPQL — платформенно-независимый объектно-

ориентированный язык запросов, являющийся частью спецификации JPA или Java
Persistence API. JPQL используется для написания запросов к сущностям, хранящимся в
реляционной базе данных.

Как передать в объект Query параметры?

HQL (Hibernate Query Language) запрос всегда начинается с получения объекта Query из
Session вызовом метода createQuery(), в который передаётся текст запроса.

Какие бывают связи таблиц SQL? (будет еще про это в теме Hibernate)

3
Синтаксис, форматирование: ctrl Alt + L

Ключевые слова пишутся в верхнем регистре,

остальные в нижнем, с нижним подчёркиванием
в качестве разделителя. Точка с запятой в конце.

1. Что такое DDL? Какие операции в него входят? Рассказать про них.

Data Definition Language, DDL – создание структуры базы данных и ее объектов.

Это краткое название языка определения данных, который имеет дело со схемами БД и
описаниями того, как данные должны храниться в базе данных.

Операции Описание Запрос

CREATE Создание базы данных и ее объектов, таких как: таблица, CREATE DATABASE IF NOT EXISTS;
индекс, представления, процедура хранения, функция и имя_базы_данных;
триггеры.

Если нужно создать таблицу в пакете (SHEMA) example: CREATE SCHEMA example;
CREATE TABLE [Link] (
column1 datatype, CREATE TABLE table_name (
column2 datatype, column1 datatype,
); column2 datatype,
column3 datatype,
DATABASE (БД) –> SHEMA (пакет) –> TABLE (таблица) ....
По умолчанию таблица будет создана в пакете public );

ALTER Изменение структуры существующей базы данных: ALTER TABLE table_name

(альтернатива) • добавить, удалить столбец → ADD column_name datatype;
• изменить тип данных столбца (ниже)
ALTER TABLE Customers
ADD Email varchar(255);

ALTER TABLE table_name ALTER TABLE table_name

ALTER COLUMN column_name datatype; DROP COLUMN column_name;

DROP Удаление таблицы из базы данных ОСТОРОЖНО!!! Удалить таблицу целиком:

DROP TABLE table_name;

TRUNCATE Удаление всех записей из таблицы и мест, выделенных TRUNCATE TABLE table_name;
для этих записей, НО НЕ самой таблицы из БД.

2. Что такое DML? Какие операции в него входят? Рассказать про них.

Data Manipulation Language, DML – это операторы манипуляции данными.

Операции Описание Запрос

SELECT Выбирает данные, удовлетворяющие
заданным
Выведите поля (такие-то) из таблицы (такой-то).
условиям
SELECT * FROM Company; (вывести все столбцы)

(только уникальные)

INSERT Добавляет новые записи в таблицу. INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

В круглых скобках: 1) тип данных (колонки, розовый), после

ключевого слова VALUES 2) указываем значения (зелёный цвет)

4
 UPDATE Изменяет существующие данные

DELETE Удаляет данные DELETE FROM имя_таблицы

[WHERE условие_отбора_записей];

DELETE Reservations, Rooms FROM

Reservations JOIN Rooms ON
Reservations.room_id = [Link]
WHERE Rooms.has_kitchen = false;

3. Что такое TCL? Какие операции в него входят? Рассказать про них.

Transaction Control Language, TCL – это операторы управления транзакциями.

Операции Описание Запрос
COMMIT применяет транзакцию

ROLLBACK откатывает все изменения, сделанные в

контексте текущей транзакции

SAVEPOINT разбивает транзакцию на логические точки

сохранения, чтобы не откатывать всю
транзакцию.

правильный СИНТАКСИС:
ROLLBACK TO <SAVEPOINT-NAME>

4. Что такое DCL? Какие операции в него входят? Рассказать про них.

Data Control Language, DCL (happens-before) – операторы определения доступа к

данным.
Операции Описание Запрос
GRANT предоставляет пользователю Предоставление разрешения SELECT на таблицу без использования
(группе) разрешения на фразы OBJECT
определенные операции с GRANT SELECT ON [Link] TO RosaQdM;
объектом GO

Предоставление учетной записи домена разрешения SELECT на

таблицу
GRANT SELECT ON [Link] TO
[AdventureWorks2012\RosaQdM];
GO

5
 REVOKE отзывает ранее выданные Следующий пример отменяет разрешение SELECT у
разрешения пользователя RosaQdM для таблицы [Link] в базе
данных AdventureWorks2012.
USE AdventureWorks2012;
REVOKE SELECT ON OBJECT::[Link] FROM RosaQdM;
GO
DENY задает запрет, имеющий DENY permission [ ,...n ] } ON SCHEMA :: schema_name
приоритет над разрешением TO database_principal [ ,...n ]
[ CASCADE ]
[ AS denying_principal ]

Разрешение на список свойств поиска DENY (Transact-SQL)

DENY permission [ ,...n ] ON
SEARCH PROPERTY LIST :: search_property_list_name
TO database_principal [ ,...n ] [ CASCADE ]
[ AS denying_principal ]

5. Нюансы работы с NULL в SQL. Как проверить поле на NULL?

NULL – это специальное значение (псевдозначение), которое может быть записано в поле
таблицы базы данных. NULL соответствует понятию «пустое поле», то есть «поле, не
содержащее никакого значения». Введено для того, чтобы различать в полях БД пустые
значения и отсутствующие значения.

NULL означает отсутствие, неизвестность информации. Значение NULL не является

значением в полном смысле слова: по определению оно означает отсутствие значения и
не принадлежит ни одному типу данных.

Поэтому NULL не равно ни логическому значению FALSE, ни пустой строке, ни нулю.

Операторы сравнения служат для сравнения двух выражений, их результатом может
являться ИСТИНА (1), ЛОЖЬ (0) и NULL.

Оператор эквивалентность аналогичен оператору равенства, с одним лишь исключением:

в отличие от него, оператор эквивалентности вернет ИСТИНУ при сравнении NULL <=> NULL

IS [NOT] NULL — позволяет узнать равно ли проверяемое значение NULL.

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

в семье не равен NULL:

Выражение NULL != NULL не будет истинным, ведь нельзя однозначно сравнить одну
неизвестность с другой. Кстати, ложным это выражение тоже не будет, потому что при
вычислении условий Oracle не ограничивается состояниями ИСТИНА и ЛОЖЬ. Из-за
наличия элемента неопределённости в виде NULLа существует ещё одно состояние
— НЕИЗВЕСТНО.

Попробуем выбрать все записи, которые входят в набор (1, 2, NULL):

6
Как видим, строка с NULLом не выбралась. Произошло это из-за того, что
вычисление предиката "A"=TO_NUMBER(NULL) вернуло состояние НЕИЗВЕСТНО.
Для того, чтобы включить NULLы в результат запроса, придётся указать это явно:

6. Виды Join’ов (виды связывания таблиц).

Часто приходится делать выборку

из нескольких таблиц, каким-то образом
объединяя их.

Общая структура многотабличного запроса →

В большинстве случаев условием соединения является равенство столбцов таблиц

(таблица_1.поле = таблица_2.поле), однако точно так же можно использовать и другие
операторы сравнения.

Соединение бывает внутренним INNER или внешним OUTER.

При этом внешнее соединение делится на левое (LEFT), правое (RIGHT) и полное (FULL).

INNER JOIN
По умолчанию, если не указаны какие-либо параметры,
JOIN выполняется как INNER JOIN, то есть как
внутреннее (перекрёстное) соединение таблиц.

Внутреннее соединение — это соединение двух

таблиц, при котором каждая запись из первой таблицы
соединяется с каждой записью второй таблицы,
создавая тем самым все возможные комбинации
записей обеих таблиц (декартово произведение) →

Например, объединим таблицы покупок Payments и членов семьи FamilyMembers таким

образом, чтобы дополнить каждую покупку данными о том, кто её совершил.

Для того, чтобы решить поставленную задачу выполним запрос, который объединяет
поля строки из одной таблицы с полями другой,
если выполняется условие, что покупатель
товара family_member совпадает с
идентификатором члена семьи member_id:

7
Для внутреннего соединения таблиц также можно
использовать оператор WHERE.

Например, вышеприведённый запрос, написанный с

помощью INNER JOIN, будет выглядеть так →

OUTER JOIN

Внешнее соединение может быть трёх типов: левое (LEFT), правое (RIGHT) и полное
(FULL). По умолчанию оно является полным.

Главным отличием внешнего соединения от внутреннего является то, что оно

обязательно возвращает все строки одной (LEFT, RIGHT) или двух таблиц (FULL).

Внешнее левое соединение (LEFT OUTER JOIN)

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

8
Для примера получим из базы данных
расписание звонков объединённых с
соответствующими занятиями в
расписании занятий:

Внешнее правое соединение (RIGHT OUTER JOIN)

Соединение, которое возвращает все значения из правой таблицы, соединённые с

соответствующими значениями из левой таблицы если они удовлетворяют условию
соединения, или заменяет их на NULL в обратном случае.

Выборка формируется по уникальным книгам. Слева в результирующий набор попала

Энциклопедия без автора. Аналогичную выборку получим при использовании Full Join.
А справа, т.к. используем RIGHT JOIN, то книга без автора в выборку не попадает.

Внешнее полное соединение (FULL OUTER JOIN)

Соединение, которое выполняет внутреннее соединение записей и дополняет их левым

внешним соединением и правым внешним соединением.

Алгоритм работы полного соединения:

9
 1. Формируется таблица на основе внутреннего соединения (INNER JOIN).
2. В таблицу добавляются значения не вошедшие в результат формирования из
левой таблицы (LEFT OUTER JOIN).
3. В таблицу добавляются значения не вошедшие в результат формирования из
правой таблицы (RIGHT OUTER JOIN).

Соединение FULL JOIN реализовано НЕ во всех СУБД.

Например, в MySQL оно отсутствует, однако его можно очень просто эмулировать.

[Link]

Базовые запросы для разных вариантов объединения таблиц (запрос Join и схема):

7. Что лучше использовать join или подзапросы? Почему?

Обычно лучше использовать JOIN, поскольку в большинстве случаев он более понятен и

лучше оптимизируется СУБД (но 100% этого гарантировать нельзя).
Так же JOIN имеет заметное преимущество над подзапросами в случае, когда список
выбора SELECT содержит столбцы более, чем из одной таблицы.
Подзапросы лучше использовать в случаях, когда нужно вычислять агрегатные
значения и использовать их для сравнений во внешних запросах.
Пример подзапроса (вложенный запрос + математическая операция):

10
Alias (псевдоним) — это имя, назначенное источнику данных в запросе при
использовании выражения в качестве источника данных или для упрощения ввода и
прочтения инструкции SQL.
Это м.б. полезно, если имя источника данных слишком длинное или его трудно вводить.

8. Что делает UNION?

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

Оба запроса должны возвращать одинаковое число столбцов и совместимые типы

данных в соответствующих столбцах.

UNION ALL выборка будет включать дубликаты.

Необходимо отметить, что UNION сам по себе не гарантирует порядок записей. Записи из
второго запроса могут оказаться в начале, в конце или вообще перемешаться с записями
из первого запроса.
В случаях, когда требуется определенный порядок, необходимо использовать ORDER BY.

11
 С удалением SELECT * FROM имя_таблицы1 WHERE условие
UNION SELECT * FROM имя_таблицы2 WHERE условие
дублей:

Без удаления SELECT * FROM имя_таблицы1 WHERE условие

UNION ALL SELECT * FROM имя_таблицы2 WHERE
дублей:
условие

Можно объединять SELECT * FROM имя_таблицы1 WHERE условие

UNION SELECT * FROM имя_таблицы2 WHERE условие
не две таблицы, а
UNION SELECT * FROM имя_таблицы3 WHERE условие
три или более: UNION SELECT * FROM имя_таблицы4 WHERE условие

9. Чем WHERE отличается от HAVING?

Ответа про то, что используются в разных частях запроса недостаточно…

Условный оператор WERE применяют

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

Для этого в операторе SELECT существует параметр WHERE, после которого следует
условие для ограничения строк. Если запись удовлетворяет этому условию, то попадает в
результат, иначе отбрасывается.

Выведем все полёты, которые были

совершены на самолёте «Boeing», но,
при этом, вылет был не из Лондона:

Оператор HAVING используется для

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

Общая структура запроса →

Отличие HAVING от WHERE

WHERE = ВЫБОРКА + ГРУППИРОВКА, то есть сначала выбираются записи по условию,

а затем могут быть сгруппированы, отсортированы и т.д.

HAVING = ГРУППИРОВКА + ВЫБОРКА, т.е. сначала группируются записи, а затем

выбираются по условию, при этом, в отличие от WHERE, в нём можно использовать
значения агрегатных функций.

Пример использования:
выведем общую сумму, потраченную на
покупки, для каждого члена семьи, где общая
сумма покупки меньше, чем 5000 рублей:

12
10. Что такое GROUP BY?

Иногда требуется узнать информацию не о самих объектах, а об определенных группах,

которые они образуют. Для этого используется оператор GROUP BY и агрегатные
функции.

Общая структура запроса →

Пример использования:
выведем общую сумму, потраченную на
покупки, для каждого члена семьи, где
общая сумма покупки менее 5000 руб.:

При выполнении запроса происходит группировка по полю family_member и

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

11. Что такое ORDER BY?

При выполнении SELECT запроса, строки по умолчанию возвращаются в

неопределенном порядке.

Фактический порядок строк в этом случае зависит от плана соединения и сканирования, а

также от порядка расположения данных на диске, поэтому полагаться на него нельзя.
Для упорядочивания записей используется конструкция ORDER BY.

Общая структура запроса ORDER BY

13
В этой структуре запроса необязательные параметры указаны в квадратных скобках:

DESCending переводится, как: нисходящий, убывающий, падающий.

ASCending переводится, как: восходящий, поднимающийся возрастающий.

Сортировка по нескольким столбцам

Для сортировки результатов по двум

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

Данные будут сортироваться по первому столбцу, но, в случае если попадаются

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

Правило сортировки применяется только к тому

столбцу, за которым оно следует →

Примеры использования:

Выведем названия авиакомпаний в алфавитном порядке из таблицы Company.

Сортировка строковых данных осуществляется в лексикографическом
(алфавитном) порядке.

Выведем всю информацию о полетах, отсортированную по времени вылета

самолета в порядке возрастания и по времени прилета в аэропорт в порядке
убывания, из таблицы Trip.

В данном примере в начале отсортировывается информация по времени вылета.

Затем там, где время вылета совпадает, отсортировывается по времени прилёта.

 выборка по нескольким базам данных

12. Что такое DISTINCT?

DISTINCT используется для исключения повторяющихся строк из результата.

Иногда возникают ситуации, в которых нужно получить только уникальные записи.

Для этого вы можете использовать DISTINCT.

14
Например, выведем список городов без
повторений, в которые летали самолеты →

Справа применяем ключевое слово DISTINCT, получаем выборку по уникальным именам.

12. Что такое LIMIT?

Устанавливаем ограничение на максимальное

количество записей, которые хотим получить в
результирующем наборе. Например, вывести
уникальные имена, но не более двух →

Вывести все уникальные фамилии из списка.

Пропустить две (OFFSET 2), оставить в результате две записи (LIMIT 2).

Первые две уникальные записи: Пушкин и Дюма пропущены, Чехов и Толстой остаются.

Важно!
Когда мы делаем выборку SELECT FROM, то порядок НЕ гарантирован и может меняться
в зависимости от вставки или индекса. Поэтому сначала нужно сделать сортировку
(например, по идентификатору), а потом добавлять ограничение LIMIT и OFFSET.

Для сортировки используем ORDER BY (см. выше).

13. Что такое EXISTS?

EXISTS берет подзапрос, как аргумент, и оценивает его как TRUE, если подзапрос
возвращает какие-либо записи и FALSE, если нет.

15
14. Расскажите про операторы IN, BETWEEN, LIKE.

Ключевое слово LIKE используем, когда нужно сделать выборку по полю (тут по имени).
Можем указать любое поле и даже только одну букву →
LIKE чувствителен к регистру (‘alex’ в выборку не попадёт).

Если в результирующий набор должны попасть данные в заданном диапазоне (например,

зарплата сотрудников), то используем ключевое слово BETWEEN (удобен также для дат).

Если в выборку должны попасть результаты с конкретными данными, то используем IN:

15. Что делает оператор MERGE? Какие у него есть ограничения?

MERGE позволяет осуществить СЛИЯНИЕ

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

16
16. Какие агрегатные/агрегирующие функции вы знаете?

Агрегатная функция вычисляет единственное значение (получаем ОДНУ строку),

обрабатывая множество строк. Агрегатные функции (иногда называют агрегирующие)
есть во всех СУБД.

Агрегатные функции, вычисляющие по столбцу для набора строк:

• sum (сумму)
• avg (среднее)
• max (максимум)
• min (минимум)
• count (количество), есть также вариант со звёздочкой: SELECT count(*) FROM employees;
• concat (конкатенация, например, объединение имени и фамилии в одном столбце)

Не агрегатные математические функции (их много) – применяют действие ко всем

значениям в столбце:
• upper/lower (перевод в верхний/нижний регистр)
• now (текущая дата и т.д.)

17
Важно понимать, как соотносятся агрегатные функции и SQL-предложения WHERE и HAVING.

Основное отличие WHERE от HAVING заключается в том, что WHERE сначала выбирает строки, а затем группирует их и
вычисляет агрегатные функции (таким образом, она отбирает строки для вычисления агрегатов), тогда как HAVING
отбирает строки групп после группировки и вычисления агрегатных функций. Следовательно…

WHERE НЕ должно содержать агрегатных функций; не имеет смысла использовать

агрегатные функции для определения строк для вычисления агрегатных функций.

HAVING всегда СОДЕРЖИТ агрегатные функции!

Строго говоря, вы можете написать предложение HAVING, не используя агрегаты, но это редко бывает полезно. То же
самое условие может работать более эффективно на стадии WHERE.)

17. Что такое ограничения (constraints)? Какие вы знаете?

Ограничения SQL — это правила, применяемые к столбцам данных таблицы.

Они используются, чтобы ограничить типы

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

Это обеспечивает точность и надежность

данных в базе.

NOT NULL – возможность вставки пустых значений в колонку таблицы.

CHECK – ограничение на диапазон значений (год для даты, например)
UNIQUE – значение в этой колонкe должно быть уникальным.

PRIMARY KEY (pkey) колонка содержит уникальное NOT NULL значение (часто это ID).
Может быть только одним в таблице! В отличие от UNIQUE, например (ограничение
для столбца, но не для таблицы).

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

18
Есть также неявные ограничения, например по количеству символов для VARCHAR
(аналог строки, последовательность символов в одинарных кавычках). Указывается в
круглых скобках. Один символ char = 1 байт памяти, соответственно 128 – это и
количество символов, и количество памяти.

При попытке «обойти ограничение» (указан год, меньше, чем 1995), вылетает ошибка:

18. Что такое суррогатные ключи?

Суррогатный ключ — понятие теории

реляционных баз данных.
Это дополнительное служебное поле,
добавленное к уже имеющимся
информационным полям таблицы,
единственное предназначение которого
— служить первичным ключом
Primary Key.

Дайте определение терминам «простой», «составной» (composite), «потенциальный»

(candidate) и «альтернативный» (alternate) ключ.
Простой ключ состоит из одного атрибута (поля). Составной - из двух и более.
Потенциальный ключ - простой или составной ключ, который уникально
идентифицирует каждую запись набора данных. При этом потенциальный ключ должен
обладать критерием неизбыточности: при удалении любого из полей набор полей
перестает уникально идентифицировать запись.
Из множества всех потенциальных ключей набора данных выбирают первичный ключ, все
остальные ключи называют альтернативными.
Что такое «первичный ключ» (primary key)? Каковы критерии его выбора?
Первичный ключ (primary key) в реляционной модели данных один из потенциальных
ключей отношения, выбранный в качестве основного ключа (ключа по умолчанию).

19
Если в отношении имеется единственный потенциальный ключ, он является и первичным
ключом. Если потенциальных ключей несколько, один из них выбирается в качестве
первичного, а другие называют «альтернативными».
В качестве первичного обычно выбирается тот из потенциальных ключей, который
наиболее удобен. Поэтому в качестве первичного ключа, как правило, выбирают тот,
который имеет наименьший размер (физического хранения) и/или включает наименьшее
количество атрибутов. Другой критерий выбора первичного ключа — сохранение его
уникальности со временем. Поэтому в качестве первичного ключа стараются выбирать
такой потенциальный ключ, который с наибольшей вероятностью никогда не утратит
уникальность.
Что такое «внешний ключ» (foreign key)?
Внешний ключ (foreign key) — подмножество атрибутов некоторого отношения A,
значения которых должны совпадать со значениями некоторого потенциального ключа
некоторого отношения B. Виды отношений таблиц и моделирование процессов UML.

Как посмотреть графическое отображение БД в Идее или DBeaver? Выбираем таблицы

(или БД), кликаем правой кнопкой мышки и в выпадающем списке выбираем Diagrams.

Теория множеств
Ограничения для операций со множествами:

• кол-во полей/столбцов в строках должны совпадать

• тип данных в колонках должны совпадать

20
Объединения (UNION и UNION ALL), пересечения (INTERSECT) и исключения (EXCEPT):

*Какой вид структуры данных (какое дерево) используется при работе с базами данных?
Как под капотом SQL организован поиск и
извлечение нужных данных?
Таблица – это файл на жёстком диске, где в
бинарном виде представлены данные,
отражённые в таблицах.
Индексы помогают обеспечивать формирование
быстрой выборки данных из БД (один индекс =
один файл).
Структура данных B-Tree используется по
умолчанию для быстрого поиска данных по
уровням (не путать с бинарным деревом).
Семейство B-Tree индексов — это наиболее часто используемый тип данных (индексов),
организованных как сбалансированное дерево, упорядоченных ключей и созданы для
эффективной работы с дисковой памятью. Они поддерживаются практически всеми СУБД
как реляционными, так не реляционными, и практически для всех типов данных.

21
Справка: Деревья представляют собой структуры данных, в которых реализованы операции над динамическими
множествами. Из таких операций хотелось бы выделить — поиск элемента, поиск минимального (максимального)
элемента, вставка, удаление, переход к родителю, переход к ребенку.

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

Основные операции в деревьях выполняются за время, пропорциональное его высоте. Сбалансированные деревья
минимизируют свою высоту (к примеру, высота бинарного сбалансированного дерева с n узлами равна log n).

Разделяют уровни B-дерева:

• корневой узел (м.б. из нескольких сегментов) + ссылки на узлы промежуточного ур.

• промежуточные узлы (количество узлов +1 к кол-ву сегментов корневого) + ссылки
• узлы-листья (ссылки на следующий уровень отсутствуют)
Поиск индекса начинается от корневого узла. Меньшее значение сегмента всегда левее.
Ищем, например, 29. Сравниваем попарно значения, начиная с левого корневого
сегмента. Значение сегмента меньше? Тогда идем вправо на этом же уровне. Значение
больше? Тогда переходим на уровень ниже и продолжаем двигаться слева направо по
сегментам узла и т.д.

Найдя нужный индекс (29), переходим к нужному файлу по ссылке (1 индекс = 1 файл).
Операция поиска выполняется за время O(t logt n), где t – минимальная степень. Важно
здесь, что дисковых операций мы совершаем всего лишь O(logt n)!
[Link]

Ссылка может быть представлена смещением количества байт от начала файла

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

22
Почему так? Количество элементов в одном узле хранятся в одном
сегменте на жёстком диске (см. рис.) и нужно обеспечить более-менее
равномерное распределение памяти, а также наименьшее возможное
количество обращений к памяти диска для оптимальной работы с БД.
За один раз компьютер считывает в оперативную память все данные из
одного сегмента на жёстком диске.
*Как оценить сложность выполняемого запроса?
Стоимостной оптимизатор делает расчёт на основании двух показателей:

• page_cost, измеряется в единицах стоимости 1.0 – сколько страниц нужно считать

для выполнения одного запроса (input-output).
• сpu_cost – как много операций сделал процессор для выполнения запроса.
Например, прошлись по строчке, считали информацию одной записи в таблице.
Стоимость будет 0,01.
В PostgreSQL есть специальная таблица pg_class, которая хранит
статистические данные, на основании которых базируется работа
стоимостного оптимизатора.
*Как узнать, сколько места занимает таблица в памяти?
В приведённом примере таблица имеет 6 полей.
Для целочисленных типов данных в поле берём
фиксированное значение (8 байт для BIGINT).
Символьные типы определяем через запрос →
Потом суммируем все поля одной записи и получаем результат одной строки таблицы в
байтах. Умножив на кол-во записей в таблице, можно оценить весь объём данных.
Full Scan – это очень дорогая операция (перебор всех строк и полей).
Индексы позволяют уменьшить стоимость запросов.

*Методы сканирования таблиц:

23
19. Что такое индексы? Какие они бывают?

Индексы – это специальные структуры в базах данных, которые позволяют ускорить

поиск и сортировку по определённому полю или набору полей в таблице, а также
обеспечивают уникальность данных. Подробно и доступно тут → [Link]

Когда мы создаём первичный ключ (чаще

всего это уникальный идентификатор id), то
автоматически формируется индекс.

Совокупность этих данных/индексов – это и

есть структура B-Tree (по умолчанию),
которая создаёт отдельный файл, где
находятся все ключи.

24
Индексы бывают кластерные и не кластерные (еще называют кластеризованные и нет).

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

Кластерный индекс хранит реальные строки/записи данных в листьях индекса. Важной

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

Не кластерный индекс, созданный для такой таблицы, содержит только указатели

ссылки) на записи таблицы (а не саму таблицу).

MySQL PostgreSQL MS SQL Oracle

B-Tree index Есть Есть Есть Есть

Spatial indexes R-Tree с Rtree_GiST 4-х уровневый Grid- R-Tree c

квадратичным (используется based spatial index квадратичным
Поддерживаемые разбиением линейное (отдельные для разбиением;
пространственные разбиение) географических и Quadtree
индексы геодезических данных)

Hash index Только в Есть Нет Нет

таблицах типа
Memory

Bitmap index Нет Есть Нет Есть

Reverse index Нет Нет Нет Есть

Inverted index Есть Есть Есть Есть

Partial index Нет Есть Есть Нет

Function based Нет Есть Есть Есть

index

Как и зачем создавать индекс?

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

Создание некластеризованного индекса в таблице или представлении:

25
Создание кластеризованного индекса в таблице и использование имени, состоящего из
трех элементов, для таблицы:

Создание некластеризованного индекса с ограничением уникальности и указание порядка

сортировки:

Индексы – это решение многих проблем с производительностью, но слишком много

индексов на таблицах будет влиять на производительность операторов INSERT, UPDATE
и DELETE. Это связано с обновлением индексов, когда вы добавляете (INSERT),
изменяете (UPDATE) или удаляете (DELETE) данные.

Зачем создавать индекс?

Оптимизация! Кратное улучшение времени поиска за счёт применения индекса:

*Что такое селективность?

Селективность – это отношение количества уникальных элементов к кол-ву записей, т.е.

строчек в таблице. В примере ниже селективность плохая, меньше 20%. Оптимально – 1.

Селективность влияет на скорость поиска, поэтому необходимо определять порядок

индексов.

Важно также понимать, что DML функции (обновление, изменение, удаление данных)
обновляет также все индексы, поэтому быть внимательным при создании индексов.

*Анализ выполнения запросов (если есть проблемы с производительностью)

26
Запрос explain analyze показывает план выполнения запроса: что ожидали и что
получили. Можно это использовать, чтобы проанализировать, каким образом
выполняется запрос + получить статистические данные.

Например, проанализируем, как происходит связывание Join с помощью планового

выполнения запросов?
ТРИ варианта связывания таблиц (и как это устроено): nested loop, hash join, merge join.

• Nested Loop работает на маленькой выборке. Считывает одну таблицу, чтобы

обратиться по индексу второй.
• Hash Join работает на больших выборках. Считывает обе таблицы полностью, а
потом на основе второй таблицы создаёт хэш-таблицу (операция считывания
которой происходит за константное время О(1) – очень быстро.
27
 • Merge Join работает на основании отсортированных ключей. Сканирует обе
таблицы, сортирует, а затем быстро сравнивает элементы попарно.
Способ связывания выбирает планировщик на основании статистических и кэшированных
данных (после предыдущих запросов).

Здесь Nested Loop занимался связыванием двух таблиц (снизу-вверх: в первой поиск по
индексу p_key, во второй использовался full scan, время выполнения соответствующее).
Nested Loop используется, когда записей не очень много, а тут есть ограничение limit.
Как устроено? Идёт обычным циклом по просканированной таблице test_2, а дальше
Index Condition связывает индекс из таблицы test_2 с индексом первой таблицы
(id=t.test_1_id); loops=100 – это значит, что 100 раз выполняли это действие по 1 строке за
указанные время и стоимость для одной записи:

Тут используется Hash Join. Он также состоит из двух частей. Сканирует первую таблицу,
а на основании просканированной второй таблицы создаёт хэш-таблицу.
Сначала происходит full scan одной таблицы, но в случае второй мы уже используем не
индекс. Элемент Hash полностью просканировал таблицу и на основании полного
считывания информации создал структуру данных Hash Table (хэш таблицу). Поиск по
ней происходит за константное время О(1). Можно увидеть, сколько бакетов создано.
Batches: 2, а значение >1 означает, что оперативной памяти не хватило и часть инфо
была сохранена на диск. То, что попало в Memory заняло около 3 мб.
Hash Condition (синяя заливка строки) показывает, что данные довольно быстро
получены. Условия связывания указаны в скобках.

Также быстрый вариант Merge Join. Состоит из трёх основных частей, использует
отсортированную последовательность.
Для сканирования таблицы test_1 использовали индекс p_key. Для таблицы test_2
сначала использовали full scan, далее отсортировали по ключу test_1_id

28
Указан метод сортировки и количество выделенной памяти около 3 мб. Далее Merge Join
обходит обычным циклом обе отсортированных последовательности (как если бы циклом
for мы обходили два отсортированных массива и попарно сравнивали значения).
20. Чем TRUNCATE отличается от DELETE?

DELETE - оператор DML, удаляет записи из таблицы, которые удовлетворяют критерию

WHERE при этом задействуются триггеры, ограничения и т.д. РОЛЛБЕК
TRUNCATE - DDL оператор, удаляет таблицу и создает ее заново. Причем если на эту
таблицу есть ссылки FOREIGN KEY или таблица используется в репликации, то
пересоздать такую таблицу не получится).
21. Что такое хранимые процедуры? Для чего они нужны?

Хранимые процедуры позволяют повысить производительность, расширяют возможности

программирования и поддерживают функции безопасности данных. В большинстве СУБД
при первом запуске хранимой процедуры она компилируется (выполняется
синтаксический анализ и генерируется план доступа к данным) и в дальнейшем её
обработка осуществляется быстрее.
Хранимая процедура — объект базы данных, представляющий собой набор SQL-
инструкций, который хранится на сервере. Хранимые процедуры очень похожи на
обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные
параметры и локальные переменные, в них могут производиться числовые вычисления и
операции над символьными данными, результаты которых могут присваиваться
переменным и параметрам. В хранимых процедурах могут выполняться стандартные
операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах
возможны циклы и ветвления, то есть в них могут использоваться инструкции управления
процессом исполнения.
Хранимые процедуры позволяют повысить производительность, расширяют возможности
программирования и поддерживают функции безопасности данных. В большинстве СУБД
при первом запуске хранимой процедуры она компилируется (выполняется
синтаксический анализ и генерируется план доступа к данным) и в дальнейшем её
обработка осуществляется быстрее.
22. Что такое представления (VIEW)? Для чего они нужны?

Представление, View – виртуальная таблица, представляющая данные одной или более

таблиц альтернативным образом. Зачем? Чтобы делать выборки (операция SELECT).
В действительности представление – всего лишь результат выполнения оператора
SELECT, который хранится в структуре памяти, напоминающей SQL таблицу. Они
работают в запросах и операторах DML точно также как и основные таблицы, но не
содержат никаких собственных данных.
Представления значительно расширяют возможности управления данными.
Это способ дать публичный доступ к некоторой (но не всей) информации в таблице.
Как создать VIEW? К команде SQL добавить одну строку и появится папка views, где
будут храниться типичные запросы.

29
Как использовать?

Что такое Materialized view и чем отличается от просто View?

Материализованное представление — физический объект базы данных, содержащий
результат выполнения запроса. Только для чтения!
Материализованные представления позволяют многократно ускорить выполнение
запросов, обращающихся к большому количеству записей, позволяя за секунды
выполнять запросы к терабайтам данных.

Полезны, если нам нужно запретить

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

Отличия:
View Materialized View
Что это? Виртуальная таблица, созданная в физическая копия, картинка или снимок
результате сохранённого запроса базовой таблицы на момент сохранения

Где хранятся? не хранятся физически на диске хранятся на диске

30
 Обновления обновляется, так как запрос, обновляется вручную или путем применения
создающий представление, к нему триггеров (события JOB для
выполняется каждый раз при обновления данных. Например, регулярная
использовании VIEW выгрузка статистических данных).

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

ответ на сохранённый запрос сформировано

Место в памяти это просто отображение, поэтому использует пространство памяти, хранящееся
ему не требуется место в памяти на диске

23. Что такое временные таблицы? Для чего они нужны?

Временная таблица – это объект базы данных, который хранится и управляется системой
базы данных на временной основе. Они могут быть локальными или глобальными.
Используется для сохранения результатов вызова хранимой процедуры, уменьшение
числа строк при соединениях, агрегирование данных из различных источников или как
замена курсоров и параметризованных представлений.
24. Что такое транзакции? Расскажите про принципы ACID.

Транзакция – это единица работы в рамках соединения с базой данных.

Имеет ДВА состояния:

• Выполняется полностью – commit
• Откатывается полностью – rollback

ACID – это набор свойств, которыми должна обладать каждая транзакция БД.

31
Lost Update – при одновременном изменении одного блока данных разными
транзакциями теряются все изменения, кроме последнего;

32
25. Расскажите про уровни изолированности транзакций.

Уровень изолированности транзакций — условное значение, определяющее, в какой

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

33
В порядке роста уровня изолированности транзакций и надежности работы с данными:

Чтение неподтверждённых данных (read uncommitted, dirty read) — чтение

незафиксированных изменений как своей транзакции, так и параллельных транзакций.
Нет гарантии, что данные, измененные другими транзакциями, не будут в любой момент
изменены в результате их отката, поэтому такое чтение является потенциальным
источником ошибок. Невозможны потерянные изменения, возможны неповторяемое
чтение и фантомы.
Типичный способ реализации данного уровня изоляции — блокировка данных на время выполнения команды изменения,
что гарантирует, что команды изменения одних и тех же строк, запущенные параллельно, фактически выполнятся
последовательно, и ни одно из изменений не потеряется.

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

Чтение подтвержденных данных (read committed) — чтение всех изменений

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

Блокирование читаемых и изменяемых данных.

Заключается в том, что пишущая транзакция блокирует изменяемые данные для читающих транзакций, работающих на
уровне read committed или более высоком, до своего завершения, препятствуя, таким образом, «грязному» чтению, а
данные, блокируемые читающей транзакцией, освобождаются сразу после завершения операции SELECT (таким образом,
ситуация «неповторяющегося чтения» может возникать на данном уровне изоляции).

Сохранение нескольких версий параллельно изменяемых строк.

При каждом изменении строки СУБД создаёт новую версию этой строки, с которой продолжает работать изменившая
данные транзакция, в то время как любой другой «читающей» транзакции возвращается последняя зафиксированная
версия. Преимущество такого подхода в том, что он обеспечивает бо́льшую скорость, так как предотвращает блокировки.

Повторяемость чтения (repeatable read, snapshot) — чтение всех изменений

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

Блокировки в разделяющем режиме применяются ко всем данным, считываемым любой инструкцией транзакции, и
сохраняются до её завершения. Это запрещает другим транзакциям изменять строки, которые были считаны
незавершённой транзакцией. Пользоваться данным и более высокими уровнями транзакций без необходимости обычно не
рекомендуется.

34
Упорядочиваемость (serializable) — результат параллельного выполнения
стерилизуемой транзакции с другими транзакциями должен быть логически эквивалентен
результату их какого-либо последовательного выполнения. Проблемы синхронизации не
возникают.

Самый высокий уровень изолированности; транзакции полностью изолируются друг от друга. Результат выполнения
нескольких параллельных транзакций должен быть таким, как если бы они выполнялись последовательно. Только на этом
уровне параллельные транзакции не подвержены эффекту «фантомного чтения».

26. Что такое нормализация и де- нормализация? Расскажите про 3 нормальные

формы?

Нормализация – процесс удаления избыточных данных. Это метод проектирования

БД, который позволяет привести базу к минимальной избыточности.

ОШИБКА!!! Не говорите, что нормализация – это процесс приведения к нормальной форме (масло масленое).

Избыточность данных – это ситуация, когда одни и те же данные хранятся в базе в

нескольких местах (таблицах). Именно это и приводит к аномалиям.

35
Нормализация нужна для:
• устранения аномалий
• повышения производительности
• повышения удобства управления данными

Базовые принципы реляционной теории:

• порядок строк не имеет значения
• порядок столбцов не имеет значения

[Link]

Пример аномалии (данные избыточны и это проблема).

Решение: вынести данные о материале в отдельную таблицу (справа), появляется связь.

Статья на хабре, откуда взяты примеры таблиц: [Link]

Первая нормальная форма 1НФ

• В таблице НЕ должно быть дублирующих строк

• В каждой ячейке таблицы хранится атомарное значение (одно НЕ составное)
• В столбце хранятся данные одного типа
• Отсутствуют массивы и списки в любом виде
36
Нарушение нормализации 1НФ происходит в моделях BMW, т.к. в одной ячейке
содержится список из 3 элементов: M5, X5M, M1, т.е. он не является атомарным.

Преобразуем таблицу к 1НФ:

Вторая нормальная форма 2НФ

• Таблица должна находиться в 1НФ

• Таблица должна иметь ключ
• Все неключевые столбцы должны зависеть от полного ключа (в случае, если ключ
составной)

Пример: человек и его паспорт. По серии и номеру (составной ключ) можно определить уникального человека.

Отношение находится во 2НФ, если оно находится в 1НФ и каждый не ключевой атрибут
неприводимо зависит от Первичного Ключа (ПК).

Неприводимость означает, что в составе потенциального ключа отсутствует меньшее

подмножество атрибутов, от которого можно также вывести данную функциональную
зависимость. Например, дана такая таблица с составным ключом (модель + фирма):

Таблица находится в первой нормальной форме, но не во второй. Цена машины зависит

от модели и фирмы (составной ключ). Скидка зависят ТОЛЬКО от фирмы, то есть
зависимость от первичного ключа неполная.

37
Исправляется это путем декомпозиции на два отношения, в которых не ключевые
атрибуты зависят от первичного ключа ПК.

Декомпозиция: процесс разбиения одного отношения (таблицы) на несколько:

Третья нормальная форма 3НФ

• Таблица должна находиться во 2НФ

• В таблицах отсутствует транзитивная зависимость
Отношение находится в 3НФ, когда находится во 2НФ и каждый не ключевой атрибут не
транзитивно зависит от первичного ключа. Проще говоря, второе правило требует
выносить все не ключевые поля, содержимое которых может относиться к нескольким
записям таблицы в отдельные таблицы. Таблица находится во 2НФ, но не в 3НФ:

В отношении атрибут «Модель» является первичным ключом. Личных телефонов у

автомобилей нет, и телефон зависит исключительно от магазина.

Таким образом, в отношении существуют следующие функциональные зависимости:

38
Модель → Магазин, Магазин → Телефон, Модель → Телефон. Зависимость Модель →
Телефон является транзитивной, следовательно, отношение не находится в 3НФ.

В результате разделения
исходного отношения
получаются два отношения,
находящиеся в 3НФ →

Магазин → Телефон
Модель → Магазин

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

Денормализация базы данных — это процесс осознанного приведения базы данных к

виду, в котором она не будет соответствовать правилам нормализации. Обычно это
необходимо для повышения производительности и скорости извлечения данных, за счет
увеличения избыточности данных.
При денормализации важно сохранить баланс между повышением скорости работы базы
и увеличением риска появления противоречивых данных, между облегчением жизни
программистам, пишущим Select'ы, и усложнением задачи тех, кто обеспечивает
наполнение базы и обновление данных. Поэтому проводить денормализацию базы надо
очень аккуратно, очень выборочно, только там, где без этого никак не обойтись.

Если заранее нельзя подсчитать плюсы и минусы денормализации, то изначально

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

Оправдан ли будет переход?

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

39
Используемые термины

Атрибут — свойство некоторой сущности. Часто называется полем таблицы.

Домен атрибута — множество допустимых значений, которые может принимать атрибут.

Кортеж — конечное множество взаимосвязанных допустимых значений атрибутов,

которые вместе описывают некоторую сущность (строка таблицы).

Отношение — конечное множество кортежей (таблица).

Схема отношения — конечное множество атрибутов, определяющих некоторую

сущность. Иными словами, это структура таблицы, состоящей из конкретного набора
полей.

Проекция — отношение, полученное из заданного путём удаления и (или) перестановки

некоторых атрибутов.

Функциональная зависимость между атрибутами (множествами атрибутов) X и Y

означает, что для любого допустимого набора кортежей в данном отношении: если два
кортежа совпадают по значению X, то они совпадают по значению Y. Например, если
значение атрибута «Название компании» — Canonical Ltd, то значением атрибута «Штаб-
квартира» в таком кортеже всегда будет Millbank Tower, London, United Kingdom.
Обозначение: {X} -> {Y}.

Нормальная форма — требование, предъявляемое к структуре таблиц в теории

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

Метод нормальных форм (НФ) состоит в сборе информации о объектах решения

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

Цель нормализации: исключить избыточное дублирование данных, которое является

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

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

в БД либо существенно усложняет обработку БД. Причиной является излишнее
дублирование данных в таблице, которое вызывается наличием функциональных
зависимостей от не ключевых атрибутов.

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

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

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

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

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

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

40
27. Что такое TIMESTAMP?
Для работы с датой и временем в MySQL есть несколько типов данных:
DATE, TIME, DATETIME и TIMESTAMP.

Тип Описание Диапазон значений Размер

Хранит значения даты в виде ГГГГ-ММ-ДД. от 1000-01-01

DATE Например, 2022-12-05 до 9999-12-31 3 байта

Хранит значения времени в формате ЧЧ:ММ:СС. (или в

формате ЧЧЧ:ММ:СС для значений с большим
количеством часов). от -838:59:59
TIME Например, 800:50:50 до 838:59:59 3 байта

Хранит значение даты и времени в виде ГГГГ-MM-ДД

ЧЧ:ММ:СС. от 1000-01-01 00:00:00
DATETIME Например, 2022-12-05 10:37:22 до 9999-12-31 23:59:59 8 байт

Хранит значение даты и времени в виде ГГГГ-MM-ДД

ЧЧ:ММ:СС. от 1970-01-01 00:00:01
TIMESTAMP Например, 2022-12-05 10:37:22 до 2038-01-19 03:14:07 4 байта

Отличие TIMESTAMP и DATETIME

Типы данных DATETIME и TIMESTAMP в MySQL похожи друг на друга, так как оба
направлены на хранение даты и времени. Но между ними есть ряд существенных
отличий, определяющих какой из этих типов данных, когда лучше использовать.

Также стоит помнить о существенном ограничении TIMESTAMP в диапазоне возможных

значений от 1970-01-01 00:00:01 до 2038-01-19 03:14:07, что ограничивает его применении.
Так, данный тип данных не подойдет для хранения дат рождения пользователей.
Способ задания значений:
Значения DATETIME, DATE и TIMESTAMP могут быть заданы одним из следующих
способов:

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

28. Расскажи про шардирование баз данных

При большом количестве данных запросы начинают долго выполняться, и сервер
перестаёт справляться с нагрузкой. Одно из решений для оптимизации — это
масштабирование базы данных. Например, шардинг или репликация (копирование).
Шардинг бывает вертикальным (партицирование для 1 экз. БД) и горизонтальным.
Допустим, есть большая таблица пользователей. Партицирование — это когда одну
большую таблицу разделяют на много маленьких по какому-либо принципу.
Единственное отличие горизонтального масштабирования от вертикального в том, что
горизонтальное будет разносить данные по разным инстансам в других базах.
Т.е. только записи с category_id=1 будут
попадать в эту таблицу.
На базовую таблицу необходимо добавить
правило. Когда мы будем работать с таблицей
news, вставка на запись с category_id = 1
должна попасть именно в партицию news_1.
Правило называем как хотим.

42
29. Как сделать запрос из двух баз?
Допустим, у нас есть две таблицы: с товарами (есть поле owner_id, отвечающего
за id владельца товара) и с пользователями (есть поле id).
Мы хотим одним SQL-запросом получить все записи, причём чтобы в каждой была
информация о пользователе и его одном товаре. В следующей записи была информация
о том же пользователе и следующем его товаре. Когда товары этого пользователя
закончатся, то переходить к следующему пользователю.
Таким образом, мы должны соединить две таблицы и получить результат, в
котором каждая запись содержит информацию о пользователе и об одном его товаре.

30. Что такое триггер?

Триггер (trigger) — это хранимая процедура особого типа, исполнение которой
обусловлено действием по модификации данных: добавлением, удалением или
изменением данных в заданной таблице реляционной базы данных.
Триггер запускается сервером автоматически и все производимые им модификации
данных рассматриваются как выполняемые в транзакции, в которой выполнено действие,
вызвавшее срабатывание триггера.
Момент запуска триггера определяется с помощью ключевых слов BEFORE (триггер
запускается до выполнения связанного с ним события) или AFTER (после события).

Доп. вопросы:
Что такое sql-injection (SQL инъекции)?
Внедрение SQL кода (англ. SQL injection) — один из распространённых способов взлома
сайтов и программ, работающих с базами данных, основанный на внедрении в запрос
произвольного SQL-кода.
SQL инъекция означает ввод/вставку SQL-кода в запрос с помощью введенных
пользователем данных. Это может произойти в любых приложениях, использующих
реляционные базы данных, такие как Oracle, MySQL, PostgreSQL и SQL Server.

43
Н. Алишев (Spring Framework. Урок 26: SQL инъекции. PreparedStatement. JDBC API) [Link]

Как выбрать между Statement, PreparedStatement и CallableStatement?

Statement – SQL-выражение, подготовленное к выполнению в рамках определенной

JDBC-сессии. Выполняется методом execute для обычного выражения,
executeUpdate для модифицирующего, executeBatch для пакетного. Когда ожидаемый
размер результата больше Integer.MAX_VALUE, используются версии
методов executeLarge*.

После выполнения, экземпляр Statement владеет ResultSet-ом, и другими данными о

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

PreparedStatement – пред-скомпилированная версия Statement, его наследник.

Эффективнее выполняет одно и то же выражение множество раз. Входные параметры
объявляются в SQL-выражении символом ?, следом сеттерами задаются их типы и
значения. Делегирует обязанность экранировать введенные пользователем параметры
базе данных.

CallableStatement – наследник PreparedStatement для вызова хранимых процедур.

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

Экземпляры всех трех типов создаются методами интерфейса Connection.

44
Какие классы вовлечены в соединение с базой данных?

DriverManager управляет всеми JDBC-драйверами в приложении. Представляет набор

статических методов. Лениво загружает системным класс-лоадером доступные
Пред-сконфигурированные драйверы:
• По списку полных имен классов из проперти [Link];
• Через Service Provider Interface (SPI).

Менеджер занимается созданием экземпляра Connection – ключевого класса при работе

с базой данных. Альтернативный менеджеру (и даже рекомендуемый) способ соединения
с источником данных – ConnectionBuilder. Билдер получают из [Link] –
формально это часть Java EE, так что здесь не будем подробно на нем останавливаться.

Driver – главный класс реализации JDBC-драйвера. Когда загружается класслоадером,

сам регистрирует себя в DriverManager. Так что кроме предсконфигурированных
драйверов, дополнительные можно загрузить просто вызвав [Link].

Можно явно создавать Connection через драйвер, минуя менеджера и билдер. Драйвер
предоставляет информацию о возможных/требуемых для своей работы свойствах в виде
массива DriverPropertyInfo.

DriverAction – дополнительный интерфейс, который должен реализовывать Driver, если

хочет получать уведомления о раз-регистрации DriverManager-ом.

Что можно делать с классом Connection?

Итак, в результате соединения JDBC драйвера создается объект Connection – сессия

работы с базой данных. Это главный класс при работе с JDBC. Основная роль этого
класса – исполнение SQL-выражений (Statement) и получение их результатов в
виде ResultSet.

Connection предоставляет в виде класса DatabaseMetaData мета-информацию о базе

данных в целом: таблицы, поддерживаемая грамматика SQL, хранимые процедуры,
возможности этого соединения, и т.д..

В коннекшне задается множество настройки самого соединения. Это уровень изоляции

транзакций, режим авто-коммита, ключи шардирования, и многое другое. Маппинг типов
данных SQL в Java-типы задается здесь же, свойством typeMap.

Помимо выполнения выражений, Connection предоставляет средства для управления

транзакциями. Его методами можно создать Savepoint, откатиться к нему, закоммитить
транзакцию когда авто-коммит отключен.

Какая разница между @⁠ElementCollection, @⁠OneToMany и @⁠ManyToMany?

Все эти аннотации – часть JPA (Java Persistence API).

С их использованием мы регулярно сталкиваемся в реализациях JPA, таких как Hibernate.

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

@⁠OneToMany и @⁠ManyToMany хранят вложенные объекты как отдельные полноценные

сущности – для них действуют всё те же требования, которые JPA выдвигает для
всех @Entity классов. Каждая из аннотаций отвечает за свое отношение.

45
@⁠ElementCollection создает коллекцию встраиваемых классов. Применять её можно
только на коллекции, тип элементов которых помечен @Embeddable, или входит в
список стандартных встраиваемых классов (обертки примитивов, строки, даты, и т.д.).

На уровне хранения в реляционной базе, для @ElementCollection будет также создана

отдельная таблица. Технически она будет находиться в отношении one-to-many.

Но из Java кода коллекция будет выглядеть встроенной: её элементом не нужно иметь

собственные id, ими нельзя манипулировать отдельно от основной сущности.

Единственное, чем такая коллекция отличается от встроенного поля-примитива – её

можно загружать лениво (включено по умолчанию).

*SQL или NoSQL — вот в чём вопрос (No SQL или Not Only SQL) – посмотреть перед
собеседованиями эту статью и далее видео → [Link]
В мире технологий баз данных существует два основных направления: SQL и NoSQL,
реляционные и нереляционные базы данных. Различия между ними заключаются в том,
как они спроектированы, какие типы данных поддерживают, как хранят информацию.
Реляционные БД сгруппированы в таблицах, формат которых задан на этапе
проектирования хранилища.
Нереляционные БД устроены иначе. То, что в реляционной БД будет разбито на
несколько взаимосвязанных таблиц, в нереляционной может храниться в виде целостной
сущности. Нереляционные базы лучше поддаются масштабированию.
Возможности, которые стали причиной популярности таких NoSQL баз данных, как
MongoDB, CouchDB, Cassandra, HBase:
1. Хранение больших объёмов неструктурированной информации.
База данных NoSQL не накладывает ограничений на типы хранимых данных. Более того, при необходимости в
процессе работы можно добавлять новые типы данных.

2. Использование облачных вычислений и хранилищ.

Облачные хранилища — отличное решение, но они требуют, чтобы данные можно было легко распределить
между несколькими серверами для обеспечения масштабирования. Использование, для тестирования и
разработки, локального оборудования, а затем перенос системы в облако, где она и работает — это именно то,
для чего созданы NoSQL базы данных.

3. Быстрая разработка.
Если вы разрабатываете систему, используя agile-методы, применение реляционной БД способно замедлить
работу. NoSQL базы данных не нуждаются в том же объёме подготовительных действий, которые обычно нужны
для реляционных баз.

Тут показана база данных, содержащая

сведения о взаимоотношениях людей.
Вариант a — это без-схемная структура, построенная в виде
графа, характерная для NoSQL-решений.

Вариант b показывает, как те же данные можно представить

в структурированном виде, типичном для SQL.

Без-схемность означает, что два документа в структуре данных NoSQL не должны иметь
одинаковые поля и могут хранить данные разных типов.
Вот, например, массив объектов, набор полей которых не совпадает.

46
И в SQL, и в NoSQL-базах индексы служат одной и той же цели — ускорить и
оптимизировать извлечение данных. Но то, как именно они работают — различается из-
за разных архитектур баз данных и особенностей хранения информации в базе.
В то время, как SQL-индексы представлены в виде B-деревьев, которые отражают
иерархическую структуру реляционных данных, в NoSQL базах данных они указывают на
документы, или на части документов, между которыми, в основном, нет никаких
отношений.
CRM-приложения являются весьма удачным примером, в котором две системы баз
данных выступают не конкурентами, а существуют в гармонии, играя каждая свою роль
в большой архитектуре управления данными.
Признаки проектов, для которых идеально подойдут SQL-базы:
• Имеются логические требования к данным, которые могут быть определены заранее.

• Очень важна целостность данных.

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

Свойства проектов, для которых подойдёт что-то из сферы NoSQL:

• Требования к данным нечёткие, неопределённые, или развивающиеся с развитием проекта.

• Цель проекта может корректироваться со временем, при этом важна возможность немедленного начала
разработки.

• Одни из основных требований к базе данных — скорость обработки данных и масштабируемость.

Всё чаще наблюдается интеграция этих технологий друг в друга.

Например, Microsoft, Oracle и Teradata сейчас предлагают некоторые формы интеграции с
Hadoop для подключения аналитических инструментов, основанных на SQL, к миру
неструктурированных больших данных.

Дополнительно по теме NoSQL, источник: [Link]

По типам данных:

47
По способу хранения данных:
in-memory, persistent (in-place updates, snapshots, append-only log).

Теорема CAP
Это эвристическое утверждение о том, что в
любой реализации распределённых вычислений
возможно обеспечить не более двух из трёх
следующих свойств:
согласованность данных (англ. consistency) — во
всех вычислительных узлах в один момент
времени данные не противоречат друг другу;
доступность (англ. availability) — любой запрос к
распределённой системе завершается
корректным откликом, однако без гарантии, что
ответы всех узлов системы совпадают;
устойчивость к разделению (англ. partition
tolerance) — расщепление распределённой
системы на несколько изолированных секций не приводит к некорректности отклика от
каждой из секций.

Когда использовать NoSQL БД?

• Масштабируемость (линейная масштабируемость, когда путём увеличения

ресурсов кластера, мы получаем пропорциональное увеличение характеристик
кластера. Круто!)
• Быстрое прототипирование (традиционные БД требуют ресурсов на
обслуживание, использование не гибкое в меняющихся реалиях бизнес-
требований)
• Высокая доступность (разносим БД по нескольким дата-центрам)
• Кэширование
• Буферизация (льётся большой поток данных, который нужно быстро
обрабатывать, по потом сохранять результат)i
• Очередь заданий (например, на сайте есть форма регистрации пользователя и во
время сеанса не обязательно производить операцию сохранения e-mail в БД.
Логичнее поставить это в очередь выполнения заданий).
• Хранилище бинарников (например, необходимо хранить фото, тогда можно
поднять собственный кластер)
• Быстрые счётчики
• Эффективная оценка кардинальности множеств (уников) Помощь: алгоритм
HyperLogLog.
Проблема: производительность падает пропорционально количеству данных.
• CMS – система управления контентом.
• Полнотекстовый поиск.

48
Анти-паттерны: как не нужно делать:

• Ваши данные реляционны (рис. 1)

• Излишний embedding
• Недостаточный embedding
• Неверно выбранный тип данных
• Недостаточно продуманная схема данных

Заключение (в стиле кэп):

• Знайте и изучайте свою предметную область

• Следите за новостями
• Выбирайте БД не только по пресс-релизам (изучайте также недостатки)

……………………………………………………………………………………………………………….
Вопросы с реальных собесов:
Какие могут быть проблемы если ты создаешь индекс на таблицу весом 300гб в
высоконагруженном проекте?
А как можно улучшить селективность при неизменяемых данных?

ПРАКТИКА ………………………………………………………………………………………………...
Обязательные задачи SQL [Link]

Академия SQL (учебник и онлайн тренажёр) [Link]

Документация PostgreSQL [Link]
Смотрите и практикуйте также курс DMDEV =)

SQL live coding на собеседовании …………………………………………………………………….

Кандидату дается условие устно, интервьюер смотрит что он пишет.
Перейти по ссылке и написать два простых запроса.

1) Изменить тип данных столбца. В коде поменяем тип данных. Какие операции с БД
нужно совершить, чтобы не потерять существующие данные и внести изменения?

2) Рассмотрите схему базы данных на рисунке ниже и напишите соответствующие

запросы

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

Вывести список сотрудников,

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

Вывести список ID отделов,

количество сотрудников в которых
не превышает 3 человек

Вывести список сотрудников, не

имеющих назначенного
руководителя, работающего в
том-же отделе

Найти список ID отделов с

максимальной суммарной
зарплатой сотрудников

50