OceanBase 基础操作之 MySQL 模式

[TOC]

基本操作

登入与登出

#.通过 OBServer 登录, 默认端口 2881
obclient -h127.0.0.1 -P2881 -uroot@sys -p'Admin_147' -Doceanbase -A -e "select version();"

#.通过 OBProxy 登录, 默认端口 2883
obclient -h127.0.0.1 -P2883 -uroot@sys -p'Admin_147' -Doceanbase -A -e "select version();"

#.登出
obclient(root@sys)[oceanbase]> exit;

实例的管理

查看版本信息

#.查看 OBServer 或 OBProxy 的版本
obclient --version
/data/ob_2881/obproxy-ce/bin/obproxy --version

#.查询 MySQL 模式下的版本
select version();
show variables like 'version';

#.查询 Oracle 模式下的版本
select * from v$version;

集群的启停与调整

#.查看集群清单
obd cluster list

#.执行display会显示指定集群的相关登录方式和密码
obd cluster display sqlfans

#.集群的启动与停止, 以 sqlfans 集群为例
obd cluster start sqlfans
obd cluster stop sqlfans
obd cluster restart sqlfans

#.重启集群中指定的服务组件
obd cluster restart sqlfans -c prometheus

#.重启部署配置文件中指定节点的指定服务组件
obd cluster restart sqlfans -c prometheus -s 192.168.31.101

#.通过 edit-config 调整配置，再 reload 重载配置
obd cluster edit-config sqlfans
obd cluster reload sqlfans

#.根据配置文件部署自定义集群
# obd cluster autodeploy -f <集群名> -c <配置文件>

参数及变量的修改

#.查看集群状态
select svr_ip,svr_port,zone,status,start_service_time,create_time from oceanbase.dba_ob_servers;

#.查看 OBServer 参数
show parameters like '%timeout%';
show parameters where name in ('rpc_timeout','arbitration_timeout');
select * from oceanbase.GV$OB_PARAMETERS where NAME in ('rpc_timeout','arbitration_timeout');

#.修改 OBServer 参数
alter system set rpc_timeout='6s';

#.查看变量
show variables like '%timeout%';
show variables where variable_name in ('connect_timeout','wait_timeout');

#.修改全局级别变量, 当前session不生效, 新session生效
set global ob_query_timeout=10000000;

#.修改会话级别变量, 当前session生效, 其他session不生效
set session ob_query_timeout=10000000;

数据库的管理

OceanBase 不直接支持 ALTER DATABASE <old_name> RENAME TO <new_name>，通常建议先创建新库，再迁移所有表到新库，最后删除旧库

#.查看数据库清单
show databases;

#.创建数据库, 并指定字符集为 utf8mb4
CREATE DATABASE if not exists bak DEFAULT CHARACTER SET utf8mb4;

#.修改数据库的字符集为 utf8mb4、字符序为 utf8mb4_bin、且为读写属性
ALTER DATABASE bak DEFAULT CHARACTER SET utf8mb4;
ALTER DATABASE bak DEFAULT COLLATE utf8mb4_bin;
ALTER DATABASE bak READ WRITE;

#.删除数据库
drop database if exists bak;

数据表的管理

#.先切换上下文
create database if not exists bak;
use bak;

#.创建表
drop table if exists tab01;
create table if not exists tab01 (c1 int PRIMARY KEY, c2 char(2));

#.创建分区表
create table if not exists tab02 (c1 int,c2 int) partition by hash (c1) partitions 3;

#.查看建表语句
show create table tab01;

#.查看当前库的表清单
show tables;

#.查看特定库的表清单, 示例 mysql
show tables from mysql;

#.修改表的并行度
alter table tab01 parallel 2;

#.修改表的行格式
alter table tab01 ROW_FORMAT = COMPRESSED;

#.修改表的块大小
alter table tab01 BLOCK_SIZE = 1024;

#.转换表的字符集
alter table tab01 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

#.查看表结构
describe tab01;

#.修改字段名及类型
alter table tab01 change column c2 c3 char(10);

#.添加字段
alter table tab01 add column c4 int;

#.在尾部添加字段
alter table tab01 add column c99 int;

#.在某个字段前面添加字段
alter table tab01 add column c98 int before c99;

