MySQL-性能优化-优化设计和设计原则

MySQL性能优化目的

如何合理的设计数据库? 什么样的数据库设计才能给后期DBA优化提供基石?

数据库设计与程序设计的差异?

数据库设计早期优化

1. 关系明确(理清表之间的关系,可以通过冗余的方式提高效率) 2. 节省空间(根据业务经验,设置字段长短) 3. 提高效率

数据库表开发流程

原型=>逐步完善(表的设计也是如此)

数据库种类

1. 层级数据库(注册表) 如:Windows操作系统的核心就是一个注册表,由于配置项比较多,采用层级关系的数据存储 2. 关系型数据库 如:MySQL 3. 时序数据库 4. 图数据库 如:最短路径,地理信息 5. Key-value数据库 如:Redis 6. 对象数据库 7. BigTable数据库

文件系统和数据库系统之间的区别?

(1)文件系统用文件将数据长期保存在外存上,数据库系统用数据库统一存储数据; (2)文件系统中的程序和数据有一定的联系,数据库系统中的程序和数据分离; (3)文件系统用操作系统中的存取方法对数据进行管理,数据库系统用DBMS统一管理和控制数据; (4)文件系统实现以文件为单位的数据共享,数据库系统实现以记录和字段为单位的数据共享。

优化设计第一步

想要在表设计中节省空间,就必须精通各种数据类型的特点(能用在什么业务上)、长度等。

int类型只增主键字段=>4字节=>每个字节8位=>32位,在CPU加载一条指令的时候,4字节是和CPU寄存器的运算有关,如:64位,由于直接的系统一般都是32位的,所以在运算4字节的数据是刚好的,效率最高,而现今我们系统基本都是64位的时候,其实没有更好的利用好CPU运算,所以在设计表字段建议,使用8字节的主键bigint,而不是直接使用int来做主键。

uuid做主键,字符类型做主键,在CPU的加载是需要消耗更多的运算过程

char(10) 不管该字段是否存储数据,都占10个字符的存储空间 char(10) 同时存在一个坑,就是存储abc数据后改数据库字段的值为“abc 7个空格 ”,在精准查询(where)就必须带上后面的7个空格

varchar 不存的时候不占空间,存多长数据就占多少空间

优化设计第二步

如何合理的设计出符合三范式数据库表? 1NF:列不可分。每一列都是不可分割的基本数据项,如这样的设计就不合理,姓名(王五,wangwu) 2NF:1NF的基础上面,非主属性完全依赖于主关键字,如学生姓名(非主属性)就是依赖于学号(主属性)的。 3NF:属性不依赖于其它非主属性 , 消除传递依赖,如这样的设计就不合理,学号做主键,学生课程表(学号=课程),当学号修改,对应的课程表也需要修改,这就是属于传递依赖 BCNF:符合3NF,每个表中只有一个候选键 4NF:没有多值依赖 由于学号不能做主键,那用什么做主键?首先就有这样的规则:不要用业务规则来做主键,主键就应该和业务无关。 如经常用的的order_no(业务订单号),即使是唯一的,也不建议做主键的,容易产生传递依赖的问题,这样就不符合第三范式了。

优化设计第三步

数据库优化策略 1、选择小的数据类型 2、单独设计主键,并考虑分布式扩展 3、外键设计 (重要,我们之前开发都是直接使用的弱外键来设置主外键关系,而实际项目中,如果要是删除了主键对应的记录后,外键表中的记录是没有删除的,这样对于数据库的数据是很容易混乱的,不便于维护,那我要是使用的是强外键的方式,这样直接删除主键记录,没有删除外键表中的记录,这样是要报错的,这样容易找到代码上的问题,外键的设计能对于数据完整性有一个好的约束,当你开发的系统已经完全不会出现数据不完整的问题的时候,你可以考虑使用弱外键来关联表操作,也同时会省去外键消耗,具体的设置外键方法查考博客:外键及其约束理解) 4、索引设计 (对于业务上的字段,那些需要字段需要建立索引?) 5、关联关系表设计,多对一,多对多 6、读写频繁的信息,与不频繁的信息分开 (如在设计支付系统的时候,会同时存在订单表和订单记录表,订单表读写频繁,而订单记录表就管理人员用,读写一般) 7、配置表,日志表,定时任务表等 8、汇总表设计 (多表关联查询会很慢,还容易卡死的情况,可以考虑在业务上汇总,记录到汇总表)

优化设计第四步

