int与float之间的排序有速度差吗？ [英] Is there a speed difference in ordering by int vs. float?

问题描述

在数据库中检索条目时，在SELECT语句中使用 ORDERBY 时，将值作为float或decimal与int之间存在差异吗？

解决方案

这取决于。您没有指定RDBMS，所以我只能专门与SQL Server通话，但数据类型与它们相关联的存储成本不同。 Ints 的范围为1到8个字节，小数是5-17，浮动是4到8个字节。

RDBMS需要从磁盘上读取数据页面才能找到您的数据（最坏的情况），并且它们只能在8k页数据上适合这么多行。所以，如果你有17位小数，你将得到读取磁盘每行读取量的1/17，如果你正确地调整数据大小，并使用一个1字节的成本来存储X的tinyint。

当您对数据进行排序（order by）时，存储成本将具有级联效应。它将尝试在内存中排序，但是如果您有一个bazillion行，并且为了内存而饿死，它可能会转储到临时存储器中，并且您一次又一次地支付该成本。

索引可能有帮助，因为数据可以按照排序的方式存储，但再次，如果将该数据存入内存可能不会像肥胖数据类型那样有效。 >

@Bohemian对整数与浮点比较的CPU效率做了一个细微的分析，但令人惊讶在数据库服务器上加入CPU很少见。您更有可能受到磁盘IO子系统和内存的限制，这就是为什么我的回答关注于将数据导入引擎之间的速度差异，以便它执行排序操作和CPU比较成本。

When retrieving entries in a database, is there a difference between storing values as a float or decimal vs. an int when using ORDERBY in a SELECT statement?

解决方案

It depends. You didn't specify the RDBMS so I can only speak to SQL Server specifically but data types have different storage costs associated with them. Ints range from 1 to 8 bytes, Decimals are 5-17 and floats are 4 to 8 bytes.

The RDBMS will need to read data pages off disk to find your data (worst case) and they can only fit so many rows on an 8k page of data. So, if you have 17 byte decimals, you're going to get 1/17th the amount of rows read off disk per read than you could have if you sized your data correctly and used a tinyint with a 1 byte cost to store X.

That storage cost will have a cascading effect when you go to sort (order by) your data. It will attempt to sort in memory but if you have a bazillion rows and are starved for memory it may dump to temp storage for the sort and you're paying that cost over and over.

Indexes may help as the data can be stored in a sorted manner but again, if getting that data into memory may not be as efficient for obese data types.

[edit]

@Bohemian makes a fine point about the CPU efficiency of integer vs floating point comparisons but it is amazingly rare for the CPU to be spiked on a database server. You are far more likely to be constrained by the disk IO subsystem and memory which is why my answer focuses on the speed difference between getting that data into the engine for it to perform the sort operation vs the CPU cost of comparison.

这篇关于int与float之间的排序有速度差吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！