数据库设计优化

数据库的基本语法的使用，很多，我也没有去整理，所以在这里就不介绍那些基本的语法了。如果对这方面有问题，我在这里给大家分享一个方法：

 

 

 　　sql语句应该怎样去优化？在职业生涯中，有一次维护一个项目中的一个模块。之前做的那个同事离职了，交给我来维护。我记得好像是七八张表吧，各种join、order by、distinct、group by.......看得头大。这样的sql语句应该怎么优化呢？额......这怎么优化啊？建议从表的设计方面考虑吧。后来我也是这样做的，重新设计表，将表的数量减少，将数据更新到那写新表中去。即使不刷数据，流程中做下控制，时间久了，老表中的数据也都会更新到新表中去。

数据库设计步骤：

　　1、需求分析：大概明白需要存储什么

　　2、概要设计阶段：e-r图，表-属性-关系表，解决多方的沟通问题

　　3、详细设计，细致到表甚至字段

关系型数据库：面向对象的封装+关系

面向对象的思想来建表：

　　一对一：人----身份证；垂直分别的时候；相同主键/外键

　　一对多：订单----商品表；省-----市；公司-----员工；主外键表示

　　多对多：用户----菜单；学生----课程；女神----备胎；关系表/中间/映射表

三大范式：

　　第一范式：每一列保持原子性，不可分割

　　　　原子性：不可分割。

　　　　人----工作电话/家庭电话/移动电话，必须要拆分开，分三个列来表示

　　　　【错误的】核心业务表-----医院/医生/疾病/症状/药品/可是----不同类型有不同的信息----用xml放在一个字段====》这种方式能解决问题，但是违反了第一范式

　　第二范式：每一类都得跟主键相关，一张表应该描述一个对象

　　　　a  要有主键   b  信息跟主键相关

　　　　公司-----多用户，不能用一个用户表（里面写公司的信息），应该是公司一张表，用户一张表。

　　　　第二范式，一般不要违背。

　　第三范式：每一列都得跟主键直接相关，而不是间接相关

　　　　用户---公司id----公司名称（非直接相关）===》违背了第三范式，因为用户表中包含了公司名称。如果需要显示公司名称，用第三范式，查询会很慢，还要关联公司表，但是更麻烦，更新了公司表，还要用户更新表中的公司名称，这个可以用一个定时作业去解决。如果更新很少但是查询特别多，这个时候就可以去适当违背第三范式。

三大范式只是建议，就好像设计模式一样，其实都可能被违背，正确的设计会减少很多工作量。

基础内容：

　　命名风格：双驼峰命名；大写字母的模块缩写，不要下划线（避免映射），英文单词，首字母大写；拼音/汉字完全反对。

　　主键：数据库自动增加，int/bigint，sqlserver默认聚集索引，空间小，可以有业务意义（比如大于1000w的都是新用户）；多库环境，不同环境的数据库id冲突；订单连续，容易让同行推出你的销售量

　　guid：程序生成的，全球唯一，插入数据库，不会错误的join，无限的，方便导入；空间大，没有自身含义，没有聚集索引（创建时间来聚集）

　　外键：描述书关系，规范数据关系；外键做数据校验，级联删除（其实现在都是假删除）；有严格的数据关系的时候，使用外键；导入麻烦、增删改查的数据库多一步操作；更多的是，通过程序来完成，虚拟外键；

　　对外键要合理使用：尽量少的使用外键；大型互联网项目的瓶颈一般都在数据库，尽量少的让数据库做事；如果数据严谨性要求很高，可以用外键；其他的就不怎么用了，尤其是互联网项目。

数据库事务：

　　多条sql作为一个整体提交给数据库系统，要么全部执行，要么全部取消，是一个不可分割的逻辑单元；两个sql语句，一个下单成功，另外一个减少库存，事务保证一起成功或者一起失败。

　　最简单的事务，就是 update a set name="123",tag=123 where id=123

　　begin tran--rollback/commit；默认开启事务，就是跨域省略begin tran；显示开启事务，还有个开启全部事务。

acid

　　原子性：要么都成功，要么都失败

　　一致性：事务执行完，数据都是正确的

　　隔离性：两个事务同时操作一张表，b事务要么在a事务前完成，要么在a事务完成执行（锁表）

　　持久性：数据提交后，就固化下来

锁

　　多用户同时访问一个数据资源：

　　　　1、修改数据===》同时修改，一个改成张三，一个改成李四，总归有一个被覆盖

　　　　2、不可重复读

　　　　3、脏读/幻读===》要么读到的是更新前的，要么读到的是更新后的。正在更新的的数据，突然来查询了。。。。

　　所就是，保证访问同一资源时，有个先后顺序管理，处理并发问题。

　　乐观锁：

　　　　认为没有并发，读取数据---更新---保存，乐观锁就是没有锁。

　　　　更新的时候做一个判断：（乐观锁的方式）

　　　　　　1、时间戳：数据库增加一列timespan，long，每次查询拿出来，更新时+1，保存时，判断是否大于数据库的值，要保证任何操作都按照这个规范，所以也有个漏洞，比如其他渠道更新。

　　　　　　2、version，版本号

　　　　　　3、检查更新字段，一定是原始的数据上更新的

　　　　　　4、检查所有的字段

　　　　乐观锁的性能搞！！！！！！！

　　悲观锁：

　　　　认为任何时候都可能在多线程并发，读书节的时候，别人恰好在修改；基于数据库的锁的机制来完成的

　　　　共享锁，也叫s锁，读的锁：允许别的事务来读，但是不允许修改；读完就释放，锁定数据页；除非holdlock一直锁定

　　　　排它锁，也叫x锁，写的锁：准备写数据，不允许读也不允许写，比如select * from a update b，b表就不允许读。

