RSS
 

 

Экономим место в БД за счет использования правильных типов данных

02 Ноя

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

Это пространство - свободное место на страницах данных (и индексов), которео не может быть занято под новые данные. Причин этой "невозможности" две.

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

Во-вторых, лишнее место занимают столбцы с типами данных с фиксированной длиной, размерность которых превышает реально хранящиеся в них данные. Например, если требуется хранить дату без времени, то совершенно не нужно использовать типы данных, котрые позволяют помимо даты хранить время за счет большего числа байт, содержащих значение. Например, тип данных datetime в SQL Server позволяет хранить значения времени в интервале с 1753 года по 9999 с точностью до 1/300 секунды, занимая при этом 8 байт. Если же мы собираемся например хранить дату выдачи документа, удостоверяющего личность, то очевидно, что вполне достаточно типа date который хранит только даты без времени, но занимает всего 3 байта.

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

Предположим, что есть такая таблица

CREATE TABLE testdata(
	id INT PRIMARY KEY,
    	txt VARCHAR(8000) NULL,
	some_date datetime NULL,
    	zip VARCHAR(20) NULL,
    	some_year INT NULL,
	some_month INT NULL)

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

  • поле some_date как говорилось выше, вполне может обойтись типом date, который занимает на 5 байт меньше, чем выбранный datetime
  • для поля zip, если там будет действительно храниться только корректный индекс, было бы "экономнее" использовать тип int (для российских индексов сэкономим по 4 байта на запись)
  • для хранения значения года можно вполне обойтись двухбайтовым smallint в поле some_year - еще 2 байта лишних
  • номер месяца никак не может быть больше 12 (в некоторых календарях бывает и 13, что в данном случае ничего не меняет), что экономит еще 3 байта за счет использования однобайтового типаtinyint для поля some_month

Итого экономия казалось бы небольшая - всего 14 байт.

Заполним данными исходную таблицу и посмотрим на реально занимаемый объем на диске:

INSERT INTO testdata
SELECT
    NUMBER,
    REPLICATE('W', 980),
    dateadd(dd, v.NUMBER, '2008-01-01'),
    CONVERT(VARCHAR(10), 100000+rand()*99999),    1000 + v.NUMBER % 2000,
    1 + v.NUMBER % 12from master.dbo.spt_values vwhere v.TYPE = 'P'goselect
    8192.*page_count/s.record_count AS StorageLen,
    avg_record_size_in_bytes AS RealRecSize,    8192*page_count AS DataSize,
    (1-s.avg_page_space_used_in_percent/100.)*8192 AS [PageFreeSpace]
FROM sys.dm_db_index_physical_stats(db_id(), object_id('testdata'), NULL, NULL, 'SAMPLED') s

Резутьтат - длина строки 1019 байт, объем таблицы - 2,400,256 байт, в среднем строка заняла 1172 байта. То есть на одну строку приходится неиспользуемых 153 байта или 13% объема, занимаемого на диске. На каждой странице размером 8192 байт осталось чуть меньше 1 килобайта свободного места, которое не может быть использовано в нашем случае, поскольку длина строки более, чем 1Кб.

Если же мы (не потеряв функциональности) будем использовать типы данных, занимающие меньше места:

CREATE TABLE testdata(
        id INT PRIMARY KEY,
	txt VARCHAR(8000) NULL,
	some_date DATE NULL,
	zip INT NULL,
    	some_year SMALLINT NULL,
	some_month tinyint NULL)

на каждой странице будет размещаться на одну строку больше. При уменьшении длины строки всего на 1%, объем таблицы уменьшится уже до 2,097,152 байт, то есть на 12,6% от исходного. Неиспользуемого пространства на каждой странице останется всего 40 байт.

Также хотелось бы предостеречь от неуместного использования строковых типов данных с фиксированной длиной (char/nchar). В стародавние времена, когда в качестве хранилища данных использовались файлы типа DBF, которые почти не кэшировались в памяти, фиксорованные типы данных действительно позволяли быстрее получать данные, поскольку для данных с фискированной длиной не нужно было сканировать файл, получая смещение от начала файла для нужного значения путем простого вычисления. В SQL Server (как и во многих других СУБД), для того, чтобы получить поля конкретной строки, почти нет разницы в затратах процессорного времени в зависимости от того, будет этот столбец хранить данные с фиксированной или переменной длиной. Тип данных char есть смысл использовать тогда, когда все значения поля имеют одинаковую длину. Например, если в поле char(1) храняться какие-либо мнемонические обозначения статусов или типов.
Источник: http://www.gotdotnet.ru/blogs/DeColores/8814/

 
 

Метки: , 02.11.2011