数据库命名/设计/访问规范

1.1.1 基本命名原则

【规则1】命名使用具有意义的英文词汇(或简写)，词汇之间使用下划线连接。

说明：普遍认为数据表及其字段等内容使用英文名称更加规范，这样可以为后续的查询及编程提供便利。英文名称应该体现其内容或功能，尽量让用户或编程人员通过名字就能了解其内容或功能。

【规则2】命名只能使用英文小写字母，数字，下划线，并以英文字母开头。

说明：因为某些数据库系统对于大小写是敏感的，统一使用小写有助于数据库在不同系统之间移植。

【规则3】使用下划线分段命名原则

说明：命名中多个单词间采用下划线分隔，方便用户阅读与理解。同时，这种结构便于某些工具对数据库对象的映射。

【规则4】避免用数据库，例如MySQL的保留字（如desc），关键字（如index）。

说明：使用数据库保留字或关键词，会增加SQL编写的复杂度，具体可参考数据库官方文档。

【规则5】命名中英文名称使用单数，动词使用原型。

说明：名称应该仅仅表示实体内容，不应该表示实体数量，对应动词一样。否则，不同人出于不同理解，对名称有人使用单数，有人使用复数；对动词有人使用原型，有人使用动名词，有人使用过去分词，无法达成一致。

1.1.2 数据表命名规则

【规则1】同一个业务模块的表尽可能使用相同的前缀，表名称尽可能表达业务功能含义，并以“gs_”作为产品表的共同前缀。规则为：gs_模块_功能

示例：企业订单数据表命名：gs_biz_order

【规则2】表名长度不超过30个字符。

1.1.3 字段命名

【规则1】使用表达实际含义的英文单词或其约定简写。

示例：注册号gsid，手机号mobile

【规则2】不同数据表之间，相同意义的字段应同名。

说明：使用相同的字段名，容易理解为相同的内容。如果因为各种原因使用不同的名字，需要说明字段之间的对应关系。

【规则3】布尔意义的字段以“is_”作为前缀，后接动词或形容词，1表示是，0表示否。

示例：是否删除is_delete；是否可见is_visible；是否匹配is_match。

【规则4】字段长度不超过30个字符。

【规则5】自增字段，如果要作为其它表的外键，不要使用id之类看不出业务含义的名称，要用有业务含有的命名，例如gsid，orderid, inviteid.

【规则6】有业务含义的字段，要放在表定义的前面，create_time,update_time, creater等作为留痕行为的字段，建议放在最后。

【规则7】相关概念的字段，定义要放在一起。

1.1.4 索引命名

【规则1】单字段普通索引的命名方式为：idx_表名_字段名，表名无须“gs_”前缀，命名长度太长时表名和字段名可以考虑缩写。

示例：gs_u_users表mobile上的索引：idx_u_users_mobile

【规则2】多字段联合普通索引命名方式同单字段，考虑长度限制，可以只列出主要字段名或者采用缩写方式描述索引字段。

示例：gs_biz_order_visit表orderid和vistor的index：idx_biz_order_visit_orderid_vistor

【规则3】唯一索引以uk为前缀，其它与普通索引一致

示例：gs_u_users表mobile上的唯一索引：uk_u_users_mobile

1.1.5 视图命名

【规则1】视图的命名以v_开头, 其他部分命名规范与表名相同。

【规则2】视图字段名一般与基表一致，但是根据需要也可以重新命名，命名仍然要求体现功能或内容含义。

1.1.6 触发器命名

【规则1】触发器的命名格式为：trg_表名_触发器类型。

说明：表名不带“gs_”前缀，触发器的类型由触发时机和触发动作组成：‘B’表示前触发，‘A’表示后触发;‘INSERT’‘UPDATE’‘DELETE’描述触发动作。

1.1.7 函数与存储过程命名

【规则1】函数前缀为“fun_”,存储过程前缀为“sp_”，采用动词+名词的形式表达其含义。

