深⼊讲解数据库中Decimal类型的使⽤以及实现⽅法
⽬录
1 背景
2 Decimal类型的使⽤
2.1 描述Decimal
2.2 建表时定义Decimal
2.3 写⼊decimal数据
2.4 取出deimcal进⾏计算
3 Decimal类型的实现
3.1 MySQL
3.2 ClickHouse
3.3 总结
4. MySQL 违反直觉的地⽅
总结
1 背景
数字运算在数据库中是很常见的需求, 例如计算数量、重量、价格等, 为了满⾜各种需求, 数据库系统通常⽀持精准的数字类型和近似的数字类型. 精准的数字类型包含 int, decimal 等, 这些类型在计算过程中⼩数点位置是固定的, 其结果和⾏为⽐较可预测. 当涉及钱时, 这个问题尤其重要, 因此部分数据库实现了专门的 money 类型. 近似的数字类型包含 float, double 等, 这些数字的精度是浮动的.
本⽂将简要介绍 decimal 类型的数据结构和计算, 对⽐ decimal 在 MySQL, ClickHouse 两个不同类型系统中的实现差异, 描述实现 decimal 运算的主要思路. MySQL 在结果的长度⽐较接近上限的情况下, 会有⽐较违反直觉的地⽅, 本⽂会在最后列出这些可能需要注意的问题.
2 Decimal类型的使⽤
decimal 的使⽤在多数数据库上都差不多, 下⾯以 MySQL 的 decimal 为例, 介绍 decimal 的基本使⽤⽅法.
2.1 描述Decimal
与 float 和 double 不同, decimal 在创建时需要指定两个描述精度的数字, 分别是 precision 和 scale, precision 指整个 decimal 包括整数和⼩数部分⼀共有多少个数字, scale 指 decimal 的⼩数部分包含多少个数字, 例如：123.45 就是⼀个 precision=5, scale=2 的 decimal.  我们可以在建表时按照这种⽅式定义我们想要的 decimal.
2.2 建表时定义Decimal
可以在建表时这样定义⼀个 decimal:
create table t(d decimal(5, 2));
2.3 写⼊decimal数据
可以向其中插⼊合法的数据, 例如
insert into t values(123.45);
insert into t values(123.4);
此时执⾏ select * from t 会得到
+--------+
| d      |
+--------+
| 123.45 |
| 123.40 |
+--------+
注意到 123.4 变成了 123.40, 这就是精确类型的特点, d 列的每⾏数据都要求 scale=2, 即⼩数点后有两位
当插⼊不满⾜ precision 和 scale 定义的数据时
insert into t values(1123.45);
ERROR 1264 (22003): Out of range value for column 'd' at row 1
insert into t values(123.456);
Query OK, 1 row affected, 1 warning
show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'd' at row 1 |
+-------+------+----------------------------------------+
select * from t;
+--------+
| d      |
+--------+
| 123.46 |
+--------+
类似 1234.5 (precision=5, scale=1)这样的数字看起来满⾜要求, 但实际上需要满⾜ scale=2 的要求, 因此会变成
1234.50(precision=6, scale=2) 也不满⾜要求.
2.4 取出deimcal进⾏计算
计算的结果不受定义的限制, ⽽是受到内部实现格式的影响, 对于 MySQL 结果最⼤可以到 precision=81, scale=30, 但是由于MySQL decimal 的内存格式和计算函数实现问题, 这个⼤⼩不是在所有情况都能达到, 将在后⽂中详细介绍. 继续上⾯的例⼦中：
select d + 9999.999 from t;
+--------------+
| d + 9999.999 |
+--------------+
|    10123.459 |
+--------------+
结果突破了 precision=5, scale=2 的限制, 这⾥涉及运算时 scale 的变化, 基本规则是：
1. 加法/减法/sum：取两边最⼤的 scale
2. 乘法：两边的 scale 相加
3. 除法：被除数的 scale + div_precision_increment(取决于数据库实现)
3 Decimal类型的实现
在这⼀部分中, 我们主要介绍 MySQL 的 decimal 实现, 此外也会对⽐ ClickHouse, 看看 decimal 在不同系统中的设计与实现差异.
实现 decimal 需要思考以下问题
1. ⽀持多⼤的 precision 和 scale
2. 在哪⾥存储 scale
3. 在连续乘法或除法时, scale 不断增长, 整数部分也不断扩⼤, ⽽存储的 buffer ⼤⼩总是有上限的, 此时应该如何处理？
4. 除法可能产⽣⽆限⼩数, 如何决定除法结果的 scale?
5. decimal 的表⽰范围和计算性能是否有冲突, 是否可以兼顾
3.1 MySQL
先来看看 MySQL decimal 相关的数据结构
typedef int32 decimal_digit_t;
struct decimal_t {
int intg, frac, len;
bool sign;
decimal_digit_t *buf;
};
MySQL 的 decimal 使⽤⼀个长度为 len 的 decimal_digit_t (int32) 的数组 buf 来存储 decimal 的数字, 每个 decimal_digit_t 最多存储 9 个数字, ⽤ intg 表⽰整数部分的数字个数, frac 表⽰⼩数部分的数字个数, sign 表⽰符号. ⼩数部分和整数部分需要分开存储, 不能混合在⼀个 decimal_digit_t 中, 两部分都向⼩数点对齐, 这是因为整数和⼩数通常需要分开计算, 所以这样的格式可以更容易地将不同 decimal_t ⼩数和整数分别对齐, 便于加减法运算. len 在 MySQL 实现中恒为 9, 它表⽰存储的上限, ⽽ buf
// 123.45 decimal(5, 2) 整数部分为 3,  ⼩数部分为 2
decimal_t dec_123_45 = {
int intg = 3;
易论坛
int frac = 2;
int len = 9;
bool sign = false;
decimal_digit_t *buf = {123, 450000000, ...};
};
MySQL 需要使⽤两个 decimal_digit_t (int32) 来存储 123.45, 其中第⼀个为 123, 结合 intg=3, 它就表⽰整数部分为 123, 第⼆个数字为 450000000 (共 9 个数字), 由于 frac=2, 它表⽰⼩数部分为 .45
免费模板素材网站
再来看⼀个⼤⼀点的例⼦：
// decimal(81, 18) 63 个整数数字, 18 个⼩数数字, ⽤满整个 buffer
width用英语怎么说// 123456789012345678901234567890123456789012345678901234567890123.012345678901234567
decimal_t dec_81_digit = {
int intg = 63;
int frac = 18;
int len = 9;
bool sign = false;
buf = {123456789, 12345678, 901234567, 890123456, 789012345, 678901234, 567890123, 12345678, 901234567}
};
这个例⼦⽤满了 81 个数字, 但是也有些场景⽆法⽤满 81 个数字, 这是因为整数和⼩数部分是分开存储的, 所以⼀个
decimal_digit_t (int32) 可能只存储了⼀个有效的⼩数数字, 但是其余的部分没有办法给整数部分使⽤, 例如⼀个 decimal 整数部分有 62 个数字, ⼩数部分有 19 个数字(precision=81, scale=19), 那么⼩数部分需要使⽤ 3 个 decimal_digit_t (int32), 整数部分还有 54 个数字的余量, ⽆法存下 62 个数字. 这种情况下, MySQL 会优先满⾜整数部分的需求, ⾃动截断⼩数点后的部分, 将它变成 decimal(80, 18)
接下来看看 MySQL 如何在这个数据结构上进⾏运算. MySQL 通过⼀系列 decimal_digit_t(int32) 来表⽰⼀个较⼤的 decimal，其计算也是对这个数组中的各个 decimal_digit_t 分别进⾏, 如同我们在⼩学数学计算时是⼀个数字⼀个数字地计算, MySQL 会把每个 decimal_digit_t 当作⼀个数字来进⾏计算、进位. 由于代码较长, 这⾥不再对具体的代码进⾏完整的分析, 仅对代码中核⼼部分进⾏分析, 如果感兴趣,
可以直接参考 MySQL 源码 strings/decimal.h 和 中的 decimal_add, decimal_mul, decimal_div 等代码.
准备步骤
在真正计算前, 还需要做⼀些准备⼯作：
1. MySQL 会将数字的个数 ROUND_UP 到 9 的整数倍, 这样后⾯就可以按照 decimal_digit_t 为单位来进⾏计算
2. 此外还要针对参与运算的两个 decimal 的具体情况, 计算结果的 precision 和 scale, 如果发现结果的 precision 超过了⽀
持的上限, 那么会按照 decimal_digit_t 为单位减少⼩数的数字.
3. 在乘法过程中, 如果发⽣了 2 中的减少⾏为, 则需要 TRUNCATE 两个运算数, 避免中间结果超出范围.
加法主要步骤
⾸先, 因为两个数字的 precision 和 scale 可能不相同, 需要做⼀些准备⼯作, 将⼩数点对齐, 然后开始计算, 从最末尾⼩数开始向⾼位加, 分为三个步骤：
1. 将⼩数较多的 decimal 多出的⼩数数字复制到结果中
2. 将两个 decimal 公共的部分相加
3. 将整数较多的 decimal 多出的整数数字与进位相加到结果中
代码中使⽤了 stop, stop2 来标记⼩数点对齐后, 长度不同的数字出现差异的位置.
/* part 1 - max(frac) ... min (frac) */
while (buf1 > stop) *--buf0 = *--buf1;
/* part 2 - min(frac) ... min(intg) */
carry = 0;
while (buf1 > stop2) {
ADD(*--buf0, *--buf1, *--buf2, carry);
}
/* part 3 - min(intg) ... max(intg) */
buf1 = intg1 > intg2 ? ((stop3 = from1->buf) + intg1 - intg2)
: ((stop3 = from2->buf) + intg2 - intg1);
while (buf1 > stop3) {
ADD(*--buf0, *--buf1, 0, carry);
乘法主要步骤
乘法引⼊了⼀个新的 dec2, 表⽰⼀个 64 bit 的数字, 这是因为两个 decimal_digit_t(int32) 相乘后得到的可能会是⼀个 64 bit 的数字. 在计算时⼀定要先把类型转换到 dec2(int64), 再计算, 否则会得到溢出后的错误结果. 乘法与加法不同, 乘法不需要对齐，例如计算 11.11 5.0, 那么只要计算 111150=55550, 再移动⼩数点位置就能得到正确结果 55.550
MySQL 实现了⼀个双重循环将 decimal1 的每⼀个 decimal_digit_t 与 decimal2 的每⼀个 decimal_digit_t 相乘, 得到⼀个 64位的 dec2,  其低 32 位是当前的结果, 其⾼ 32 位是进位.
typedef decimal_digit_t dec1;
typedef longlong dec2;
for (buf1 += frac1 - 1; buf1 >= stop1; buf1--, start0--) {
carry = 0;
for (buf0 = start0, buf2 = start2; buf2 >= stop2; buf2--, buf0--) {
dec1 hi, lo;
dec2 p = ((dec2)*buf1) * ((dec2)*buf2);
hi = (dec1)(p / DIG_BASE);
lo = (dec1)(p - ((dec2)hi) * DIG_BASE);
ADD2(*buf0, *buf0, lo, carry);
carry += hi;
}
if (carry) {
headache前面为什么加aif (buf0 < to->buf) return E_DEC_OVERFLOW;
ADD2(*buf0, *buf0, 0, carry);
}
for (buf0--; carry; buf0--) {
if (buf0 < to->buf) return E_DEC_OVERFLOW;
ADD(*buf0, *buf0, 0, carry);
}
}
除法主要步骤
除法使⽤的是 Knuth's Algorithm D, 其基本思路和⼿动除法也⽐较类似.
⾸先使⽤除数的前两个 decimal_digit_t 组成⼀个试商因数, 这⾥使⽤了⼀个 norm_factor 来保证数字在不溢出的情况下尽可能扩⼤, 这是因为 decimal 为了保证精度必须使⽤整形来进⾏计算, 数字越⼤, 得到的结果就越准确. D3: 猜商, 就是⽤被除数的前两个 decimal_digit_t 除以试商因数这⾥如果不乘 norm_factor, 则 start1[1] 和 start2[1] 都不会体现在结果之中.
D4: 将 guess 与除数相乘, 再从被除数中剪掉结果然后做⼀些修正, 移动向下⼀个 decimal_digit_t, 重复这个过程.
norm2 = (dec1)(norm_factor * start2[0]);
if (likely(len2 > 0)) norm2 += (dec1)(norm_factor * start2[1] / DIG_BASE)；
x = start1[0] + ((dec2)dcarry) * DIG_BASE;
y = start1[1];
guess = (norm_factor * x + norm_factor * y / DIG_BASE) / norm2;
for (carry = 0; buf2 > start2; buf1--) {
dec1 hi, lo;mysql面试题及讲解
x = guess * (*--buf2);
hi = (dec1)(x / DIG_BASE);
lo = (dec1)(x - ((dec2)hi) * DIG_BASE);
SUB2(*buf1, *buf1, lo, carry);
carry += hi;
}
carry = dcarry < carry;
3.2 ClickHouse
ClickHouse 是列存, 相同列的数据会放在⼀起, 因此计算时通常也将⼀列的数据合成 batch ⼀起计算.
⼀列的 batch 在 ClickHouse 中使⽤ PODArray, 例如上图中的 c1 在计算时就会有⼀个 PODArray, 进⾏简化后⼤致可以表⽰如下:
class PODArray {
char * c_start          = null;
char * c_end            = null;
char * c_end_of_storage = null;
在计算时会讲 c_start 指向的数组转换成实际的类型, 对于 decimal, ClickHouse 使⽤⾜够⼤的 int 来表⽰, 根据 decimal 的precision 选择 int32, int64 或者 int128. 例如⼀个 decimal(10, 2), 123.45, 使⽤这样⽅式可以表⽰为⼀个 int32_t, 其内容为12345, decimal(10, 3) 的 123.450 表⽰为 123450. ClickHouse ⽤来表⽰每个 decimal 的结构如下, 实际上就是⾜够⼤的 int：template <typename T>
struct Decimal
{
using NativeType = T;
// ...
T value;
};
using Int32 = int32_t;
using Int64 = int64_t;
using Int128 = __int128;
using Decimal32 = Decimal<Int32>;
using Decimal64 = Decimal<Int64>;php7下载64位教程
using Decimal128 = Decimal<Int128>;
显⽽易见, 这样的表⽰⽅法相较于 MySQL 的⽅法更轻量, 但是范围更⼩, 同时也带来了⼀个问题是没有⼩数点的位置, 在进⾏加减法、⼤⼩⽐较等需要⼩数点对齐的场景下, ClickHouse 会在运算实际发⽣的时候将 scale 以参数的形式传⼊, 此时配合上⾯的数字就可以正确地还原出真实的 decimal 值了.
ResultDataType type = decimalResultType(left, right, is_multiply, is_division);
int scale_a = type.scaleFactorFor(left, is_multiply);
int scale_b = type.scaleFactorFor(right, is_multiply || is_division);
OpImpl::vector_vector(col_left->getData(), col_right->getData(), vec_res,
scale_a, scale_b, check_decimal_overflow);
例如两个 decimal: a = 123.45000(p=8, s=5), b = 123.4(p=4, s=1), 那么计算时传⼊的参数就是  col_left->getData() =
123.45000 10 ^ 5 = 12345000, scale_a = 1, col_right->getData() = 123.4 10 ^ 1 = 1234, scale_b = 10000, 12345000 1 和1234 10000 的⼩数点位置是对齐的, 可以直接计算.
加法主要步骤
ClickHouse 实现加法同样要先对齐, 对齐的⽅法是将 scale 较⼩的数字乘上⼀个系数, 使两边的 scale 相等. 然后直接做加法即可. ClickHouse 在计算中也根据 decimal 的 precision 进⾏了细分, 对于长度没那么长的 decimal, 直接⽤ int32, int64 等原⽣类型计算就可以了, 这样⼤⼤提升了速度.
bool overflow = false;
if constexpr (scale_left)
overflow |= common::mulOverflow(a, scale, a);
else
overflow |= common::mulOverflow(b, scale, b);
overflow |= Op::template apply<NativeResultType>(a, b, res);
template <typename T>
inline bool addOverflow(T x, T y, T & res)
{
return __builtin_add_overflow(x, y, &res);
}
template <>
inline bool addOverflow(__int128 x, __int128 y, __int128 & res)
{
static constexpr __int128 min_int128 = __int128(0x8000000000000000ll) << 64;
static constexpr __int128 max_int128 = (__int128(0x7fffffffffffffffll) << 64) + 0xffffffffffffffffll;
res = x + y;
return (y > 0 && x > max_int128 - y) || (y < 0 && x < min_int128 - y);
}
乘法主要步骤
同 MySQL, 乘法不需要对齐, 直接按整数相乘就可以了, ⽐较短的 decimal 同样可以使⽤ int32, int64 原⽣类型. int128 在溢出检测时被转换成 unsigned int128 避免溢出时的未定义⾏为.
template <typename T>
inline bool mulOverflow(T x, T y, T & res)
{
return __builtin_mul_overflow(x, y, &res);
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。