Співбесіда розробника: розбір питань щодо баз даних

Всім привіт! Всі ми тут б'ємося над однією метою стати Java Developer -ами. Мабуть, найважливіший етап на шляху становлення — технічна співбесіда. Як правило, співбесідник проходить по основним темам, ставлячи з кількох питань. У цій статті ми поговоримо про одну з таких ключових тем - бази даних . Розглянемо найчастіші питання та спробуємо відповісти на них без глибокого занурення у матеріал, адже у такому разі нам і обсягу книги не вистачить! Тож поїхали.

1. Що таке бази даних? На які типи вони поділяються?

Що мається на увазі під СУБД?

База даних (БД) - це організована структура, призначена для зберігання, зміни та обробки взаємопов'язаної інформації переважно більших обсягів. Іншими словами, БД – це структуроване сховище даних. Наприклад телефонна книга.

Типи БД

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) — поле таблиці для ідентифікації кожного рядка в таблиці бази даних. У таблиці може бути тільки одне таке поле, і всі значення мають бути унікальними. Нічого не нагадало? Адже первинний ключ — ніщо інше, як унікальний кластеризований індекс. Як правило, первинні ключі створюються при закладі таблиці:

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— з'єднання, яке показує лише дані з першої таблиці, яким відповідають деякі дані з другої таблиці. Інші - опускаються.
• LEFT JOIN— з'єднання, яке показує всі дані з першої таблиці та відповідні дані з другої, якщо вони є. Якщо відповідних даних немає, поля для даних з другої таблиці будуть порожніми.
• RIGHT JOIN— з'єднання, яке показує всі дані з другої таблиці та відповідні дані з першої, якщо вони є. Якщо відповідних даних немає, поля для даних з першої таблиці будуть порожніми.
• FULL JOIN- З'єднання, яке показує всі дані з першої та другої таблиці. Якщо пов'язаних даних в іншій таблиці немає, поля цих даних будуть порожніми.
• CROSS JOIN- перехресне з'єднання, при якому кожен рядок першої таблиці з'єднується з кожним рядком другої таблиці (кожний з кожної). Тобто, якщо у двох таблицях по 3 рядки, після цього з'єднання ми отримаємо результат із 9 рядків.

Приклад 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. Рівні ізольованості транзакцій

Кожен рівень ізольованості дозволяє/забороняє певні дії (можливості):

• фантомне читання — у межах однієї транзакції той самий запит даних дає різні результати, що відбувається через додавання даних іншою (паралельною) транзакцією.
• неповторне читання — в рамках однієї транзакції той самий запит даних дає різні результати, що відбувається через зміну або видалення даних іншою (паралельною) транзакцією.
• "брудне" читання - читання даних, доданих або змінених транзакцією, яка згодом не відкотиться;
• втрачене оновлення - при одночасному зміні одного блоку даних різними транзакціями втрачаються всі зміни, крім останнього (схоже на стан гонки в багатопоточності).

Для зручності розглянемо рівні ізоляцій та їх можливості в таблиці:

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-ін'єкцій можна використовувати перевірку на основі регулярних виразів (докладніше про регулярні вирази можна почитати у цій статті ). Ще варіант — встановити обмеження на кількість символів вхідних параметрів: наприклад, якщо вам має приходити число не більше 9999, підійде обмеження чотирьох вхідних символів. Воно зменшить ризик злому за допомогою SQL-ін'єкцій.Докладніше про безпеку 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-завданням. На цьому сьогодні все: сподіваюся, після прочитання цієї статті питання щодо баз даних не викличуть жодних складнощів та проблем. Дякуємо за увагу і до нових зустрічей!