SQL Server Archiтектура (пояснення)

MS SQL Server має клієнт-серверну архітектуру. Процес MS SQL Server починається з надсилання запиту клієнтською програмою. SQL Server приймає, обробляє та відповідає на запит, надаючи оброблені дані. Давайте детально обговоримо всю архітектуру, показану нижче:

Як показано на діаграмі нижче, SQL Server складається з трьох основних компонентів. Archiтекстура:

1. Рівень протоколу
2. Реляційний механізм
3. Система зберігання

Рівень протоколу – SNI

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

Спільна пам'ять

Розглянемо сценарій ранкової розмови. Том і його мама знаходяться в одному логічному місці, вдома. Том просить каву, а мама подає її безпосередньо. Аналогічно, SQL Server забезпечує протокол спільної пам'яті, коли клієнт і сервер працюють на одній машині. Обидва взаємодіють через спільну пам'ять без будь-яких мережевих витрат.

Аналогія: Том зіставляється з Клієнтом, Мама зіставляється з SQL Server, Дім зіставляється з Машиною, а вербальна комунікація зіставляється з протоколом спільної пам'яті.

Примітки щодо конфігурації: In Студія управління SQL, параметр «Ім’я сервера» для локального підключення може мати формат «.», «localhost», «127.0.0.1» або «Machine\Instance».

TCP / IP

Тепер уявіть, що Том хоче кави з кав'ярні, розташованої за 10 км. Том вдома, а кав'ярня знаходиться на жвавому ринку. Вони спілкуються через стільникову мережу. Аналогічно, SQL Server надає… TCP / IP-протокол коли клієнт і SQL Server знаходяться на окремих машинах, підключених через мережу.

Аналогія: Том зіставляється з Клієнтом, кав'ярня – з SQL Server, дім та торговий майданчик – з віддаленими розташуваннями, а стільникова мережа – з протоколом TCP/IP.

Примітки щодо конфігурації: У SQL Management Studio параметр «Ім’я сервера» для з’єднання TCP/IP має бути «Машина\Екземпляр сервера». SQL Server за замовчуванням використовує порт 1433 для з’єднань TCP/IP.

Названі труби

Зрештою, Том хоче зеленого чаю від своєї сусідки Сьєрри. Вони знаходяться в одному фізичному місці, є сусідами та спілкуються через внутрішню мережу. Аналогічно, SQL Server забезпечує протокол іменованого каналу, коли клієнт і сервер підключені через локальну мережу (LAN).

Аналогія: Tom зіставляється з Client, Sierra – з SQL Server, being neighbors – з LAN, а intra network – з протоколом Named Pipe.

Примітки щодо конфігурації: Іменовані канали вимкнено за замовчуванням і їх потрібно ввімкнути через диспетчер конфігурації SQL.

Що таке TDS?

Тепер, коли три типи клієнт-серверної архітектури зрозумілі, ось розгляд TDS:

• TDS означає потік табличних даних.
• Усі три протоколи використовують TDS-пакети.
• TDS інкапсульовано в мережеві пакети, що дозволяє передавати дані з клієнтської машини на серверну.
• TDS був спочатку розроблений Sybase, а зараз належить Microsoft.

У наступній таблиці порівнюються три протоколи підключення SQL Server:

особливість	Спільна пам'ять	TCP / IP	Названі труби
Область дії мережі	Та сама машина	Віддалений (WAN/Інтернет)	Тільки локальна мережа
Порт за замовчуванням	N / A	1433	445
продуктивність	Найшвидший (без накладних витрат на мережу)	Добре (оптимізовано для WAN)	Добре (оптимізовано для локальної мережі)
Увімкнено за замовчуванням	Так	Так	Немає
Найкращий варіант використання	Локальна розробка та тестування	Віддалений доступ до виробництва	Надійні середовища локальної мережі

Оскільки протокольний рівень обробляє мережевий зв'язок, наступним кроком в архітектурі SQL Server є обробка самого запиту. Саме тут реляційний механізм бере на себе його роботу.

Реляційний механізм

Реляційний механізм також відомий як процесор запитів. Він містить компоненти SQL Server, які визначають, що має робити запит і як його можна виконати найефективніше. Він відповідає за виконання запитів користувачів шляхом запиту даних від механізму сховища та обробки повернутих результатів.

Як показано на архітектурній схемі, реляційний механізм складається з трьох основних компонентів:

Парсер CMD

Дані, отримані від протокольного рівня, передаються до реляційного механізму. CMD-парсер – це перший компонент, який отримує дані запиту. Його основне завдання – перевірити запит на наявність синтаксичних та семантичних помилок, а потім створити дерево запитів.

Синтаксична перевірка: Як і будь-яка інша мова програмування, SQL Server має попередньо визначений набір ключових слів та граматичних правил. SELECT, INSERT, UPDATE та багато інших належать до попередньо визначеного списку ключових слів. CMD Parser перевіряє, чи введені дані відповідають цим правилам. Якщо введені користувачем дані відхиляються від очікуваного синтаксису, парсер повертає помилку.

приклад: Уявіть собі росіянина, який заходить до японського ресторану та замовляє замовлення російською мовою. Офіціант розуміє лише японську мову та не може обробити замовлення. Аналогічно, якщо користувач вводить «SELECR» замість «SELECT», CMD Parser повертає помилку, оскільки не розпізнає ключове слово.

Семантична перевірка: Це виконує Нормалізатор. Він перевіряє, чи дійсно існують назви стовпців, назви таблиць та інші об'єкти, що запитуються, у схемі. Якщо вони існують, Нормалізатор прив'язує їх до запиту. Цей процес також відомий як зв'язування (Binding). Коли запити користувача містять VIEW (ВИД), Нормалізатор замінює його внутрішньо збереженим визначенням видання (view).

приклад: Робота SELECT * from USER_ID призведе до того, що парсер викличе помилку під час семантичної перевірки, якщо таблиця USER_ID не існує в базі даних.

Створити дерево запитів: Цей крок генерує різні дерева виконання, що представляють різні способи виконання запиту. Усі дерева дають однаковий бажаний результат.

Оптимізатор

Оптимізатор створює план виконання для запиту користувача. Цей план визначає, як буде виконано запит. Не всі запити оптимізовані. Оптимізація застосовується до команд DML (мова модифікації даних), таких як SELECT, INSERT, DELETE та UPDATE. Команди DDL, такі як CREATE та ALTER, не оптимізуються, а компілюються у внутрішню форму.

Вартість запиту розраховується на основі таких факторів, як використання процесора, використання пам'яті та потреби вводу/виводу. Роль оптимізатора полягає в тому, щоб знайти найдешевший економічно ефективний план виконання, а не обов'язково найкращий.

приклад: Уявіть, що ви хочете відкрити банківський рахунок онлайн. В одному банку обробка займає максимум 2 дні. У вас також є список із 20 інших банків, які можуть зайняти менше часу, а можуть і ні. Пошук по всіх 20 банках може не знайти швидшого варіанту, а сам пошук вимагає часу. Було б краще обрати перший банк. Аналогічно, оптимізатор SQL використовує вичерпні та евристичні алгоритми для мінімізації часу виконання запиту.

Оптимізатор виконує пошук у три фази:

Фаза 0: Пошук тривіального плану

Це етап передоптимізації. Для деяких запитів існує лише один практичний план, відомий як тривіальний план. Немає потреби шукати далі, оскільки будь-який додатковий пошук виявив би той самий план виконання за додаткову плату.

Фаза 1: Пошук планів обробки транзакцій

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

Фаза 2: Паралельна обробка та оптимізація

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

Виконавець запитів

Виконавець запитів викликає метод доступу (Access Method) у механізмі сховища (Storage Engine). Він надає план виконання, що містить логіку отримання даних, необхідну для виконання. Після отримання даних від механізму сховища результат публікується на рівні протоколу (Protocol Layer) та надсилається кінцевому користувачеві.

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