#.重新各个字段的顺序
alter table tab01 modify column c99 int first;
alter table tab01 modify column c98 int after c99;

#.删除字段
alter table tab01 drop c99;

#.单个表的重命名
rename table tab01 to tab01_20250729;

#.通过重命名实现迁移
create table if not exists tab09 (c1 int);
rename table tab09 to mysql.tab09;
show tables from mysql like 'tab%';
drop table if exists mysql.tab09;

#.多个表的重命名
create table if not exists tab01 (c1 int PRIMARY KEY, c2 char(2));
create table if not exists tab02 (c1 int PRIMARY KEY, c2 char(2));
rename table tab01 to tab01_20250730, tab02 to tab02_20250730;

#.TRUNCATE 会删除并重新创建表,这比逐行删除要快得多,特别是对于大表, 且会重头计算 AUTO_INCREMENT 值
truncate table tab01_20250729;

#.删除表
drop table if exists tab01_20250729;

#.批量删表
drop table if exists tab01_20250730,tab02_20250730;

增删改查

#.准备测试表
use bak;
drop table if exists tab01;
create table tab01 (c1 INT PRIMARY KEY, c2 int) PARTITION BY KEY(c1) PARTITIONS 4;

#.插入数据
insert into tab01 values(1,1);
insert into tab01 VALUES(2,2),(3,3),(2+2,3*4);

#.修改数据
update tab01 set c2=10 where c1=1;

#.查询数据
select * from tab01;

#.删除数据
delete from tab01 where c1=1;

#.删除表中按照 c2 列排序之后的第一行数据
select * from tab01 order by c2 asc;
delete from tab01 order by c2 asc limit 1;

租户的管理

创建租户的顺序是 unit -> resource pool -> tenant，而删除租户的顺序则相反为 tenant -> resource pool -> unit

#.删除租户的顺序
drop tenant if exists tenant_demo force;
drop resource pool if exists pool_demo;
drop resource unit if exists unit_demo;

#.创建租户的顺序
create resource unit unit_demo min_cpu=1, max_cpu=2, memory_size='2G', min_iops=2000, max_iops=8000;
create resource pool pool_demo unit='unit_demo', unit_num=1, zone_list=('zone1');
create tenant tenant_demo charset='utf8mb4', replica_num=3, primary_zone='zone1', resource_pool_list=('pool_demo');


#.修改资源规格/资源池/租户
alter resource unit unit_demo min_cpu=1, memory_size='2G', log_disk_size='4G';
alter resource pool pool_demo unit='unit_demo';
alter tenant tenant_demo resource_pool_list=('pool_demo');
alter tenant tenant_demo set variables ob_tcp_invited_nodes='%';

#.不支持修改租户的部分设置
# alter tenant tenant_demo charset='utf8mb4', primary_zone='zone2', zone_list=('zone1','zone2','zone3');


#.查看unit信息
select * from oceanbase.__all_unit;
select unit_config_id,name,min_cpu,max_cpu,memory_size,min_iops,max_iops,iops_weight,gmt_create from oceanbase.__all_unit_config;
select unit_config_id,name,min_cpu,max_cpu,memory_size,min_iops,max_iops,iops_weight,create_time from oceanbase.dba_ob_unit_configs;

#.查看资源池信息
select * from oceanbase.__all_resource_pool;
select * from oceanbase.dba_ob_resource_pools;

#.查看租户信息
select * from oceanbase.dba_ob_tenants;
select tenant_id,tenant_name,zone_list,primary_zone,status,gmt_create from oceanbase.__all_tenant;

#.确认集群可用资源
select 
  zone,svr_ip,svr_port,cpu_capacity,cpu_assigned_max,cpu_capacity-cpu_assigned_max as cpu_free,
  round(memory_limit/1024/1024/1024,2) as memory_total_gb,
  round((memory_limit-mem_capacity)/1024/1024/1024,2) as system_memory_gb,
  round(mem_assigned/1024/1024/1024,2) as mem_assigned_gb,
  round((mem_capacity-mem_assigned)/1024/1024/1024,2) as memory_free_gb,
  round(log_disk_capacity/1024/1024/1024,2) as log_disk_capacity_gb,
  round(log_disk_assigned/1024/1024/1024,2) as log_disk_assigned_gb,
  round((log_disk_capacity-log_disk_assigned)/1024/1024/1024,2) as log_disk_free_gb,
  round((data_disk_capacity/1024/1024/1024),2) as data_disk_gb,
  round((data_disk_in_use/1024/1024/1024),2) as data_disk_used_gb,
  round((data_disk_capacity-data_disk_in_use)/1024/1024/1024,2) as data_disk_free_gb 
