使用 AUTO INCREMENT 列构建全局字典以加速精确去重计算和 Join
应用场景
-
场景一:您需要对海量订单数据(零售订单、快递订单等)计算精确去重。但是去重计数的列为 STRING 类型,此时直接计数,性能会不够理想。例如订单表
orders
中表示订单编号的order_uuid
列为 STRING 类型,大小通常为 32 ~ 36 字节,由UUID()
或其他类似函数生成。直接基于 STRING 列order_uuid
精确去重计数SELECT count(DISTINCT order_uuid) FROM orders WHERE create_date >= CURDATE();
,查询性能可能无法满足您的需 要。 如果能使用 INTEGER 列做精确去重计数,性能则会显著提升。 -
场景二:您需要借助bitmap函数进一步加速多维分析中对订单计算精确去重。然而,
bitmap_count()
函数要求输入值为 INTEGER 类型,如果业务场景中去重计数的列为 STRING 类型,则需要使用bitmap_hash()
函数,但是这样可能导致最终返回的是近似且值小一点的去重计数。并且,相对于连续分配的 INTEGER 值,bitmap_hash()
产生的 INTEGER 值更分散,会导致查询性能下降、存储数据量变大。 -
场景三:您需要查询从下单到支付的时间相对较短的订单数量,而下单时间和支付时间可能存储在两张表里,由不同的业务团队维护。则您可能需要基于订单编号关联两张表,然后对订单计算精确去重。例如如下语句:
SELECT count(distinct order_uuid)
FROM orders_t1 as t1 JOIN orders_t2 as t2
ON t1.order_uuid = t2.order_uuid
WHERE t2.payment_time - t1.create_time <= 3600
AND create_date >= CURDATE();但是订单编号
order_uuid
列是 STRING 类型,直接基于 STRING 列进行 Join,性能也不如基于 INTEGER 列。
优化思路
针对上述应用场景,优化思路是将订单数据导入目标表并构建 STRING 和 INTEGER 值之间的映射关系,后续查询分析基于 INTEGER 列进行。该思路可以拆分为如下阶段执行:
- 阶段一: 创建全局字典并构建 STRING 值和 INTEGER 值之间的映射关系。字典中 key 列为 STRING 类型,value 列为 INTEGER 类型且为自增列。每次导入数据时候,系统都会自动为每个 STRING 值生成一个表内全局唯一的 ID,如此就建立了 STRING 值和 INTEGER 值之间的映射关系。
- 阶段二:将订单数据和全局字典的映射关系导入至目标表。
- 阶段三:后续查询分析时基于目标表的 INTEGER 列来计算精确去重或 Join,可以显著提高性能。
- 阶段四:为了进一步优化性能,您还可以在 INTEGER 列上使用 bitmap 函数来进一步加速计算精确去重。