OceanBase使用规范

本文介绍: 所有使用OceanBase的数据库。降低故障率和维护成本。

规约制定背景:

降低故障率和维护成本

适用范围:

所有使用OceanBase的数据库

【分区表设计建议】

关于分区表创建时的注意事项。
。单表行数可能超过10亿行或者单表容量超过200GB,推荐进行创建分区表。
。如果预计三年后的数据量根本达不到这个级别,请不要在创建表时使用分区表。
分区表在表创建的时候需要指定,后续不支持将非分区表在线改造成分区表,也不支持分区数量、分区类型、分区键值的在线调整。
分区表的主键和约束注意事项。
。建分区表时,表上的每一个主键、唯一键所对应的字段里都必须至少有一个字段包含在表的分区键字段中。
。分区表中的全局唯一性建议能通过主键实现的都通过主键3实现。
。分区表的唯一索引必须包含表分区的拆分键。
关于分区策略,推荐从表的实际用途和应用场景方面进行设计
。实际用途:历史表,流水表。
。应用场景:存在明显访问热点的表。
关于分区键的选择,使用分区表时要选择合适的拆分键以及拆分策略。
。hash分区:选择区分度较大、在查询条件中出现频率最高的的字段作为hash分区的分区键。
。range和list分区:根据业务规则选择合适的字段作为分区健,但分区数量不宜过少。示例:如果是日志类
型的大表,根据时间类型的列做range分区。
key分区:定义分区数时,需要选择质数个分区数,来协助数据分布均匀。
。Range分区:最后一列不能是maxvalue。
关于分区的使用限制:hash分区下,不适合基于分区字段进行范围查询。

【建表规约】

【强制】表必须定义主键,默认为ID,整型自增,如果不采用默认设计必须咨询DBA进行设计评估
【强制】建表时统一创建好主键。说明:OB主键暂不能修改
建议分区表的主键设置为(分区键,id),再加一个global的唯一索引(id,分区键)[OB的实现机制决定的]
【强制】多表中的相同列,必须保证列定义一致
【强制】表达是与否概念的字段,数据类型是unsigned tinyint(1表示是是,0表示否),值的内容要统一。正例:表达逻辑删除的字段名is_deleted,1表示删除,0表示未床删除。
【强制】表名、字段名必须使用小写字母或数字。禁止出现数字开头,禁止两个下划线中间只出现数字。数据库字段名的修改代价很大,所以字段名称需要慎重考虑。
【强制】禁用保留字,如desc、range、match、delayed等
【强制】唯一索引名为uniq_字段名;普通索引名则为idx_字段名。说明:uniq_即unique key;idx_即index的简称
【强制】小数类型为decimal,禁止使用float和double。
【强制】禁止使用外键,触发器,存储过程
【强制】所有的字符存储与表示,均以utf8mb4编码
【推荐】库名与应用名称尽量一致,表的命名最好能够清晰明了的表达表的作用,推荐使用”业务名称_表的作用”的命名方式
【推荐】如果修改字段含义或对字段表示的状态追加时,需要及时更新字段注释。
【推荐】字段允许适当冗余,以提高性能,但是必须考虑数据同步的情况。冗余字段应遵循:1)不是频繁修改的字段2)不是varchar超长字段
【强制】单分表行数可能超过10亿行或者单分表容量超过2000GB,才推荐进行分区表。说明:如果预计三年后的数据量根本达不到这个级别,请不要在创建表时使用分区表
【参考】合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。正例:无符号值可以避免误存负数,且扩大了表示范围

【索引规约】

【推荐】利用覆盖索引来进行查询操作,来避免回表操作。说明:索引中包含查询条件字段和结果集字段
【强制】【适用OB】索引修改流程为:先新建索引待新索引生效后后,确保旧索引无用再删除反例:idb直接修改索引;刚新建了最优索引,重复索引马上变更去删掉
【强制】索引建立需满足最左前缀原则
【强制】超过三个表禁止join。需要join的字段,数据类型保持绝对一致;多表关联查询时,保证被关联的字段需要有索引。说明:即使双表join也要注意表索引、SQL性能。
【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎擎来解决。说明:索引文件具有B-Tree的最左前缀匹配特性,如果左边的值未未确定,那么无法使用此索引。
【推荐】如果有order by的场景,请注意利用索引的有】序性。order by最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现file_sort的情况,影响查询性能。正例:where a=? and b=? order by c;索引:a_b_c。反例:索引中有范围查找,那么索引有序性无法利用,如:WHEREa>10 ORDERBYb;索引a_b无法排序。
【推荐】建组合索引的时候,区分度最高的在最左边。
正例:如果where a=? and b=?,a列的几乎接近于唯一值,那么只需要单建idx_a索引即可。说明:存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。如:where a>? and b=?那么即使a的区分度更高,也必须把b放在索引的最前列。

【SQL规约】

【强制】禁止使用非同类型的列进行等值查询。说明:如字段类型为varchar,where条件中字段内容为int,存在隐式转换问题,无法走索引近而可能拖垮数据库
【强制】orderby查询语句中,orderby的字段必须唯一或者组合唯一(非唯一字段排序,OB与MySQL表现不一致)
【强制】不要使用count(列名)或count(常量)来替代count(),ccount()就是SQL92定义的标准统计行数的语法,跟数据库无关,跟NULL和非NULL无关。说明:count(*)会统计值为NULL的行,而count(列名)不会统计此列为NULL值的行
【强制】不得使用外键与级联,一切外键概念必须在应用层解解决。说明:外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。
【强制】禁止使用跨库查询
【强制】禁止使用子查询,改为关联查询
【强制】禁止核心业务流程SQL包含:计算操作、多表关联、表遍历casewhen等复杂查询,建议拆分成单表简单查询
【强制】对于查询条件过滤性差的SQL,SQL绑定执行计划。说明:MySQL采用force index方式 OB采用hint/+index(tb tb_ix)/
打标签:Select/+aaaa/*from aa;
【推荐】优化超多分页场景
正例1:根据唯一键
select * from tb where c1 = ” and c2 = “and id > ” limit100;保留每100条的最后一个id为下100条的条件值(c1、c2、id建联合索引)
注意:
a.适用于数据分布较均匀,如果查询1页100条的数据代价为扫描几万或几十万性能不会到理想预期
b.适用不加其他条件只根据id分页捞全表(select*from tb where id >” limit 100;)
【推荐】【适用OB】in操作能避免则避免,若实在无法避免需要仔细评估in后边的集合元素数量,控制在100个之内,注意in最大个数8192,否则报错size overflow
【推荐】【适用OB】在业务cache表场景中,比如频繁insert/cdelete,数据生命周期较短的场景,需要添加HINT指定查询
【参考】删除或更新SQL需优化,核心规则是粒度尽量小(不大于100条)且where条件需索引优化
正例:先分页捞取数据,后根据id=等值条件或in范围条件进行删除除或更新覆盖SQL整个生命周期的稳定性治
理