Всем привет! Все мы здесь бьемся над одной целью — стать Java Developer-ами. Пожалуй, самый важный из этапов на пути становления — техническое собеседование. Как правило собеседующий проходится по основным темам, задавая по паре вопросов. В этой статье мы поговорим об одной из таких ключевых тем — базы данных. Рассмотрим наиболее частые вопросы и попытаемся ответить на них без глубокого погружения в материал, ведь в таком случае нам и объема книги не хватит! Итак, поехали.Собеседование разработчика: разбор вопросов по базам данных - 1

1. Что такое базы данных? На какие типы они делятся?

Что подразумевается под СУБД?

Собеседование разработчика: разбор вопросов по базам данных - 2База данных (БД) — это организованная структура, предназначенная для хранения, изменения и обработки взаимосвязанной информации преимущественно больших объемов. Иными словами, БД — это структурированное хранилище данных. Например, телефонная книга.

Типы БД

  1. Реляционная база данных — это набор данных с предопределенными связями между ними. Данные хранятся в виде набора таблиц, состоящих из столбцов и строк. В таблицах хранится информация об объектах, представленных в базе данных. В каждом столбце таблицы хранится определенный тип данных, в каждой ячейке — значение атрибута.
  2. Нереляционные системы (NoSQL) — системы, созданные для определенных моделей данных, обладающие гибкими схемами. Иначе говоря, это БД, которые хранят данные не в виде табличных схем, строк и столбцов, а в иных форматах.
Подробнее о нереляционных базах данных можно прочесть в этой статье: Руководство по NoSQL для разработчиков. Система управления базами данных (СУБД) — это совокупность программного обеспечения, при помощи которого пользователь может создавать базы данных (БД) и проводить над ними различные операции: дополнять, обновлять, удалять, выбирать, и т. д. СУБД гарантирует сохранность, целостность, безопасность хранения данных и позволяет выдавать доступ к администрированию БД. В качестве примера можно привести MySql — это СУБД, предоставляющая доступ к реляционной базе данных или MongoDB для нереляционной.

2. Что такое нормализация? Нормализованная форма? Сколько форм нормализации существует? Назовите первые три.

Нормализация — это процесс организации, структуризации данных в базе, который обеспечивает большую гибкость базы данных за счет исключения избыточности и несогласованности зависимостей. Нормальная форма — свойство таблицы, рассматриваемое в контексте нормализации, которое характерезует таблицу с точки зрения простоты и правильности построения структуры. Нормальная форма определяется как совокупность требований, которым должна удовлетворять таблица. Всего существует шесть нормальных форм, но на практике применяются не более первых трех:
  1. Первая нормальная форма:
    • Все атрибуты простые (то есть атомарные и неделимые);
    • Все данные скалярные (то есть положительные);
    • Нет повторяющихся строк (для этого для каждой строки создается первичный ключ).
  2. Вторая нормальная форма:
    • Соблюдены условия первой нормальной формы;
    • Каждый неключевой атрибут ссылается на первичный ключ.
  3. Третья нормальная форма:
    • Соблюдены условия второй нормальной группы;
    • Неключевые поля не зависят от других неключевых полей: они могут быть связаны лишь с первичным ключом.

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

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

4. Индексы

Индекс — отсортированный набор значений, связанных с таблицей или представлением с конкретной колонкой, который ускоряет получение данных. То есть это такой себе указатель: как алфавит в телефонной книге, который помогает нам при поиске по фамилии. Если использовать эту возможность правильно, можно сильно повысить производительность при работе с большими базами данных. А можно и сильно понизить. Чтобы ускорить поиск, эти ключи хранятся в виде структуры сбалансированного дерева, по которому выполняется поиск. Как правило индексы нужно вводить на поля, по которым наиболее часто ведется поиск. Задумываться о создании индекса нужно не ранее, чем когда у вас появится хотя бы 10 тысяч записей. В ином случае заметного результата вы не увидите, ибо преждевременная оптимизация — ЗЛО. И как же индекс может ударить по производительности системы, спросите вы? При вставке новых данных или удалении старых структура сбалансированного дерева будет заново пересчитываться. Собственно, чем больше данных и индексов, тем больше деревьев нужно пересчитать. Представьте ситуацию: у вас есть порядка 20 000 записей и 7 индексов на эту таблицу. То есть, при вставке данных нужно заново пересчитать 7 деревьев, в каждом из которых — по 20 000 записей. Строго говоря, использовать индексы для таблиц, в которые будут часто добавляться/удаляться данные, и вовсе не рекомендуется. Напоследок отмечу то, что индексы для столбцов, в которых часто встречается значение null, будут не так эффективны, поэтому не стоить их добавлять на такие колонки.