经过业务的沉淀,积累出一些设计思路或抽取出多项目的共同点,减少开发成本

1、通用型设计 例:人员,部门,角色 2、特别设计 附件,日志,配置,监控等 3、存储设计 类型划分便于分区 4、一些附加字段 创建日期,修改日期,排序 5、流水表 类似于日志,但由业务处理结果组成,帐户变动或业务处理的中间值

在设计数据库的时候应当落实如下的原则

(一)降低对数据库功能的依赖(如在业务上使用了MySQL特性,且这个特性是只有MySQL存在的,对以后的数据库迁移会带来很大的麻烦) (二)定义实体关系的原则 牵涉到的实体 识别出关系所涉及的所有实体。 所有权 考虑一个实体“拥有”另一个实体的情况。 基数 考量一个实体的实例和另一个实体实例关联的数量。 (三)列意味着唯一的值 如果表示坐标(0,0),应该使用两列表示,而不是将“0,0”放在1个列中。 (四)列的顺序,可读性问题 (五)定义主键和外键 数据表必须定义主键和外键(如果有外键)。 (六)选择键 (七)是否允许NULL 任何值和NULL拼接后都为NULL。 所有与NULL进行的数学操作都返回NULL。 引入NULL后,逻辑不易处理。 (八)规范化——范式 1NF 包含分隔符类字符的字符串数据。 名字尾端有数字的属性。 没有定义键或键定义不好的表。 2NF 多个属性有同样的前缀。 重复的数据组。 汇总的数据,所引用的数据在一个完全不同的实体中。 BCNF- “每个键必须唯一标识实体,每个非键熟悉必须描述实体。” 4NF 三元关系(实体:实体:实体)。 潜伏的多值属性。(如多个手机号。) 临时数据或历史值。(需要将历史数据的主体提出,否则将存在大量冗余。) (九)选择数据类型 (十)优化并行 设计DB时就应该考虑到对并行进行优化,比如,timestamp类型。

命名规则

表名规则 1、要用前缀,但不要用无意义的前缀 2、下划线分隔 3、全小写 列名规则 1、一般不用前缀(当和关键词冲突的可以考虑加前缀区别) 2、下划线分隔 3、全小写

不管是表名设计还是列名设计,都不要使用拼音来命名,过一段时间就完全不记得了,就用英文,即使英语不好设计的时候也建议设置为英文。

原文发布于微信公众号 - JAVA烂猪皮(gp1106701116)

原文发表时间:2018-06-01

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏JavaWeb

原 荐 MySQL-性能优化-优化设计和设计

1804
来自专栏架构之路

悲观锁&乐观锁

最近意外发现之前对悲观锁乐观锁的理解有误,所以重新学习了一下。 1.悲观锁 悲观锁介绍(百科): 悲观锁,正如其名,它指的是对数据被外界(包括本系统当前的其他事...

2954
来自专栏沈唁志

谈谈在SQL语句中的优化技巧

1634
来自专栏微信终端开发团队的专栏

Matrix SQLiteLint -- SQLite 使用质量检测

SQLite 在移动端开发中广泛使用,其使用质量直接影响到产品的体验。

871
来自专栏搜云库

MySql常用30种SQL查询语句优化方法

1、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

43119
来自专栏微信公众号:Java团长

从程序员的角度深入理解MySQL

不必多说,数据当然需要存储;存储了还不够,显然需要提供程序对存储的操作进行封装,对外提供增删改查的API,即实例。

894
来自专栏数据和云

深入解析:由SQL解析失败看开发与DBA的性能之争

李华 云和恩墨高级技术顾问 以下案例来自大讲堂的一次分享,从这个案例中我们可以了解“错误的SQL”可能对数据库产生的种种影响。如何找到这些错误的、解析失败的S...

2935
来自专栏数据和云

故障分析:一则library cache lock问题处理

编辑手记:library cache lock 大家都并不陌生,在MOS上对该阻塞的一般成因描述为:一般可以理解的是alter table或者alter pac...

3465
来自专栏java架构技术

从大神的角度深入理解MySQL,值得收藏~

在此我向大家推荐一个架构学习交流群。程序员面试社区:236283328 里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分...

831
来自专栏IT技术精选文摘

从程序员的角度深入理解MySQL

数据库基本原理 ? 第一,数据库的组成:存储 + 实例 不必多说,数据当然需要存储;存储了还不够,显然需要提供程序对存储的操作进行封装,对外提供增删改查的API...

1705

扫码关注云+社区