专业级的MySQL开发设计规范及SQL编写规范

 更新时间:2020年11月16日 13:48  
这篇文章主要介绍了专业级的MySQL开发设计规范及SQL编写规范,需要的朋友可以参考下

在团队开发过程中为了项目的稳定,代码的高效,管理的便捷制定内部种开发设计规范是必不可少的,

这里分享一份我们定义MySQL开发设计规范包括表设计规范,字段设计规范,SQL编写规范

数据库对象命名规范

数据库对象

命名规范的对象是指数据库SCHEMA、表TABLE、索引INDEX、约束CONSTRAINTS等的命名约定

数据库对象命名原则

命名使用具有意义的英文词汇,词汇中间以下划线分隔

命名只能使用英文字母、数字、下划线

避免用MySQL的保留字如:call、group等

所有数据库对象使用小写字母

数据库命名规范

数据库名不能超过30个字符

数据库命名必须为项目英文名称或有意义的简写

数据库创建时必须添加默认字符集和校对规则子句。默认字符集为UTF8(已迁移dumbo的使用utf8mb4)

命名应使用小写

表命名规范

同一个模块的表尽可能使用相同的前缀,表名称尽可能表达含义

多个单词以下划线(_)分隔

表名不能超过30个字符

普通表名以t_开头,表示为table,命名规则为t_模块名(或有意义的简写)_+table_name

临时表(运营、开发或数据库人员临时用作临时进行数据采集用的中间表)命名规则:加上tmp前缀和8位时间后缀(tmp_test_user_20181109)

备份表(DBA备份用作保存历史数据的中间表)命名规则:加上bak前缀和8位时间后缀(bak_test_user_20181109)

命名应使用小写

字段命名规范

字段命名需要表示其实际含义的英文单词或简写,单词之间用下划线(_)进行连接

各表之间相同意义的字段必须同名

字段名不能超过30个字符

用户命名规范

生产使用的用户命名格式为 code_应用

只读用户命名规则为 read_应用

数据库对象设计规范

存储引擎的选择

如无特殊需求,必须使用innodb存储引擎

字符集的选择

如无特殊要求,必须使用utf8或utf8mb4

表设计规范

不同应用间所对应的数据库表之间的关联应尽可能减少,不允许使用外键对表之间进行关联,确保组件对应的表之间的独立性,为系统或表结构的重构提供可能性

表设计的角度不应该针对整个系统进行数据库设计,而应该根据系统架构中组件划分,针对每个组件所处理的业务进行数据库设计

表必须要有PK

一个字段只表示一个含义

表不应该有重复列

禁止使用复杂数据类型(数组,自定义等)

需要join的字段(连接键),数据类型必须保持绝对一致,避免隐式转换

设计应至少满足第三范式,尽量减少数据冗余。一些特殊场景允许反范式化设计,但在项目评审时需要对冗余字段的设计给出解释

TEXT字段必须放在独立的表中,用PK与主表关联。如无特殊需要,禁止使用TEXT、BLOB字段

需要定期删除(或者转移)过期数据的表,通过分表解决

单表字段数不要太多,建议最多不要大于50个

MySQL在处理大表时,性能就开始明显降低,所以建议单表物理大小限制在16GB,表中数据控制在2000W内

如果数据量或数据增长在前期规划时就较大,那么在设计评审时就应加入分表策略

无特殊需求,严禁使用分区表

字段设计规范

INT:如无特殊需要,存放整型数字使用UNSIGNED INT型。整型字段后的数字代表显示长度

DATETIME:所有需要精确到时间(时分秒)的字段均使用DATETIME,不要使用TIMESTAMP类型

VARCHAR:所有动态长度字符串 全部使用VARCHAR类型,类似于状态等有限类别的字段,也使用可以比较明显表示出实际意义的字符串,而不应该使用INT之类的数字来代替;VARCHAR(N),N表示的是字符数而不是字节数。比如VARCHAR(255),可以最大可存储255个字符(字符包括英文字母,汉字,特殊字符等)。但N应尽可能小,因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节,且存储字符个数由所选字符集决定。如UTF8存储一个字符最大要3个字节,那么varchar在存放占用3个字节长度的字符时不应超过21845个字符。同时,在进行排序和创建临时表一类的内存操作时,会使用N的长度申请内存。(如无特殊需要,原则上单个varchar型字段不允许超过255个字符)

