数据库架构--物理设计

物理设计
1、定义数据库、表及字段的命名规范
1.1、数据库、表及字段的命名要遵守：可读性原则
1.2、数据库、表及字段的命名要遵守：表意性原则  
1.3、数据库、表及字段的命名要遵守：长名原则（适度）

2、选择合适的存储引擎
存储引擎     事务      锁粒度              主要应用        忌用
MyISAM     不支持   支持并发插入的表级锁  SELECT，INSERT  读写操作频繁
MRG_MYISAM 不支持   支持并发插入的表级锁  分段归档，数据仓库 全局查找过多的场景
Innodb     支持       支持MVCC的行级锁    事务处理       无
Archive    不支持      行级锁            日志记录，只支持insert，select   需要随机读取，更新，删除
Ndb cluster  支持     行级锁              高可用性       大部分应用

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

3.1、如何选择正确的整数类型
2的n次幂   2的n-1次幂~2的n-1次幂-1
列类型     存储空间       取值范围signed     unsigned
tinyint     1字节           -128-127            0-255
smallint    2字节           -32768-32767        0-65535
mediumint   3字节          -8388608-838607      0-16777215
int    4字节      -2147483648-21474483647  0-4294967295
bigint      8字节      -9223372036854775808-9223372036854775807  0-18446744073709551615

3.2、如何选择正确的实数类型
列类型            存储空间                                            是否精确类型
FLOAT            4个字节                                                   否
DOUBLE          8个字节                                                   否
DECIAML         每4个字节存9个数字，小数点站一个字节        是

3.3、如何选择varchar和char
注意：存储是以字符为单位，不是以字节为单位
utf-8一个字符占用3个字节

VARCHAR类型的存储特点
varchar用于存储长字符串，只占用必要的存储空间
列最大长度小于255则只占用一个额外字节用于记录字符串长度
列最大长度大于255则需要用2个额外字节用于记录字符串长度

VARCHAR长度的选择问题
使用最小的符合需求的长度
注意:MySQL5.7之前的版本，varchar的长度修改时需要锁表

适用场景
字符串列的最大长度比平均长度大很多
字符串列很少被更新（为了生成碎片）
使用了多字节字符集存储字符串

char类型的存储特点
char类型是定长的
字符串存储在char中的列中会被删除末尾的空格，varchar不会
char 最大的宽度为255，如果超过需要选择varchar
适合的场景
char类型适合存储所长度近似值（如md5值，手机号）
char类型适合短字符串
char类型适合存储经常更新的字符串列

如何存储日期数据

DATATIME类型
以YYYY-MM-DD HH：MM：SS格式存储日期时间
与时区无关，占用8个字节的存储空间
时间范围10000-01-01 00：00:00到9999-12-31 23：59：59

TIMESTAMP类型
存储时间戳占用4个字节
时间范围：1970-01-01到2038-01-19
依赖于所指定的时区
在行的数据修改时可以自动修改timestamp 

date类型和time类型
1、把日期部分存储为字符串（至少要8个字节）
2、使用int类型来存储（4个字节）
3、使用datetime类型（8个字节）

date类型的优点:
1、占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节
2、使用Date类型还可以利用日期时间函数进行日期之间的计算
3、保存时间:1000-01-01到9999-12-31之间的日期

time类型用于存储时间数据，格式为HH:MM:SS
注意事项
1、不要使用字符串类型来存储日期时间数据
1.1、日期时间类型通常比字符串占用的存储空间小
1.2、日期时间类型在进行查找过滤时可以利用日期来进对比
1.3、日期时间类型还有着丰富的处理函数，可以方便的对日期类型进行日期计算
2、使用int存储日期不如使用timestamp类型

4、建立数据库结构
Innodb主键
1、主键应该尽可能的小
2、主键应该是顺序增长的（插入数据的插入效率）
3、 Innodb的主键和业务主键可以不同