Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Когда в ms sql появится свой mediumint ?  [new]
Кесарь
Member

Откуда:
Сообщений: 653
Задумался тут над вопросами реализации модели ВЕРО и плавно перешёл (через избыточность ссылочных полей в центральной таблице объектов) к вопросу о недостаточности целочисленных типов в ms sql.

Ну что это в самом деле? Есть int, есть bigint. Разница между ними 4 байта и 4 294 967 раз. Ничего себе разгон! Есть smallint, есть int. Разница между ними уже не такая огромная, "всего" 2 байта и 65000 с лишним раз.

И в том, и в другом случае это слишком большие интервалы.

Не знаю как там для хранения каких-то вещественных значений (кол-во деталей в штуках, например), но для внутренних целей БД это слишком малый набор вариантов.

Парадокс, люди занимавшиеся созданием СУБД подумали о собссно ссылочной структуре в последнюю очередь. Чем они руководствовались, понять трудно. Однако очевидно, что размер хранения целых чисел в 1, 2, 4 и 8 байтов продиктован "красотой" этого ряда (всё как любят тру программисты вышедшие из математиков, всё это степени числа 2), но никак не практическими задачами.

Первейшая же задача реляционной БД это целостность и непротиворечивость, для чего нужны указатели и ссылки. Т.е. айдишники и ключи по ним. Потому как главное не положить данные в БД, а извлечь их потом.

Вот под это и нужно затачивать типы целочисленных данных. Потому как в ПЕРВУЮ очередь это вовсе не типы для хранения кол-ва мороженного на складе молокозавода. А типы для хранения номеров строк в таблицах.


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

(А вот в других РСУБД об этом подумали, хотя тоже вариант там дополнительный только один: медиуминт в MySQL)

Я полагаю, что типы целочисленных данных должны идти просто с шагом в 1 байт, и кому нужно, тот сам подберёт себе нужный диапазон.

Но сильнее всего конечно нужен медиуминт, потому как именно в этом диапазоне располагается значительная часть бизнес-задач, назовём это так. Ну скажем кол-во типов объектов ещё может дойти до десятков тысяч (2 байта), но вряд ли достигнет верхнего порога в 8 с лишним миллионов (3 байта).

Для примера есть таблица на 10 млрд записей. В ней есть поле с ID некой сущности. Сущностей не так много, но ставить smallint несколько опрометчиво. Мало ли как дело пойдёт? И назначают обычный инт, т.е. 4 байта.

Если же у нас был бы тип медиуминт, то экономия составила бы 10 млрд байт, т.е. 9.3 Гб. Оно вроде как по нынешним временам и не так много, но таблица такая не одна, помимо хранения самой таблицы на дисках, есть ещё чтение, запись, оперативка, tempdb и проталкивание данных через сети и системы обмена. В общем прилично набегает.

P.S. Более того! Sql server вообще-то умеет (умеет, когда захочет, что называется) хранить данные побитово. И ничто не мешает сделать типы данных с размером в 1.5 и 2.5 байт. Т.е. в 12 и 20 бит. Если такие типы данных ограничить только положительными значениями, то они были бы очень хороши для идентификаторов справочников (12 бит = 0 - 4095) или иных бизнес-сущностей (20 бит = 0 - 1 048 575).

Сообщение было отредактировано: 2 июн 21, 18:32
2 июн 21, 18:36    [22330519]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37254
Учитывая, что отрицательная часть интов применяется редко, давайте еще unsigned int сделаем, ага.
Есть binary/varbinary , закроет большинство сценариев хранения трехбайтовых интов.
2 июн 21, 18:40    [22330522]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21184
Кесарь
Однако очевидно, что размер хранения целых чисел в 1, 2, 4 и 8 байтов продиктован "красотой" этого ряда (всё как любят тру программисты вышедшие из математиков, всё это степени числа 2), но никак не практическими задачами.

Ну да... а архитектура процессоров и их типы данных тут, конечно, не при делах... а что, предложи, пусть Intel забацает процессорные регистры из трёх байтов.
2 июн 21, 18:46    [22330524]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Кесарь
Member

Откуда:
Сообщений: 653
Гавриленко Сергей Алексеевич
Учитывая, что отрицательная часть интов применяется редко, давайте еще unsigned int сделаем, ага.
Есть binary/varbinary , закроет большинство сценариев хранения трехбайтовых интов.


1. "unsigned int" Было бы неплохо, хотя вот именно это не так остро нужно. Надо брать у конкурентов удачные решения.

2. "binary/varbinary". А поиск так же быстро будет работать? Это риторический вопрос.

