Создание эффективных таблиц

  • 8 мин

Эффективная структура таблиц формирует основу любой базы данных. Таблицы структурируют данные и определяют, насколько эффективно ваши запросы получают доступ к данным и изменяют их.

Проектирование и создание таблиц

Таблицы представляют собой основные стандартные блоки реляционных баз данных, упорядочивая данные в строки и столбцы, представляющие сущности и их атрибуты. В реляционных системах таблицы определяют структуру для хранения транзакционных данных, принудительно применяют связи через внешние ключи и предоставляют основу для запросов и отчетов.

Для многомерной аналитики таблицы служат таблицами фактов , которые хранят измеримые события и таблицы измерений , предоставляющие контекст для анализа. Решения по проектированию при создании таблиц — типы данных, размер столбцов, ограничения и связи — непосредственно влияют на эффективность хранения, производительность запросов, целостность данных и масштабируемость как в операционных, так и в аналитических рабочих нагрузках.

Выбор соответствующих типов данных

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

Выберите правильные типы данных при разработке начальной схемы, так как это самый простой момент, чтобы получить его правильно. Кроме того, внимательно рассмотрите типы данных, когда:

  • Вы храните данные, в которых имеет значение точность
  • Вы работаете с таблицами с большим объемом, где затраты на хранение умножаются
  • Вы определяете часто запрашиваемые столбцы, которые выполняются быстрее с меньшими типами

Изучение распространенных типов данных

Соответствующие типы данных влияют на хранение, производительность и операции:

Категория типов Типы данных Размер хранилища Рекомендации по использованию Пример
Numeric INT, BIGINT, DECIMAL, FLOAT 4 байта, 8 байта, зависит Выбор на основе требований в области диапазона и точности Quantity INT, Revenue DECIMAL(10,2), Population BIGINT
String VARCHAR, CHAR, NVARCHAR 1 байт/char, фиксированный, 2 байт/char Используйте VARCHAR для данных переменной длины, CHAR для данных фиксированной длины, NVARCHAR для Unicode Email VARCHAR(100), CountryCode CHAR(2), ProductName NVARCHAR(100)
Дата и время DATE, DATETIME2, DATETIMEOFFSET 3 байта, 6–8 байт, 10 байт DATETIME2 обеспечивает лучшую точность, чем DATETIME BirthDate DATE, OrderTimestamp DATETIME2, EventTime DATETIMEOFFSET
Бинарный VARBINARY, IMAGE varies Хранение двоичных данных, таких как изображения или документы ProfilePhoto VARBINARY(MAX), DocumentContent VARBINARY(MAX)
Специальный UNIQUEIDENTIFIER, XML, JSON 16 байт, зависящий, нативный двоичный UNIQUEIDENTIFIER для GUID, XML для XML-документов, JSON (SQL 2025+) для JSON-документов в собственном двоичном формате RowGUID UNIQUEIDENTIFIER, Config XML, Settings JSON

Нюансы типов данных требуют внимательного внимания. Например, использование FLOAT для финансовых данных вместо DECIMAL может влечь за собой ошибки округления, которые нельзя исправить без пересчета каждого зависимого значения. UNIQUEIDENTIFIER Использование первичного ключа, когда INT достаточно, увеличивает размер индекса в три раза и замедляет каждую операцию JOIN. Большинство этих решений влияют на производительность базы данных и могут определить, выполняются ли запросы в миллисекундах или минутах.

Оценка требований к размеру таблицы

Размер таблицы влияет не только на затраты на хранение; это непосредственно влияет на операции базы данных. Размер таблицы влияет на время резервного копирования и восстановления, длительность перестроения индекса и производительность запросов.

Это важно

Плохо разработанная таблица, в которой хранится 200 байтов на строку вместо 100 байт, удвоит потребности в хранилище, время резервного копирования и требования к ввода-выводам.

Другой сценарий планирования размера таблиц — это вычисление затрат на хранение облачных баз данных, проектирование ограниченного места на диске или планирование стратегий архива. Все эти сценарии требуют точных оценок размера для принятия обоснованных решений о ресурсах и операциях.

Например, розничная компания, ежедневно хранящая 100 миллионов транзакций с дополнительными 50 байтами на строку, тратит 5 ГБ в день. Это составляет 1,8 ТБ ненужного объема хранилища в год, а также пропорционально увеличивает время резервного копирования и затраты.

В следующем примере показано, как оценить размер таблицы Employee :

