MySQL库表设计规范

MySQL库表设计规范

本文仅针对 MySQL、Oracle

表设计

1)     表必须定义主键，默认为ID，整型自增，如果不采用默认设计必须咨询DBA进行设计评估

2)    ID字段作为自增主键，禁止在非事务内作为上下文作为条件进行数据传递，禁止非自增非数字类型主键设计出现

3)     禁止使用外键,触发器,存储过程

4)     多表中的相同列，必须保证列定义一致

5)    表默认使用InnoDB，国内表字符集默认使用utf8mb4，国际默认使用utf8的表

6)     表必须包含gmt_create和gmt_modified字段，即表必须包含记录创建时间和修改时间的字段

7)     单表一到两年内数据量超过500w或数据容量超过10G考虑分表，且需要提前考虑历史数据迁移或应用自行删除历史数据

8)    单条记录大小禁止超过8k（列长度(中文)*2（gbk）/3(utf8)+列长度(英文)*1）

9)    日志类数据不建议存储在MySQL上，优先考虑Hbase或OB，如需要存储请找DBA评估使用压缩表存储

10)  主键不允许修改，如特殊需求，需要提前一个月和DBA沟通方案

11)  绝对禁止使用MySQL保留关键字作为表名、列名、索引名等
       关键字列表：
       MySQL5.6：MySQL :: MySQL 8.4 Reference Manual :: 11.3 Keywords and Reserved Words
       MySQL5.5：http://dev.mysql.com/doc/refman/5.5/en/keywords.html

12）可变长度设计(例如varchar)一定要按照需要设计，同时控制单行长度，如果过大会降低数据库Buffer命中率，导致更新、查询性能下降

字段设计

1)     表被索引列必须定义为not null，并设置default值

2)     禁止使用float、double类型，建议使用decimal替代

3)     禁止使用blob、text类型保留大文本、文件、图片，建议使用其他方式存储（TFS/SFS/OSS），数据库(MySQL/Oracle)只保存指针信息

4)    禁止使用varchar类型作为主键语句设计

5）  Oracle里已上线表禁止将允许为空的列修改成非空列，否则锁表

索引设计

    1)    索引根据左前缀原则，当建立一个联合索引(a,b,c)，则查询条件里面只有包含(a)或(a,b)或(a,b,c)的时候才能走索引,(a,c)作为条件的时候只能使用到a列索引,所以这个时候要确定a的返回列一定不能太多，不然语句设计就不合理,(b,c)则不能走索引
    2)    索引不是越多越好，越多的索引带来的就是更高的索引维护成本，包含CPU计算消耗，索引建立时增加的IO开销等，因此一定要合理建立索引
    3)    联合索引应该选择筛选性更优的列值放在最前面，比如单号、userid等，type，status等筛选性一般的不建议放在最前面

语句设计

    1)    如果更新大量数据，可以采用两种方式：
        a) update tb set column='Yes' where col2='Init' limit 100;
        b)先select id from tb where col2='Init' limit 10000;
            然后循环做update tb ser column='Yes' where id in (xx,xx,xx,xx)，这里in值需要控制一定的数量，平时比较推荐的是200个id一条SQL

    2)    禁止使用非同类型的列进行等值查询！

其他

1)     禁止使用：存储过程、触发器、函数、视图、事件等MySQL高级功能

2)     禁止使用跨库查询

3)     禁止使用子查询，建议将子查询转换成关联查询

4)     禁止核心业务流程SQL包含：计算操作、多表关联、表遍历case when等复杂查询，建议拆分成单表简单查询

5)    varchar长度设计需要根据业务实际需要进行长度控制，禁止预留过长空间。例如status使用varchar(128)进行存储。原因是varchar类型虽然对于存储是根据实际长度进行存储，但内存分配则是根据指定长度进行分配，因此不合理的长度设计会导致内存的不合理占用

 OnlineDDL 限制

Table 14.8 Summary of Online Status for DDL Operations

Oracle库表设计规范

表的命名   

规范1： 表的命名总长度不能超过26位!

规范2： 表名由英文单词与下划线组成，命名方式：系统名_表功能名单词之间用下划线隔开，严禁使用中文拼音。
对于单词超长的，可使用单词缩写，但是单词缩写必须能够完整表达原单词的含义。
如：TRADE_BASE表，系统名为trade，而此表的含义是trade系统的基本信息表。

规范3： 临时表命名方式：TMP_表缩写_日期

字段的命名

规范1： 字段名必须非ORACLE关键字  

