mysql表字段设计总结

mysql的性能优化，涉及到很多知识，而良好的表设计是提升性能的基础，特别是针对于大表而言，好的设计对性能提升很大，以下是设计方面的几个优化点

1. 尽量避免null列

• not in ,!=等负向条件下null列返回的是空结果

• 对于null column，count(null column)是不计入统计结果的

• null列会占用多一个字节的空间，来表明是否为空

• 大多数情况下，null列都是可以用默认值来替代的

2. 尽量使用更简单的数据类型

1. 简单意味着更低的磁盘存储，内存占用和cpu性能开销，特别是大数据量高并发的情况下，对性能影响比较大
2. 对于索引列，越小的数据类型，意味着每个数据页能存更多的分支，减少索引的次数

2.1 整数

整数类型主要有： tinyint,smallint,mediumint,int,bigint,分别用8，16，24，32，64个位进行存储，存储范围是-2^N-1-1 ~ 2^N-1,N表示存储的位数。可以使用unsigned来表明正数，这样可以使存储的上限变成2^N-1,将近扩大了一半。
另外特别要注意的是，mysql中整型可以指定宽度，但是这个宽度并不是用来限制这个列的长度的，只是用来对查询的结果进行补0显示。也就是说int(1)和int(7),int(100)都是只能存8个byte32位,范围都是-2³¹-1 ~ 2³¹，指定的宽度唯一的作用就是用来对客户端查询结果进行补0，而且得开启zero_fill才行，比如age int(3) ZEROFILL，如果插入值为10，那么查询的结果为010，插入1000，结果为1000。
如果确定没有负数，可以使用unsigned 类型，让可存储的上限能扩大一倍，间接的使用更小的数据类型

2.2 实数

1 float和double来表示，属于浮点数，存在精度丢失问题，但是因为cpu原生支持浮点运算，所以性能更好,float最大支持存储4个字节，double最大支持8个字节
2 decimal 属于定点数，精确类型，但是由于是通过mysql实现的精度计算，相对于浮点型来讲性能要差点
实数类型选择：
1 如果精度要求不高，直接使用float或double
2 如果对精度要求高，性能要求不高，可以考虑使用decimal
3 如果对精度和性能都有要求，可以考虑将小数转为整型存储，
比如财务系统，需要精确为分，可以将金额乘以100进行保存到int或bigint里面

2.4 字符串

使用varchar要尽量设定小的字节数，因为mysql在创建临时表或排序的时候，会悲观的给varchar类型分配最大的长度

总结：
1 字段设计时尽量选择小而简单的数据类型越好
2 除非必要，不使用可空列，用默认值代替
3 varchar类型虽然是变长，也应该按实际所需分配字节
4 整型不需要指定长度
5 只有正数考虑用unsigned,可以扩大上限
6 实数精度要求不高情况，优先使用浮点类型float，double,如果精度要求高可考虑用整型转化或直接使用decimal类型