from oceanbase.gv$ob_servers \G;

#.查看租户对应的Unit数量及Zone信息
SELECT pl.tenant_id,tt.tenant_name,pl.name AS pool_name,pl.unit_config_id,pl.unit_count,ut.unit_id,pl.zone_list,ut.svr_ip
FROM oceanbase.__all_resource_pool pl
INNER JOIN oceanbase.__all_tenant tt ON pl.tenant_id = tt.tenant_id
INNER JOIN oceanbase.__all_unit ut ON pl.resource_pool_id = ut.resource_pool_id
WHERE pl.tenant_id >= 1 ORDER BY tt.tenant_name, pl.zone_list;

#.查看租户资源已分配情况
select 
  t4.tenant_id,t4.tenant_name,t1.name pool_name, t1.unit_count, t2.`name` unit_config_name, 
  t2.max_cpu, t2.min_cpu, round(t2.memory_size/1024/1024/1024,2) mem_size_gb, 
  round(t2.log_disk_size/1024/1024/1024,2) log_disk_size_gb, t2.max_iops, t2.min_iops, 
  t3.unit_id, t3.zone, concat(t3.svr_ip,':',t3.`svr_port`) observer 
from oceanbase.dba_ob_resource_pools t1 
join oceanbase.dba_ob_unit_configs t2 on (t1.unit_config_id=t2.unit_config_id) 
join oceanbase.dba_ob_units t3 on (t1.`resource_pool_id` = t3.`resource_pool_id`) 
left join oceanbase.dba_ob_tenants t4 on (t1.tenant_id=t4.tenant_id) 
order by t4.tenant_name,t3.zone;


#.不退出 sys 切换到 tenant_demo 租户
alter system change tenant tenant_demo;

#.查看当前链接的租户
show tenant;

#.再从 tenant_demo 切换到 sys 租户
alter system change tenant sys;

账号的管理

#.创建账号
CREATE USER IF NOT EXISTS 'user_demo' IDENTIFIED BY 'Admin_147';

#.批量创建账号
CREATE USER 'user_test_01' IDENTIFIED BY 'Admin_123', 'user_test_02' IDENTIFIED BY 'Admin_456';

#.修改当前用户的密码
set password=password('Admin_147');

#.修改指定账号的密码
ALTER USER 'user_demo' IDENTIFIED BY 'Admin_258';
SET PASSWORD FOR user_demo = PASSWORD('Admin_258');

#.查看账号清单
select user,host from mysql.user;

#.账号的锁定与解锁
ALTER USER 'user_demo' ACCOUNT LOCK;
ALTER USER 'user_demo' ACCOUNT UNLOCK;

#.查看账号的锁定状态
select user,host,plugin,account_locked from mysql.user;

#.账号的重命名
rename user 'user_demo' to 'user_demo_20250729';

#.删除账号
drop user if exists user_demo_20250729;

#.批量删除账号
drop user if exists user_test_01,user_test_02;

授权与撤销

#.查看各权限的含义
SHOW PRIVILEGES;

#.创建账号
CREATE USER IF NOT EXISTS 'user_demo' IDENTIFIED BY 'Admin_147';

#.添加授权
GRANT SELECT,INSERT,UPDATE,DELETE ON bak.* TO 'user_demo';

#.查看当前登录用户
SELECT CURRENT_USER, CURRENT_USER();
SELECT * FROM information_schema.processlist WHERE ID = CONNECTION_ID();

#.查看指定账号的权限
SHOW GRANTS FOR user_demo;

#.用户权限的回收
REVOKE SELECT ON bak.* FROM user_demo;

三类示范账号

#.创建超管账号
CREATE USER IF NOT EXISTS 'dba_admin'@'%' IDENTIFIED BY 'cf_rB1NKCzbaQuPH';
GRANT ALL PRIVILEGES ON *.* TO 'dba_admin'@'%' WITH GRANT OPTION;

