数据库设计规范

数据库设计规范

一 命名规范

说明：指数据库中表（TABLE），字段（COLUMN），视图（VIEW），存储过程（PROCEDURE），函数（FUNCTION），触发器（TRIGGER）等的命名规范。

1.1基本命名原则

1.   命名统一采用26个小写英文字母和0－9这十个自然数，加上下划线_组成，共37 个字符。不能出现其他字符（注释除外）。

不规范示例：USERINFO、Post@HZ                                          

规范示例：userinfo、posthz、customer、customer_order                   

2.   名称长度不超过30个字符，要求使用能准确表达对象功能的单数单词或单词缩写。

3.   单个数据库中如果含系统多个模块对象，名称中需要加模块前缀。

示例：yx_user、yx_group、music_userrecommend                              

4.   视图、存储过程、触发器、函数等对象命名需要分别加上v、sp、tr、fn前缀在相应名称前。

1.2表命名

1.   实体表使用小写英文单词或单词组合，如userinfo、address。关系表由‘实体表_实体表’组成，如userinfo_address。

2.  临时表的名称，以tmp开头，以创建日期结尾，如tmp_userexpired_20140101。

1.3字段命名

1.  字段的名称中如果包含多个单词，则由下划线分隔，如last_name、dest_address。

2.  关联表间的关联字段应同名或以表面_字段名命名，如role表引用了user表的id字段则role表中关联字段为user_id即 user.id=role.user_id。

1.4索引命名

1.   除了主键外，所有索引必须定义索引名，格式为“索引前缀+字段名列表”。

2.  索引前缀：除主键外的唯一性索引，使用“uk_”开头（unique key，唯一键），非唯一性索引，使用“idx_”开头。

3.  字段名列表：单个字段尽量使用字段全名；多个字段名之间用下划线分隔，多个字段则尽量使用名字缩写。例如，(userid,parentid)上的非唯一性索引，可以命名为idx_uid_pid；userid上的唯一索引命名为uk_userid。

1.5视图、存储过程、触发器、函数的命名

1.   必须使用对象前缀：v、sp、tr、fn。

2.   对象开头，以注释的形式，详细描述此对象的作用、创建者、创建时间以及后续修改记录。

3.   视图、存储过程和触发器等，若只涉及一个表，在命名时尽量包含涉及的表名。

4.   名称应能大致表达对象的实际功能和作用，或对象的触发情景。

如：插入触发器：tr_beforeinsertuser、得到id映射函数：fn_getuseridmap                                 

二 设计规范

2.1表设计规范

2.1.1字段的设计

1.   字段类型选择，尽量选择能满足应用要求的最小数据类型，关联表间字段类型要求一致。例如，is_deleted字段值只有true/false值,可以考虑使用tinyint类型，无需使用int或varchar类型。

2.   字段长度选择，避免定义过长的字段长度。合适的字段及字段长度不仅可以有效减少内存磁盘网络等方面开销，还可以加速ddl/join等执行速，如主机名可以定义为varchar(64) 不要随便定义为varchar(1000)。

3.   尽量使用整型代替字符型。整形在字段长度、索引大小等方面开销更小效率更高，如ip字段、邮编字段等。

4.   非空及默认值定义，建议字段都设为非空，使用如-1、0、空字符串等常量代替NULL值。

5.   字符集选择，选择满足应用要求的字符集与字符比较集，如无特殊要求，则统一使用表级别字符集，否则需要在字段级别单独定义。

6.   禁止使用enum、set、boolean等数据类型，统一使用tinyint代替，金融类数据禁止使用float、double类型，统一只用decimal

7.   注释，每个字段必须以COMMENT语句给出字段的作用、必要的取值范围等。

2.1.2索引的设计

1.   选择在where子句中出现，且选择性和过滤性高的字段创建索引，过滤性少的无需创建。

2.   在经常组合的过滤字段上做复合索引，选择性高的字段放在前面。