【规则2】输入参数命名规范为p_name，普通类型变量命名规范为v_name，输出参数规范为o_name，输出参数放在参数列表最后。

说明：命名规范中的name部分应能清楚表示变量或者参数的含义，以提高代码可读性。避免使用v_1、v_m、p_1、p_n等无法表达具体含义的参数或者变量命名。

1.2.1 数据表设计三范式

什么是范式：简言之就是，数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的的数据库是需要满足一些

规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式。

什么是三大范式：

第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。

第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.

注：关系实质上是一张二维表，其中每一行是一条实例，每一列是对应的每一个属性

第一范式

1、每一列属性都是不可再分的属性值，确保每一列的原子性

2、两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。

表一

表二

　　如果需求知道那个省那个市并按其分类，那么显然表一是不容易满足需求的，也不符合第一范式，改成表二即可。

表三

表四

　　显然表三结构不但不能满足足够多物品的要求，还会在物品少时产生冗余。也是不符合第一范式的，改成表四即可。

第二范式

每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。

表五

　　表五中一个人同时订几个房间，就会出来一个订单号多条数据，这样的话联系人都是重复的，就会造成数据冗余。我们应该把他拆开来。需要注意的是假设表中只有联系人，没有电话和身份证号就不必拆分了。

表六

表七

　　这样便实现啦一条数据做一件事，不掺杂复杂的关系逻辑。同时对表数据的更新维护也更易操作。

第三范式

数据不能存在传递关系，即没个属性都跟主键有直接关系而不是间接关系。像：a-->b-->c 属性之间含有这样的关系，是不符合第三范式的。

　　比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）

　　这样一个表结构，就存在上述关系。 学号--> 所在院校 --> (院校地址，院校电话)

　　这样的表结构，我们应该拆开来，如下。

　　（学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）

最后：

　　三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

1.2.2 数据表设计

【规则1】要求数据表都必须有主键，要选择具有唯一值的字段或字段组合（建议最多2个）作为主键字段，而且这些字段的值不要在业务处理过程中被修改。

说明：如果数据实体中找不到合适的主键，可以创建一个无意义的ID字段，并通过自动加1来实现唯一性。

【规则2】除非数据表中含有用于表达创建时间和修改时间的字段，否则必须增加“创建时间”、“修改时间”字段，并且表中数据的任何变化，都需更新“修改时间”字段。

【规则3】单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。

说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

【规则4】不得使用外键与级联，一切外键概念必须在应用层解决

说明 : 学生表中的 studentid 是主键，那么成绩表中的 studentid 则为外键。如果更新学生表中的 studentid，同时触发成绩表中的 studentid 更新，则为级联更新，级联更新是强阻塞，存在数据库更新风暴的风险；而且维护外键约束影响数据库的插入速度。

【规则5】数据表定义中字符存储使用utf-8 编码

【规则6】一个数据表中的字段不宜过多，建议控制在60个以内。

说明：字段过多或导致每行数据过长，尤其存在较多值为null的字段时，在数据行被更新时，会频繁产生行数据迁移，影响系统性能。

1.2.3 字段设计

【规则1】每个字段都必须标注业务含义，如果是枚举值字段，还必须列举所有枚举值，并说明每个值的业务含义。

【规则2】小数字段类型使用decimal，禁止使用float和double。

说明：float和double在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。

【规则3】如果字段存储字符串长度几乎相等，使用CHAR定长字符串类型。

说明：定长字段字符串可以避免数据更新时产生数据行迁移。

【规则4】对于可变长字字符串字段，不预先分配存储空间，长度不要超过5000字节。

说明：如果存储长度大于5000字节，定义字段类型为TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

【规则5】字段适当冗余可以提高性能，但冗余字段不应该是频繁修改的字段，不应该是超长的变长字符串字段。

1.2.4 索引设计

【规则1】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。

