Числе в базе данных: как выбрать тип поля для точности вычислений
Ошибка в типе числового поля редко выглядит как сбой. Чаще это тихое расхождение: сумма после нескольких операций отличается в последнем разряде, счётчик переполняется, а выгрузка не совпадает с исходными данными.
Число в базе данных: как выбрать тип поля для точности вычислений
Причина обычно не в формуле, а в выбранном представлении числа.
Типы данных для чисел в базе решают три разные задачи: задают диапазон значений, определяют точность и влияют на стоимость вычислений. INTEGER подходит для целых величин с ограниченным диапазоном. DECIMAL — для десятичной точности, где важен каждый разряд. FLOAT и DOUBLE PRECISION — для приближённых вычислений с широким диапазоном. Универсального числового типа нет. Есть соответствие между спецификацией поля и тем, что приложение делает с данными.
Целочисленные типы: диапазон важнее привычки
Если значение всегда целое, начинать следует с целочисленного типа. Это счётчики, идентификаторы, количество объектов, коды статусов, число попыток. Для таких данных дробная часть не нужна, а значит, тип с плавающей точкой создаёт ненужную неопределённость.
В типичной SQL-схеме доступны три базовых варианта:
| Тип | Размер | Диапазон |
|---|---|---|
SMALLINT | 2 байта | от −32 768 до 32 767 |
INTEGER / INT | 4 байта | от −2 147 483 648 до 2 147 483 647 |
BIGINT | 8 байт | от −9 223 372 036 854 775 808 до 9 223 372 036 854 775 807 |
Выбор здесь сводится к диапазону и контракту данных. SMALLINT экономит место, но быстро становится ограничением, если счётчик растёт годами или значение может выйти за ожидаемые пределы. BIGINT даёт широкий диапазон, но не должен назначаться автоматически «на будущее»: он занимает больше места, а его использование меняет размер ключей, индексов и связанных полей.
Для идентификатора с автоинкрементом важен не только текущий объём таблицы. Нужно оценить темп роста, правила удаления записей и то, может ли значение приходить извне. Если два сервиса создают идентификаторы независимо, проблема диапазона может оказаться вторичной: сначала потребуется определить стратегию генерации ключей. Но тип столбца всё равно должен выдерживать верхнюю границу этой стратегии.
В MySQL атрибут UNSIGNED сдвигает диапазон целочисленного типа в положительную область. Например, INT UNSIGNED хранит значения от 0 до 4 294 967 295. Это полезно, если отрицательные числа запрещены самой моделью данных. Но это не бесплатная оптимизация: схема становится менее переносимой между СУБД, а приложение должно согласованно обрабатывать типы при обмене данными и миграциях.
Перед использованием UNSIGNED проверьте все слои контракта: драйвер БД, ORM, API и код, который преобразует значение. Если один компонент считает поле знаковым, а другой — беззнаковым, ошибка проявится не в самой таблице, а на границе сериализации или сравнений.
Практический порядок выбора целочисленного типа:
1. Определить семантику поля: счётчик, количество, идентификатор или код. Не объединять разные сущности под одним типом только потому, что сейчас они выглядят как числа.
2. Установить максимальное допустимое значение по правилам системы, а не по текущим данным. Для монотонного счётчика учитывать срок жизни проекта и темп прироста.
3. Выбрать минимальный тип, который покрывает диапазон с запасом, предусмотренным спецификацией.
4. Проверить совместимость типа на всех границах: SQL-драйвер, приложение, API, очередь сообщений и аналитическое хранилище.
У целого поля есть жёсткое свойство: дробная часть ему не принадлежит. Если приложение отправит значение с дробью, результат зависит от СУБД, приведения типов и конкретного выражения. Рассчитывать на неявное преобразование нельзя. Оно может усечь значение или вызвать ошибку. В любом случае логика округления должна быть задана явно.
FLOAT и DOUBLE PRECISION: быстрые, но приближённые
REAL и DOUBLE PRECISION используют двоичное представление чисел с плавающей точкой по IEEE 754. В распространённой терминологии REAL соответствует FLOAT4 и занимает 4 байта; DOUBLE PRECISION, или FLOAT8, занимает 8 байт.
Ориентир по точности — около 6 значащих десятичных цифр для FLOAT4 и до 15 для FLOAT8. Это не означает, что тип гарантирует ровно столько цифр после запятой. Значащие цифры относятся ко всему числу. У значения с большой целой частью на дробную часть останется меньше точности.
Причина — не дефект конкретной СУБД. Десятичные дроби кодируются в двоичном формате, и многие из них не имеют конечного двоичного представления. Поэтому хранимое значение может быть ближайшим представимым числом, а не исходной десятичной записью. При последовательности операций малое отклонение может переноситься дальше и становиться заметным при сравнении или суммировании.
Это ожидаемое поведение для приближённой арифметики. Оно приемлемо, когда задача допускает небольшую погрешность: например, при работе с измерениями, физическими моделями или промежуточными расчётами, где результат интерпретируется с заданной точностью. Оно не подходит для полей, где десятичное значение должно сохраняться точно.
FLOAT хранит не десятичную запись, а ближайшее представимое двоичное значение. Совпадение на экране не доказывает совпадение внутри БД.Типичная ошибка — сравнивать результат вычисления с точным равенством. Для приближённых значений условие вроде value = expected может не сработать, даже если разница незаметна при выводе. В прикладной логике используют допустимую погрешность, соответствующую предметной области, либо переводят расчёт в целочисленные единицы, если такой перевод корректен.
Например, сравнение измерения с порогом можно строить через интервал допуска, но величину допуска нельзя назначать произвольно. Её задаёт модель задачи: точность датчика, правила округления или требования расчёта. Подстановка случайного эпсилона лишь маскирует проблему.
В MySQL атрибут UNSIGNED для FLOAT и DOUBLE начиная с версии 8.0.17 объявлен нерекомендуемым. Если нужно запретить отрицательные значения, это следует выразить ограничением схемы и проверкой входных данных, а не опираться на устаревающий атрибут типа.
DECIMAL и NUMERIC: точность задаётся схемой
Для десятичных значений, которые должны сохраняться без двоичной погрешности, применяют DECIMAL или NUMERIC. В MySQL и PostgreSQL эти названия используются как синонимы. Объявление обычно задаёт общую точность и масштаб:
DECIMAL(M, D)
Здесь M — общее число значащих цифр, D — число цифр после десятичной точки. Например, поле DECIMAL(12, 2) допускает до 12 цифр всего, из них две — после точки. Это не просто формат отображения. Параметры определяют, какие значения схема может хранить и с какой дробной частью.
Для суммы с фиксированным числом десятичных разрядов DECIMAL часто является правильным выбором. То же относится к тарифам, ставкам и другим значениям, где округление должно быть предсказуемым. При этом тип не отменяет необходимости определить правила округления. Он сохраняет десятичную арифметику, но разработчик всё равно должен решить, на каком этапе и по какому правилу приводить результат к требуемому масштабу.
Допустимые пределы зависят от СУБД. В MySQL для DECIMAL и NUMERIC предусмотрено до 65 цифр в параметре M. В PostgreSQL спецификация допускает до 131 072 цифр до десятичной точки и до 16 383 после неё. Эти верхние значения не являются рекомендацией для прикладной схемы. Поле с чрезмерной точностью усложняет вычисления и обычно сигнализирует, что модель данных не определила реальные границы значения.
При проектировании нужно ответить на два вопроса:
- Какой максимальный диапазон требуется целой части?
- Сколько знаков после точки имеют смысл по правилам предметной области?
Если хранить цену с точностью до двух знаков, но расчёт ставки требует большего масштаба, нельзя бездумно использовать один и тот же тип для обеих величин. Цена, ставка и промежуточный результат могут иметь разные требования. Потеря точности часто происходит не при записи исходного значения, а при промежуточном вычислении или приведении результата к масштабу поля.
Не следует также считать, что отображаемое число полностью описывает способ хранения. Сериализатор, драйвер или ORM может преобразовать NUMERIC в тип приложения с другой моделью точности. Например, если слой приложения разбирает значение как двоичное число с плавающей точкой, точность теряется уже после чтения из БД. Тип столбца не защищает данные от некорректного преобразования на выходе.
Проверьте путь значения целиком: ввод, валидация, параметр SQL-запроса, вычисление в СУБД, передача через драйвер и сериализация ответа. Числовое поле — часть контракта между компонентами, а не изолированная декларация в CREATE TABLE.
Цена точности: арифметика, индексы и запросы
NUMERIC и DECIMAL выполняют десятичные вычисления программно. Это позволяет получать точные результаты в десятичной системе, но такие операции медленнее аппаратной арифметики над FLOAT или целыми типами. Универсального коэффициента замедления нет: он зависит от СУБД, версии, аппаратной платформы, точности и характера запроса.
Поэтому утверждение, что один тип всегда быстрее другого, не является спецификацией. Для конкретной нагрузки нужен бенчмарк на целевой СУБД и данных, близких к боевым. Синтетическое сложение двух чисел не покажет стоимость реального запроса с фильтрацией, сортировкой, агрегацией и индексами.
Отдельный фактор — размер и точность DECIMAL. Чем шире заданная точность, тем больше работы может потребоваться для обработки значения. Но это не повод заменять точный тип приближённым там, где ошибка недопустима. Оптимизировать нужно запрос и модель, не меняя смысл данных ради абстрактной скорости.
Для проверки производительности зафиксируйте сценарий:
- используйте одинаковый набор данных и одинаковые запросы;
- сравнивайте те типы, которые действительно рассматриваются для схемы;
- включите операции чтения, записи, агрегирования и сортировки, если они есть в приложении;
- измеряйте план и время выполнения на той версии СУБД, где будет работать система;
- отдельно проверьте корректность результата, включая крайние значения и округление.
Результат бенчмарка отвечает на вопрос о конкретной конфигурации. Он не доказывает, что тип быстрее во всех приложениях. Если узкое место — сеть, блокировки или неоптимальный план запроса, замена NUMERIC на FLOAT может ускорить арифметическую часть и не изменить общую задержку заметным образом.
Индексы тоже требуют внимания. Различие типов в сравниваемых выражениях может привести к неявному приведению и повлиять на использование индекса. Сравнивайте значения одинаковых типов и проверяйте план выполнения после изменения схемы. Миграция поля — не косметическая правка: она может затронуть индексы, ограничения, совместимость с кодом и формат данных в API.
Как выбрать тип для конкретного поля
Рабочий алгоритм начинается не с вопроса «какой тип быстрее», а с вопроса «какую ошибку система не имеет права допустить».
| Сценарий | Типичный выбор | Что зафиксировать |
|---|---|---|
| Счётчик, количество, код | SMALLINT, INTEGER или BIGINT | Диапазон, допустимость отрицательных значений, предел роста |
| Денежная сумма или тариф | DECIMAL / NUMERIC | Масштаб, правила округления, максимальную сумму |
| Научное или инженерное измерение | REAL или DOUBLE PRECISION | Допустимую погрешность, диапазон, требования к сравнению |
| Дробное значение, которое можно хранить в минимальных единицах | Целое число | Единицу хранения и преобразование при вводе и выводе |
Хранение суммы в целых минимальных единицах может быть корректной альтернативой, если единица однозначно определена и вычисления не требуют промежуточной дробной точности. Тогда поле хранит, например, не условные 12,34, а целое количество минимальных единиц. Но это решение должно быть частью контракта: клиент, сервер, отчёт и интеграции обязаны одинаково трактовать масштаб. Иначе вместо погрешности FLOAT появится систематическая ошибка преобразования.
Последовательность проектирования числового поля выглядит так:
1. Опишите смысл значения. Число может выглядеть одинаково в интерфейсе, но быть суммой, измерением, счётчиком или долей. Семантика определяет тип.
2. Запишите допустимые границы. Укажите минимальное и максимальное значения, а для дробных данных — допустимый масштаб.
3. Выберите модель точности. Если десятичный результат обязан быть точным, используйте DECIMAL/NUMERIC или подходящее целое представление. Если погрешность допустима, можно рассматривать плавающую точку.
4. Определите правила округления. Укажите, где выполняется округление: при вводе, после каждой операции, перед сохранением или только при выводе.
5. Проверьте вычисления и интеграции. Сравните поведение СУБД, драйвера, приложения и сериализатора на крайних значениях.
6. Измерьте реальную нагрузку. Если производительность критична, проведите бенчмарк вместо предположений о скорости типа.
Типовые дефекты возникают в одних и тех же местах. Поле объявляют как FLOAT, потому что значение дробное, хотя требуется точная десятичная сумма. DECIMAL задают с произвольным масштабом, после чего приложение округляет по другим правилам. Целочисленный счётчик выбирают без оценки верхней границы. Наконец, миграцию проводят только в БД, забывая о типе в коде и формате API.
Исправление после накопления данных сложнее, чем корректный выбор при создании схемы. Перед изменением типа нужно определить, как преобразуются существующие значения, какие строки выйдут за новый диапазон и какие результаты изменятся после округления. Преобразование FLOAT в DECIMAL не восстанавливает исходную десятичную запись: оно фиксирует значение, которое уже было сохранено приближённо.
Ограничения, которые следует закрепить в спецификации
У типа поля есть пределы, но сама декларация не описывает все правила предметной области. В спецификации числовых данных должны быть как минимум диапазон, точность, масштаб, допустимость отрицательных значений и правило округления. Если значение приходит через API, тот же контракт нужно выразить в схеме API и проверить при валидации.
INTEGER не становится безопасным только потому, что переполнение пока не наблюдалось. DOUBLE PRECISION не превращается в точный тип после вывода числа с ограниченным количеством знаков. DECIMAL не гарантирует корректность, если приложение переводит значение во float. Каждый слой должен сохранять выбранную модель данных.
Итоговая граница выбора проста: целые значения хранятся в целочисленном типе; точные десятичные — в DECIMAL или NUMERIC; приближённые измерения и расчёты — в REAL или DOUBLE PRECISION, если предметная область допускает погрешность. Затем следует проверить диапазон, округление, преобразования и нагрузку.
Числовое поле — часть архитектуры. Его нельзя выбирать по внешнему виду числа или привычке разработчика. Нужна спецификация; без неё любой тип станет источником скрытого дефекта.