概述


本文主要演示OceanBase集群常用SQL用法。如建租户(实例)、建库建表、分析执行计划、扩容缩容、内部视图分析等等。非常适合初学者入门OceanBase。

如果您是第一次接触OceanBase，请先看前文《​​OceanBase数据库实践入门——手动搭建OceanBase集群​​》了解OceanBase集群安装过程。即使没有条件安装OceanBase，也可以通过本文的示例了解OceanBase特征。　


集群资源管理


OceanBase的创新之一在于支持多租户，具体来说就是对集群资源进行二次管理。

每个节点的OBServer进程撷取了主机的大部分资源后，通过集群聚合成一个很大的资源池，这就是集群的最大能力。然后在这个大的资源池里可以分配出小的资源池给某个业务用，这个就是租户(也叫实例)。

分配资源和创建租户

-- 删除测试租户
drop tenant if exists tnt_mysql;
drop tenant if exists tnt_oracle;
-- 删除测试资源池
drop resource pool if exists pool_mysql;
drop resource pool if exists pool_oracle;
-- 删除测试资源单元规格
drop resource unit if exists unit_demo;

-- 创建测试资源单元规格
create resource unit unit_demo max_cpu=10, min_cpu=8, max_memory='10G', min_memory='10G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='1024G'; 
-- 创建资源池(分配资源)
create resource pool pool_mysql unit = 'unit_demo', unit_num = 1;
create resource pool pool_oracle unit = 'unit_demo', unit_num = 1;
-- 创建测试租户
create tenant tnt_mysql resource_pool_list=('pool_mysql'), primary_zone='RANDOM',comment 'mysql tenant/instance' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';
create tenant tnt_oracle resource_pool_list=('pool_oracle'), primary_zone='RANDOM',comment 'oracle tenant/instance' set ob_tcp_invited_nodes='%', ob_compatibility_mode='oracle';


备注：

  1. 在创建资源池的时候分配资源，​​unit_num​​决定了能否使用多台机器的能力。
  2. OceanBase 2.1版本支持两种兼容模式的租户类型：一是兼容​​mysql​​，一是兼容​​oracle​​。
  3. 创建租户的时候可以设定​​primary_zone​​决定租户内数据(分区)的​​leader​​副本默认位置，设定白名单特征限制租户访问的客户端。

查看资源利用率

-- sys租户查看当前资源利用率
select zone,concat(svr_ip,':',svr_port) observer, cpu_total, cpu_assigned,cpu_assigned_percent cpu_ass_pct, round(mem_total/1024/1024/1024) mem_total_gb, round(mem_assigned/1024/1024/1024) mem_ass_gb, mem_assigned_percent mem_ass_pct, unit_num, migrating_unit_num,leader_count,round(`load`,2) `load`, round(cpu_weight,2) cpu_weight, round(memory_weight,2) memory_weight
from __all_virtual_server_stat
order by zone,svr_ip,svr_port
;

OceanBase数据库实践入门——常用操作SQL_oracle


备注：

  1. 目前OceanBase只对​​CPU​​和​​Memory​​资源进行分配，所以重点关注这两项资源的分配比例(百分数，​​pct​​字段)。
  2. 默认情况下，​​cpu_total​​和​​mem_total​​资源都取自节点进程启动时从主机撷取的资源然后按 ​​resource_hard_limit/100​​ 展示。
-- 查看当前资源分配情况
select t1.name resource_pool_name, t2.`name` unit_config_name, t2.max_cpu, t2.min_cpu, round(t2.max_memory/1024/1024/1024) max_mem_gb, round(t2.min_memory/1024/1024/1024) min_mem_gb, t3.unit_id, t3.zone, concat(t3.svr_ip,':',t3.`svr_port`) observer,t4.tenant_id, t4.tenant_name
from __all_resource_pool t1 join __all_unit_config t2 on (t1.unit_config_id=t2.unit_config_id)
    join __all_unit t3 on (t1.`resource_pool_id` = t3.`resource_pool_id`)
    left join __all_tenant t4 on (t1.tenant_id=t4.tenant_id)
order by t1.`resource_pool_id`, t2.`unit_config_id`, t3.unit_id
;

OceanBase数据库实践入门——常用操作SQL_oracle_02


