一眼mysql之二:schema和数据类型优化

schema和数据类型优化

选择优化的数据类型

• 小的更好:使用可以正确存储数据的最小数据类型
• 越简单越好:简单数据类型的操作需要更是的cpu周期
• 避免null:除非真的需要存储null,不然设置为not null,因为查询时,可为null的列使得索引和值比较,索引统计更复杂(timestamp只使用datetime一半的存储空间,但是允许的时间范围比较小)

整数类型

• 整数类型就是数字,分为整数和实数.

• 整数,可用TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT,分别使用8,16,24,32,64位存储空间,值的范围为-2^(位数-1) 到2^(位数-1)
  可选属性为UNSIGNED,表示只存正数,可以使正数的上限提高一倍(节省存储空间)
  为整数类型指定宽度,但是大部分是没有意义的,不会限制值的合法范围(对于存储和计算来说)

• 实数类型
  实数类型就是带小数的数字,可以选择精确类型和不确定类型,但是存储是存储,计算是计算,存储是DECIMAL,但是计算的时候是用DOUBLE类型的,在存储方面,浮点类型占据的空间比DECIMAL少,FLOAT使用4个字节存储,DOUBLE用8个字节,(所以在不考虑特别大误差的情况下可以使用浮点类型),

• 如果只在对小数进行精确计算时才使用DECIMAL,如果是数据量比较大的时候,可以考虑使用BIGINT代替,将存储数据根据小数的位数乘以相应的倍数即可,避免浮点计算不精确和DECIMAL精确计算代价高的问题

字符串类型

• VARCHAR和CHAR

  • VARCHAR:存储可变长字符串,比定长类型更节省空间,如果使用ROW_FORMAT=FIXED,每一行都会使用定长存储,浪费空间,但是行是变长的,做update的时候就需要做额外的工作,如果刚好页内没空间了innodb会分裂页,myisam会将行拆分为不同的片段存储
  • 适用:字符串列的最大长度比平均长度大很多,列更新少,使用了utf-8这样复杂的字符集,每个字符都使用了不同的字节数进行存储(还是需要指定合适的长度,因为mysql会分配固定大小的内存来保存内部值)
  • CHAR:定长的,在开始的时候就会分配固定的空间,存储是,会删除所有的末尾空格,然后根据需要采用空格进行填充以方便比较
  • 适用:存储很短的字符串,或者所有值都接近一个长度

• BLOB和TEXT

  • BLOB:二进制存储,没有排序规则
  • TEXT:字符集存储
  • 总结: mysql把每个BLOB和TEXT当做一个独立的对象处理,当值太大的时候,innodb会使用专门的外部存储区域进行存储,然后用1~4个字节存储地址的指针,外部存储区存储实际的值,mysql无法对BLOB和TEXT列进行排序和全部长度索引,只能排序前面一部分字符,可以减少max_sort_length的配置或者是用ORDER BY SUSRING(cloumn,lenght)

• 使用枚举(ENUM)代替字符串类型

  • 缺点:字符串列表是固定的,添加或删除字符串必须使用ALTER TABLE,所以基本不改变的字符串使用枚举会好一些(或者只在列表末尾添加元素)
  • 优点:吧列转换为ENUM后,关联变得很快,如果关联的是VARCHAR则会比较慢,使用ENUM可以使表存储变小

日期和时间类型

• DATETIME:存的是从1001到9999年,和时区取关,八个字节的存储空间
• TIMESTAMP:存的是从1970/1/1到现在的秒数,只使用四个字节空间,有FROM_UNIXTIME()函数可以转为日期,UNIX_TIMESTAMP()函数转为时间戳,但是依赖时区,
  尽量使用TIMESTAMP

2 设计数据库的原则

• 2.1:避免太多的列:mysql存储引擎api工作需要在服务器层和存储引擎之间通过行缓存格式拷贝数据,然后再服务器层将缓存内容解码成各个列,这个转码操作代价非常高,所以避免所有列都放在一起
• 2.2:避免太多的关联:单个查询最好在12个表里面做关联
• 2.3:防止过度使用枚举
• 2.4:过度的对null说不:有的时候还是需要null值的

3:范式和反范式

• 范式的优点:

  • 更新操作比反范式的快
  • 当数据较好的范式化的时候,只有很少或者没有重复数据,所以只需要修改更少的数据
  • 范式的表通常比较小,执行操作更快
  • 很少多余的数据意味着更少使用distinct或group by语句

• 缺点:需要关联,可能复杂的语句关联的表越来越多

• 反范式的优点:避免关联,单独的表能使用更有效的索引策略

• 结论:混用范式化和反范式化

4 加快ALTER TABLE操作

• 对于大表来讲,备份就是需要先创建一个新表,然后查询,插入,删除旧表,但是有两种方案可以加快速度

  • 在一台不提供服务的机器上执行ALTER TABLE操作,然后进行库的切换
  • 用要求的表结构建一张表,通过重命名和删表操作交换两张表

• 如果只是修改或删除一个列的默认值,不需要锁表,而是对存储默认值的.frm文件中可以ALTER TABLE XXX.film ALTER COLUMN column_name SET DEFAULT X;