作者：翁志华来源：https://www.cnblogs.com/wzh2010/数据库对象命名规范，视图（View），图表（Diagram），默认值（Default），规则（Rule），触发器（Trigger）)、存储过程(StoredProcedure)、用户(User)等。命名约定是指数据库(SCHEMA)、表(TABLE)、索引(INDEX)、约束(CONSTRAINTS)等数据库对象的命名约定.数据库对象全局命名约定1.使用有意义的英文词汇命名，词汇中间用下划线分隔2.命名只能使用英文字母、数字、下划线，以英文字母开头3.避免使用MySQL保留字如：backup、call、group等。4.所有数据库对象使用小写字母。其实MySQL是可以设置是否区分大小写的。为了保证统一性，我们在这里对所有的小写表达式进行了标准化。数据库命名规范1、数据库名称尽量不要超过30个字符。2.数据库名一般是项目名+代表库含义的缩写。例如IM项目的工作流数据库可以是im_flow。3、默认的字符集和collat??ion子句必须在创建数据库的时候加入。默认字符集为UTF8（迁移后的dumbo使用utf8mb4）4.命名应使用小写字母。表命名约定1、常规表名以t_开头，t代表table。命名规则为t+module（简写包含模块的意思）+table（简写包含table的意思），比如用户模块的教育信息表：t_user_eduinfo。2、临时表（RD、QA或DBA同学临时处理数据使用的表），命名规则：temp前缀+模块+表+日期后缀：temp_user_eduinfo_202107193，备份表（用于历史数据保存归档或作为灾难恢复）data）命名规则，bak前缀+模块+表+日期后缀：bak_user_eduinfo_202107194。同一模块的表尽量使用相同的前缀，表名尽量表意。5.多个单词用下划线隔开_6.常规表格名称尽量不要超过30个字符。temp表和bak表视情况而定，越短越好。命名应使用小写字段命名约定。下划线_连接，如service_ip，service_port。2、表之间含义相同的字段必须重名。比如表a和表b都有一个创建时间，应该统一为create_time。不一致会造成混乱。3、多个单词之间用下划线_分隔4、字段名不超过30个字符，名称使用小写索引命名规范1、唯一索引使用uni+字段名命名：createuniqueindexuni_uidont_user_basic(uid)。2.非唯一索引使用idx+字段名命名：在t_user_basic(uname,mobile)上创建索引idx_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)。视图命名约定1、视图名称以v开头，表示视图，完整结构为v+视图内容含义的缩写。2、如果视图只来自单表，则为v+表名。如果视图是由几个表关联生成的，用v+下划线（_）连接几个表名，视图名不超过30个字符。如果超过30个字符，将被缩写。3、如无特殊需要，严禁开发者创建视图。4.名称应小写。存储过程命名约定1、存储过程名称以sp开头，表示一个存储过程（storageprocedure）。之后，多个单词用下划线（_）连接。存储过程名称应反映其功能。存储过程名称不应超过30个字符。2、存储过程中输入参数以i_开头，输出参数以o_开头。3.名字要小写。1createproceduresp_multi_param(ini_idbigint,ini_namevarchar(32),outo_memovarchar(100))函数命名约定1.函数名以func开头，意思是函数。之后，多个单词用下划线（_）连接，函数名要体现其功能。函数名称不应超过30个字符。2.名称应小写。1createfunctionfunc_format_date(ctimedatetime)触发器命名约定1.触发器以trig开头，表示trigger触发器。2、基础部分描述了触发器添加的表，触发器名称不超过30个字符。3.后缀(_i,_u,_d)表示触发条件的触发方式(insert,updateordelete)。4.名称应小写。1如果存在则删除触发器trig_attach_log_d；2在为每一行的t_dept上删除后创建触发器trig_attach_log_d；约束命名约定1、唯一约束：uk_表名_字段名。uk是UNIQUEKEY的缩写。例如，对一个部门的部门名称添加唯一约束，保证名称不重复，如下：ALTERTABLEt_deptADDCONSTRAINTun_nameUNIQUE(name);2.外键约束：fk_表名，后接外键表名和对应的主表名（不包括t_）。子表名和父表名之间用下划线（_）分隔。如下：ALTERTABLEt_userADDCONSTRAINTfk_user_deptFOREIGNKEY(depno)REFERENCESt_dept(id);3.非空约束：如果没有特殊需要，建议所有字段默认非空（notnull），不同数据类型必须给默认值（default）。1`id`int(11)NOTNULL,2`name`varchar(30)DEFAULT'',3`deptId`int(11)DEFAULT0,4`salary`floatDEFAULTNULL,4.性能原因，比如如果没有特殊需要，建议不要使用外键。参照完整性由代码控制。这也是我们通常的做法，从程序的角度来控制完整性，但是稍不注意，也会产生脏数据。5.名称应小写。用户命名约定1.生产中使用的用户命名格式为code_application2.只读用户命名规则为read_application数据库对象设计规范存储引擎选择1.如果没有特殊要求，必须使用innodb存储引擎。您可以通过显示“default_storage_engine”等变量来查看当前的默认引擎。主要有MyISAM和InnoDB，从5.5版本开始默认使用InnoDB引擎。基本区别是：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型表强调性能，执行速度比InnoDB类型快，但不提供事务支持，而InnoDB提供事务支持和外部键等高级数据库功能。字符集的选择1、如果没有特殊要求，必须使用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%';表设计规范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.如果前期规划数据量大或者数据增长大，那么在设计评审的时候要加入分表策略，会有专门的文章分析数据的分拆方式：垂直分拆（垂直数据库和垂直分表）、水平分表（分库分表和库内分表）；十四、无特殊要求，严禁使用分区表字段设计规范1、INT：如无特殊要求，使用UNSIGNEDINT类型存储整数，整数字段后面的数字代表显示长度。例如idint(11)NOTNULL2.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等数字代替；VARCHAR(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。BIGINT，自增字段必须是主键或主键的一部分。索引设计规范1.索引区分索引必须创建在索引选择性（distinctdegree）高的列上。selectivity的计算方法为：selectivity=count(distinctc_name)/count(*);如果判别度的结果小于0.2，不建议在该列上建立索引，否则极有可能拖慢SQL执行速度2.按照最左边的前缀确定需要组成复合索引的多个字段，建议在设计时将高选择性字段放在首位。使用时，组合索引的第一个字段必须是where条件，必须按照最左前缀规则匹配。3、禁止使用外键，可以在程序层面进行完整性约束。4.如果需要为Text类型的字段创建索引，则必须使用前缀索引。5、单表索引数理论上应控制在5个以内。经常有大量的insert和update操作的表，建索引越少越好。索引创建原则理论上是多读少写的场景。6、索引后需要加上ORDERBY、GROUPBY、DISTINCT的字段，形成覆盖索引。7、正确理解和计算索引字段的区分度。文中有计算规则，辨别度高的索引可以快速定位数据。辨别度太低，无法有效使用索引，可能需要扫描大量的数据页，与不使用索引无异。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)的索引，那么只需要修改原来的索引即可。例如：比如一张品牌表，创建的索引如下，一个主键索引，一个唯一索引1PRIMARYKEY(`id`),2UNIQUEKEY`uni_brand_define`(`app_id`,`define_id`)当你的同事业务代码中的搜索语句如下时，应该立即警告，即没有覆盖索引，不遵循最左前缀原则：1selectbrand_id,brand_namefromds_brand_systemwherestatus=?和define_id=?和app_id=?建议改为如下：1selectbrand_id,brand_namefromds_brand_systemwhereapp_id=?和define_id=?和状态=？约束设计规范1.PK要有序无意义，开发者自定义，尽量短，自增序列。2、如果表中除了PK之外还有唯一约束，可以在数据库中创建一个以“uk_”为前缀的唯一约束索引。3.PK字段不允许更新。4.禁止创建外键约束，由程序控制。5、如果没有特殊需要，所有字段都要加上非空约束，即notnull。6.如无特殊要求，所有字段必须有默认值。SQL使用标准select检索的标准化1.尽量避免使用select*。在join语句中使用select*可能会导致只需要访问索引的查询就完成了，需要回表取数据。一是可能会取出很多不需要的数据，这对宽表来说是灾难；另外就是尽量避免回表，因为取一些不需要的数据回表是很不经济的，导致性能低下。2、严禁在不加任何where条件的情况下使用select*fromt_name。道理是一样的。这将成为全表全字段扫描。3、MySQL中文本类型字段的存储：3.1．不要和其他普通字段一起存放，因为读取效率低也会影响其他轻量级字段的访问效率。3.2.如果不需要文本类型字段而使用select*，执行会消耗大量io，效率会很低。4、可以在fetched字段上使用相关函数，但要尽量避免now()、rand()、sysdate()等结果不确定的函数，严禁使用任何函数，包括数据类型转换函数,在Where条件中的筛选条件字段上。大量的计算和转换会导致效率低下，在索引端也有说明。5、所有的分页查询语句都需要有排序条件，否则容易造成乱序。6、用in()/union代替or，效率会更好，注意in的个数小于3007，严禁使用%前缀模糊前缀查询：如：selecta,b,c来自t_name，其中alike'%name'；可以使用%模糊后缀查询如：selecta,bfromt_namewherealike'name%';8.避免使用子查询，可以将子查询优化成连接操作。通常，子查询在in子句中，子查询是简单的SQL（不包括union、groupby、orderby、limit子句），那么可以将子查询转化为关联查询执行。优化。子查询性能差的原因：子查询的结果集不能使用索引。通常，子查询的结果集会存储在一个临时表中。内存临时表和磁盘临时表都不会有索引，所以会影响查询性能。一定的影响；尤其对于返回比较大的结果集的子查询，对查询性能的影响更大；因为子查询会产生大量的临时表，没有索引，会消耗过多的CPU和IO资源，导致大量的慢查询。操作规范化1、禁止使用没有字段列表的INSERT语句，如：insertintovalues('a','b','c');insertintot_name(c1,c2,c3)values('a'应该用','b','c');.2、海量写操作（UPDATE、DELETE、INSERT）需要分批多次进行。大量操作可能会导致严重的主从延迟，尤其是在主从模式下。大量操作可能会导致严重的主从延迟。从延迟上来说，因为slave需要从master的binlog中读取日志进行数据同步。·当binlog日志为行格式时，日志程序会有很多约束。我们团队后续的目标是开发一个审核工具，对开发者提交的建库、建表、数据刷刷、查询语句进行分析，看是否满足应用需求。有规格。如果不是，则拒绝修改。近期热点文章推荐：1.1,000+Java面试题及答案（2021最新版）2.别在满屏的if/else中，试试策略模式，真的很好吃！！3.操！Java中xx≠null的新语法是什么？4、SpringBoot2.5发布，深色模式太炸了！5.《Java开发手册（嵩山版）》最新发布，赶快下载吧！感觉不错，别忘了点赞+转发！
