高性能的Mysql读书笔记系列之四(Schema与数据类型优化)

前言:

良好的逻辑设计和物理设计是高性能的基石,应该根据系统将要执行的查询语句来设计schema,这往往需要权衡各种因素。例如,反范式的设计可以加快某些类型的查询,但同时可能使另一些类型的查询变慢。

所以在进行库表设计的时候,需要根据具体的情况在范式和非范式之间进行取舍。

正文:

一、如何选择优化的数据类型

1.更小的通常更好

一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为它们占用更少的磁盘、内存和cpu缓存,并且处理时需要的cpu周期更少。

但是要确保没有低估需要存储的值的范围,因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的,就选择你认为不会超过范围的最小类型。(如果系统不是很忙或者存储的数据量不多,或者是在可以轻易修改设计的早期阶段,那之后修改数据类型也比较容易)。

2.尽量避免null

如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理。当可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM里甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。

3.整数类型指定长度意义不大

MySQL可以为整数类型指定宽度,例如INT(11),对大多数应用这是没有意义的:它不会限制值的合法范围,只是规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,INT(1)和INT(20)是相同的。

4.实数类型建议只在对小数精确计算使用(Decimal)

浮点类型在存储同样范围的值时,通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节,相比FLOAT有更高的精度和更大的范围。和整数类型一样,能选择的只是存储类型;MySQL使用DOUBLE作为内部浮点计算的类型。


因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但在数据量比较大的时候,可以考虑使用BIGINT代替DECIMAL,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分,则可以把所有金额乘以一百万,然后将结果存储在BIGINT里,这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

5.字符串类型选择

  •  char长度固定, 即每条数据占用等长字节空间;适合用在身份证号码、手机号码等定。
  •  varchar可变长度,可以设置最大长度;适合用在长度可变的属性。
  •  text不设置长度, 当不知道属性的最大长度时,适合用text。

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

不推荐在mysql中设置某一字段类型为enum,因为枚举的作用主要是为了限定值得取值范围,比如性别(男,女,未知),这个可以在入参时,从前端和参数校验来做。

7.日期和时间类型

  • 两者的存储方式不一样,TIMESTAMP涉及时区的转化问题,DATETIME是原样输入和输出
  • 两者所能存储的时间范围不一样,DATETIME的范围比TIMESTAMP的范围更大些

建议使用DATETIME类型来存储时间 

二、MySQL schema设计中的陷阱

太多的列,会使cpu的占用非常高,从而降低查询效率。

太多的关联,一个粗略的经验法则,如果希望查询执行得快速且并发性好,单个查询最好在12张表以内做关联,当然随着mysql的版本不断优化,可能这个限制的阈值会变高,但是还是尽量关联少量的表。

三、范式和反范式

范式的优点和缺点:

  • 范式化的更新操作通常比反范式化要快。
  • 当数据较好地范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据。
  • 范式化的表通常更小,可以更好地放在内存里,所以执行操作会更快。
  • 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。
  • 范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联,也许更多。这不但代价昂贵,也可能使一些索引策略无效。

 反范式的优点和缺点:

  •  反范式化的schema因为所有数据都在一张表中,可以很好地避免关联。
  • 如果不需要关联表,则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多,因为这样避免了随机I/O(14)。
  • 单独的表也能使用更有效的索引策略。

范式化和反范式化的schema各有优劣,怎么选择最佳的设计?
事实是,完全的范式化和完全的反范式化schema都是实验室里才有的东西:在真实世界中很少会这么极端地使用。在实际应用中经常需要混用,可能使用部分范式化的schema、缓存表,以及其他技巧。 

总结:

今日分享一句土味情话:

虽然今天天气很冷,但你让我心里暖。

我是阿达,一名喜欢分享知识的程序员,时不时的也会荒腔走板的聊一聊电影、电视剧、音乐、漫画,这里已经有11359位小伙伴在等你们啦,感兴趣的就赶紧来点击关注我把,哪里有不明白或有不同观点的地方欢迎留言!

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页