规范2： 字段名长度不得超过15位

规范3： 字段的命名需要有含义 字段命名要能真实表达字段的意义。
对于外键引用的字段，需要与主表的字段名保持一致。(备用字段命名：VAR1、VAR2等等)

规范4： 分库分表的字段名，字段类型和字段顺序要相同

约束的命名

规范1：  约束名的最大长度为26位

规范2： 主键约束：表名_PK

规范3： 唯一性约束：表名_U 
对于存在多个唯一性约束的表，唯一性约束的命名为：表名缩写_字段名_U

规范4： 检查约束：表名_C 对于存在多个检查约束的表，检查约束的命名为：表名缩写_字段名_C

表的创建规范

规范1： 表的设计须遵循第一范式，
尽量达到第二范式及第三范式(从实际情况出发，取平衡点。当有设计不遵循第一范式的情况，请及时与接口的开发DBA进行沟通)
即不允许字段出现二义性；
例如，表中有这样的字段，字段的值是由数位数字组成的代码，第一位表示客户类型，第二位表示渠道类型……，这种设计不符合第一范式。
1NF：关系模式R中的每一个具体关系r中，每个属性值都是不可再分的最小数据单位
2NF：关系模式R中的所有非主属性都完全依赖于任意一个候选关键字
3NF：关系模式R中的所有非主属性对任何候选关键字都不存在传递依赖
 
规范2： 表主键不可使用联合主键(分区表除外，可加上分区键作为主键)，分库分表的表必须有全局的主键

规范3： create table语句参数不能包含storage选项，不能包含nologging选项
因为DBA已经在表空间设置存储参数，不需要在表一级设置存储参数。

create table TRADE_BASE

(

  TRADE_NO      VARCHAR2(64),

  GMT_MODIFIED   TIMESTAMP,

  GMT_CREATE     TIMESTAMP

)tablespace ZHIFB_DATA

规范4： 每个表的字段数目不要超过100个，同时，表中一条记录所有字段的长度不能超过数据库的db_block_size大小(LOB类型的字段除外)
如果超过，可以创建多个小表的方式处理；如有特殊情况确实需要创建多字段的宽表，则按实际情况与数据库设计审核方讨论后决定。
目前的线上数据库中，DB_BLOCK_SIZE一般为8K，如果有不清楚的地方，可咨询接口的开发DBA。

规范5： 字段必须定义合适的数据类型。
在设计表结构时，只存储数字的字段定义成数字类型，只存储字符的字段定义成字符类型，只存储日期的字段定义成日期类型，以减少使用过程中的数据类型转换。
禁止使用long数据类型，因为long类型已淘汰，且oracle已经不再对LONG类型做后续开发。
若字段有记录大数据的情况，建议将数据保存到文件，然后字段里记录文件的路径。

规范6： 表和字段必须有comment中文注释。表和字段必须有中文注释，注释采用comment on的形式，如：
                Comment on table TRADE_BASE is ‘交易基本信息表，预估日增长量5000万’;
                Comment on column TRADE_BASE.TRADE_NO is ‘交易号’;
                
规范7： 根据更新的频繁程度决定字段的顺序。
为提高数据库效率，建议将更新频繁程度高的字段排在表中靠前的位置。
越靠后的字段效率越低：比如4AAAA6BBBBBB2CC，当要扫描到2CC时，数据库并不知道C的位置，
如果头占了10个长度，那么C的位移就是 10+(4+1)+(6+1)+1.每个字段没有直接的位移地址的，
既然没有23的位移，ORACLE只有通过前面的头，以及A B的位置来推算位移。
那么越往后面的字段，推算位移次数越多，CPU计算的次数也越多。

规范8： 除临时表以外，其他表必须有GMT_CREATE与GMT_MODIFIED字段在添加数据时，需要将GMT_CREATE与GMT_MODIFIED字段写入为SYSDATE；
在更新数据时，需要将GMT_MODIFIED字段修改为SYSDATE；GMT_CREATE与GMT_MODIFED字段类型一致，为TIMESTAMP

规范9： 创建表时，添加必要的约束。
添加主键约束：ALTER TABLE TRADE_BASE ADD CONSTRAINT TRADE_BASE_PK PRIMARY KEY(TRADE_NO) USING INDEX TABLESPACE ZHIFB_INDX;
添加唯一性约束：ALTER TABLE TRADE_BASE ADD CONSTRAINT TRADE_BASE_U UNIQUE(BUYER_ACCOUNT) USING INDEX TABLESPACE ZHIFB_INDX;