3.   较长字符类型字段考虑使用前缀索引，如字段定义：user_name varchar(255) 字段前8个字符即可非常准确过滤，此时可以考虑建立索引idx_user_name(8)。

4.   在需要order by/distinct/group by 等字段上建立合适索引。

5.   在执行频率、并发非常高的情况下考虑建立覆盖索引，如下面语句并发非常高：

select uid,pid,order_idfrom user where uid=234 and is_valid=1;   

可以考虑建立idx_uid_pid_mid(uid,is_valid,pid,order_id)索引            

2.1.3约束的设计

1.   主键约束：每张表需要有一个主键，统一用id bigint unsigned自增字段。

2.   唯一约束：每张关键业务表需要有一个唯一键，代表业务规则。

3.   不在数据库中定义外键，由应用保证，避免带来锁问题。

4.   使用整型来实现check约束,如 性别字段使用tinyint，0表示男，1表示女。

2.1.4表其它规范

1.   表中必须包含表示记录创建时间字段，如create_time、publish_time等，同时需要加上表示记录更新时间字段，如下，该字段在数据更新时值会自动变为更新时间。

updated_at   timestamp  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,

2.   如果表需要经常更新且更新的字段占比很少，将需要频繁更新的字段拆分到单独表。

3.   经常访问的大字段（如blob,text）等需要单独放到一张表中，避免降低SQL效率，图片、电影等大文件数据禁止存数据库。

4.  表的字符集统一使用utf8或utf8mb4(移动产品)，比较字符集使用utf8_general_ci，如有特殊需求，在字段中单独定义。

5.       必须包含表功能的注释在建表语句中。

2.2其它对象设计

系统中通常非常少或不会含有视图存储过程触发器等设计需求，如果需要应与产品DBA 协商设计细则。

2.3 建表示例

建表示例：

CREATE TABLE user (                                                      

  id          int unsigned NOT NULL AUTO_INCREMENT,                     

  name        varchar(64) not null default '' comment '用户名',         

  password    varchar(128) NOT NULL default '' comment '加密后字符串',  

  email       varchar(64) not null default '' comment '邮箱',           

  age         tinyint unsigned comment '年龄',                         

  sex         tinyint comment '0:男，1:女',                             

  mobile      bigint unsigned not null comment '用户手机号码',           

  birthday    date comment '生日日期',                                  

  face_url    varchar(255) not null default '' comment '头像url',       

  address     varchar(255) not null default '' comment '地址',          

  is_active   tinyint not null default 1 comment '是否激活,1：是 0:不是',

  created_at timestamp NOT NULL DEFAULT '0000-000-0000:00:00',        

  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ONUPDATE      CURRENT_TIMESTAMP,                                                      

  PRIMARY KEY (id),                                                      

  unique key uk_name(name),                                             

  key idx_mobile(mobile)                                                

) ENGINE=InnoDB  DEFAULTCHARSET=utf8 comment '用户信息';                

三 SQL编写规范

3.1 DML事务相关规则

1.  select/insert语句必须指定出具体的字段名，delete/update语句必须要有过滤字段，且过滤字段上需要有索引。

说明：delete/update的过滤字段上没有索引，则SQL将锁整张表               

2.  使用短小事务，不使用显示加锁如select for update/lockin sharemode等，如需使用需与产品DBA协商。

3.   大量数据需要更改时（更新记录超过10万条），请事先与DBA协商，操作时需要使用批量提交，并且避免业务高峰期操作。

3.2查询语句-过滤字段

1.   不在where子句中对索引字段施加函数，这样将导致SQL无法正确利用索引。

如：错误：select id,namefrom tab where unix_timestamp(creeated_at)= 13883324

正确：select id,namefrom tab where creeated_at=from_unixtime(13883324)

2.   模糊查询like：在业务允许情况下，尽量使用右模糊查询。

说明：col_name like 'abc%'可以使用索引 而col_name like '%abc'则不可以

3.   查询分区表示时，尽量将查询都落在1个分区。

