schema与数据类型优化
schema优化
太多的列:mysql的服务层和存储引擎层通过行缓冲格式拷贝数据,然后在服务器将缓存内容解析成列。当列很多时,转换的代价逐渐增大
太多的关联:关联虽然符合范式要求,但是会造成查询时间量增大和使索引失效
值为null:值为null时,索引要做什么很多复杂和额外处理,需要额外的字节记录,索引统计也很麻烦而且可为null的列会使用更多的存储空间
类型的优化
整数型和字符类型,优先字符类型,字符类型在数据库要做很多额外的解析工作
浮点型与精确类型(Decimal)与bigint的选择:当计算的数值不敏感是可以采用浮点型,其中float 4个字节 ,double 是8个字节,mysql内部支持浮点数直接计算,而不支持decimal直接计算,而且decimal的需要存储小数点,指定精度,增加额外的计算和空间开销
所以只有精确计算才用到。但是decimal最大支持65位,包含小数点前和小数点后,这是bigint就能用到了,只要乘以对应的倍数就行,所以bigint是三者效率最好的
varchar类型与char类型
varchar类型:
好处:不定长,可以节省空间。但是需要1到2个字节存储字符的长度(<255 用1个字节存储)
坏处:因为不定长,update语句可能造成字符串长度变长,当页内没有更多的空间时,此时需要分裂页使行可以放进页内
用法:保存的值平均长度远远小于可变长度的最大长度。列的更新很少
char类型:
好处:更新不需要考虑页内碎片的问题
坏处:定长,预先要估算好字符长度,空间浪费,默认会删除存储进去的值尾部的空格
用法:适用定长的值如UUID,适用很短的列(相对于varchar效率高,varchar有记录长度的开销),适用更新多的列(不会产生页内碎片)
注意:使用varchar(10)和varchar(100) 存储同样的值如"hello"的开销是一样的,但是为什么不会选择后者呢?因为mysql会分配固定大小内存的保存内部的值,更长的列会消耗更多的内存(你用这个很多内存,别的人就少了,公共资源尽量按需求用)
枚举类型:
用枚举类型代替字符串类型,枚举类型在数据库存储的是整型,所以对存储友好,但是如果用该列用来查询或者比较就需要额外的开销,得补偿是失,一般不推荐,你可以用位数据类型bit代替
日期类型
mysql里面的日期类型都是精确到秒的,如果你对精度有更高的要求可以用bigint代替,只要使用上相应的倍数就行。mysql的日期类型是datetime和timestamp
datetime:取值范围从1000到9999,默认以yyyy-MM-dd HH:mm:ss的形式格式化,以8个字节存储
timestamp:保存从1970年1月1日午夜以来的秒数,4个字节存储,依赖时区,会根据时区自动转换
综上:尽量使用timestamp
范式与反范式选择
两者混合使用,有时候冗余字段会大大加快查询效率,譬如冗余字段在覆盖索引上
alter table的操作
这个操作对大表操作很慢,而且会锁表,因为(大部分情况下)这会先建立一个新结构的空表然后把大表的记录拷贝到新的表,然后把旧的表删除,如果这张表很大超过内存,就需要大量的磁盘io读写操作,很慢。
如何解决:
方法一:先建立一张新结构空表,然后异步任务去读取旧表的记录插入就行,最后再分别改名。这种避免了锁表的长时间无法查询
方法二:建立一张新结构的空表,然后后台使用命令覆盖.frm的内容到旧表的.frm内容
还有就是alter table alter column相对与modify column和change column 没有重建表。速度会很快