网站开发技术项目实战,知名网站建设是哪家便宜,景德镇做网站公司,江门市住房和城乡建设局门户网站数据库对象命名规范
1、数据库对象
数据库对象是数据库的组成部分#xff0c;常见的有以下几种#xff1a;表#xff08;Table #xff09;、索引#xff08;Index#xff09;、视图#xff08;View#xff09;、图表#xff08;Diagram#xff09;、缺省值#x…数据库对象命名规范
1、数据库对象
数据库对象是数据库的组成部分常见的有以下几种表Table 、索引Index、视图View、图表Diagram、缺省值Default、规则Rule、触发器Trigger、存储过程Stored Procedure、 用户User等。命名规范是指数据库对象如数据库SCHEMA、表TABLE、索引INDEX、约束CONSTRAINTS等的命名约定。
2、数据库对象全局命名规范 1、命名使用具有意义的英文词汇词汇中间以下划线分隔 2、命名只能使用英文字母、数字、下划线以英文字母开头 3、避免用MySQL的保留字如backup、call、group等 4、所有数据库对象使用小写字母实际上MySQL中是可以设置大小写是否敏感的为了保证统一性我们这边规范全部小写表示。 3、数据库命名规范 1、数据库命名尽量不超过30个字符。 2、数据库命名一般为项目名称代表库含义的简写比如IM项目的工作流数据库可以是 im_flow。 3、数据库创建时必须添加默认字符集和校对规则子句。默认字符集为UTF8已迁移dumbo的使用utf8mb4 4、命名应使用小写。
4、表命名规范 1、常规表表名以t_开头t代表table的意思命名规则即 t 模块包含模块含义的简写 表包含表含义的简写比如用户模块的教育信息表t_user_eduinfo。 2、临时表RD、QA或DBA同学用于数据临时处理的表命名规则temp前缀模块表日期后缀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 字段名 来命名create unique index uni_uid on t_user_basic(uid) 。 2、非唯一索引使用idx 字段名 来命名create index idx_uname_mobile on t_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开头表示view完整结构是v视图内容含义缩写。 2、如果视图只来源单个表则为v表名。如果视图由几个表关联产生就用v下划线_连接几个表名视图名尽量不超过30个字符。如超过30个字符则取简写。 3、如无特殊需要严禁开发人员创建视图。 4、命名应使用小写。
8、存储过程命名规范 1、存储过程名以sp开头表示存储过程storage procedure。之后多个单词以下划线_进行连接。存储过程命名中应体现其功能。存储过程名尽量不能超过30个字符。 2、存储过程中的输入参数以i_开头输出参数以o_开头。 3、命名应使用小写。
create procedure sp_multi_param(in i_id bigint,in i_name varchar(32),out o_memo varchar(100)) 9、函数命名规范 1、函数名以func开始表示function。之后多个单词以下划线_进行连接函数命名中应体现其功能。函数名尽量不超过30个字符。 2、命名应使用小写。
create function func_format_date(ctime datetime)10、触发器命名规范 1、触发器以trig开头表示trigger 触发器。 2、基本部分描述触发器所加的表触发器名尽量不超过30个字符。 3、后缀_i,_u,_d,表示触发条件的触发方式insert,update或delete。 4、命名应使用小写。
DROP TRIGGER IF EXISTS trig_attach_log_d;
CREATE TRIGGER trig_attach_log_d AFTER DELETE ON t_dept FOR EACH ROW; 11、约束命名规范 1、唯一约束uk_表名称_字段名。uk是UNIQUE KEY的缩写。比如给一个部门的部门名称加上唯一约束来保证不重名如下ALTER TABLE t_dept ADD CONSTRAINT un_name UNIQUE(name); 2、外键约束fk_表名后面紧跟该外键所在的表名和对应的主表名不含t_。子表名和父表名用下划线(_)分隔。如下ALTER TABLE t_user ADD CONSTRAINT fk_user_dept FOREIGN KEY(depno) REFERENCES t_dept (id); 3、非空约束如无特殊需要建议所有字段默认非空(not null)不同数据类型必须给出默认值(default)。
id int(11) NOT NULL,
name varchar(30) DEFAULT ,
deptId int(11) DEFAULT 0,
salary float DEFAULT NULL, 4、出于性能考虑如无特殊需要建议不使用外键。参照完整性由代码控制。这个也是我们普遍的做法从程序角度进行完整性控制但是如果不注意也会产生脏数据。 5、命名应使用小写。
12、用户命名规范 1、生产使用的用户命名格式为 code_应用 2、只读用户命名规则为 read_应用
数据库对象设计规范
13、存储引擎的选择 1、如无特殊需求必须使用innodb存储引擎。
可以通过 show variables like default_storage_engine来查看当前默认引擎。主要有MyISAM 和 InnoDB从5.5版本开始默认使用 InnoDB 引擎。
基本的差别为MyISAM类型不支持事务处理等高级处理而InnoDB类型支持。MyISAM类型的表强调的是性能其执行速度比InnoDB类型更快但是不提供事务支持而InnoDB提供事务支持以及外部键等高级数据库功能。
14、字符集的选择 1、如无特殊要求必须使用utf8或utf8mb4。
在国内选择对中文和各语言支持都非常完善的utf8格式是最好的方式MySQL在5.5之后增加utf8mb4编码mb4就是most bytes 4的意思专门用来兼容四字节的unicode。
所以utf8mb4是utf8的超集除了将编码改为utf8mb4外不需要做其他转换。当然为了节省空间一般情况下使用utf8也就够了。
可以使用如下脚本来查看数据库的编码格式
SHOW VARIABLES WHERE Variable_name LIKE character_set_% OR Variable_name LIKE collation%;
-- 或
SHOW VARIABLES Like %char%; 15、表设计规范 1、不同应用间所对应的数据库表之间的关联应尽可能减少不允许使用外键对表之间进行关联确保组件对应的表之间的独立性为系统或表结构的重构提供可能性。目前业内的做法一般 由程序控制参照完整性。 2、表设计的角度不应该针对整个系统进行数据库设计而应该根据系统架构中组件划分针对每个组件所处理的业务进行数据库设计。 3、表必须要有PK主键的优势是唯一标识、有效引用、高效检索所以一般情况下尽量有主键字段。 4、一个字段只表示一个含义。 5、表不应该有重复列。 6、禁止使用复杂数据类型(数组,自定义等)Json类型的使用视情况而定。 7、需要join的字段(连接键)数据类型必须保持绝对一致避免隐式转换。比如关联的字段都是int类型。 8、设计应至少满足第三范式,尽量减少数据冗余。一些特殊场景允许反范式化设计但在项目评审时需要对冗余字段的设计给出解释。 9、TEXT字段作为大体量文本存储必须放在独立的表中 , 用PK与主表关联。如无特殊需要禁止使用TEXT、BLOB字段。 10、需要定期删除(或者转移)过期数据的表通过分表解决我们的做法是按照2/8法则将操作频率较低的历史数据迁移到历史表中按照时间或者则曾Id做切割点。 11、单表字段数不要太多建议最多不要大于50个。过度的宽表对性能也是很大的影响。 12、MySQL在处理大表时性能就开始明显降低所以建议单表物理大小限制在16GB表中数据行数控制在2000W内。 业内的规则是超过2000W性能开始明显降低。但是这个值是灵活的你可以根据实际情况进行测试来判断比如阿里的标准就是500W百度的确是2000W。实际上是否宽表单行数据所占用的空间都有起到作用的。 13、如果数据量或数据增长在前期规划时就较大那么在设计评审时就应加入分表策略数据拆分的做法垂直拆分、水平拆分 14、无特殊需求严禁使用分区表
16、字段设计规范 1、INT如无特殊需要存放整型数字使用UNSIGNED INT型整型字段后的数字代表显示长度。比如 id int(11) NOT NULL 2、DATETIME所有需要精确到时间(时分秒)的字段均使用DATETIME,不要使用TIMESTAMP类型。
对于TIMESTAMP它把写入的时间从当前时区转化为UTC世界标准时间进行存储。查询时将其又转化为客户端当前时区进行返回。而对于DATETIME不做任何改变基本上是原样输入和输出。
另外DATETIME存储的范围也比较大
timestamp所能存储的时间范围为1970-01-01 00:00:01.000000 到 2038-01-19 03:14:07.999999。
datetime所能存储的时间范围为1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999。但是特殊情况对于跨时区的业务TIMESTAMP更为合适。 3、VARCHAR所有动态长度字符串 全部使用VARCHAR类型,类似于状态等有限类别的字段,也使用可以比较明显表示出实际意义的字符串,而不应该使用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、如无特殊需要字段建议使用NOT NULL属性可用默认值代替NULL 8、自增字段类型必须是整型且必须为UNSIGNED推荐类型为INT或BIGINT并且自增字段必须是主键或者主键的一部分。
17、索引设计规范 1、索引区分度
索引必须创建在索引选择性区分度较高的列上选择性的计算方式为: selecttivity count(distinct c_name)/count(*) ;如果区分度结果小于0.2则不建议在此列上创建索引否则大概率会拖慢SQL执行 2、遵循最左前缀
对于确定需要组成组合索引的多个字段设计时建议将选择性高的字段靠前放。使用时组合索引的首字段必须在where条件中且需要按照最左前缀规则去匹配。 3、禁止使用外键可以在程序级别来约束完整性 4、Text类型字段如果需要创建索引必须使用前缀索引 5、单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表应尽量少建索引索引建立的原则理论上是多读少写的场景。 6、ORDER BYGROUP BYDISTINCT的字段需要添加在索引的后面形成覆盖索引 7、正确理解和计算索引字段的区分度文中有计算规则区分度高的索引可以快速得定位数据区分度太低无法有效的利用索引可能需要扫描大量数据页和不使用索引没什么差别。 8、正确理解和计算前缀索引的字段长度文中有判断规则合适的长度要保证高的区分度和最恰当的索引存储容量只有达到最佳状态才是保证高效率的索引。 9、联合索引注意最左匹配原则必须按照从左到右的顺序匹配MySQL会一直向右匹配索引直到遇到范围查询(、、between、like)然后停止匹配。
如depno1 and empname and job1 如果建立(depno,empname,job)顺序的索引job是用不到索引的。10、应需而取策略查询记录的时候不要一上来就使用*只取需要的数据可能的话尽量只利用索引覆盖可以减少回表操作提升效率。 11、正确判断是否使用联合索引上面联合索引的使用那一小节有说明判断规则也可以进一步分析到索引下推IPC减少回表操作提升效率。 12、避免索引失效的原则禁止对索引字段使用函数、运算符操作会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。 13、避免非必要的类型转换字符串字段使用数值进行比较的时候会导致索引无效。 14、模糊查询%value%会使索引无效变为全表扫描因为无法判断扫描的区间但是value%是可以有效利用索引。 15、索引覆盖排序字段这样可以减少排序步骤提升查询效率 16、尽量的扩展索引非必要不新建索引。比如表中已经有a的索引现在要加(a,b)的索引那么只需要修改原来的索引即可。
举例子比如一个品牌表建立的的索引如下一个主键索引一个唯一索引
PRIMARY KEY (id),
UNIQUE KEY uni_brand_define (app_id,define_id)当你同事业务代码中的检索语句如下的时候应该立即警告了即没有覆盖索引也没按照最左前缀原则
select brand_id,brand_name from ds_brand_system where status? and define_id? and app_id?建议改成如下
select brand_id,brand_name from ds_brand_system where app_id? and define_id? and status? 18、约束设计规范 1、PK应该是有序并且无意义的由开发人员自定义尽可能简短并且是自增序列。 2、表中除PK以外,还存在唯一性约束的,可以在数据库中创建以“uk_”作为前缀的唯一约束索引。 3、PK字段不允许更新。 4、禁止创建外键约束外键约束由程序控制。 5、如无特殊需要所有字段必须添加非空约束即not null。 6、如无特殊需要所有字段必须有默认值。
SQL使用规范
19、select 检索的规范性 1、尽量避免使用select *join语句使用select *可能导致只需要访问索引即可完成的查询需要回表取数。 一种是可能取出很多不需要的数据对于宽表来说这是灾难一种是尽可能避免回表因为取一些根本不需要的数据而回表导致性能低下是很不合算。 2、严禁使用 select * from t_name 而不加任何where条件道理一样这样会变成全表全字段扫描。 3、MySQL中的text类型字段存储 3.1、不与其他普通字段存放在一起,因为读取效率低也会影响其他轻量字段存取效率。 3.2、如果不需要text类型字段又使用了select *会让该执行消耗大量io效率也很低下 4、在取出字段上可以使用相关函数但应尽可能避免出现 now() , rand() , sysdate() 等不确定结果的函数在Where条件中的过滤条件字段上严禁使用任何函数包括数据类型转换函数。大量的计算和转换会造成效率低下这个在索引那边也描述过了。 5、分页查询语句全部都需要带有排序条件 , 否则很容易引起乱序 6、用in()/union替换or效率会好一些并注意in的个数小于300 7、严禁使用%前缀进行模糊前缀查询:如select a,b,c from t_name where a like ‘%name’;可以使用%模糊后缀查询如select a,b from t_name where a like ‘name%’; 8、避免使用子查询可以把子查询优化为join操作
通常子查询在in子句中且子查询中为简单SQL(不包含union、group by、order by、limit从句)时才可以把子查询转化为关联查询进行优化。
20、子查询性能差的原因 子查询的结果集无法使用索引通常子查询的结果集会被存储到临时表中不论是内存临时表还是磁盘临时表都不会存在索引所以查询性能 会受到一定的影响 特别是对于返回结果集比较大的子查询其对查询性能的影响也就越大 由于子查询会产生大量的临时表也没有索引所以会消耗过多的CPU和IO资源产生大量的慢查询。
21、操作的规范性 1、禁止使用不含字段列表的INSERT语句
如insert into values (a,b,c); 应使用 insert into t_name(c1,c2,c3) values (a,b,c); 。2、大批量写操作UPDATE、DELETE、INSERT需要分批多次进行操作 大批量操作可能会造成严重的主从延迟特别是主从模式下大批量操作可能会造成严重的主从延迟因为需要slave从master的binlog中读取日志来进行数据同步。 binlog日志为row格式时会产生大量的日志
程序上的约束
后续我们团队的目标是研发评审工具对开发同学提交的建库、建表、刷数据、查询的语句进行分析看看是否符合应有的规范。如果不符合驳回修改。
