一、数据库对象命名约定1、数据库对象数据库对象是数据库的组成部分。常见的有：表（Table）、索引（Index）、视图（View）、图表（Diagram）、默认值（Default）、规则（Rule）、触发器（Trigger）、存储过程（StoredProcedure）、用户（User）等命名约定是指数据库（SCHEMA）、表（TABLE）、索引（INDEX）、约束（CONSTRAINTS）等数据库对象的命名约定。2.数据库的全局命名约定objects1)使用有意义的英文词汇命名，词汇中间用下划线分隔。2)名称只能使用英文字母、数字和下划线，以英文字母开头。3）避免使用MySQL保留字如：backup、call、group等。4）所有数据库对象使用小写字母。其实MySQL是可以设置是否区分大小写的。为了保证统一性，我们在这里对所有的小写表达式进行了标准化。3.数据库命名规则1)数据库命名不能超过30个字符。2）数据库名一般是项目名+一个代表库含义的缩写。例如IM项目的工作流数据库可以是im_flow。3)必须在创建数据库时添加默认字符集和排序规则子句。默认字符集是UTF8（Utf8mb4用于迁移的dumbo）。4)名称应小写。4.表命名规范1）常规表名以t_开头，t代表表，命名规则为t+module（简写包含模块的意思）+table（简写包含表的意思），如作为教育用户模块的信息表：t_user_eduinfo。2）临时表（RD、QA或DBA同学用于临时数据处理），命名规则：temp前缀+module+table+date后缀：temp_user_eduinfo_20210719。3）备份表（用于保存归档历史数据或作为备份恢复的灾难数据）命名规则，bak前缀+模块+表+日期后缀：bak_user_eduinfo_20210719。4）同一模块的表尽量使用相同的前缀，表名尽量表意。5)多个单词用下划线_隔开。6)正则表名不超过30个字符。temp表和bak表视情况而定，越短越好。名称应为小写。5.字段命名规范1)字段命名需用英文单词或其实际含义的缩写表示，单词之间用下划线_连接，如service_ip、service_port。2）表之间含义相同的字段必须重名。比如表a和表b都有创建时间，应该统一为create_time。不一致会造成混乱。3)多个单词用下划线_隔开。4)字段名不超过30个字符，名称需小写。6.索引命名约定1)唯一索引使用uni+字段名命名：createuniqueindexuni_uidont_user_basic(uid)。2）非唯一索引使用idx+字段名命名：createindexidx_uname_mobileont_user_basic(uname,mobile)。3)多个单词用下划线_隔开。4）索引名称尽量不要超过50个字符，名称要小写。组合索引的字段不宜过多，否则不利于查询效率的提高。5）对于由多个单词组成的列名，尽量使用代表意思的缩写，比如test_contact表的member_id和friend_id上的组合索引：idx_mid_fid。6）理解组合索引最左前缀的原则，避免重复建索引。如果(a,b,c)成立，则相当于成立(a),(a,b),(a,b,c)。7、视图命名约定1）视图名称以v开头，表示视图，完整结构为v+视图内容含义的缩写。2）如果视图只来自单表，则为v+表名。如果视图是由几个表关联生成的，用v+下划线（_）连接几个表名，视图名不超过30个字符。如果超过30个字符，将被缩写。3）如无特殊需要，严禁开发者创建视图。4)名称应小写。8、存储过程命名规范1）存储过程名称以sp开头，表示一个存储过程（storageprocedure）。之后，多个单词用下划线（_）连接。存储过程名称应反映其功能。存储过程名称不应超过30个字符。2）存储过程中输入参数以i_开头，输出参数以o_开头。3)名称应小写。createproceduresp_multi_param(ini_idbigint,ini_namevarchar(32),outo_memovarchar(100))9、函数命名约定1）函数名以func开头，意思是函数。之后，多个单词用下划线（_）连接，函数名要体现其功能。函数名称不应超过30个字符。2)名称应小写。createfunctionfunc_format_date(ctimedatetime)10.触发器命名约定1）触发器以trig开头，表示trigger触发器。2）基础部分描述了触发器添加的表，触发器名称不超过30个字符。3）后缀（_i、_u、_d）表示触发条件的触发方式（插入、更新或删除）。4)名称应小写。如果存在则删除触发器trig_attach_log_d；在删除每一行的t_dept后创建触发器trig_attach_log_d；11、约束命名约定1）唯一约束：uk_表名_字段名。uk是UNIQUEKEY的缩写。例如，给一个部门的部门名称添加唯一约束，保证没有重名，如下：ALTERTABLEt_deptADDCONSTRAINTun_nameUNIQUE(name)。2）外键约束：fk_表名，后接外键所在表名和对应的主表名（不包括t_）。子表名和父表名之间用下划线（_）分隔。如下：ALTERTABLEt_userADDCONSTRAINTfk_user_deptFOREIGNKEY(depno)REFERENCESt_dept(id)。3）非空约束：如果没有特殊需要，建议所有字段默认不为空，不同数据类型必须给出默认值（default）。`id`int(11)NOTNULL,`name`varchar(30)DEFAULT'',`deptId`int(11)DEFAULT0,`salary`floatDEFAULTNULL,4)出于性能考虑，如果没有特殊需要，建议不要使用外键。参照完整性由代码控制。这也是我们通常的做法，从程序的角度来控制完整性，但是稍不注意，也会产生脏数据。另外，搜索公众号python人工智能科技回复关键词“python进阶”，即可获得惊喜礼包。5)名称应为小写。12.用户命名规范1)生产中使用的用户命名格式为code_application。2）只读用户的命名规则是read_application。推荐一下自己实际做的SpringBoot工程：https://github.com/YunaiV/ruoyi-vue-pro二、数据库对象设计规范1、存储引擎的选择如果没有特殊要求，必须是innodb存储引擎用过的。您可以通过显示“default_storage_engine”等变量来查看当前的默认引擎。主要有MyISAM和InnoDB，从5.5版本开始默认使用InnoDB引擎。基本区别是：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型表强调性能，执行速度比InnoDB类型快，但不提供事务支持，而InnoDB提供事务支持和外部键等高级数据库功能。2、如果对字符集选择没有特殊要求，必须使用utf8或者utf8mb4。在国内，选择对中文和其他语言支持很好的utf8格式是最好的方式。MySQL在5.5之后加入了utf8mb4编码，mb4表示最多字节4，专门用来兼容四字节的unicode。所以utf8mb4是utf8的超集，除了将编码改成utf8mb4外不需要其他转换。当然，为了节省空间，通常使用utf8就足够了。可以使用以下脚本查看数据库的编码格式：1SHOWVARIABLESWHEREVariable_nameLIKE'character_set_%'ORVariable_nameLIKE'collat??ion%';2--或3显示变量Like'%char%';3.表设计规范1)应尽可能减少不同应用对应的数据库表之间的关联，不允许外键关联表，保证组件对应的表之间的独立性，并提供可能重建系统或表结构性。当前的行业实践通常通过程序来控制参照完整性。2）从表设计的角度来看，数据库设计不应针对整个系统进行，而应基于系统架构中的组件划分，针对各个组件所处理的业务进行数据库设计。3）表必须有PK。主键的优点是唯一标识、有效引用、高效检索。因此，一般情况下，尽量有一个主键字段。4）一个字段只表示一个意思。5）表格不应有重复的列。6）禁止使用复杂数据类型（数组、自定义等），使用Json类型视情况而定。7）需要连接的字段（连接键）的数据类型必须绝对一致，避免隐式转换。比如关联字段都是int类型。8）设计至少要满足第三范式，尽量减少数据冗余。一些特殊场景允许非规范化设计，但需要在项目评审时对冗余字段的设计进行说明。9）TEXT字段作为大量的文本存储，必须放在一个独立的表中，并通过PK与主表相关联。除非另有要求，否则禁止使用TEXT和BLOB字段。10）需要定期删除（或转移）过期数据的表，可以通过分表来解决。我们的做法是将操作频率低的历史数据按照2/8规则迁移到历史表中，按照时间或者ZengId点进行裁剪。11）单表字段数不宜过多，建议最多不超过50个。过大的宽表对性能也有很大的影响。12）MySQL在处理大表时，性能开始明显下降，建议单表物理大小限制在16GB，表数据行数控制在2000W以内。行业规则是超过2000W性能开始显着下降。但是这个值是灵活的，大家可以根据实际情况来判断。比如阿里的标准是500W，百度的确实是2000W。其实无论是宽表还是宽表，单行数据占用的空间是有影响的。13）如果前期规划数据量大或者数据增长大，那么在设计评审的时候应该加入分表策略，会有专门的文章来分析数据的分表方式：垂直分表（垂直分库和分表）垂直拆分表），水平拆分（分库分表和分库分表）。14)无特殊要求，严禁使用分区表。4、字段设计规范1）INT：如无特殊需要，使用UNSIGNEDINT类型存储整数，整数字段后面的数字代表显示长度。例如idint(11)NOTNULL。2）DATETIME：所有需要精确到时间（时、分、秒）的字段都使用DATETIME，不要使用TIMESTAMP类型。对于TIMESTAMP，它将写入的时间从当前时区转换为UTC（协调世界时）进行存储。查询时，转换成客户端当前时区返回。而对于DATETIME，不作任何改动，基本按原样输入输出。另外DATETIME的存储范围比较大：timestamp可以存储的时间范围是：'1970-01-0100:00:01.000000'到'2038-01-1903:14:07.999999'。datetime可以存储的时间范围是：'1000-01-0100:00:00.000000'到'9999-12-3123:59:59.999999'。但在特殊情况下，TIMESTAMP更适合跨时区的业务。3）VARCHAR：所有动态长度的字符串都使用VARCHAR类型，类似于status等有限类别的字段，也使用能够明确表达实际含义的字符串，不宜用INT等数字代替；变量（N）。N表示字符数，而不是字节数。例如VARCHAR(255)最多可以存储255个字符（字符包括英文字母、汉字、特殊字符等）。但是N要越小越好，因为MySQL表中所有VARCHAR字段的最大长度为65535字节，存储的字符个数由选择的字符集决定。如果UTF8存储的字符最大为3个字节，那么varchar在存储占用3个字节的字符时不应超过21845个字符。同时，在进行排序、创建临时表等内存操作时，会使用N的长度来申请内存。（如果没有特殊需要，原则上单个varchar字段不允许超过255个字符）4）TEXT：只有当字符数可能超过20000时，才可以使用TEXT类型来存储字符数据，因为所有MySQL数据库将使用UTF8字符集。所有使用TEXT类型的字段都必须从原表中拆分出来，以原表的主键组成另一张表存储。与大型文本字段隔离的目的是。如果没有特殊需要，不要使用MEDIUMTEXT、TEXT、LONGTEXT类型。5）对于精确的浮点数据存储，要求使用DECIMAL，严禁使用FLOAT和DOUBLE。6）如果没有特殊需要，尽量不要使用BLOB类型。7）如果没有特殊需要，建议字段使用NOTNULL属性，可以使用默认值代替NULL。8）自增字段类型必须是整数且必须是UNSIGNED，推荐类型为INT或BIGINT，自增字段必须是主键或主键的一部分。五、指数设计规范1）指数差异化。索引必须创建在索引选择性高（distinctdegree）的列上。选择性的计算方法为：selecttivity=count(distinctc_name)/count(*)；如果判别度的结果小于0.2，不建议在该列上面创建索引，否则极有可能拖慢SQL执行速度。2)跟随最左边的前缀。对于确定需要组成组合索引的多个字段，建议在设计时将选择性高的字段放在首位。使用时，组合索引的第一个字段必须是where条件，必须按照最左前缀规则匹配。3）禁止使用外键，可以在程序层面进行完整性约束。4）如果需要为Text类型的字段创建索引，必须使用前缀索引。5)单表索引数理论上应控制在5个以内。经常有大量的insert和update操作的表，建索引越少越好。索引创建原则理论上是多读少写的场景。6）索引后需要添加ORDERBY、GROUPBY、DISTINCT字段，形成覆盖索引。7）正确理解和计算索引字段的判别。文中有计算规则。具有高区分度的索引可以快速定位数据。如果区分度太低，则无法有效使用该指标。它可能需要扫描大量的数据页，和不使用Indexes没什么两样。8)正确理解和计算前缀索引的字段长度。文中有判断规则。合适的长度必须保证高辨别力和最合适的索引存储容量。只有达到最优状态才是保证高效率的指标。9）联合索引注意最左匹配原则：必须按照从左到右的顺序进行匹配，MySQL会一直向右匹配索引，直到遇到范围查询（>、<、between、like）和然后停止匹配。例如：depno=1andempname>''andjob=1如果按照(depno,empname,job)的顺序创建索引，则job不会使用该索引。10）根据需要选择策略。查询记录时，不要立即使用*，只取需要的数据。如果可能，尽量使用索引覆盖，可以减少回表操作，提高效率。11）正确判断是否使用联合索引（判断规则在上文联合索引使用部分有说明），也可以进一步分析索引下推（IPC），减少回表操作并提高效率。12）避免索引失效原则：禁止在索引字段上使用函数和运算符，会使索引失效。这其实就是需要保证索引对应的字段“干净”。13)避免不必要的类型转换。当字符串字段与值进行比较时，索引将无效。14)模糊查询'%value%'会使索引失效，变成全表扫描，因为无法判断扫描间隔，但是'value%'可以有效利用索引。15)索引覆盖排序字段，可以减少排序步骤，提高查询效率。16)尽量扩展索引，非必要不要新建索引。比如表中已经有a的索引，现在要增加(a,b)的索引，那么只需要修改原来的索引即可。另外，搜索公众号编程技术圈，回复关键字“Java”，即可获得惊喜礼包。示例：比如对于一张brand表，创建的索引如下，一个主键索引和一个唯一索引。PRIMARYKEY(`id`),UNIQUEKEY`uni_brand_define`(`app_id`,`define_id`)当你同事的业务代码中的搜索语句如下，应该立即警告，即没有覆盖索引，并且没有最左前缀原则：selectbrand_id,brand_namefromds_brand_systemwherestatus=?和define_id=?和app_id=?建议改为：selectbrand_id,brand_namefromds_brand_systemwhereapp_id=?)PK要有序无意义，由开发者定义，尽可能短，自增序列。2）如果表中除了PK之外还有唯一约束，可以在数据库中创建以“uk_”为前缀的唯一约束索引。3）PK字段不允许更新。4）禁止创建外键约束，由程序控制。5）如果没有特殊需要，所有字段都要加上非空约束，即notnull。6)除非另有要求，所有字段必须有默认值。推荐自己的SpringCloud实战项目：https://github.com/YunaiV/onemall三、SQL使用规范1、select检索规范1）尽量避免使用select*。在join语句中使用select*可能会导致只有访问索引才能完成的查询需要回表取数据。一是可能会取出很多不需要的数据，这对宽表来说是灾难；另外就是尽量避免回表，因为取一些不需要的数据回表是很不经济的，导致性能低下。2）严禁在不加任何where条件的情况下使用select*fromt_name。道理是一样的。这将成为全表全字段扫描。3）MySQL中文本类型字段的存储：不与其他普通字段存储在一起，因为读取效率低，同时也会影响其他轻量级字段的访问效率。如果不需要text类型的字段，使用select*，执行会消耗很多io，效率也很低。4）提取字段上可以使用相关函数，但尽量避免使用now()、rand()、sysdate()等结果不确定的函数，严禁在过滤条件字段上使用任何函数Where条件，包括数据类型转换函数。大量的计算和转换会导致效率低下，在索引端也有说明。5）所有的分页查询语句都需要有排序条件，否则容易造成乱序。6）用in()/union代替or，效率会更好，注意in的个数小于300。7）模糊前缀查询严禁使用%前缀：如：selecta,b,c来自t_name，其中alike'%name'；可以使用%模糊后缀查询如：selecta,bfromt_namewherealike'name%'。8）为了避免使用子查询，可以将子查询优化为连接操作。通常子查询在in子句中，子查询是简单的SQL（不包括union、groupby、orderby、limit子句），那么可以将子查询转化为关联查询进行优化。子查询性能差的原因：子查询的结果集不能使用索引。通常，子查询的结果集会存储在一个临时表中。内存临时表和磁盘临时表都不会有索引，所以会影响查询性能。一定的影响；尤其对于返回大结果集的子查询，对查询性能的影响更大；因为子查询会产生大量的临时表，没有索引，会消耗过多的CPU和IO资源，导致大量的慢查询。2、操作规范化1）禁止使用没有字段列表的INSERT语句。例如：insertintovalues('a','b','c');应该使用insertintot_name(c1,c2,c3)values('a','b','c');.2）海量写操作（UPDATE、DELETE、INSERT）需要批量执行多次。大规模操作可能会导致主从延迟严重，尤其是在主从模式下，大规模操作可能会导致主从严重延迟，因为slave需要从master的binlog中读取日志进行数据同步。当binlog日志为行格式时，会产生大量的日志。4.流程约束我们团队后续的目标是开发审核工具，对开发者提交的建库、建表、数据刷、查询语句等进行分析，看是否符合规范。如果不是，则拒绝修改。
