`
student_lp
  • 浏览: 437996 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论
阅读更多

    良好的逻辑设计和物理设计时高性能的基石,应该根据系统将要执行的查询语句来设计schema,这往往需要权衡各种因素。例如:发范式的设计可以加速某些类型的查询,但同时可能使另一些类型的查询变慢。比如添加计数表和汇总表是一种很好的查询优化查询的方式,但这些表的维护成本可能会很高。

一、数据类型的选择优化

    mysql支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。下面是选取类型的几个指导原则:

  • 更小的通常更好:一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快,因为它占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少。
  • 简单就好:简单数据类型的操作通常需要更少的CPU周期。例如,整型比字符串代价更低,因为字符集和校队规则使字符比较更复杂。
  • 尽量避免NULL:很多表都包含可以null的列,即使应用程序并不需要保存null也是如此,这是因为可为null是列的默认属性。通常情况下最好指定列为not null,这是因为查询中包含可为null的列,对mysql来说更难优化,null的列使得索引、查询统计和值比较都更为复杂。

    类型的选择过程是:①确定合适的大类型----数字、字符串、时间等;②选择具体类型,很多mysql的数据类型可以存储相同类型的数据,只是存储的长度和范围不一样、允许的精度不同,或者需要的物理空间不同。

    mysql基本数据类型①整数类型----tinyint(8bit)、smallint(16bit)、mediumint(24bit)、int(32bit)、bigint(64bit);②实数类型----float(32bit)、double(64bit)他两个是不精确的浮点类型,decimal精确的小数,例如,decimal(18,9)小数点两边各存储9个数字,一共使用9个字节,小数点前的数字用4个字节,小数点后的数字用4个字节,小数点本身用1个字节(最多65个数字);③字符串类型----varchar和char。varchar需要1或2个额外字节记录字符串的长度,如果最大长度小于或等于255字节,则使用1个字节表示,否则使用2个字节。这两种类型占用的字节类型与字符编码有关;④大字段存储类型----blob和text类型,分别采用二进制和字符方式存储。⑤枚举类型,看似定义的是字符串,实际存储的是数字。⑥日期和时间类型----year、date、datetime(8字节)、timestamp(4字节)。

二、MySQL schema设计中的缺陷

    虽然有一些普遍的好或坏的设计原则,但也有一些问题是由MySQL的实现机制导致的,这意味着有可能犯一些只在MySQL下发生的特定错误,如下:

  • 太多的列:MySQL的存储引擎API工作时需要在服务器层和存储引擎之间通过缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价都很高的。MyISAM的定长行结构实际上与服务器层的行结构正好匹配,所以不需要转换。然而,MyISAM的变长行结构和InnoDB的行结构则总是需要转换。转换的代价依赖于列的数量。当CPU占用非常高时,发现客户使用了非常宽的表,然而只有一小部分列会实际用到,这时转换的代价就非常高。
  • 太多的关联:所谓“实体-属性-值”设计模式是一个常见的糟糕设计模式,尤其是在MySQL下不能靠谱的工作。MySQL限制了每个关联操作最多只能有61张表,但是eav数据库需要许多自关联,事实上在许多关联少于61张表的情况下,解析和优化查询的代价也会成为MySQL的问题。如果希望查询执行的快速并且并发性好,单个查询最好在12个表以内做关联。
  • 全能的枚举:注意防止过度使用枚举(ENUM)。例如:create table...(country enum('','1','2',...,'31'),这种模式的schema设计非常凌乱。这里该用整数作为外键关联到字典表或者查找表来查找具体值。
  • 变相的枚举:枚举(ENUM)允许在列中存储一组定义值中的单个值,集合(set)列则允许在列中存储一组定义值中的一个或多个值。有时候这可能比较容易导致混乱。例如:create table...(is_default set('Y','N') not null default 'N'。如果这里真和假两种情况不会同时出现,那么毫无疑问应该使用枚举代替集合列。
  • 非此发明的NULL:之前讲过避免NULL,建议尽可能考虑替代方案。但是遵守这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULL。例如用-1代表一个未知的整数,这可能导致代码复杂很多,并容易引入bug。处理NULL确实不容易,但有时候会比它的替代方案更好。

三、数据库范式

1、第一范式1NF

   实体所有属性的值都是单一的,这样的实体称为符合第一范式。对实体应用1NF,必须要验证实体的每个属性对应于实体的实例都有单一的值。如果任何属性有重复的值,他就不符合1NF,就可能需要定义一个新的实体,新的实体由重复的属性构成,新实体与原实体之间为一对多关系。

    映射到数据库,则数据库表中的所有字段值都是不可分解的原子值, 这样的数据库表满足第一范式。对于数据库表的设计应用1NF,指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值。

   如学生信息表,不能将学生信息都放在一列中显示,也不能将其中的两列或多列在一列中显示。简而言之,第一范式就是无重复的列。

学号   姓名 年龄 家庭地址
001 小文 22 温州市DD县DD镇
002 小红 21 温州市AA县AA镇

    本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分,那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储,这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式,如下表所示:


2、第二范式2NF

    如果实体符合第一范式,并且其所有非标识属性都完全依赖于实体的唯一标识符,则称实体符合第二范式。如果任何属性不完全依赖于实体的唯一标识符,那么这个属性必定是放错了位置,必须去除。(唯一标识符必须满足:①在实体的所有实例中是唯一的;②在实体的每个实例的整个生命周期中都有非NULL值;③在实例的整个生命周期中,他的值不会改变)

    对应于数据库,就是需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。

    比如要设计一个订单信息表,因为订单中可能会有多种商品,所以要将订单编号和商品编号作为数据库表的联合主键,如下表所示:


    这样就产生一个问题:这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关,而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。而如果把这个订单信息表进行拆分,把商品信息分离到另一个表中,把订单项目表也分离到另一个表中,就非常完美了。如下所示:


3、第三范式3NF

    如果实体符合2NF并且其所有标识属性均不依赖于其他任何非标识属性,则称这个实体符合第三范式。非标识属性是不作为实体标识符的一部分的任何属性。对于依赖于其他非标识属性的属性,可以将依赖属性和他所依赖的属性都移到新实体中从而实现标准化。

   对应于数据库,数据表中的每一列数据都和主键直接相关,而不能间接相关。

   比如在设计一个订单数据表的时候,可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息(比如姓名、所属公司等)的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。


四、范式与反范式

1、范式的优点和缺点

    当为性能问题而寻求帮助时,经常会被建议对schema进行范式化设计,尤其是写密集的场景。这通常是一个好建议。范式化通常能带来的好处:

  • 范式化的更新操作通常比反范式化更快;
  • 当数据较好的范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据。
  • 范式化的表通常更小,可以更好地放在内存里,所以执行操作会更快。
  • 很少有多余的数据意味着检索列表数据更少需要distinct或者group by语句。

   范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联,也许更多。但不但代价恩贵,也可能使一些索引策略无效。

2、反范式的优点和缺点

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

3、混用范式化和反范式化

   完全的范式化和完全的反范式化,在真实的世界中很少会这么极端的使用。在实际应用中经常需要混用,可能使用部分范式化的schema、缓存表,以及其他技巧。

    最常见的反范式化数据的方法是复制或者缓存,在不同的表中存储相同的特定列。另一个从父表冗余一些数据到字表的理由是排序的需要。

五、缓存表和汇总表

    有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而,有时也需要创建一张完全独立的汇总表或缓存表。如果能容许少量的脏数据,这是非常好的方法,但是有时确实没有选择的余地。

    通常“缓存表”表示存储那些可以比较简单的从schema其他表获取(但是每次获取的速度比较慢)数据的表(例如:逻辑上冗余的数据)。它对优化搜索和检索查询语句很有效。这些查询语句经常需要特殊的表和索引结构,跟普通OLTP操作用的表有些区别。

    通常“汇总表”时,则保存的是使用group by语句聚合数据的表(例如:数据不是逻辑上冗余的)。建立汇总表的最关键原因是,实时计算统计值是很昂贵的操作,因为要么需要扫描表中的大部分数据,要么查询语句只能在某些特定的索引上才能有效运行,而这类特定索引一般会对update操作有影响,所以一般不希望创建这样的索引。

    在使用缓存表和汇总表时,必须决定是实时维护数据还是定期重建。那个更好依赖于应用程序,但是定期重建并不只是节省资源,也可以保持表不会有很多碎片,以及有完全顺序组织的索引。当重建汇总表和缓存表时,通常需要保证数据在操作时依然可用。这就需要通过使用“影子表”来实现,“影子表”指的是一张在真是表“背后”创建的表。当完成了建表操作后,可以通过一个原子的重命名操作切换影子表和原表。

六、汇总

    良好的schema设计原则是普遍适用的,但MySQL有他自己的实现细节需要注意。概括起来说,尽可能保持任何东西小而简单总是好的。MySQL喜欢简单,需要使用数据库的人应该也同样会喜欢简单的原则:

  • 尽量避免过度设计,例如会导致及其复杂查询的schema设计,或者有很多列的表设计。
  • 使用小而简单的合适数据类型,除非真实数据模型中确切的需要,否则应该尽可能避免使用NULL值。
  • 尽量使用相同的数据类型存储相似或相关的值,尤其是在要在关联条件中使用的列。
  • 注意可变长字符串,其在临时表和排序时可能导致悲观的按最大长度分配内存。
  • 尽量使用整形定义标识符。
  • 避免使用MySQL已经遗弃的特性,例如指定浮点数的精度,或者整数的显示宽度。
  • 小心使用ENUM和set。虽然他们用起来很方便,但是不要滥用,否则有时候会变成陷阱。最好避免使用set。
  • 范式是好的,但是反范式有时也是必须要的,并且能带来好处。
  • 大小: 9.2 KB
  • 大小: 6.7 KB
  • 大小: 10.4 KB
  • 大小: 9.2 KB
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics