OceanBase数据字典视图学习与总结(MySQL模式)

OceanBase 数据库的系统视图分为字典视图和性能视图。其中字典视图就是描述数据字典的视图,OceanBase 数据库的字典视图包含 information_schema.* 视图、oceanbase.CDB_* 视图、oceanbase.DBA_*视图以及 mysql.* 视图。本文所涉及的版本主要为OceanBase4.1.0。

information_schema.* 视图

该类视图提供对 MySQL 租户中数据库元数据(包括:数据库或表的名称、列的数据类型或访问权限)的访问。称为数据字典或系统目录。这个视图类似MySQL数据库的information_schema,我们在MySQL中经常使用processlist、tables等查看当前连接,数据库表信息等,在oceanbase数据库中同样提供了这样的接口,这对MySQL DBA是比较方便的。可以看到,processlist和tables的表结构和MySQL里的也是如出一辙。

MySQL [information_schema]> select * from processlist limit 3;
+------------+------+------------------+--------------------+---------+-------+--------+-----------------------------------+
| ID         | USER | HOST             | DB                 | COMMAND | TIME  | STATE  | INFO                              |
+------------+------+------------------+--------------------+---------+-------+--------+-----------------------------------+
| 1111111570 | root | 10.10.97.94:49426 | bghg               | Sleep   |  1430 | SLEEP  | NULL                              |
| 1111119446 | root | 10.10.97.94:6786  | information_schema | Query   |     0 | ACTIVE | select * from processlist limit 3 |
| 1111111020 | root | 10.10.97.84:59088 | oceanbase          | Sleep   | 24743 | SLEEP  | NULL                              |
+------------+------+------------------+--------------------+---------+-------+--------+-----------------------------------+
3 rows in set (0.01 sec)
MySQL [information_schema]> desc tables;
+-----------------+--------------------+------+-----+---------+-------+
| Field           | Type               | Null | Key | Default | Extra |
+-----------------+--------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(3)         | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(128)       | NO   |     |         |       |
| TABLE_NAME      | varchar(256)       | NO   |     |         |       |
| TABLE_TYPE      | varchar(12)        | NO   |     |         |       |
| ENGINE          | null               | NO   |     |         |       |
| VERSION         | null               | NO   |     |         |       |
| ROW_FORMAT      | null               | NO   |     |         |       |
| TABLE_ROWS      | decimal(20,0)      | NO   |     |         |       |
| AVG_ROW_LENGTH  | decimal(24,4)      | NO   |     |         |       |
| DATA_LENGTH     | decimal(20,0)      | NO   |     |         |       |
| MAX_DATA_LENGTH | null               | NO   |     |         |       |
| INDEX_LENGTH    | null               | NO   |     |         |       |
| DATA_FREE       | null               | NO   |     |         |       |
| AUTO_INCREMENT  | null               | NO   |     |         |       |
| CREATE_TIME     | timestamp(6)       | NO   |     | NULL    |       |
| UPDATE_TIME     | timestamp(6)       | NO   |     | NULL    |       |
| CHECK_TIME      | null               | NO   |     |         |       |
| TABLE_COLLATION | varchar(128)       | NO   |     |         |       |
| CHECKSUM        | bigint(0) unsigned | NO   |     |         |       |
| CREATE_OPTIONS  | null               | NO   |     |         |       |
| TABLE_COMMENT   | varchar(4096)      | NO   |     |         |       |
+-----------------+--------------------+------+-----+---------+-------+
21 rows in set (0.02 sec)

mysql.* 视图

这类视图其实也是放数据库里面元数据的的数据字典表,和MySQL类似,用的比较多的是mysql.user表,用来查数据库里的用户信息。

oceanbase.CDB_* 视图

从命名为CDB可以看出,这个视图是SYS租户下才能看到,从整个集群层面,获取全部租户的一些数据字典信息。oceanbase.CDB_TABLES展示了所有租户表的信息,oceanbase.CDB_OB_DATABASES展示所有租户的数据库元信息,oceanbase.CDB_TAB_PARTITIONS展示所有租户的分区表的一级分区信息。

oceanbase.DBA_*视图