#.创建业务账号
CREATE USER IF NOT EXISTS user_bigdata_push IDENTIFIED BY 'dw_RX2a_6GemCf7u';
GRANT SELECT,INSERT,UPDATE ON bak.* TO user_bigdata_push;

#.创建只读账号
CREATE USER IF NOT EXISTS user_bigdata_readonly IDENTIFIED BY 'dw_iuDIfQzWgVb9G';
GRANT SELECT ON bak.* TO user_bigdata_readonly;

连接数与会话

#.查看当前连接id
SELECT CONNECTION_ID();

#.查看当前的会话状态
SHOW SESSION STATUS;

#.查看所有连接
show processlist;
select * from information_schema.processlist;

#.根据事务时间, 找到执行时间最长且未结束的事务的 trans_id
SELECT * FROM oceanbase.__all_virtual_trans_lock_stat ORDER BY ctx_create_time LIMIT 5\G;

#.通过事务的 trans_id 找到其所持有的所有锁，并根据 rowkey 明确哪个 session_id 是需要停止的事务
SELECT * FROM oceanbase.__all_virtual_trans_lock_stat WHERE trans_id LIKE '%%'\G

#.终止一个会话
KILL <session_id>;

字符集

OceanBase 数据库默认的字符序是 utf8mb4_general_ci
数据库社区版暂不支持 utf8mb4_unicode_ci 和 utf16_unicode_ci
对于任何 Unicode 字符集，使用 xxx_general_ci 排序规则执行的操作比使用 xxx_unicode_ci 更快

#.查看支持的字符集
SHOW CHARSET;

#.查看支持的字符序
SHOW COLLATION;

数据库的字符集和字符序

#.创建数据库指定字符集, 比如 latin1
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb CHARSET latin1;

#.修改已存在数据库的字符集，比如改为 utf8mb4
ALTER DATABASE testdb CHARSET utf8mb4;

#.查看当前数据库的字符集
USE testdb;
SELECT @@character_set_database, @@collation_database;

#.查看指定数据库的字符集
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'mysql';

数据表的字符集和字符序

#.创建表的时候指定字符集和字符序
use bak;
drop table if exists tab01;
CREATE TABLE tab01 (c1 int, c2 varchar(4)) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin;

#.修改表的字符集和字符序
ALTER TABLE tab01 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;

#.修改表中已有数据的字符集和字符序，并修改对应的设置
alter table tab01 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

字段的字符集和字符序

#.创建表的时候指定字段的字符集和字符序
drop table if exists tab01;
CREATE TABLE tab01 (c1 int, c2 varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci);

#.修改字段的字符集和字符序
ALTER TABLE tab01 MODIFY c2 varchar(4) CHARACTER SET gbk COLLATE gbk_chinese_ci;

客户端连接的字符集和字符序

#.设置客户端字符集为 gbk
SET CHARSET 'gbk';
SHOW VARIABLES LIKE 'character\_set\_%';

#.设置服务器向客户端返回查询结果的字符集，包括列值、列名和错误消息
#.如果不需要不执行结果集或错误消息的转换，请将 character_set_results 设置为 NULL 或二进制
SET character_set_results = NULL;
SET character_set_results = binary;

#.指定客户端、连接与结果字符集为 gbk、字符序为 gbk_bin
SET NAMES gbk COLLATE gbk_bin;
SHOW VARIABLES LIKE '%char%';

索引与调优

索引的创建与删除

注意：OceanBase 的索引名称必须全局唯一，即不同的表不可以使用同一个索引名称，所以规范命名 idx_表名_字段名

#.准备测试表
use bak;
drop table if exists tab01;
create table if not exists tab01 (c1 INT PRIMARY KEY, c2 CHAR(2), c3 VARCHAR(4));

#.创建普通索引的两种方式
create index idx_tab01_c2 on tab01 (c2);
alter table tab01 add index idx_tab01_c2_add (c2);

#.创建唯一索引
create unique index udx_tab01_c3 on tab01 (c3);

#.创建本地索引(默认)与全局索引
create index idx_tab01_c3_local on tab01 (c3) local;
create index idx_tab01_c3_global on tab01 (c3) global;

