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

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