　　　　更新所，也叫u锁：先查询再更新

　　　　行锁：where id = 2

　　　　表锁：where 1 = 1

　　怎么避免死锁呢？高并发的情况下，死锁是不可能避免的，只能尽量减少。

　　　　1、不用锁，就不会死锁，乐观锁也可以，因为乐观锁没有锁的概念

　　　　2、统一操作顺序，先a后b再c

　　　　3、最小单元锁，锁里面的操作尽量减少

　　　　4、避免事务中等待用户输入

　　　　5、减少数据库并发

　　　　6、分库/分表/分区

　　　　7、降低事务级别----谨慎使用

　　　　8、设置死锁时间----谨慎使用

　　高并发的西永，死锁其实是不可避免的，只能尽量减少

　　　　1、按照固有的顺序操作数据

　　　　2、事务尽量的简短；更不要再事务期间搞什么等待；降低并发；事务里面耗时短；锁的隔离级别

存储过程

　　存储过程procedure是一组为了完成特定功能的sql语句集合，经编译后存储在数据库中，用户用过指定存储过程的名称并给出参数来执行。

　　坏处：

　　　　存储过程不太好维护；逻辑分散了；不好管理；全部数据库操作

　　　　【少用】，大量复杂的逻辑、计算、数据传输，可以用，否则还是程序来完成

　　好处：

　　　　数据库编程（下订单---减少一个库存---写日志），迁移方便；预编译；减少数据的传输

触发器

　　触发器是一种特殊类型的存储过程，触发器主要是通过事件进行触发被自动调用执行的；尽量不要使用；事发前/事发后执行动作；数据库的自发动作，更新company的name，会自动更新别的表名字，这个是完整动作；触发器泛滥，嵌套式。有很多个触发器的时候，排查问题都不好排查

游标

　　游标实际上是一种能从多条数据记录的结果集中每次提取一条记录的机制；复杂的结果集，复杂的操作就可以用。但是我倒是从来没用到过游标，一般存储过程中会用到游标

视图

　　视图就是一个虚拟的数据表，该数据表中的数据记录是有一条查询语句的查询结果得到的

函数

　　不太推荐使用自定义函数，因为计算交给数据库，没法使用索引

字段类型

　　这里有一个博客，介绍的还是蛮详细的：

字段可空

　　数值类型的，不要可空，in查询  not in 查询，会没有结果，一般给个默认值；尽量非空，看情况（时间不适合默认值）

统计字段

　　创建时间、创建人、最后更新时间、最后更新人、state、isenable

软删除

　　假删除、逻辑删除，把状态改成删除，而不是物理删除。会造成一点空间浪费，但是可以保存数据备份。

 

数据库瓶颈，数据量的增加，并发的增加：

　　1、加强硬件，换商业化软件====》花钱解决

　　2、架构设计上来解决，让数据库最少的做事，放弃触发器、外键、存储过程、函数

数据库复杂均衡

 

 

 多要数据库完成一台的事，数据库要保证一致性的

负载均衡：

　　利用多台服务器的读写能力，但是数据同步和访问分配交给第三方软件

moebius：

　　读的压力酚酞到不同的服务器，写其实是多态服务器都得完成，对外只有一个ip，使用者是不知道细节的。

读写分离

　　二八原则：数据库中80%操作都是读，只有20%是写。

　　实现原理：就是把读和写的压力分开，降低io压力；一主多从，主库写从库读；数据同步的问题，从主库到从库（肯定是有延迟的，网速没问题，两三秒吧）

　　1、数据库里面列别的数据库？link到主要+定时的job，是不是就可以了？【效率低】

　　2、日志传送，sqlserver2005，备份----复制----复制，这种方式简单，但是有局限性（局域网，只能文件夹共享）

 

 

 　　3、镜像，snapshot：内存拍照；主库，对外提供服务；从库，通过快照回复，数据跟主课一直；监控转移，负责检查状况，有问题切到从库

　　4、数据复制（发布/订阅）

 

 

 　　主库---发布到服务器---从库；延迟小，配置方便，但是需要程序配合。发布订阅后，从数据库是可以水平卓展的，查询就不是大问题。业务量进一步增加，写库还是抗不住===》分库/分区/分表

分库/分表/分区（最考验设计和开发）

　　多台服务器

　　分库：

　　　　1、系统里面有订单/物流/仓储/论坛/博客/客服

　　　　　　垂直分库，按照业务拆分库，不同的库不同的服务器

　　　　2、订单增删改查特别大

　　　　　　水平分库，每个库结构一致，数据不一致（地域/时间/类别/随机算法）

　　分表　　

　　　　垂直分表：

　　　　　　1、文章表，10个常规字段，还有一个很长很长的内容字段

　　　　　　　　垂直分表，直接减小表的体积，提升增删改查的效率

　　　　　　2、单表数据量太大（订单表/商品表）

　　　　　　　　水平分表（地域/时间/类别/随机算法）

　　分完之后，数据的查询怎么办呢？

　　　　拆分的时候要根据产品特点来

　　　　　　房产-----按照城市拆分

　　　　　　订单/流水-----按时间拆分

　　　　　　追求平均----随机算法

　　　　　　抛砖引玉，自己要根据业务特点来拆分，考虑业务，拆分的维度，最好不要再被聚合查询，但是又无法完全避免业务操作。

　　　　1、跨库垮表join---很不推荐

　　　　2、汇总表，定时任务定时作业====》专门应该特殊要求

　　　　3、特殊要求，就是为难你，同步一个报表库（总库），专门满足需求，性能肯定低

　　　　4、向业务妥协

　　表分区：就是水平分表的意思，也可以垂直分（用的少）