数据类型的优化

骑猪看日落 2022-04-18 01:24 234阅读 0赞

## 1.整数 ##

数字有两种类型:整数(Whole Number)和实数(Real Number)。如果存储整数，就可以使用这几种整数类型:TINYINT、SMALLINT、 MEDIUMINT、INT 或BIGINT，它们分别需要8、16、24、32 和64位存储空间。它们的范围为  
 − 2 n − 1 到 2 n − 1 − 1 -2^\{n-1\}到2^\{n-1\}-1 −2n−1到2n−1−1  
这里的n是所需存储空间的位数。

整数类型有可选的UNSIGNED属性，它表示不允许负数，并大致把正，上限提高了一倍。例如，TINYINT UNS IGNED保存的范围为0到255，而不是-127到128。

有符号(Signed)和无符号(Unsigned) 类型占用的存储空间是一样的，性能也一样。因此可以根据实际情况采用合适的类型。

你的选择将会决定MySQL把数据保存在内存中还是磁盘上。然而，整数运算通常使用64位BIGINT整数，即使是32位架构也如此。(一些聚合函数是例外，它们使用DECIMAL或DOUBlE进行计算。)

MySQL还可以对整数类型定义宽度，比如INT(11)。这对于大多数应用程序都是没有意义的:它不会限制值的范围，只规定了MySQL的交互工具(例如命令行客户端)用来显示字符的个数。对于存储和计算，INT(1)和INT (20)是一样的。

提示: Falcon 存储引擎和MySQLAB提供的其他存储引擎内部存储整数的机制是不同的。用户不能控制存储数据的实际大小。第三方存储引擎，例如Brighthouse， 也有自己的存储格式和压缩方案。

## 2.实数 ##

实数有分数部分。然而，它们并不仅仅是分数。可以使用DECIMAL保存比BIGINT还大的整数。MySQL同时支持精确与非精确类型。

floAt和DOUBLE 类型支持使用标准的浮点运算进行近似计算。如果想知道浮点运算到底如何进行，则要研究平台浮点数的具体实现。

DECIMAL类型用于保存精确的小数。在MySQL 5.0及以上版本，DECIMAL类型支持精确的数学运算。MySQL4.1和早期版本对DECIMAL值执行浮点运算，它会因为丢失精度而导致奇怪的结果。在这些MySQL版本中，DECIMAL仅仅是“存储类型”。

在MySQL5.0及以上版本中，服务器自身进行了DECIMAL运算，因为CPU并不支持对它进行直接计算。浮点运算会快一点，因为计算直接在CPU上进行。

可以定义浮点类型和DECIMAL类型的精度。对于DECIMAI列，可以定义小数点之前和之后的最大位数，这影响了所需的存储空间。MySQL 5.0和以上版本把数字保存到了一个二进制字符串中(每4个字节保存9个數字)。例如，DECIMAL (18， 9)将会在小数点前后都保存9位数字，总共使用9个字节:小数点前4个字节，小数点占1个字节，小数点后4个字节。

MySQL 5.0及以上版本中的DECIMAL类型最多允许65个数字。在较早的版本中，DECIMAL 最多可以有254个数字，并且保存为未压缩的字符串(一个数字占一个字节)。然而，这些版本的MySQL根本不能在计算中使用如此大的数字，因为DECIMAL只是一种存储格式。DECIMAL 在计算时会被转换为DOUBLE类型。

可以用多种方式定义浮点数列的精度，它会导致MySQL悄悄采用不同的数据类型，或者在保存的时候进行圆整。这些精度定义符不是标准的，因此我们建议定义需要的类型，而不是精度。

比起DECIMAL类型，浮点类型保存同样大小的值使用的空间通常更少。flOAT 占用4个字节。DOUBLE占用8个字节，而且精度更高、范围更大。和整数一样，你选择的仅仅是存储类型。MySQL在内部对浮点类型使用DOUBLE进行计算。

**由于需要额外的空间和计算开销，只有在需要对小数进行精确计算的时候才使用DECIMAL，比如保存金融数据**。

## 3.字符串类型 ##

VARCHAR和CHAR类型

两种主要的字符串类型是VARCHAR和CHAR。不幸的是，很难确切地解释这两种类型是怎样破保存到磁盘或内存中的，因为具体实现依赖于存储引擎(例如，Falcon 几乎对每种数据类型都用了自己的存储格式)。这里假设你使用的是InnoDB和或MyISAM。如果不是，请参考所使用的存储引擎的文档。

先看看VARCHAR和CHAR是如何被保存到磁盘上的。要知道存储引擎可能会使用不同的方式把CHAR和VARCHAR类型保存到内存中，并且服务器从存储引擎取回这些值的时候还可能会把它转换为其他存储格式。下面是两种类型的比较:

VARCHAR

\*\*VARCHAR保存了可变长度的字符串，是使用得最多的字符串类型。\*\*它能比固定长度类型占用更少的存储空间，因为它只占用了自己需要的空间(也就是说较短的值占用的空间就较少)。例外情况是使用ROW\_ FORMAT-FIXED 创建的MyISAM表，它为每行使用固定长度的空间，可能会造成浪费。

\*\*VARCHAR使用额外的1到2字节来存储值的长度。\*\*如果列的最大长度小于或等于255，则使用1字节，否则就使用2字节。假设使用latin1 字符集，VARCHAR(10)将会占用Il字节的存储空间。VARCHAR (1000)则会占用1002字节，因为需要2个字节来保存长度信息。