"по int сравнение будет быстрее, потому что для него есть поддержка процессора: сравнение двух целых чисел - это одна инструкция процессора."
2 июн 21, 18:54    [22330529]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
aleks222
Member

Откуда:
Сообщений: 1416
Кесарь
.

Если же у нас был бы тип медиуминт, то экономия составила бы 10 млрд байт, т.е. 9.3 Гб. Оно вроде как по нынешним временам и не так много, но таблица такая не одна, помимо хранения самой таблицы на дисках, есть ещё чтение, запись, оперативка, tempdb и проталкивание данных через сети и системы обмена. В общем прилично набегает.

P.S. Более того! Sql server вообще-то умеет (умеет, когда захочет, что называется) хранить данные побитово. И ничто не мешает сделать типы данных с размером в 1.5 и 2.5 байт. Т.е. в 12 и 20 бит. Если такие типы данных ограничить только положительными значениями, то они были бы очень хороши для идентификаторов справочников (12 бит = 0 - 4095) или иных бизнес-сущностей (20 бит = 0 - 1 048 575).

Гм, вот прецеденты, когда int не хватало - были. И был геморрой по переделке этого в бигинт.
А вот шобы bigint мешался "из-за размера" - такого не припомню.
"9.3 Гб" на таблице, содержащей десяток полей - это ниочем.
2 июн 21, 18:56    [22330531]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37254
Кесарь
2. "binary/varbinary". А поиск так же быстро будет работать? Это риторический вопрос.
На большом кол-ве данных -- а ведь именно на них экономия байтов имеет смысл -- вы скорее упретесь в диски/память, чем в проц, и не увидите этих накладных расходов.
2 июн 21, 19:03    [22330533]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37254
И, кстати, да, компрессию уже давно завезли:

https://docs.microsoft.com/ru-ru/sql/relational-databases/data-compression/row-compression-implementation?view=sql-server-ver15
bigint Да Использует минимально необходимое число байт. Например, если значение может храниться в 1 байте, то для его хранения будет использоваться всего 1 байт.
2 июн 21, 19:06    [22330535]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Кесарь
Member

Откуда:
Сообщений: 653
Akina
Ну да... а архитектура процессоров и их типы данных тут, конечно, не при делах... а что, предложи, пусть Intel забацает процессорные регистры из трёх байтов.


Akina,

исходя из этой логики:

1. Непонятно, как получилось, что есть типы данных на 1 и 2 байта. Раз у регистра строго 4 (теперь уже 8). Если регистр 4 (8) байта, то можно иметь от 1 до 4 байт (до 8). Типы данных на 1 и 2 байта есть, почему нельзя иметь 3? Вы придумываете сложность на ровном месте.

2. Поиск в MySQL по медиуминту должен быть не быстрее, чем по бигинту. Это так?
2 июн 21, 19:08    [22330538]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37254
Кесарь
Поиск в MySQL по медиуминту должен быть не быстрее, чем по бигинту. Это так?
Модератор: У меня начинает появляться желание снести тему в Сравнение СУБД. Давайте или в расках скуля оставаться, или туда поедете.


Сообщение было отредактировано: 2 июн 21, 19:03
2 июн 21, 19:12    [22330544]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Кесарь
Member

Откуда:
Сообщений: 653
Гавриленко Сергей Алексеевич
И, кстати, да, компрессию уже давно завезли:

https://docs.microsoft.com/ru-ru/sql/relational-databases/data-compression/row-compression-implementation?view=sql-server-ver15
bigint Да Использует минимально необходимое число байт. Например, если значение может храниться в 1 байте, то для его хранения будет использоваться всего 1 байт.


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

Ну вот например:

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


Я про что и говорил: БД не в вакууме вертится, а в среде. И в этой среде пересылка данных туда сюда тоже требует затрат. Уменьшение напрямую размера данных уменьшает затраты, а сжатие данных в самой БД нет.
2 июн 21, 19:22    [22330549]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Кесарь
Member

Откуда:
Сообщений: 653
aleks222
Гм, вот прецеденты, когда int не хватало - были. И был геморрой по переделке этого в бигинт.


Вы так возражаете? Это ж подтверждение: был бы медиуминт, вы бы его заложили сразу и ничего переделывать не пришлось бы!


А вот шобы bigint мешался "из-за размера" - такого не припомню.
"9.3 Гб" на таблице, содержащей десяток полей - это ниочем.


Речь не про экономию дискового пространства, хотя и это важно. А про скорость работы системы в целом.
2 июн 21, 19:34    [22330555]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Кесарь
Member

