数据库设计和切分的学习记录
博客专区 > Its_Ryan 的博客 > 博客详情
数据库设计和切分的学习记录
Its_Ryan 发表于6个月前
数据库设计和切分的学习记录
  • 发表于 6个月前
  • 阅读 4
  • 收藏 1
  • 点赞 0
  • 评论 0

腾讯云 新注册用户 域名抢购1元起>>>   

优良的数据库设计:减少数据冗余,避免数据维护异常,节约存储空间,高效访问

慢查询?

数据库设计的步骤:需求分析,逻辑设计,物理设计,维护优化

需求分析部分:系统存储数据,数据的存储特点,数据的生命周期

需求分析举例:电商项目,用户信息表(属性,唯一标识,存储时效),商品信息,供应商信息,购物车信息

逻辑设计:ER图展现逻辑模型,椭圆标识属性,菱形标识关系,矩形标识实体

设计范式:123,BC范式

操作异常:插入异常,更新异常,删除异常
数据冗余:相同数据存在于多个地方

第一范式:表中所有字段都是单一属性,不可再分,单一属性是由基本数据类型构成

第二范式:


--》反范式化,以存储空间换时间

Oracle--事务操作成本低,扩展更容易且安全性更高

MySQL存储引擎:5.5之前,mysam不支持事务,读写效率更高
        innodb支持事务,支持行级锁
        archive不支持事务,支持行级锁,日志记录
        ndb cluster 

