Как реляционная база данных хранит данные?

Как реляционная база данных хранит данные

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

1. Концептуальная основа: Таблицы и записи

Реляционная БД хранит данные в таблицах (отношениях). Каждая таблица состоит из:

Строки (Rows/Tuples): отдельные записи с данными Колонки (Columns): атрибуты данных

Пример таблицы Users:

id | name      | email              | age
---|-----------|--------------------|----- 
1  | John Doe  | john@example.com   | 30
2  | Jane Smith| jane@example.com   | 28

2. Физическое хранение: Pages и Blocks

На диске данные хранятся блоками (pages). Обычно размер page = 4KB (зависит от СУБД).

Структура page:

┌─────────────────────────────────────┐
│ Page Header                         │ 100 байт
├─────────────────────────────────────┤
│ Row 1 (полная запись)               │
│ Row 2 (полная запись)               │
│ Row 3 (полная запись)               │ Данные записей
│ ...                                 │
├─────────────────────────────────────┤
│ Row Offsets (указатели на строки)   │
├─────────────────────────────────────┤
│ Free Space (свободное место)        │
└─────────────────────────────────────┘

Page Header содержит:

• Версию page format
• Количество строк
• Размер free space
• Checksum для валидации

3. Индексы: B-Tree структура

Индексы — это упорядоченные структуры данных для быстрого поиска.

Примерно 99% БД используют B-Tree индексы. Структура:

                    Root Node
                   [10 | 20 | 30]
                   /    |    |   \
            [5,7]  [12,15] [25,28] [35,40]
              |      |       |       |
         Leaf Nodes (указывают на физические pages)

Свойства B-Tree:

• Сбалансировано — поиск O(log n)
• Листовые узлы хранят указатели на данные (row IDs)
• Все листья на одном уровне
• Хорошо работает с range запросами

Когда запрашиваешь:

SELECT * FROM users WHERE age = 25

БД:

1. Смотрит индекс по age
2. Находит листовой узел с 25
3. Берет row ID из листового узла
4. Идет в physical page и читает полную запись

4. Clustered vs Non-Clustered индексы

Clustered Index (главный индекс):

• Определяет физический порядок строк в таблице
• Один на таблицу (обычно первичный ключ)
• Листовые узлы содержат сами данные
• Быстрее всех

Clustered Index by ID:
ID: 1 → [John Doe, john@example.com, 30]
ID: 2 → [Jane Smith, jane@example.com, 28]
ID: 3 → [Bob Johnson, bob@example.com, 35]

Non-Clustered Index (вторичный индекс):

• Отдельная структура
• Много может быть
• Листовые узлы содержат указатели на clustered индекс (или row ID)
• Требует дополнительного поиска

Non-Clustered Index by Email:
email: john@example.com → ID: 1 → [actual data from clustered index]
email: jane@example.com → ID: 2 → [actual data from clustered index]

5. MVCC: Multi-Version Concurrency Control

PostgreSQL, InnoDB используют MVCC для конкурентного доступа.

Каждая запись имеет версии:

Row ID: 1
Version 1: John Doe, age 30 (valid from tx 100 to 150)
Version 2: John Doe, age 31 (valid from tx 151 to present)

Преимущества:

• Читатели не блокируют писателей
• Писатели не блокируют читателей
• Каждая транзакция видит снимок БД на момент начала

6. Write-Ahead Logging (WAL)

Для надежности используется логирование перед записью:

1. Операция (INSERT) начинается
2. Запись о операции пишется в WAL log на диск
3. Данные изменяются в памяти (buffer pool)
4. При commit WAL фиксируется
5. При crash переиграет WAL логи и восстанавливает данные

7. Buffer Pool: Кэширование в памяти

БД не читает данные каждый раз с диска. Использует кэш (Buffer Pool).

Read Query:
1. Проверить в Buffer Pool (очень быстро, наносекунды)
2. Если нет — read с диска и положить в Buffer Pool
3. Вернуть данные

Write Query:
1. Изменить страницу в Buffer Pool
2. Пометить как "dirty"
3. Позже (background) записать на диск

Размер Buffer Pool — критичный параметр для производительности:

• PostgreSQL (shared_buffers): обычно 25% от RAM
• MySQL (innodb_buffer_pool_size): обычно 75% от RAM

8. Partitioning: Распределение больших таблиц

Для огромных таблиц (миллиарды записей) разбивают на части:

Table: orders
├── orders_2025_01 (Jan 2025 данные)
├── orders_2025_02 (Feb 2025 данные)
└── orders_2025_03 (Mar 2025 данные)

Преимущества:

• Быстрее поиск (меньше pages для сканирования)
• Легче управлять (удалить старые партиции)
• Параллельная обработка возможна

9. ACID свойства и диск

Atomicity: либо всё, либо ничего (WAL обеспечивает) Consistency: данные в консистентном состоянии (ограничения обеспечивают) Isolation: транзакции не видят друг друга (MVCC обеспечивает) Durability: сохраненные данные остаются (диск обеспечивает)

10. Практические следствия

Почему SELECT * медленнее чем SELECT (few columns)?

• Нужно читать больше pages с диска
• Больше данных в buffer pool

Почему WHERE быстрее с индексом?

• Индекс B-Tree, логарифмический поиск
• Без индекса — полное сканирование всех pages

Почему UPDATE медленнее чем SELECT?

• Нужно писать в WAL
• Нужно синхронизировать с диском
• Может блокировать другие операции

Почему LIKE '%string%' медленнее чем LIKE 'string%'?

• Индекс по префиксу помогает только если начинается с нужного
• '%string%' требует полного сканирования

Monitoring хранилища

Важные метрики:

• Cache hit ratio (сколько % читаний из памяти)
• Disk I/O (сколько операций в секунду)
• Table/Index size (растут ли они)
• Unused indexes (тратим ли место впустую)

Примечание: на практике понимание этих механизмов помогает писать эффективные запросы и избегать неожиданных performance проблем.