如何确定MariaDB中decimal类型列所需的精度(P)与小数位数(D)

针对你百万行数据下decimal列实际P/D值计算慢的问题，核心优化思路是替换低效的对数/字符串转换操作，同时减少不必要的全表计算开销，以下是具体实现：

一、核心优化点说明 #

原SQL慢的原因在于大量使用LOG10、多次字符串反转/数值转换这类非轻量操作，且无法利用索引加速。优化方案通过简化计算逻辑、减少重复运算来提升效率。

二、高效SQL实现 #

1. 合并查询（一次获取maxI和maxD） #

WITH cte AS (
    SELECT
        col,
        FLOOR(col) AS int_part,
        -- 预先提取小数部分字符串，避免重复计算
        SUBSTRING(CAST(col AS CHAR), LOCATE('.', CAST(col AS CHAR)) + 1) AS dec_str
    FROM tbl
    WHERE col IS NOT NULL -- 过滤空值，避免干扰计算
)
SELECT
    -- 整数部分最大位数：用字符串长度替代对数计算，避免LOG10(0)报错
    CHAR_LENGTH(CAST(MAX(int_part) AS CHAR)) AS maxI,
    -- 小数部分有效位数：找最后一个非零数字的位置
    MAX(
        CASE
            WHEN col = int_part THEN 0 -- 整数行的小数位数为0
            ELSE CHAR_LENGTH(dec_str) - LOCATE('123456789', REVERSE(dec_str)) + 1
        END
    ) AS maxD
FROM cte;

2. 分拆查询（适合单独优化某一部分） #

如果只需要整数部分：

-- 非负数场景
SELECT CHAR_LENGTH(CAST(FLOOR(MAX(col)) AS CHAR)) AS maxI 
FROM tbl 
WHERE col IS NOT NULL;

-- 含负数场景（去掉负号后计算长度）
SELECT CHAR_LENGTH(CAST(ABS(FLOOR(MAX(col))) AS CHAR)) AS maxI 
FROM tbl 
WHERE col IS NOT NULL;

如果只需要小数部分：

SELECT MAX(
    CASE
        WHEN col = FLOOR(col) THEN 0
        ELSE CHAR_LENGTH(dec_str) - LOCATE('123456789', REVERSE(dec_str)) + 1
    END
) AS maxD
FROM (
    SELECT
        col,
        SUBSTRING(CAST(col AS CHAR), LOCATE('.', CAST(col AS CHAR)) + 1) AS dec_str
    FROM tbl
    WHERE col IS NOT NULL AND col != FLOOR(col) -- 只处理含小数的行
) AS t;

三、额外优化建议 #

1. 利用索引加速MAX计算：如果col列有索引，MariaDB可以快速获取MAX(col)，无需全表扫描整数部分的计算。
2. 采样计算（非精确场景）：如果允许少量误差，可以随机抽取10%-20%的数据计算结果后适当放大（比如maxI+1、maxD+1），大幅缩短计算时间。
3. 批量处理多列：如果有多个decimal列，可以在同一查询中批量计算，避免多次全表扫描。

内容的提问来源于stack exchange，提问作者neucassi