\*\*VARCHAR能节约空间，所以对性能有帮助。\*\*然而，由于行的长度是可变的，它们在更新的时候可能会发生变化，这会引起额外的工作。如果行的长度增加并不再适合于原始的位置时，具体的行为则会和存储引擎相关。例如，MyISAM会把行拆开，InnoDB则可能进行分页。另外的存储引擎还可能不会在合适的位置更新数据。

当最大长度远大于平均长度，并且很少发生更新的时候，通常适合使用VARCHAR。这时候碎片就不会成为问题。还有当你使用复杂的字符集，比如UTF-8时，它的每个字符都可能会占用不同的存储空间。

在5.0及以上版本，无论是保存还是取值，MySQL都会保留字符串末尾的空格。但是在4.1及之前的版本，这些空格会被去掉。

CHAR

**CHAR是固定长度的**。MySQL总是为特定数量的字符分配足够的空间。当保存CHAR值的时候，MySQL会去掉任何末尾的空格。(在MySQL4.1及之前版本，VARCHAR也是如此。CHAR 和VARCHAR在逻辑上是一样的，只是存储格式不同。)进行比较的时候，空格会被填充到字符串末尾。

\*\*CHAR在存储很短的字符串或长度近似相同的字符串的时候很有用。\*\*例如，CHAR适合用存储用户密码的MDS哈希值，它的长度总是一-样的。对于经常改变的值，CHAR 也好于VARCHAR，因为固定长度的行不容易产生碎片。对于很短的列，CHAR的效率也高于VARCHAR。CHAR(1) 字符串对于单字节字符集(注1)只会占用1个字节，但是VARCHAR(1)则会占用2个字节，因为有1个字节用来存储长度信息。

## 4.BLOB和TEXT类型 ##

BLOB和TEXT分别別以二进制和字符形式保存大量数据

事实上，它们各自有自己的数据类型家族:字符类型有ェ INYTEXT、 SMALLT2xT、TXT、 MEDIUMTEX和工 ONGTEXT，进制类型有 TINYBLOB、 SMALLBLOB、BLOB、 MEDIOMBLOB和 LONGBLOB。BLOB等同于 SMALLBLDB，TEx等同

于MAL

不同， MYSQL把BLOB和TEXT当成有实体的对象来处理。存储引擘通常会特别地保存它们。 INNODB的时候会使用单独的“外部”存储区域来进行保存。毎个值在行里面都需要1到4字节，并且还需

**BLOB和TEXT唯一的区别就是BLOB保存的是二进制数据，没有字符集和排序规则，但是TEXT有字符集和排序**

MySL对BLOB和TEXT列的排序方式和其他类型不同:它不会按照字符串的完整长度进行排序，而只是按照max\_soxt\_1 ength规定的前若干个字节进行排序。如果只按照开始的几个字符排序，就可以减少

max\_sort\_1 ength的值或使用 ORDER BY SUBSTRING( column，length)

MYSQL不能索引这些数据类型的完整长度，也不能为排序使用索引。(后文有这方面的更多内容。)

## 5.日期和时间类型 ##

MYSQL提供了两种相似的数据类型: DATETIME和 TIMESTAMP。

**DATETIME**

这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到一个格式为YYYYMMDDHHMMSS I的整数中，与时区无关。它使用了8字节存储空间

在默认情况下， MYSQL以一种可排序的、清楚的格式显示 DATETIME值，例如2008-01-16 22:37208.这种表示日期和时间的方式符合ANSI标准

**TIMESTAMP**

就像它的名字一样， TIMESTAMP类型保持了自1970年1月1日午夜(格林尼治标准时间)以来的秒数 它和Unⅸ的时间数相同。 TIMESTAMP只使用了4字节的存储空间，因此它的范围比 DATETIME小得多。它表示只能从1970年到2038年。 MYSQL提供了FROM\_ UNIXTIME()函数把Unix时间藏转换为日期，并提供录入UNIX\_TIMESTAMP()函数，把日期转换为unix时间戳。

较新的 MYSQL版本按照 DATETIME格式化 TIMESTAMP的值，但是较老的 MYSQL不会在各个部分之间显示任何标点符号。

TIMESTAMP显示的值也依赖于时区。 MYSQL服务器、操作系统及客户端连接都有时区设置。

保存0值的 TIMESTAMP实际显示为美国东部时间1969-12-31 19:00:00,与格林尼治标准时间(GMT)相差5小时。

TIMESTAMP也有 DATETIME没有的特殊性质。在默认情况下，如果插入的行没有定义 TIMESTAMP列的值，MYSQL就会把它设置为当前时间(注3)，在更新的时候，如果没有显式地定义 TIMESTAMP列的值， MYSQL 也会自动更新它。可以配置 TIMESTAMP列的插入和更新行为。最后， TIMESTAMP.列默认是not null，这和其他的数据类型都不一样

除了这些特殊行为之外，通常应该使用 TIMESTAMP，因为它比 DATETIME更节约空间。有时人们把Unⅸ的时间戳保存为整数值，但是这通常没有任何好处。因为这种格式处理起来不太方便，我们并不推荐它。

如果需要以秒以下的精度保存日期和时间，应该怎么办? MYSQL当前没有适合的数据类型，但是可以使用自己的存储格式:可以使用 BIGINT类型井且把它以毫秒的精度保存为时间戳格式，或者使用 DOUBLE保存秒的分数部分。这两种办法都不错。