--- title: 通过 select into outfile 导出的表文件存放位置说明-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于通过 select into outfile 导出的表文件存放位置说明相关的常见问题和使用技巧，帮助您快速解决通过 select into outfile 导出的表文件存放位置说明的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # 通过 select into outfile 导出的表文件存放位置说明 更新时间：2026-05-28 02:06 适用版本： V2.2.x、V3.1.x、V3.2.x、V4.0.x、V4.1.x、V4.2.x、V4.3.x 内容类型：TechNote `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 三个节点。 ```shell 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`，导出方式为串行写单个文件。 ```shell [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 的设置，优先级依次从高到低。 ```shell [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) -- 导出非分区表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 节点上：** ```shell 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 节点上：** ```shell [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) 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 ``` #### 备注 1. `single`、`max_file_size` 选项从 OceanBase 数据库 V4.3.1 版本开始支持，之前的低版本无此选项。 2. 因为导出时表文件的位置依赖于导出 SQL 的选项及表/分区 Leader 的分布情况及 OBProxy 的实际路由，因此推荐使用共享存储介质（如 NFS、OSS）路径来作为导出文件的位置。 ## 影响租户 影响 OceanBase 数据库中的 Oracle 租户和 MySQL 租户，对于 SYS 租户无影响。 ## 适用版本 所有兼容 `select into outfile` 语法的 OceanBase 数据库版本（V2.2.40 及之后版本）。 Previous [跨节点访问数据错误，错误代码 1235](https://www.oceanbase.com/knowledge-base/oceanbase-database-20000001060) Next [OceanBase Oracle SELECT 中 TYPE 类型兼容问题](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002942617) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线