select into outfile 语句是常用的一种数据导出方式，该语句能够对需要导出的字段做出限制，这很好地满足了某些不需要导出主键字段的场景。配合 load data infile 语句导入数据，是一种便利的数据导入导出方式。
目前通过 select into outfile 直接导出的表文件都位于 OBServer 服务器侧（OSS 路径除外），具体位于哪个 OBServer 节点取决于用于导出的 SQL 语句是否被正确路由到相关表的 Leader 节点以及使用的导出选项。本文主要介绍在各种使用场景下导出的表文件的存放位置。
详细说明
用户可以通过 OBProxy 或者直连 OBServer 两种方式连接到 OceanBase 数据库来执行数据导出 SQL 语句。假设 OBProxy 总是能将 select into outfile 语句正确地路由到相关表的 Leader 节点，那么导出的表文件位于该表的 Leader 节点上。如果 OBProxy 不能将 select into outfile 语句正确地路由到相关表的 Leader 节点，导出的文件位置可以参考下面直连 OBServer 的情况。
测试版本
OceanBase 数据库 V4.3.4 GA 版本。
OBProxy V4.3.2.0 版本。
测试表结构
t1 表为非分区表，Leader 位于 xxx.xxx.15.4 节点。
t2 表为 Hash 分区表，Leader 打散到 xxx.xxx.15.3/4/5 三个节点。
MySQL [test]> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`pk1` int(11) NOT NULL,
`id` int(11) DEFAULT NULL,
`f1` varchar(4096) DEFAULT NULL,
`f2` varchar(4096) DEFAULT NULL,
`f3` varchar(4096) DEFAULT NULL,
`f4` varchar(4096) DEFAULT NULL,
`f5` varchar(4096) DEFAULT NULL,
`f6` varchar(4096) DEFAULT NULL,
`f7` varchar(4096) DEFAULT NULL,
`f8` varchar(4096) DEFAULT NULL,
`f9` varchar(4096) DEFAULT NULL,
PRIMARY KEY (`pk1`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT COMPRESSION = 'none' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.009 sec)
MySQL [test]> select * from oceanbase.dba_ob_table_locations where table_name='t1' and role='leader'\G
*************************** 1. row ***************************
DATABASE_NAME: test
TABLE_NAME: t1
TABLE_ID: 500035
TABLE_TYPE: USER TABLE
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
DATA_TABLE_ID: NULL
TABLET_ID: 200014
LS_ID: 1001
ZONE: zone2
SVR_IP: xxx.xxx.15.4
SVR_PORT: 2882
ROLE: LEADER
REPLICA_TYPE: FULL
DUPLICATE_SCOPE: NONE
DUPLICATE_READ_CONSISTENCY: NONE
OBJECT_ID: 500035
TABLEGROUP_NAME: NULL
TABLEGROUP_ID: NULL
SHARDING: NULL
1 row in set (0.026 sec)
MySQL [test]> show create table t2;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`pk1` int(11) NOT NULL,
`id` int(11) DEFAULT NULL,
`f1` varchar(4096) DEFAULT NULL,
`f2` varchar(4096) DEFAULT NULL,
`f3` varchar(4096) DEFAULT NULL,
`f4` varchar(4096) DEFAULT NULL,
`f5` varchar(4096) DEFAULT NULL,
`f6` varchar(4096) DEFAULT NULL,
`f7` varchar(4096) DEFAULT NULL,
`f8` varchar(4096) DEFAULT NULL,
`f9` varchar(4096) DEFAULT NULL,
PRIMARY KEY (`pk1`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by hash(pk1)
(partition `p0`,
partition `p1`,
partition `p2`) |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.004 sec)
MySQL [test]> select * from oceanbase.dba_ob_table_locations where table_name='t2' and role='leader'\G
*************************** 1. row ***************************
DATABASE_NAME: test
TABLE_NAME: t2
TABLE_ID: 500036
TABLE_TYPE: USER TABLE
PARTITION_NAME: p0
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
DATA_TABLE_ID: NULL
TABLET_ID: 200015
LS_ID: 1001
ZONE: zone1
SVR_IP: xxx.xxx.15.3
SVR_PORT: 2882
ROLE: LEADER
REPLICA_TYPE: FULL
DUPLICATE_SCOPE: NONE
DUPLICATE_READ_CONSISTENCY: NONE
OBJECT_ID: 500037
TABLEGROUP_NAME: NULL
TABLEGROUP_ID: NULL
SHARDING: NULL
*************************** 2. row ***************************
DATABASE_NAME: test
TABLE_NAME: t2
TABLE_ID: 500036
TABLE_TYPE: USER TABLE
PARTITION_NAME: p1
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
DATA_TABLE_ID: NULL
TABLET_ID: 200016
LS_ID: 1002
ZONE: zone2
SVR_IP: xxx.xxx.15.4
SVR_PORT: 2882
ROLE: LEADER
REPLICA_TYPE: FULL
DUPLICATE_SCOPE: NONE
DUPLICATE_READ_CONSISTENCY: NONE
OBJECT_ID: 500038
TABLEGROUP_NAME: NULL
TABLEGROUP_ID: NULL
SHARDING: NULL
*************************** 3. row ***************************
DATABASE_NAME: test
TABLE_NAME: t2
TABLE_ID: 500036
TABLE_TYPE: USER TABLE
PARTITION_NAME: p2
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
DATA_TABLE_ID: NULL
TABLET_ID: 200017
LS_ID: 1003
ZONE: zone3
SVR_IP: xxx.xxx.15.5
SVR_PORT: 2882
ROLE: LEADER
REPLICA_TYPE: FULL
DUPLICATE_SCOPE: NONE
DUPLICATE_READ_CONSISTENCY: NONE
OBJECT_ID: 500039
TABLEGROUP_NAME: NULL
TABLEGROUP_ID: NULL
SHARDING: NULL
3 rows in set (0.039 sec)
Case 1）如果导出时没带 single=false 或者带了 single=true，导出的文件位于首先接收到该 SQL 的 OBServer 机器上（不一定是表的 Leader 节点）
说明
导出时如果没带 single=false 或者带了 single=true，导出方式为串行写单个文件。
[root@n153 select_into]# mysql -hxxx.xxx.15.3 -P2881 -uroot@mysql -pxxx -A -c test
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3221654680
Server version: 5.7.25 OceanBase 4.3.4.0 (r100000132024103017-57f7e034b952f62c81fa52782fd21a8856ff95da) (Built Oct 30 2024 18:44:26)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [test]> explain extended select /*+parallel(3)*/ * from t1 into outfile 'select_into/data' single=true;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =========================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------------------- |
| |0 |SELECT INTO | |256 |3054 | |
| |1 |└─PX COORDINATOR | |256 |3054 | |
| |2 | └─EXCHANGE OUT DISTR |:EX10000|256 |1320 | |
| |3 | └─PX BLOCK ITERATOR| |256 |19 | |
| |4 | └─TABLE FULL SCAN|t1 |256 |19 | |
| =========================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output(nil), filter(nil), rowset=256 |
| 1 - output([t1.pk1(0x7f608c21f2b0)], [t1.id(0x7f608c21f6f0)], [t1.f1(0x7f608c21fb30)], [t1.f2(0x7f608c21ff70)], [t1.f3(0x7f608c2203b0)], [t1.f4(0x7f608c2207f0)], |
| [t1.f5(0x7f608c220c30)], [t1.f6(0x7f608c221070)], [t1.f7(0x7f608c2214b0)], [t1.f8(0x7f608c2218f0)], [t1.f9(0x7f608c221d30)]), filter(nil), rowset=256 |
| 2 - output([t1.pk1(0x7f608c21f2b0)], [t1.id(0x7f608c21f6f0)], [t1.f1(0x7f608c21fb30)], [t1.f2(0x7f608c21ff70)], [t1.f3(0x7f608c2203b0)], [t1.f4(0x7f608c2207f0)], |
| [t1.f5(0x7f608c220c30)], [t1.f6(0x7f608c221070)], [t1.f7(0x7f608c2214b0)], [t1.f8(0x7f608c2218f0)], [t1.f9(0x7f608c221d30)]), filter(nil), rowset=256 |
| dop=3 |
| 3 - output([t1.pk1(0x7f608c21f2b0)], [t1.id(0x7f608c21f6f0)], [t1.f1(0x7f608c21fb30)], [t1.f2(0x7f608c21ff70)], [t1.f3(0x7f608c2203b0)], [t1.f4(0x7f608c2207f0)], |
| [t1.f5(0x7f608c220c30)], [t1.f6(0x7f608c221070)], [t1.f7(0x7f608c2214b0)], [t1.f8(0x7f608c2218f0)], [t1.f9(0x7f608c221d30)]), filter(nil), rowset=256 |
| 4 - output([t1.pk1(0x7f608c21f2b0)], [t1.id(0x7f608c21f6f0)], [t1.f1(0x7f608c21fb30)], [t1.f2(0x7f608c21ff70)], [t1.f3(0x7f608c2203b0)], [t1.f4(0x7f608c2207f0)], |
| [t1.f5(0x7f608c220c30)], [t1.f6(0x7f608c221070)], [t1.f7(0x7f608c2214b0)], [t1.f8(0x7f608c2218f0)], [t1.f9(0x7f608c221d30)]), filter(nil), rowset=256 |
| access([t1.pk1(0x7f608c21f2b0)], [t1.id(0x7f608c21f6f0)], [t1.f1(0x7f608c21fb30)], [t1.f2(0x7f608c21ff70)], [t1.f3(0x7f608c2203b0)], [t1.f4(0x7f608c2207f0)], |
| [t1.f5(0x7f608c220c30)], [t1.f6(0x7f608c221070)], [t1.f7(0x7f608c2214b0)], [t1.f8(0x7f608c2218f0)], [t1.f9(0x7f608c221d30)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.pk1(0x7f608c21f2b0)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| PARALLEL(3) |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| PARALLEL(@"SEL$1" "test"."t1"@"SEL$1" 3) |
| FULL(@"SEL$1" "test"."t1"@"SEL$1") |
| PARALLEL(3) |
| OPTIMIZER_FEATURES_ENABLE('4.3.3.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| t1: |
| table_rows:256 |
| physical_range_rows:256 |
| logical_range_rows:256 |
| index_back_rows:0 |
| output_rows:256 |
| table_dop:3 |
| dop_method:Global DOP |
| avaiable_index_name:[t1] |
| stats info:[version=0, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[DEFAULT, STORAGE] |
| Plan Type: |
| DISTRIBUTED |
| Note: |
| Degree of Parallelism is 3 because of hint |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
63 rows in set (0.004 sec)
-- 导出非分区表t1
MySQL [test]> select /*+parallel(3)*/ * from t1 into outfile 'select_into/data' single=true;
ERROR 1017 (HY000): File not exist
MySQL [test]>
-- 在接收到该SQL的observer机器上（非t1表的leader节点）创建需要的目录：
[admin@n153 oceanbase]$ hostname -i
xxx.xxx.15.3
[admin@n153 oceanbase]$ mkdir /home/admin/oceanbase/select_into
MySQL [test]> select /*+parallel(3)*/ * from t1 into outfile 'select_into/data' single=true;
Query OK, 256 rows affected (0.390 sec)
[admin@n153 oceanbase]$ hostname -i
xxx.xxx.15.3
[admin@n153 oceanbase]$ ls -tlr /home/admin/oceanbase/select_into/
total 8200
-rw-r--r-- 1 admin admin 8393512 Nov 6 11:45 data
[admin@n153 oceanbase]$
-- 导出分区表t2
MySQL [test]> select /*+parallel(3)*/ * from t2 into outfile 'select_into2/data' single=true;
ERROR 1017 (HY000): File not exist
-- 在接收到该SQL的observer机器上创建需要的目录：
[admin@n153 oceanbase]$ hostname -i
xxx.xxx.15.3
[admin@n153 oceanbase]$ mkdir select_into2
MySQL [test]> select /*+parallel(3)*/ * from t2 into outfile 'select_into2/data' single=true;
Query OK, 256 rows affected (17.547 sec)
[admin@n153 oceanbase]$ hostname -i
xxx.xxx.15.3
[admin@n153 oceanbase]$ ls -tlr select_into2
total 8200
-rw-r--r-- 1 admin admin 8393512 Nov 7 10:13 data
Case 2）如果导出时带了 single=false 且 DoP=1，导出的文件位于首先接收到该 SQL 的 OBServer 机器上（不一定是表的 Leader节点）
说明
- 导出时如果带了
single=false且 DoP=1，导出方式为串行写文件，文件个数和单个文件的大小上限受max_file_size控制。 MAX_FILE_SIZE [=] {int | string}：用于控制导出时单个文件的大小，缺省为 256MB，仅在SINGLE = FALSE时生效。- 这边的 DoP 取决于表级并行 Hint 如
parallel(t1 3)、全局并行 Hint 如parallel(3)、并行度 Session 变量配置、Schema DOP 的设置，优先级依次从高到低。
[root@n153 ~]# mysql -hxxx.xxx.15.3 -P2881 -uroot@mysql -pxxx -A -c test
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3221615874
Server version: 5.7.25 OceanBase 4.3.4.0 (r100000132024103017-57f7e034b952f62c81fa52782fd21a8856ff95da) (Built Oct 30 2024 18:44:26)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
-- 导出非分区表t1
MySQL [test]> select /*+parallel(1)*/ * from t1 into outfile 'select_into/data' single=false;
ERROR 1017 (HY000): File not exist
MySQL [test]>
-- 在接收到该SQL的observer机器上（非t1表的leader节点）创建需要的目录：
[admin@n153 oceanbase]$ hostname -i
xxx.xxx.15.3
[admin@n153 oceanbase]$ mkdir select_into
MySQL [test]> select /*+parallel(1)*/ * from t1 into outfile 'select_into/data' single=false;
Query OK, 256 rows affected (0.386 sec)
[admin@n153 oceanbase]$ hostname -i
xxx.xxx.15.3
[admin@n153 oceanbase]$ ls -tlr select_into/
total 8200
-rw-r--r-- 1 admin admin 8393512 Nov 7 10:21 data_0
[admin@n153 oceanbase]$
-- 导出分区表t2
MySQL [test]> select /*+parallel(1)*/ * from t2 into outfile 'select_into2/data' single=false;
ERROR 1017 (HY000): File not exist
MySQL [test]>
-- 在接收到该SQL的observer机器上创建需要的目录：
[admin@n153 oceanbase]$ hostname -i
xxx.xxx.15.3
[admin@n153 oceanbase]$ mkdir select_into2
[admin@n153 oceanbase]$
MySQL [test]> select /*+parallel(1)*/ * from t2 into outfile 'select_into2/data' single=false;
Query OK, 256 rows affected (17.552 sec)
[admin@n153 oceanbase]$ hostname -i
xxx.xxx.15.3
[admin@n153 oceanbase]$ ls -tlr select_into2/
total 8200
-rw-r--r-- 1 admin admin 8393512 Nov 7 10:26 data_0
Case 3）如果导出时带了 single=false 且 DoP > 1，导出的文件位于对应的表 Leader 所在的 OBServer 机器上
说明
- 一旦指定了
single=false且 DoP > 1，导出时会通过并行写多个文件来提高性能，导出的文件个数受 DoP 和max_file_size共同决定，但文件个数至少为 DoP。 MAX_FILE_SIZE [=] {int | string}：用于控制导出时单个文件的大小，缺省为 256MB，仅在SINGLE = FALSE时生效。- 这边的 DoP 取决于表级并行 Hint 如
parallel(t1 3)、全局并行 Hint 如parallel(3)、并行度 Session 变量配置、Schema DoP 的设置，优先级依次从高到低。
对于非分区表，导出的所有文件都会位于该表的 Leader 节点上：
MySQL [test]> explain extended select /*+parallel(3)*/ * from t1 into outfile 'select_into/data' single=false;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ======================================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------------------------------- |
| |0 |PX COORDINATOR | |256 |4932 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10001|256 |3199 | |
| |2 | └─SELECT INTO | |256 |1898 | |
| |3 | └─EXCHANGE IN DISTR | |256 |1898 | |
| |4 | └─EXCHANGE OUT DISTR (RANDOM)|:EX10000|256 |1320 | |
| |5 | └─PX BLOCK ITERATOR | |256 |19 | |
| |6 | └─TABLE FULL SCAN |t1 |256 |19 | |
| ======================================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output(nil), filter(nil), rowset=256 |
| 1 - output(nil), filter(nil), rowset=256 |
| dop=3 |
| 2 - output(nil), filter(nil), rowset=256 |
| 3 - output([t1.pk1(0x7f60a2a1f2d0)], [t1.id(0x7f60a2a1f710)], [t1.f1(0x7f60a2a1fb50)], [t1.f2(0x7f60a2a1ff90)], [t1.f3(0x7f60a2a203d0)], [t1.f4(0x7f60a2a20810)], |
| [t1.f5(0x7f60a2a20c50)], [t1.f6(0x7f60a2a21090)], [t1.f7(0x7f60a2a214d0)], [t1.f8(0x7f60a2a21910)], [t1.f9(0x7f60a2a21d50)]), filter(nil), rowset=256 |
| 4 - output([t1.pk1(0x7f60a2a1f2d0)], [t1.id(0x7f60a2a1f710)], [t1.f1(0x7f60a2a1fb50)], [t1.f2(0x7f60a2a1ff90)], [t1.f3(0x7f60a2a203d0)], [t1.f4(0x7f60a2a20810)], |
| [t1.f5(0x7f60a2a20c50)], [t1.f6(0x7f60a2a21090)], [t1.f7(0x7f60a2a214d0)], [t1.f8(0x7f60a2a21910)], [t1.f9(0x7f60a2a21d50)]), filter(nil), rowset=256 |
| dop=3 |
| 5 - output([t1.pk1(0x7f60a2a1f2d0)], [t1.id(0x7f60a2a1f710)], [t1.f1(0x7f60a2a1fb50)], [t1.f2(0x7f60a2a1ff90)], [t1.f3(0x7f60a2a203d0)], [t1.f4(0x7f60a2a20810)], |
| [t1.f5(0x7f60a2a20c50)], [t1.f6(0x7f60a2a21090)], [t1.f7(0x7f60a2a214d0)], [t1.f8(0x7f60a2a21910)], [t1.f9(0x7f60a2a21d50)]), filter(nil), rowset=256 |
| 6 - output([t1.pk1(0x7f60a2a1f2d0)], [t1.id(0x7f60a2a1f710)], [t1.f1(0x7f60a2a1fb50)], [t1.f2(0x7f60a2a1ff90)], [t1.f3(0x7f60a2a203d0)], [t1.f4(0x7f60a2a20810)], |
| [t1.f5(0x7f60a2a20c50)], [t1.f6(0x7f60a2a21090)], [t1.f7(0x7f60a2a214d0)], [t1.f8(0x7f60a2a21910)], [t1.f9(0x7f60a2a21d50)]), filter(nil), rowset=256 |
| access([t1.pk1(0x7f60a2a1f2d0)], [t1.id(0x7f60a2a1f710)], [t1.f1(0x7f60a2a1fb50)], [t1.f2(0x7f60a2a1ff90)], [t1.f3(0x7f60a2a203d0)], [t1.f4(0x7f60a2a20810)], |
| [t1.f5(0x7f60a2a20c50)], [t1.f6(0x7f60a2a21090)], [t1.f7(0x7f60a2a214d0)], [t1.f8(0x7f60a2a21910)], [t1.f9(0x7f60a2a21d50)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.pk1(0x7f60a2a1f2d0)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| PARALLEL(3) |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| PARALLEL(@"SEL$1" "test"."t1"@"SEL$1" 3) |
| FULL(@"SEL$1" "test"."t1"@"SEL$1") |
| PARALLEL(3) |
| OPTIMIZER_FEATURES_ENABLE('4.3.3.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| t1: |
| table_rows:256 |
| physical_range_rows:256 |
| logical_range_rows:256 |
| index_back_rows:0 |
| output_rows:256 |
| table_dop:3 |
| dop_method:Global DOP |
| avaiable_index_name:[t1] |
| stats info:[version=0, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[DEFAULT, STORAGE] |
| Plan Type: |
| DISTRIBUTED |
| Note: |
| Degree of Parallelism is 3 because of hint |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
68 rows in set (0.005 sec)
-- 导出非分区表t1
MySQL [test]> select /*+parallel(3)*/ * from t1 into outfile 'select_into/data' single=false;
ERROR 1017 (HY000): File not exist
MySQL [test]>
-- 在t1表的leader节点（非接收到该SQL的observer机器上）创建需要的目录：
[admin@n154 oceanbase]$ hostname -i
xxx.xxx.15.4
[admin@n154 oceanbase]$ mkdir /home/admin/oceanbase/select_into
MySQL [test]> select /*+parallel(3)*/ * from t1 into outfile 'select_into/data' single=false;
Query OK, 256 rows affected (0.123 sec)
[admin@n154 oceanbase]$ hostname -i
xxx.xxx.15.4
[admin@n154 oceanbase]$ ls -tlr /home/admin/oceanbase/select_into/
total 8204
-rw-r--r-- 1 admin admin 2786908 Nov 6 11:50 data_0_2_0
-rw-r--r-- 1 admin admin 2786908 Nov 6 11:50 data_0_1_0
-rw-r--r-- 1 admin admin 2819696 Nov 6 11:50 data_0_0_0
对于分区表，通过 OBProxy 连接进来的和通过OBServer直连进来的结果一样，导出的所有文件会分布在各个分区的 Leader 节点上：
[root@n153 ~]# mysql -hxxx.xxx.15.8 -P2883 -uroot@mysql#obcluster -pxxx -A -c test
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2099817
Server version: 5.6.25 OceanBase 4.3.4.0 (r100000132024103017-57f7e034b952f62c81fa52782fd21a8856ff95da) (Built Oct 30 2024 18:44:26)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [test]> explain extended select /*+parallel(4)*/ * from t2 into outfile 'select_into2/data' single=false;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ======================================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------------------------------- |
| |0 |PX COORDINATOR | |256 |189299 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10001|256 |109627 | |
| |2 | └─SELECT INTO | |256 |64780 | |
| |3 | └─EXCHANGE IN DISTR | |256 |64780 | |
| |4 | └─EXCHANGE OUT DISTR (RANDOM)|:EX10000|256 |44862 | |
| |5 | └─PX BLOCK ITERATOR | |256 |16 | |
| |6 | └─TABLE FULL SCAN |t2 |256 |16 | |
| ======================================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output(nil), filter(nil), rowset=256 |
| 1 - output(nil), filter(nil), rowset=256 |
| dop=4 |
| 2 - output(nil), filter(nil), rowset=256 |
| 3 - output([t2.pk1(0x7f604201fe40)], [t2.id(0x7f6042020280)], [t2.f1(0x7f60420206c0)], [t2.f2(0x7f6042020b00)], [t2.f3(0x7f6042020f40)], [t2.f4(0x7f6042021380)], |
| [t2.f5(0x7f60420217c0)], [t2.f6(0x7f6042021c00)], [t2.f7(0x7f6042022040)], [t2.f8(0x7f6042022480)], [t2.f9(0x7f60420228c0)]), filter(nil), rowset=256 |
| 4 - output([t2.pk1(0x7f604201fe40)], [t2.id(0x7f6042020280)], [t2.f1(0x7f60420206c0)], [t2.f2(0x7f6042020b00)], [t2.f3(0x7f6042020f40)], [t2.f4(0x7f6042021380)], |
| [t2.f5(0x7f60420217c0)], [t2.f6(0x7f6042021c00)], [t2.f7(0x7f6042022040)], [t2.f8(0x7f6042022480)], [t2.f9(0x7f60420228c0)]), filter(nil), rowset=256 |
| dop=4 |
| 5 - output([t2.pk1(0x7f604201fe40)], [t2.id(0x7f6042020280)], [t2.f1(0x7f60420206c0)], [t2.f2(0x7f6042020b00)], [t2.f3(0x7f6042020f40)], [t2.f4(0x7f6042021380)], |
| [t2.f5(0x7f60420217c0)], [t2.f6(0x7f6042021c00)], [t2.f7(0x7f6042022040)], [t2.f8(0x7f6042022480)], [t2.f9(0x7f60420228c0)]), filter(nil), rowset=256 |
| 6 - output([t2.pk1(0x7f604201fe40)], [t2.id(0x7f6042020280)], [t2.f1(0x7f60420206c0)], [t2.f2(0x7f6042020b00)], [t2.f3(0x7f6042020f40)], [t2.f4(0x7f6042021380)], |
| [t2.f5(0x7f60420217c0)], [t2.f6(0x7f6042021c00)], [t2.f7(0x7f6042022040)], [t2.f8(0x7f6042022480)], [t2.f9(0x7f60420228c0)]), filter(nil), rowset=256 |
| access([t2.pk1(0x7f604201fe40)], [t2.id(0x7f6042020280)], [t2.f1(0x7f60420206c0)], [t2.f2(0x7f6042020b00)], [t2.f3(0x7f6042020f40)], [t2.f4(0x7f6042021380)], |
| [t2.f5(0x7f60420217c0)], [t2.f6(0x7f6042021c00)], [t2.f7(0x7f6042022040)], [t2.f8(0x7f6042022480)], [t2.f9(0x7f60420228c0)]), partitions(p[0-2]) |
| is_index_back=false, is_global_index=false, |
| range_key([t2.pk1(0x7f604201fe40)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| PARALLEL(4) |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| PARALLEL(@"SEL$1" "test"."t2"@"SEL$1" 4) |
| FULL(@"SEL$1" "test"."t2"@"SEL$1") |
| PARALLEL(4) |
| OPTIMIZER_FEATURES_ENABLE('4.3.3.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| t2: |
| table_rows:256 |
| physical_range_rows:256 |
| logical_range_rows:256 |
| index_back_rows:0 |
| output_rows:256 |
| table_dop:4 |
| dop_method:Global DOP |
| avaiable_index_name:[t2] |
| stats info:[version=1730988007538012, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[OPTIMIZER STATISTICS, STORAGE] |
| Plan Type: |
| DISTRIBUTED |
| Note: |
| Degree of Parallelism is 4 because of hint |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
68 rows in set (0.004 sec)
MySQL [test]> select /*+parallel(4)*/ * from t2 into outfile 'select_into2/data' single=false;
Query OK, 256 rows affected (5.901 sec)
MySQL [test]>
-- 导出的四个文件分散在多个分区leader节点上
[admin@n153 oceanbase]$ hostname -i
xxx.xxx.15.3
[admin@n153 oceanbase]$ ls -ltr select_into2/
total 4168
-rw-r--r-- 1 admin admin 2098378 Nov 8 16:18 data_0_1_0
-rw-r--r-- 1 admin admin 2163948 Nov 8 16:18 data_0_0_0
[admin@n153 oceanbase]$
[admin@n154 oceanbase]$ hostname -i
xxx.xxx.15.4
[admin@n154 oceanbase]$ ls -ltr select_into2/
total 2020
-rw-r--r-- 1 admin admin 2065590 Nov 8 16:18 data_1_0_0
[admin@n154 oceanbase]$
[admin@n155 ~/oceanbase]$ hostname -i
xxx.xxx.15.5
[admin@n155 ~/oceanbase]$ ls -ltr select_into2/
total 2020
-rw-r--r-- 1 admin admin 2065596 Nov 8 16:18 data_2_0_0
备注
single、max_file_size选项从 OceanBase 数据库 V4.3.1 版本开始支持，之前的低版本无此选项。- 因为导出时表文件的位置依赖于导出 SQL 的选项及表/分区 Leader 的分布情况及 OBProxy 的实际路由，因此推荐使用共享存储介质（如 NFS、OSS）路径来作为导出文件的位置。
影响租户
影响 OceanBase 数据库中的 Oracle 租户和 MySQL 租户，对于 SYS 租户无影响。
适用版本
所有兼容 select into outfile 语法的 OceanBase 数据库版本（V2.2.40 及之后版本）。