В чем разница между кластеризованным и некластеризованным индексами в SQL?

Кластеризованный:

  • Обеспечивает физический порядок по выбранному полю;
  • Если у таблицы есть кластеризованный индекс, она называется кластеризованной;
  • Нужно не более одного индекса на таблицу;
  • В MySQL кластеризованный индекс не задается явно пользователем, так как если вы не определяете PRIMARY KEY для своей таблицы, MySQL находит первый индекс UNIQUE, где все ключевые столбцы — NOT NULL, и InnoDB использует его в качестве кластеризованного индекса.

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

  • В одной таблице возможно до 999 некластеризованных индексов;
  • Содержит указатель на строки с реальными данными в таблице;
  • Не обеспечивает физический порядок;
  • Для некластеризованных индексов присутствуют отдельные таблицы с отсортированными данными, а именно — одна таблица для одного столбца, на котором индекс, поэтому при запросе данных, не входящих в состав данного поля, будет сначала выполняться запрос к полю в данной таблице, а только затем — дополнительный запрос к строке в изначальной таблице.
Создание некластеризованного индекса:
CREATE INDEX index_name ON table_name(column_name)

6. Что такое составной индекс?

Составной индекс — построенный с посыланием на несколько колонок одновременно. Иначе говоря, это комплексный индекс, состоящий из нескольких колонок. Такие индексы используют, когда в одном запросе фигурирует более одной колонки. Создание составного индекса:
CREATE INDEX index_name ON table_name(first_column_name, second_column_name, third_column_name)
Как правило эти индексы используются, когда данные в нескольких столбцах логически взаимосвязаны.

7. Что такое покрывающий индекс? Уникальный индекс?

Покрывающий индекс — это индекс, которого вполне достаточно для ответа на запрос без обращения к самой таблице. По этому индексу можно достать всю строку данных, но по факту это просто не нужно. Благодаря тому, что не нужно ходить непосредственно в исходную таблицу, а ответить можно, используя только индекс, покрывающие индексы немного быстрее в использовании. При этом не стоит забывать, что чем больше колонок, тем более громоздким и медленным становится сам индекс. Так что злоупотреблять этим не стоит. Выше мы говорили о кластеризованных и некластеризованных индексах, которые могут быть уникальными. Это означает, что никакие две поля не имеют одинаковое значение для ключа индекса. В ином же случае индекс не будет уникальным, ведь несколько строк могут содержать одно и то же значение. Пример создания уникального некластеризованного индекса:
CREATE UNIQUE INDEX index_name ON table_name(column_name)

8. Что такое первичный ключ

Первичный ключ (Primary key) — поле в таблице для идентификации каждой строки в таблице базы данных. В таблице может быть только одно такое поле, и все значения должны быть уникальными. Ничего не напомнило?Собеседование разработчика: разбор вопросов по базам данных - 3Ведь первичный ключ — ничто иное, как уникальный, кластеризованный индекс. Как правило, первичные ключи создаются, при заведении таблицы:
CREATE TABLE table_name(
column_name int PRIMARY KEY,..)
К данной колонке автоматически добавится ограничение — NOT NULL. Также можно задать ключ и для уже созданной таблицы:
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
Если первичный ключ добавляется способом, описанным выше, то значения полей, задаваемых как первичный ключ (column_name), проверяются на отсутствие нулевых (также будет добавлено ограничение — NOT NULL).

Что такое внешний ключ?

Внешний ключ (Foreign key) — это свойство, создаваемое для того, чтобы обеспечить связь между таблицами. Как правило, внешний ключ устанавливается для столбцов в подчиненной таблице и указывает на один из столбцов из главной таблицы. Может задаваться как при создании таблицы:
CREATE TABLE table_name{
column_name int,..
FOREIGN KEY(column_name) REFERENCES another_table_name(another_table_column_name) }
Так и после создания таблицы:
ALTER TABLE table_name
ADD FOREIGN KEY(column_name) REFERENCES another_table_name(another_table_column_name));
Можно задать поведение внешнего ключа при манипуляциях с полем, на которое он ссылается. Манипуляции могут быть вида ON DELETE и ON UPDATE. Возможные варианты поведения:
  • CASCADE — с этим свойством будут автоматически удалены или изменены строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице;
  • SET NULL — при данном свойстве, когда происходит удаление или обновление связанной строки из главной таблицы, будет устанавливаться значение NULL для столбца внешнего ключа;
  • NO ACTION — отклоняет попытки удалить или изменить строки в главной таблице, если присутствуют связанные строки в зависимой таблице;
  • RESTRICT — эквивалентно NO ACTION;
  • SET DEFAULT — при данном свойстве, когда происходит удаление или обновление связанной строки из главной таблицы, будет устанавливаться значение по умолчанию (если оно есть) для столбца внешнего ключа.
