数据库设计

Posted on | In

数据库设计

宽表模式

把一个对象的所有属性存储到一个表中，这种类型的表在性能上可能对数据库造成困扰，因为它们需要加载更多的数据，并且需要更多的内存来存储索引。

优势

  1. 查询性能提升：宽表可以减少表关联操作的次数，从而减少查询的复杂性和提高查询性能。

  2. 简化数据模型：宽表可以将相关数据聚合在一张表中，简化数据模型和查询逻辑，提高开发和维护效率。

  3. 减少表关联操作：宽表可以避免频繁的表关联操作，降低系统开销和数据库负载。

存在问题

  1. 数据冗余：相同的数据一个表中出现了多次。

  2. 数据更新异常：修改一行中某列的值时，同时修改了多行数据。

  3. 数据插入异常：部分数据由于缺失主键信息而无法写入表中。

  4. 数据删除异常：删除某一项数据时不得不删除另一数据。

优化性能

  1. 使用索引

  2. 限制列数

  3. 垂直拆分

  4. 分区：将大型表分成多个分区，以提高查询性能和存储效率。

  5. 使用缓存

设计范式

  1. 第一范式：表中的所有字段都是不可再分的。

  2. 第二范式：表中必须存在业务主键，并且非主键依赖于全部业务主键。

  3. 第三范式：表中的非主键列之间不能相互依赖。

物理设计

MySQL常见的存储引擎
引擎名次 事务 说明
MYISAM N MySQL5.6之前的默认引擎，最常用的是非事务型存储引擎
CSV N 以CSV格式存储的非事务型存储引擎
Archive N 只允许查询和新增数据而不允许修改的非事务型存储引擎
Memory N 是一种易失性非事务存储引擎
INNODB Y 最常用的事务型存储引擎
InnoDB存储引擎的特点

  1. 事务型存储引擎支持ACID.

  2. 数据按主键聚集存储。

  3. 支持行级锁及MVCC。

  4. 支持Btree和自适应Hash索引。

  5. 支持全文和空间索引。

MySQL整数类型

  1. tinyint 1字节 SIGNED(-128~127) UNSIGNED(0~255)

  2. smallint 2字节 SIGNED(-32768~32767) UNSIGNED(0~65535)

  3. mediumint 3字节 SIGNED(-8388608~8388607) UNSIGNED(0~16777215)

  4. int 4字节 SIGNED(-2147483648~2147483647) UNSIGNED(0~4294967295)

  5. bigint 8字节 SIGNED(-9223372023654775808~9223372036854775807) UNSIGNED(0~18446744073907551615)

MySQL浮点类型
列表型 存储空间 是否精确类型
FLOAT 4个字节 否
DOUBLE 8个字节 否
DECIMAL 每4个字节存9个数字，小数点占一个字节 是
MySQL时间类型
类型 存储空间 格式 范围
DATE 3字节 YYYY-MM-DD 从1000-01-01到9999-12-31
TIME 3-6字节 HH:MM:SS 从-838:59:59到838:59:59
YEAR 1字节 YYYY 从1901到2155
DATETIME 5-8字节 YYYY-MM-DD HH:MM:SS 从1000-01-01 00:00:00到9999-12-31 23:59:59
TIMESTAMP 4-7字节 YYYY-MM-DD HH:MM:SS 从1970-01-01 00:00:01 UTC到2038-01-19 03:14:07 UTC
MySQL字符串类型
类型 范围 说明
Char(M) M=1~255个字符 固定长度
VarChar(M) 不能超过65535字节，UTF8mb4 varchar(10)=40字节 存储可变长度的M个字符
TinyText 最大长度255字节 可变长度
Text 最大长度65535字节 可变长度
MediumText 最大长度16777215字节 可变长度
LongText 最大长度4294967295字节 可变长度
Enum 集合最大数目65535 只能插入列表中的值
MySQL数据库命名

  1. 所有数据库对象名称必须使用小写字母可选下划线分割。

  2. 所有数据库对象名称定义禁止使用MySQL的关键字。

  3. 数据库对象的命名要能做到见名识义，并且最好不要超过32个字。

MySQL表和列的命名

  1. 临时库表必须以tmp为前缀并以日期为后缀。

  2. 用于备份的库，表必须以bak为前缀并以日期为后缀。

  3. 所有存储相同数据的列名和列类型必须一致。