Система зберігання

Механізм зберігання даних відповідає за зберігання даних у системі зберігання даних, такій як диск або мережа зберігання даних (SAN), та їх отримання за потреби. Перш ніж розглядати компоненти механізму зберігання даних, важливо зрозуміти, як фізично зберігаються дані.

Файли даних та екстенти

Файли даних фізично зберігають дані у вигляді сторінок даних, кожна з яких має розмір 8 КБ. Це найменша одиниця зберігання в SQL ServerСторінки даних логічно згруповані в екстенти. Жодному об'єкту не призначається окрема сторінка безпосередньо; натомість обслуговування здійснюється через екстенти. Кожна сторінка має заголовок сторінки (96 байт), який містить метадані, такі як тип сторінки, номер сторінки, використаний простір, вільний простір та вказівники на наступну та попередню сторінки.

Типи файлів

Основний файл: Кожна база даних містить один основний файл. У ньому зберігаються всі важливі дані, пов'язані з таблицями, представленнями, тригерами та іншими об'єктами. Розширення зазвичай .mdf, але може мати будь-яке розширення.

Вторинний файл: База даних може містити кілька вторинних файлів, а може й не містити. Вони є необов'язковими та містять дані, специфічні для користувача. Розширення зазвичай .ndf, але може мати будь-яке розширення.

Файл журналу: Також відомі як журнали попередньої запису. Розширення .ldf. Файли журналів використовуються для керування транзакціями, відновлення після небажаних випадків та виконання відкату незафіксованих транзакцій.

Механізм сховища має три основні компоненти. Кожен з них відіграє певну роль в управлінні доступом до даних та їх цілісністю.

Метод доступу

Метод доступу діє як інтерфейс між виконавцем запитів та Buffer Журнали менеджера або транзакцій. Він не виконує сам процес виконання, але визначає тип запиту:

• Якщо запит є Оператор SELECT (DML), його передають до Buffer Менеджеру для подальшої обробки.
• Якщо запит є Оператор, що не є SELECT (DDL та DML), він передається до менеджера транзакцій. Це здебільшого включає оператори UPDATE, INSERT та DELETE.

Buffer менеджер

Команда Buffer Менеджер керує основними функціями кешу планів, парсингу даних та обробки некоректних сторінок.

Кеш плану

Існуючий план запитів: Команда Buffer Менеджер перевіряє, чи існує план виконання у збереженому кеші планів. Якщо так, кешований план запиту та пов'язаний з ним кеш даних використовуються безпосередньо.

План кешування для першого використання: Якщо план виконання першого запиту є складним, він зберігається в кеші планів. Це забезпечує швидшу доступність наступного разу, коли SQL Server отримає той самий запит.

Розбір даних: Buffer Кеш та сховище даних

Команда Buffer Менеджер надає доступ до необхідних даних. Можливі два підходи залежно від того, чи є дані в кеші:

Buffer Кеш – М’який парсинг

Команда Buffer Менеджер шукає дані в Buffer Кеш. Якщо дані присутні, виконавець запитів використовує їх безпосередньо. Це покращує продуктивність, оскільки отримання даних з кешу вимагає менше операцій вводу-виводу порівняно з отриманням з дискового сховища.

Зберігання даних – Апаратний парсинг

Якщо дані відсутні в Buffer Кеш, потрібні дані шукаються у сховищі даних на диску. Потім дані також зберігаються в кеші даних для подальшого використання.

Менеджер транзакцій

Менеджер транзакцій викликається, коли метод доступу визначає, що запит не є оператором SELECT. Він забезпечує узгодженість та довговічність даних за допомогою кількох підкомпонентів:

Менеджер журналів

Менеджер журналів зберігає track усіх оновлень, виконаних у системі, через журнали, що зберігаються в журналах транзакцій. Кожен запис журналу містить порядковий номер журналу разом з ідентифікатором транзакції та записом модифікації даних. Цей механізм tracks зафіксованих та відкатованих транзакцій.