TEXT:仅仅当字符数量可能超过20000个的时候,才可以使用TEXT类型来存放字符类数据,因为所有MySQL数据库都会使用UTF8字符集。所有使用TEXT类型的字段必须和原表进行分拆,与原表主键单独组成另外一个表进行存放。如无特殊需要,严禁开发人员使用MEDIUMTEXT、TEXT、LONGTEXT类型

对于精确浮点型数据存储,需要使用DECIMAL,严禁使用FLOAT和DOUBLE

如无特殊需要,严禁开发人员使用BLOB类型

如无特殊需要,字段建议使用NOT NULL属性,可用默认值代替NULL

自增字段类型必须是整型且必须为UNSIGNED,推荐类型为INT或BIGINT,并且自增字段必须是主键或者主键的一部分

索引设计规范

索引必须创建在索引选择性选择性较高的列上,选择性的计算方式为: select count(distinct(col_name))/count(*) from tb_name;如果结果小于0.2,则不建议在此列上创建索引,否则大概率会拖慢SQL执行

组合索引的首字段,必须在where条件中,对于确定需要组成组合索引的多个字段,建议将选择性高的字段靠前放

禁止使用外键

Text类型字段如果需要创建索引,必须使用前缀索引

单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表,应尽量少建索引

ORDER BY,GROUP BY,DISTINCT的字段需要添加在索引的后面,形成覆盖索引

尽量使用Btree索引,不要使用其它类型索引

约束设计规范

PK应该是有序并且无意义的,尽量由开发人员自定义,且尽可能短,使用自增序列。

表中除PK以外,还存在唯一性约束的,可以在数据库中创建以“uidx_”作为前缀的唯一约束索引。

PK字段不允许更新。

禁止创建外键约束,外键约束由应用控制。

如无特殊需要,所有字段必须添加非空约束,即not null

如无特殊需要,所有字段必须有默认值。

SQL编写规范

尽量避免使用select *,join语句使用select *可能导致只需要访问索引即可完成的查询需要回表取数

严禁使用select * from table而不加任何where条件

MySQL中的text类型字段存储的时候不是和由其他普通字段类型的字段组成的记录存放在一起,而且读取效率本身也不如普通字段块。如果不需要取回text字段,又使用了select *,会让完成相同功能的sql所消耗的io量大很多,而且增加部分的io效率也更低下

在取出字段上可以使用相关函数,但应尽可能避免出现now(),rand(),sysdate(),current_user()等不确定结果的函数,在Where条件中的过滤条件字段上严禁使用任何函数,包括数据类型转换函数

所有连接的SQL必须使用Join ... On ...方式进行连接,而不允许直接通过普通的Where条件关联方式。外连接的SQL语句,可以使用Left Join On的Join方式,且所有外连接一律写成Left Join,而不要使用Right Join

分页查询语句全部都需要带有排序条件,除非应用方明确要求不要使用任何排序来随机展示数据

WHERE条件中严禁在索引列上进行数学运算或函数运算

in()/union替换or,并注意in的个数小于300

严禁使用%前缀进行模糊前缀查询:如:select id,val from table where val like ‘%name';可以使用%模糊后缀查询如:select id,val from table where val like ‘name%'

严禁使用INSERT ON DUPLICATE KEY UPDATEREPLACE INTOINSERT IGNORE

本文做个抛砖引玉,每个团队都有自己的开发设计规范,Mysql开发设计规范不单单只有这些,希望本文对您有所启发

