数据库设计规范建议收藏

关于库

1. 【强制】名称长度与字符限制：库的名称长度必须控制在32个字符以内，且仅能包含英文字母、数字以及下划线（_）。建议以英文字母开头，以便于国际化和可读性。
2. 【强制】大小写与命名风格：库名中的所有英文单词必须统一使用小写字母，不同单词之间通过下划线（_）进行分隔，确保名称的清晰易懂，即“见名知意”。
3. 【强制】命名格式：库的名称应遵循“业务系统名称_子系统名”的格式，以明确标识其所属的业务领域和子系统功能。
4. 【强制】避免使用关键字：库名中严禁使用SQL中的保留关键字（如type、order等），以避免潜在的语法冲突或误解。
5. 【强制】字符集指定：在创建数据库时，必须显式指定字符集，且字符集仅限于utf8或utf8mb4，以确保多语言环境下的数据兼容性和正确性。例如，创建数据库SQL语句为：CREATE DATABASE crm_fund DEFAULT CHARACTER SET 'utf8'; 注意，根据实际需要，utf8mb4可能是更优选择，因为它支持更全面的Unicode字符集。
6. 【建议】权限最小化原则：对于程序连接数据库的账号，应遵循权限最小化的原则，即数据库账号应仅在其所需的特定数据库中使用，且不应跨库操作。此外，原则上程序使用的数据库账号不应具备DROP权限，以降低数据误删除的风险。
7. 【建议】临时库与备份库命名：

• 临时库：以tmp_为前缀，并附加日期作为后缀，以便于区分和管理临时存储的数据。例如：tmp_20230401_data。
• 备份库：以bak_为前缀，同样附加日期作为后缀，用于存储数据库的备份数据。例如：bak_20230331_crm_fund。这样的命名方式有助于快速识别库的用途和备份时间，便于数据恢复和版本管理。

关于表、列

1. 【强制】表和列的名称长度应控制在32个字符以内，仅使用英文字母（建议以英文字母开头）、数字和下划线（_）组合。
2. 【强制】表名、列名采用小写字母，单词间通过下划线分隔，确保名称具有自描述性，即“见名知意”。
3. 【强制】表名需包含模块名前缀，以体现其所属业务模块，例如：crm_fund_item。
4. 【强制】创建表时，必须显式指定字符集为utf8或utf8mb4，以支持更广泛的字符集（包括emoji等）。
5. 【强制】避免在表名、列名中使用SQL关键字（如type、order等），以及任何特殊字符（除了下划线作为分隔符）。
6. 【强制】明确指定表的存储引擎类型，除非有特定需求，否则应使用InnoDB，因为它支持事务处理、行级锁定和外键等特性。
7. 【强制】所有表必须有COMMENT说明，简短描述表的用途或存储的数据内容。
8. 【强制】字段命名应使用实际含义的英文单词或缩写，如corp_id代替corporation_id。
9. 【强制】布尔值字段以is_开头，后跟描述性词汇，如is_enabled。
10. 【强制】禁止在数据库中直接存储大型二进制数据（如图片、文件等），应使用文件服务器存储，数据库中仅保存文件路径或URL。
11. 【建议】表的主键应为id，类型为INT UNSIGNED AUTO_INCREMENT或BIGINT UNSIGNED AUTO_INCREMENT，视预期数据量而定。非唯一标识主体的字段（如user_id、order_id）可设为UNIQUE KEY索引，避免主键随机插入导致的性能问题。
12. 【建议】核心表应包含create_time（创建时间）和update_time（最后更新时间）字段，便于问题追踪和数据分析。
13. 【建议】尽量将字段设置为NOT NULL，并合理设置DEFAULT值。使用NULL值可能会带来额外的存储开销、数据迁移风险及查询性能问题。
14. 【建议】在同一数据库或业务逻辑中，存储相同数据的列应采用相同的列名和列类型，以避免数据类型转换导致的性能问题。
15. 【建议】中间表或临时表以tmp_开头，备份表以bak_开头，以区分其用途。同时，应定期清理这些表，避免占用过多存储空间。
16. 【示范】一个较为规范的建表语句：

17. 【建议】创建表时，可以使用可视化工具。这样可以确保表、字段相关的约定都能设置上。 实际上，我们通常很少自己写 DDL 语句，可以使用一些可视化工具来创建和操作数据库和数据表。 可视化工具除了方便，还能直接帮我们将数据库的结构定义转化成 SQL 语言，方便数据库和数据表结构的导出和导入。

关于索引

1. 【强制】InnoDB表的主键必须为id，类型可以是INT AUTO_INCREMENT或BIGINT AUTO_INCREMENT，且主键值一旦被设置后禁止更新。
2. 【强制】InnoDB和MyISAM存储引擎的表，索引类型必须为BTREE。
3. 【建议】索引名称应遵循一定的命名规则，以提高可读性。主键索引以pk_开头，唯一键索引以uni_或uk_开头，普通索引以idx_开头。索引名称一律使用小写字母，并尽量以相关字段的名称或缩写作为后缀。
4. 【建议】对于多单词组成的列名，在创建索引时可以取前几个单词的首字母加上最后一个单词来简化索引名称。例如，在foo表的member_id列上创建的索引可以命名为idx_foo_mid。
5. 【建议】单个表上的索引数量应控制在合理范围内，一般不建议超过6个，以避免过多的索引维护开销和降低写操作的性能。
6. 【建议】在建立索引时，优先考虑建立联合索引，并根据查询条件中字段的区分度将区分度最高的字段放在最前面。
7. 【建议】在多表JOIN的SQL查询中，确保被驱动表的连接列上有索引，以提高JOIN操作的执行效率。
8. 【建议】建表或加索引时，应检查并避免冗余索引的存在。如果表中已经存在KEY(a,b)，则单独的KEY(a)是冗余的，需要删除。
9. 【强制】程序端的SELECT语句必须明确指定需要查询的字段名称，禁止使用*来选择所有字段。
10. 【建议】INSERT语句也应指定具体的字段名称，避免使用INSERT INTO t1 VALUES(...)的写法。
11. 【建议】除静态表或小表外，DML语句（如UPDATE、DELETE）应包含WHERE条件，并尽量使用索引来加速查找过程。
12. 【建议】在批量插入数据时（如INSERT INTO...VALUES(XX),(XX),(XX)...），每个批次的值不宜过多，建议不超过5000个，以避免引起主从同步延迟等问题。
13. 【建议】在可能的情况下，使用UNION ALL代替UNION，因为UNION ALL不会去除重复行，执行效率更高。同时，UNION子句的数量应限制在合理范围内，如5个以内。
14. 【建议】线上环境的多表JOIN操作应尽量避免超过5个表，以减少查询的复杂性和执行时间。
15. 【建议】减少使用ORDER BY、GROUP BY和DISTINCT等操作，这些操作较为耗费CPU资源。如果业务上确实需要排序，尽量与业务沟通，看是否可以在程序端进行排序处理。
16. 【建议】对于包含排序、分组等操作的查询，WHERE条件过滤出的结果集应保持在合理范围内，如1000行以内，以避免查询过慢。
17. 【建议】对单表的多次ALTER操作应合并为一次执行，特别是对于超过100万行的大表，ALTER TABLE操作必须经过DBA审核，并在业务低峰期执行。同时，多个ALTER操作应整合在一起执行，以减少锁表时间和对业务的影响。
18. 【建议】在批量操作数据时，应控制事务处理的间隔时间，并在必要时进行sleep操作，以避免长时间占用数据库资源。
19. 【建议】事务中包含的SQL语句数量应控制在合理范围内，如不超过5个，以减少锁数据的时间和MySQL内部资源的消耗。
20. 【建议】事务中的更新语句应尽量基于主键或UNIQUE KEY来执行，以减少锁的范围和避免死锁的发生。

欢迎加入 DevXTalk 的程序员、架构师、IT从业者交流群,在这里你可以和大佬们一起交流分享技术,行业经验,职位内推,摸鱼聊天。总之期待你的加入... 添加我的wx,备注：“进群”拉你进群(群二维码有效时间太短维持不了几天)

有招聘需求的也可以加我微信把招聘岗位发给我，我会贴在文章末尾，毕竟我的读者个个都是人才。

有需要推广自己的开源作品的也可以联系我，可以在公众号内帮助推广。

看完本文有收获？转发给更多的朋友吧

DevXTalk 不止于技术