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:
2.3 写入decimal数据
可以向其中插入合法的数据, 例如
insert into t values(123.4);
此时履行 select * from t 会得到
| d |
+——–+
| 123.45 |
| 123.40 |
+——–+
注意到 123.4 变成了 123.40, 这就是精确类型的特点, d 列的每行数据都要求 scale=2, 即小数点后有两位
当插入不满足 precision 和 scale 定义的数据时
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 的内存格式和计算函数实现问题, 这个大小不是在所有情况都能到达, 将在后文中详细介绍. 继续上面的例子中：
+————–+
| d + 9999.999 |
+————–+
| 10123.459 |
+————–+
结果突破了 precision=5, scale=2 的限制, 这里触及运算时 scale 的变化, 基本规则是：
- 加法/减法/sum：取两边最大的 scale
- 乘法：两边的 scale 相加
- 除法：被除数的 scale + div_precision_increment(取决于数据库实现)
3 Decimal类型的实现
在这一部份中, 我们主要介绍 MySQL 的 decimal 实现, 另外也会对照 ClickHouse, 看看 decimal 在区别系统中的设计与实现差异.
实现 decimal 需要思考以下问题
- 支持多大的 precision 和 scale
- 在哪里存储 scale
- 在连续乘法或除法时, scale 不断增长, 整数部份也不断扩大, 而存储的 buffer 大小总是有上限的, 此时应当怎么处理？
- 除法可能产生无穷小数, 如何决定除法结果的 scale?
- decimal 的表示范围和计算性能会不会有冲突, 会不会可以统筹
3.1 MySQL
先来看看 MySQL decimal 相关的数据结构
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 实际有效的部份, 则是由 intg 和 frac 共同决定. 例如：
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
再来看一个大一点的例子：
// 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 和 strings/decimal.cc 中的 decimal_add, decimal_mul, decimal_div 等代码.
准备步骤
在真正计算前, 还需要做一些准备工作：
- MySQL 会将数字的个数 ROUND_UP 到 9 的整数倍, 这样后面就能够依照 decimal_digit_t 为单位来进行计算
- 另外还要针对参与运算的两个 decimal 的具体情况, 计算结果的 precision 和 scale, 如果发现结果的 precision 超过了支持的上限, 那末会依照 decimal_digit_t 为单位减少小数的数字.
- 在乘法进程中, 如果产生了 2 中的减少行动, 则需要 TRUNCATE 两个运算数, 避免中间结果超越范围.
加法主要步骤
首先, 由于两个数字的 precision 和 scale 可能不相同, 需要做一些准备工作, 将小数点对齐, 然后开始计算, 从最末尾小数开始向高位加, 分为三个步骤：
- 将小数较多的 decimal 多出的小数数字复制到结果中
- 将两个 decimal 公共的部份相加
- 将整数较多的 decimal 多出的整数数字与进位相加到结果中
代码中使用了 stop, stop2 来标记小数点对齐后, 长度区别的数字出现差异的位置.
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 longlong dec2;
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) {
if (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, 重复这个进程.
想更详细地了解这个算法可以参考 https://skanthak.homepage.t-online.de/division.html
if (likely(len2 > 0)) norm2 += (dec1)(norm_factor * start2[1] / DIG_BASE)；
y = start1[1];
guess = (norm_factor * x + norm_factor * y / DIG_BASE) / norm2;
dec1 hi, lo;
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, 进行简化后大致可以表示以下:
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：
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>;
using Decimal128 = Decimal<Int128>;
不言而喻, 这样的表示方法相较于 MySQL 的方法更轻量, 但是范围更小, 同时也带来了一个问题是没有小数点的位置, 在进行加减法、大小比较等需要小数点对齐的场景下, ClickHouse 会在运算实际产生的时候将 scale 以参数的情势传入, 此时配合上面的数字就能够正确地还原出真实的 decimal 值了.
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 等原生类型计算就能够了, 这样大大提升了速度.
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);
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 避免溢出时的未定义行动.
inline bool mulOverflow(T x, T y, T & res)
{
return __builtin_mul_overflow(x, y, &res);
}
template <>
inline bool mulOverflow(__int128 x, __int128 y, __int128 & res)
{
res = static_cast<unsigned __int128>(x) * static_cast<unsigned __int128>(y); /// Avoid signed integer overflow.
if (!x || !y)
return false;
unsigned __int128 a = (x > 0) ? x : -x;
unsigned __int128 b = (y > 0) ? y : -y;
return (a * b) / b != a;
}
除法主要步骤
先转换 scale 再直接做整数除法. 本身来说除法和乘法一样是不需要对齐小数点的, 但是除法不一样的地方在于可能会产生无穷小数, 所以一般数据库都会给结果一个固定的小数位数, ClickHouse 选择的小数位数是和被除数一样, 因此需要将 a 乘上 scale, 然后在除法运算的进程中, 这个 scale 被自然减去, 得到结果的小数位数就能够保持和被除数一样.
if constexpr (!IsDecimalNumber<A>)
overflow |= common::mulOverflow(scale, scale, scale);
overflow |= common::mulOverflow(a, scale, a);
if (overflow)
throw Exception(“Decimal math overflow”, ErrorCodes::DECIMAL_OVERFLOW);
return Op::template apply<NativeResultType>(a, b);
3.3 总结
MySQL 通过一个 int32 的数组来表示一个大数, ClickHouse 则是尽量使用原生类型, GCC 和 Clang 都支持 int128 扩大, 这使得 ClickHouse 的这类做法可以比较方便地实现.
MySQL 与 ClickHouse 的实现差别或者比较大的, 针对我们开始提到的问题, 分别来看看他们的解答.
- precision 和 scale 范围, MySQL 最高可定义 precision=65, scale=30, 中间结果最多包括 81 个数字, ClickHouse 最高可定义 precision=38, scale=37, 中间结果最大为 int128 的最大值 ⑵^127 ~ 2^127⑴.
- 在哪里存储 scale, MySQL 是行式存储, 使用火山模型逐行迭代, 计算也是按行进行, 每一个 decimal 都有自己的 scale；ClickHouse 是列式存储, 计算按列批量进行, 每行依照相同的 scale 处理能提升性能, 因此 scale 来自表达式解析进程中推导出来的类型.
- scale 增长, scale 增长超过极限时, MySQL 会通过动态挤占小数空间, truncate 运算数, 尽量保证计算完成, ClickHouse 会直接报溢出错.
- 除法 scale, MySQL 通过 div_prec_increment 来控制除法结果的 scale, ClickHouse 固定使用被除数的 scale.
- 性能, MySQL 使用了更宽的 decimal 表示, 同时要进行 ROUND_UP, 小数挤占, TRUNCATE 等动作, 性能较差, ClickHouse 使用原生的数据类型和计算最大限度地提升了性能.
4. MySQL 违背直觉的地方
在这一部份中, 我们将讲述一些 MySQL 实现酿成的违背直觉的地方. 这些行动通常产生在运算结果接近 81 digit 时, 因此如果可以保证运算结果的范围较小也能够疏忽这些问题.
- 乘法的 scale 会截断到 31, 且该截断是通过截断运算数字的方式来实现的, 例如: select 10000000000000000000000000000000.100000000 10000000000000000000000000000000 = 10000000000000000000000000000000.100000000000000000000000000000 10000000000000000000000000000000.555555555555555555555555555555 返回 1, 第二个运算数中的 .555555555555555555555555555555 全部被截断
- MySQL 使用的 buffer 包括了 81 个 digit 的容量, 但是由于小数部份一定要和整数部份分开, 因此很多时候没法用满 81 个 digit, 例如: select 99999999999999999999999999999999999999999999999999999999999999999999999999.999999 = 99999999999999999999999999999999999999999999999999999999999999999999999999.9 返回 1
- 计算进程中如果发现整数部份太大会动态地挤占小数部份, 例如: select 999999999999999999999999999999999999999999999999999999999999999999999999.999999999 + 999999999999999999999999999999999999999999999999999999999999999999999999.999999999 = 999999999999999999999999999999999999999999999999999999999999999999999999 + 999999999999999999999999999999999999999999999999999999999999999999999999 返回 1
- 除法计算中间结果不受 scale = 31 的限制, 除法中间结果的 scale 一定是 9 的整数倍, 不能依照终究结果来推测除法作为中间结果的精度, 例如 select 2.0000 / 3 3 返回 2.00000000, 而 select 2.00000 / 3 3 返回 1.999999998, 可见前者除法的中间结果其实保存了更多的精度.
- 除法, avg 计算终究结果的小数部份如果正好是 9 的倍数, 则不会四舍五入, 例如: select 2.00000 / 3 返回 0.666666666, select 2.0000 / 3 返回 0.66666667
- 除法, avg 计算时, 运算数字的小数部份如果不是 9 的倍数, 那末会实际上存储 9 的倍数个小数数字, 因此会出现以下差异：
insert into t1 values (100000.20, 1000000.10, 5);
insert into t1 values (200000.20, 2000000.10, 2);
insert into t1 values (300000.20, 3000000.10, 4);
insert into t1 values (400000.20, 4000000.10, 6);
insert into t1 values (500000.20, 5000000.10, 8);
insert into t1 values (600000.20, 6000000.10, 9);
insert into t1 values (700000.20, 7000000.10, 8);
insert into t1 values (800000.20, 8000000.10, 7);
insert into t1 values (900000.20, 9000000.10, 7);
insert into t1 values (1000000.20, 10000000.10, 2);
insert into t1 values (2000000.20, 20000000.10, 5);
insert into t1 values (3000000.20, 30000000.10, 2);
select sum(a+b), avg(c), sum(a+b) / avg(c) from t1;
+————–+——–+——————-+
| sum(a+b) | avg(c) | sum(a+b) / avg(c) |
+————–+——–+——————-+
| 115500003.60 | 5.4167 | 21323077.590317 |
+————–+——–+——————-+
1 row in set (0.01 sec)
select 115500003.60 / 5.4167;
+———————–+
| 115500003.60 / 5.4167 |
+———————–+
| 21322946.369561 |
+———————–+
1 row in set (0.00 sec)
总结
到此这篇关于数据库中Decimal类型使用和实现方法的文章就介绍到这了,更多相关Decimal类型使用及实现内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持！
本文来源：https://www.yuntue.com/post/149950.html | 云服务器网，转载请注明出处！
微信扫一扫打赏
支付宝扫一扫打赏