说明：不要认为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明。另外，即使在应用层做了非常完善的校验和控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

【规则2】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，可根据实际文本区分度决定索引长度。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名,索引长度))/count(*)的区分度来确定。

语法：CREATE INDEX index_name ON table_name (column_name(length), clolumn_name(length)…)；

【规则3】一个数据表的索引数量最好控制在5个以内

说明：过多的索引会因为索引维护而影响数据记录插入和修改的性能。

【规则4】索引字段应选择查询条件中经常使用、区分度高、更新频率低的字段。

【规则5】禁止创建重复索引

说明：重复索引只会增加索引的维护成本，不会产生任何效益。

【规则6】建立复合索引,精确确定where条件对应的列.

【规则7】多个字段等值查询，建立组合索引时，区分度最高的在最左边。如果某字段的值几乎接近唯一，则只需对它单独建立索引即可。

示例：如果where a=? and b=?，若b列区分度高，可建立INDEX(b,a)，若b列几乎接近于唯一值，那么单独建立INDEX(b)即可。

【规则8】存在非等号和等号混合判断条件，建索引时，请把等号条件的列前置。

示例：wherea>? and b=? 那么即使a的区分度更高，也必须把b放在索引的最前面，即INDEX(b,a)。

1.2.5 视图设计

【规则1】除非特殊需要，不建议创建视图。确实需要时，必须经过审核后才准使用。

说明：数据库视图有物理视图和逻辑视图。物理视图有自己的物理存储，在关联数据表数据更新时立即更新视图，因此会影响数据更新效率。逻辑视图没有自己的物理存储，它是在业务访问时进行实时计算，因此逻辑视图并不能提升SQL处理性能，反而因为它的存在（被别的SQL引用）而影响对它的性能优化。

1.2.6 触发器与存储过程

【规则1】在数据库物理设计时，要避免使用触发器和存储过程。确实需要时，必须经过审核后才准使用。

说明：触发器功能通常可以用其他方式实现，因为触发器处理在数据库后台完成，比较隐蔽，在调试程序时容易成为干扰。存储过程的问题是难以调试和扩展，更没有移植性，因此均不建议使用。

1.3 SQL设计

【规则1】需要join的字段，数据类型保持绝对一致

【规则2】多表join查询时，保证被用于join的字段需要有索引。

【规则3】利用延迟关联或者子查询优化超多分页场景。

说明：LIMIT offset,n处理，MySQL并不是跳过offset行，而是从数据表取出offset+n行，并放弃前面offset行，实际返回后面n行，那么当offset特别大的时候，效率就非常的低下

示例：先快速定位需要获取的id段，然后再关联：

SELECT a.* FROM T1 a, (SELECT id FROM T1 WHERE LIMIT 100000,20 ) b WHERE a.id=b.id

【规则4】不要使用count(列名)来替代count(常量)或count(*)

