MySql

数据类型

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

⭐ 更小的通常更好

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

⭐ 简单就好

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

⭐ 尽量避免NULL

很多表都包含可为NULL（空值）的列，即使应用程序并不需要保存NULL值也是如此，这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。

如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列回事用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录都需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

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

在为列选择数据类型时，第一步需要确定合适的大类型：数字、字符串、时间等。这通常是很简单的，但是会有一些数据类型的选择不那么的直观，很多MySQL的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间（磁盘和内存空间）不同。相同大类型的不同子类型数据有时也有一些特殊的行为和属性。

比如，datetime 和 timestamp 列，decimal 和 double 列等等。

整数类型

现实中有两种类型的数字：整数和实数。如果存储整数，可以使用这几种类型：tinyint，smallint，mediumint，int，bigint ，分别使用 8，16，24，32，64 位存储空间。

整数类型有可选的UNSIGED属性，表示不允许负值，这大致可以使正数的上限提高一倍。（例如 tinyint unsiged 可以存储 0 到 255，而 tinyint 的存储范围是 -128 到 127）

MySQL可以为正数指定宽度，例如 int(11) ，对于大多数应用这个是没有意义的，它不会限制值的合法范围，只是规定了MySQL的一些交互工具用来显示字符的数。对于存储和计算来说， int(1) 和 int(20) 是相同的。

实数类型

实数类型是带有小数点部分的数字。然后它们不只是为了存储小数部分，也可以使用 decimal 存储比 bigint 还大的整数。MySQL即支持精确类型，也支持不精确类型。

浮点类型FLOAT和DOUBLE支持使用标准的浮点运算进行近似计算。DECIMAL类型用于存储精确的小数。因为CPU不支持对DECIMAL的直接计算，相对而言，CPU直接支持原生浮点类型计算，所以浮点类型运算明显更快。

FLOAT占用4个字节，DOUBLE占用8个字节，MySQL使用DOUBLE作为内部浮点计算的类型。浮点类型通常比DECIMAL使用更少的空间，因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL（例如存储财务数据），但数据量叫大时，可以考虑使用bigint代替DECIMAL，这样可以避免浮点计算不精确和DECIMAL精确计算代价高的问题。

字符串类型

Varchar

Varchar类型，用于存储可变长度字符串，是最常见的字符串数据类型。它比定长类型更节省空间。有一种情况例外，如果表使用 ROW_FORMAT=FIXED 创建的话，每一行都会使用定长存储，会很浪费空间。

Varchar需要使用1或2个额外字节记录字符串的长度，如果列长最大长度 小于等于 255字节，则只使用1个字节表示，否则使用2个字节。Varchar节省了存储空间，由于是变长度的，在UPDATE时可能使行变得比原来更长，这就会导致要做额外的工作。

下面这些情况适合使用Varchar：

• 字符串列的最大长度比平均长度大很多；
• 列的更新很少，索引碎片不是问题；
• 使用了UTF-8这样复杂的字符集，每个字符都使用不同的字节进行存储。

⭐ 慷慨是不明智的，使用Varchar(5) 和 Varchar(200) 存储 'Hello' 的空间开销是一样的，那么使用短的列有什么优势呢？更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也通用糟糕。所以最高的策略是只分配真正需要的空间。

Char

Char类型是定长的，Char和Varchar在逻辑上是一样的区别只是在存储格式上。Char类型的值会根据需要采用空格进行填充以方便比较。Char类型适合存储很短的字符串，或者所有值都接近同一个长度。

对于经常变更的数据，Char比Varchar更好，因为定长Char类型不易产生碎片。对于非常短的列，Char的存储空间也优于Varchar，例如用Char(1)来存储只有 Y和N的值，Char只用1个字节，而Varchar却需要2个字节，因为还有1个字节来记录长度的额外字节。

与Char和Varchar类似的类型还有Binary和Varbinary，他们存储的是二进制字符串。

Blob 和 Text

blob和text都是为存储大的数据而设计的字符串类型，分别采用二进制和字符方式存储。Blob 和 Text的仅有区别是：Blob存储的是二进制数据，没有排序规则和字符集，而Text又字符集和排序规则。

MySQL不能讲Blob和Text列全部长度的字符串进行索引，也不能使用这些索引消除排序。

日期和时间

MySQL提供2种相似的日期类型，Datetime和Timestamp。

Datetime，它能保存大范围的值，从 1001 年到 9999年，精度为秒，存储格式为YYYYMMDDHHmmSS的整数，与时区无关，使用8个字节存储空间。

Timestamp，就像它的名字一样，Timestamp类型保存了从 1970年1月1日午夜（格林尼治时间）以来的秒杀，他和UNIX时间戳相同。它只有4个字节的存储空间，因此它的范围比Datetime小得多，只能从1970年到2038年。

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

⭐ 除了特殊情况外，通常应该尽量使用Timestamp，因为它比Datetime空间效率更高。

中国青海，卓尔山

作者： Zealon
崇尚简单，一切简单自然的事物都是美好的。