Mysql优化之选择合适的数据类型

在设计数据库表时,合适的数据类型可以提高数据库的存取效率,节省存储空间.本文介绍了mysql的常用数据类型.以及设计数据表时的注意事项.

1.时间类型

Datetime 和Timestamp都能存储相同类型的数据,并且精确到秒.然而Timestamp只使用Datetime一半的存储空间,并且会根据时区变化,具有自动更新的能力.但是Timestamp允许的时间范围要小很多.(1970.1.1 - 2038.1.18).Datetime类型可以保存大范围的值,从1001年到9999年,精确到秒.

2.整数类型

可以用来存储实数和整数.tinyint smallint mediumint int bigint 分别占用 8,16,24,32,64位存储空间.有可选的unsigned属性,表示不允许负值,可以使正数的上限提高一倍.

3.实数类型

包含了小数部分.然而,它们不只是用来存储小数,也可以使用decimal来存储比bigint还大的整数.float 和double支持标准的浮点运算.decimal用来存储精确的小数.因为需要额外的空间和计算开销.所以应该尽量只在需要对小数进行精确计算时使用decimal.

4.字符串类型.

varchar和char是最主要的两种字符串类型.varchar用来存储可变长字符串,是最常见的字符串类型.
char的字符串是定长的,适合存储很短的字符串,或者定长的字符串.例如char很适合用来存储MD5摘要,因为这是一个定长的值.对于经常变更的数据,char也比varchar更好.因为定长的char不容易产生碎片.
(注意 : 更长的列会消耗更多的内存,因为Mysql会分配固定大小的内存块用来保存内部值,尤其是在使用临时表排序时.所以最好只分配真正需要的空间)

5.大数据类型

blob和text都是为了存储大数据而设计的字符串数据类型.分别才用二进制和字符串方式存储.
与其他类型不同,mysql会把blob 和 text当做独立的对象来处理.存储引擎在存储时会做特殊处理,当blob和text值太大时,InnoDB会使用专门的外部存储区域进行存储.
blob和text唯一的差别是,blob存储的是二进制数据,没有字符集和排序规则.而text有字符集和排序规则.
blob和text的排序规则 和其他数据类型不同,它们只对每个列最前max_sort_length做排序,而不是整个字符串.
mysql不能使用blob 和 text列全部长度的字符串进行索引,也不能使用这些字符串索引排序.

6.枚举类型

有时候可以使用枚举列代替常用的字符串类型.枚举可以把一些不重复的字符串存储成一个预定义的集合,mysql在存储枚举时非常紧凑,会根据列表值的数量压缩到1-2字节.
mysql会在存储每个值时存储整数.并且在排序时根据内部存储的整数进行排序,而不是定义的字符串.
枚举的问题在于,字符串列表是固定的.因此添加或者删除字符串必须使用alter table.因此,对于一系列可能会改变的字符串.使用枚举不是一个好主意.除非你能接受只在列表末尾添加元素,在mysql5.1以后不需要重建整个表来完成修改

7.位数据类型

mysql有少数几种存储类型使用紧凑的位存储数据.所有这些位类型,不管底层存储格式和处理方式如何,从技术上来说都是字符串类型.
bit类型可以存储一个或多个true/false值.bit(1)定义一个包含1个位的字段,bit(2)存储两个位,以此类推.
set类型可以有零或多个值.它的行为跟枚举类似,其值来自表创建时规定的允许的一列值,改变列的定义需要alter table.这对大表来说是非常昂贵的操作.
虽然在set上无法使用索引查找,但是mysql提供了像find_in_set()和field()这样的函数,方便在查询中使用
一种替代set的方法是,使用一个整数包装一系列的位.例如,把8个位包装到一个tinyInt中,并按照位操作来使用.比起set,这样使用的好处是可以不使用alter table 改变字段的枚举值,缺点是查询语句更难写,而且更难理解.因此是否采用这种技术取决于个人的偏好.

8.二进制类型

二进制类型是在数据库中存储二进制数据的数据类型.二进制类型包括binary，varbinary，bit，tinyblob，blob，mediumblob，longblob。
binary类型的长度是固定的,不足最大长度的空间由‘\0’补全.
varbinary的长度是可变的,在创建时指定了最大长度.varbinary类型实际占用的空间为实际长度加一。这样，可以有效的节约系统的空间。

注意事项 :

1.更小的字段通常更好.一般情况下,应该尽量使用可以正确存储数据的最小数据类型.
2.简单的类型比复杂类型的操作消耗的CPU周期少.比如 整型比字符串操作代价更低.因为字符集和校对规则(排序规则)使字符串比整型更复杂.
3.尽量避免NULL值.如果查询条件中包含可为NULL的列,对Mysql来说更难优化.因为可为NULL的列使得索引和值比较都更加复杂.(但是,把可为NULL的列改为NOT NULL 对性能提升比较小.所以在查询优化时不需要优先考虑)
4.存储IPV4地址时,使用无符号整数.而不是varchar(15).Mysql提供inet_aton()和inet_ntoa()函数在这两种表示方法之间转换
5.在选择标识列(ID Column),整数类型通常是最好的选择.因为它们很快并且可以使用自增值.尽量避免使用字符串类型,特别是在MyISAM中,mysql默认对字符串使用压缩索引,这会导致查询慢许多.
6.如果存储UUID值,则应该移除 " - " 符号.或者更好的做法是,用unhex()函数转换uuid为16字节的数字,并且存储在一个binary(16)中.检索时可以通过hex()函数将id值格式化为16进制格式.