05 MySQL数据类型的选择与使用
数据类型的选择
1.CHAR与VARCHAR
存储/检索的方式不同.
CHAR是固定长度,而VARCHAR是可变长度
非SQLMode下,超过指定长度,会做截取操作.SQLMode模式下,会报错
'adcdefgh'插入到CHAR(4)和VARCHAR(4)中,前者需要4字节,后者需要5字节,结果都是非SQLMode下 存储了'abcd' todo 为什么VAR(4) 需要5字节??
CHAR会去掉尾部的空格,VARCHAR则不会.
CHAR处理速度快,但浪费存储空间,对长度变化不大,且对查询速度有较高要求的推荐使用CHAR
VARCHAR 随着MySQL的不断升级,性能也在不断提高,许多应用用使用VARCHAR.
不同引擎对CHAR和VARCHAR的使用原则不同:
MyISAM : 建议使用固定长度代替可变长度,因此推荐CHAR
MEMORY : 无论使用CHAR或VARCHAR都默认处理成固定长度的数据存储.
InnoDB : 建议使用VARCHAR .内部存储格式没有区分固定长度/可变长度.性能因素主要是数据行使用的存储总量.CHAR的平均空间大于VARCHAR ,用VARCHAR进行存储和磁盘I/O比较好.
2.TEXT与BLOB
少量字符串选择CHAR/VARCHAR,大文本选择TEXT/BLOB.
二者之间的区别是,BLOB可以用来保存二进制数据,比如照片;而TEXT只能保存字符数据,比如文章/日记.
TEXT/BLOB 会引发性能问题,特别是在执行了大量的删除操作时.
删除操作会在数据表中留下很大的"空洞",对日后插入操作的性能上有影响.
为了提高性能,建议定期使用OPTIMIZE TABLE 对这类表进行碎片整理.
查看表的物理文件大小:
du -sh tbl_name.* ;
经测试,即使进行了删除操作,表的物理大小并没有减少.
OPTIMIZE TABLE tbl_name ;
优化后发现物理文件减小了.
可以使用合成的(Syntheic)索引来提高TEXT/BLOB的查询性能.
合成索引就是根据大文本的内容建立一个散列值,并把该值存储到一个单独的数据列中.接下来就可以通过它进行查找数据.
注意,该方式只能对精确查找的方式进行优化,而类似于< , >= 这种范围查找没有用处.
散列值可以通过MD5(),SHA1(),CRC32()等方式生成,也可以使用自己的应用程序逻辑来计算散列值.
数值型的散列值可以很高效的存储.如果生成的散列值尾部有空格,就不要存储到CHAR/VARCHAR列中. todo 为什么VARCHAR 也不行,VARCHAR不是不对尾部空格进行处理么??
Demo:
CREATE TABLE t (id VARCHAR(100) , context BLOB , hash_value VARCHAR(40) ;
INSERT INTO t VALUES(1,REPEAT('beijing',2),MD5(context));
SELECT * FROM t where hash_value=MD5(repeat('beijing 2008',2));
如果要使用模糊查询,MySQL提供了前缀索引,也就是只为字段的前n列创建索引.
Demo:
CREATE INDEX idx_blob ON t (context(100)); --为context的前100个字符创建索引.
DESC SELECT * FROM t WHERE context LIKE 'beijing%' ; --'%'不能放在最前面,否则索引不起作用.
注意:
避免在不必要的时候检索大型的BLOB/TEXT.除非WHERE字句可以精确匹配到哪一行.
推荐使用索引进行检索,决定需要哪些数据行,然后从这些数据行中检索BLOB/TEXT的值.
把BLOB/TEXT列分离到单独的表中.减少主表中的碎片,也不会在SELECT * 进行查询时是一哦那个网络传输大量的BLOB/TEXT 值.
3.浮点数与定点数
浮点数,当精度超过实际定义的精度,会四舍五入,并不会报错.
定点数,实际是以字符串形式存放的,所以定点数可以更加精确的保存数据.
默认的SQLMode下会报警,但是还是会四舍五入进行保存;在传统模式(TRADITIONAL)的SQLMode下,会报错,无法插入.
浮点数,比如使用单精度float(10,2) 插入2.32 , 可能插入结果是2.31 ,所以浮点数会产生误差,这是浮点数特有的问题
因此精度要求比较高的场景,比如金融/货币,要使用定点数.
浮点数的比较也是一个普遍问题.编程中,尽量避免浮点数的比较,如果非要比较,也最好使用范围比较,而不是 '=='
Java中把浮点数转化为BigDecimal,在进行比较:
BigDecimal b1 = new BigDecimal(Double.toString(d1));
BigDecimal b2 = new BigDecimal(Double.toString(d2));
b1.subtract(b2).doubleValue();
4.日期类型的选择
根据场景,选择最小的存储日期类型.
如果只需要"年份",则使用1字节来存储YEAR类型,而不需要用4字节来存储DATE类型.
如果记录的年份比较久远,使用DATETIME , 而非TIMESTAMP.
如果记录的日期要让不同时区的用户使用,最好使用TIMESTAMP
