在MySQL中对int使用smallint数据类型是否确实节省了内存? [英] Does using smallint datatype over int in mysql actually save memory?

问题描述

在mysql表中通过常规int使用smallint数据类型是否会真正提高内存使用率?硬件是否仍不为所有数据分配完整的64位字长?如果它不分配完整的单词，那么我们是否不会因为必须从分配给内存的64位单词中解析出多个smallint或tinyints而导致性能下降?

Does using a smallint datatype in a mysql table over a regular int actually improve memory usage? Wouldn't the hardware just allocate a full 64 bit word size for all data anyway? If it doesn't allocate a full word, then wouldn't we see a performance decrease from having to parse out multiple smallints or tinyints from a 64 bit word allocated in memory?

基本上，假设我们知道存储在Status列中的值的范围将永远不会超过smallint的最大值/最小值范围，那么使用下表对其后的表格有没有设计/内存/性能方面的好处? ?任何见识将不胜感激:

Basically, is there any design/memory/performance benefit to using the following table over the one after it, assuming we know the range of the values stored in the Status column will never exceed the max/min range of smallint? Any insight would be appreciated:

create table `TestTableWithSmallInt` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Status` smallint(11) DEFAULT 0,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table `TestTableWithInt` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Status` int(11) DEFAULT 0,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

推荐答案

理论上，您将每行节省两个字节，SMALLINT是16位带符号整数，而INT是32位带符号整数. 各种类型具有不同的存储要求.

You'll theoretically save two bytes per row, a SMALLINT is a 16-bit signed integer versus the INT which is 32-bit signed. The various types have varying storage requirements.

通常，在INT和SMALLINT之间节省的费用会带来微妙的性能改进，以至于您很难测量它，尤其是当您要修剪的字段数量很少时.

Normally the savings between INT and SMALLINT produces such a slim performance improvement that you'll have a hard time measuring it, especially if there's a small number of fields you're trimming this way.

反之，仅在可能会用尽AUTO_INCREMENT标记字段的数字空间的情况下，才想使用BIGINT.

For the opposite, you'll only want to use a BIGINT when it's conceivable that you might exhaust the number space of an AUTO_INCREMENT flagged field.

您可能应该以没有长度的裸类型声明它们，以获取最佳拟合. INT比INT(11)更可取，而SMALLINT(11)具有误导性，因为不可能从16位值中获得如此高的精度.

You should probably declare them in their bare types, without a length, to get the best fit. INT is preferable to INT(11) and SMALLINT(11) is misleading as it's impossible to get that much precision from a 16-bit value.

这篇关于在MySQL中对int使用smallint数据类型是否确实节省了内存?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！