数据库字段数据类型的选择与优化_数据库的数据类型怎么选

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

一、Mysql基本数据类型

1.1 整数类型：tinyint、smallint、mediumint、int、bigint

1.2 实数类型：float、double、decimal

1.3 字符串类型：var、varchar、blob、text、enum、set

1.4 日期类型：datetime、timestamp

二、优化基本原则

2.1 更小的通常更好

一般情况下,尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。

比如：是有一个类型既可以用字符串也可以使用整型，优先选择整型。因为字符串牵涉到了字符集及校对规则等。

2.2 简单就好

简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。比如应该使用MySQL内置的数据类型而不是字符串来存储日期和时间。

2.3 尽量避免null

通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。

如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计以及比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节。

通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以（调优时）没有必要首先考虑这种修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。

三、常见的数据类型选择

3.1 Int/整数类型

存储整数，可以使用这几种整数类型:TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分就占用1、2、3、4、8个字节。

注意：

• 整数类型有可选的 UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0255，而TINYINT的存储范围是-128-127。
• 有符号和无符号类型使用相同的存储空间，查询性能是相同的，因此可以根据实际情况选择合适的类型。另外integer和int存储及大小没有任何差别，只是为了业务上区分。
• MySQL可以为整数类型指定宽度，如INT(11)，对大多数应用这是没有意义的，它不会限制值的合法范围，只是规定了MySQL的一些交互工具，用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。
• MySQL中没有long型，对应的只有bigint

 3.2 实数类型

3.1.1 实数是带有小数部分的数字。MySQL既支持精确类型的存储decimal类型，也支持不精确类型存储float和 double类型（浮点类型）。decimal类型用于存储精确的小数，本质上MySQL是以字符串形式存放的。所以CPU不支持对decimal的直接计算，只是在MySQL中自身实现了decimal的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。

3.1.2 浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储，double占用8个字节，decimal里面存储65个数字，decimal对于列的空间消耗比较大，另外double比 float有更高的精度和更大的范围。

3.1.3 如何选择实数类型

• 在精度要求不高的快速运算的场景下，选择float和 double
• 尽量只在对小数进行精确计算时才使用decimal，例如存储财务或金融数据
• 但在数据量比较大的而且要求精度时，可以考虑使用bigint代替decimal，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一元，则可以把所有金额乘以一万，然后将结果存储在bigint里，这样可以同时避免浮点存储计算不精确和 decimal精确计算代价高的问题

3.3 字符串类型

MysQL支持多种字符串类型，包括VARCHAR和CHAR类型、BLOB和TEXT类型、ENUM（枚举）和SET类型。VARCHAR和 CHAR是两种最主要的字符串类型。

3.2.1 VARCHAR

VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间)。在内部实现上，由于是可变长，VARCHAR需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于或等于255字节,则只使用1个字节表示，否则使用2个字节。

VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时新值比旧值长时，使行变得比原来更长，这就肯能导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

3.2.2 CHAR

CHAR类型是定长的，MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格，CHAR值会根据需要采用空格进行填充以方便比较。

3.2.3 CHAR与VARCHAR使用场景

• 字符串列的最大长度比平均长度大很多，列的更新很少，每个字符都使用不同的字节数进行存储，一般使用VARCHAR
• CHAR适合存储很短的字符串，或者所有值定长或都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。
• 对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR( 1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。
• 另外，使用CHAR(5)和VARCHAR(200)存储'hello'在磁盘空间上开销是一样的。但是站在性能角度，我们通常会选择CHAR，虽然实际存储大小一致，但是实际分配内存的时候，VARCHAR可能会分配更多内存

3.2.4  BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。与其他类型不同，MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

BLOB和TEXT不同的是BLOB类型存储的是二进制数据，没有排序规则或字符集；而 TEXT类型有字符集和排序规则。

BLOB和TEXT使用时应注意：

1. BLOB和 TEXT 值会引起一些性能问题，所以尽量避免使用BLOB和TEXT类型；
2. 一定要用，建议把BLOB或TEXT 列分离到单独的表中，需要的时候再关联查询
3. 在不必要的时候避免检索大型的 BLOB或TEXT值。例如，SELECT * ，除非能够确定作为约束条件的WHERE子句只会找到所需要的数据行。建议可以搜索索引列，决定需要的哪些数据行，然后从符合条件的数据行中检索BLOB或 TEXT值；
4. 还可以使用合成索引来提高大文本字段(BLOB或TEXT)的查询性能。合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似“<”或“>=”等范围搜索操作符是没有用处的)。

3.2.5  枚举类型

 如果表中的字段的取值是固定几个字符串，可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中，MySQL在内部会将每个值在列表中的位置保存为整数，这样的话可以大大缩小表的大小。

注意：

1. 因为枚举列实际存储为整数，而不是字符串，所以不要使用数字作为ENUM枚举常量，这种双重性很容易导致混乱，例如ENUM( '1','2','3')。
2. 枚举字段是按照内部存储的整数而不是定义的字符串进行排序的，所以尽量按照需要的顺序来定义枚举列。

3.4 日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如YEAR和 DATE以及DATETIME和TIMESTAMP。MySQL能存储的最小时间粒度为秒。

datetime 存储日期范围：1001年~9999年，与时区无关

timestamp 存储日期范围：1970年~2038年,并且跟时区有关系。

如果需要存储比秒更小粒度的日期和时间值：可以使用自己的存储格式，例如可以使用BIGINT类型存储微秒级别的时间截，或者使用DOUBLE存储秒之后的小数部分。