备注：

  1. 资源池由每个​​Zone​​里的资源单元(​​Unit​​)组合而成。这里可以看到租户具体的资源单元分布位置。
  2. 各个资源单元是彼此隔离的。

业务租户使用

连接业务租户(mysql租户)

使用​​mysql​​客户端或者OB客户端​​obclient​​连接​​mysql​​类型租户，默认密码是空。注意用户名格式是：​​集群名:租户名:用户名​​ 或者 ​​用户名@租户名#集群名​​。 应用代码建议用前者格式。

$mysql -h11.***.84.84 -uobdemo:tnt_mysql:root -P2883 oceanbase -A -p
Enter password:

MySQL [oceanbase]> show databases;
+--------------------+
| Database           |
+--------------------+
| oceanbase          |
| information_schema |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.10 sec)

$obclient -h11.***.84.84 -uroot@tnt_mysql#obdemo -P2883 oceanbase -A -p
Enter password:

obclient> show databases;
+--------------------+
| Database           |
+--------------------+
| oceanbase          |
| information_schema |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.03 sec)

obclient> create user my_user identified by '123456';
Query OK, 0 rows affected (0.01 sec)
obclient> select host,user from mysql.user;
+------+---------+
| host | user    |
+------+---------+
| %    | root    |
| %    | my_user |
+------+---------+
2 rows in set (0.05 sec)

obclient> create database demodb;
Query OK, 1 row affected (0.02 sec)

obclient> grant all privileges on demodb.* to my_user;
Query OK, 0 rows affected (0.01 sec)

连接业务租户(oracle租户)

使用obclient连接​​oracle​​类型租户，默认密码也是空。

$obclient -h11.***.84.84 -usys@tnt_oracle#obdemo -P2883 SYS -A -p
Enter password:

obclient> create user ora_user identified by 123456;
Query OK, 0 rows affected (0.02 sec)

obclient> select username, account_status, lock_date, expiry_date, created from dba_users;
+----------+----------------+-----------+-------------+---------------------+
| USERNAME | ACCOUNT_STATUS | LOCK_DATE | EXPIRY_DATE | CREATED             |
+----------+----------------+-----------+-------------+---------------------+
| SYS      | NULL           | NULL      | NULL        | 2019-05-18 11:52:43 |
+----------+----------------+-----------+-------------+---------------------+
1 row in set (0.08 sec)

obclient> grant all privileges on ora_user.* to ora_user;
Query OK, 0 rows affected (0.04 sec)
obclient> grant select on sys.* to ora_user;
Query OK, 0 rows affected (0.04 sec)

mysql租户建表

$obclient -h11.***.84.84 -umy_user@tnt_mysql#obdemo -P2883 demodb -A -p123456