规范10：不在表中添加外键关联。为了提高数据库处理效率，不在表中添加外键的关联关系。该关系由应用来保证。

规范11：不添加带默认值的字段。可使用IBATIS的默认值，而不使用表字段默认值

规范12：对于长度为1的字段，建议使用VARCHAR2(1)。
如：状态字段，
alter table trade_base add （trade_status varchar2(1)）;
comment on column trade_base.trade_status is ‘交易状态：0,表示交易失败；1,表示交易成功’;

Sequence命名格式1 

sequence的命名格式为：”seq_”+表名

在一个表仅使用一个SEQUENCE的情况下，使用此命名方式

Sequence命名格式2 

“seq_”+表名+”_”+字段名。

特殊情况：如果同一个表上有多个字段使用SEQUENCE，则后创建的SEQUENCE使用此方式命名

例如：seq_trade_base_00_id，其中trade_base_00为表名，id为与sequence关联的字段名；如果命名的长度超过了oracle的规定（oracle规定30个字符），则表名与字段名都采用缩写的方式。

创建sequence的格式规范

建议1： create sequence 建议放在一行里，其它的minvalue、maxvalue、start with、increment by、cache、cycle、noorder选项建议各自单独一行，如下所示：

create sequence tradecore00.seq_trade_base_00

minvalue 1

maxvalue 999999

start with 1

increment by 1

cache 200

cycle

noorder;

其中：

(1) minvalue用于指定sequence的最小值。

(2) maxvalue用于指定sequence的最大值。

(3) start with用于指定sequence产生的第一个值。

(4) increment by用于指定步长。

(5) cache 用于让sequence预生成一些序列号cache在内存中。

(6) cycle 用于指定sequence到达最大值时从最小值又开始循环。

(7) noorder 用于指定sequence产生唯一的但不一定连续的序列号。

创建sequence应遵循的设计要求

规范1： 建议sequence都指定为循环模式（cycle）。
对使用循环的sequence，必须要考虑使用sequence值的字段是否作为主键或者其上是否有唯一性约束，
如果是，则需要采取措施防止字段值出现重复的情况(如：主键规则为YYYYMMDD+SEQUENCE)。
因特殊原因必须使用非循环模式(nocycle)的sequence，在指定其最大值时，必须要结合业务的量进行考虑，
必须要保证业务量不会超过sequence的最大值，
同时开发人员需要提供为什么要使用非循环(nocycle)模式及业务量是否会超过sequence的最大值的说明，由开发dba审核。
 
规范2： 要求所有的sequence都使用cache选项。
采用缓存（cache）技术，是为了减少对产生sequence值的等待。
CACHE SIZE的设置规则：MAX(200,可支撑5分钟业务调用的SEQUENCE量)。
在exclusive模式数据库中（单节点数据库）所有sequence的cache值最低为200；
可支撑5分钟业务调用的SEQUENCE量计算方法：如，目前交易高峰值500笔/秒，
那么需要的SEQUENCE CACHE量=500*5*60=150000，而交易库TRADE_BASE表有100个SEQUENCE，
因此TRADE_BASE各个SEQUENCE的CACHE量=150000/100=1500.

规范3： sequence的最大值不能超过字段宽度。
超过字段长度，得到的sequence值无法插入。
需要注意的是：部分使用SEUQNECE的字段，其值并不完全由SEQUENCE组成。
例如：ID字段长度是15，而ID是由8位日期+SEQUENCE组成，那么SEQUENCE的最大长度应该是9999999
 
规范4： 创建sequence时，必须指定minvalue、maxvalue、start with、increment by、cache的值
maxvalue的值要求设为全为9的数字，例如：maxvalue 999999 而不要设成maxvalue 911111。

规范5： 创建sequence的用户与使用此sequence的表的属主相同。
例如： trade_base_00表ID字段所使用的序列，其属主就是trade_base_00表的属主tradecore00，即表的属主和sequence的属主为同一个用户。

规范6： 建议使用noorder选项，如果业务对产生序列号的顺序有要求就用order。
在rac模式数据库中，使用noorder会减少节点之间数据交换，从而提高性能。

复杂语句设计规范

分页查询

每次查询返回条数控制在200条以内，当预计到查询的数据肯定会大量超过200时，需要进行分页查询。当条数较多是，请采用高效页面语句：
      select col1,col2,col3,col4 from table_name where id>(select id from table_name limit N,1) limit 200
