如何为MySQL选择更合适的数据类型
更小的通常更好
在表设计最开始之初,我认为这里的选择合适的数据类型是非常关键的,他可以尽可能减轻数据库的硬盘占用,和在查询的过程中越简单越小的数据类型则需要的占用了更小的cpu缓存,内存,硬盘,和相对应的处理cpu的周期。
简单就好
数据类型的选择应该保持越简单越好的准则, 比方说整形比起字符类型来说的代价更低,因为字符类型的校对规则和排序规则相对于整形来说更加复杂。
应该使用mysql 的内建类型而不是选择字符串来进行存储日期和时间,也应该使用整形来存储ip地址。
尽量避免NULL
而且应该避免使用null类型,因为NULL对MySQL来说会更难进行优化,null列会使得索引、索引统计、和值比较发生更为复杂的变化,并且为null的列会使用更多的储存空间在MySQL中也需要更特殊的处理,当null被索引的时候
每个索引记录会需要一个额外的字节,在myisam 中甚至会导致固定大小的索引(例如只有一个整数列索引)变成可变大小的索引。通常null的列改为not null 带来的性能提升比较小,所以(调优时)没有必要首先在现有的数据表
中查找并且修正 ,除非确定这块会导致问题,但是在计划创建索引的列中,就应该尽量避免设计成null列,但值得一提的是在innodb中使用进行null列的存储是使用单独的位(bit)进行存储null 的值,在大量出现null 的情况,有很好
的空间效率。但这点不适用于myisam。
下面进行进行数据类型
数字类型
整数类型
MySQL中有两种数字类型:整数(whole number)和实数(real number)。如果存储整数,可以使用以下的整数类型:
TINYINT | SMALLINT | MEDIUMINT | INT | BIGINT | |
储存空间(位) | 8 | 16 | 24 | 32 | 64 |
存储值范围 | -128~127 | -32768~32767 | -8388608~8388607 | -2147483648~2147483647 | -9223372036854775808~9223372036854775807 |
其中存储值范围计算公式为-2^(N-1)~2^(N-1)-1,其中N是存储空间的位数。
其中整数类型可以选用UNSIGNED属性,表示不允许为负值,这大致可以使得正数的上限提升一倍,比如TINYINT UNSIGNED 可以存储的范围是0~255,而TINYINT 的范围是-128~127。
您的选择将会决定mysql是怎样在内存和磁盘保存数据的。然而整数类型的计算一般使用64位的BIGINT整数,即使在32位的环境也是如此。(一些聚合函数是例外,他们使用的是decimal或double进行计算)
mysql可以为整数类型指定宽度 ,例如INT(11),其实这里对于大多数应用是没有意义的,他不会限制值的合法范围,只是规定了mysql中的交互工具(例如mysql命令行客户端)用来显示字符的个数。对于存储
和计算来说INT(1)和INT(10)的作用是相同的,这里很多的人都会误解,包括我。
实数类型
首先实数类型就是带有小数点的数据类型,在权威指南书中看来,mysql进行存储实数类型的数据数据占用的空间比起整数类型要高的多,基本单位都是以字节为单位,而整数类型是以bit为单位1字节=8bit,
说说实数类型的特点,FLOAT、DOUBLE是依赖于所使用平台的浮点数具体实现的,所以这种类型是不精确的,但由于是使用cpu直接进行的原声浮点运算,所以浮点运算的速度明显会更快,
而DECIMAL 是由mysql服务器自身实现的,其中它将数字列打包保存到了一个二进制字符串中(每4个字节存储9个数字),例如DECIMAL(18,9)主要看9, 它是将小数点两边各存储9个数字,小数点占用1个字节,
一共使用9个字节 ,小数点前后各使用4个字节,小数点一个字节。且DECIMAL也可以用来存储比BIGINT范围更大的数字。
实数类型包括了FLOAT、DOUBLE、DECIMAL
FLOAT | DOUBLE | DECIMAL | |
存储空间(字节) | 4 | 8 | 自己设定(最大支持65位数字) |
因为需要额外的空间和开销,所以应该在只对小数进行精确计算的时候才使用DECIMAL ,例如存储财务数据。但是在数据量比较大的时候,可以考虑使用BIGINT 进行代替DECIMAL ,将需要存储的货币单位乘于相应的倍数,
假设要存储的财务数据需要精确到万分之一,则可以将所有金额乘于一百万,然后将结果存储到BIGINT中,这样可以同时避免计算不精确和DECIMAL 精确计算代价高的问题。
字符类型