1 背景

數(shù)字運(yùn)算在數(shù)據(jù)庫(kù)中是很常見(jiàn)的需求, 例如計(jì)算數(shù)量、重量、價(jià)格等, 為了滿足各種需求, 數(shù)據(jù)庫(kù)系統(tǒng)通常支持精準(zhǔn)的數(shù)字類型和近似的數(shù)字類型. 精準(zhǔn)的數(shù)字類型包含 int, decimal 等, 這些類型在計(jì)算過(guò)程中小數(shù)點(diǎn)位置是固定的, 其結(jié)果和行為比較可預(yù)測(cè). 當(dāng)涉及錢時(shí), 這個(gè)問(wèn)題尤其重要, 因此部分?jǐn)?shù)據(jù)庫(kù)實(shí)現(xiàn)了專門的 money 類型. 近似的數(shù)字類型包含 float, double 等, 這些數(shù)字的精度是浮動(dòng)的.

本文將簡(jiǎn)要介紹 decimal 類型的數(shù)據(jù)結(jié)構(gòu)和計(jì)算, 對(duì)比 decimal 在 MySQL, ClickHouse 兩個(gè)不同類型系統(tǒng)中的實(shí)現(xiàn)差異, 描述實(shí)現(xiàn) decimal 運(yùn)算的主要思路. MySQL 在結(jié)果的長(zhǎng)度比較接近上限的情況下, 會(huì)有比較違反直覺(jué)的地方, 本文會(huì)在最后列出這些可能需要注意的問(wèn)題.

2 Decimal類型的使用

decimal 的使用在多數(shù)數(shù)據(jù)庫(kù)上都差不多, 下面以 MySQL 的 decimal 為例, 介紹 decimal 的基本使用方法.

2.1 描述Decimal

與 float 和 double 不同, decimal 在創(chuàng)建時(shí)需要指定兩個(gè)描述精度的數(shù)字, 分別是 precision 和 scale, precision 指整個(gè) decimal 包括整數(shù)和小數(shù)部分一共有多少個(gè)數(shù)字, scale 指 decimal 的小數(shù)部分包含多少個(gè)數(shù)字, 例如：123.45 就是一個(gè) precision=5, scale=2 的 decimal.  我們可以在建表時(shí)按照這種方式定義我們想要的 decimal.

2.2 建表時(shí)定義Decimal

可以在建表時(shí)這樣定義一個(gè) decimal:

create table t(d decimal(5, 2));

2.3 寫入decimal數(shù)據(jù)

可以向其中插入合法的數(shù)據(jù), 例如

insert into t values(123.45);
insert into t values(123.4);

此時(shí)執(zhí)行 select * from t 會(huì)得到

+--------+
| d      |
+--------+
| 123.45 |
| 123.40 |
+--------+

注意到 123.4 變成了 123.40, 這就是精確類型的特點(diǎn), d 列的每行數(shù)據(jù)都要求 scale=2, 即小數(shù)點(diǎn)后有兩位

當(dāng)插入不滿足 precision 和 scale 定義的數(shù)據(jù)時(shí)

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)這樣的數(shù)字看起來(lái)滿足要求, 但實(shí)際上需要滿足 scale=2 的要求, 因此會(huì)變成 1234.50(precision=6, scale=2) 也不滿足要求.

2.4 取出deimcal進(jìn)行計(jì)算

計(jì)算的結(jié)果不受定義的限制, 而是受到內(nèi)部實(shí)現(xiàn)格式的影響, 對(duì)于 MySQL 結(jié)果最大可以到 precision=81, scale=30, 但是由于 MySQL decimal 的內(nèi)存格式和計(jì)算函數(shù)實(shí)現(xiàn)問(wèn)題, 這個(gè)大小不是在所有情況都能達(dá)到, 將在后文中詳細(xì)介紹. 繼續(xù)上面的例子中：

