数据库设计标准规范.docxVIP

MySQL数据库设计规范  目录 TOC \o 1-4 \h \z \u 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 规范背景和目标 MySQL数据库和oracle、sqlserver等数据库相比，有其内核上优势和劣势。我们在使用MySQL数据库时候需要遵照一定规范，扬长避短。本规范意在帮助或指导RD、QA、OP等技术人员做出适合线上业务数据库设计。在数据库变更和处理步骤、数据库表设计、SQL编写等方面给予规范，从而为企业业务系统稳定、健康地运行提供保障。 设计规范 3.1 数据库设计 以下全部规范会根据【高危】、【强制】、【提议】三个等级进行标注，遵守优先级从高到低。 对于不满足【高危】和【强制】两个等级设计，DBA会强制打回要求修改。 3.2.1 库名 1. 【强制】库名称必需控制在32个字符以内，相关模块表名和表名之间尽可能提现join关系，如user表和user_login表。 2. 【强制】库名称格式：业务系统名称_子系统名，同一模块使用表名尽可能使用统一前缀。 3. 【强制】通常分库名称命名格式是“库通配名_编号”，编号从“0”开始递增，比如“wenda_001” 以时间进行分库名称格式是“库通配名_时间” 【强制】创建数据库时必需显式指定字符集，而且字符集只能是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。 【强制】建表必需有comment 【提议】建表时相关主键： (1)强制要求主键为id，类型为int或bigint，且为auto_increment (2)标识表里每一行主体字段不要设为主键，提议设为其它字段如user_id，order_id等，并建立unique key索引（可参考cdb.teacher表设计）。 因为假如设为主键且主键值为随机插入，则会造成innodb内部page分裂和大量随机I/O，性能下降。 【提议】关键表（如用户表，金钱相关表）必需有行数据创建时间字段create_time和最终更新时间字段update_time，便于查问题。 【提议】表中全部字段必需全部是NOT NULL属性，业务能够依据需要定义DEFAULT值。 因为使用NULL值会存在每一行全部会占用额外存放空间、数据迁移轻易犯错、聚合函数计算结果偏差等问题。 【提议】提议对表里blob、text等大字段，垂直拆分到其它表里，仅在需要读这些对象时候才去select。 【提议】反范式设计：把常常需要join查询字段，在其它表里冗余一份。如user_name属性在user_account，user_login_log等表里冗余一份，降低join查询。 【强制】中间表用于保留中间结果集，名称必需以“tmp_”开头。 备份表用于备份或抓取源表快照，名称必需以“bak_”开头。 中间表和备份表定时清理。 【强制】对于超出100W行大表进行alter table，必需经过DBA审核，并在业务低峰期实施。 因为alter table会产生表锁，期间阻塞对于该表全部写入，对于业务可能会产生极大影响。 列数据类型优化 【提议】表中自增列（auto_increment属性），推荐使用bigint类型。 因为无符号int存放范围为-~（大约21亿左右），溢出后会造成报错