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
Operation | In-Place? | Rebuilds Table? | Permits Concurrent DML? | Only Modifies Metadata? | Notes |
Yes* | No* | Yes | No | Restrictions apply for | |
Yes* | No* | No | No | Adding the first | |
Yes | No | Yes | Yes | Only modifies table metadata. | |
Yes* | Yes | Yes | No | Performed in-place as of MySQL 5.6.17. In-place operation is not supported for tables with | |
Set column default value | Yes | No | Yes | Yes | Only modifies table metadata. |
Change auto-increment value | Yes | No | Yes | No* | Modifies a value stored in memory, not the data file. |
Yes* | No | Yes | Yes | The | |
Yes | No | Yes | Yes | foreign_key_checks can be enabled or disabled. | |
Rename column | Yes | No | Yes* | Yes | To permit concurrent DML, keep the same data type and only change the column name. |
Add column | Yes | Yes | Yes* | No | Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation. |
Drop column | Yes | Yes | Yes | No | Data is reorganized substantially, making it an expensive operation. |
Reorder columns | Yes | Yes | Yes | No | Data is reorganized substantially, making it an expensive operation. |
Change | Yes | Yes | Yes | No | Data is reorganized substantially, making it an expensive operation. |
Change | Yes | Yes | Yes | No | Data is reorganized substantially, making it an expensive operation. |
Make column | Yes | Yes | Yes | No | Data is reorganized substantially, making it an expensive operation. |
Make column | Yes* | Yes | Yes | No |
|
Change column data type | No | Yes | No | No | Only supports |
Add primary key | Yes* | Yes | Yes | No | Data is reorganized substantially, making it an expensive operation. |
Drop primary key and add another | Yes | Yes | Yes | No | Data is reorganized substantially, making it an expensive operation. |
Drop primary key | No | Yes | No | No | Only |
Convert character set | No | Yes* | No | No | Rebuilds the table if the new character encoding is different. |
Specify character set | No | Yes* | No | No | Rebuilds the table if the new character encoding is different. |
Rebuild with | Yes* | Yes | Yes | No | Uses |
“null” rebuild using | Yes* | Yes | Yes | No | Uses |
Set | Yes | No | Yes | Yes | Only modifies table metadata. |
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字段转换规则：
Oracle | MySQL |
number(2) | tinyint |
number(4) | smallint |
number(6) | mediumint |
number(9) | int |
number | bigint |
char[最大2000字节] | char |
varchar2[最大4000字节] | varchar[最大65532字节] |
date | datetime |
问题回复
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的时候就会由于文件过大导致网卡打满的情况出现。
因此我们不建议使用大字段设计，如果需要存储，建议对字符串进行压缩处理或者进行字段拆分处理。
