【数据库】Oracle11g与Oceanbase3.2.3对比_oceanbase跟oracle

一、数据库体系结构对比

数据库的体系结构是从某一个角度来分析和考察数据库的组成、工作过程与原理，以及数据在数据库中的组织与管理机制。

Oracle11g作为传统关系数据库的代表、Oceanbase作为分布式关系数据库的代表，体系结构上的设计差别很大。

（一）Oracle11g体系结构

一般Oracle数据库（Oracle Database）可以分为两部分。
（1）实例（Instance）
实例是一个非固定的、基于内存的基本进程与内存结构。当服务器关闭后，实例也就不存在了。
（2）数据库（Database）
数据库指的是固定的、基于磁盘的数据文件、控制文件、日志文件、参数文件和归档日志文件等。

一个实例只能对应一个数据库。但是一个数据库有可能对应多个实例。除非使用并行Oracle服务器选项，在并行的数据库系统中，一个数据库会对应多个实例，同一时间用户只与一个实例相联系，当某一个实例出现故障时，其他实例自动服务，保证数据库正常运行。

在Oracle数据库系统中，用于存放数据库表、索引、回滚段等对象的磁盘逻辑空间叫表空间（tablespace）。数据库是通过表空间来存储物理表的，一个数据库实例可以有N个表空间，一个表空间下可以包含一个或者多个数据文件。数据文件（Datafile）是用于保存用户应用数据和Oracle系统内部数据的文件。

表空间是逻辑概念

一般在完成Oracle系统的安装并创建Oracle实例后，Oracle系统自动建立多个表空间。

Oracle 11g版本默认创建的主要表空间：

（1）SYSTEM表空间
SYSTEM表空间用于存放Oracle系统内部表和数据字典的数据，如表名、列名、用户名等。
不建议将用户创建的表、索引等存放在SYSTEM表空间中。

（2）撤销表空间
撤销表空间（Undo Tablesapce）用于存储撤销信息的表空间。当我们对数据库表进行修改（包括INSERT,UPDATE,DELETE操作）时，Oracle系统自动使用撤销表空间来临时存放修改前的数据（Before Image）。当所做的修改操作完成并提交（Commit）后，Oracle系统可根据需要保留修改前数据时间长短来释放撤销表空间的部分空间。

（3）USERS表空间
USER是Oracle建议用户使用的表空间，可以在这个表空间上创建各种对象，如创建表、索引等。

当我们创建表空间时至少需要创建一个以上的数据文件，Oracle创建数据文件时，实际上是将磁盘的操作系统块重新格式化成Oracle数据块，并且每个Oracle数据块都有唯一的标识。

对于传统数据库而言一般采用分库分表进行水平拓展。通过分库分表，可以快速实现数据库的水平扩展。采用这种方式，技术成本相对低，不需要改造核心数据库引擎，或者只需要做很少的改造。但是或多或少会存在一些问题：

1. 跨库分布式事务：数据库核心引擎没有分布式能力，只能通过中间件来完成分布式处理，但中间件难以做到RPO=0，因此在遇到异常和故障时无法100%保证分布式事务的ACID能力。
2. 全局一致性：由于多个数据库服务器的时间戳不一致，因此很难保证多个库之间数据版本号的全局一致性。
3. 负载均衡：扩容和缩容时，底层数据库引擎无法在线调整数据分布规则， 因此需要暂停业务并重新导数据，对业务和运维挑战很大跨库复杂SQL：
4. 跨库的复杂SQL运算（比如多表做分片键无关的关联查询）只能在中间件完成，而中间件不具备分布式并行计算能力，最终会限制应用对SQL的使用，产生业务侵入性

Oracle通过对自身的改进，引入了RAC在一定程度上规避了些上述的问题，但是和Oceanbase这种原生支持分布式的数据库比，存在差距。

Oracle RAC，全称是Oracle Real Application Cluster，翻译过来为Oracle真正的应用集群，它是Oracle提供的一个并行集群系统。

Oracle RAC的实质是位于不同操作系统的Oracle实例节点同时访问同一个Oracle数据库 ，每个节点间通过私有网络进行通信，互相监控节点的运行状态，Oracle数据库所有的数据文件、联机日志文件、控制文件等均放在集群的共享存储设备（可以是RAW、ASM、OCFS2等）上，所有集群节点可以同时读写共享存储。

共享存储设备有全部的数据文件，可以看出来oracle体系结构并不是天然支持分布式的。（横向扩展困难）

• 一个Oracle RAC数据库由多个服务器节点组成，每个节点上都有自己独立的OS、ClusterWare、Oracle RAC数据库程序等，每个节点都有自己的网络监听器。
• ClusterWare是一个集群软件，主要用于集群系统管理。
• Oracle RAC数据库程序用于提供Oracle实例进程，以供客户端访问集群系统。
• 监听服务主要用于监控自己的网络端口信息。
• 所有的服务和程序通过操作系统都去访问同一个共享存储，最终完成数据的读写。
• 共享存储实现方式有多种，如自动存储管理（ASM）、Oracle集群文件系统（OCFS）、裸设备（Raw）、网络区域存储（NAS）等来保证整个集群系统数据的一致性。

RAC数据库如何实现节点数据的一致性？

每个RAC实例的SGA内有一个buffer cache（缓冲区），通过Cache Fusion（缓存融合）技术，RAC在各个节点之间同步SGA中的缓存信息，从而保证节点数据的一致性，同时也提高集群的访问速度。（性能不如单机）

（二）Oceanbase3.2.3体系结构

OceanBase 数据库的一个集群由若干个节点组成。这些节点分属于若干个可用区（Zone）， 每个节点属于一个可用区。可用区是一个逻辑概念，表示集群内具有相似硬件可用性的一组节 点，它在不同的部署模式下代表不同的含义。例如，当整个集群部署在同一个数据中心（IDC）内的时候，一个可用区的节点可以属于同一个机架，同一个交换机等。当集群分布在
多个数据中心的时候，每个可用区可以对应于一个数据中心。

正是由于Oceanbase可用区的概念设计，分区对于Oceanbase而言是极为重要的概念。

为了使 OceanBase 数据库对应用程序屏蔽内部分区和副本分布等细节，使应用访问分布式数 据库像访问单机数据库一样简单，这里使用了 obproxy 代理服务。OBProxy是OceanBase数据库高性能反向代理服务器，它接收客户端的应用请求，并转发给OBServer。obproxy 是无状态的服务，多个 obproxy 节点通过网络负载均衡（SLB）对 应用提供统一的网络地址。

在集群的每个节点上会运行一个叫做 observer 的服务进程，它内部包含多个操作系统线程。 节点的功能都是对等的。每个服务负责自己所在节点上分区数据的存取，也负责路由到本机 的 SQL 语句的解析和执行。这些服务进程之间通过 TCP/IP 协议进行通信。同时，每个服务会 监听来自外部应用的连接请求，建立连接和数据库会话，并提供数据库服务。

为了简化大规模部署多个业务数据库的管理并降低资源成本，OceanBase 数据库提供了独特 的多租户特性。在一个 OceanBase 集群内，可以创建很多个互相之间隔离的数据库"实例"， 叫做一个租户。从应用程序的视角来看，每个租户等同于一个独立的数据库实例。租户是一个逻辑概念。在 OceanBase 数据库中，租户是资源分配的单位，是数据库对象管理 和资源管理的基础。

