MySQL database engine is a software responsible for storing data in the database. MySQL has pluggable storage engines that allow you to set a storage engine when creating a table in the database:

CREATE TABLE Countries (
    ID INT,
    Name VARCHAR(50),
    PRIMARY KEY (ID)
) ENGINE=InnoDB;

Внутрішні елементи бази даних

У цьому уроці ми заглянемо «під капот» MySQL і розберемося як організована його архітектура.

Архітектура MySQL

MySQL працює за клієнт-серверною моделлю. Це така модель, у якій клієнт (це ти, користувач) взаємодіє з сервером (де зберігається база даних) за допомогою мережевих сервісів. Ця модель розділяє інтерфейс користувача і зберігання даних, що покращує безпеку та ефективність. Архітектура MySQL містить три основні рівні ⬇️

Клієнтський рівень (застосунків)

На цьому рівні відбуваються:

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

Серверний рівень

Це «мозок» системи MySQL, який керує всіма логічними функціями. Основні компоненти цього рівня мають наступні функції:

обробка потоків — серверний рівень керує з'єднаннями клієнтів і виконує запити;
парсер — серверний рівень аналізує та розбиває SQL-запити на зрозумілі сегменти (токени);
оптимізатор — серверний рівень оптимізує запити для ефективного виконання;
кеш запитів — серверний рівень зберігає результати запитів для швидшого доступу, якщо той самий запит буде зроблений знову.

Рівень сховища (зберігання)

Це «склад», де зберігаються усі дані. MySQL особливий тим, що ти можеш обрати різні database engines (імплементації рівня зберігання) у залежності від того, що потрібно — швидкість, надійність, підтримка транзакцій тощо.

Приклад

Розглянемо архітектуру MySQL на прикладі запиту SELECT * FROM users WHERE age > 30. Коли ти відправляєш цей запит через клієнтський інтерфейс, наприклад, через MySQL Workbench або командний рядок, то він потрапляє на клієнтський рівень. Клієнт встановлює з’єднання з сервером, автентифікується та, якщо все в порядку, передає запит на серверний рівень.

Серверний рівень приймає запит та аналізує його. Сервер також перевіряє, чи вже є результат цього запиту у кеші. Якщо так, то він може відразу повернути результат, минаючи інші етапи. Якщо результату у кеші немає, то парсер розбиває SQL-запит на логічні частини (токени), перевіряє синтаксис і переконується, що запит коректний. Після парсингу оптимізатор аналізує різні способи виконання запиту та обирає найбільш ефективний. Наприклад, він може вирішити, які індекси використовувати для швидкого доступу до даних.

Нарешті, запит потрапляє на рівень сховища, де відбувається фактичний доступ до таблиці users і вибірка тих записів, де вік користувачів більше 30 років. Залежно від використовуваного двигуна зберігання, наприклад, InnoDB (розглянемо нижче), дані читаються та обробляються у спосіб, оптимальний для цього конкретного двигуна. Потім результат запиту відправляються назад через серверний рівень до клієнта, де він відображається у користувальницькому інтерфейсі.

Двигун SQL

Створення, зчитування, оновлення та видалення (операції CRUD) інформації із бази даних в MySQL — це задача компонента, який називається database engine. Database engine — це програма, яка відповідає за зберігання та обробку даних у сервері бази даних. Іноді database engine ще називають storage engine.

Двигуни бази даних можна розділити на два типи ⬇️

Транзакційні

Транзакційні двигуни підтримують виконання транзакцій, а також забезпечують надійність та стабільність операцій з даними. Прикладом є InnoDB, який здатний виконувати або повністю всю групу операцій, або жодну з них, якщо сталася помилка. Це забезпечує надійність при роботі з даними. За допомогою підтримки ACID він також забезпечує цілісність даних, тобто знижує ризик втрати даних на випадок збоїв. Але така робота вимагає більше ресурсів, що може призвести до зниження швидкості обробки даних у порівнянні з нетранзакційними двигунами.

💡 Транзакційні двигуни гарно підійдуть для тих проєктів, для яких важлива цілісність і узгодженість даних.

Нетранзакційні

Нетранзакційні двигуни не підтримують виконання транзакцій, але є простішими у використанні та швидшими в роботі. Прикладами є:

MyISAM — швидкий та простий у використанні двигун, який не підтримує транзакції, тому операції запису виконуються без гарантій цілісності даних при збоях. Він підійде для вебсайтів, блогів або систем, де переважно читають дані, а не оновлюють їх.
Memory — використовує оперативну пам'ять для зберігання даних, що забезпечує високу швидкість доступу до них. Але у разі перезапуску системи дані будуть втрачені, тобто для довготривалого зберігання даних він не підійде.
CSV — зберігає дані у форматі текстових файлів CSV, що полегшує обмін даними з іншими програмами. Також у ньому відсутня можливість індексації, що може уповільнити пошук та обробку даних.
Blackhole — приймає дані, але не зберігає їх. Є гарним для тестування систем без ризику втрати реальних даних. Але для зберігання даних він непридатний.
Federated — з'єднує декілька MySQL-серверів в одну логічну базу даних, але не зберігає дані локально. Є корисним для створення розподілених баз даних із підвищеною доступністю. Але при виборі потрібно враховувати складність управління та налаштування, що може ускладнити подальший процес підтримки.

Як обрати двигун?

При виборі двигуна варто поставити собі наступні запитання:

Яка ціль проєкту? Наприклад, це вебдодаток, аналітична система, фінансова система тощо.
Які основні операції будуть виконуватися? Багато читань, записів, змішаних операцій?
Чи важлива цілісність та надійність даних? Які вимоги до безпеки даних?
Чи потрібна підтримка транзакцій?
Яка очікувана навантаженість на базу даних?
Чи важливіша швидкість читання за швидкість запису?
Чи потрібна підтримка специфічних функцій, як от повнотекстовий пошук чи реплікація?
Чи потрібні спеціальні можливості, наприклад, для розподілених систем або тимчасового зберігання даних?