4.   严格要求使用正确类型的变量，杜绝隐式类型转换,表Join时 join字段必须确保类型一致。

int_code为int类型，错误写法：int_code='1234' 正确写法：int_code=1234 

str_code为char类型，表join时 错误写法：a.str_code=b.int_code        

正确写法：cast(a.str_code as signed)=b.int_code                        

3.3查询优化建议

1.    降低SQL复杂度，将需要多表关联的复杂查询拆分为多个简单查询。

2.    当需要左连接、右连接、外连接时统一使用左连接实现。

说明：MySQL使用左连接的方式实现右连接，外连接则需要多个左连接实现       

3.   严禁使用select *,明确写出查询的具体字段。

说明：1， select * 可能消耗更多的cpu、内存、网络等资源                   

2，可能导致覆盖索引无法使用                                              

3，可能导致order by语句无法使用最优的排序算法                           

4，字段数量进行变更时，字段位置会偏移，可能导致原有SQL 出现问题         

4.   尽量避免使用子查询，可以改为[left]join或拆为多条sql 。

5.   hint的使用需要由 dba review 确认。

6.   不需要去重情况下使用 union all 而非 union，因为union需要额外的去重操作。

7.    不在数据库端进行复杂计算(如浮点运算、时间复杂度计算等)，将计算等任务交给client完成。

8.   正确使用索引进行order by /group by/distinct 避免filesort use tmp table等情况。

9.    没必要的时候不要做外连接，内连接效率比外连接高。

10. or条件若不能正确的走索引可以考虑使用union 或in，通常同一个字段的or操作可以改为in,不同字段or 操作改为union 如：

1, select id,uid,cid from  tabwhere uid = 1 or uid= 100 or uid=10;      

   改为：select id,uid,cid from tab where uid in (1,100,100);            

2, select id,uid,cid from tab where  uid=100 or cid=500                 

   改为：  select id,uid,cid fromtab  where  uid=100                    

           union  select id,uid,cid from tab  where cid=500;            

11. 避免检索大字段。

12. 使用预编译(prepare),减少解析 防止注入。

13. 计数统一使用count(*)，而不是count(1)或count(pk)也不是count(col_name)。

当col_name 为空时count(col_name) 结果与count(*) 不一致，这里我们统一规定使用count(*) 而不是count(1) 或count(pk）                               

14.  in字句中不超过1000个常量。

15.  禁止SQL中笛卡尔集操作，即表Join时不指定关联字段。

16.  禁止查询结果非常大，建议使用Limit分段取结果。

3.4分页语句

1.   分页查询语句统一使用LIMIT row_count OFFSET offset形式编写。

2.   线上应用禁止LIMIT或OFFSET的值过大（超过10000），分页交互方面建议只显示相邻几页按钮。

3.   在一定适用范围内采用先分页后连接对多表关联分页查询SQL进行性能优化，或拆分为2个字句，先查出分页条件数据，拿到结果后再去查询其它所需字段。

具体参见下面优化示例

优化1 --单表分页查询优化：                                                   select id,poster, create_at,message from tab where created_at>'2014-01-01'  order by created_at desclimit 10 offset 1000;                                  

可以写成：                                                                   select tab.id,poster,create_at,message from tab,(select id from tab where   created_at>'2014-01-01'  order by created_at desc limit10 offset 1000) t where    tab.id=t.id                                                              

优化2--表join分页查询优化：                                             

原sql: select a.created_at,b.user_name,a.message  from message a,post b where a.post_id=b.idand created_at> '2014-01-01' order by a. created_at limit 10 offset 1000;                                                          

优化前提：id 是post 中为唯一键或主键，且message中id在post中都存在，否则转换后结果集可能不一致                                                 

优化结果：select a.created_at,b.user_name,a.message  from post           b,( select created_at, message,post_id frommessage where created_at>    '2014-01-01'order by created_at limit 10 offset 1000) a where           t.post_id=b.id;