select d + 9999.999 from t;
+--------------+
| d + 9999.999 |
+--------------+
|    10123.459 |
+--------------+

結(jié)果突破了 precision=5, scale=2 的限制, 這里涉及運(yùn)算時(shí) scale 的變化, 基本規(guī)則是：

1. 加法/減法/sum：取兩邊最大的 scale
2. 乘法：兩邊的 scale 相加
3. 除法：被除數(shù)的 scale + div_precision_increment(取決于數(shù)據(jù)庫(kù)實(shí)現(xiàn))

3 Decimal類型的實(shí)現(xiàn)

在這一部分中, 我們主要介紹 MySQL 的 decimal 實(shí)現(xiàn), 此外也會(huì)對(duì)比 ClickHouse, 看看 decimal 在不同系統(tǒng)中的設(shè)計(jì)與實(shí)現(xiàn)差異.

實(shí)現(xiàn) decimal 需要思考以下問(wèn)題

1. 支持多大的 precision 和 scale
2. 在哪里存儲(chǔ) scale
3. 在連續(xù)乘法或除法時(shí), scale 不斷增長(zhǎng), 整數(shù)部分也不斷擴(kuò)大, 而存儲(chǔ)的 buffer 大小總是有上限的, 此時(shí)應(yīng)該如何處理？
4. 除法可能產(chǎn)生無(wú)限小數(shù), 如何決定除法結(jié)果的 scale?
5. decimal 的表示范圍和計(jì)算性能是否有沖突, 是否可以兼顧

3.1 MySQL

先來(lái)看看 MySQL decimal 相關(guān)的數(shù)據(jù)結(jié)構(gòu)

typedef int32 decimal_digit_t;

struct decimal_t {
? int intg, frac, len;
? bool sign;
? decimal_digit_t *buf;
};

MySQL 的 decimal 使用一個(gè)長(zhǎng)度為 len 的 decimal_digit_t (int32) 的數(shù)組 buf 來(lái)存儲(chǔ) decimal 的數(shù)字, 每個(gè) decimal_digit_t 最多存儲(chǔ) 9 個(gè)數(shù)字, 用 intg 表示整數(shù)部分的數(shù)字個(gè)數(shù), frac 表示小數(shù)部分的數(shù)字個(gè)數(shù), sign 表示符號(hào). 小數(shù)部分和整數(shù)部分需要分開(kāi)存儲(chǔ), 不能混合在一個(gè) decimal_digit_t 中, 兩部分都向小數(shù)點(diǎn)對(duì)齊, 這是因?yàn)檎麛?shù)和小數(shù)通常需要分開(kāi)計(jì)算, 所以這樣的格式可以更容易地將不同 decimal_t 小數(shù)和整數(shù)分別對(duì)齊, 便于加減法運(yùn)算. len 在 MySQL 實(shí)現(xiàn)中恒為 9, 它表示存儲(chǔ)的上限, 而 buf 實(shí)際有效的部分, 則是由 intg 和 frac 共同決定. 例如：

// 123.45 decimal(5, 2) 整數(shù)部分為 3,  小數(shù)部分為 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 需要使用兩個(gè) decimal_digit_t (int32) 來(lái)存儲(chǔ) 123.45, 其中第一個(gè)為 123, 結(jié)合 intg=3, 它就表示整數(shù)部分為 123, 第二個(gè)數(shù)字為 450000000 (共 9 個(gè)數(shù)字), 由于 frac=2, 它表示小數(shù)部分為 .45

再來(lái)看一個(gè)大一點(diǎn)的例子：

// decimal(81, 18) 63 個(gè)整數(shù)數(shù)字, 18 個(gè)小數(shù)數(shù)字, 用滿整個(gè) buffer
// 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}
};

