MySQL 性能优化，优化设计及设计原则解读

末蓝、 2022-04-23 04:30 260阅读 0赞

**MySQL性能优化的目的**

如何合理的设计数据库？

什么样的数据库设计才能给后期DBA优化提供基石？

**数据库设计与程序设计的差异？**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwNDUwNDU_size_16_color_FFFFFF_t_70][]

**数据库设计早期优化**

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

**数据库表开发流程**

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

**数据库种类**

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

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwNDUwNDU_size_16_color_FFFFFF_t_70 1][]

**优化设计第一步**

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

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

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、单独设计主键，并考虑分布式扩展

4、索引设计

（对于业务上的字段，那些需要字段需要建立索引？）

5、关联关系表设计，多对一，多对多

6、读写频繁的信息，与不频繁的信息分开

（如在设计支付系统的时候，会同时存在订单表和订单记录表，订单表读写频繁，而订单记录表就管理人员用，读写一般）

7、配置表，日志表，定时任务表等

8、汇总表设计

（多表关联查询会很慢，还容易卡死的情况，可以考虑在业务上汇总，记录到汇总表）

**优化设计第四步**

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

1、通用型设计

例：人员，部门，角色

2、特别设计

附件，日志，配置，监控等

3、存储设计

类型划分便于分区

4、一些附加字段

创建日期，修改日期，排序

5、流水表

类似于日志，但由业务处理结果组成，帐户变动或业务处理的中间值

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

（一）降低对数据库功能的依赖（如在业务上使用了MySQL特性，且这个特性是只有MySQL存在的，对以后的数据库迁移会带来很大的麻烦）

（二）定义实体关系的原则

牵涉到的实体 识别出关系所涉及的所有实体。

所有权 考虑一个实体“拥有”另一个实体的情况。

基数 考量一个实体的实例和另一个实体实例关联的数量。

（三）列意味着唯一的值

如果表示坐标（0,0），应该使用两列表示，而不是将“0,0”放在1个列中。

（四）列的顺序，可读性问题

（五）定义主键

（六）选择键

（七）是否允许NULL

任何值和NULL拼接后都为NULL。

所有与NULL进行的数学操作都返回NULL。

引入NULL后，逻辑不易处理。

（九）选择数据类型

（十）优化并行

设计DB时就应该考虑到对并行进行优化，比如，timestamp类型。

**命名规则**

**表名规则**

1、要用前缀，但不要用无意义的前缀

2、下划线分隔

3、全小写

**列名规则**

1、一般不用前缀（当和关键词冲突的可以考虑加前缀区别）

2、下划线分隔

3、全小写

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwNDUwNDU_size_16_color_FFFFFF_t_70]: /images/20220323/34e6028a4dff493aa65d1ae1c1d08df2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwNDUwNDU_size_16_color_FFFFFF_t_70 1]: /images/20220323/021df0eeb2a349bdaa0218ca5728abfb.png