Откуда:
Сообщений: 653
Кесарь
aleks222
Гм, вот прецеденты, когда int не хватало - были. И был геморрой по переделке этого в бигинт.


Вы так возражаете? Это ж подтверждение: был бы медиуминт, вы бы его заложили сразу и ничего переделывать не пришлось бы!

Речь не про экономию дискового пространства, хотя и это важно. А про скорость работы системы в целом.


Извиняюсь, имелся в виду медиуминт2, который между инт и бигинт, а не между смолинт и инт.


А вообще по хорошему всех этих бигинтов и смолинтов быть не должно, а должны быть инты:

int1, int2, ..., int8 где число означает кол-во байт.
2 июн 21, 20:00    [22330568]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8701
Скорее всего, дело в скорости обработки и наборе команд интеловских процессоров. Судя по основанию 2 получить указатель на новое значение можно операцией сдвига, а это очень быстро. Бытовые рассуждения здесь не помогут, надо знать процессорную механику.
2 июн 21, 20:22    [22330577]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37254
Рассуждать про процессорные команды, не зная, что и как делает МС -- это супер-наивно.

Доводы про размер? Они понятны -- это экономия диска и памяти.
Доводы про скорость процессорной обработки int vs binary? Смех какой-то.
2 июн 21, 20:40    [22330583]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8701
Главный вопрос - а оно надо? Оракл вообще не имеет целочисленных типов и прекрасно себя чувствует.
2 июн 21, 21:32    [22330599]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8701
Гавриленко Сергей Алексеевич,

Доводы про скорость процессорной обработки int vs binary? Смех какой-то.


Поему - смех? Когда писали ядро этой системы?
2 июн 21, 21:34    [22330600]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37254
Владислав Колосов
Гавриленко Сергей Алексеевич,

Доводы про скорость процессорной обработки int vs binary? Смех какой-то.


Поему - смех? Когда писали ядро этой системы?
Да пофиг "когда". Не пофиг - "как".
Поэтому и смех, что вообще никто не знает, как сделан скуль. Может все типы побайтово и так сравниваются. И разницы между int и varbinary (8) нет в принципе.
2 июн 21, 21:49    [22330608]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31910
Кесарь
И в том, и в другом случае это слишком большие интервалы.
И даже это программисты не используют. Не делают tinyint там, где его точно хватит. Почему? Потому что нет гарантии, что хватит, потому что экономия этих 9 гб выйдет компании в такие затраты, что она может обеспечить себя всю топовыми СХД на 100 лет.
Поэтому нужен не Int3 или Int7, а VarInt, который бы занимал подходящее количество бит под лежащее там значение.
Записали туда 127, выделилось 8 бит, прибавили 1, опа, уже под поле в этой конкретной записи выделено 9 бит.
2 июн 21, 21:49    [22330609]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37254
alexeyvg
Кесарь
И в том, и в другом случае это слишком большие интервалы.
И даже это программисты не используют. Не делают tinyint там, где его точно хватит. Почему? Потому что нет гарантии, что хватит, потому что экономия этих 9 гб выйдет компании в такие затраты, что она может обеспечить себя всю топовыми СХД на 100 лет.
Поэтому нужен не Int3 или Int7, а VarInt, который бы занимал подходящее количество бит под лежащее там значение.
Записали туда 127, выделилось 8 бит, прибавили 1, опа, уже под поле в этой конкретной записи выделено 9 бит.
Лепить bigint везде на случай "как бы чего не вышло" -- так себе идея.
Я внизу в спойлере накидал запрос на оценку, что будет, если вместо не-bigint делать всегда bigint. На одной из основных баз получается оверхед 25 TB. (Не уверен на 100%, что не ошибся).

+
select
    a.data_type
    , overhead_gb = sum ( rows.rows * cast (r.multiplier as bigint) ) / 1024. / 1024. / 1024.
from        INFORMATION_SCHEMA.COLUMNS a
cross apply (
    select
        multiplier = case a.DATA_TYPE
            when 'int'      then 2
            when 'smallint' then 4
            when 'tinyint'  then 8
        end
        , object_id = object_id( quotename( a.TABLE_SCHEMA ) + '.' + quotename( a.table_name ) )
) r
cross apply (
    select
        c.object_id
        , i.index_id
    from         sys.columns         c
    inner join   sys.index_columns   ic on 
        ic.object_id        = c.object_id
    inner join   sys.indexes         i  on 
        ic.object_id = i.object_id
        and i.index_id not in (0, 1)
    where
        c.object_id = r.object_id
        and c.name  = a.COLUMN_NAME
    union all
    select
        c.object_id
        , i.index_id
    from        sys.columns         c
    left join   sys.indexes         i  on 
        c.object_id = i.object_id
        and i.index_id in (0, 1)
    where
        c.object_id = r.object_id
        and c.name  = a.COLUMN_NAME
) ic
cross apply (
    select
        rows = sum( p.rows )
    from    sys.partitions p
    where
        p.object_id     = ic.object_id
        and p.index_id  = ic.index_id
) rows
where
    a.DATA_TYPE in ( 'int', 'smallint', 'tinyint' )
    and objectproperty( r.object_id, 'IsMsShipped' ) = 0
