解读 | ||
1、基础规范 | 必须使用InnoDB存储引擎 | 解读:支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高 |
必须使用UTF 8mb4字符集 | 解读:统一字符集,万国码,无需转码,无乱码风险,节省空间,创建库和表字符集和排序规则定义为DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci | |
必须为数据表、数据字段加入中文注释 | 除主键外的其它字段都有需要增加注释。请使用英文标点,避免出现乱码 | |
禁止使用存储过程、试图、触发器、Event | 高并发大数据的互联网业务,架构设计思路是“解放数据库CPU,将计算转移到服务层”,并发量大的情况下,这些功能可能将数据库拖死,业务逻辑放到服务层具备更好的扩展性,能够轻易实现“增机器就能加性能”。数据库擅长存储和索引,CPU计算还是上移吧 | |
禁止存储大文件或大照片 | 为何要让数据库做它不擅长的事情?大文件和照片存储在文件系统,数据库存url多好 | |
禁止使用分区表 | 分区表在物理上表现多个文件,在逻辑上表现为一个表,跨分区查询效率可能很低,建议采用物理分表的方式管理大数据。 | |
禁止在数据库中存储明文密码 | 解读: 防止被拖库 包括不能明文存储手机号、银行卡号、QQ、邮箱等会员个人信息 | |
二、命名规范 | 库名、表名、字段名:小写,下划线风格,不超过32个字符;必须见名知意,禁止拼音英文混用 | 另外: 库名、表名、字段名禁止使用MySQL保留关键字(如 desc、range、match、delayed 等,请参考 【MySQL官方保留字】) |
非唯一索引名idx_xxx,唯一索引名uniq_xxx | ||
三、表设计规范 | 单实例表数目必须小于500 | |
单表列数目必须小于50 | 超过50个字段的表考虑拆表 | |
必须有一个与业务无关的自增主键(5.7版本) 8.0版本中开发可以使用id生成器生成全局唯一主键或者使用自增主键. | 解读: 示例: | |
禁止在表中建立预留字段 | 解读: (除非有非常明确的目的) | |
禁止使用外键,如果有外键完整性约束,需要应用程序控制 | 解读:外键会导致表与表之间耦合,update与delete操作都会涉及相关联的表,十分影响sql 的性能,甚至会造成死锁。高并发情况下容易造成数据库性能,大数据高并发业务场景数据库使用以性能优先 | |
四、字段设计规范 | 必须把字段定义为NOT NULL并且提供默认值(text类型除外) | 解读: 示例: |
禁止使用BLOB类型 | 解读: 禁止给超过十万行的大表建立TEXT类型. 大表如果要建立TEXT类型必须要拆表,然后用主键和主表关联 | |
金额类数据必须使用 decimal 类型 | 解读:浮点类型容易导致钱对不上 (Decimal类型为精准浮点数,在计算时不会丢失精度) | |
必须使用varchar(20)存储手机号 | 解读: 示例: | |
时间字段设计规范 | a) 禁止往mysql时间类型的字段中传 '0000-00-00 00:00:00' ,'0000-00-00' 这种值 c) '创建时间',强制使用: `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' | |
必须使用尽可能小的VARCHAR字段,能用varchar(20)的就不用varchar(255) | 解读: 合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。 | |
禁止使用ENUM,可使用TINYINT代替 | 解读: a)增加新的ENUM值要做DDL操作 b)ENUM的内部实际存储就是整数,你以为自己定义的是字符串? | |
五、索引设计规范 | 单表索引控制在5个以内 | 解读: 更新会变更B+树,索引过多会降低数据库性能 |
单表唯一索引的数量必须小于3个 | ||
组合索引字段数不允许超过5个 | 解读:字段超过5个时,实际已经起不到有效过滤数据的作用了 | |
禁止创建冗余索引 | 解读: a) 主键也是一个索引(效率最高), 本身还有唯一性约束 b) 联合索引 inx_a_b_c(a,b,c) ; 相当于创建了 idx(a), idx(a,b), idx(abc) | |
禁止对过长的varchar字段创建索引 | 解读: 对长字符串应该考虑使用前缀索引,前缀索引长度不超过8个字符。 不要对过长的VARCHAR字段建立索引。优先考虑前缀索引。或添加CRC32 CRC64或MD5伪列并建立索引。例:SELECT * FROM urls WHERE url = 'http://wwww.shopperplus.com'; 建立crc_url伪列,程序使用时对url取其crc32值,然后select * from urls where crc_url=1215054814 | |
禁止在更新十分频繁、区分度不高的属性上建立索引 | 解读: a)更新会变更B+树,更新频繁的字段建立索引会大大降低数据库性能 b)'性别' 这种区分度不大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似 | |
建立组合索引,必须把区分度高的字段放在前面 | 解读:能够更加有效的过滤数据 区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0 | |
双活数据校验需求 | 如果updated_at没有索引需要添加 KEY `idx_updated_at` (`updated_at`) ,有联合索引第一位可以不加 | |
六、SQL使用规范 | 禁止使用SELECT *,只获取必要的字段,需要显示说明列属性 | 解读: a)读取不需要的列会增加CPU、IO、NET消耗 b)不能有效的利用覆盖索引 c)使用SELECT * 容易在增加或者删除字段后出现程序BUG |
禁止使用INSERT INTO t_xxx VALUES(xxx),必须显式指定插入的列属性 | 解读:容易在增加或者删除字段后出现程序BUG 正确写法:INSERT INTO t_xxx(col1, col2, col3) VALUES('a','b','c') 错误写法:INSERT INTO t_xxx VALUES('a','b','c') | |
禁止使用属性隐式转换 | 解读:对索引字段做函数操作或者计算时,优化器会放弃使用索引. 例如: 不走索引的原因 | |
禁止在WHERE条件的属性上使用函数或者表达式 | 解读:索引树中存储的是列的实际值和主键值 例如: 正确的写法是:SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00') | |
减少负向查询,以及%开头的模糊查询 | 解读: a)负向查询条件:NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等,会导致全表扫描 《类比字典》 b)%开头的模糊查询,会导致全表扫描 《类比组合索引》 | |
禁止使用子查询 | 解读:会产生临时表,消耗较多内存与CPU,极大影响数据库性能 可以把子查询优化为join操作, 禁止超过3个表以上的join查询 | |
禁止在 update 时将逗号 ”,” 写成 ”and”,非常非常非常危险 | 正确示例:update table set uid=uid+1000,gid=gid+100 where id<=2; 错误示例:update table set uid=uid+1000 and gid=gid+1000 where id<=2; 此时uid=uid+1000 and gid=gid+1000将作为值(1或0)赋值给uid,并且无warning! | |
建议拆分复杂的大SQL为多个小SQL | 1、大SQL:逻辑上比较复杂,需要占用大量CPU进行计算的SQL 2、禁止在主库上执行后台管理和统计类的功能查询,这种复杂类的SQL会造成CPU的升高,进而会影响业务。 | |
禁止使用order by rand() | 解读 推荐在程序中获取一个随机值,然后从数据库中获取数据的方式 | |
七、行为规范 | 流程工单SQL禁止使用变量 | 例如: select @parentId := LAST_INSERT_ID() 解读: 执行后无法生成回滚语句,请提供确切得数值 |
禁止有super权限的应用账号存在 | 安全第一,super权限会导致很多诡异问题且难以进行追踪 | |
禁止有DDL、DCL权限的应用账号存在 | ||
重大项目的数据库方案选型和设计必须提前通知DBA | ||
批量导入、导出数据必须通过DBA审核,并在执行过程中观注监控 | ||
批量更新数据,如DELETE、UPDATE操作,必须DBA审核,并在执行过程中观注监控 | ||
产品出现非数据库导致故障时,如被攻击,必须及时通知DBA,便于维护服务稳定 | ||
业务部门程序出现bug等影响数据库服务的问题,必须及时通知DBA,便于维护服务稳定 | ||
业务部门推广活动或上线新功能,必须提前通知DBA进行服务和访问量评估,并留出必要时间以便DBA完成扩容 | ||
出现业务部门人为误操作导致数据丢失,需要恢复数据的,必须第一时间通知DBA,并提供准确时间点、误操作语句等重要线索 | ||
不要在数据库中存放业务逻辑 | 数据库是有状态的服务,变更复杂而且速度慢,如果把业务逻辑放到数据库中,将会限制业务的快速发展。建议把业务逻辑提前放到前端或者中间逻辑层,数据库仅仅作为存储层,实现逻辑与存储分离 |
开发相关数据库规范
于 2022-08-14 20:59:13 首次发布