MySQL学习笔记1 系统库综述
− 系统数据库在每个gcluster节点、gnode节点上都存在；
− 系统库里的系统表所查询到的信息大多是本地信息；
• Information_schema
• performance_schema
• gbase
• gclusterdb
2 集群元数据
—2.1 information_schema库
库内表为系统视图（MEMORY引擎表，只读），在查询时通过检索扫描相关文件获取集群的元数据信息， 如库或表的名称、列的数据类型、访问权限、数据加载结果及状态信息、资源信息等；
—2.2 performance_schema库
与information_schema库相似，库内表为系统视图，区别在于performance_schema用于 监控数据库本地运行时的信息，包括运行状态信息、磁盘、内存使用情况等；
—2.3 gbase库
gbase库内的系统表为GsSYS引擎表，存储用户及权限、审计日志、函数/存储过程、UDF及引擎插件等信息；
—2.4 gclusterdb库
gclusterdb库的系统表为express引擎表，主要为dual、数据重分布、kafka-consumer流数据处理、 temporary临时表对应的物理映射表等系统表（部分系统表在使用相关功能时才生成）；
3 系统表查询场景示例
INFORMATION_SCHEMA系统库信息查询示例：
− 查询VC虚拟集群
SELECT * FROM INFORMATION_SCHEMA.vc;
− 查询数据库
SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA;
− 查询某个库的所有表和视图
SELECT table_name, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'test';
− 查询某个表中的列
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM
INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'customer' AND table_schema='ssbm';
− 查询存储过程和自定义函数
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES ;
− 查询索引信息
SELECT table_name , INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, index_type FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema='ssbm';
4 元数据信息查询
show vcs; -- 查看系统中VC名的语句
show databases;-- 查看系统中数据库名的语句（根据权限显示）
show tables from test like 't%';-- 列出给定数据库的所有非临时表
show columns from test.t; 等价于desc test.t;-- 查看test库t表的列信息
show index from test.t;-- 查看test库t表的索引信息
show create table test.t;-- 查看test的建库语句
show create procedure p_demo;-- 查看创建存储过程的语句
5 磁盘空间使用信息查询
performance_schema系统库示例：
• 查询当前节点gcluster层磁盘空间使用信息
select * from performance_schema.DISK_USAGE_INFO;
• 查询集群某节点的gcluster层和gnode层磁盘空间使用信息
select * from performance_schema.CLUSTER_DISK_USAGE_INFO where host='vm1';
6 用户信息查询
gbase系统库示例：
• 查询数据库中的用户
gbase> select user from gbase.user;
• 查询数据库中的用户与用户组role的关联关系。
gbase> select * from gbase.role_edges \G
7 hash分布表和分布键信息查询
gbase系统库示例：
• 如何查看哪些表是复制表，哪些是hash分布表，分布键是哪列?
− select dbName,tbName,isReplicate,hash_column from gbase.table_distribution where dbname= '库名' and tbname like '表名';
− show create table 表名;
gbase> select dbName,tbName,isReplicate,hash_column from gbase.table_distribution where dbname= 'ssbm' ; +--------+-----------+-------------+-------------+ | dbName | tbName | isReplicate | hash_column | +--------+-----------+-------------+-------------+ | ssbm | lineorder | NO | lo_custkey | | ssbm | part | YES | NULL | | ssbm | supplier | YES | NULL | | ssbm | customer | NO | c_custkey | | ssbm | dwdate | NO | NULL | +--------+-----------+-------------+-------------+ 5 rows in set (Elapsed: 00:00:00.34)
8 查询分布表数据量
如何判断分布表数据分布没有倾斜？
● select * from information_schema.cluster_table_segments where table_schema ='库名' and table_name = '表名'；
- cluster_table_segments ：记录每个分片的数据占用磁盘空间信息
gbase> select * from information_schema.cluster_table_segments where table_schema = 'ssbm' and table_name = 'lineorder'; +----------+--------------+------------+--------+--------------+-----------------+--------------------+--------------+ | TABLE_VC | TABLE_SCHEMA | TABLE_NAME | SUFFIX | HOST | TABLE_DATA_SIZE | TABLE_STORAGE_SIZE | DATA_PERCENT | +----------+--------------+------------+--------+--------------+-----------------+--------------------+--------------+ | vc1 | ssbm | lineorder | n1 | 172.16.9.166 | 97555674 | 97604152 | 50.5161% | | vc1 | ssbm | lineorder | n2 | 172.16.9.167 | 95562216 | 95609793 | 49.4839% | +----------+--------------+------------+--------+--------------+-----------------+--------------------+--------------+ 2 rows in set (Elapsed: 00:00:02.89)
9 查询分布表数据量
如何查询表总条数
● select count(*) from 表名;
● select TABLE_SCHEMA,TABLE_NAME, TABLE_ROWS/2，STORAGE_SIZE/2 from performance_schema.tables where TABLE_SCHEMA='库名' and TABLE_NAME='表名'；
− TABLE_ROWS：为表所有分片的条数和，当表分片有一个副本时，是表条数的2倍；当表分片有两个副本时，是表条数的3倍；
gbase> select TABLE_SCHEMA,TABLE_NAME,round(TABLE_ROWS/2) ROWS, round(STORAGE_SIZE/2/1024) STORAGE_SIZE_kb from performance_schema.tables where TABLE_SCHEMA='ssbm' and TABLE_NAME='lineorder'; +--------------+------------+---------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | ROWS | STORAGE_SIZE_kb | +--------------+------------+---------+-----------------+ | ssbm | lineorder | 6001171 | 188772 | +--------------+------------+---------+-----------------+ 1 row in set (Elapsed: 00:00:00.34)1 row in set (Elapsed: 00:00:00.35) gbase> select count(*) from ssbm.lineorder; +----------+ | count(*) | +----------+ | 6001171 | +----------+ 1 row in set (Elapsed: 00:00:00.52)
10 显示/设置服务器系统变量
显示8a集群的系统变量值：
SELECT * from information_schema.global_variables where variable_name like 'pattern';
SHOW VARIABLES [LIKE 'pattern'];
设置系统变量值
SET [GLOBAL | SESSION] <variablename> = value
- GLOBAL 全局变量值改变，只对新的session会话起作用。
- SESSION默认为会话变量，可不写。
举例：
− 开启本节点的trace日志，默认是设置 session 变量。
SHOW VARIABLES like 'gbase_sql_trace%';
SET gbase_sql_trace = on;
11 系统压缩模式查询
查询8a集群的默认压缩模式信息：
SHOW global variables like '%compress%';
SELECT * from information_schema.global_variables where variable_name like '%compress%';
gbase> show global variables like '%compress%'; +---------------------------------+------------+ | Variable_name | Value | +---------------------------------+------------+ | _gbase_delete_filter_compressed | OFF | | _gbase_tx_log_compress | ON | | gbase_compress_level | 0 | | gbase_compress_method | No Setting | | gbase_compression_num_method | 5 | | gbase_compression_sampling | 1 | | gbase_compression_str_method | 5 | | have_compress | YES | | slave_compressed_protocol | OFF | +---------------------------------+------------+
gbase> select * from information_schema.global_variables where variable_name like '%compress%'; +---------------------------------+----------------+---------------+-----------+ | VARIABLE_NAME | VARIABLE_VALUE | SESSION_LEVEL | WRITEABLE | +---------------------------------+----------------+---------------+-----------+ | _GBASE_TX_LOG_COMPRESS | ON | 0 | 0 | | GBASE_COMPRESSION_SAMPLING | 1 | 0 | 0 | | _GBASE_DELETE_FILTER_COMPRESSED | OFF | 0 | 0 | | GBASE_COMPRESSION_NUM_METHOD | 5 | 0 | 0 | | HAVE_COMPRESS | YES | 0 | 0 | | GBASE_COMPRESS_LEVEL | 0 | 0 | 0 | | GBASE_COMPRESSION_STR_METHOD | 5 | 0 | 0 | | GBASE_COMPRESS_METHOD | No Setting | 0 | 0 | | SLAVE_COMPRESSED_PROTOCOL | OFF | 0 | 1 | +---------------------------------+----------------+---------------+-----------+
12 查询当前节点运行信息
查询当前节点正在运行的线程信息：
SHOW [FULL] PROCESSLIST; -- 可以查看正在运行的线程。
select id,user,host,db,command,time,state,info from information_schema.processlist;
13 管理元数据库线程
终止正在运行的线程（SQL语句)：
KILL [CONNECTION | QUERY] thread_id;
− CONNECTION参数：用于终止指定的thread_id 线程，默认参数。
− QUERY参数： 中止连接当前执行的语句，但是不中止该连接本身。
暂停正在运行的线程：
PAUSE thread_id；
继续已暂停的线程：
CONTINUE thread_id；
14 显示语句错误信息
显示最后一条警告或错误信息：
SHOW WARNINGS; -- 显示由最后一个语句产生的错误，警告和注意信息。
SHOW ERRORS; -- 显示由最后一个语句产生的错误信息。
gbase> use test; Query OK, 0 rows affected (Elapsed: 00:00:00.01) gbase> drop table a; ERROR 1702 (HY000): gcluster table error: (GBA-02DD-0017) Unknown table 'test.a' gbase> show errors; +-------+------+--------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------+ | Error | 1702 | gcluster table error: (GBA-02DD-0017) Unknown table 'test.a' | +-------+------+--------------------------------------------------------------+
15 显示集群节点信息
显示集群节点信息：
SHOW NODES; --如果有SUPER 权限，可以看到所有节点信息。
SHOW LOCAL NODE; -- 显示集群中客户端正在访问节点的信息。
gbase> show nodes; +------------+--------------+-------+--------------+----------------+--------+-----------+ | Id | ip | name | primary part | duplicate part | status | datastate | +------------+--------------+-------+--------------+----------------+--------+-----------+ | 2718503084 | 172.16.9.162 | node1 | n1 | n4 | online | 0 | | 2735280300 | 172.16.9.163 | node2 | n2 | n1 | online | 0 | | 2752057516 | 172.16.9.164 | node3 | n3 | n2 | online | 0 | | 2768834732 | 172.16.9.165 | node4 | n4 | n3 | online | 0 | +------------+--------------+-------+--------------+----------------+--------+-----------+ gbase> show local node; +------------+--------------+--------------+--------+ | Id | ip | name | status | +------------+--------------+--------------+--------+ | 2718503084 | 172.16.9.162 | coordinator1 | ONLINE | +------------+--------------+--------------+--------+
16 查询当前系统信息
使用系统函数，查看当前系统信息：
version() -- 返回当前8a系统版本
vc() -- 返回当前VC名字
database() -- 返回当前数据库名字
user() -- 返回当前用户名
charset(str) -- 返回字符串参数使用的字符集
使用 use 切换当前VC/数据库：
use vc vc1； -- 切换到vc1虚拟集群
use test； -- 切换到test数据库
gbase> select version();
+-----------------+
| version() |
+-----------------+
| 9.5.2.23.119588 |
+-----------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select vc();
+---------+
| vc() |
+---------+
| vc00001 |
+---------+
gbase> use test;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
gbase> select database(),user(),charset('南大通用');
+------------+----------------+-------------------------+
| database() | user() | charset('南大通用') |
+------------+----------------+-------------------------+
| test | root@localhost | utf8 |
+------------+----------------+-------------------------+
1 row in set (Elapsed: 00:00:00.00)