mysql系列(四) mysql数据库设计优化

序号	名称	链接地址
1	mysql系列(一) centos7 安装msql	https://blog.csdn.net/qq_38130094/article/details/103529535
2	mysql系列(二)mysql简介之逻辑架构/锁粒度/事务/死锁/事务日志/MVCC	https://blog.csdn.net/qq_38130094/article/details/103549194
3	mysql系列(三) mysql存储引擎简介	https://blog.csdn.net/qq_38130094/article/details/103599497
4	mysql系列(四) mysql数据库设计优化	https://blog.csdn.net/qq_38130094/article/details/103551778
5	mysql系列(五) mysql索引详细解析及使用	https://blog.csdn.net/qq_38130094/article/details/103553971
6	mysql系列(六)mysql 慢日志查询(pt-query-digest)/如何单条SQL分析和Explain及trace工具	https://blog.csdn.net/qq_38130094/article/details/103551705
7	mysql系列(七)mysql 主从复制和mysql查询优化	https://blog.csdn.net/qq_38130094/article/details/103603586

mysql数据库优化是一项繁杂且艰巨的工作，需要各方面的经验来达到最终的优化效果；而且只有当mysql到达一定的数据量优化的效果才会凸显。

总之你知道的越多，你不知道的越多；技术之路永无止境

1. 选择优化的数据类型

1.更小的通常更好：一般情况下，应该尽量选择使用可以正确存储数据的最小数据类型。例如：只需要存储-128~127，tinyint更好。更小 的数据类型通常更快，因为它们占用更少的磁盘，内存和CPU缓存，并且处理时需要的CPU周期也更少。

2.简单： 简单数据类型的操作通常需要更少的CPU周期。例如，整形比字符操作代价更低，因为字符集和校对规则（排序规则） 使字符比整形更复杂。例如：使用date/time/datetime而不是字符串来存储日期和时间，使用整形存储IP地址【 INET_ATON()  INET_NTON 】

3.尽量避免NULL : 如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。 可为NULL的列会使用更多的存储空间，在MySQL中也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个 额外的字节。如果计划在列上建索引，尽量避免设计为可为NULL的列。当然也有例外，例如InnoDB使用单独的位（bit）存 储NULL值，所以对于稀疏数据有很好的空间效率。

2. schema设计

1.数据库列的设计：MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。转换的代价依赖于列的数量

2.表关联：太多的表关联情况下，解析和优化查询的代价也会非常高。如果希望查询执行快且并发性好，单个查 询最好在12个表以内做关联

3.数据库枚举 ：

3. 范式与反范式

1、第一范式（1NF）：无重复的列.表中的每一列都是不可分割的基本数据项.不满足1NF的数据库不是关系数据库.
考虑这样一个表：【联系人】（姓名，性别，电话）

2、第二范式（2NF）： 属性完全依赖于主键。首先要满足它是1NF，不存在仅依赖于关键一部分的属性（不能存在部分依赖于主键）
例子：选课关系(学号，课程名称，成绩，学分)，学号与课程名称是主键，其不满足2NF，因为课程名称->学分

3、第三范式（3NF）：属性不传递依赖于其他非主属性，非主键必须直接依赖于主键而不能传递依赖
例子：学生表(学号，姓名，学院编号，学院名称)，学号是主键,姓名、学院编号、学院名称都完全依赖于学号,满足2NF,但不满足3NF,因为学院名称直接依赖的是学院编号 ,它是通过传递才依赖于主键.

3.1范式化的优点：

1. 范式化的更新操作通常比反范式化要快。
2. 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
3. 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
4. 很少有多余的数据意味着检索列表数据时，更少需要Distinct或者Group By语句，去保证数据的唯一性。 

没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率。

数据库优化总结

• 尽量避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表设计。
• 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL 值。
• 尽量使用相同的数据类型存储相似或相关的值，尤其是需要在关联条件中使用的列。
• 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存和索引上面。
• 尽量使用整形定义标识列。
• 避免使用MySQL已经废弃的特性，例如指定浮点数的精度等。
• 小心使用ENUM和SET。虽然他们用起来很方便，但是不要滥用，否则有可能变成陷阱。