其中N表示当前最小的分页码。例如分成3批，语句为:
      select col1,col2,col3,col4 from table_name where id>=(select id from table_name limit 0,1) limit 200;
      select col1,col2,col3,col4 from table_name where id>=(select id from table_name limit 200,1) limit 200;
      select col1,col2,col3,col4 from table_name where id>=(select id from table_name limit 400,1) limit 200;
这个方法只适用于全表数据分页,带有条件的分页查询参考下面的做法:

如果过滤条件里面没有order by

select t.*

         from (select id

                 from bbs_0009 g 

                where g.g_id = 154505

                   and g.deleted = 0

                   and (g.verify != 0 OR g.verify IS NULL)

                   and board_id = 50555

                   and (g.type = 0 or g.type = 2)

             limit 840, 40) g,

               bbs_0009 t

         where t.id = g.id;

如果有order by，请带上order by h后面的索引信息

select t.*

         from (select id

                 from bbs_0009 g force index (ind_group_type_time)

                where g.g_id = 154505

                   and g.deleted = 0

                   and (g.verify != 0 OR g.verify IS NULL)

                   and board_id = 50555

                   and (g.type = 0 or g.type = 2)

                 order by g.reply_time desc limit 840, 40) g,

               bbs_0009 t

         where t.id = g.id;

另外一个例子禁止使用:
      select xxx,xxx,xxx from tb_name where type='S' and id>(select id from tb_name where type='S' limit 401,1) limit 200;
      因为在这种情况下MySQL有可能会使用id作为索引,反而导致了全表查询.

Oracle->MySQL字段转换规则：

 
问题回复

1.为什么自增主键ID不能作为上下文?

        因为MySQL的主备在极端情况下有可能存在数据丢失的情况，那设想一种极端情况，主库宕机，备库没有接收到数据，那这个时候对于App来说，已经收到了主库给返回的自增ID例如是1004，在DBA做完主备切换以后，新主库原来可能只到了1003，这个时候业务再进行数据插入，那又返回了一个1004，这个时候就会有数据冲突，而且很有可能上游的1004对应的并不是这个时候的1004，造成数据错乱。因此强烈不建议业务在非事务里面使用自增主键做查询条件，或者作为上下文进行传输

2.为什么不允许使用外键

        从三个方面考虑:

              1)DB维护成本。增加了外键约束，在数据维护上会增加开销，不易维护

              2)DB性能影响。线上DB尽可能做到的是轻量，这样可以提供更高效的DB容量，而更多的DB功能带来的就是更多的额外开销

              3)升级风险。类似存储过程等功能在DB升级时可能会由于版本问题带来一些不一样的实现方式，因此使用额外功能需要谨慎
 

3.为什么列类型要保持一致

    因为列类型不一致的时候会导致走不上索引，所以需要保持在做等值查询的时候列类型一致

 

4.为什么禁止使用float、double?

    因为float、double属于浮点型数据，在插入的时候如果插入的数据精度不一致，数据库会做四舍五入，这样的话存储的数据和真实的业务数据就会不一致，导致业务异常，因此线上禁止使用浮点类型数据
 

5.数据更新建议使用二级索引先查询出主键，再根据主键进行数据更新.  不知道内部机制上有什么区别

    首先说清楚一个概念，那就是MySQL的行级锁是先锁住二级索引(如果走了二级索引的情况下)，然后再走主键索引锁行记录。当出现大量数据更新的时候，如果根据二级索引如果查询出来的数据很多，这个更新的话会锁住较多数据且时间较长，如果在这个期间有对使用到这些索引的更新就会出现等待，因此影响性能，DB的负载也会上升，而根据二级索引先查询出主键，再根据主键删除数据，那这个时候就只会锁住尽可能少的行数，性能更好。当然如果确认二级索引返回量少，时间够快，那直接使用二级索引删除可以减少QPS，也可以避免两次DAO调用，因此这个规范需要判断使用场景，不是万能方案！

6.为什么不建议使用大字段?

    第一，大字段比较容易产生页分裂，导致空间利用率较低，相应的会带来额外的IO开销、BP命中率下降、不能创建覆盖索引等问题。
    第二，线上由于大字段设计也发生过一些问题，比较常见的就是批量更新。因为有大字段，因此单行长度很长，当做批量更新的时候，会产生大量的Binlog，这个时候主库在发送Binlog的时候就会由于文件过大导致网卡打满的情况出现。
    因此我们不建议使用大字段设计，如果需要存储，建议对字符串进行压缩处理或者进行字段拆分处理。