南大通用GBase 8a集群列文件和列名顺序的对照关系

发表于 作者 laozizhu

GBase 8a集群里默认按照建表语句的字段先后顺序，在磁盘上生成文件名递增的类似C00000.seg数据文件。但当表的列出现删除，新增后，列的顺序与磁盘文件名的顺序将不再保障对应。本文介绍如何找到某一个列的数据文件的方法。

环境

集群版本

8.6.2.43.33 和 9.5.2.43.5

表结构

先建1个两列的表

gbase> create table tt(id int, name varchar(100));
Query OK, 0 rows affected (Elapsed: 00:00:02.43)

gbase> desc tt;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)

再增加2个列到id后面，注意顺序是id,id3，id2,name 了。

gbase> alter table tt add column id2 int after id;
Query OK, 0 rows affected (Elapsed: 00:00:00.30)
Records: 0  Duplicates: 0  Warnings: 0

gbase> alter table tt add column id3 int after id;
Query OK, 0 rows affected (Elapsed: 00:00:00.28)
Records: 0  Duplicates: 0  Warnings: 0

gbase> desc testdb.tt;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| id3   | int(11)      | YES  |     | NULL    |       |
| id2   | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (Elapsed: 00:00:00.00)

测试

列顺序

就是如上的desc的顺序，如果要更精细的，可以查询元数据表columns

gbase> select column_name,ORDINAL_POSITION from information_schema.columns where table_schema='testdb' and table_name='tt';
+-------------+------------------+
| column_name | ORDINAL_POSITION |
+-------------+------------------+
| id          |                1 |
| id3         |                2 |
| id2         |                3 |
| name        |                4 |
+-------------+------------------+
4 rows in set (Elapsed: 00:00:00.01)

列数据文件

通过show full create tablle XXXX 可以看到COLUMN_IDS，标识列的名字顺序

gbase> show full create table tt;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt    | CREATE TABLE "tt" (
  "id" int(11) DEFAULT NULL,
  "id3" int(11) DEFAULT NULL,
  "id2" int(11) DEFAULT NULL,
  "name" varchar(100) DEFAULT NULL
) ENGINE=EXPRESS TID(297) UID(1) DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' COLUMN_IDS(0, 3, 2, 1) |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

可以看到列文件都是C开头的，包含递增数字的.seg文件。其中C00000是第1组，C00001是第2组。如下图可以看到共4组，对应4个列文件。

如果表创建后，没有做过删除，中间添加列的操作，那么列文件的顺序就和表列的顺序完全对应。 第1列=C00000, 第2列=C00001。

如果删除添加过，看后面的介绍。

[root@rh6-1 tt_n1]# ll
total 28
-rw------- 1 gbase gbase  40 May 19 12:53 C00000.seg
-rw------- 1 gbase gbase  37 May 19 12:52 C00000.seg.1
-rw------- 1 gbase gbase 114 May 19 12:53 C00001.seg
-rw------- 1 gbase gbase  91 May 19 12:52 C00001.seg.1
-rw------- 1 gbase gbase  37 May 19 12:52 C00002.seg
-rw------- 1 gbase gbase  40 May 19 12:53 C00002.seg.1
-rw------- 1 gbase gbase  40 May 19 12:53 C00003.seg
[root@rh6-1 tt_n1]#

数据文件和列对应关系

通过show full create table

show full create table XXX的最简单，COLUMN_IDS(0, 3, 2, 1) 表示对应1，2，3，4列的文件顺序是0,3,2,1

gbase> show full create table tt;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt    | CREATE TABLE "tt" (
  "id" int(11) DEFAULT NULL,
  "id3" int(11) DEFAULT NULL,
  "id2" int(11) DEFAULT NULL,
  "name" varchar(100) DEFAULT NULL
) ENGINE=EXPRESS TID(297) UID(1) DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' COLUMN_IDS(0, 3, 2, 1) |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

通过元数据文件metadump

V86版本