该类视图可用于获取整个数据库中的所有相关信息。该类视图需要使用管理员权限访问。对应于oceanbase.CDB_* 视图,oceanbase.DBA_*视图更多的是用来描述租户级别的数据字典信息。比如oceanbase.DBA_OB_DATABASES展示的是租户级别的数据库元信息,对应于oceanbase.CDB_OB_DATABASES。oceanbase.DBA_TAB_PARTITIONS描述的是在当前租户下当前用户可访问的多级分区信息、分区存储参数和分区统计信息。

此外,也有部分oceanbase.DBA_*视图只能在SYS租户下才能访问到,这些主要都是集群层面的监控。oceanbase.DBA_OB_TENANTS,展示所有租户的基本信息(普通用户访问时只展示本租户的信息)。oceanbase.DBA_OB_SERVERS展示所有 OBServer 节点的信息,oceanbase.DBA_OB_ZONES展示所有 Zone 的信息。

值得注意的是oceanbase.DBA_*视图和大部分oceanbase.CDB_* 视图在4.0.0以下的版本是没有的。值得一提的是,3.x.x版本的oceanbase提供了很多__all_*视图,__all_server和oceanbase.DBA_OB_SERVERS功能类似,__all_telnet和oceanbase.DBA_OB_TENANTS功能类似。但是在OceanBase的官方文档中,并没有找到对__all_*视图的相关描述,白鳝老师也在他的文章中吐槽过ob的元数据藏得太深:https://mp.weixin.qq.com/s/gyxABzj7OY8o5vH-Kf0t2w 。这也是ob后续需要优化的一个方向吧。

obclient> desc __all_server;
+-----------------------+--------------+------+-----+----------------------+--------------------------------+
| Field                 | Type         | Null | Key | Default              | Extra                          |
+-----------------------+--------------+------+-----+----------------------+--------------------------------+
| gmt_create            | timestamp(6) | YES  |     | CURRENT_TIMESTAMP(6) |                                |
| gmt_modified          | timestamp(6) | YES  |     | CURRENT_TIMESTAMP(6) | ON UPDATE CURRENT_TIMESTAMP(6) |
| svr_ip                | varchar(46)  | NO   | PRI | NULL                 |                                |
| svr_port              | bigint(20)   | NO   | PRI | NULL                 |                                |
| id                    | bigint(20)   | NO   |     | NULL                 |                                |
| zone                  | varchar(128) | NO   |     | NULL                 |                                |
| inner_port            | bigint(20)   | NO   |     | NULL                 |                                |
| with_rootserver       | bigint(20)   | NO   |     | NULL                 |                                |
| status                | varchar(64)  | NO   |     | NULL                 |                                |
| block_migrate_in_time | bigint(20)   | NO   |     | NULL                 |                                |
| build_version         | varchar(256) | NO   |     | NULL                 |                                |
| stop_time             | bigint(20)   | NO   |     | 0                    |                                |
| start_service_time    | bigint(20)   | NO   |     | NULL                 |                                |
| first_sessid          | bigint(20)   | NO   |     | 0                    |                                |
| with_partition        | bigint(20)   | NO   |     | 0                    |                                |
| last_offline_time     | bigint(20)   | NO   |     | 0                    |                                |
+-----------------------+--------------+------+-----+----------------------+--------------------------------+
16 rows in set (0.01 sec)
obclient> select * from __all_tenant limit 1 \G
*************************** 1. row ***************************
                 gmt_create: 2022-07-19 10:57:45.156936
               gmt_modified: 2022-07-19 10:57:45.156936
                  tenant_id: 1
                tenant_name: sys
                replica_num: -1
                  zone_list: zone1;zone2;zone3
               primary_zone: zone1;zone2,zone3
                     locked: 0
             collation_type: 0
                       info: system tenant
                  read_only: 0
      rewrite_merge_version: 0
                   locality: FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3
        logonly_replica_num: 0
          previous_locality: 
     storage_format_version: 0
storage_format_work_version: 0
      default_tablegroup_id: -1
         compatibility_mode: 0
           drop_tenant_time: -1
                     status: TENANT_STATUS_NORMAL
              in_recyclebin: 0
1 row in set (0.00 sec)

 

热门相关:恭喜你被逮捕了   今天也没变成玩偶呢   饥渴女大学生的秘密性爱   未来兽世:买来的媳妇,不生崽   异世修真邪君