這個(gè)例子用滿了 81 個(gè)數(shù)字, 但是也有些場(chǎng)景無(wú)法用滿 81 個(gè)數(shù)字, 這是因?yàn)檎麛?shù)和小數(shù)部分是分開(kāi)存儲(chǔ)的, 所以一個(gè) decimal_digit_t (int32) 可能只存儲(chǔ)了一個(gè)有效的小數(shù)數(shù)字, 但是其余的部分沒(méi)有辦法給整數(shù)部分使用, 例如一個(gè) decimal 整數(shù)部分有 62 個(gè)數(shù)字, 小數(shù)部分有 19 個(gè)數(shù)字(precision=81, scale=19), 那么小數(shù)部分需要使用 3 個(gè) decimal_digit_t (int32), 整數(shù)部分還有 54 個(gè)數(shù)字的余量, 無(wú)法存下 62 個(gè)數(shù)字. 這種情況下, MySQL 會(huì)優(yōu)先滿足整數(shù)部分的需求, 自動(dòng)截?cái)嘈?shù)點(diǎn)后的部分, 將它變成 decimal(80, 18)

接下來(lái)看看 MySQL 如何在這個(gè)數(shù)據(jù)結(jié)構(gòu)上進(jìn)行運(yùn)算. MySQL 通過(guò)一系列 decimal_digit_t(int32) 來(lái)表示一個(gè)較大的 decimal, 其計(jì)算也是對(duì)這個(gè)數(shù)組中的各個(gè) decimal_digit_t 分別進(jìn)行, 如同我們?cè)谛W(xué)數(shù)學(xué)計(jì)算時(shí)是一個(gè)數(shù)字一個(gè)數(shù)字地計(jì)算, MySQL 會(huì)把每個(gè) decimal_digit_t 當(dāng)作一個(gè)數(shù)字來(lái)進(jìn)行計(jì)算、進(jìn)位. 由于代碼較長(zhǎng), 這里不再對(duì)具體的代碼進(jìn)行完整的分析, 僅對(duì)代碼中核心部分進(jìn)行分析, 如果感興趣, 可以直接參考 MySQL 源碼 strings/decimal.h 和 strings/decimal.cc 中的 decimal_add, decimal_mul, decimal_div 等代碼.

準(zhǔn)備步驟

在真正計(jì)算前, 還需要做一些準(zhǔn)備工作：

1. MySQL 會(huì)將數(shù)字的個(gè)數(shù) ROUND_UP 到 9 的整數(shù)倍, 這樣后面就可以按照 decimal_digit_t 為單位來(lái)進(jìn)行計(jì)算
2. 此外還要針對(duì)參與運(yùn)算的兩個(gè) decimal 的具體情況, 計(jì)算結(jié)果的 precision 和 scale, 如果發(fā)現(xiàn)結(jié)果的 precision 超過(guò)了支持的上限, 那么會(huì)按照 decimal_digit_t 為單位減少小數(shù)的數(shù)字.
3. 在乘法過(guò)程中, 如果發(fā)生了 2 中的減少行為, 則需要 TRUNCATE 兩個(gè)運(yùn)算數(shù), 避免中間結(jié)果超出范圍.

加法主要步驟

首先, 因?yàn)閮蓚€(gè)數(shù)字的 precision 和 scale 可能不相同, 需要做一些準(zhǔn)備工作, 將小數(shù)點(diǎn)對(duì)齊, 然后開(kāi)始計(jì)算, 從最末尾小數(shù)開(kāi)始向高位加, 分為三個(gè)步驟：

1. 將小數(shù)較多的 decimal 多出的小數(shù)數(shù)字復(fù)制到結(jié)果中
2. 將兩個(gè) decimal 公共的部分相加
3. 將整數(shù)較多的 decimal 多出的整數(shù)數(shù)字與進(jìn)位相加到結(jié)果中

代碼中使用了 stop, stop2 來(lái)標(biāo)記小數(shù)點(diǎn)對(duì)齊后, 長(zhǎng)度不同的數(shù)字出現(xiàn)差異的位置.

