南大通用数据库-Gbase-8a-学习-36-查看SQL的数据分布情况

阳光九叶草LXGZXJ

已于 2023-06-27 17:40:28 修改

阅读量1k

点赞数 2

分类专栏： # Gbase-8a-学习文章标签：学习数据库运维 sql linux

于 2023-06-27 14:07:55 首次发布

本文链接：https://blog.csdn.net/qq_45111959/article/details/131413226

版权

Gbase-8a-学习专栏收录该内容

44 篇文章 17 订阅

订阅专栏

一、测试环境

名称	值
CPU	Intel(R) Core(TM) i5-1035G1 CPU @ 1.00GHz
操作系统	CentOS Linux release 7.9.2009 (Core)
内存	3G
逻辑核数	2
Gbase8a版本	9.5.3.27.17c111049

二、数据库集群部署模式

[gbase@czg1 ~]$ gcadmin
CLUSTER STATE:         ACTIVE

======================================
|  GBASE GCWARE CLUSTER INFORMATION  |
======================================
| NodeName |   IpAddress    | gcware |
--------------------------------------
| gcware1  | 192.168.142.10 |  OPEN  |
--------------------------------------
| gcware2  | 192.168.142.11 |  OPEN  |
--------------------------------------
========================================================
|        GBASE COORDINATOR CLUSTER INFORMATION         |
========================================================
|   NodeName   |   IpAddress    | gcluster | DataState |
--------------------------------------------------------
| coordinator1 | 192.168.142.10 |   OPEN   |     0     |
--------------------------------------------------------
| coordinator2 | 192.168.142.11 |   OPEN   |     0     |
--------------------------------------------------------
=========================================================================
|                   GBASE VIRTUAL CLUSTER INFORMATION                   |
=========================================================================
|    VcName    | DistributionId |                comment                |
-------------------------------------------------------------------------
|     vc1      |       1        | comment message no more than 60 bytes |
-------------------------------------------------------------------------
|     vc2      |       2        | comment message no more than 60 bytes |
-------------------------------------------------------------------------

2 virtual cluster: vc1, vc2
2 coordinator node
0 free data node

三、集群分片情况

[gbase@czg1 ~]$ gcadmin showdistribution vc vc1

                                 Distribution ID: 1 | State: new | Total segment num: 1

             Primary Segment Node IP                   Segment ID                 Duplicate Segment node IP
========================================================================================================================
|                 192.168.142.10                 |         1          |                                                |
========================================================================================================================
[gbase@czg1 ~]$ gcadmin showdistribution vc vc2

                                 Distribution ID: 2 | State: new | Total segment num: 1

             Primary Segment Node IP                   Segment ID                 Duplicate Segment node IP
========================================================================================================================
|                 192.168.142.11                 |         1          |                                                |
========================================================================================================================

四、参数介绍

953版本之后才支持这个参数。

相当于在你原有的SQL中可以加入segment_id字段，查看SQL都是用哪些分片的数据。

参数名	描述
gcluster_segment_id_replace	说明：默认值为 0，不支持 segment_id(tbname) 函数，值设置为 1 时支持 Segment_id(tbname)函数。修改方式：可使用 set 语句修改值也可在配置文件中修改值。适用于 session、global 范围均可。

五、参数测试实验

我这边由于是两个节点（主要是电脑带不动太多虚机），一个节点一个vc，所以实验效果并不明显，显示的都是1，实际生产环境查看数据分布情况肯定更加明显。

1、表结构

gbase> desc vc1.czg.czg;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | int(11)      | YES  |     | NULL    |       |
| b     | varchar(100) | YES  | MUL | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> desc vc2.czg.czg;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)

2、配置参数

gbase> show variables like '%seg%';
+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| _gbase_segment_size                             | 2147483648 |
| _t_gcluster_single_segment_cluster_optimization | 0          |
| gcluster_segment_id_replace                     | 0          |
+-------------------------------------------------+------------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> set gcluster_segment_id_replace = 1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

3、单表

gbase> select segment_id from czg tab1 where tab1.a = 1 group by segment_id;
+------------+
| segment_id |
+------------+
|          1 |
+------------+
1 row in set (Elapsed: 00:00:00.07)

gbase> select segment_id from czg tab1 where tab1.a = 1;                                                                                         
+------------+
| segment_id |
+------------+
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
+------------+
1 row in set (Elapsed: 00:00:00.09)

4、多表

gbase> select segment_id(tab1),segment_id(tab2) from czg tab1, vc2.czg.czg tab2 where tab1.a = tab2.a and tab1.a = 1 limit 10;
+------------+------------+
| segment_id | segment_id |
+------------+------------+
|          1 |          1 |
|          1 |          1 |
|          1 |          1 |
|          1 |          1 |
|          1 |          1 |
|          1 |          1 |
|          1 |          1 |
|          1 |          1 |
|          1 |          1 |
|          1 |          1 |
+------------+------------+
10 rows in set (Elapsed: 00:00:00.31)

gbase> select segment_id(tab1),segment_id(tab2) from czg tab1, vc2.czg.czg tab2 where tab1.a = tab2.a group by segment_id(tab1),segment_id(tab2);
+------------+------------+
| segment_id | segment_id |
+------------+------------+
|          1 |          1 |
+------------+------------+
1 row in set (Elapsed: 00:00:00.12)

六、非953版本如何查看SQL数据分布情况

gbase> select crc32(tab1.a)%65536 as NewHashVal from sun tab1, moon tab2 where tab1.a = tab2.a group by tab1.a;
+------------+
| NewHashVal |
+------------+
|      40790 |
|      54049 |
+------------+
2 rows in set (Elapsed: 00:00:00.03)

gbase> select * from gbase.nodedatamap  where hashkey in (40790,54049);
+---------+--------+----------------------+
| hashkey | nodeid | data_distribution_id |
+---------+--------+----------------------+
|   40790 |      0 |                    1 |
|   54049 |      0 |                    1 |
+---------+--------+----------------------+
2 rows in set (Elapsed: 00:00:00.00)

查看nodeid字段，表示：表分片 id。从 0 开始，与分片一一对应关系。可以通过 gcadmin showdistribution 查看 segment id。这里的 nodeid = segment id - 1

如果是以下写法会提示不支持的错误，以后我再摸索一下。

gbase> select * from gbase.nodedatamap where hashkey in (select crc32(tab1.a)%65536 as NewHashVal from sun tab1, moon tab2 where tab1.a = tab2.a group by tab1.a);
ERROR 1733 (HY000): (GBA-01EX-700) The query includes syntax that is not supported by the Express engine. Either restructure the query with supported syntax, or enable the GBase Query Path in the configuration file to execute the query with reduced performance.