-- Estimate row size for a table
-- Fixed-length columns: sum of column sizes
-- Variable-length: estimate average size
-- Example row calculation:
CREATE TABLE Employee (
    EmployeeID INT,              -- 4 bytes
    FirstName NVARCHAR(50),      -- ~2-100 bytes (avg 40)
    LastName NVARCHAR(50),       -- ~2-100 bytes (avg 40)
    HireDate DATE,               -- 3 bytes
    Salary DECIMAL(10,2)         -- 5 bytes
);  
-- Estimated row size: 4 + 40 + 40 + 3 + 5 = ~92 bytes
-- Plus row overhead (~7 bytes) = ~99 bytes per row
-- 1 million rows ≈ 94 MB

Подсказка

Вы можете использовать Copilot для создания оценки размера таблицы.

Проектирование эффективных столбцов

В следующем примере показана хорошо разработанная Product таблица, которая применяет принципы, описанные в этом уроке:

CREATE TABLE Product (
    ProductID INT PRIMARY KEY IDENTITY(1,1),       -- Auto-incrementing surrogate key (4 bytes)
    ProductName NVARCHAR(100) NOT NULL,             -- Unicode support, appropriate length, enforced
    Category NVARCHAR(50) NOT NULL,                 -- Smaller than ProductName (categorization needs less space)
    Price DECIMAL(10,2) NOT NULL,                   -- Exact precision for financial data
    StockQuantity INT NOT NULL DEFAULT 0,           -- Integer sufficient for inventory, default prevents nulls
    LastRestocked DATETIME2 DEFAULT GETUTCDATE()    -- Modern date type with automatic timestamp
);

В этой таблице демонстрируются несколько наилучших практик.

  • Соответствующие типы данных: INT для первичного ключа (меньше BIGINT или UNIQUEIDENTIFIER), DECIMAL(10,2) для точных финансовых вычислений вместо FLOAT, DATETIME2 для улучшения точности по сравнению с устаревшей DATETIME
  • Столбцы оптимального размера: NVARCHAR(100) для имен продуктов и NVARCHAR(50) для категорий в зависимости от ожидаемой длины данных
  • Ограничения: NOT NULL обеспечивает качество данных, предотвращая отсутствие критических значений
  • Значения по умолчанию: автоматические значения ( StockQuantity 0) и LastRestocked (текущее время UTC) снижают сложность кода приложения.
  • Эффективный первичный ключ: IDENTITY создает последовательные ключи, которые эффективно кластеризуются и используют минимальное хранилище (4 байта по сравнению с 16 байтами для GUID)

Замечание

В этом примере для поддержки Юникода используется NVARCHAR (2 байта на символ). Если данные доступны только для ASCII, VARCHAR (1 байт на символ) сокращает хранилище строк в половину. В ProductName VARCHAR(100) используется ~30 байтов против ~60 байтов у NVARCHAR(100) для 30-символьного имени. На 10 миллионов строк это экономит примерно 300 МБ. Используйте NVARCHAR для международных данных; используйте VARCHAR, когда важна эффективность хранения и данные будут оставаться только в формате ASCII.

Рекомендации по проектированию

При разработке и реализации таблиц применяются следующие основные принципы, чтобы обеспечить их производительность и сопровождемость:

  • Использование соответствующих типов данных — уменьшение объема хранилища и повышение производительности
  • Рассмотрим ранний размер таблицы . Оценка размера строки и общего размера таблицы для планирования хранения и индексирования
  • Реализация значимых ограничений . Обеспечение качества данных на уровне базы данных
  • Планирование роста — разработка таблиц для обработки будущих объемов данных
  • Стратегически индексируйте - индексируйте столбцы, используемые в WHERE, JOIN и ORDER BY предложениях
  • Выбор columnstore для аналитики - Использование индексов columnstore для больших таблиц с аналитическими запросами
  • Нормализация при необходимости — баланс нормализации с потребностями производительности запросов
  • Мониторинг сжатия строк и страниц — включение сжатия для больших таблиц для сохранения хранилища

Большинство проблем с производительностью базы данных связаны с плохими решениями по проектированию, принятыми на раннем этапе разработки. Чрезмерно большие типы данных расходуют место и замедляют запросы. Отсутствующие или неправильные типы индексов создают узкие места, которые обновления ресурсов не могут устранить. Предотвратить эти проблемы путем инвестирования времени в правильную структуру объектов перед созданием или изменением таблиц. Решения, которые вы принимаете во время проектирования, выбор соответствующих типов данных, оценка размеров таблиц, выбор правильных типов индексов, гораздо больше влияет на долгосрочную производительность и затраты, чем любая оптимизация, которая может применяться позже.