什么是物理设计

根据所选择的关系型数据库的特点对逻辑模型进行存储结构设计。

MySQL数据库物理设计涉及的内容

1、定义数据库、表及字段命名规范

命名要遵守可读性原则。比如使用下划线来分割不同的单词等

遵守表意性原则。一看表名或字段名就知道是干什么的

长名原则。尽量少使用缩写，但命名也不能太长，适中最好。

2、选择合适的存储引擎

3、为表中字段选择合适的数据类型。

如何选择正确的整数类型

当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的类型。

整数类型的存储最大值是2的n-1次幂，最小值是2的n-1次幂减1，n是存储空间，比如tinyint，存储空间是1字节=8bit，取值范围就是-2的8-1次幂到2的8-1次幂减1，也就是-128~127之间的整数都可以存到tinyint中。需要说明的是，有一个误区，比如很多人使用int(2)来指定int类型的宽度，认为int(2)只会存储2位整数，这样可以节省存储空间，而实际上，这种限制整数类型的宽度是没有任何意义的，即使做了宽度的限制，int类型也会占用4个字节(32个bit)的空间，对存储值的范围也不会有任何影响，所以如果只需要存储2位数的话，使用tinyint类型。

如何选择浮点类型

通过例子来看下什么是精确类型

如上表结构，i列是int类型，d1、d2是双精度类型，对该表数据进行按i分组对d1、d2分表求和并且查找出d1求和与d2求和后不相等的数据，结果如下

从结果可以看出，两个double类型数之间的运算结果是不一样的，比如i为1分组，d1列求和(101.4-80)不等于21.4，这就说明double类型是非精确类型。

同样使用decimal类型来进行上面例子的操作

结果只有i为6的数据是不一样的，其他的都是一样的，说明decimal类型是精确类型。

但是decimal类型比其他两个类型占的存储空间要多。当进行和财务有关的数字运算时，一定要采用decimal。

如何选择正确的字符串类型，VARCHAR和CHAR的选择

VARCHAR类型的存储特点

1.varchar用于存储变长字符串，只占用必要的存储空间

2.列的最大长度小于255则只占用一个额外字节用于记录字符串长度

3.列的最大长度大于255则要占用两个额外字节用于记录字符串长度

VARCHAR长度选择问题：使用最小的符合需求的长度。这时会有个疑问，使用varchar(5)和varchar(200)来存储“MySQL”字符串，由于这两种列的宽度都没有超过255，所以都只需要占用一个额外的存储字节，存储"MySQL"这个字符串只需要6个字节的存储空间就够了，那么我们还有必要选择最小的符合需求的长度吗，难道不是说只要不超过255，像这类字符串所占的存储空间不是一样的吗？其实还是需要选择最小的符合需求的长度的，MySQL为了更有效的优化查询，在内存中对字符串使用的是固定的长度，所以如果我们把列的长度设置的太长，会占用更多的内存，这才是要求使用最小的符合需求的长度的原因。

VARCHAR适用场景

1.字符串列的最大长度比平均长度大很多。

2.字符串列很少被更新。因为varchar列更新字符长度会变化，可能会引起存储页的分裂，造成很多的存储碎片。

3.使用了多字节字符集存储字符串

CHAR类型的存储特点

1.CHAR的类型是定长的

2.字符串存储在CHAR类型的列中会删除末尾的空格

3.CHAR类型的最大宽度为255

CHAR适用场景

1.适合存储长度近似的值。比如MD5加密的数据、身份证等

2.适合存储短字符串

3.适合存储经常更新的字符串

如何存储日期数据

1.DATETIME类型，占用8个字节的存储空间

以YYYY-MM-DD HH:MM:SS[.fraction]格式存储日期时间

默认datetime=YYYY-MM-DD HH:MM:SS 也就是默认最小精度是秒，

如果想存到毫秒，就要定义一个宽度，这个宽度的最大值是6。比如datetime(6)=YYYY-MM-DD HH:MM:SS.fraction，fraction代表6位的毫秒数

DATETIME类型与时区无关，不会随着时区的变化而变化，如果我们在不同的时区中使用相同的数据，那就要注意了。

DATETIME存储范围很大：1000-01-01 00:00:00~9999-12-31 23:59:59

2.TIMESTAMP类型

存储占用 4个字节，以年月日小时分秒的日期型式显示，存储范围'1970-01-01 00:00:01' to '2038-01-19 03:14:07'，以UTC时区进行存储，但是以系统当前时间进行显示，可以在insert和update时把值自动更新为当前时间。

存储了由格林尼治时间1970年1月1日到当前时间的秒数，以YYYY-MM-DD HH:SS:MM[.fraction]的格式显示，占用四个字节，实际上是以int类型存储。比DATETIME类型存储范围小很多

时间范围：1970-01-01到2038-01-19。如果要存的时间范围小，那就选用TIMESTAMP类型，因为这要比DATETIME占用空间小的多。

TIMESTAMP类型显示的时间依赖于时区，不同时区显示不同的时间。在一个表中指定一个timestamp列，那么在本行的任何数据修改时都可以自动修改timestamp列的值，这是非常有用的，可以自动记录最后更新数据的时间。需要注意的是，如果定义了两个timestamp列，只有一个会自动更新。

3.date类型和time类型

date类型只存储日期部分，存储时间范围是1000-01-01~9999-12-31。比DATETIME、字符串、int类型占用的字节数要少，使用datetime和字符串类型存储日期至少需要8个字节，使用int存储需要4个字节，使用date类型只需要3个字节。使用date类型还可以利用日期时间函数进行日期之间的计算，这是其他类型做不到的。

time类型存储时间部分，格式HH:MM:SS[.fraction]。

存储日期时间类型的注意事项：

①不要使用字符串类型来存储日期时间数据。日期时间类型比字符串占用空间小，而且日期时间类型在进行查找过滤时可以利用日期来进行对比，如果使用字符串类型，那么只能按照字符集按顺序过滤，这是不符合对时间过滤的要求。

②使用int存储日期时间不如使用timestamp类型，有人认为把日期时间转换为时间戳存到int类型里会提高效率，这是不对的。timestamp存储占用的空间和INT类型相同，实际上timestamp类型的数据在存储时就是被保存成INT类型的数据来存储的，这和我们使用INT来存储日期时间数据可以说是完全一样的。由于同样是使用INT类型来保存数据，所以和INT类型一样其存储的时间范围也是有限制的，这一点大家一定要注意，超过了这个范围的日期数据建议大家使用datetime类型来保存。另外timestamp数据存储时是以UTC时区来保存的，在显示时MySQL会自动的把数据转换为当前连接所对应时间来显示。可见，使用timestamp来存储日期时间数据不但保证了数据类型的大小同INT类型一样，同时可以显示为日期时间格式，这在给我们使用数据带来了很多的方便。所以强烈建议大家，使用timestamp类型来存储日期数据而不要再使用INT类型了。