为了隔离租户的资源，每个 observer 进程内可以有多个属于不同租户的虚拟容器，叫做资源 单元（UNIT）。每个租户在多个节点上的资源单元组成一个资源池。资源单元包括 CPU 和内 存资源。OB的多租户资源隔离通过cgroup实现租户资源隔离，还无法实现磁盘空间的分配和IO隔离 （考虑资源竞争的情况）。

在 OceanBase 数据库中，一个表的数据可以按照某种划分规则水平拆分为多个分片，每个分 片叫做一个表分区，简称分区（Partition）。某行数据属于且只属于一个分区，一个表的若干个分区可以分布在一个可用区内的多个节点上。分区的规则由 用户在建表的时候指定，包括hash、range、list等类型的分区，还支持二级分区。分区就是表的子集。

OceanBase非分区表只有一个分区，非分区表不能添加分区，非分区表只会被创建在一个OBSever中无法在集群内多节点横向扩展。一个分区表可以有多个分区，单个分区只能在一个节点上，不同分区可以在不同节点上。

每个分区都有多个副本，自动建立paxos组，在分区级采用多副本保障数据可靠性和服务高可用。

在OB数据库中创建一张表的时候需要考虑采用哪种模式，是创建为分区表还是普通的表，如果创建表的时候不指定是分区表，那么这张表，无法在集群内多节点横向扩展。另外如果有多张表要进行JOIN，如果要JOIN的数据分别属于不同的OBSERVER管理，那么这种JOIN是跨网络的，其性能也会受到一定的影响。
如果表分区设计不合理，或者无较好的分区列，容易出现分区倾斜，数据热点等问题，需要熟悉对应的业务场景 。

为了能够保护数据，并在节点发生故障的时候不中断服务，每个分区有多个副本。一般来说， 一个分区的多个副本分散在多个不同的可用区里。多个副本中有且只有一个副本接受修改操 作，叫做主副本（Leader），其他副本叫做从副本（Follower）。主从副本之间通过分布式共识协议实现了副本之间数据的一致性。

对于分布式数据库, 多个表中的数据可能会分布在不同的机器上, 这样在执行 Join 查询或跨表 事务等复杂操作时就需要涉及跨机器的通信，而表组功能可以避免这种跨机器操作，从而提高 数据库性能。

将分区方式相同的表聚集到一起就形成了表组（以 Hash 分区为例，分区方式相同等价于分区个数相同，当然计算分区的 Hash 算法也是一样的），表组内每个表的同号分区称为一个分区组。

对于访问数据库的应用而言，逻辑上访问的只有一个表或一个索引，但是实际上这个表可能由 数十个物理分区对象组成，每个分区都是一个独立的对象，可以独自处理访问，也可以作为表 的一部分处理访问。分区对应用来说是完全透明的，不影响应用的业务逻辑。

OceanBase是鼓励使用分区的。

使用分区的好处如下:

1.提高了可用性
分区不可用并不意味着对象不可用。查询优化器自动从查询计划中删除未引用的分区。因此，当分区不可用时，查询不受影响。

2.更轻松地管理对象
分区对象具有可以集体或单独管理的片段。DDL语句可以操作分区而不是整个表或索引。因 此，可以对重建索引或表等资源密集型任务进行分解。例如，可以一次只移动一个分区。如 果出现问题，只需要重做分区移动，而不是表移动。此外，对分区进行 TRUNCATE 操作可 以避免大量数据被 DELETE 。

3.减少 OLTP 系统中共享资源的争用
在 TP 场景中，分区可以减少共享资源的争用。例如，DML 分布在许多分区而不是一个表 上。

4.增强数据仓库中的查询性能
在 AP 场景中，分区可以加快即席查询的处理速度。分区键有天然的过滤功能。例如，查询 一个季度的销售数据，当销售数据按照销售时间进行分区时，仅仅需要查询一个分区或者几个分区，而不是整个表。

5.提供更好的负载均衡效果
OceanBase 数据库的存储单位和负载均衡单位都是分区。不同的分区可以存储在不同的节 点。因此，一个分区表可以将不同的分区分布在不同的节点，这样可以将一个表的数据比较 均匀的分布在整个集群。

OceanBase和传统数据库的对比

从数据库体系结构可以看到，OceanBase不兼容Oracle，原因源于结构性差异，通过适配只能解决一部分的问题。具体使用时要考虑两者的差异性。

二、数据库兼容性对比

（一）事务隔离级别

四种隔离级别比较如下所示：

OceanBase 数据库（Oracle 模式）目前支持了以下几种隔离级别：
读已提交（Read Committed）
可重复读（Repeatable Read）
可串行化（Serializable）

Oracle上述隔离级别均支持。

OceanBase 和Oracle数据库默认的隔离级别为读已提交（Read Committed）。存在不可重复读和幻读的可能性。

不过对于读未提交的隔离级别只有理论上的意义，没有实践上的价值。

（二）数据组织结构

二叉树

任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值

由于二叉树的根节点是确定不变的，所以当调整数据的插入或删除顺序，会造成二叉树朝着单项链表的方向发展，大大降低了数据的查询效率。

平衡二叉树/AVL树
当二叉树非常极端，变成一个链表后，它就没有了二叉树的相关优秀性质了。所以我们在insert节点的时候，需要不断的旋转，来使二叉树平衡，最终使得其查询效率最高。调整一共分为四种情况：LL，RR，LR，RL

B-树

因为数据库中大部分数据都存在于磁盘，但是IO一次磁盘的代价相对来说比较大，我们需要尽可能的减少AVL树的深度，即增加每个节点的数据量。这便是B-树的由来。
每一个节点称为页，也就是一个磁盘块。 B-树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点

B-树的查询流程：
如上图我要从找到E字母，查找流程如下：
1）获取根节点的关键字进行比较，当前根节点关键字为M，E<M（26个字母顺序），所以往找到指向左边的子节点（二分法规则，左小右大，左边放小于当前节点值的子节点、右边放大于当前节点值的子节点）。
2）拿到关键字D和G，D<E<G 所以直接找到D和G中间的节点。
3）拿到E和F，因为E=E 所以直接返回关键字和指针信息（如果树结构里面没有包含所要查找的节点则返回null）。
4）通过指针信息取出这条记录的所有信息。

因为 B 树索引在数据更新和删除时的维护成本相对较低，Oracle的数据结构组织形式采用B树。

B+树

是B-树的变形，相对于B-树来说，B+树最主要的不同之处就是其非叶子节点上是不存储数据的，数据全在叶子节点存储。这就意味着B+树比B-树更胖。因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。

B+树和B-树的区别是：
1）B-树的节点(根节点/父节点/中间节点/叶子节点)中没有重复元素，B+树有。
2）B-树的中间节点会存储数据指针信息，而B+树只有叶子节点才存储。
3）B+树的每个叶子节点有一个指针指向下一个节点，把所有的叶子节点串在了一起。
从下图我们可以直观的看到B-树和B+树的区别：紫红色的箭头是指向被索引的数据的指针，大红色的箭头即指向下一个叶子节点的指针。

B+树是B-树的改良版，但是索引在数据更新和删除时效率会更低。

Mysql的数据组织形式采用B+树。

LSM树

