MySql表设计与优化

MySql设计与优化系列笔记：
一、数据库设计三范式与反范式
二、MySql表设计与优化

1、实体关系分析

• 实体关系需要双向分析，实体关系决定表关系
• 实体关系种类：一对一，一对多，多对多

实体关系分析

2、表关系设计

• 一对一：通过主键将两张表进行关联
• 一对多：在多的一方设置外键
• 多对多：增加中间表，持有双方外键

例：学生信息表关系设计

3、自然主键与代理主键

• 自然主键是指事物属性中的自然唯一标识，如学生信息表中的学号
• 代理主键是指与业务无关的，无意义的数字序列
• 推荐使用代理主键，

4、数据类型的选择

• 性能优先级：数字类型 > 日期类型与二进制类型 > 字符串类型

• 1：数字类型

代理主键使用无符号int就足够了，可以表达40亿个数字，

整数类型 实数类型

• 2：varchar

varchar根据实际内容长度保存数据
1、使用最小的符合需求的长度。
2、varchar(n) n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。
3、varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小 。
4、varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时时，都会导致锁表。

使用场景：
1、存储长度波动较大的数据，如：文章，有的会很短有的会很长
2、字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度
3、适合保存多字节字符，如：汉字，特殊字符等

• 3：char

1、最大长度：255
2、会自动删除末尾的空格
3、检索效率、写效率 会比varchar高，以空间换时间

使用场景：
1、存储长度波动不大的数据，如：md5摘要
2、存储短字符串、经常更新的字符串

• 4、datetime 日期数据类型

• 占用8个字节
• 与时区无关，数据库底层时区配置，对datetime无效
• 可保存到毫秒
• 可保存时间范围大
  PS: 不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性

• 5、timestamp 时间戳

占用4个字节
时间范围：1970-01-01到2038-01-19
精确到秒
采用整形存储
依赖数据库设置的时区
自动更新timestamp列的值

• 整理自老齐的it加油站以及其它互联网资料