По материалам статьи Neil Boyle на SWYNK.COM "Saving Space To Increase Performance"

Введение

Нейл пишет, что многие разработчики и DBA перестали уделять в должной мере внимание оптимизации данных для экономии дискового пространства, чему причиной является существенный рост размеров выпускаемых жестких дисков и снижения их стоимости. В этой статье Нейл показывает не состоятельность такого подхода и напоминает, что экономия нескольких байт в полях таблиц может существенно поднять эффективность SQL сервера. Если Вы когда-либо анализировали Execution Plan исполнения запроса, Вы видели, что SQL сервер определяет его оцененную "стоимость". Эта стоимость, очевидно, не в деньгах, а в терминах серверных ресурсов, требуемых для выполнения запроса. Самой главной составляющей этой оценки является дисковое I/O, так что снижение стоимости запроса, это, фактически, уменьшение I/O операций сервера. Поскольку дисковые операции самые медленные (по определению), мы можем рассчитывать на значительное увеличение эффективности. В этой статье будет рассмотрено несколько путей оптимизации данных.

Основы

Вот - довольно показательный пример - я создавал две таблицы (SQL сервер 7) и загрузил в каждую 10,000 строк по 4 байта:

create table t1 (v char(255) NOT NULL)
create table t2 (v varchar(255) NULL)

Одна таблица была создана с использованием типа столбца varchar (255), а другая с использованием типа char(255). Теперь, тип char(255) использует весь заданный размер для хранения данных, т.е. если для строк, которые будут введены, и будут содержать меньше чем 255 символов, оставшееся не заполненным место будет потрачено впустую. Зато для типа данных varchar такого наблюдаться не будет. Выполнение DBCC SHOWCONTIG для каждой из этих таблиц показывало, что таблица с char(255) заняла 334 страницы (страница - 8 килобайтов в SQL 7 или 2000), а использующая varchar(255) заняла только 23 страницы, чтобы хранить те же самые данные. Использование меньшего дискового пространства означает, что любая операция поиска, и особенно простые операции просмотра таблицы типа SELECT COUNT(1) FROM... будет выполняться намного быстрее, в отличии от таблицы использующей char(255). Хотя это - достаточно показательный пример, я слегка изменил эти таблицы и опять загрузил по 10,000 строк в каждую. Для простоты, я использую один столбец в каждой таблице, но то же самое будет наблюдаться и любом числом столбцов. Существенное значение имеет только полная длина строки:

create table t1 (v char(4000))
create table t2 (v char(4040))

Таблица t1, после загрузки 10,000 строк, заняла 5000. Длина строки для таблицы t2 - только на один процент больше, чем длина t1, так что Вы могли бы ожидать что таблица t2 будет занимать на один процент большее страниц, но в действительности оказывается, что она занимает в двое больше, чем t1. Причина этого в том, что SQL сервер 7 может хранить по 8060 байт данных на одной странице, так что на каждой странице таблицы t1 хватает места для размещения двух строк. Однако, когда мы расширяем длину строки до 4040, тогда только одна строка будет помещаться на странице. Следовательно, для размещения того же количества строк потребуется в два раза больше страниц. Это происходит потому, что SQL сервер не умеет разбивать строку на две страницы.
Снова, это был показательный пример, из которого следует общее правило:
-Чем короче длина строки, тем большее количество строк Вы разместите на странице, и тем меньше ваша таблица будет занимать места в базе данных.
Этот эффект особенно значим для SQL сервера 6.5, где максимальная длина строки слегка больше чем 2000 байтов. Ниже представлены несколько советов по экономии места в базе:
- Используйте varchar вместо char, если ваши данные не имеют фиксированную длину, или не используют очень короткие строки.
- Старайтесь избегать использования Unicode типы nchar и nvarchar, занимающих двойное место, если Вы не нуждаетесь в них.
- Используйте smallint и tinyint если Вы не нуждаетесь в больших числах, и используете целые числа вместо типов с плавающей точкой или Numeric везде, где это возможно.
- Использование smalldatetime вместо datetime сохраняет два байта, если Вам не нужно фиксировать время точнее минуты.
- Старайтесь избегать использования столбцов GUID, если только Вы действительно не нуждаетесь в них.

Это только несколько наиболее очевидных примеров. Вы должны ознакомиться со всеми имеющимися у SQL сервера типами данных, и очень тщательно выбирать из них наиболее экономичные. Вы могли бы выбирать использовать тип данных smallmoney вместо типа money, и только это сохранит Вам 4 байта. Но тогда необходимо помнить, что значения этого типа данных может содержать только сравнительно маленькие величины, что не годится для японской Иены или итальянской Лиры. Если Вы используете тип данных, который в будущем может превысить максимально допустимое значение, то смена типа может вызвать больше проблем, чем снижение эффективности на этапе начала эксплуатации приложения.

Индексы

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

Другие выгоды.

Более компактное хранение ваших данных не только уменьшает их размер, но и предоставляет другие выгоды:
- Сервер сможет разместить большее количество данных в кэше, увеличивая таким образом cache hit ratio и сокращая дисковый I/O.
- Резервные копии будут занимать меньше места и быстрее копироваться.
- Уменьшится трафик при перемещении данных по сети.
- Объединения будут происходить быстрые (короткие столбцы проще сравнивать, чем длинные).