Как выбор типа данных влияет на производительность

Автор: Paul Randal, How can data-type choice affect performance?

На одном из занятий, которые мы с Кимберли вели на этой неделе на конференции SQL Connections, мы обсуждали, как выбирать эффективные типы данных. Я хотел бы поделиться этим обсуждением здесь на примере.

Суть вопроса заключается в объёме пространства, необходимом для хранения типов данных в схеме, а также в том, чтобы выбор типа данных соответствовал хранимым данным без создания каких-либо излишних накладных расходов. Вот несколько примеров:

• Поле, хранящее возраст человека: обычно может использовать тип TINYINT, который может хранить максимальное значение 256, если только это не археологическая или историческая база данных. Однако, не уделив этому особого внимания, кто-то может выбрать тип INTEGER. В чём разница? TINYINT занимает один байт для хранения, тогда как INTEGER занимает 4 байта, теряя 3 байта на каждую запись.
• Поле, хранящее город проживания в США: должно быть способно вместить 24 символа  (см. мою предыдущую статью о том, какой длины должны быть поля). Какой тип данных использовать? CHAR(24) сможет хранить все возможные значения, но всегда будет занимать 24 байта в записи, так как это столбец фиксированной длины. С другой стороны, VARCHAR(24) будет хранить только количество байтов, равное количеству символов в названии города, поэтому использование типа фиксированной длины приведёт к потере переменного количества байтов на каждую запись.
• Аббревиатуры штатов США: обычно сокращаются до двух символов (например, Вашингтон — WA, Калифорния — CA). Какой тип лучше использовать: CHAR(2) или VARCHAR(2)? Учитывая, что аббревиатуры всегда состоят из 2 символов, оба будут хранить 2 байта постоянно. НО столбец переменной длины имеет двухбайтовые накладные расходы (двухбайтовый указатель для столбца, который хранится в массиве смещений столбцов переменной длины в записи), поэтому в этом случае лучшим типом данных будет CHAR(2).

Вы видите, как выбор типов данных без учёта того, является ли он наилучшим, может привести к нерациональному расходованию пространства. Давайте рассмотрим пример схемы для поддержки переписи населения США.

CREATE TABLE CensusSchema1 (
    SSN CHAR (256),
    StateName CHAR (256),
    Sex INTEGER,
    Employed INTEGER,
    SelfEmployed INTEGER,
    EthnicOrigin INTEGER,
    MaritalStatus INTEGER,
    NumberOfDependents INTEGER,
    Age INTEGER,
    CountryOfBirth INTEGER,
    HouseholdIncomeGroup INTEGER,
    ZipCode5 INTEGER);
GO

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

CREATE TABLE CensusSchema2 (
    SSN CHAR (9),                      -- экономия 244 байта
    StateName VARCHAR (256),           -- экономия не менее 240 байт (самое длинное название штата — 14 символов + 2 байта накладных расходов varchar)
    Sex BIT,
    Employed BIT,
    SelfEmployed BIT,                  -- экономия в сумме 11 байт на этих трёх полях
    EthnicOrigin TINYINT,              -- экономия 3 байта
    MaritalStatus TINYINT,             -- экономия 3 байта
    NumberOfDependents TINYINT,        -- экономия 3 байта
    Age TINYINT,                       -- экономия 3 байта
    CountryOfBirth TINYINT,            -- экономия 3 байта
    HouseholdIncomeGroup TINYINT,      -- экономия 3 байта
    ZipCode5 INTEGER);                 -- экономии нет
GO

Плохая схема даёт размер строки 574 байта, а очищенная схема — не более 48 байт на строку. Я разработал эти две схемы для поддержки переписи населения США. Население США составляет примерно 300 миллионов человек. Это означает, что плохая схема заняла бы около 190 ГБ для хранения всей информации, а очищенная схема — всего около 15 ГБ, что более чем в 12 раз эффективнее! Теперь мы начинаем понимать, как неправильный выбор типа данных может привести к низкой производительности.

Широкие строки означают:

• Меньше строк может поместиться на странице размером 8 КБ.
• Требуется больше ЦП для разбора записи из-за количества недействительных строк кэша данных ЦП, необходимых для чтения записи в различные кэши ЦП (каждый раз, когда строка кэша становится недействительной, это требует большого количества тактов ЦП — подробнее о процессорах и строках кэша см. здесь).

Меньше строк на странице означает:

• Для хранения данных требуется больше страниц.
• Индексы могут иметь меньшую степень ветвления (если ключи индекса шире, чем необходимо), что приводит к большему количеству уровней в индексе и менее эффективной навигации по индексу от корневой страницы к листовому уровню.

Больше страниц означает:

• Требуется больше операций ввода-вывода для чтения того же количества фактических данных.
• Требуется больше памяти буферного пула для хранения того же количества фактических данных.
• Требуется больше дискового пространства для хранения того же количества фактических данных.

Очевидно, что существует связь между различными аспектами производительности рабочей нагрузки и плохо выбранными типами данных.