Стоит ли использовать tinyint вместо int для таблиц поиска SqlServer?
При разработке таблицы поиска (перечисления) в SqlServer 2005, если вы знаете, что количество записей никогда не будет очень большим, следует ли использовать tinyint вместо int? Меня больше всего беспокоит производительность, особенно эффективность индексов.
Допустим, у вас есть репрезентативные таблицы:
Person
------
PersonId int (PK)
PersonTypeId tinyint (FK to PersonTypes)
и
PersonTypes
-----------
PersonTypeId tinyint
PersonTypeName varchar(50)
Очевидными факторами являются размер данных и сложности с кодированием. Когда мы достигаем 100 миллионов строк в таблице person, мы сохраняем на 300 миллионов байтов меньше с tinyint, а не с int, плюс пространство, занимаемое нашими индексами. Небольшой объем данных, но значительный, если проектное решение применяется к десяткам больших таблиц. Проблемы с кодированием, конечно, возникают из-за всех этих проблем с приведением в коде ASP.NET C# / VB.
Если отбросить эти два вопроса, что еще нам понадобится? Станут ли запросы намного эффективнее из-за уменьшенного размера индексных страниц? Или есть какие-то дополнения, которые просто сводят на нет преимущества? Есть еще проблемы?
Я всегда просто использовал ints лично, но я рассматриваю tinyint для предстоящей работы по редизайну / миграции на некоторых огромных таблицах, поэтому я хотел бы получить совет.
[Редактировать]
Поэкспериментировав с этим, я ожидал, что проблем с кодированием не возникнет. Переход с int на tinyint вообще не привел к каким-либо проблемам с приведением типов.
Ответы 5
Я сомневаюсь, что использование smallint вместо int даст большой выигрыш в производительности, за исключением редких крайних случаев. Однако вы можете легко создать для этого тестовое приложение, создать несколько тестовых таблиц и выполнить миллион вставок / обновлений / выборок и сравнить производительность.
Чем уже таблица (или запись узла индекса), тем больше записей (или узлов индекса) может поместиться на одной странице ввода-вывода и тем меньше физических (и логических) операций чтения требуется для любого запроса. Кроме того, чем больше узлов индекса находится на одной странице, тем меньше уровней может быть в индексе, от корневого до конечного уровня, и если, сделав таблицу более узкой, вы пройдете порог, при котором индекс может быть на один уровень меньше, это может сильно повлиять на перфорацию.
Если, переключившись на TinyInt, вы измените свою таблицу с 200 байт на 197, это, вероятно, не будет иметь никакого значения ... Но если вы измените его с 20 байтов на 14 (скажем, у вас там 2 целых), тогда это могло быть драматично ...
вероятно, тогда это не будет иметь большого значения ... Не знаю, какой db ur использует, но на SQL Server страницы ввода-вывода имеют размер 8K, поэтому для сканирования / поиска таблиц вы будете переходить от 93 до 117 записей на страницу ... А как насчет индексов? Эти столбцы int в ваших индексах? Там это могло иметь эффект бигера.
Да, таблица имеет более десятка индексов и активно используется для OLTP. Размер индекса на диске в 10 раз превышает размер данных. Эти столбцы фактически взяты из отдельной таблицы, которая денормализована, чтобы уменьшить количество необходимых соединений. Почти все индексы будут изменены, чтобы включить эти новые поля.
Затем, если в индексе есть только одно или два из этих полей, и вы измените их с int (4 байта) на TinyInt (1 байт), вы получите почти трехкратное уменьшение размера индекса и увеличение количества узлов индекса на страницу ввода-вывода.
Память 101: меньший размер означает одновременное удержание большего объема ОЗУ и, следовательно, меньшее количество операций чтения с жесткого диска. Если БД достаточно велика и вы выполняете определенные типы запросов, это может быть очень серьезным фактором. Но, вероятно, это не будет иметь большого значения.
За исключением большей части времени, все, что меньше int, будет расширено до int в памяти - и если это не так, следующий элемент будет выровнен так, чтобы тратить 2 байта, которые вы якобы сохраняете в любом случае.
Существует также фактор поддержки индексов / резервных копий дисков / резервных копий на магнитной ленте, которые также будут занимать место, но я бы сказал, что наиболее важными являются производительность ввода-вывода и памяти.
Any other gotchas?
Я не уверен, что вы имеете в виду именно такую "ошибку", но я сталкивался с ситуациями, когда использование datetime вместо smalldatetime приводило к неправильному функциональному поведению, потому что более низкая точность smalldatetime не сравнивалась как эквивалент более высокая точность datetime для двух дат, которые в остальном были «одинаковыми».
Здесь нет никаких шансов, что это произойдет, так как tinyint / smallint / int / bigint будут сравниваться как идентичные для одного и того же числового целочисленного значения. Таким образом, вы, очевидно, в безопасности в этом отношении, но это не значит, что он точно отвечает на ваш вопрос.
Насколько я могу оценить, эффект будет 88 -> 70 для одной из основных таблиц, которые я имею в виду.