《Mysql 5.7 数据库设计规范v1.0》由会员分享，可在线阅读，更多相关《Mysql 5.7 数据库设计规范v1.0（8页珍藏版）》请在金锄头文库上搜索。
1、Mysql 5.7 数据库设计规范一、 基础规范1.1 必须使用InnoDB存储引擎没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等，空间数据请使用Myisam存储引擎）的情况下，所有表必须使用Innodb存储引擎（mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb）Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。1.2 禁止使用关键字以及保留字1.3 必须使用utf-8字符集万国码兼容性更高，无需转码，无乱码风险，节省空间。1.4 数据表以及字段必须加入中文注释使用comment从句添加表和列的备注，为了避免随着时间的推移以及研发
2、人员的迭代后研发人员无从知晓字段的真正含义。1.5 禁止使用触发器以及event触发器与event可移植性差,占用数据库服务器资源，给数据库服务器造成压力，执行速度主要取决于数据库服务器的性能与触发器代码的复杂程度，嵌套调用一旦出现问题，排错困难，而且数据容易造成不一致，最重要的是后期维护极为不方便。业务层面的需求必须在业务层面解决，而不是把业务需求传递给数据库解决。1.6 禁止在数据库中存储图片文件等大的二进制数据通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时 通常存储于文件服务器，数据库只存储文件地址信息。1.7
3、 禁止在表中建立预留字段预留字段的命名很难做到见名识义 预留字段无法确认存储的数据类型，所以无法选择合适的类型 对预留字段类型的修改，会对表进行锁定。1.8 禁止在线上做数据库压力测试原则上禁止，具体原因没必要做过多的解释。1.9 禁止开发环境测试环境直连生产环境数据库1.10 禁止为程序使用的账户赋予super权限当达到最大连接数限制时，还运行1个有super权限的用户连接super权限只能留给DBA处理问题的账号使用1.11 对于程序连接数据库账号应遵循权限最小原则程序使用数据库账号只能在一个DB下使用不准跨库，程序使用的账号原则上不准有drop权限。二、 数据库命名规范2.1 数据库命名
4、采用26个英文字母以及下划线_组成，命名简洁明确(长度不能超过30个字符)且必须小写，建议使用如：dzzh_sso,dzzh_log,格式给数据库加个前缀。2.2 备份数据库命名采用26个英文字母以及下划线_组成以及日期部分组成，命名简洁明确(长度不能超过40个字符)且必须小写，建议使用如：dzzh_sso_20200204,dzzh_log_20200204。 三、 数据表设计规范33.1 命名规范采用26个英文字母且必须小写加上下划线_组成，命名简洁明确,多个单词用下划线_分隔，例如：user_login, user_profile, user_detail, user_role, use
5、r_role_relation，前缀user_可以有效的把相同关系或类型的表显示的聚合在一起。3.2 物理主键每个表必须有一个物理主键且必须为int类型自增长；主键递增，数据行写入可以 提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用；无主键的表删除，在row模式的主从架构，会导致备库夯住；业务层原则上禁止使用物理主键。3.3 逻辑主键建议每个表都有一个逻辑主键（无需设置为主键），类型为varchar(36)用于保存uuid数据，逻辑主键应用于具体业务数据唯一标识，为了避免对外暴露物理主键从而规避业务数据被不法推导造成数据泄露。3.4 数据表数据数量控制单表数据量的大小，建议
6、控制在500万以内，500万条记录并不是mysql数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小。3.5 数据表字段数量单张数据表字段不得超过64个，mysql限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节 减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO） 更有效的利用缓存，避免读入无用的冷数据经常一起使用的列放到一个表中（避免更多的关联操作）。3.6 分区表谨慎使用mysql分区表，分区表在物理上表现
7、为多个文件，在逻辑上表现为一个表 谨慎选择分区键，跨分区查询效率可能更低 建议采用物理分表的方式管理大数据。3.7 外键禁止使用外键约束，外键可用于保证数据的参照完整性，但建议在业务端实现因为外键会影响父表和子表的写操作从而降低性能。四、 数据表字段设计规范44.1 命名规则采用26个英文字母且必须小写或下划线_组成，命名简洁明确,多个单词用下划线_分隔，命名必须简明扼要。4.2 字段设计原则优先选择符合存储需要的最小的数据类型，列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的IO次数也就越多， 索引的性能也就越差。例如：能tin
8、yint类型绝对不使用smallint，能用smallint绝对不使用int，能用int绝对不使用bigint，能用varchar（255）绝对不用varchar（512）。4.3 字段类型4.3.1 不推荐使用text与blob类型会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能，特殊情况除外。4.3.2 禁止使用enum类型增加新的enum值要做DDL操作, enum的内部实际存储就是整数,请使用tinyint或smallint存储枚举值。4.3.3 时间类型存储使用timespan（4个字节）或datetime类型（8个字节）存储
9、时间，timespan占用4字节和INT相同，但比INT可读性高，超出timespan取值范围的使用datetime类型存储。禁止使用字符串存储日期型的数据：缺点1：无法用日期函数进行计算和比较缺点2：用字符串存储日期要占用更多的空间4.3.4 金额类型存储必须使用decimal类型（精确到小数点后8位），禁止使用float以及double存储金额。float,double为非精准浮点，计算时有可能丢失精度，decimal为精准浮点数，在计算时不会丢失精度，占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。定义数据类型为DE
10、CIMAL的列，请使用以下语法：column_name DECIMAL(P,D);在上面的语法中：P是表示有效数字数的精度。 P范围为165。D是表示小数点后的位数。 D的范围是030。MySQL要求D小于或等于(=)P。DECIMAL(P，D)表示列可以存储D位小数的P位数。十进制列的实际范围取决于精度和刻度。与INT数据类型一样，DECIMAL类型也具有UNSIGNED和ZEROFILL属性。 如果使用UNSIGNED属性，则DECIMAL UNSIGNED的列将不接受负值。五、 索引设计规范55.1 命名规范命名简洁明确,例如：user_login表user_name字段的索引应为use
11、r_name_index唯一索引。5.2 索引数量限制每张表索引数量建议不超过5个，禁止为表中的每一列都建立索引，索引并不是越多越好，索引可以提高效率同样可以降低效率。索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。因为mysql优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加mysql优化器生成执行计划的时间，同样会降低查询性能。5.3 联合索引（复合索引）请慎重使用联合索引5.3.1 需要加索引的字段要在where条件中5.3.2 避免建立冗余索引和重复
12、索引5.3.3 数据量少的字段不需要加索引5.3.4 最窄的字段放在键的左边5.3.5 where条件中or关系所有的or条件都必须是独立索引，否则加索引不起作用。5.3.6 最左匹配原则5.3.7 null值只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。六、 存储过程设计规范66.1 一般性原则不推荐使用存储过程，存储过程很好用但是阅读困难，在研发过程中应尽最大程度规避存储过程的使用尽量在应用程序层面解决。 6.2 命名规范采用26个英文字母且必须小写或下划线_组成，命名简洁明确,多个单词用下划线_分隔，命名必须简明扼
13、要且必须以proc_开头，例如：proc_update_activecode6.3 参数规范输入参数必须以i_开头，输出参数必须以o_开头。6.4 注释规范存储过程要去必须添加注释，注释内容须包含功能描述、作者、创建日期、修改日期、修改人、参数含义、存储过程引用关系说明、思路等必要信息，例如：/*功能：功能描述作者：乔晟创建日期：2020-02-26修改日期：2020-02-27 张三 添加了对名称唯一性的限制。修改日期：2020-02-28 李四 修改了张三导致的某BUG返回值：i_name具体含义o_count具体含义.调用到的存储过程：1、proc_check_username2、pro
14、c_get_pwd思路：1、首先判断名称是否为空2、其次.*/七、 视图设计规范77.1 一般性原则原则上不允许普通研发人员在研发任务中编写视图，至少需项目组长指导编写。7.2 命名规范采用26个英文字母且必须小写或下划线_组成，命名简洁明确,多个单词用下划线_分隔，命名必须简明扼要且必须以v_开头，关联到哪些表追加到尾部，例如：v_table1_table2八、 T-SQL编写规范88.1 避免数据类型的隐式转换隐式转换会导致索引失效。如：select name,phone from customer where phone= 131234567898.2 充分利用表上已经存在的索引8.3
15、使用left join或 not exists来优化not in操作因为not in 也通常会使用索引失效8.4 禁止使用SELECT *消耗更多的CPU和IO以网络带宽资源无法使用覆盖索引可减少表结构变更带来的影响。8.5 避免使用子查询，可以把子查询优化为join操作通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时，才可以把子查询转化为关联查询进行优化。子查询性能差的原因：子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大；由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。8.6 避免使用JOIN关联太多的表对于Mysql来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置。在Mysql中，对于同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果