Пример использования:
CREATE TABLE table_name{
column_name int,..
FOREIGN KEY(column_name) REFERENCES another_table_name(another_table_column_name) ON UPDATE CASCADE ON DELETE CASCADE }
Если не будет явно задано поведение для ON DELETE и ON UPDATE, то будет устанавливаться поведение RESTRICT.

10. Типы соединений между таблицами (Join)

Связь между таблицами обеспечивается на основе общих данных (полей). Это происходит с помощью оператора JOIN — операции, которая сопоставляет строки из одной таблицы со строками в другой. Сопоставление выполняется так, чтобы столбцы обеих таблиц располагались рядом, хотя их можно получить из отдельных таблиц. И если у нас есть общие поля для трех таблиц, мы можем вывести их данные в виде одной общей таблицы. Тем не менее, стоит учитывать, что чем меньше таблиц объединяется, тем быстрее будет выполняться запрос. Итак, виды JOIN:
  • INNER JOIN — соединение, которое показывает только те данные из первой таблицы, которым соответствуют некоторые данные из второй таблицы. Остальные — опускаются.Собеседование разработчика: разбор вопросов по базам данных - 4
  • LEFT JOIN — соединение, которое показывает все данные из первой таблицы и соответствующие данные из второй, если они есть. Если же соответствующих данных нет, поля для данных из второй таблицы будут пустыми.Собеседование разработчика: разбор вопросов по базам данных - 5
  • RIGHT JOIN — соединение, которое показывает все данные из второй таблицы и соответствующие данные из первой, если они есть. Если же соответствующих данных нет, поля для данных из первой таблицы будут пустыми.Собеседование разработчика: разбор вопросов по базам данных - 6
  • FULL JOIN — соединение, которое показывает все данные из первой и второй таблицы. Если связанных данных в другой таблице нет, поля для этих данных будут пустыми.Собеседование разработчика: разбор вопросов по базам данных - 7
  • CROSS JOIN — перекрестное соединение, при котором каждая строка первой таблицы соединяется с каждой строкой второй таблицы (каждая с каждой). То есть, если в двух таблицах по 3 строки, после данного соединения мы получим результат из 9 строк.Собеседование разработчика: разбор вопросов по базам данных - 8
Пример Join(inner):
SELECT *
FROM first_table
INNER JOIN second_table ON first_table.some_column = second_table.some_column

11. Что такое свойство ACID в базе данных?

A — Атомарность, гарантирует, что никакая транзакция не фиксируется в системе частично. Выполняются либо все её подоперации, либо ни одной. К примеру, перевод денег в банке на другой счет — это две операции:
  1. Перевести деньги на счет банка.
  2. Перевести деньги со счета банка на конкретный счет.
Но может всякое случиться. Например, поступят в банк, а дальше выпадет какая-то ошибка и вторая операция не выполнится. Или наоборот: выполнится только вторая операция. Поэтому эти действия осуществляются в рамках одной транзакции, и в результате либо всё, либо ничего. С — Согласованность: каждая успешная транзакция всегда фиксирует только разрешаемые результаты. Это гарантирует, что все ограничения будут соблюдены (например, NOT NULL), иначе — транзакция откатится. И — изолированность: во время выполнения транзакции параллельные транзакции не должны оказывать влияние на ее результат. Это дает нам возможность скрывать от всех не конечные состояния данных. Собственно, поэтому неуспешные транзакции не могут ничего сломать. Немного ниже мы ознакомимся с уровнями изолированности транзакций. Д — долговечность: если транзакция выполнена, то можно быть уверенным, что внесенные ею изменения не отменятся из-за какого-либо сбоя.

12. Уровни изолированности транзакций

Каждый уровень изолированности разрешает/запрещает определенные действия (возможности):
  • фантомное чтение — в рамках одной транзакции один и тот же запрос данных дает разные результаты, что происходит из-за добавления данных другой (параллельной) транзакцией.
  • неповторяющееся чтение — в рамках одной транзакции один и тот же запрос данных дает разные результаты, что происходит из-за изменения или удаления данных другой (параллельной) транзакцией.
  • «грязное» чтение — чтение данных, добавленных или измененных транзакцией, которая впоследствии не откатится;
  • потерянное обновление — при одновременном изменении одного блока данных разными транзакциями теряются все изменения, кроме последнего (похоже на “состояние гонки” в многопоточности).
