schema与数据类型优化

数据类型

• 更小的通常更好（保证范围够用的情况下）
• 简单就好
  • 整型比字符串操作代价更低
• 尽量避免使用null
  • 对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂

实际细则

• 字符串
  • 按照查询速度：char>varchar>text
  • varchar根据实际内容长度保存数据
  • char固定长度的字符串
    • 最大长度：255
    • 会自动删除末尾的空格
• BLOB与TEXT
  • 两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。
• 日期时间
  • datetime
    • 与时区无关，数据库底层时区配置，对datetime无效
    • 可保存到毫秒
  • timestamp
    • 时间范围：1970-01-01到2038-01-19
  • date
    • date类型用于保存1000-01-01到9999-12-31之间的日期
• 使用枚举替代字符串
• 特殊数据类型
  • IP使用整数表示

范式与反范式

范式化的更新通常比反范式要快 好的范式可以减少冗余 通常需要进行关联

而反范式所有的数据都在同一张表中，可以避免关联 可以设计有效的索引 表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失

反范式化前：用户表 |用户ID|姓名|电话|地址|邮编订单表 |订单ID|用户ID|下单时间|支付类型|订单状态订单商品表 |订单ID|商品ID|商品价格|商品数量商品表 |商品ID|名称|描述|过期时间SELECT b.用户名，b.电话，b.地址，a.订单ID        ,SUM(c.商品价价*c.商品数量)as订单价格FROM`订单表`aJOIN`用户表` b ON a.用户ID=b.用户DJOIN`订单商品表` c ON c.订单ID=b.订单IDGROUP BY b.用户名, b.电话, b.地址, a.订单ID反范式化后：用户表 |用户ID|姓名|电话|地址|邮编订单表 |订单ID|用户ID|下单时间|订单状态|订单价格|支付类型|用户名|电话|地址订单商品表 |订单ID|商品ID|商品价格|商品数量商品表 |商品ID|名称|描述|过期时间SELECT a.用户名,a.电话,a.地址, a.订单ID, a.订单价格 FROM`订单表` a

在实际中，一般都是两种混用，一种很常见的情况就是缓存。

主键

• 代理主键：与业务无关的，无意义的数字序列
  • 不与业务耦合，因此更容易维护
  • 通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本
• 自然主键：事物属性中的自然唯一标识

对于主键的选择，最好的选择是使用数字。特别是，要注意外键定义的两个列数据类型最好一致，在InnoDB中，如果不一致，创建表将失败。

使用字符串作为主键通常比数字类型慢，且对于诸如MD5,unid这些随机的字符串，将会导致INSERT及SELECT语句变得很慢。这是因为随机分布的数据导致局部性原理失效，每次写入的位置不可预测，逻辑相邻的数据物理不相邻。

字符集

纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间

如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型

MySQL的数据类型可以精确到字段，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量

数据冗余

汇总表

某些诸如实时统计的操作很耗费资源，那么就可以定期进行计算，放到一张缓存表来提高性能。

物化视图

被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段 可以将这些字段独立出一张表

冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新

计数器表

为了避免统计各种计数器值的开销，通过维护一个cnt字段来实现高性能。

这种方式会使写性能更差，但会提升读的性能。

适当拆分

对于一些如TEXT 大VARCHAR 数据时 可以将这些大数据字段拆分到另一个表 降低IO压力

Schema设计中的陷阱

• 使用了太多的列：转换代价是非常高的
• 使用太多的关联会影响性能
• 不必害怕使用NULL

加快ALTER TABLE

大部分ALTER TABLE 语句都会使MySQL停止服务。

为了避免ALTER TABLE过于缓慢的问题，有两种方法：

1. 在另外一台服务器上进行操作，完了之后把流量切换过去
2. 创建一张新表，对新表重命名 再把旧表删掉