数据库设计规范标准

MySQL数据库设计规

目录

1.规背景与目的 (3)

2.设计规 (3)

3.1 数据库设计 (3)

3.2.1 库名 (3)

3.2.2 表结构 (3)

3.2.3 列数据类型优化 (4)

3.2.4 索引设计 (6)

3.2.5 分库分表、分区表 (7)

3.2.6 字符集 (7)

3.2.7 程序DAO层设计建议 (7)

3.2.8 一个规的建表语句示例 (8)

3.2 SQL编写 (8)

3.2.1 DML语句 (8)

3.2.2 多表连接 (9)

3.2.3 事务 (10)

3.2.4 排序和分组 (10)

3.2.5 线上禁止使用的SQL语句 (10)

1.规背景与目的

MySQL数据库与oracle、sqlserver等数据库相比，有其核上的优势与劣势。我们在使用MySQL数据库的时候需要遵循一定规，扬长避短。本规旨在帮助或指导RD、QA、OP等技术人员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL编写等方面予以规，从而为公司业务系统稳定、健康地运行提供保障。

2.设计规

3.1 数据库设计

以下所有规会按照【高危】、【强制】、【建议】三个级别进行标注，遵守优先级从高到低。对于不满足【高危】和【强制】两个级别的设计，DBA会强制打回要求修改。

3.2.1 库名

1. 【强制】库的名称必须控制在32个字符以，相关模块的表名与表名之间尽量提现join 的关系，如user表和user_login表。

2. 【强制】库的名称格式：业务系统名称_子系统名，同一模块使用的表名尽量使用统一前缀。

3. 【强制】一般分库名称命名格式是“库通配名_编号”，编号从“0”开始递增，比如“wenda_001”

以时间进行分库的名称格式是“库通配名_时间”

3.【强制】创建数据库时必须显式指定字符集，并且字符集只能是utf8或者utf8mb4 创建数据库SQL举例：

Create database db1 default character set utf8;

3.2.2 表结构

1. 【强制】表和列的名称必须控制在32个字符以，表名只能使用字母、数字和下划线，一律小写。

2. 【强制】表名要求模块名强相关，如师资系统采用”sz”作为前缀，渠道系统采用”qd”作为前缀等。

3. 【强制】创建表时必须显式指定字符集为utf8或utf8mb4。

4. 【强制】创建表时必须显式指定表存储引擎类型，如无特殊需求，一律为InnoDB。当需要使用除InnoDB/MyISAM/Memory以外的存储引擎时，必须通过DBA审核才能在生产环境中使用。

因为Innodb表支持事务、行锁、宕机恢复、MVCC等关系型数据库重要特性，为业界使用最多的MySQL存储引擎。而这是其他大多数存储引擎不具备的，因此首推InnoDB。

4.【强制】建表必须有comment

5.【建议】建表时关于主键：

(1)强制要求主键为id，类型为int或bigint，且为auto_increment

(2)标识表里每一行主体的字段不要设为主键，建议设为其他字段如user_id，order_id

等，并建立unique key索引（可参考cdb.teacher表设计）。

因为如果设为主键且主键值为随机插入，则会导致innodb部page分裂和大量随机I/O，性能下降。

6.【建议】核心表（如用户表，金钱相关的表）必须有行数据的创建时间字段create_time

和最后更新时间字段update_time，便于查问题。

7.【建议】表中所有字段必须都是NOT NULL属性，业务可以根据需要定义DEFAULT值。

因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。

8.【建议】建议对表里的blob、text等大字段，垂直拆分到其他表里，仅在需要读这些对

象的时候才去select。

9.【建议】反式设计：把经常需要join查询的字段，在其他表里冗余一份。如user_name

属性在user_account，user_login_log等表里冗余一份，减少join查询。

10.【强制】中间表用于保留中间结果集，名称必须以“tmp_”开头。

备份表用于备份或抓取源表快照，名称必须以“bak_”开头。

中间表和备份表定期清理。

11.【强制】对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰

期执行。

因为alter table会产生表锁，期间阻塞对于该表的所有写入，对于业务可能会产生极大影响。

3.2.3列数据类型优化

1.【建议】表中的自增列（auto_increment属性），推荐使用bigint类型。

因为无符号int存储围为-2147483648~2147483647（大约21亿左右），溢出后会导致报错。

2.【建议】业务中选择性很少的状态status、类型type等字段推荐使用tinytint或者

smallint类型节省存储空间。

3.【建议】业务中IP地址字段推荐使用int类型，不推荐用char(15)

因为int只占4字节，可以用如下函数相互转换，而char(15)占用至少15字节。一旦表数据行数到了1亿，那么要多用1.1G存储空间！

SQL：select inet_aton('192.168.2.12'); select inet_ntoa(3232236044);