相关文章

  • MySQL系列之十四 MySQL的高可用实现

    这篇文章主要介绍了MySQL系列之十四 MySQL的高可用实现,从工作原理到具体的技术实现,本文详细的讲述了该项技术,以下就是详细内容,需要的朋友可以参考下...2021-07-03
  • MySQL 8.0 Online DDL快速加列的相关总结

    在实际的MySQL运维过程中,我们经常会遇到业务需要给某张表添加字段的情况,本文将介绍几种加字段的方法,感兴趣的朋友可以参考下...2021-06-01
  • mysql外连接与内连接查询的不同之处

    在关系型数据库中,我们经常是把数据存储到多个相互关联的表中,这些相互关联的表通过指定的列发生联系,下面这篇文章主要给大家介绍了关于mysql外连接与内连接查询的不同之处,需要的朋友可以参考下...2021-06-03
  • 安装配置mysql及Navicat prenium的详细流程

    这篇文章主要介绍了安装配置mysql及Navicat Premium的详细流程,配置方法也真的很简单,本文给大家详细介绍mysql Navicat Premium安装配置相关知识感兴趣的朋友,一起学习吧...2021-06-10
  • MySQL中存储时间的最佳实践指南

    这篇文章主要给大家介绍了关于MySQL中存储时间的最佳实践,文中详细介绍了哪种存储时间的方式更好,对大家学习或者使用mysql具有一定的参考学习价值,需要的朋友可以参考下...2021-07-01
  • 浅谈MySQL之浅入深出页原理

    首先,我们需要知道,页(Pages)是InnoDB中管理数据的最小单元。Buffer Pool中存的就是一页一页的数据。当我们要查询的数据不在Buffer Pool中时,InnoDB会将记录所在的页整个加载到Buffer Pool中去;同样,将Buffer Pool中的脏页刷入磁盘时,也是按照页为单位刷入磁盘的...2021-06-24
  • mysql 带多个条件的查询方式

    这篇文章主要介绍了mysql 带多个条件的查询方式,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教...2021-06-04
  • 解决Navicat for Mysql连接报错1251的问题(连接失败)

    记得在之前给大家介绍过Navicat for Mysql连接报错的问题,可能写的不够详细,今天在稍作修改补充下,对Navicat for Mysql连接报错1251问题感兴趣的朋友跟随小编一起看看吧...2021-05-27
  • 浅谈mysql执行过程以及顺序

    这篇文章主要介绍了浅谈mysql执行过程以及顺序,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2021-05-12
  • mysql 8.0.25 解压版安装配置方法图文教程

    这篇文章主要为大家详细介绍了mysql 8.0.25 解压版安装配置方法图文教程,文中安装步骤介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2021-07-07
  • Unity连接MySQL并读取表格数据的实现代码

    本文给大家介绍Unity连接MySQL并读取表格数据的实现代码,实例化的同时调用MySqlConnection,传入参数,这里的传入参数个人认为是CMD里面的直接输入了,string格式直接类似手敲到cmd里面,完整代码参考下本文...2021-06-20
  • MySQL系列之十 MySQL事务隔离实现并发控制

    今天的内容就和大家聊一聊MySQL数据库中关于MySQL事务隔离实现并发控制的问题,主要是基于锁实现控制技术...2021-07-03
  • 浅析MySQL如何实现事务隔离

    使用过关系型数据库的,应该都事务的概念有所了解,知道事务有 ACID 四个基本属性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability),今天我们主要来理解一下事务的隔离性...2021-06-26
  • MySQL定时全库备份数据库

    数据备份真的很重要, 因为可能有一天数据会被莫名其妙的删掉了,本文主要介绍了MySQL定时备份数据库,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2021-07-03
  • Navicat连接MySQL错误描述分析

    最近遇到了一件非常棘手的问题,用Navicat连接MySQL总是出错,网上查阅了一下原因,最终找到解决方案,好吧,下面我就来回忆一下自己怎么处理这问题的,分享到脚本之家平台需要的朋友参考下吧...2021-06-02
  • 详解MySQL 联合查询优化机制

    MySQL 使用联合的形式的地方会远远超过我们过去认知的范畴。基本上,它会认为每个查询都有联合,而不仅仅是从两张表中查出匹配的数据行,这包括了子查询,甚至仅仅对单表的 SELECT 操作。因此,理解 MySQL 如何执行联合十分重要。...2021-05-10
  • 深入探究Mysql模糊查询是否区分大小写

    这篇文章主要给大家介绍了关于Mysql模糊查询是否区分大小写的相关资料,文中给出了5种解决方法以及各个方法的建议,需要的朋友可以参考下...2021-06-11
  • MYSQL数据库基础之Join操作原理

    这篇文章主要给大家介绍了关于MYSQL数据库基础之Join操作原理的相关资料,连接(join)查询是将两个查询的结果以“横向对接”的方式合并起来的结果,需要的朋友可以参考下...2021-07-05
  • 详细谈谈MYSQL中的COLLATE是什么

    mysql COLLATE是校对集的意思,可以理解为,排序规则等,下面这篇文章详细的给大家介绍了关于MYSQL中COLLATE是什么的相关资料,需要的朋友可以参考下...2021-06-11
  • IDEA 链接Mysql数据库并执行查询操作的完整代码

    这篇文章主要介绍了IDEA 链接Mysql数据库并执行查询操作的完整代码,代码不难,详细大家看完本文肯定有意向不到的收获,感兴趣的朋友跟随小编一起看看吧...2021-05-20