/* 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);
}

乘法主要步驟

乘法引入了一個(gè)新的 dec2, 表示一個(gè) 64 bit 的數(shù)字, 這是因?yàn)閮蓚€(gè) decimal_digit_t(int32) 相乘后得到的可能會(huì)是一個(gè) 64 bit 的數(shù)字. 在計(jì)算時(shí)一定要先把類型轉(zhuǎn)換到 dec2(int64), 再計(jì)算, 否則會(huì)得到溢出后的錯(cuò)誤結(jié)果. 乘法與加法不同, 乘法不需要對(duì)齊, 例如計(jì)算 11.11 5.0, 那么只要計(jì)算 111150=55550, 再移動(dòng)小數(shù)點(diǎn)位置就能得到正確結(jié)果 55.550

MySQL 實(shí)現(xiàn)了一個(gè)雙重循環(huán)將 decimal1 的 每一個(gè) decimal_digit_t 與 decimal2 的每一個(gè) decimal_digit_t 相乘, 得到一個(gè) 64 位的 dec2,  其低 32 位是當(dāng)前的結(jié)果, 其高 32 位是進(jìn)位.

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) {
    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, 其基本思路和手動(dòng)除法也比較類似.

首先使用除數(shù)的前兩個(gè) decimal_digit_t 組成一個(gè)試商因數(shù), 這里使用了一個(gè) norm_factor 來(lái)保證數(shù)字在不溢出的情況下盡可能擴(kuò)大, 這是因?yàn)?decimal 為了保證精度必須使用整形來(lái)進(jìn)行計(jì)算, 數(shù)字越大, 得到的結(jié)果就越準(zhǔn)確. D3: 猜商, 就是用被除數(shù)的前兩個(gè) decimal_digit_t 除以試商因數(shù) 這里如果不乘 norm_factor, 則 start1[1] 和 start2[1] 都不會(huì)體現(xiàn)在結(jié)果之中.

D4: 將 guess 與除數(shù)相乘, 再?gòu)谋怀龜?shù)中剪掉結(jié)果 然后做一些修正, 移動(dòng)向下一個(gè) decimal_digit_t, 重復(fù)這個(gè)過(guò)程.

想更詳細(xì)地了解這個(gè)算法可以參考 https://skanthak.homepage.t-online.de/division.html

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;
  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 是列存, 相同列的數(shù)據(jù)會(huì)放在一起, 因此計(jì)算時(shí)通常也將一列的數(shù)據(jù)合成 batch 一起計(jì)算.

一列的 batch 在 ClickHouse 中使用 PODArray, 例如上圖中的 c1 在計(jì)算時(shí)就會(huì)有一個(gè) PODArray, 進(jìn)行簡(jiǎn)化后大致可以表示如下:

class PODArray {
    char * c_start          = null;
    char * c_end            = null;
    char * c_end_of_storage = null;
}

在計(jì)算時(shí)會(huì)講 c_start 指向的數(shù)組轉(zhuǎn)換成實(shí)際的類型, 對(duì)于 decimal, ClickHouse 使用足夠大的 int 來(lái)表示, 根據(jù) decimal 的 precision 選擇 int32, int64 或者 int128. 例如一個(gè) decimal(10, 2), 123.45, 使用這樣方式可以表示為一個(gè) int32_t, 其內(nèi)容為 12345, decimal(10, 3) 的 123.450 表示為 123450. ClickHouse 用來(lái)表示每個(gè) decimal 的結(jié)構(gòu)如下, 實(shí)際上就是足夠大的 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>;
using Decimal128 = Decimal<Int128>;

顯而易見(jiàn), 這樣的表示方法相較于 MySQL 的方法更輕量, 但是范圍更小, 同時(shí)也帶來(lái)了一個(gè)問(wèn)題是沒(méi)有小數(shù)點(diǎn)的位置, 在進(jìn)行加減法、大小比較等需要小數(shù)點(diǎn)對(duì)齊的場(chǎng)景下, ClickHouse 會(huì)在運(yùn)算實(shí)際發(fā)生的時(shí)候?qū)?scale 以參數(shù)的形式傳入, 此時(shí)配合上面的數(shù)字就可以正確地還原出真實(shí)的 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);

例如兩個(gè) decimal: a = 123.45000(p=8, s=5), b = 123.4(p=4, s=1), 那么計(jì)算時(shí)傳入的參數(shù)就是  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 的小數(shù)點(diǎn)位置是對(duì)齊的, 可以直接計(jì)算.

加法主要步驟

ClickHouse 實(shí)現(xiàn)加法同樣要先對(duì)齊, 對(duì)齊的方法是將 scale 較小的數(shù)字乘上一個(gè)系數(shù), 使兩邊的 scale 相等. 然后直接做加法即可. ClickHouse 在計(jì)算中也根據(jù) decimal 的 precision 進(jìn)行了細(xì)分, 對(duì)于長(zhǎng)度沒(méi)那么長(zhǎng)的 decimal, 直接用 int32, int64 等原生類型計(jì)算就可以了, 這樣大大提升了速度.

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, 乘法不需要對(duì)齊, 直接按整數(shù)相乘就可以了, 比較短的 decimal 同樣可以使用 int32, int64 原生類型. int128 在溢出檢測(cè)時(shí)被轉(zhuǎn)換成 unsigned int128 避免溢出時(shí)的未定義行為.

template <typename T>
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;
}

除法主要步驟

先轉(zhuǎn)換 scale 再直接做整數(shù)除法. 本身來(lái)講除法和乘法一樣是不需要對(duì)齊小數(shù)點(diǎn)的, 但是除法不一樣的地方在于可能會(huì)產(chǎn)生無(wú)限小數(shù), 所以一般數(shù)據(jù)庫(kù)都會(huì)給結(jié)果一個(gè)固定的小數(shù)位數(shù), ClickHouse 選擇的小數(shù)位數(shù)是和被除數(shù)一樣, 因此需要將 a 乘上 scale, 然后在除法運(yùn)算的過(guò)程中, 這個(gè) scale 被自然減去, 得到結(jié)果的小數(shù)位數(shù)就可以保持和被除數(shù)一樣.

bool overflow = false;
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 總結(jié)

MySQL 通過(guò)一個(gè) int32 的數(shù)組來(lái)表示一個(gè)大數(shù), ClickHouse 則是盡可能使用原生類型, GCC 和 Clang 都支持 int128 擴(kuò)展, 這使得 ClickHouse 的這種做法可以比較方便地實(shí)現(xiàn).

MySQL 與 ClickHouse 的實(shí)現(xiàn)差別還是比較大的, 針對(duì)我們開(kāi)始提到的問(wèn)題, 分別來(lái)看看他們的解答.

1. precision 和 scale 范圍, MySQL 最高可定義 precision=65, scale=30, 中間結(jié)果最多包含 81 個(gè)數(shù)字, ClickHouse 最高可定義 precision=38, scale=37, 中間結(jié)果最大為 int128 的最大值 -2^127 ~ 2^127-1.
2. 在哪里存儲(chǔ) scale, MySQL 是行式存儲(chǔ), 使用火山模型逐行迭代, 計(jì)算也是按行進(jìn)行, 每個(gè) decimal 都有自己的 scale；ClickHouse 是列式存儲(chǔ), 計(jì)算按列批量進(jìn)行, 每行按照相同的 scale 處理能提升性能, 因此 scale 來(lái)自表達(dá)式解析過(guò)程中推導(dǎo)出來(lái)的類型.
3. scale 增長(zhǎng), scale 增長(zhǎng)超過(guò)極限時(shí), MySQL 會(huì)通過(guò)動(dòng)態(tài)擠占小數(shù)空間, truncate 運(yùn)算數(shù), 盡可能保證計(jì)算完成, ClickHouse 會(huì)直接報(bào)溢出錯(cuò).
4. 除法 scale, MySQL 通過(guò) div_prec_increment 來(lái)控制除法結(jié)果的 scale, ClickHouse 固定使用被除數(shù)的 scale.
5. 性能, MySQL 使用了更寬的 decimal 表示, 同時(shí)要進(jìn)行 ROUND_UP, 小數(shù)擠占, TRUNCATE 等動(dòng)作, 性能較差, ClickHouse 使用原生的數(shù)據(jù)類型和計(jì)算最大限度地提升了性能.

4. MySQL 違反直覺(jué)的地方

在這一部分中, 我們將講述一些 MySQL 實(shí)現(xiàn)造成的違反直覺(jué)的地方. 這些行為通常發(fā)生在運(yùn)算結(jié)果接近 81 digit 時(shí), 因此如果可以保證運(yùn)算結(jié)果的范圍較小也可以忽略這些問(wèn)題.

1. 乘法的 scale 會(huì)截?cái)嗟?31, 且該截?cái)嗍峭ㄟ^(guò)截?cái)噙\(yùn)算數(shù)字的方式來(lái)實(shí)現(xiàn)的, 例如: select 10000000000000000000000000000000.100000000 10000000000000000000000000000000 = 10000000000000000000000000000000.100000000000000000000000000000 10000000000000000000000000000000.555555555555555555555555555555 返回 1, 第二個(gè)運(yùn)算數(shù)中的 .555555555555555555555555555555 全部被截?cái)?/li>
2. MySQL 使用的 buffer 包含了 81 個(gè) digit 的容量, 但是由于小數(shù)部分必須和整數(shù)部分分開(kāi), 因此很多時(shí)候無(wú)法用滿 81 個(gè) digit, 例如: select 99999999999999999999999999999999999999999999999999999999999999999999999999.999999 = 99999999999999999999999999999999999999999999999999999999999999999999999999.9 返回 1
3. 計(jì)算過(guò)程中如果發(fā)現(xiàn)整數(shù)部分太大會(huì)動(dòng)態(tài)地?cái)D占小數(shù)部分, 例如: select 999999999999999999999999999999999999999999999999999999999999999999999999.999999999 + 999999999999999999999999999999999999999999999999999999999999999999999999.999999999 = 999999999999999999999999999999999999999999999999999999999999999999999999 + 999999999999999999999999999999999999999999999999999999999999999999999999 返回 1
4. 除法計(jì)算中間結(jié)果不受 scale = 31 的限制, 除法中間結(jié)果的 scale 一定是 9 的整數(shù)倍, 不能按照最終結(jié)果來(lái)推測(cè)除法作為中間結(jié)果的精度, 例如 select 2.0000 / 3 3 返回 2.00000000, 而 select 2.00000 / 3 3 返回 1.999999998, 可見(jiàn)前者除法的中間結(jié)果其實(shí)保留了更多的精度.
5. 除法, avg 計(jì)算最終結(jié)果的小數(shù)部分如果正好是 9 的倍數(shù), 則不會(huì)四舍五入, 例如: select 2.00000 / 3 返回 0.666666666, select 2.0000 / 3 返回 0.66666667
6. 除法, avg 計(jì)算時(shí), 運(yùn)算數(shù)字的小數(shù)部分如果不是 9 的倍數(shù), 那么會(huì)實(shí)際上存儲(chǔ) 9  的倍數(shù)個(gè)小數(shù)數(shù)字, 因此會(huì)出現(xiàn)以下差異：

create table t1 (a decimal(20, 2), b decimal(20, 2), c integer);

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)

總結(jié)

到此這篇關(guān)于數(shù)據(jù)庫(kù)中Decimal類型使用以及實(shí)現(xiàn)方法的文章就介紹到這了,更多相關(guān)Decimal類型使用及實(shí)現(xiàn)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論