group by
    a.data_type
order by
    overhead_gb desc


К сообщению приложен файл. Размер - 6Kb
2 июн 21, 22:32    [22330627]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8701
Гавриленко Сергей Алексеевич,

Да пофиг "когда". Не пофиг - "как".


Именно, "как" тогда и "как" сейчас - очень сильно отличается. Тогда покомандно анализировали работу компилятора. Кто сейчас этим будет заниматься?
3 июн 21, 00:55    [22330660]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Siemargl
Member

Откуда: 010100
Сообщений: 6497
Гавриленко Сергей Алексеевич,

Ошибся чуть. Надо так
        multiplier = case a.DATA_TYPE
            when 'int'      then 4
            when 'smallint' then 6
            when 'tinyint'  then 7

Сколько каждый тип экономит лишних вместо 8-байтового bigint

и это не оверхед, а наоборот - экономия.

ЗЫ. топик мается бездельем

Сообщение было отредактировано: 3 июн 21, 02:40
3 июн 21, 02:47    [22330680]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37254
Siemargl,

Сглсн. Поправил. Пересчитал. Плюс-минус те же "сырые" 25 Тб.

+
select
    a.data_type
    , overhead_gb = sum ( rows.rows * cast (8 - r.size as bigint) ) / 1024. / 1024. / 1024.
from        INFORMATION_SCHEMA.COLUMNS a
cross apply (
    select
        size = case a.DATA_TYPE
            when 'int'      then 4
            when 'smallint' then 2
            when 'tinyint'  then 1
        end
        , object_id = object_id( quotename( a.TABLE_SCHEMA ) + '.' + quotename( a.table_name ) )
) r
cross apply (
    select
        c.object_id
        , i.index_id
    from         sys.columns         c
    inner join   sys.index_columns   ic on 
        ic.object_id        = c.object_id
    inner join   sys.indexes         i  on 
        ic.object_id = i.object_id
        and i.index_id not in (0, 1)
    where
        c.object_id = r.object_id
        and c.name  = a.COLUMN_NAME
    union all
    select
        c.object_id
        , i.index_id
    from        sys.columns         c
    left join   sys.indexes         i  on 
        c.object_id = i.object_id
        and i.index_id in (0, 1)
    where
        c.object_id = r.object_id
        and c.name  = a.COLUMN_NAME
) ic
cross apply (
    select
        rows = sum( p.rows )
    from    sys.partitions p
    where
        p.object_id     = ic.object_id
        and p.index_id  = ic.index_id
) rows
where
    a.DATA_TYPE in ( 'int', 'smallint', 'tinyint' )
    and objectproperty( r.object_id, 'IsMsShipped' ) = 0
group by
    a.data_type
order by
    overhead_gb desc


К сообщению приложен файл. Размер - 5Kb
3 июн 21, 03:23    [22330685]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8701
Помнится, я когда-то писал программу, которая рекомендует замену размеров колонки, анализируя содержимое, в том числе с заменой типов и not null.
3 июн 21, 10:49    [22330784]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Кесарь
Member

Откуда:
Сообщений: 653
Гавриленко Сергей Алексеевич

Я внизу в спойлере накидал запрос на оценку, что будет, если вместо не-bigint делать всегда bigint.


Отличный запрос! Мне понравилось.


На одной из основных баз получается оверхед 25 TB. (Не уверен на 100%, что не ошибся).


Вооот! А это помимо дисков и всё остальное окружение, которое должно через себя пропустить все эти лишние объёмы.
3 июн 21, 11:25    [22330805]     Ответить | Цитировать Сообщить модератору
 Re: Когда в ms sql появится свой mediumint ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8701
Гавриленко Сергей Алексеевич,

а в процентном соотношении какой over? У меня на базе 0.3%, база около 1 Тб.

Сообщение было отредактировано: 3 июн 21, 11:40
3 июн 21, 11:44    [22330815]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить