参考:O'Reilly的第三版高性能MySql和阿里巴巴java代码规范1.2.0。
总结的内容基本是mysql5.0以上的版本,存储引擎是InnoDB。
1、一般使用数据类型原则;
a、越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。
b、简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂。在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;以及用整型数据类型存储IP地址。
c、尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL。在MySQL中,含有空值的列很难进行 查询优化 ,因为它们使得 索引、索引的统计 信息以及 比较运算 更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
d、如果是非负数的字段,使用unsigned,这样的表达的范围扩大了一倍;
e、存储小数类型的时候使用decimal,不要用float和double,因为计算的时候精度会丢失。(js中的计算也需要需要注意,精度丢失前后端都会出现,可以扩大小数到整数,然后计算,或者用Math.round()把计算后的结果四舍五入)
2、整形类型:tinyint、smallint、mediumint、int、bigint对应的字节数1、2、3、4、8 ,表达范围-2^(n-1) ~ 2^(n-1)-1。
说明:整数的列宽的作用(int(M))
a. M和数据类型的取值范围是无关的,int(1)和int(10)的取值范围是一样的。
b. M只是指明MYSQL最大可能显示的数字个数,数值的位数小于M时会有空格填充;大于M时,只要该值不超过该类型整数的取值范围,数值依然可以插入,而且能显示出来。
c. M的效果需要配合zerofill使用。
3、实数类型:
a、float、double分别占用4个字节,8个字节;
b、decimal是一种存储类型,DECIMAL(M,D),占用字节M+2 bytes。M是数字最大位数(精度precision),范围1-65;D是小数点右侧数字个数(标度scale),范围0-30,但不得超过M。
比如定义DECIMAL(7,3):
能存的数值范围是 -9999.999 ~ 9999.999,占用9个字节
123.12 -> 123.120,因为小数点后未满3位,补0
123.1245 -> 123.125,小数点只留3位,多余的自动四舍五入截断
12345.12 -> 保存失败,因为小数点未满3位,补0变成12345.120,超过了7位。严格模式下报错,非严格模式存成9999.999
一般货币类型用整形bigint x100用分去处理;
4、varchar有关字节和字符的:
a、MySQL 数据库的varchar类型在4.1以下的版本中的最大长度限制为255,其数据范围可以是0~255或1~255(根据不同版本数据库来定)。在 MySQL5.0以上的版本中,varchar数据类型的长度支持到了65535(2^16-1),也就是说可以存放65532个字节的数据,起始位和结束位占去了3个字 节;
b、varchar(M),M为0-255的时候需要使用一个字节表示长度,M大于255需要使用2个字节表示存储的长度;
字符类型若为gbk,每个字符最多占2个字节,最大长度不能超过32766;
字符类型若为utf8,每个字符最多占3个字节,最大长度不能超过21845(一般是5000以内,超过的话用text)。(例:length("你好")=6与char_length("你好")=2)
c、char对比varchar
char与varchar都是存储字符串的数据类型。
char是固定长度的字符类型,而varchar是可变长度的字符类型,这个一定要注意。另外进行select时数据末尾有空格的话,char会将其去掉。
char定长的不容易产生碎片,char(1)只需一个字节,而varchar(1)需要两个,一个用来表示长度。
5、blog和text都是存储大数据的,一个是二进制一个是字符方式,mysql会当做一个独立对象处理,对该列进行排序的时候是使用每列的最前max_sort_length字节,可以配置的,或者自己定义order by substring(column, length);
datetime年的范围(1001-9999)占用8个字节 没有时区, timestamp年的范围(1970-2038) 占用4个字节 有时区。