08月12, 2016

MYSQL优化系列(一)——数据库设计范式

MYSQL优化系列前言

如同经济学中把课程分为微观经济学和宏观经济学,我个人认为MYSQL的优化也是分为微观方向和宏观方向的。

  • 微观优化 包含如何建表,如何创建字段,字段类型的选择以及表引擎的选择,SQL语句的优化,当然还有重中之重的索引创建和优化

  • 宏观优化 宏观优化就是在架构层进行优化,包括分表分库,主从,读写分离,集群,负载均衡等等。

显而易见,微观优化是宏观优化的基础。如果没有做好微观优化单纯用机器来堆性能,堆的越多问题越多!同时作为程序员来说,我想微观优化才是我们通常需要面对的问题(宏观优化通常有专门的运维或者DBA进行优化)。因此下面这个mysql优化系列将从微观的角度入手,根据我平时工作中的sql优化流程进行写作。

MYSQL优化系列书写顺序计划如下

  1. 数据库设计“范式”。
  2. 如何选择字段类型。
  3. 如何选择数据表引擎。
  4. 索引的优化。(这可能要花费大量的篇幅)
  5. sql语句的调优,explain你的sql语句。

话不多说,先说说数据库设计范式吧。

数据库设计范式

数据库范式分为1NF,2NF,3NF,BCNF,4NF,5NF。一般在我们设计数据库的时候,最多考虑到BCNF就足够了。同时符合高一级范式的设计,必定符合低一级范式,例如符合2NF的关系模式,必定符合1NF。

关于数据库设计范式网上的概念解析有很多,其中知乎这个高票答案我个人认为是解释最好的,传送门,不了解概念的可以自己去看一下。

我要补充的关于数据库范式的理解就是,我们要知道我们为什么要遵循数据库设计范式。无非 就是避免插入异常,更新异常,删除异常,减少数据冗余。其实三范式更像一种数据库设计的校验法则,你可以根据三范式来观察你的表是否可能存在以上的不合理情况。在设计的时候,校验最简便的办法就是我们观察一张表,看看他是不是已经拆分到最细了,有没有属性能够单独拿出来再建一张表,所有的依赖是不是都用ID关联的(注意我这里用的是ID不是外键,后面会说原因)。如果以上回答都为是的话,基本这张表就没什么问题了。

反范式化

反范式化就是为了性能和读取速率适当考虑违反三范式的要求,而允许存在少量的数据冗余,一言以蔽之,反范式化就是用空间换取时间

曾经在知乎上看过一个人十分自豪的说,一个DBA是否有足够的设计能力,就看他有多大的能力做反范式设计就可以了。一眼看去,深以为然。然而大神一点而过,也不详细展开讲,令人莫测高深,却也不得其门。其实这就是一个经验问题,踩过的坑多了,自然有避坑的技巧。

不过这里却有一个窍门在,一位大神在阿里巴巴的内部数据库设计规范中规定过:如果join超过三个表,那么就要考虑重新设计表了。大神的结论从何而来我不知道,但不妨碍可以把它当做一个结论记下来,也就是说,当你拿不准数据要不要冗余的时候,就考虑他的使用场景,三表以内的join通常是不需要冗余的。

另外介绍一种常见可以冗余的场景,就是订单表。为什么订单表可以冗余呢,因为通常冗余订单表里面的数据是不会造成更新删除异常的。

我举个例子,假如我有两张表。

商品表:

Id ProductName ProductPrice
1 渔网 150

订单表:

Id OrderPrice CreateTime ProductName ProductPrice
1 渔网 2016-08-12 00:00:00 渔网 150

如上图,我们在订单表冗余商品名称,商品价格,是完全没有问题的。因为商品表中无论商品名称修改,还是商品价格修改,我都不用修改订单中的商品信息。订单表本身记录的就是下单时刻的历史信息,是不需要随着更新的。也就没有了更新和删除的异常。

其他需要注意的地方

  • 表和字段的命名要有规范,做到让人一目了然。
  • 尽量由一个人设计数据库,以便统一标准。
  • 尽量不要用外键,外键会大大的影响性能,如果需要保持数据一致性可以用程序来控制。

本文链接:http://www.qiana.info/post/mysql_optimize_1.html

-- EOF --

Comments