MySQL数据库设计规范
2021-05-29 v1.0.1
⽬录
1. 规范背景与⽬的
2. 设计规范
2.1 数据库设计
2.1.1 通⽤命名约定
2.1.2 库
2.1.3 表
2.1.4 字段名
2.1.5 字段数据类型优化
2.1.6 索引设计
2.1.7 分库分表、分区表
2.1.8 字符集
2.1.9 程序层 DAO 设计建议
2.1.10 ⼀个规范的建表语句⽰例
2.2 SQL 编写
2.2.1 DML 语句
2.2.2 多表连接
2.2.3 事务
2.2.4 排序和分组
2.2.5 线上禁⽌使⽤的 SQL 语句
3. MySQL 使⽤约束及建议
3.1 关于性能的争论
3.2. MySQL 特点
1. 规范背景与⽬的
MySQL 数据库与 Oracle、 SQL Server 等数据库相⽐，有其内核上的优势与劣势。我们在使⽤ MySQL 数据库的时候需要遵循⼀定规范，扬长避短。
本规范旨在帮助或指导技术⼈员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL 编写等⽅⾯予以规范，从⽽为业务系统稳定、健康地运⾏提供保障。
2. 设计规范
2.1 数据库设计
以下所有规范会按照【强制】、【建议】两个级别进⾏标注。
2.1.1 ⼀般命名规则
1. 【强制】使⽤⼩写，避免因⼤⼩写敏感⽽导致的错误。
2. 【强制】没有空格，使⽤下划线代替。
3. 【强制】名称中没有数字，只有英⽂字母，单词之间⽤下划线分隔。
4. 【强制】有效的可理解的名称，禁⽌拼⾳英⽂混⽤，库名最好与应⽤名称⼀致。
5. 【强制】名称应该是⾃我解释的。
6. 【强制】名称不应超过 32 个字符。
7. 【强制】使⽤前缀，便于业务分类。
2.1.2 库
1. 【强制】遵守以上全部⼀般命名规则。
2. 【强制】使⽤单数命名。
3. 【强制】库的名称格式：业务系统名称_⼦系统名。
4. 【强制】⼀般分库名称命名格式是 库通配名_编号，编号从 0 开始递增，⽐如 northwind_001，以时间进⾏分库的名称格式是库通配名_时间。
5. 【强制】开发环境库的名称增加后缀 _dev，测试环境增加后缀 _test。
6. 【强制】创建数据库时必须显式指定字符集，并且字符集只能是 utf8 或 utf8mb4，优先使⽤ utf8mb4。
2.1.3 表
1. 【强制】遵守以上全部⼀般命名规则。
2. 【强制】使⽤单数命名。
3. 【强制】表的名称格式：业务模块名称_表名。视图增加前缀 v_。
4. 【强制】每个表建议不超过 30-50 个字段。
5. 【强制】相关模块的表名与表名之间尽量体现 join 的关系，如 user 表和 user_login 表。
6. 【强制】创建表时必须显式指定字符集为 utf8 或 utf8mb4，优先使⽤ utf8mb4。
7. 【强制】创建表时必须显式指定表存储引擎类型，如⽆特殊需求，⼀律为 InnoDB。当需要使⽤除 InnoDB/MyISAM/Memory 以外的存储
引擎时，必须通过 DBA 审核才能在⽣产环境中使⽤。因为 InnoDB 表⽀持事务、⾏锁、宕机恢复、MVCC 等关系型数据库重要特性，为业界使⽤最多的 MySQL 存储引擎。⽽这是其它⼤多数存储引擎不具备的，因此⾸推 InnoDB。
8. 【强制】建表必须有 comment。
9. 【建议】关于主键：每个表必须有主键。(1) 类型为 bigint，使⽤ snowflake 雪花算法⽣成，禁⽌使⽤⾃增数值主键；(2) 命名格式是 表名
_id ，例如 user_id，order_id，禁⽌使⽤ id 命名，多表关联时容易产⽣混淆。
10. 【建议】核⼼表（如⽤户表，⾦钱相关的表）必须有⾏数据的创建⼈、创建时间、修改⼈、修改时间字段
create_userid、create_time、update_userid、update_time，便于排查问题。
11. 【建议】表中所有字段必须都是 NOT NULL 属性，业务可以根据需要定义 DEFAULT 值。因为使⽤ NULL 值会存在每⼀⾏都会占⽤额外存储
空间、数据迁移容易出错、聚合函数计算结果偏差等问题。
12. 【建议】建议对表⾥的 blob、text 等⼤字段，垂直拆分到其它表⾥，仅在需要读这些对象的时候才去 select。
13. 【建议】反范式设计：把经常需要 join 查询的字段，在其它表⾥冗余⼀份。如 username 属性在 user_account，user_login_log 等表⾥冗余
⼀份，减少 join 查询。
14. 【强制】中间表⽤于保留中间结果集，名称必须以 tmp_ 开头。备份表⽤于备份或抓取源表快照，名称必须以 bak_ 开头。中间表和备份表定
期清理。
15. 【强制】对于超过 100 万⾏的⼤表进⾏ alter table，必须经过 DBA 审核，并在业务低峰期执⾏。因为 alter table 会产⽣表锁，期间阻塞对于
该表的所有写⼊，对于业务可能会产⽣极⼤影响。
16. 【建议】尽量控制单表数据量的⼤⼩，建议控制在 500 万以内。500 万并不是 MySQL 数据库的限制，数据量过⼤会对修改表结构，数据备
份、恢复产⽣影响。
17. 【强制】禁⽌使⽤存储过程、触发器、Event。
18. 【建议】建议不使⽤视图。
19. 【强制】禁⽌嵌套视图。
20. 【强制】禁⽌存储⼤⽂件或者⼤照⽚。
2.1.4 字段
1. 【强制】遵守以上全部⼀般命名规则。
2. 【建议】尽可能选择短的或⼀两个单词。
3. 【强制】避免使⽤保留字作为字段名称：order，date，name 是数据库的保留字，避免使⽤它。可以为这些名称添加前缀使其易于理解，如
user_name，signup_date 等。
4. 【强制】避免使⽤与表名相同的字段名，这会在编写查询时造成混淆。
5. 【建议】字符集和库级保持⼀致。不单独定义字段字符集。
6. 【建议】反范式设计：为了提⾼数据更新性能，禁⽌使⽤外键。如果有外键完整性约束，需要应⽤程序控制。外键会导致表与表之间耦
合，update 与 delete 操作都会涉及相关联的表，⼗分影响性能，甚⾄会造成死锁。⾼并发情况下容易造成数据库性能，⼤数据⾼并发业务场景数据库使⽤以性能优先。
7. 【强制】严禁在数据库中明⽂存储⽤户密码、⾝份证、信⽤卡号（信⽤卡PIN码）等核⼼机密数据。
8. 【强制】⼀个字段仅允许表达⼀种业务含义，例如通过员⼯职级反映薪资等级是不合适的。
9. 【强制】多表中的相同列，必须保证列名⼀致，数据类型⼀致。
2.1.5 字段数据类型优化
1. 【建议】⽂本数据尽量⽤ varchar 存储。因为 varchar 是变长存储，⽐ char 更省空间。字符数不要超
过 2700。
2. 【建议】时间类型尽量选取 datetime 。
3. 【建议】业务中选择性很少的状态 status、类型 type 等字段推荐使⽤ smallint 类型节省存储空间。
4. 【建议】⾦额货币科学计数建议采⽤ decimal 数据类型。
5. 【建议】禁⽌使⽤数据库私有数据类型，例如 enum，set，不利于数据库迁移。
详细存储⼤⼩参考下图：
类型（同义词）存储长度
(BYTES)
最⼩值
(SIGNED/UNSIGNED)
最⼤值(SIGNED/UNSIGNED)
整形数字
TINYINT1-128/0127/255
SMALLINT2-32,768/032767/65,535 MEDIUMINT3-8,388,608/08388607/16,777,215/
INT(INTEGER)4-2,14,7483,648/021********/4,294,967,29 5/
BIGINT8-2^63/0264-1⼩数⽀持
FLOAT[(M[,D])]  4 or 8-
63-1/2
DOUBLE[(M[,D])]
(REAL, DOUBLE PRECISION)
8
-时间类型
DATETIME 81001-01-01 00:00:009999-12-31 23:59:59DATE 31001-01-019999-12-31TIME 300:00:0023:59:59YEAR 11001
9999
TIMESTAMP
4
1970-01-01 00:00:00
类型（同义词）存储长度(BYTES)最⼩值
(SIGNED/UNSIGNED)
最⼤值(SIGNED/UNSIGNED)
2.1.6 索引设计
1. 【强制】主键类型为 int/bigint ，且主键值禁⽌被更新。
2. 【建议】主键索引的名称以 pk_ 开头，唯⼀键以 uk_ 开头，普通索引以 idx_ 开头，⼀律使⽤⼩写格式，以 表名_字段的名称或缩写 作为后缀。
3. 【强制】InnoDB 和 MyISAM 存储引擎表，索引类型必须为 BTREE ；MEMORY 表可以根据需要选择 HASH  或者 BTREE  类型索引。
4. 【强制】单个索引中每个索引记录的长度不能超过 64KB。
5. 【建议】单个表上的索引个数不能超过 5 个。
6. 【建议】在建⽴索引时，多考虑建⽴联合索引，并把区分度最⾼的字段放在最前⾯。如列 user_id  的区分度可由 select count(distinct user_id) 计算出来。
7. 【建议】在多表 join 的 SQL ⾥，保证被驱动表的连接列上有索引，这样 join 执⾏效率最⾼。
8. 【建议】建表或加索引时，保证表⾥互相不存在冗余索引。如果表⾥已经存在 key(a, b)，则 key(a) 为冗余索引，需要删除。9. 【建议】如果选择性超过 20%，那么全表扫描⽐使⽤索引性能更优，即没有设置索引的必要。
2.1.7 分库分表、分区表
mysql视图和存储过程
1. 【强制】分区表的分区字段（partition-key ）必须有索引，或者是组合索引的⾸列。
2. 【强制】单个分区表中的分区（包括⼦分区）个数不能超过 1024。
3. 【强制】上线前 DBA 必须指定分区表的创建、清理策略。
4. 【强制】访问分区表的 SQL 必须包含分区键。
5. 【建议】单个分区⽂件不超过 2G，总⼤⼩不超过 50G。建议总分区数不超过 20 个。
6. 【强制】对于分区表执⾏ alter table  操作，必须在业务低峰期执⾏。
7. 【强制】采⽤分库策略的，库的数量不能超过 1024。
8. 【强制】采⽤分表策略的，表的数量不能超过 4096。
9. 【建议】单个分表不超过 500 万⾏，ibd ⽂件⼤⼩不超过 2G，这样才能让数据分布式变得性能更佳。10.【建议】⽔平分表尽量⽤取模⽅式，⽇志、报表类数据建议采⽤⽇期进⾏分表。
2.1.8 字符集
1. 【强制】数据库本⾝库、表、列所有字符集必须保持⼀致，为 utf8 或 utf8mb4，优先使⽤ utf8mb4。
2. 【强制】前端程序字符集或者环境变量中的字符集，与数据库、表的字符集必须⼀致，统⼀为 utf8。
2.1.9 程序层 DAO 设计建议

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。