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

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

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

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

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

数据库表开发流程

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

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

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

优化设计第一步

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

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、全小写

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

未经允许不得转载:JX BLOG » MySQL-性能优化-优化设计和设计原则

赞 (1)

评论 0

评论前必须登录!

登陆 注册