命名规范的重要性
字段类型的选择:优先选择数字类型,选用占用空间更少的,(优化io性能

同一级别字段类型选择:小于50字节优先选择char类型,decimal存储精确数据,float用于存储非精确数据

时间类型存储:int存储时间字段优缺点,优点是字段长度比datetime小但是使用起来不方便需要进行函数转换,只能存储到未来某个时间点

如何选择主键:区分 业务主键,数据库主键
业务主键主要用于标识业务数据,进行表和表之间的关联,数据库主键是为了优化数据存储(innodb会生成6个字节的隐含主键)

根据数据库的类型考虑主键是否需要顺序增长,主键字段类型所占空间尽可能小

避免使用外键约束:降低数据导入效率,增加维护成本,虽不建议使用外键约束但是相关联的列一定要建立索引提高查询效率

避免使用触发器:降低数据导入效率,可能会出现数据异常,使得业务逻辑更复杂

关于预留字段:预留字段的类型和内容无法预知,后期维护字段所需成本和增加一个字段成本是一样的,所以严禁使用预留字段

为何反范式化:减少表的关联数量,增加数据的读取效率,反范式化一定要适度

数据库维护和优化:维护数据字典,维护索引,维护表结构,适当对表进行水平拆分和垂直拆分

如何维护数据字典:第三方工具对数据库字典维护,利用数据库自身的备注字段维护数据字典(comment‘。。。’)

维护索引:
    如何选择合适的列建立索引:
    出现在where,group by,order by从句中的列
    可选择性高的列要放到索引的前面
    索引不要包含太长的数据类型

注意事项:索引不能过量,过多的索引不但会降低写的效率也会降低读的效率
    定期维护索引碎片
    SQL语句不要强制使用索引关键字(指定索引可能已经更名或者已经不适合作为索引而被删除的情况)

表结构维护:注意事项1使用在线变更表结构的工具:MySQL5.5之前使用pt-online-schema-change,MySQL5.6之后支持在线表结构变更
            2同时对数据字典进行维护3控制表的宽度和大小

数据库适合的操作:1批量操作2禁止使用*(避免io浪费,查询出不需要的字段是一种浪费)3控制使用自定义函数(大量使用函数使得列中的索引不起作用,所以自定义函数往往比较低效)4不要使用数据库全文索引

表的垂直拆分:为控制表的宽度可以进行表的垂直拆分1经常一起查询的列放到一起2text,blob等大字段拆分出到附加表

表的水平拆分:为了控制表的大小可以进行表的水平拆分,方法:主键hash,垂直和水平拆分都是为了优化表的io

水平拆分的举例:取模的方式进行表的拆分,例如有一张400w用户的用户表,为提高查询效率分成四张表,通过ID取模的方式把数据分散到四张表内,然后查询更新删除也都是通过取模的方式来进行,先知道要去哪个分表然后找到具体分表操作,插入数据时需要一张临时表提供自增ID,得到自增ID再取模进行分表插入数据

 

 

海量数据的存储和访问,瓶颈问题

大型互联网应用,数据库负载问题,系统稳定性扩展性造成极大问题

通过数据切分提高网站的性能,横向扩展数据层,架构研发人员的首选

水平切分可以降低单台机器的访问负载,降低了宕机的可能,通过负载均衡策略有效降低单台机器的访问

SQL语法的差异:pl/sql或t/sql,同样的功能在不同的数据库的实现方式是不一样的,比如分页SQL Oracle数据库使用rollnumber,MySQL数据库使用limit关键字,SQLserver使用

程序过分依赖SQL语句 的话对将来的移植扩展以及维护带来很大的麻烦

使用完全面向对象的方式抛弃SQL

orm框架技术,对jdbc进行轻量级的封装,持久化层ejb,toplink,mybatis,hibernate

为何需要数据库切分:用免费的MySQL和廉价的Server甚至是PC做集群,达到小型机+大型商业DB的效果,减少大量的资金投入,降低运营成本

怎么做到数据库的切分:物理上:通过切分规则将数据分布到不同的服务器上,通过路由规则访问特定的数据库,这样访问数据库就不是每次面对单台服务器而是很多服务器,降低负载压力

            数据库内部:通过切分规则将数据分布到一个数据库的不同数据表当中,提高数据库的运行效率提高了并发量(不单单是这些,还有诸如写操作的锁操作?)
综上归纳:分库降低了单点机器的负载,分表提高了数据操作的效率(尤其是写的效率)

要想做到数据的水平切分在每个表中都要有冗余字符作为切分依据和标记字段

基于标记字段有下列三种分库方式和规则:号段分区(优点是可部分迁移缺点则是数据分布不均),hash取模分区(优点是数据分布均匀缺点是数据迁移麻烦不能按照机器性能分摊数据),认证库保存数据库配置(单独建立数据库以单独保存标记字段到具体切分数据库的映射关系,优点是灵活性强一对一关系缺点是每次查询之前都多一次查询性能大打折扣大打折扣)

分布式数据方案提供功能:1分库规则和路由规则(routerule简称rr)2引入集群(group)概念保证数据的高可用性 3引入负载均衡策略(LoadBalancePolicy简称LB)4引入集群节点可用性探测机制,对单点机器可用性进行定时侦测保证LB策略的正确实施以确保系统的高稳定性 5引入读写分离策略提高数据的查询速度

仅仅是分库分表的数据层设计也是不够完善的,采用数据库切分的方案也就是有n台机器组成了一个完整的DB,一台机器宕机也仅是N分之一数据不能访问了而已,起码比切分之前的情况好多了,总不至于整个DB都不能访问,一般应用这样的机器故障导致的数据无法访问是可以接受的,但如果系统是一个高并发的电子商务网站单节点宕机带来的经济损失是非常严重的,也就是说现在我们的方案还是存在问题的,容错性能是经不起考验的。

引入集群概念:每一个分库的节点引入多台机器,每台机器保存一样的数据。

1没有引入集群以前,我们的一次查询的过程大致如下:请求数据层,并传递必要的分库区分字段 (通常情况下是user_id)。数据层根据区分字段Route到具体的DB,在这个确定的DB内进行数据操作。

2当时引入集群会 是什么样子的呢?我们的路由器上规则和策略其实只能路由到具体的Group,也就是只能路由到一个虚拟的Group,这个Group并不是某个特定的物理服务器。接下来需要做的工作就是找到具体的物理的DB服务器,以进行具体的数据操作。

基于这个环节的需求,我们引入了负载均衡器的概念 (LB),负载均衡器的职责就是定位到一台具体的DB服务器。具体的规则如下:负载均衡器会分析当前sql的读写特性,如果是写操作或者是要求实时性很强的操作的话,直接将查询负载分到Master,如果是读操作则通过负载均衡策略分配一个Slave。

负载均衡包括随机负载均衡和加权负载均衡

引入集群节点的可用性探测机制 ,或者是可用性的数据推送机制:一个是主动的监听机制,一个是被动的被告知的机制

Master和Slave:一个Group由1个Master和N个Slave组成;一切写的操作都在Master上进行,而读的操作则分摊到Slave上进行(一般的互联网应用中,经过一些数据调查得出结论,读/写的比例大概在 10:1左右)

为什么要分离读和写:写操作涉及到锁的问题,不管是行锁还是表锁还是块锁,都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上,而读操作其其他 的N个节点上进行,从另一个方面有效的提高了读的效率,保证了系统的高可用性

共有 人打赏支持
粉丝 0
博文 20
码字总数 21471
×
Its_Ryan
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: