使用十进制数据类型(MySQL / Postgres)是否有性能损失 [英] Is there a performance hit using decimal data types (MySQL / Postgres)

查看:268
本文介绍了使用十进制数据类型(MySQL / Postgres)是否有性能损失的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我理解整数和浮点数据类型是如何存储的,我猜想十进制数据类型的可变长度意味着它更像一个字符串存储。

I understand how integer and floating point data types are stored, and I am guessing that the variable length of decimal data types means it is stored more like a string.

这是否意味着在使用十进制数据类型并搜索时会产生性能开销?

Does that imply a performance overhead when using a decimal data type and searching against them?

推荐答案

Pavel说得对,我只想解释一下。

Pavel has it quite right, I'd just like to explain a little.

假设你的意思是与浮点相比的性能影响,或固定点偏移整数(即存储一千分之一的百分之一作为整数):是的,有非常多的性能影响。 PostgreSQL,并通过事物MySQL的声音,以二进制编码的十进制存储 DECIMAL / NUMERIC 。这种格式比将数字存储为文本更紧凑,但它仍然不能很有效地处理。

Presuming that you mean a performance impact as compared to floating point, or fixed-point-offset integer (i.e. storing thousandsths of a cent as an integer): Yes, there is very much a performance impact. PostgreSQL, and by the sounds of things MySQL, store DECIMAL / NUMERIC in binary-coded decimal. This format is more compact than storing the digits as text, but it's still not very efficient to work with.

如果你不在数据库中做很多计算,影响被限制为与整数或浮点相比对于BCD所需的更大的存储空间,并且因此更宽的行和更慢的扫描,更大的索引等.b-树索引搜索中的比较操作也更慢,但是不够重要

If you're not doing many calculations in the database, the impact is limited to the greater storage space requried for BCD as compared to integer or floating point, and thus the wider rows and slower scans, bigger indexes, etc. Comparision operations in b-tree index searches are also slower, but not enough to matter unless you're already CPU-bound for some other reason.

如果你使用 DECIMAL / NUMERIC 值,那么性能可能会受到影响。这是特别值得注意的,至少在PostgreSQL,因为Pg不能使用多于一个CPU任何给定的查询。如果你做了一大堆分工,乘法,更复杂的数学,聚合等,你可以开始发现自己CPU的约束在你永远不会使用浮点或整数数据类型的情况下。这在类似于OLAP的(分析)工作负载以及在加载或提取(ETL)期间的报告或数据转换中是特别明显的。

If you're doing lots of calculations with the DECIMAL / NUMERIC values in the database, then performance can really suffer. This is particularly noticeable, at least in PostgreSQL, because Pg can't use more than one CPU for any given query. If you're doing a huge bunch of division & multiplication, more complex maths, aggregation, etc on numerics you can start to find yourself CPU-bound in situations where you would never be when using a float or integer data type. This is particularly noticeable in OLAP-like (analytics) workloads, and in reporting or data transformation during loading or extraction (ETL).

是性能影响(根据工作负载从微不足道到相当大),您通常应使用 numeric / 十进制当它是你的任务最合适的类型 - 即当非常高的范围值必须存储和/或舍入误差是不能接受的。

Despite the fact that there is a performance impact (which varies based on workload from negligible to quite big) you should generally use numeric / decimal when it is the most appropriate type for your task - i.e. when very high range values must be stored and/or rounding error isn't acceptable.

值得使用bigint和固定点偏移的麻烦,但这是笨拙和不灵活。使用浮点代替是很少正确的答案,因为所有的挑战,使用浮点值可靠地操作货币。

Occasionally it's worth the hassle of using a bigint and fixed-point offset, but that is clumsy and inflexible. Using floating point instead is very rarely the right answer due to all the challenges of working reliably with floating point values for things like currency.

(BTW,我很兴奋一些新的英特尔CPU和IBM的Power 7系列CPU包括对IEEE 754十进制浮点的硬件支持,如果这种情况在低端CPU中可用,将是数据库的巨大胜利。)

(BTW, I'm quite excited that some new Intel CPUs, and IBM's Power 7 range of CPUs, include hardware support for IEEE 754 decimal floating point. If this ever becomes available in lower end CPUs it'll be a huge win for databases.)

这篇关于使用十进制数据类型(MySQL / Postgres)是否有性能损失的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