Для удобства, рассмотрим уровни изоляций и их возможности в таблице:
Уровни изоляций Фантомное чтение Неповторяющееся чтение “грязное” чтение потерянное обновление
SERIALIZABLE + + + +
REPEATABLE_READ - + + +
READ_COMMITTED - - + +
READ_UNCOMMITTED - - - +

13. Что такое SQL-инъекция?

SQL-инъекция — один из способов взлома сайта, суть которого — внедрение в данные через GET, POST запросы или Cookie некоторого SQL кода. Если веб-сайт выполняет такие инъекции, можно получить доступ к БД и взломать приложение. Например, мы знаем имя некоторой переменной. Допустим, column_name с типом boolean. Если система восприимчива к инъекциям, можно добавить OR column_name=true и после этого писать всё, что нам нужно от БД. OR создаст условие ИЛИ, и наше выражение после него будет всегда true, что и пропустит нас дальше. Атака на сайт вроде инъекции SQL возможна из-за неправильной обработки входящих данных, используемых в SQL-запросах. При соединении с базой данных при помощи JDBC вы используете различные Statements. Для повышения безопасности необходимо использовать PreparedStatement вместо обычного Statement, так как при использовании Statement строки запроса и значений просто складываются, делая возможными инъекции. В свою очередь, в PreparedStatement есть конкретный шаблон запроса, и данные в него вставляются с отражением кавычек. В итоге SQL-инъекции будут восприниматься только как строковое представление какого-то поля. Для защиты от SQL-инъекций можно использовать проверку на основе регулярных выражений (подробнее о регулярных выражениях можно почитать в этой статье).Собеседование разработчика: разбор вопросов по базам данных - 9Ещё вариант — установить ограничение на количество символов входящих параметров: например, если вам должно приходить число не более 9999, подойдет ограничение в четыре входящих символа. Оно снизит риск взлома с помощью SQL-инъекций.Собеседование разработчика: разбор вопросов по базам данных - 10Подробнее о безопасности в Java можно узнать из статьи “Безопасность в Java: best practices”.

14. Что такое хранимые процедуры? Хранимые функции? Триггер?

Хранимые процедуры в SQL — сущность в БД, представляющая собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Словом, это аналог методов в Java. Хранимые процедуры могут выполнять действия над данными как обычные запросы, так и некоторые действия, недоступные для обычных запросов. Процедура является сущностью SQL, которую создают один раз, а затем вызывают, передавая аргументы. Плюс этого подхода состоит в том, что данные инструкции можно переиспользовать не единожды. Хранимые процедуры увеличивают производительность, расширяют возможности программирования и поддерживают функции безопасности данных. Рассмотрим создание процедуры:
CREATE PROCEDURE procedure_name (first_param some_type, second_param some_type..)
 begin
……...
 end
Вызов процедуры:
CALL procedure_name (first_param, second_param…..);
Хранимая функция — это разновидность хранимой процедуры. Отличие функции состоит в том, что она всегда возвращает только единичное значение, а процедура — набор значений. Хранимые процедуры нельзя смешивать с обычным SQL, в то время как с сохраненной функцией это можно — и это ее преимущество. С другой стороны, у хранимых функций гораздо больше ограничений, нежели у процедур. Создание хранимой функции:
CREATE FUNCTION function_name (first_param, second_param…..)
RETURNS some_type
 begin
……...
RETURN some_value;
end
Вызов хранимой функции:
SELECT function_name(first_param, second_param…..);
Триггер — еще одна разновидность хранимой процедуры, которая вызывается не непосредственно пользователем, а ее активация происходит при модификации данных. То есть эта процедура активируется при выполнении некоторых условий, как, например, INSERT или DELETE, или UPDATE данных в определенном столбце данной таблицы. Момент запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до связанного события) или AFTER (после события).
CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT
 begin
……...
 end

15. Практика

Как бы то ни было, самым частым SQL-вопросом на собеседовании будет практика — решение задач. Пытаться угадать, какие именно вам попадутся задачи, бессмысленно, ведь все зависит от изощренности фантазии человека напротив. Поэтому единственным рабочим вариантом будет набивание руки на SQL-запросах разной сложности. В качестве ресурса для практики на различных задачах может послужить sql-ex.ru. Уже после первых двадцати выполненных тасков вашему собеседнику будет довольно сложно напугать вас какой либо SQL-задачей.Собеседование разработчика: разбор вопросов по базам данных - 11На этом сегодня все: надеюсь, после прочтения данной статьи вопросы по базам данных не вызовут никаких сложностей и проблем. Спасибо за внимание и до новых встреч!