schema与数据类型优化

数据类型

实际细则

范式与反范式

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

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

批注 2020-07-30 084057批注 2020-07-30 084112

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

主键

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

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

字符集

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

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

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

数据冗余

汇总表

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

物化视图

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

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

计数器表

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

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

适当拆分

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

Schema设计中的陷阱

加快ALTER TABLE

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

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

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