#.查看索引
show index from tab01;

#.版本 4.3.5 不支持索引重建
# ALTER TABLE tab01 REBUILD INDEX idx_tab01_c2;
# ALTER INDEX idx_tab01_c2 ON tab01 REBUILD;
# ALTER TABLE tab01 REBUILD;

#.索引的重命名
ALTER TABLE tab01 RENAME INDEX idx_tab01_c2 TO idx_tab01_c2_20250729;

#.删除索引
drop index idx_tab01_c2_20250729 on tab01;

#.添加check约束
alter table tab01 add c11 int;
alter table tab01 add c12 int;
alter table tab01 add constraint check_tab01_c11c12 check (c11 > c12);

#.删除check约束
alter table tab01 drop check check_tab01_c11c12;

关于主键

#.准备测试表
use bak;
drop table if exists tab01;
create table if not exists tab01 (c1 INT, c2 CHAR(2), c3 VARCHAR(4));

#.添加主键
ALTER TABLE tab01 ADD PRIMARY KEY (c1);

#.修改主键
ALTER TABLE tab01 DROP PRIMARY KEY, ADD PRIMARY KEY (c2);

#.删除主键
ALTER TABLE tab01 DROP PRIMARY KEY;

关于外键

#.创建表 employee
drop table if exists employee;
create table employee (
  employee_id INTEGER PRIMARY KEY,
  employee_name VARCHAR(20),
  hire_date DATE,
  salary INTEGER,
  department_id INTEGER NOT NULL,
  insert_time DATE
);

#.创建表 department
drop table if exists department;
create table department (
  department_id INTEGER NOT NULL PRIMARY KEY,
  department_name VARCHAR(30),
  insert_time DATE
);

#.创建外键约束
ALTER TABLE employee ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES department (department_id);

#.删除外键约束
ALTER TABLE employee DROP FOREIGN KEY fk_department;

在 OceanBase V2.x 和 V3.x 版本，删除分区表的全局唯一索引会触发索引重建。注意：重建过程中全局唯一索引会失效，这个期间可能会写入违反唯一性约束的数据，导致全局唯一索引失效。当出现上述问题时，可以按照如下方式尝试解决。

推荐方案：在 truncate/drop 分区时加上 global indexs 防止全局分区索引失效
临时应急：需要手动清除冲突数据后，手动删除全局唯一索引，然后手动重新创建全局唯一索引。drop 掉全局唯一索引并重新重建

其他优化

开启压缩

#.开启压缩，使用指定的压缩算法对创建索引过程中产生的临时文件进行压缩，比如 ZSTD 或 LZ4 或 AUTO
alter system set _ob_ddl_temp_file_compress_func = 'ZSTD';
alter system set _ob_ddl_temp_file_compress_func = 'LZ4';
alter system set _ob_ddl_temp_file_compress_func = 'AUTO';

#.关闭压缩
alter system set _ob_ddl_temp_file_compress_func = 'NONE';

为避免因快照点保留而导致主表空间放大的问题，建议关闭合并

#.将 minor_freeze_times 参数调高至足够的值，例如 500
alter system set minor_freeze_times = 500;

#.将 major_freeze_duty_time 配置为 disable
alter system set major_freeze_duty_time = disable;

通过设置DDL的并行度来优化CPU

#.假设当前集群中可以分配 N 个 CPU 给建索引, 可以将建索引的并行度设置为 N
#.由于执行DDL默认是串行的，可以如下参数来控制并行度，确保所有DDL的并行度加起来不超过租户max_cpu的上限
SET SESSION _FORCE_PARALLEL_DDL_DOP = 8;

#.在调整并行度后，还需要调整相应的 parallel_servers_target 该值设置应大于所有并行度之和
SET GLOBAL parallel_servers_target = 100;

参考：https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001699561

OceanBase入门篇

OceanBase 基础操作之 MySQL 模式

基本操作

登入与登出

实例的管理

数据库的管理

数据表的管理

增删改查

租户的管理

账号的管理

授权与撤销

三类示范账号

连接数与会话

字符集

索引与调优

索引的创建与删除

其他优化

results matching ""

No results matching ""