LSM树，即日志结构合并树(Log-Structured Merge-Tree)。 其实它并不属于一个具体的数据结构，它更多是一种数据结构的结合。 大多NoSQL数据库核心思想都是基于LSM来做的， 只是具体的实现不同。LSM树由两个或以上的存储结构组成，存在多种实现方式。
它的核心思路其实非常简单，就是假定内存足够大，因此不需要每次有数据更新就必须将数据写入到磁盘中， 而可以先将最新的数据驻留在内存中，等到积累到最后多之后，再使用归并排序的方式将内存内的数据合并追加到磁盘队尾 (因为所有待排序的树都是有序的，可以通过合并排序的方式快速合并到一起)。

LSM具有批量特性，存储延迟。当写读比例很大的时候（写比读多），LSM树相比于B树有更好的性能。因为随着insert操作，为了维护B树结构，节点分裂。 读磁盘的随机读写概率会变大，性能会逐渐减弱。 多次单页随机写，变成一次多页随机写,复用了磁盘寻道时间，极大提升效率。

核心思想的核心就是放弃部分读能力，换取写入的最大化能力。

OceanBase 数据库的存储引擎基于 LSM-Tree 架构，将数据分为 静态基线数据 （放在 SSTable 中）和 动态增量数据 （放在 MemTable 中）两部分，其中 SSTable 是只读的，一旦生成就不再被修改，存储于磁盘；MemTable 支持读写，存储于内存。数据库 DML 操作插入、更新、删除等首先写入 MemTable，等到 MemTable 达到一定大小时转储到磁盘成为 SSTable。在进行查询时，需要分别对 SSTable 和 MemTable 进行查询，并将查询结果进行归并，返回给 SQL 层归并后的查询结果。同时在内存实现了 Block Cache 和 Row cache，来避免对基线数据的随机读。

OceanBase 数据库的内存存储引擎 MemTable 由 BTree 和 Hashtable 组成，在插入/更新/删除数据时，数据被写入内存块，在 HashTable 和 BTree 中存储的均为指向对应数据的指针。

当 MemTable 的大小达到某个阈值后，OceanBase 数据库会将 MemTable 中的数据转存于磁盘上，转储后的结构称之为 SSTable。

OceanBase采用LSM进行数据结构的组织。这就导致OceanBase对内存要求极高。基于LSM树结构的缺点，内存和磁盘数据合并时需要同时保存前后两个版本的数据，至少需要2倍的磁盘空间。

（三）OB-SQL语法差异与实践

OceanBase3.2.3 Oracle模式是兼容Oracle11g语法的，支持90%的Oracle数据类型和内置函数； 支持分布式执行的存储过程（PL/SQL）；

1、建表

在OB中，非分区表作为一个独立的分区表对待 。如果数据量比较大，建议建设分区。如果不指定分区表，默认为非分区表，非分区表后续不能添加分区。

OB建表设计主键或者唯一键（对提高性能和后期维护有益）

如果没有合适的字段作为主键，可以使用一个序列号（sequence）填充的数值列作为主键。

ALTER TABLE 语法不支持创建表以后添加主键，所以在建表的时候需要决定主键列

注：如果建表时没有指定主键列，OB会自动使用一个隐含列作为主键，此列对用户不可见。

2、复制表的数据

Oracle 租户不支持create table like …语法，Oracle 租户支持 CREATE TABLE AS SELECT 复制表的基本数据类型和数据，但不包含约束、索引和非空等属性。

3、表组

表组是OB特有的概念。

表组是表的属性，影响多个表分区在 OBServer 上的分布特征。

为降低分布式访问的影响，OceanBase建议对业务上关系密切的表，设置相同的表组。 OceanBase 对于同一个表组中表的同号分区会放置在一个分区组内。同一个分区组中的分 区，OceanBase 会将其尽可能的分配到同一个节点内部，尽量规避跨节点的请求。

创建表组时，首先要规划好表组的用途。如果是用于普通表（单分区），表组不用分区； 如果是用于分区表的属性，表组就要指定分区策略，并且要跟分区表的分区策略保持一致。

创建表组的语法在OceanBase不同版本是有差异的。

表创建后加入到表组有两种方式：
• 修改表的表组属性
• 使用 ALTER TABLEGROUP ADD 语法，对一个表组增加多张表。

ALTER TABLEGROUP tablegroup_name ADD [TABLE] table_name [, table_name...];
1

查看表组信息：

4、分区

分区是OB最重要的设计之一。相比Oracle，除了分区物理模型上的不同，OB在逻辑模型上多了二级分区的设计。

OB 现在支持的一级分区类型有：HASH， KEY， LIST，RANGE，RANGE COLOMNS，生成列分区。

HASH 分区

HASH分区需要指定分区键和分区个数。通过HASH的分区表达式计算得到一个int类型的结果，这个结果再跟分区个 数取模得到具体这行数据属于那个分区。通常用于给定分区键的点查询，例如按照用户id来分区。 HASH分区通常 能消除热点查询。

create table t1 (c1 int, c2 int) partition by hash(c1 + 1) partitions 5
1

其中partition by hash(c1+1)指定了分区键c1和分区表达式c1 + 1；
partitions 5指定了分区数

KEY 分区

KEY分区与HASH分区类似，不同在于：

• 使用系统提供的HASH函数(murmurhash)对涉及的分区键进行计算后再分区，不允许使用用户自定义的表达式。
• 用户通常没有办法自己通过简单的计算来得知某一行属于哪个分区。
• 测试发现KEY分区所用到的HASH函数不太均匀。

create table t1 (c1 varchar(16), c2 int) partition by key(c1) partitions 5
1

• KEY分区不要求是int类型，可以是任意类型（与HASH分区区别）
• KEY分区不能写表达式（与HASH分区区别）
• KEY分区支持向量（与HASH分区区别）
• KEY分区有一个特殊的语法

create table t1 (c1 int primary key, c2 int) partition by key() partitions 5
1

KEY分区分区键不写任何column，表示key分区的列是主键

LIST 分区

LIST分区是根据枚举类型的值来划分分区的，主要用于枚举类型
LIST分区的限制和要求

• 分区表达式的结果必须是int类型
• 不能写向量，例如partition by list(c1, c2)

create table t1 (c1 int, c2 int) partition by list(c1) 
(partition p0 values in (1,2,3),
partition p1 values in (5, 6)，
 partition p2 values in (default));
1
2
3
4

List columns 和 list 的区别是：
●list columns分区不要求是int类型，可以是任意类型
●list columns分区不能写表达式
●list columns分区支持向量

RANGE 分区

RANGE分区是按用户指定的表达式范围将每一条记录划分到不同分区 常用场景： 按时间字段进行分区
目前提供对range分区的分区操作功能，能add/drop分区
add分区现在只能加在最后，可以存在maxvalue的分区，但是不建议。

CREATE TABLE `info_t`(id INT, gmt_create TIMESTAMP, info VARCHAR(20), PRIMARY KEY (gmt_create)) PARTITION BY RANGE(UNIX_TIMESTAMP(gmt_create))
(PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')), PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')), PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2017-01-01 00:00:00')));
1
2

RANGE COLUMNS 分区

RANGE COLUMNS分区与RANGE分区类似，但不同点在于RANGE COLUMNS分区可以按一个或多个分区键向量 进行分区，并且每个分区键的类型除了INT类型还可以支持其他类型，比如VARCHAR、DATETIME等
RANGE COLUMNS和RANGE的区别是

• RANGE COLUMNS分区不要求是int类型，可以是任意类型
• RANGE COLUMNS分区不能写表达
• RANGE COLUMNS分区支持向量