进入表的元数据目录(metadata/表名.GED)， 运行metadump table.des.B 或者A。其中的attr_nums对应列，里面的数字对应列顺序，后面的值对应CXXXXX的顺序。如下是摘录的

0 = id = C00000
1 = id3 = C00003
2 = id2 = C00002
3 = name = C00001

[gbase@rh6-1 tt.GED]$ metadump table.des.B
This tool is designed for use at GBase internally,and is unsupported externally.
GBase makes no claims and holds no liability regarding the external use of this tool.
This tool is copyrighted and his redistribution is restricted.
table.des.B
+----------------------+-----------------+
| storage_version      | GEHV7           |
| encrypt              | 0               |
| shrinked_rowid       | 0               |
| no_attr              | 4               |
| attr_nums[0]         | 0               |
| attr_nums[1]         | 3               |
| attr_nums[2]         | 2               |
| attr_nums[3]         | 1               |
| no_attr_cnt          | 4               |
| no_attr[0]           | 1               |
| no_attr[1]           | 1               |
..... 其余的不写了

V95版本

同样是metadata/表名.GED目录，用table.info.B/A, 需要用grep过滤下。 如下的输出，可以获得和V86相同的结果。

0 = id = C00000
1 = id3 = C00003
2 = id2 = C00002
3 = name = C00001

[gbase@localhost tt.GED]$ metadump table.info.B  | grep attr
| no_attr              | 4               |
| no_attr_cnt          | 4               |
---------------------------attr 0------------------------
| attr_number          | 0                    |
---------------------------attr 1------------------------
| attr_number          | 3                    |
---------------------------attr 2------------------------
| attr_number          | 2                    |
---------------------------attr 3------------------------
| attr_number          | 1                    |


或者
[gbase@localhost tt.GED]$ metadump table.info.B  | grep attr_number
| attr_number          | 0                    |
| attr_number          | 3                    |
| attr_number          | 2                    |
| attr_number          | 1                    |

删除列测试

base> alter table tt drop column id2;
Query OK, 0 rows affected (Elapsed: 00:00:00.32)
Records: 0  Duplicates: 0  Warnings: 0

gbase> desc testdb.tt;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| id3   | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (Elapsed: 00:00:00.00)

查看顺序，对应0，3，1， 其中C00002消失了。

[gbase@localhost tt.GED]$ ll /opt/gbase/gnode/userdata/gbase/testdb/sys_tablespace/tt_n1/
total 12
-rw------- 1 gbase gbase 42 May 19 14:07 C00000.seg.1
-rw------- 1 gbase gbase 98 May 19 14:07 C00001.seg.1
-rw------- 1 gbase gbase 42 May 19 14:07 C00003.seg
[gbase@localhost tt.GED]$

[gbase@localhost tt.GED]$ metadump table.info.B  | grep attr
| no_attr              | 3               |
| no_attr_cnt          | 4               |
---------------------------attr 0------------------------
| attr_number          | 0                    |
---------------------------attr 1------------------------
| attr_number          | 3                    |
---------------------------attr 2------------------------
| attr_number          | 1                    |
Post Views: 306

相关文章:

  1. 南大通用GBase 8a集群如何查看版本号
  2. 南大通用GBase 8a集群手工修复gssys表的损坏
  3. 南大通用GBase 8a集群版本命名规范
  4. 南大通用GBase 8a集群通过sftp和ftp加载单个小文件非常慢的问题，关闭DNS反向解析
  5. 南大通用GBase 8a 数据库集群常用优化手段建议方法
  6. 南大通用GBase 8a数据库快速手工卸载
  7. 南大通用GBase 8a数据库数据不一致ddlevent故障模拟和恢复过程分析
  8. 南大通用GBase 8a数据库数据不一致dmlevent故障模拟和恢复过程分析
  9. 南大通用GBase 8a数据的缺点是什么
  10. 南大通用GBase 8a 加载LOAD数据无法入库错误 skipped records，text parser error