说明：count(*)或count(常量）会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。

【规则5】count(distinct col)计算该列除NULL之外的不重复col值的数量。

【规则6】count(distinct col1, col2) 计算该col1、col2列值均不NULL，且每行数据不重复的记录数量。

说明：如果其中一列为NULL，即使另一列有不同的值，也返回为0。

【规则7】当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为NULL，因此使用sum()时需注意NPE问题。

示例：可以使用如下方式来避免sum的NPE问题：SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;

【规则8】使用ISNULL()来判断是否为NULL值。

说明：NULL与任何值的直接比较都为NULL。

1> NULL<>NULL 的返回结果是 NULL， 而不是 false

2> NULL=NULL 的返回结果是 NULL， 而不是 true

3> NULL<>1 的返回结果是 NULL，而不是 true

【规则9】数据表使用utf-8编码，字符编码长度不等长，要判断字符个数，不能使用LENGTH(col)，而要使用CHARACTER_LENGTH(col)

示例：

select length('中软国际') ; // 值为12

select character_length('中软国际'); // 值为4

【规则10】TRUNCATE比DELETE删除数据表记录速度快

说明：TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同。因为TRUNCATE不记录事务日志，因此性能高，然而正因为其TRUNCATE不记录事务，而且不触发trigger，有可能造成事故，因此要格外小心。

【规则11】字段位于表达式中或作为函数参数不能使用索引

例如：

select * from t where lower(F1)=‘abc’ # F1无法使用索引

select * from t1, t2 where t1.F1 = t2.F2+1 # t1.F2无法使用索引

【规则12】隐式数据类型转换可能导致索引失效

例如：select * from t where F1=2，其中：字段F1为char类型（新版本数据库有优化除外）

【规则13】在索引字段上使用两端模糊查询，无法使用索引

例如：name like ‘%alibab%’不能使用索引

但是：name like ‘alibab%’后端模糊查询，能够使用索引

【规则14】组合字段索引包含前导索引字段。

例如：gs_biz_order_visit索引(orderid, visitor)

Select * from gs_biz_order_visit where orderid=223 含有前导order字段，可以使用索引；

Select * from gs_biz_order_visit where visitor =223 不能使用索引，因为没有使用前导字段order。

【规则15】能通过索引字段完成的查询，不要引入索引外的字段

例如：使用select orderid, visitor from gs_app_portal.gs_biz_order_visit;

而不使用：select * from gs_biz_order_visit;

【规则16】使用limit及早停止查询

例如：判断visitor在gs_biz_order_visit表中是否存在记录：

select count(*) from (select * from gs_biz_order_visit where visitor=234 limit 1) T;

优于 select count(*) from gs_biz_order_visit where visitor=234;

【规则17】利用当前分页进行翻译处理

例如：对gs_biz_order_visit表进行分页查询，数据库访问顺序排序

SQL ：Select * from gs_biz_order_visit order by vid limit $m，$n

若查询第1001页，每页20个记录，则$m=20, $n=20000，性能泪崩！

优化策略：程序记录当前页的vid最小值$vid_min和最大值$vid_max

向前翻1页： Select * from gs_biz_order_visit where vid<$vid_min order by vid desc limit 20, 0;

向前翻x页： Select * from gs_biz_order_visit where vid<$vid_min order by vid desc limit 20, $n;

向后翻1页： Select * from gs_biz_order_visit where vid>$vid_max order by vid asc limit 20, 0;

向后翻x页： Select * from gs_biz_order_visit where vid>$vid_max order by vid asc limit 20, $n;

【规则18】使用left join优化 [not] in/exist子查询

说明：[not] in/exist常用于SQL的where条件中，但是它们的效率比较差，实际上它是一个集合的交集或差集操作。在Mysql中没有提供交集和差集运算语法，但可以使用左链接的方式来解决问题。

例1：查询不在黑名单表中的用户信息

select * from user_info

where userid not in (select userid from user_black)

可优化为：

select a.* from user_info a

left join (select userid from user_black) b

on a.userid = b.userid

where b.userid is null

例2：查询在白名单表中的用户信息

select * from user_info

where userid in (select userid from user_white)

可优化为：

select a.* from user_info a

left join (select userid from user_white) b

on a.userid = b.userid

where b.userid is not null

F数据库表设计约束：

1、 主键取值建议由程序自动加1来实现。

2、表中必须包含记录“创建时间”、“修改时间”字段，并且，表中数据的任何变化，都需更新“修改时间”字段。

3、表中必须包含删除标识字段。数据的删除只是对删除标识字段值进行修改，同时更新“修改时间”字段。

4、对于数据量大的表设计时，需考虑设计历史数据备份表，定时将假删除的数据从源表迁移到对应的备份表中，在备份表中视情况进行物理删除。

5、表的命名以“gs_”+[功能简称]为前缀。

6、对于第三方引入的表，需要经过设计讨论后才能创建。

7、所有设计新增的表，都需要及时添加到文档上的库表结构设计页。