Менеджер блокувань

Під час транзакції пов'язані дані у сховищі переходять у заблокований стан. Менеджер блокувань керує цим процесом, забезпечуючи узгодженість та ізоляцію даних. Ці властивості також відомі як ACID (Atomicity, Consistency, Isolation, Durability).

Процес виконання

Процес виконання відбувається за такими кроками:

1. Менеджер журналів розпочинає ведення журналу, а Менеджер блокувань блокує пов'язані дані.
2. Копія даних зберігається в Buffer Кеш.
3. Копія даних, що підлягають оновленню, зберігається в журналі. Buffer, і всі події оновлюють дані в розділі Дані Buffer.
4. Сторінки, які зберігають змінені дані, відомі як Брудні сторінки.

Контрольні точки та ведення журналу попереднього запису

Процес контрольної точки виконується приблизно раз на хвилину та позначає всі "несправні" сторінки для запису на диск. Однак, сторінка спочатку переміщується на сторінку даних файлу журналу з... Buffer Журнал. Цей механізм відомий як ведення журналу попереднього запису. Брудні сторінки залишаються в кеші навіть після запису на диск.

Лінивий Writer

Коли SQL Server виявляє велике навантаження і для нових транзакцій потрібна буферна пам'ять, він звільняє брудні сторінки з кешу. Лінивий Writer працює за алгоритмом LRU (найменше використані) для очищення сторінок з буферного пулу на диск.

Як SQL Server обробляє запит від початку до кінця

Розуміння кожного рівня окремо є цінним, але спостереження за тим, як вони працюють разом, прояснює повну картину. Коли клієнтська програма надсилає SQL-запит, відбувається така послідовність:

Команда Рівень протоколу отримує запит через спільну пам'ять, TCP/IP або іменовані канали та обгортає його в TDS-пакет. Реляційний механізм потім бере на себе: CMD Parser перевіряє синтаксис і семантику, Optimizer генерує найдешевший план виконання, а Query Executor починає пошук даних.

Виконавець запитів викликає Двигун зберігання даних Метод доступу, який спрямовує запити SELECT до Buffer Менеджер та запити на модифікацію до Менеджера транзакцій. Buffer Менеджер перевіряє кеш планів та Buffer Спочатку кеш (м'який розбір). Якщо дані не кешуються, виконується читання з диска (апаратний розбір). Для операцій запису менеджер транзакцій координує роботу менеджера журналів, менеджера блокувань та процесу контрольних точок, щоб забезпечити відповідність ACID.

Після того, як механізм сховища повертає запитувані дані, реляційний механізм форматує результуючий набір, а рівень протоколу передає його назад до клієнтської програми через той самий протокол TDS.

Як вибрати правильний протокол для підключень до SQL Server

Вибір правильного протоколу залежить від фізичного зв'язку між клієнтом і сервером, а також від вимог до продуктивності.

Використання спільної пам'яті коли клієнтська програма працює на тому ж комп'ютері, що й SQL Server. Це найшвидший варіант, оскільки він усуває всі мережеві накладні витрати. Він ідеально підходить для локальної розробки, тестування та розгортання на одному комп'ютері.

Використовувати TCP/IP коли клієнт і сервер знаходяться на різних машинах, підключених через WAN або Інтернет. Це найпоширеніший протокол у виробничих середовищах. SQL Server за замовчуванням прослуховує порт 1433, і цей протокол підтримує зашифровані з'єднання через TLS.

Використання іменованих каналів коли клієнт і сервер знаходяться в одній довіреній локальній мережі, а продуктивність у внутрішніх мережах є пріоритетом. Іменовані канали вимкнено за замовчуванням і їх потрібно ввімкнути через диспетчер конфігурації SQL Server. Вони менш поширені в сучасних розгортаннях, але залишаються корисними для застарілих інтрамережевих програм.

Поширені запитання