选择数据类型的原则
1.更小的通常更好:一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为它们占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少。
2.简单就好:简单数据类型的操作通常需要更少的CPU周期。例如,整型比字符操作代价更低,因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。这里有两个例子:一个是应该使用MySQL内建的类型而不是字符串来存储日期和时间,另外一个是应该用整型存储IP地址。
3.尽量避免NULL:如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL利也需要特殊处理。当可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM里甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。
InnoDB使用单独的位(bit)存储NULL值,所以对于稀疏数据有很好的空间效率。
整数类型
TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间。它们可以存储的值的范围从-2的(N-1)次方到2的(N-1)次方减一,其中N是存储空间的位数。
整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0~255,而TINYINT的存储范围是-128~127。因此可以根据实际情况选择合适的类型。
MySQL可以为整数类型制定宽度,例如INT(11),对大多数应用这是没有意义的:他不会限制值的合法范围,只是规定了MySQL的一些交互工具(例如命令行)用来显示字符个数。对于存储和计算来说,INT(1)和INT(20)是相同的。
实数类型
实数是带有小数部分的数字。但是不只是为了存储小数,也可以用DECIMAL存储比BIGINT还大的整数。
FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。DECIMAL类型用于存储精确的小数,并且它可以指定小数点前后的所允许的最大位数。这会影响列的空间消耗。在较高的版本将会把数字打包保存到一个二进制字符串中(每4个字节存9个数字)。例如DECIMAL(18,9)小数点两边将各存储9个数字,一共使用9个字节:小数点前的数字用4个字节,小数点后的数字用4个字节,小数点本身占1个字节。而DECIMAL类型允许最多65个数字,注意DECIMAL只是一种存储格式,计算时会转换为DOUBLE类型。DOUBLE是MySQL内部计算类型。
浮点类型在存储同样范围的值时,通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节,相比FLOAT有更高的精度和更大的范围。
由于消耗问题。所以应该有小数精确计算时才用DECIMAL。另外,某些场合可以考虑用BIGINT代替DECIMAL,比如存储财务数据,可以将需要存储的货币单位根据小数的位数乘以相应的倍数,然后用BIGINT存储。
字符串类型
VARCHAR和CHAR类型
VARCHAR:VARCHAR类型用于存储可变长字符串,因此它仅使用必要的空间,它比定长类型更节省空间。VARCHAR需要使用1或2个额外字节记录字符串的长度,如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。
VARCHAR节省了存储空间,所以对性能也有帮助。但是,由于行是变长的,在UPDATE时可能使行变得更长,这就需要做额外的工作。如果一个行占用的空间增长,并且在页内没有更多的空间可以存储,这种情况下,不同的存储引擎的处理方式不一样。例如MyISAM会将行拆成不同的片段存储,InnoDB则需要分裂页来使行可以放进页内。这样一来就会产生很多内存碎片。
根据上面的描述可以确定VARCHAR适合的情况:
1.字符串列的最大长度比平均长度大很多;
2.列的更新很少所以碎片不是问题;
3.使用了像UTF-8这样复杂的字符集,每个字符都使用不同的字节数进行存储。
CHAR:CHAR类型是定长的,MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时,MySQL会删除所有的末尾空格
1.CHAR适合存储很短的字符串,或所有值都接近同一个长度。例如,CHAR非常适合存储密码的MD5值,因为这是一个定长的值。
2.对于经常变更的数据,CHAR也比VARCHAR更好,因为定长的CHAR类型不容易产生碎片。
3.对于非常短的列,CHAR也比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值,如果采用单字符集只需一个字节,但是VARCHAR(1)却需要两个字节,因为还有一个记录长度的额外字节。
注:使用VARCHAR(5)和VARCHAR(200)存储较小的数据时,虽然空间开销一样,但是还是用VARCHAR(5)比较好。因为更长的列会消耗更多的内存,MySQL通常会分配固定大小的内存块来保存内部值。所以最好的策略就是分配真正需要的空间。
日期和时间类型
DATETIME
这个类型能保存大范围的值,从1001年到9999年,精度为秒。他把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字节的存储空间。默认情况下,MySQL以一种可排序的格式显示DATETIME值。
TIMESTAMP
保存了从1970年1月1日午夜以来的秒数。它只使用4个字节的存储空间,因此它的范围比DATETIME小的多。
TIMESTAMP显示的值也依赖于时区,MySQL服务器、操作系统,以及客户端连接都有时区设置。
应该多用TIMESTAMP因为它比DATETIME空间效率更高。