高性能的mysql初探之四

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 没有重建表。速度会很快