MySQL 社区规范 | 数据库篇

这周公司开发工作比较悠闲，工作几乎压在设计上游，于是整理了下公司开发的文档，包括项目架构、服务器运维、规范、 api 对接、基本依赖信息等。如下是包含其中的 MySQL 开发规范，根据社区很多的博文参考以及结合自身小团队开发情况总结。

参考 A_aliane 、 雪松 等前辈的总结，非常感谢！

数据库设计是应用程序设计的基础 其性能直接影响应用程序的性能 数据库性能包括存储空间需求量的大小和查询响应时间的长短两个方面 为了优化数据库性能 需要对数据库中的表进行规范化 规范化的范式可分为第一范式 第二范式 第三范式 BCNF范式 第四范式和第五范式 一般来说 逻辑数据库设计会满足规范化的前 级标准 但由于满足第三范式的表结构容易维护且基本满足实际应用的要求 因此 实际应用中一般都按照第三范式的标准进行规范化 但是 规范化也有缺点 由于将一个表拆分成为多个表 在查询时需要多表连接 降低了查询速度

由于规范化有可能导致查询速度慢的缺点 考虑到一些应用需要较快的响应速度 在设计表时应同时考虑对某些表进行反规范化 反规范化可以采用以下几种方法

分割表

分割表包括水平分割和垂直分割

水平分割是按照行将一个表分割为多个表 这可以提高每个表的查询速度 但查询 更新时要选择不同的表 统计时要汇总多个表 因此应用程序会更复杂

垂直分割是对于一个列很多的表 若某些列的访问频率远远高于其它列 就可以将主键和这些列作为一个表 将主键和其它列作为另外一个表 通过减少列的宽度 增加了每个数据页的行数 一次I/O就可以扫描更多的行 从而提高了访问每一个表的速度 但是由于造成了多表连接 所以应该在同时查询或更新不同分割表中的列的情况比较少的情况下使用

保留冗余列

当两个或多个表在查询中经常需要连接时 可以在其中一个表上增加若干冗余的列 以避免表之间的连接过于频繁 由于对冗余列的更新 *** 作必须对多个表同步进行 所以一般在冗余列的数据不经常变动的情况下使用

增加派生列

派生列是由表中的其它多个列计算所得 增加派生列可以减少统计运算 在数据汇总时可以大大缩短运算时间

二　应用程序性能的优化

应用程序的优化通常可分为两个方面 源代码和SQL语句 由于涉及到对程序逻辑的改变 源代码的优化在时间成本和风险上代价很高 而对数据库系统性能的提升收效有限 因此应用程序的优化应着重在SQL语句的优化 对于海量数据 劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍 可见对于一个系统不是简单地能实现其功能就行 而是要写出高质量的SQL语句 提高系统的可用性

下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍 在这些where子句中 即使某些列存在索引 但是由于编写了劣质的SQL 系统在运行该SQL语句时也不能使用该索引 而同样使用全表扫描 这就造成了响应速度的极大降低

IS NULL 与 IS NOT NULL

不能用null作索引 任何包含null值的列都将不会被包含在索引中 即使索引有多列的情况下 只要这些列中有一列含有null 该列就会从索引中排除 也就是说如果某列存在空值 即使对该列建索引也不会提高性能

任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的

联接列

对于有联接的列 即使最后的联接值为一个静态值 优化器不会使用索引的 例如 假定有一个职工表(employee) 对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME) 现在要查询一个叫乔治·布什(Gee Bush)的职工 下面是一个采用联接查询的SQL语句

select from employee where first_name|| ||last_name = Gee Bush ;

上面这条语句完全可以查询出是否有Gee Bush这个员工 但是这里需要注意 系统优化器对基于last_name创建的索引没有使用

当采用下面这种SQL语句的编写 Oracle系统就可以采用基于last_name创建的索引

Select From employee where first_name = Gee and last_name = Bush ;

遇到下面这种情况又如何处理呢如果一个变量(name)中存放著Gee Bush这个员工的姓名 对于这种情况我们又如何避免全程遍历使用索引呢可以使用一个函数 将变量name中的姓和名分开就可以了 但是有一点需要注意 这个函数是不能作用在索引列上 下面是SQL查询脚本

select

from employee where first_name = SUBSTR

( &&name INSTR( &&name ) )

and last_name = SUBSTR( &&name INSTR

( &&name )+ ) ;

带通配符(%)的like语句

同样以上面的例子来看这种情况 目前的需求是这样的 要求在职工表中查询名字中包含Bush的人 可以采用如下的查询SQL语句

select from employee where last_name like %Bush% ;

这里由于通配符(%)在搜寻词首出现 所以Oracle系统不使用last_name的索引 在很多情况下可能无法避免这种情况 但是一定要心中有底 通配符如此使用会降低查询速度 然而当通配符出现在字符串其他位置时 优化器就能利用索引 例如 在下面的查询中索引得到了使用

select from employee where last_name like c% ;

NOT

我们在查询时经常在where子句使用一些逻辑表达式 如大于 小于 等于以及不等于等等 也可以使用and(与) or(或)以及not(非) NOT可用来对任何逻辑运算符号取反 下面是一个NOT子句的例子

where not (status = VALID )

如果要使用NOT 则应在取反的短语前面加上括号 并在短语前面加上NOT运算符 NOT运算符包含在另外一个逻辑运算符中 这就是不等于(<>)运算符 换句话说 即使不在查询where子句中显式地加入NOT词 NOT仍在运算符中 见下例

where status <> INVALID ;

再看下面这个例子

select from employee where　salary<> ;

对这个查询 可以改写为不使用NOT的语句

select from employee where　salary< or salary> ; lishixinzhi/Article/program/SQL/201311/16352

　 数据库设计简述 数据库设计是把现实世界的商业模型与需求转换成数据库的模型的过程 它是建立数据库应用系统的核心问题 设计的关键是如何使设计的数据库能合理地存储用户的数据 方便用户进行数据处理 数据库设计完全是人的问题 而不是数据库管理系统的问题 系统不管设计是好是坏 照样运行 数据库设计应当由数据库管理员和系统分析员一起和用户一道工作 了解各个用户的要求 共同为整个数据库做出恰当的 完整的设计 数据库及其应用的性能和调优都是建立在良好的数据库设计的基础上 数据库的数据是一切 *** 作的基础 如果数据库设计不好 则其它一切调优方法提高数据库性能的效果都是有限的 数据的规范化 范式概述规范化理论是研究如何将一个不好的关系模式转化为好的关系模式的理论 规范化理论是围绕范式而建立的 规范化理论认为 一个关系数据库中所有的关系 都应满足一定的规范(约束条件) 规范化理论把关系应满足的规范要求分为几级 满足最低要求的一级叫做第一范式( NF) 在第一范式的基础上提出了第二范式( NF) 在第二范式的基础上又提出了第三范式( NF) 以后又提出了BCNF范式 NF NF 范式的等级越高 应满足的约束集条件也越严格 规范的每一级别都依赖于它的前一级别 例如若一个关系模式满足 NF 则一定满足 NF 下面我们只介绍 NF NF NF范式 NF NF是关系模型的最低要求 它的规则是 每一列必须是原子的 不能分成多个子列 每一行和列的位置只能有一个值 不能具有多值列 例 如果要求一个学生一行 一个学生可选多门课 则下面的 学生 表就不满足 NF student(s－no s－name class－no)其中 s－no为学号 s－name为学生姓名 class－no为课程号 因为一个学生可选多门课 所以列class－no有多个值 所以空不符合 NF 规范化就是把它分成如下两个表 学生 表和 选课 表 则这两个表就都满足 NF了 student(s－no s－name)stu－class(s－no class－no) NF对于满足 NF的表 除满足 NF外 非主码的列必须依赖于所有的主码 而不是组合主码的一部分 如果满足 NF的表的主码只有一列 则它自动满足 NF 例 下面的 选课 表 不符合 NF stu－class(s－no class－no class－name)其中 class－name为课程名称 因为词表的主码是 (s－no class－no) 非主码列class－name依赖于组合主码的一部分class－no 所以它不符合 NF 对该表规范化也是把它分解成两个表 选课 表和 课程 表 则它们就都满足 NF了 stu－class(s－no class－no)class(class－no class－name) NF NF的规则是除满足 NF外 任一非主码列不能依赖于其它非主码列 例 下面的 课程 表 不符合 NF class(class－no class－name teacher－no teacher－name)其中 teacher－no为任课教师号 teacher－name为任课教师姓名 因为非主码列teacher－name依赖于另一非主码列teacher－no 所以它不符合 NF 其解决办法也是把它分解成两个表 课程 表和 教师 表 则它们就都满足 NF了 class(class－no class－name teacher－no)teacher(teacher－no teacher－name) 小结当一个表是规范的 则其非主码列依赖于主码列 从关系模型的角度来看 表满足 NF最符合标准 这样的设计容易维护 一个完全规范化的设计并不总能生成最优的性能 因此通常是先按照 NF设计 如果有性能问题 再通过反规范来解决 数据库中的数据规范化的优点是减少了数据冗余 节约了存储空间 相应逻辑和物理的I/O次数减少 同时加快了增 删 改的速度 但是对完全规范的数据库查询 通常需要更多的连接 *** 作 从而影响查询的速度 因此 有时为了提高某些查询或应用的性能而破坏规范规则 即反规范 数据的反规范 反规范的好处是否规范化的程度越高越好这要根据需要来决定 因为 分离 越深 产生的关系越多 关系过多 连接 *** 作越频繁 而连接 *** 作是最费时间的 特别对以查询为主的数据库应用来说 频繁的连接会影响查询速度 所以 关系有时故意保留成非规范化的 或者规范化以后又反规范了 这样做通常是为了改进性能 例如帐户系统中的 帐户 表B－TB 它的列busi－balance(企业帐户的总余额)就违反规范 其中的值可以通过下面的查询获得 select busi－code sum(acc－balance)from　 B－TB group by busi－code如果B－TB 中没有该列 若想获得busi－name(企业名称)和企业帐户的总余额 则需要做连接 *** 作 select busi－name sum(acc－balance)from B－TB B－TB where B－TB busi－code=B－TB busi－codegroup by busi－code如果经常做这种查询 则就有必要在B－TB 中加入列busi－balance 相应的代价则是必须在表B－TB 上创建增 删 改的触发器来维护B－TB 表上busi－balance列的值 类似的情况在决策支持系统中经常发生 反规范的好处是降低连接 *** 作的需求 降低外码和索引的数目 还可能减少表的数目 相应带来的问题是可能出现数据的完整性问题 加快查询速度 但会降低修改速度 因此决定做反规范时 一定要权衡利弊 仔细分析应用的数据存取需求和实际的性能特点 好的索引和其它方法经常能够解决性能问题 而不必采用反规范这种方法 常用的反规范技术在进行反规范 *** 作之前 要充分考虑数据的存取需求 常用表的大小 一些特殊的计算(例如合计) 数据的物理存储位置等 常用的反规范技术有增加冗余列 增加派生列 重新组表和分割表 增加冗余列增加冗余列是指在多个表中具有相同的列 它常用来在查询时避免连接 *** 作 例如前面例子中 如果经常检索一门课的任课教师姓名 则需要做class和teacher表的连接查询 select class－name teacher－namefrom　 class teacherwhere　class teacher－no=teacher teacher－no这样的话就可以在class表中增加一列teacher－name就不需要连接 *** 作了 增加冗余列可以在查询时避免连接 *** 作 但它需要更多的磁盘空间 同时增加表维护的工作量 增加派生列增加派生列指增加的列来自其它表中的数据 由它们计算生成 它的作用是在查询时减少连接 *** 作 避免使用集函数 例如前面所讲的账户系统中的表B－TB 的列busi－balance就是派生列 派生列也具有与冗余列同样的缺点 重新组表重新组表指如果许多用户需要查看两个表连接出来的结果数据 则把这两个表重新组成一个表来减少连接而提高性能 例如 用户经常需要同时查看课程号 课程名称 任课教师号 任课教师姓名 则可把表class(class－no class－name teacher－no)和表teacher(teacher－no teacher－name)合并成一个表class(class－no class－name teacher－no teacher－name) 这样可提高性能 但需要更多的磁盘空间 同时也损失了数据在概念上的独立性 分割表有时对表做分割可以提高性能 表分割有两种方式 水平分割 根据一列或多列数据的值把数据行放到两个独立的表中 水平分割通常在下面的情况下使用:A 表很大 分割后可以降低在查询时需要读的数据和索引的页数 同时也降低了索引的层数 提高查询速度 B 表中的数据本来就有独立性 例如表中分别记录各个地区的数据或不同时期的数据 特别是有些数据常用 而另外一些数据不常用 C 需要把数据存放到多个介质上 例如法规表law就可以分成两个表active－law和inactive－law activea－authors表中的内容是正生效的法规 是经常使用的 而inactive－law表则使已经作废的法规 不常被查询 水平分割会给应用增加复杂度 它通常在查询时需要多个表名 查询所有数据需要union *** 作 在许多数据库应用中 这种复杂性会超过它带来的优点 因为只要索引关键字不大 则在索引用于查询时 表中增加两到三倍数据量 查询时也就增加读一个索引层的磁盘次数 垂直分割 把主码和一些列放到一个表 然后把主码和另外的列放到另一个表中 如果一个表中某些列常用 而另外一些列不常用 则可以采用垂直分割 另外垂直分割可以使得数据行变小 一个数据页就能存放更多的数据 在查询时就会减少I/O次数 其缺点是需要管理冗余列 查询所有数据需要join *** 作 反规范技术需要维护数据的完整性 无论使用何种反规范技术 都需要一定的管理来维护数据的完整性 常用的方法是批处理维护 应用逻辑和触发器 批处理维护是指对复制列或派生列的修改积累一定的时间后 运行一批处理作业或存储过程对复制或派生列进行修改 这只能在对实时性要求不高的情况下使用 数据的完整性也可由应用逻辑来实现 这就要求必须在同一事务中对所有涉及的表进行增 删 改 *** 作 用应用逻辑来实现数据的完整性风险较大 因为同一逻辑必须在所有的应用中使用和维护 容易遗漏 特别是在需求变化时 不易于维护 另一种方式就是使用触发器 对数据的任何修改立即触发对复制列或派生列的相应修改 触发器是实时的 而且相应的处理逻辑只在一个地方出现 易于维护 一般来说 是解决这类问题的最好的办法 结束语 数据库的反规范设计可以提高查询性能 常用的反规范技术有增加冗余列 增加派生列 重新组表和分割表 但反规范技术需要维护数据的完整性 因此在做反规范时 一定要权衡利弊 仔细分析应用的数据存取需求和实际的性能特点 lishixinzhi/Article/program/Oracle/201311/16946

以上就是关于MySQL 社区规范 | 数据库篇全部的内容，包括:MySQL 社区规范 | 数据库篇、数据库规范化与优化问题讲解、数据库设计中的反规范技术探讨等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！