阅读量:0
MySQL数据库表格设计涉及创建表、字段、数据类型、主键、外键等，以优化数据存储和查询性能。
MySQL数据库表格设计及数据库对象设计
命名规范
在MySQL数据库中，表名、字段名和索引名的命名规范至关重要，以下是一些常见的命名准则：
1、使用英文命名：所有表名、字段名和索引名应使用英文单词，确保可读性和一致性。
2、小写字母和数字：名称只能包含小写字母或数字，禁止使用数字开头。
3、避免拼音和缩写：不推荐使用拼音或英文缩写，以增加代码的可读性和维护性。
4、主键和索引命名规则：
主键索引命名为pk_ + 字段名。
唯一索引命名为uk_ + 字段名。
普通索引命名为idx_ + 字段名。
选择合适的字段类型
选择适当的字段类型有助于优化存储空间和查询性能：
1、整数类型：从TINYINT 到BIGINT，根据实际需求选择合适的整数类型。
2、浮点数类型：对于金额等精确计算，使用DECIMAL 类型，避免使用FLOAT 和DOUBLE。
3、字符串类型：
定长字符串使用CHAR。
不定长字符串使用VARCHAR，长度不超过5000字符。
超长文本使用TEXT 或LONGTEXT。
4、日期时间类型：使用DATETIME 或TIMESTAMP 来存储日期和时间信息。
主键设计要合理
主键是数据库表的核心，设计时需注意以下几点：
1、与业务无关：主键不应与业务逻辑相关联，建议使用无意义的自增ID，如UUID或Auto_increment。
2、自增主键：推荐使用自增字段类型（如AUTO_INCREMENT）来生成主键，确保主键的唯一性和连续性。
3、避免使用UUID：尽管UUID能保证全球唯一性，但其作为主键会带来性能和索引效率问题。
选择合适的字段长度
字段长度的选择直接影响存储空间和查询性能：
1、字符类型长度：VARCHAR 和CHAR 表示字符长度，其他类型表示字节长度。
2、用户名字段示例：如果用户名长度为5~20个字符，可设置为VARCHAR(32)，并尽量设置为2的幂。
3、大字段处理：若字段内容过大，建议将其转为TEXT 类型，并单独抽离出一张表进行管理。
优先考虑逻辑删除而非物理删除
在数据库操作中，删除记录是一个常见需求，推荐使用逻辑删除而非物理删除：
1、物理删除：直接从硬盘中删除数据，释放存储空间，但恢复困难，可能导致自增主键不连续。
2、逻辑删除：通过添加标记字段（如is_deleted）来标识记录已删除，便于数据恢复。
每个表都需要添加通用字段
为了方便管理和审计，每个表都应包含一些通用字段：
1、主键 (id)：每张表必须有一个主键，通常为自增字段。
2、创建时间和修改时间 (create_time, modified_time)：记录行数据的创建和最后修改时间。
3、版本号 (version)：用于乐观锁，非必须。
4、备注 (remark)：记录数据相关的备注信息，非必须。
一张表的字段不宜过多
表设计时应注意字段数量的控制：
1、字段数量限制：每张表的字段数量尽量不要超过20个，以避免查询效率下降。
2、拆分大表：若业务需求导致字段过多，可将表拆分成多张小表，通过主键关联。
尽可能使用NOT NULL定义字段
为了确保数据的完整性和查询性能，应尽量将字段定义为 NOT NULL：
1、防止空指针问题：NOT NULL 可以防止出现空指针异常。
2、提高查询性能：NULL 值需要额外的存储空间，且会使比较运算更复杂。
3、默认值设置：若无特殊理由，建议将字段设为 NOT NULL，并设置合理的默认值。
设计表时评估哪些字段需要加索引
合理使用索引可以显著提高查询性能：
1、评估数据量：若表的数据量较少，索引的效果不明显；数据量大时，应根据查询条件建立相应索引。
2、索引数量控制：单表索引个数一般不超过5个，过多索引会降低写操作的性能。
3、区分度高的字段：如性别等区分度低的字段不适合建立索引。
4、联合索引和覆盖索引：利用最左匹配原则和覆盖索引优化查询。
相关问题与解答
1. 为什么推荐使用逻辑删除而不是物理删除？
答：逻辑删除通过添加标记字段（如is_deleted）来标识记录已删除，这样可以避免物理删除带来的数据恢复困难和自增主键不连续的问题，同时便于数据审计和管理。
2. 如何选择合适的字段类型和长度？
答：选择字段类型时应根据数据的实际需求，如整数类型可选TINYINT 到BIGINT，浮点数类型用DECIMAL，字符串类型根据是否定长选择CHAR 或VARCHAR，字段长度应充分考虑实际数据长度，如VARCHAR 类型的字段长度尽量设置为2的幂。
