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

Проблема

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

Решение

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

Для определения столбцов-кандидатов для оптимизации мы можем изучить метаданные и таблицы напрямую. Например, если все значения в столбце bigint могут поместиться в столбец int, мы можем изменить столбец на int и сэкономить 4 байта на строку. Аналогично, если все значения помещаются в smallint, мы можем сэкономить 6 байт на строку. Хотя эти сбережения могут показаться незначительными, они могут значительно накапливаться, когда у вас есть несколько столбцов в миллионах строк.

В этой статье мы сосредоточимся на типах, основанных на целых числах: bigint, int, smallint, tinyint и bit. Стоит отметить, что столбцы bit могут объединять до 8 столбцов в один байт, в то время как каждый столбец tinyint всегда будет занимать свой собственный байт.

Генерация типов данных

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

DECLARE @src bigint = 2;
SELECT [type], minval, maxval FROM 
(VALUES
  (N'bit',      0, 1),
  (N'tinyint',  0, 255),
  (N'smallint', -(POWER(@src,15)),       (POWER(@src,15)-1)),
  (N'int',      -(POWER(@src,31)),       (POWER(@src,31)-1)),
  (N'bigint',   -(POWER(@src,62)-1)*2-2, (POWER(@src,62)-1)*2+1)
) AS v([type], minval, maxval);

Этот запрос предоставляет нам поддерживаемый диапазон значений для каждого типа данных. Мы можем использовать это в качестве основы для определения столбцов, которые можно оптимизировать.

Определение столбцов для оптимизации

Теперь, когда у нас есть типы данных и их диапазоны, мы можем объединить их с представлениями каталога для таблиц и столбцов, чтобы найти все столбцы-кандидаты, которые потенциально можно оптимизировать. Мы также должны учесть дополнительную контекстную информацию, такую как ранжирование типов, могут ли они быть “исходными” или “целевыми”, идентификатор типа и количество байт, которое каждый тип занимает.

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

DECLARE @src bigint = 2;
WITH types AS 
(
  SELECT * FROM 
  (VALUES
    (1,0,1, 104, N'bit',      1, 0, 1),
    (2,1,1, 48,  N'tinyint',  1, 0, 255),
    (3,1,1, 52,  N'smallint', 2, -(POWER(@src,15)),       (POWER(@src,15)-1)),
    (4,1,1, 56,  N'int',      4, -(POWER(@src,31)),       (POWER(@src,31)-1)),
    (5,1,0, 127, N'bigint',   8, -(POWER(@src,62)-1)*2-2, (POWER(@src,62)-1)*2+1)
  ) AS v(seq, src, trg, type_id, [type], bytes, minval, maxval)
),
cols AS
(
  SELECT t.[object_id],
         [schema] = s.name, 
         [table]  = t.name, 
         [column] = QUOTENAME(c.name), 
         [type]   = styp.name + COALESCE(' (alias: ' + utyp.name + ')', ''),
         c.is_nullable, 
         trgtyp.seq,
         trgtyp.type_id, 
         trgtype = trgtyp.[type],
         savings = srctyp.bytes - trgtyp.bytes, 
         trgtyp.minval,
         trgtyp.maxval,
         [rowcount] = (SELECT SUM([rows]) FROM sys.partitions
           WHERE object_id = t.object_id AND index_id IN (0,1))
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON s.[schema_id] = t.[schema_id]
  INNER JOIN sys.columns AS c
  ON t.[object_id] = c.[object_id]
  INNER JOIN sys.types AS styp
  ON c.system_type_id = styp.system_type_id
  AND c.system_type_id = styp.user_type_id
  LEFT OUTER JOIN sys.types AS utyp
  ON c.user_type_id = utyp.user_type_id
  AND utyp.user_type_id <> utyp.system_type_id
  INNER JOIN types AS srctyp
  ON srctyp.type_id = c.system_type_id
  INNER JOIN types AS trgtyp
  ON trgtyp.seq < srctyp.seq
  WHERE srctyp.src = 1
  AND trgtyp.trg = 1
)
SELECT * INTO #cols FROM cols;

SELECT * FROM #cols;

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

Оценка кандидатов для оптимизации

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

DECLARE @sql nvarchar(max) = N';WITH x([object_id],[column],minval,maxval) 
AS (',
        @core nvarchar(max) = N'
    SELECT $oid, ''$c'', MIN($c), MAX($c) FROM $obj UNION ALL';    
SELECT @sql += REPLACE(REPLACE(REPLACE(@core,'$oid',RTRIM(object_id)),
  '$c',[column]),'$obj',QUOTENAME([schema]) + '.' + QUOTENAME([table]))
  FROM (SELECT [schema],[table],[column],object_id FROM #cols
  GROUP BY [schema],[table],[column],object_id) AS x;
SET @sql += N' 
    SELECT NULL,NULL,NULL,NULL
  ) 
  SELECT c.[schema],c.[table],c.[column],c.is_nullable,
    current_type = c.[type],potential_type=c.trgtype,
    space_savings=c.savings*c.[rowcount],x.minval,x.maxval,
    range = RTRIM(c.minval) + '' -> '' + RTRIM(c.maxval) 
  FROM x
  INNER JOIN #cols AS c 
  ON x.[object_id] = c.[object_id]
  AND x.[column] = c.[column]
  AND x.minval >= c.minval
  AND x.maxval <= c.maxval;';
PRINT @sql;
-- EXEC sys.sp_executesql @sql;

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

Соображения и следующие шаги

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

Перед внесением каких-либо изменений крайне важно тщательно оценить потенциальное влияние и учесть такие факторы, как диапазон данных, экономию места и будущее использование столбца. Этот скрипт не генерирует команды ALTER TABLE, так как его целью является поощрение тщательного обдумывания перед внесением каких-либо изменений.

Резюме

Анализируя типы данных и диапазоны и используя динамический SQL, вы можете быстро определить столбцы, которые потенциально можно оптимизировать, чтобы сэкономить место в ваших таблицах SQL Server. Хотя эта статья сосредоточена на столбцах, основанных на целых числах, вы можете расширить этот подход, чтобы охватить также столбцы, основанные на строках. Помните, что перед внесением каких-либо изменений необходимо тщательно оценить потенциальное влияние и учесть все соответствующие факторы.

Следите за будущими советами по оптимизации использования пространства таблиц SQL Server!