CREATE TABLE `info_t`(id INT, gmt_create DATETIME, info VARCHAR(20), PRIMARY KEY (gmt_create)) PARTITION BY RANGE COLUMNS(gmt_create)
(PARTITION p0 VALUES LESS THAN ('2015-01-01 00:00:00’), PARTITION p1 VALUES LESS THAN ('2016-01-01 00:00:00’), PARTITION p2 VALUES LESS THAN ('2017-01-01 00:00:00’), PARTITION p3 VALUES LESS THAN (MAXVALUE));
1
2

生成列分区

• 生成列是指这一列是由其他列计算而得
• 生成列分区是指将生成列作为分区键进行分区，该功能能够更好的满足期望将某些字段进行一定处理后作为分区键 的需求（比如提取一个字段的一部分，作为分区键）

CREATE TABLE gc_part_t(t_key varchar(10) PRIMARY KEY, gc_user_id VARCHAR(4) GENERATED ALWAYS AS (SUBSTRING(t_key, 1, 4)) VIRTUAL, c3 INT)
PARTITION BY KEY(gc_user_id) PARTITIONS 10;
1
2

二级分区相当于在一级分区的基础上，又从第二个维度进行了拆分。最常用的地方就是类似用户账单领域，会按照 user_id 做 HASH 分区，按照账单创建时间做 RANGE 分区。

MySQL 模式

• RANGE 分区
• RANGE COLUMNS 分区
• LIST 分区
• LIST COLUMNS 分区
• HASH 分区
• KEY 分区
• 组合分区

Oracle 模式

• RANGE 分区
• LIST 分区
• HASH 分区
• 组合分区

二级分区支持的分区方式

• HASH/KEY + RANGE/RANGE_COLUMNS 分区
• RANGE/RANGE_COLUMNS + HASH/KEY 分区
• LIST/LIST_COLUMNS+ RANGE/RANGE_COLUMNS 分区
• RANGE/RANGE_COLUMNS + LIST/LIST_COLUMNS分区
• HASH/KEY + LIST/LIST_COLUMNS 分区
• LIST/LIST_COLUMNS+HASH/KEY 分区

对于 RANGE 分区的分区操作 add/drop，必须是 RANGE 分区做为一级分区的方式。所以强烈建议用 RANGE + HASH 的分区方式，而不是 HASH + RANGE。

分区管理

增加分区
对于按时间范围分区的表，有时需要作过期数据清理 -> ADD PARTITION
语法: ALTER TABLE … ADD PARTITION

删除分区
伴随数据量增长，range分区需要能够扩展 -> DROP PARTITION
语法: ALTER TABLE … DROP PARTITION

ALTER TABLE members ADD PARTITION(PARTITION p3 VALUES LESS THAN(2000));
ALTER TABLE members DROP PARTITION(P3);
1
2

分区表最佳实践

1. 通常当表的数据量非常大,导致数据库空间紧张或者 SQL 查询性能变慢时，可以考虑使用 分区表。
2. 使用分区表时要选择合适的拆分键以及拆分策略。如果是日志类型的大表，根据时间类型 的列做 RANGE 分区。如果是访问高并发的表时，结合业务特点选择能满足绝大部分核心 业务查询的列作为拆分键。
3. 分区表中的全局唯一性可通过主键和唯一键约束实现。

更新分区键注意事项

• 针对分区键做 UPDATE 时，理论上记录有可能需要从一个分区移动到另外一个分区
• 如果系统检测到 UPDATE 操作会导致分区移动，则会禁止这种更新

obclient> create table t_part(id number not null , c1 varchar2(10) not null, c2 varchar2(100), primary key(id,c1)) partition by hash(c1) partitions 8; 
Query OK, 0 rows affected (0.29 sec)

obclient> insert into t_part(id,c1,c2)
values(1,'A','aaaaaaaa'),(2,'B','bbbbbbbbb'),(3,'C','ccccccccc'),(4,'D','dddddddd’); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0

obclient> select * from t_part;
+----+----+-----------+
| ID | C1 | C2 |
+----+----+-----------+
| 3 | C | ccccccccc |
| 2 | B | bbbbbbbbb |
| 4 | D | dddddddd |
| 1 | A | aaaaaaaa |
+----+----+-----------+
4 rows in set (0.05 sec)

obclient> update t_part set c1='CC' where c1='C*’;
ORA-14402: updating partition key column would cause a partition change
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

5、自增列

自增列和序列均提供顺序递增的唯一值

• 自增列作为column，隶属于table
• 序列是一个独立的数据对象，不隶属于任何一个表

Oracle模式不支持自增列（同Oracle），可以使用序列（sequence）实现自增列的功能

MySQL模式（3.2版本及以上）既支持自增列（identity），又支持序列（sequence）。自增列功能和表强绑定，提供了两个独特的特性：

• 递增特性
• 指定值 N 插入后，后继自动生成的值总大于 N

因为存在切主等导致自增列缓存被动刷新的场景，自增列会跳变，建议使用BIGINT类型来定义自增列。

自增列的属性由租户变量统计管理：

• auto_increment_cache_size:cache 大小，默认值1000000
• auto_increment_increment：步长，默认值1
• auto_increment_offset：起始值，默认值1

在分区表中，自增列保证分区内有序，不能保证分区间有序

6、序列

目前OceanBase中序列仅支持两种模式

CREATE SEQUENCE sequence_name
[MINVALUE value | NOMINVALUE]
[MAXVALUE value | NOMAXVALUE]
[START WITH value]
[INCREMENT BY value]
[CACHE value | NOCACHE]
[ORDER | NOORDER]
[CYCLE | NOCYCLE];
1
2
3
4
5
6
7
8

出于性能考虑，OceanBase 数据库建议使用默认的 CACHE with NOORDER方式。

OceanBase 数据库中，不建议使用 ORDER with NO CACHE。这种模式下，每次调用 NEXTVAL 都会触发一次内部表 SELECT 与 UPDATE 操作，会影响数据库的性能。

在创建序列时，由于默认的 CACHE 值只有 20，需要手动声明一个比较大的值。CACHE 每次耗尽后，都会触发一次网络缓存刷新。对于单机 TPS 为 100 时，CACHE SIZE 建议设置为 360000。

在分布式系统中使用 Sequence 时，如果使用了 CACHE + NOORDER 选项，那么连接到不同机器时获得的序列不是有序递增的，但是可以保证唯一。

7、索引

传统的“非”分区表中，主表和索引的对应关系：
主表的所有数据都保存在一个完整的数据结构中，主表上的每一个索引也对应一个完整的数据结构（比如最常见的B+ Tree），主表的 数据结构和索引的数据结构之间是一对一的关系，如下图所展示，在 employee表中，以 emp_创建的索引：

Oracle为B树，Mysql为B+树。

对于OB而言，由于分区表的特性，情况发生了变化：
主表的数据按照分区键（Partitioning Key）的值被分成了多个分区，每个分区 都是独立的数据结构，分区之间的数据没有交集。这样一来，索引所依赖的单一数据结构不复存在。

这就引入了“局部索引”和“全局索引”两个概念。

局部索引又名分区索引，创建索引的分区关键字是LOCAL；分区键等同于表的分区键，分区数等同于表的分区数；局部索引的分区机制和表的分区机制一样。

全局索引的创建规则是在索引属性中指定GLOBAL关键字；全局索引最大的特点是全局索引的分区规则跟表分区是相互独立的；全局索引允许指定自己的分区规则和分区个数，不一定 需要跟表分区规则保持一致。

分区表的局部索引和非分区表的索引类似，索引的数据结构还是和主表的数据结构保持一对一的关系，但由于主表已经做了分区，主表的“每一个分区”都会有自己单独的索引数据结构。 局部索引的结构如下图所示：

分区表的全局索引不再和主表的分区保持一对一的关系，而是将所有主表分区的数据合成一个整体来建立全局索引。更进一步，全局索引可以定义自己独立的数据分布模式，既可以选择非分区模式也可以选择分区模式

• 全局非分区索引（Global Non-Partitioned Index）
  
• 全局分区索引（Global Partitioned Index）
  
  在表的“分区键无关”的字段上建唯一索引
  局部索引在“索引键没有包含主表所有的分区键字段”的情况下，此时索引键值对应的索引数据在所有分区中都可能存在。如下图， employee按照emp_id做了分区，但同时想利用局部索引建立关于emp_name的唯一约束是无法实现的 。
  
  由于某索引键值在所有分区的局部索引上都可能存在，索引扫描必须在所有的分区上都做一遍，以免造成数据遗漏。这会导致索引扫描 效率低下，并且会在全局范围内造成CPU和IO资源的浪费。

全局非分区索引与全局分区索引的比较

全局非分区索引：
此时索引的结构和“非分区”表没有区别，只有一个完整的索引树，自然保证唯一性。
因为只有一个完整的索引树，自然没有多分区扫描的问题。

全局分区索引：
数据只可能落在一个固定的索引分区中，因此每一个索引分区内保证唯一性约束，就能在全表范围内保证唯一性约束。
全局索引能保证某一个索引键的数据只落在一个固定的索引分区中，所以无论是针对固定键值的索引扫描，还是针对一个键值范围的索引扫描，都可以直接定位出需要扫描的一个或者几个分区。

局部索引与全局索引的取舍
如果查询条件里“包含完整的分区键”，使用本地索引是最高效的。
如果需要“不包含完整分区键”的唯一约束，

• 用全局索引
• 或者本地索引，且需要索引列上必须带上表的分区键

其它情况：

• 通常来说，全局索引能为高频且精准命中的查询（比如单记录查询）提速并减少IO；对范围查询则不一定 哪种索引效果更好。
• 不能忽视全局索引在DML语句中引入的额外开销：数据更新时带来的跨机分布式事务，事务的数据量越大 则分布式事务越复杂。

如果数据量较大，或者容易出现索引热点，可考虑创建全局分区索引。

如果全局索引的分区规则和主表的分区规则相同并且分区数相同，这时推荐创建一个局部索引。

非必要情况下，不推荐使用全局索引：一方面是因为全局索引的维护代价更大；另一方面是因为全局索引无法 保证和主表分区的物理位置相同，除非将其和主表指定在一个表组中。

创建索引
OceanBase 数据库支持在非分区表和分区表上创建索引，索引可以是局部索引或全局索引，也可以是唯一索引或 普通索引。如果是分区表的唯一索引，则唯一索引必须包含表分区的拆分键。

可以对一张表的单列或多列创建索引来提高表查询速度。创建合适的索引，能够减少对磁盘的读写

• 建表的时候创建，立即生效。
• 建表后再创建索引，是同步生效，表中数据量大时需要等待一段时间。

CREATE [UNIQUE] INDEX index_name ON table_name ( column_list ) [LOCAL | GLOBAL] [ PARTITION BY column_list PARTITIONS N ] ;
1

OB高效索引实践

1. 索引表与普通数据表一样都是实体表，在数据表进行更新的时候会先更新索引表然后再更新数据表。
2. 索引要全部包含所查询的列：包含的列越全越好，这样可以尽可能的减少回表的行数。
3. 等值条件永远放在最前面。
4. 过滤与排序数据量大的放前面。
5. 索引表与普通数据表一样都是实体表，在数据表进行更新的时候会先更新索引表然后再更新数据表。
6. 索引要全部包含所查询的列：包含的列越全越好，这样可以尽可能的减少回表的行数。
7. 等值条件永远放在最前面。
8. 过滤与排序数据量大的放前面。
9. 选择具有高选择性、频繁在where 从句中出现、频繁在join关联字段中的字段。
10. 不对函数或表达式中的字段建索引，要么就建函数索引。
11. 创建一个索引时，评估该索引给查询带来的性能优化是否比因其而引起INSERT，UPDATE，DELETE操作的性能下降以及索引占用的空间更要值得。
12. 在常被修改到字段上建索引需要进行评估。

OB索引属性

• OceanBase 数据库的表存储模型为索引聚集表模型(IOT)，如果用户未指定主键，系统会 自动生成一个隐藏主键。
• 支持在非分区表和分区表上创建索引，索引可以是局部索引或全局索引，也可以是唯一索 引或普通索引。
• 如果是分区表的唯一索引，则唯一索引必须包含表分区的拆分键。
  

条件的先后顺序不影响索引能效，如where A = ? and B = ? 和 where B = ? and A = ? 效果相同 从索引能效来看: 【Where A =? And B=? and C=?】>【Where A=? and B=? 】> 【Where A=? and C=?】

创建索引-范围查询

常见的范围查询有： 大于、小于、大于等于、小于等于、between…and 、 in(?,?)

遇到第一个范围查询字段后，后续的字段不参与索引过滤（不走索引）
如【where A > ? and B > ? and C < ?】、【where A > ? and B > ?】 、【where A > ? and C < ?】 只能走A字段的索引

创建索引-等值和范围查询

遇到第一个范围查询字段后，后续的字段不参与索引过滤（不走索引）
从索引能效看：【where A = ? and B = ? and C > ?】>【where A = ? and B > ? and C > ?】 【where A = ? and B > ? and C = ?】=【where A = ? and B > ? and C > ?】

8、租户

租户是OB特有的设计。

将数据库集群按指定规格（CPU、内存、存储、TPS、QPS）划分 成多个资源池，分配给不同的租户；

租户资源隔离策略：内存物理隔离；CPU逻辑隔离，数据隔离；

一般一个应用占用一个租户。

创建租户一般要三个步骤：

步骤一、创建“资源单元规格”，create resource unit命令，指定资源单元的规格；

CREATE RESOURCE UNIT unit1
max_cpu = 4,
max_memory = 10737418240, -- 10GB min_memory = 10737418240, -- 10GB max_iops = 1000,
min_iops = 128,
max_session_num = 300,
max_disk_size = 21474836480 -- 20GB ;
1
2
3
4
5
6

步骤二、创建“资源池”，create resource pool命令，根据资源单元规格的定义创建资源单元，并赋给一个新的资源池；

CREATE RESOURCE POOL pool1
UNIT = 'unit1',
UNIT_NUM = 1,
ZONE_LIST = ('zone1', 'zone2', 'zone3');
1
2
3
4

• 每个resource pool在每个OB Server上只能有一个resource unit；如果unit_num大于1，每个 zone内都必须有和unit_num对应数目的机器
• zone List一般与zone个数保持一致
• 如果在某个zone内找不到有足够剩余资源的机器来创建resource unit，资源池会创建失败

步骤三、 创建租户，create tenant命令，将资源池赋给一个新的租户；

CREATE TENANT oracle_tenant
RESOURCE_POOL_LIST = ('pool1'),
primary_zone = 'zone1,zone2,zone3'
set ob_tcp_invited_nodes = '%', ob_compatibility_mode = 'oracle', recyclebin = off, ob_timestamp_service = 'GTS’;
1
2
3
4

• Primary Zone：指定主副本分配到Zone内的优先级，逗号两侧优先级相同，分号左侧优先级 高于右侧。

9、其它兼容问题

Oceanase的Oracle模式：兼容Oracle的视图、基础数据类型、SQL功能、数据库对象和PL功能。（兼容90%以上的内容）
Oceanase的MySQL模式：OceanBase 4.0 版本之前兼容 MySQL 5.7，4.0 版本之后兼容MySQL 8.0。

主要的不兼容项功能有（以OceanBase 3.2.3 版本为例）：

• 不支持 创建表后修改主键。
• 不支持 时态表（temporal table）。
• 不支持 物化视图（materialized view）。
• LOB字段最大 48 MB，且性能不佳，不建议在复杂场景下使用
• 不支持 SQL并行的Auto DOP 功能，需要通过 hint/session 变量指定并行度不支持执行租户内数据库级或表级的备份和恢复。
• DBLink功能只支持连接Oracle数据库或OceanBase的Oracle租户，且不支持通过DBLink修改、插入或者删除数据，不建议在复杂场景下使用。
• 分区表不支持自动分区，需要从应用层面实现分区的添加。

10、OB库连接建议

如果是Oracle租户，可以直接使用连接串进行连接：

$ obclient -h192.168.1.101 -usys@t_oracle0_91#obdoc -P2883 -pabcABC123 -c -A sys
1

-h：OceanBase数据库连接的IP，通常是一个 OBProxy 或OBserver地址。
-u：租户的连接帐户，有两种格式：“用户名@租户名#集群名或者“集群名:租户名:用户名”。Oracle 租户的管理员用户名默认是sys。
-P：OceanBase 数据库连接端口，也是 OBProxy 的监听端口，默认是 2883，可自定义。
-p：帐户密码。为了安全可以不提供，改为在后面提示符下输入，密码文本不可见。
-c：表示在将 SQL 语句中的注释发往数据库端。
-A：表示在连接数据库时不去获取全部表信息，可以使登录数据库速度最快。 sys：访问的数据库名，可以改为业务数据库

需要注意的是，在yaml配置数据库连接的时候，最好明确指定是Oracle模式，不然可能会出现问题（行内某系统因为分页查询出现过生产问题，指定Oracle模式得以解决，原因是框架的兼容问题）。配置信息如下：

三、认识ODC开发者工具

OceanBase 开发者中心（OceanBase Developer Center，ODC）是为 OceanBase 数据库量身打造的企业级数据库 开发平台。ODC 支持连接 OceanBase 中 MySQL 和 Oracle 模式下的数据库，同时为数据库开发者提供了数据库日常开发操作、WebSQL、SQL 诊断、会话管理和数据导入导出等功能。

（一）创建链接

进入 ODC 首页后，在右上角单击“新建连接 ”按钮进行连接配置，首页的连接列表中可查看已保存的数据库连接。

（二）SQL窗口

单击上方导航栏中的 工作台 标签，在弹出下拉菜单中单击 SQL 窗口 以完成新建。 SQL 窗口中提供了可编辑脚本的 SQL 编辑区域，显示运行结果的执行记录页签与结果页签，同时也支持运行 PL 语句。

（三）命令行窗口

单击上方导航栏中的 工作台 标签，在弹出下拉菜单中单击 命令行窗口 （最多同时新建 3 个命令行窗口）以完成新建。 打开的窗口会自动连接到当前实例并显示一段默认代码展示连接的 ID、版本信息和帮助信息。

（四）任务管理

ODC提供的各种工具功能会生成对应的任务，可在任务中心查看对应的任务状态和任务详情信息。 登录 ODC 后，单击上方导航栏中的 任务 按钮会弹出 任务中心 面板，在面板中可查看对应任务的详细信息。

（五）导入任务

创建批量导入和单表导入任务后，可以进入目标数据库连接，单击上方导航栏中的任务标签弹出任务中心面板，在面板中单击导入页签展示任务列表。

任务列表仅会展示最近 48 小时内的任务，导入任务最大支持 3 个 并行，后续任务在队列中等待运行。

任务状态用于展示任务的当前状态，有 运行中、已终止、完成 和 失败 几种状态，不同状态可以对应 查看、重试、终止和删除 等不同的任务管理操作。

单击操作列中的查看按钮弹出目标任务的任务详情面板，在面板的右上角单击任务信息标签查看任务基本信息、导入文件信息和导入对象信息；单击 任务日志 标签查看任务的全部日志和告警日志，有 INFO、ERROR 和 WARN几种级别。

（六）模拟数据任务

创建异步执行任务后，可以进入目标数据库连接，单击上方导航栏中的 任务 标签弹出任务中心面板，在面板中单击 模 拟数据 页签展示任务列表。

任务状态用于展示任务的当前状态，有 运行中、已终止、完成 和 失败 几种状态，不同状态可以对应 下载、查看、终 止 和 删除 等不同的任务管理操作。

单击操作列中的 查看 按钮弹出目标任务的任务详情面板，在面板的右上角单击任务信息标签查看任务基本信息和模 拟数据设置信息；单击 任务日志 标签查看任务的全部日志和告警日志，有 INFO、ERROR 和 WARN几种级别。

（七）会话管理

这个需要有管理员权限。

应用与数据库的连接被称为一个会话，在 ODC 会话管理页面可以查看连接到当前数据库所有会话的详细信息。 进入 ODC 对应的数据库连接后，单击页面上方导航栏的 会话 按钮，可选择进入 会话管理 页面。 在会话管理页面您可以查看连接到当前数据库的所有会话以及会话ID、用户、发起地址、数据库名称、会话状态、执行 命令类型、SQL执行时间、SQL语句、代理地址等。

进入 ODC 对应的数据库连接后，单击页面上方导航栏的 会话 按钮，可选择进入 会话属性 页面。 会话属性即数据库变量，ODC 提供的可视化界面可以清晰直观的查看和修改当前数据库支持的会话变量和全局变量：

• 会话变量：客户端连接时会复制全局变量以自动生成会话变量，会话变量的修改只对当前会话生效。
• 全局变量：数据库实例共享全局变量，全局变量的修改对所有用户生效，但对当前会话无效。

（八）表对象

在 ODC 中单击连接名进入连接后，在左导航栏中单击表标签可以查看表列表。 在表列表中双击表名进入表管理页面，可以在表管理页面的数据页签查看和修改表的数据，或在属性页签查看表的基本信息、列、分区、索引、约束和 DDL 等属性信息。

新建表
步骤一：设置基本信息

步骤二：设置列

步骤三：设置分区规则（可选）

步骤四：设置索引（可选）

步骤五：设置约束（可选）

当然也可以在SQL窗口通过create

表属性管理
左侧导航栏中单击表标签可查看表列表，双击表名进入表管理页面，单击上方导航栏中的 属性按钮进入属性页签。

表的基本信息

列信息

索引信息

约束信息

DDL语句信息

表数据管理
左侧导航栏中单击表标签可查看表列表，双击表名进入表管理页面，单击上方导航栏中的 数据按钮进入数据页签。 可对当前表中的数据进行查看、新增、修改和删除操作。

导出导入格式

ODC 支持的数据导出格式有 SQL 格式和 CSV 格式。
只读模式是可以导出数据的，如果上线涉及数据的导入导出。对于导出，可以找个客户量少的时间进行演练。

四、OceanBase开发建议

（一）表结构设计

1. 表中一个字段业务逻辑不为空，建议设置为NOT NULL属性，如NULL需求时，建议业务根据业务需要定义 DEFAULT值。
2. 每张表上都有主键，在内部以主键为序组织存放数据。主键只能在建表语句中创建，不允许在表上添加主键。如果在创建表时不显式指定主键，系统会自动为表生成隐藏主键，隐藏主键不可被查询。
3. 当列上有 NOT NULL 约束时，通常建议设置默认值。当列类型是日期或时间类型时，可以设置默认值为1800- 01-01这样一个特殊的时间。

（二）字段设计

数值类型：

1. 数值类型建议使用NUMBER类型存储。
2. NUMBER(p,s)存储变长、十进制精度的定点数；也可以存储浮点数，此时NUMBER没有p和s，number类型如果确认小数点后保留位数，建议定义明确给出，而不是用默认的number类型。

字符类型：

1. VARCHAR2列长度修改只能由短变长，不能由长变短。
2. VARCHAR2是可变长字符串，长度范围1~32767。
3. CHAR和VARCHAR2 列在定义时需明确长度，长度支持字节和字符两种方式，其默认是字节长度，由系统变量 NLS_LENGTH_SEMANTICS 控制，与Oracle系统默认行为一致。
4. 字符数据类型的列可以存储所有字母数字值，但是 NUMBER 数据类型的列只能存储数字值。
5. 表达是与否的字段，建议使用CHAR(1)类型以节省空间（1代表TRUE，0代表FALSE），值的内容要统一，所有应用值要统一，具体内容值根据业务场景决定，例如：表达逻辑删除的字段名is_deleted，1表示删除，0表示未删除。
6. 表的列建议增加两字段，分别为：gmt_create, gmt_modified，gmt_create表示行插入时间，gmt_modified表示行修改时间，便于数据归档管理、入湖操作以及审计。在表的数据入湖中要求中，要求表必须有gmt_create, gmt_modified两列字段。说明：gmt_create, gmt_modified的类型选择DATE ，可以使用 sysdate函数。
7. 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
8. 字段允许适当冗余，以提高性能，但是必须考虑数据同步的情况。冗余字段应遵循：1）不是频繁修改的字段 2）不 是varchar超长字段。

时间字段类型：

1. 有时间精度要求的业务，可以使用TIMESTAMP。
2. 对精度没要求的，设置为DATE即可。
3. 不建议使用字符作为时间字段的数据类型。

字符字段类型：

1. 字符串类型定义建议使用VARCHAR2(N)类型而不是CHAR(N)类型。
2. 定义varchar字段长度时，需考虑到业务发展需求，适当保留冗余长度。
3. 字段长度按字节计算，不能按字符计算。
4. 列的类型禁止使用 NCHAR、NVARCHAR、NCLOB 等类型

字段数目限制：

• OB表的列数应该不能超过500

（三）索引设计

1. 表必须要有主键，如果没有合适的字段作为主键，可以为表增加一个数值列作为主键，使用OB Oracle 租户的序列为这一列填充值，也可以是组合主键。
2. 表的主键列值禁止被更新，可以进行删除操作。
3. 分区表上的主键必须包括表的分区键。
4. 分区表上的本地唯一键的交集必须包括表的分区键。
5. 组合主键最多不超过3个字段，如主键超过3个字段建议新增一列冗余列（如：自增主键，这样可以提高查询的效率）。（复合主键当字段过多时，会用更多的块来创建索引，所以会影响update,insert的性能）
6. 单个索引中每个索引记录的长度不能超过64KB。
7. 单个表上的索引个数建议控制在5个以内，并且不能超过7个，具体个数根据业务需求。
8. 唯一索引名为uk_表名_字段名；普通索引名则为idx_表名_字段名，过长时可使用缩写。
9. 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。说明：唯一索引对insert速度的损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验和控制，只要没有唯一索引，必然有脏数据产生。
10. 超过三个表禁止join。需要join的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。 说明：即使双表join也要注意表索引、SQL性能。
11. 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。说明：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
12. 如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。
13. 如果索引包含多个列，要明智选择列的顺序，一般来说，索引的第一列应当是一个高基数列（基数：数据列所包 含不同值的数量）。
14. 对ORDER BY、GROUP BY、DISTINCT子句中频繁使用的列添加到索引后面，形成覆盖索引避免回表。
15. 组合索引列的个数控制在3个字段以内，最多不超过5个。
16. 等值条件的列放在范围条件列的前面。
17. 若存在where a=? and b=?的查询条件，使用组合索引(a,b)，而不要分别在a、b字段上建立(a).(b)两个索引，后者将无法同时用到两个索引
18. 合理设计组合索引同时满足多场景的查询，减少索引的冗余性：
    a.多条语句可以共用同一个索引，其中某些语句只要覆盖索引前缀即可。如有两条语句条件分别为：a = ? and b = ?，和b = ?，那么组合索引(b,a)就可以同时为两条语句使用，不必在(a,b)和(b)上分别建立索引。
19. 非必要情况下不要使用全局索引，尤其要杜绝使用频率不高的全局索引。（如果全局索引的分区规则和主表的分区规则相同并且分区数相同，这时推荐创建一个局部索引。非必要情况下，不推荐使用全局索引： 一方面是因为全局索引的维护代价更大；另一方面是因为全局索引无法保证和主表分区的物理位置相同，除非将其和主表指定在一个表组中）。
20. where条件中如包含有分区键字段，则可以在分区键字段和条件里的其它字段上建立联合本地索引 。
21. 全局分区索引适合查询返回数据量较少的场景，对于返回数据量较大的场景建议在全局索引与本地索引间进行比较测试。
22. 建议不要对分区表分区键、全局分区索引分区键所在字段进行update操作，若业务确有需要务必打开分区表的 row movement功能：alter table XXX enable row movement 。
23. 避免重复索引：冗余的索引影响数据的增删改效率，同时浪费存储成本。
    b.主键默认创建索引和唯一性约束。
    c.索引(a,b,c)已创建的情况下不要再创建索引(a)和(a,b)。

（四）分区表设计

1. 业务流水表建议按range分区表进行创建，以保证数据清理和查询的效率。
2. 分区表在表创建的时候需要指定，后续不支持将非分区表在线改造成分区表，也不支持分区数量、分区类型、分区键值的在线调整。
3. 分区表索引建议：按照本地索引->全局分区索引->全局索引的顺序进行选择，只有在有必要的时候才使用全局索引，原因是全局索引会降低 DML 的性能，可能会因此产生分布式事务。 分区表的唯一索引推荐带分区键（本地索引）。关于分区策略，考虑从表的实际用途和应用场景方面进行设计。
   a.实际用途：历史表，流水表
   b.应用场景：存在明显访问热点的表
4. 使用分区表时要选择合适的拆分键以及拆分策略，分区键的选择：
   a.hash分区：选择区分度较大、在查询条件中出现频率最高的字段作为hash分区的分区键。
   b.range和list分区：根据业务规则选择合适的字段作为分区键，但分区数量不宜过少。
5. 分区的使用限制：hash分区下，不适合基于分区字段进行范围查询。
6. 善用Table group技术
   a.如果多张表通过各自的分区键进行关联，且分区键的分区策略一致，比如都是hash分区，且分区数也相等， 可以把多张表通过tablegroup技术放到一个表组中提高关联效率

（五）数据库连接

1. 如果一个连接超过60分钟空闲，服务端会主动断开，在使用连接池的时候需要设置一个连接最大的空闲时间。
2. 前端程序连接数据库或者数据库代理层，对于jdbc建议连接超时设置为1秒（蚂蚁主站是设置为500ms），要具备 失败重连机制，且失败重连必须有间隔时间。
3. 程序端日志必须记录连接数据库的标准OceanBase错误号以及所连接的数据库信息（比如IP和PORT，数据库用户名），用于DBA排查错误 。
4. 对于有连接池的前端程序，必须根据业务需要配置初始、最小、最大连接数，建议超时时间设置为30秒，且要设 置连接检测或空闲超时，以及连接回收机制（最大3600秒）。
5. 程序端使用的连接数据库的so库包、jar 库包以及客户端数据库版本，必须与线上数据库服务器的版本兼容。

（六）功能屏蔽

业务层面
存储过程、触发器的调用是否成功，前端不可控 。
业务的变更如果涉及到触发器、存储过程、外键等，需要数据库方面额外的操作及流程，不利于前端快速上线 。

触发器
部署在从库上，容易造成从库主从延迟、难于维护、需要考虑冗余成本增加。
部署在主库上，不仅导致系统写入性能受影响，触发器中的SQL在STATEMENT（默认同步模式） 下,不会写 BINLOG同步至从库,也就意味着若要数据同步，从库也得部署触发器,整个系统的性能降低。
触发器存在某些场景及版本下导致主从数据不一致，触发器失效等情况，比如自增主键主从不一致问题。

存储过程
其日志不利于问题追查定位，存储过程中涉及语句较多，而且循环较多。

从性能角度而言，复杂的判断和循环，消耗资源严重。存储过程中有算数运算，也会导致严重的性能问题，如从1 累加到1亿,其耗时将达到到5分钟，是C语言的1000倍

外键
禁止外键和级联：如无特殊情况，禁止使用外键和级联，一切外键必须在应用层解决。
外键与级联更新适用于单机低并发，不适合分布式、高并发集群。
级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。 无庸置疑的带来性能上的问题，每次数据变更都需要对外键做检查。
外键给数据恢复带来非常大的困难,因为多个表上的外键依赖，使得数据恢复流程非常复杂。

运维角度
触发器、存储过程、外键等功能的容易使得系统处于异构状态,相对应的数据维护、变更等操作, 无论是研发人员或是DBA,都需要关注集群由此导致的特殊性，需专门记录、维护、操作各个功能所在的具体实例，且操作步骤复 杂，特别是在进行主从切换等操作的时候，也带来了更多的判断操作,极不利于统一运维。

特殊的实例意味着需要更多的冗余成本。

（七）SQL编写

Select语句

1. 去掉Select无用列：SQL语句的Select部分只写必要的列，因为多余的列会导致数据库产生回表(进入数据页获取请求的特定列)，导致更多的I/O。
2. 不要使用count(列名)或count(常量)来替代count()，count()就是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。 说明：count(*)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。
3. 不得使用外键与级联，一切外键概念必须在应用层解决。
4. SELECT语句非必要不使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内。 说明：UNION最后会自动执行一个排序来消除重复，但实际上不同的查询之间通常不存在重复
5. 尽量避免in操作，带in的子查询尽量改写成join
6. 尽量避免读锁 select … for update (with cs)。如果事务较大，高并发下容易导致锁等待影响业务。
7. where子句中的in列表参数的个数限制在1000以内。
8. SELECT|UPDATE|DELETE|REPLACE要有WHERE子句，且WHERE子句的条件必须使用索引进行查找（可以通过explain检查执行计划，是否走的索引查询。
9. 尽量避免大表的全表扫描：一般情况下，大表的大多数查询使用索引扫描比全表扫描好，但对于100行以下的静态 表可以全表扫描。
10. WHERE 子句中禁止只使用全模糊或者左模糊的LIKE条件进行查找，必须有其他查询条件。
11. 有distinct、order by和group by子句的查询，中间结果集限制10000行以内，对于大结果集（中间结果集超过 10000行）的排序、分组放到程序端实现。

DML语句

1. INSERT语句必须指定具体的字段名称，不要写成INSERT VALUES(……)形式 。
2. SQL语句在程序中传入的参数值类型必须与字段在数据库中的类型相同 。
3. 分区表DML语句中的where条件中推荐使用分区键 。
4. DML 语句需要在分区或子分区名称的周围加上括号 。
5. DML 语句对数据的修改效果只有在提交事务时才永久生效。单个 DML 语句也可以是一个事务。
6. 不带条件更新的时候，如果记录数达到几十万或者几百万，会有大事务产生，可能会失败。所以UPDATE要注意 控制事务不要太大。
7. DELETE 语句的WHERE 条件子句是可选的，如果没有提供就全表删除。如果表记录数多达几百万以上，会形成 大事务，可能会有性能问题。建议带上 WHERE 条件分批删除，或者使用 TRUNCATE TABLE 语句。

隐式转换

数字与字符之间的隐式转换。
出现隐式转换的SQL语句对应业务逻辑是没有问题，但在书写SQL时没有关注字段的类型，导致执行计划出现隐式转 换，影响SQL执行效率。主要体现在两类SQL上：
a）字段是数字类型，在查询的时候当成字符在使用 where id=’1’
b）字段是字符类型，在查询的时候当数字在使用where id=1
其中，b种情况会导致无法利用到索引而扫全表。在进行这种字段比值的时候，一定要做书写规范，做到数字=数字， 字符=字符。

JOIN表不推荐超过2个

1. 某些性能消耗非常大的SQL可以直接造成数据库主从长时间延迟、主从中断、甚至实例CRASH等。 性能消耗过大的SQL本身执行时间长，其实也就是资源占用时间长，会造成集群并发能力低下。在业务流量突增 (业务本身或网络抖动都可能导致)等情况下，容易造成SQL堆积、并发超过限制等，从而影响到业务正常运行。而 联合查询过多张表，正是这种情况的常见案例。
2. 不只是JOIN不超过2张表，其它诸如在一个SQL语句用使用if,各种复杂函数、各种判断、多层子查询嵌 套等等的长语句，都是不被推荐的。 一个业务，使用简单的SQL语句，使用数据库最简单的增、删、改、查功能，从而让数据库处于一种可预估，可 扩展，可控的状态。
3. 我们来衡量一个业务SQL写的是否优秀，其关键点是这个业务的SQL是否在合理范围内足够的简单。这个合理的 范围指的是随着业务及数据的增长，SQL本身的性能消耗不大且不会有大的变化，不会占用过多的cpu或io时间。 比如一个根据主键查询的语句，一行数据与一千万行数据不会有太大的变化。业务可以很好的在此基础之上预估 当前流量要增加比如N倍的情况下，数据库应就当如何扩容并能确保数据库可以支撑.但如果业务中复杂语句过多， 性能消耗又大，数据库可能就只因为偶尔的或是前端的，或是网络的，可是数据库本身的波动导致SQL堆积、实例并发增长，业务受到影响。