DROP TABLE IF EXISTS `customers`;
CREATE TABLE`customers` (
  `customerNumber` bigint(11) NOT NULL,
  `customerName` varchar(50) NOT NULL,
  `contactLastName` varchar(50) NOT NULL,
  `contactFirstName` varchar(50) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `addressLine1` varchar(50) NOT NULL,
  `addressLine2` varchar(50) DEFAULT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) DEFAULT NULL,
  `postalCode` varchar(15) DEFAULT NULL,
  `country` varchar(50) NOT NULL,
  `salesRepEmployeeNumber` bigint(11) DEFAULT NULL,
  `creditLimit` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`customerNumber`),
  KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`) GLOBAL
) partition by hash(`customernumber`) partitions 6


DROP TABLE IF EXISTS `orders`;
CREATE TABLE`orders` (
  `orderNumber` int(11) NOT NULL,
  `orderDate` date NOT NULL,
  `requiredDate` date NOT NULL,
  `shippedDate` date DEFAULT NULL,
  `status` varchar(15) NOT NULL,
  `comments` text DEFAULT NULL,
  `customerNumber` int(11) NOT NULL,
  PRIMARY KEY (`orderNumber`, `customerNumber`),
  KEY `customerNumber` (`customerNumber`) GLOBAL
) PARTITION by HASH(customerNumber) PARTITIONS 6
;

DROP TABLE IF EXISTS `orderdetails`;
CREATE TABLE `orderdetails` (
  `orderNumber` bigint(11) NOT NULL,
  `productCode` varchar(15) NOT NULL,
  `quantityOrdered` bigint(11) NOT NULL,
  `priceEach` decimal(10,2) NOT NULL,
  `orderLineNumber` smallint(6) NOT NULL,
  `customerNumber` bigint(11) NOT NULL,
  PRIMARY KEY (`customerNumber`, `orderNumber`, `productCode` ),
  KEY `productCode` (`productCode`) GLOBAL
) PARTITION by HASH(customerNumber) PARTITIONS 6;

obclient> ^DBye

$obclient -h11.***.84.84 -uroot@tnt_mysql#obdemo -P2883 demodb -A -p
obclient> create tablegroup tg_orders partition by hash partitions 6;
Query OK, 0 rows affected (0.02 sec)

obclient> alter tablegroup tg_orders add customers, orders, orderdetails;
Query OK, 0 rows affected (0.02 sec)

obclient> show tablegroups;
+-----------------+--------------+---------------+
| Tablegroup_name | Table_name   | Database_name |
+-----------------+--------------+---------------+
| oceanbase       | NULL         | NULL          |
| tg_orders       | customers    | demodb        |
| tg_orders       | orderdetails | demodb        |
| tg_orders       | orders       | demodb        |
+-----------------+--------------+---------------+
4 rows in set (0.01 sec)


备注：

  1. 业务上有强关联的表如果要拆分，可以选择同一个拆分策略和分片数。然后利用OceanBase的​​tablegroup​​机制可以将这些分区表的同号分区(即分区组​​PartitionGroup​​)聚合到同一个节点内部。
  2. 分区组的分区策略和分片数必须跟分区表的分区策略和分片数一致。
  3. 分区表支持全局索引，需要指定 ​​GLOBAL​​。本地索引类型是​​LOCAL​​。

分区负载均衡和租户扩容

select t5.tenant_name, t3.database_name, t4.tablegroup_name, t1.table_name, t2.partition_id, t2.role, t2.zone, concat(t2.svr_ip, ':', t2.svr_port) observer, t2.`row_count`, round(t2.data_size/1024/1024) data_size_mb
from __all_table t1 join gv$partition t2 on (t1.tenant_id=t2.tenant_id and t1.table_id=t2.table_id)
    join gv$database t3 on (t1.tenant_id=t3.tenant_id and t1.database_id=t3.database_id)
    left join __all_tablegroup t4 on (t1.tenant_id=t4.tenant_id and t1.tablegroup_id=t4.tablegroup_id)
    join __all_tenant t5 on (t1.tenant_id=t5.tenant_id)
where t5.tenant_id=1012 and t2.role=1 and t1.table_name in ('customers','orders','orderdetails')
order by t5.tenant_name, t3.database_name, t4.tablegroup_name, t2.partition_id, t2.zone, t2.svr_ip, t2.svr_port, t1.table_name,partition_id
;


OceanBase数据库实践入门——常用操作SQL_sql_03


备注：

  1. ​​role​​为1表示​​leader​​副本，​​svr_ip:svr_port​​是副本所在的位置。此时租户资源池还是​​1-1-1​​布局，所以所有分区的​​leader​​都在同一个节点内部。
  2. 虽然集群拓扑是​​2-2-2​​，这个租户只能使用到其中3个节点的能力。
  3. 跟​​tablegroup​​的连接必须是外连接，因为可能有些表没有关联到任何​​tablegroup​​。

下面将租户资源扩容为​​2-2-2​​布局。随后我们再看看分区分布。

alter resource pool​​​​ pool_mysql unit_num=2;​​


OceanBase数据库实践入门——常用操作SQL_mysql_04


备注：

  1. 这三个表的同号分区在同一个节点内部，不同分区在不同的节点上。这是分区负载均衡机制生效了。

OceanBase数据库实践入门——常用操作SQL_oracle_05

备注：

  1. 各个节点上的​​cpu​​和​​memory​​利用率基本均等。

  3. 每个节点上都有​​leader​​副本，虽然数量不均衡并没有关系，因为均衡策略是综合租户、分区组、分区空间、资源(​​cpu​​和​​memory​​)利用率的。1136里大部分是​​sys​​租户的内部表。

  4. 

OceanBase数据库实践入门——常用操作SQL_mysql_06

备注：

  1. 当把一个资源池的​​unit_num​​设置为2个时，意味着每个​​Zone​​里有2个​​Unit​​，这两个资源单元不能在一个节点内部。这样设置后这个​​mysql​​租户理论上就可以发挥多个节点的能力了。

oracle租户建表

$obclient -h11.***.84.84 -uora_user@tnt_oracle#obdemo -P2883 ORA_USER -A -p123456

DROP TABLE  customers;
CREATE TABLE customers ( customerNumber number(11) NOT NULL,
  customerName number NOT NULL,
  contactLastName varchar2(50) NOT NULL,
  contactFirstName varchar2(50) NOT NULL,
  phone varchar2(50) NOT NULL,
  addressLine1 varchar2(50) NOT NULL,
  addressLine2 varchar2(50) DEFAULT NULL,
  city varchar2(50) NOT NULL,
  state varchar2(50) DEFAULT NULL,
  postalCode varchar2(15) DEFAULT NULL,
  country varchar2(50) NOT NULL,
  salesRepEmployeeNumber number(11) DEFAULT NULL,
  creditLimit decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (customerNumber),
)  partition by hash(customernumber) partitions 6
;

DROP TABLE  orders;
CREATE TABLE orders (
  orderNumber number(11) NOT NULL,
  orderDate date NOT NULL,
  requiredDate date NOT NULL,
  shippedDate date DEFAULT NULL,
  status varchar2(15) NOT NULL,
  comments varchar2(4000),
  customerNumber number(11) NOT NULL,
  PRIMARY KEY (orderNumber, customerNumber)
) partition by hash(customernumber) partitions 6
;

DROP TABLE  orderdetails;
CREATE TABLE orderdetails (
  orderNumber number(11) NOT NULL,
  productCode varchar2(15) NOT NULL,
  quantityOrdered number(11) NOT NULL,
  priceEach decimal(10,2) NOT NULL,
  orderLineNumber number(6) NOT NULL,
  customerNumber number(11) NOT NULL,
  PRIMARY KEY (customerNumber, orderNumber, productCode)
) partition by hash(customernumber) partitions 6
;

create index salesRepEmployeeNumber on customers(salesRepEmployeeNumber) GLOBAL ;
create index productCode on orderdetails(productCode) GLOBAL;
create index customerNumber on orders(customerNumber) GLOBAL;

$obclient -h11.***.84.84 -usys@tnt_oracle#obdemo -P2883 SYS -A -p
obclient> create tablegroup tg_orders partition by hash partitions 6;
Query OK, 0 rows affected (0.04 sec)
obclient> alter tablegroup tg_orders add ora_user.customers, ora_user.orders, ora_user.orderdetails;
Query OK, 0 rows affected (0.03 sec)


同样也对​​oracle​​租户资源进行扩容

​​alter resource pool​​​​ pool_oracle unit_num=2;​​

下面看看负载均衡的效果。

OceanBase数据库实践入门——常用操作SQL_sql_07

备注：

  1. ​​ORACLE​​里的表默认都是大写的，​​tablegroup​​机制将三个表的同号分区约束在同一个节点内部。


OceanBase数据库实践入门——常用操作SQL_oracle_08


备注：

  1. 可以观察到​​leader_count​​有了变化。

查看执行计划

无论是​​mysql​​租户，还是​​oracle​​租户，查看执行计划的命令都很简单，就是 ​​explain [sql]...​​。

obclient> explain select t1.customerNumber, t1.contactFirstName, t1.contactLastName, t2.orderNumber, t2.status, t2.comments, t3.orderNumber, t3.productCode, t3.quantityOrdered  
from customers t1 join orders t2 on (t1.customerNumber=t2.customerNumber) join orderdetails t3 on (t2.orderNumber=t3.orderNumber and t2.customerNumber=t3.customerNumber)  
where t1.customerNumber=363\G


OceanBase数据库实践入门——常用操作SQL_mysql_09

不过同样的sql，在mysql租户里执行计划略有不同

OceanBase数据库实践入门——常用操作SQL_oracle_10

备注：

  1. 用了​​t2​​和​​t3​​的​​merge join​​，不是很合理，估计是跟数据量有关。实际执行性能也不差。
  2. 可以用​​HINT​​ 修改执行计划，如下：

OceanBase数据库实践入门——常用操作SQL_sql_11

内部视图


OceanBase提供了非常丰富的内部视图，方便运维和开发了解内部原理、性能诊断等等。

gv$视图(仿ORACLE)

MySQL [oceanbase]> show tables like 'gv$%';
+------------------------------------+
| Tables_in_oceanbase (gv$%)         |
+------------------------------------+
| gv$concurrent_limit_sql            |
| gv$database                        |
| gv$latch                           |
| gv$lock_wait_stat                  |
| gv$memory                          |
| gv$memstore                        |
| gv$memstore_info                   |
| gv$obrpc_incoming                  |
| gv$obrpc_outgoing                  |
| gv$outline                         |
| gv$partition                       |
| gv$partition_audit                 |
| gv$plan_cache_plan_explain         |
| gv$plan_cache_plan_stat            |
| gv$plan_cache_stat                 |
| gv$server_memstore                 |
| gv$session_event                   |
| gv$session_longops                 |
| gv$session_wait                    |
| gv$session_wait_history            |
| gv$sesstat                         |
| gv$sql                             |
| gv$sql_audit                       |
| gv$sql_monitor                     |
| gv$sql_plan_monitor                |
| gv$sql_plan_statistics             |
| gv$sysstat                         |
| gv$system_event                    |
| gv$table                           |
| gv$tenant                          |
| gv$tenant_memstore_allocator_info  |
| gv$tenant_sequence_object          |
| gv$unit                            |
| gv$unit_load_balance_event_history |
+------------------------------------+
34 rows in set (0.01 sec)

视图详细定义可以查看官网(​​oceanbase.alipay.com/docs​​)。
其中视图​​gv$sql_audit​​是OceanBase全量SQL运行日志的。详情参见前文《​​阿里数据库性能诊断的利器——SQL全量日志​​》。

OceanBase特有视图(__all_)

MySQL [oceanbase]> show tables like '__all_%';
+--------------------------------------+
| Tables_in_oceanbase (__all_%)        |
+--------------------------------------+
| __all_acquired_snapshot              |
| __all_build_index_param              |
| __all_charset                        |
| __all_clog_history_info              |
<<<省略>>>
| __all_unit                           |
| __all_unit_config                    |
| __all_unit_load_history              |
| __all_user                           |
| __all_user_history                   |
| __all_zone                           |
+--------------------------------------+
106 rows in set (0.00 sec)

其中视图​​__all_rootservice_event_history​​可以查看内部事件日志。详情参见前面文章《​​OceanBase数据库实践入门——了解总控服务​​》。

​​select *
from ​​​​__all_rootservice_event_history​​​​ h
order by h.gmt_create desc limit 100;​​


OceanBase数据库实践入门——常用操作SQL_mysql_12


备注：

  1. 通过这个​​sql​​可以观察到资源分配、分区负载均衡(迁移)、​​leader​​副本切换的过程等等。

参数和变量

参数(Parameter)变更

OceanBase的参数变更影响的是集群级别的设置，只能在​​sys​​租户下变更。操作方式非常类似ORACLE的参数变更。

alter system set migrate_concurrency=20;
alter system set server_data_copy_out_concurrency=20;
alter system set server_data_copy_in_concurrency=20;
show parameters where name in ('migrate_concurrency','data_copy_concurrency','server_data_copy_out_concurrency','server_data_copy_in_concurrency');

OceanBase数据库实践入门——常用操作SQL_mysql_13

变量(variables)变更

OceanBase的变量变更影响的是租户(实例)的设置，只能在具体租户下变更(​​sys​​租户也可以)，影响范围是当前租户。这点跟MySQL运维操作非常类似。

set global ob_query_timeout=20000000;
show global variables like '%timeout%';

OceanBase数据库实践入门——常用操作SQL_sql_14


总结


OceanBase的SQL兼容性既支持MySQL(5.6)又支持ORACLE，在运维操作上部分功能很像ORACLE，部分功能又像MySQL。所以传统ORACLE/MySQL DBA在接触OceanBase过程中会很容易入手。


其他


  • 个人理解，难免有误。我会在月底集中对当月文章里的错误进行修正。敬请关注。
  • 更多分享请查阅公众号

OceanBase数据库实践入门——常用操作SQL_oracle_15