PolarDB可以通过OSS外表直接查询存储在OSS上的CSV格式数据,有效地降低存储的成本。本文档主要介绍了通过OSS外表访问OSS数据的操作步骤。

前提条件

PolarDB集群版本需满足如下条件之一:
  • PolarDB MySQL引擎为8.0.1版本且修订版本为8.0.1.1.25.4及以上。
  • PolarDB MySQL引擎为8.0.2版本且修订版本为8.0.2.2.1及以上。

如何确认集群版本,详情请参见查询版本号

技术原理

通过OSS外表,您可以将CSV格式的查询频度低的数据(称为冷数据)存储到OSS引擎上,并对冷数据进行查询和分析。具体原理如下: OSS外表
CSV格式的数据支持的数据类型包括数值类型、日期和时间类型、字符串类型以及NULL值。具体如下:
说明
  • 目前不支持地理空间数据类型。
  • 目前不支持查询CSV格式的压缩文件。
  • PolarDB MySQL引擎为8.0.1版本且修订版本为8.0.1.1.28及以上,或PolarDB MySQL引擎为8.0.2版本且修订版本为8.0.2.2.5及以上时,支持使用NULL值。
  • 数值类型
    类型 大小 数据范围(有符号) 数据范围(无符号) 说明
    TINYINT 1 Byte -128~127 0~255 小整数值
    SMALLINT 2 Bytes -32768~32767 0~65535 大整数值
    MEDIUMINT 3 Bytes -8388608~8388 607 0~16777215 大整数值
    INT或INTEGER 4 Bytes -2147483648~2147483647 0~4294967295 大整数值
    BIGINT 8 Bytes -9,223,372,036,854,775,808~9223372036854775807 0~18446744073709551615 极大整数值
    FLOAT 4 Bytes -3.402823466 E+38~-1.175494351E-38;0;1.175494351E-38~3.402823466351E+38 0;1.175494351E-38~3.402823466E+38 单精度浮点数值
    DOUBLE 8 Bytes -2.2250738585072014E-308~-1.7976931348623157E+308;0;1.7976931348623157E+308~2.2250738585072014E-308 0;1.7976931348623157E+308~2.2250738585072014E-308 双精度浮点数值
    DECIMAL 对于DECIMAL(M,D) ,如果M>D,为M+2;否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值
  • 日期和时间类型
    类型 大小 数据范围 数据格式 说明
    DATE 3 Bytes 1000-01-01~9999-12-31 YYYY-MM-DD 日期值
    TIME 3 Bytes -838:59:59~838:59:59 HH:MM:SS 时间值或持续时间
    YEAR 1 Byte 1901~2155 YYYY 年份值
    DATETIME 8 Bytes 1000-01-01 00:00:00~9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
    说明 该类型中的月份和日期必须是两位数。例如,2020年1月1日要写成2020-01-01 ,而不能写成2020-1-1,否则该查询下推到OSS后无法被正确执行。
    TIMESTAMP 4 Bytes 1970-01-01 00:00:00~2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS 时间戳(混合日期和时间值)
    说明 该类型中的月份和日期必须是两位数。例如,2020年1月1日要写成2020-01-01 ,而不能写成2020-1-1,否则该查询下推到OSS后无法被正确执行。
  • 字符串类型
    类型 大小 说明
    CHAR 0~255 Bytes 定长字符串
    VARCHAR 0~65535 Bytes 变长字符串
    TINYBLOB 0~255 Bytes 不超过255个字符的二进制字符串
    TINYTEXT 0~255 Bytes 短文本字符串
    BLOB 0~65535 Bytes 二进制形式的长文本数据
    TEXT 0~65535 Bytes 长文本数据
    MEDIUMBLOB 0~16777215 Bytes 二进制形式的中等长度文本数据
    MEDIUMTEXT 0~16777215 Bytes 中等长度文本数据
    LONGBLOB 0~4294967295 Bytes 二进制形式的极大文本数据
    LONGTEXT 0~4294967295 Bytes 极大文本数据
  • NULL值
    • 插入NULL值。
      • 在OSS外表中插入NULL值。
        如果在OSS外表中插入NULL值,则需要在建表时指明对应的NULL值标记,即NULL_MARKER。OSS外表的NULL_MARKER值默认为NULL,您可以通过show create table语句来查看NULL值标记:
        show create table t1;
        查询结果如下:
        show create table t1;
        +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
        | Table | Create Table                                                                                                                                                                      |
        +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
        | t1    | CREATE TABLE `t1` (
          `id` int(11) DEFAULT NULL
        ) ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ CONNECTION='server_name' |
        +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
        1 row in set (0.00 sec)
      • 在CSV格式的文件中插入NULL值。
        如果在CSV格式的文件中需要在某一字段对应的位置插入NULL_MARKER,且NULL_MARKER两端不添加双引号,则PolarDB会把该值识别为NULL。
        说明
        • 当您在NULL_MARKER两端添加双引号,则PolarDB会识别为字符串,通过is_null语句无法查出NULL值,且如果CSV文件中被赋予NULL值的参数与OSS外表中对应的参数类型不匹配,则会报错。
        • NULL_MARKER不能设置为纯数字,也不能设置为空,且不能含有以下四种字符:

          "\n\r,

        示例:创建OSS外表的建表语句如下:
        CREATE TABLE `t1` (
          `id` int(11) DEFAULT NULL,
          `name` varchar(20) DEFAULT NULL,
          `time` timestamp NULL DEFAULT NULL
        ) ENGINE=CSV NULL_MARKER='NULL' CONNECTION='server_name';
        假设对应的数据文件内容如下:
        1,"xiaohong","2022-01-01 00:00:00"
        NULL,"xiaoming","2022-02-01 00:00:00"
        3,NULL,"2022-03-01 00:00:00"
        4,"xiaowang",NULL
        则通过OSS外表查询的OSS数据如下:
        select * from t1;
        +------+----------+---------------------+
        | id   | name     | time                |
        +------+----------+---------------------+
        |    1 | xiaohong | 2022-01-01 00:00:00 |
        | NULL | xiaoming | 2022-02-01 00:00:00 |
        |    3 | NULL     | 2022-03-01 00:00:00 |
        |    4 | xiaowang | NULL                |
        +------+----------+---------------------+
        4 rows in set (0.00 sec)
    • 读取NULL值。
      • 从CSV格式的数据文件中读取数据时,如果CSV中的值为NULL,且OSS外表中对应的值可以为NULL时,则当前字段直接设置为NULL。
      • 从CSV格式的数据文件中读取数据时,如果CSV中的值为NULL,但OSS外表中对应的值设置为NOT NULL时,即CSV中的数据内容与OSS外表中定义的内容冲突。则会根据您设置的语法校验规则返回不同的结果。
        • 当您将语法校验规则sql_mode设置为STRICT_TRANS_TABLES 时,则会报错。
        • 当您将语法校验规则sql_mode设置为除STRICT_TRANS_TABLES 之外的其他模式时,如果当前字段有默认值,则当前字段的值会设置为默认值。如果没有默认值,则当前字段会根据字段类型被赋予MySQL的默认值,详情请参见数据类型默认值。且会有warning提示,您可以通过show warnings; 命令查看warning提示详细信息。
        说明 您可以通过show variables like "sql_mode"; 命令查看当前的语法校验规则。且可以在控制台的参数配置中通过修改sql_mode参数的值来修改当前的语法校验规则,具体请参见修改参数值
        示例:创建一张OSS外表t,将id字段设置为NOT NULL,并且没有默认值。
        CREATE TABLE `t` (
          `id` int(11) NOT NULL
        ) ENGINE=CSV 
        CONNECTION="server_name";
        假设CSV格式的数据文件t.CSV中的内容为:
        NULL
        2
        通过OSS外表读取CSV格式文件中的数据会有以下两种情况:
        • sql_mode设置为STRICT_TRANS_TABLES 时,执行如下命令,查询CSV格式文件中的数据:
          select * from t;
          报错信息如下:
          ERROR 1364 (HY000): Field 'id' doesn't have a default value
        • sql_mode设置为除STRICT_TRANS_TABLES 之外的模式时,执行如下命令,查询CSV格式文件中的数据:
          select * from t;
          查询结果如下:
          +----+
          | id |
          +----+
          |  0 |
          |  2 |
          +----+
          2 rows in set, 1 warning (0.00 sec)
          其中,0为MySQL默认值。
          执行以下命令,查看warning提示信息:
          show warnings;
          查询结果如下:
          +---------+------+-----------------------------------------+
          | Level   | Code | Message                                 |
          +---------+------+-----------------------------------------+
          | Warning | 1364 | Field 'id' doesn't have a default value |
          +---------+------+-----------------------------------------+
          1 row in set (0.00 sec)

使用限制

  • 目前通过OSS外表仅支持查询CSV格式的数据。
  • 目前针对OSS外表的语句只支持CREATE、SELECT、DROP三种。
    说明 DROP操作不会删除OSS上的数据文件,仅删除PolarDB上的表信息。
  • OSS外表目前不支持索引、分区和事务。

参数说明

您可以在控制台的参数配置页面查看或修改以下参数:
参数名称 级别 参数说明
loose_csv_oss_buff_size 全局参数 当前一个OSS线程所占用的内存大小。默认值为134217728 KB。单位:KB。

取值范围:4096~10737418240

loose_csv_max_oss_threads 全局参数 当前允许运行的OSS线程数量。默认值为1。

取值范围:1~100

根据以上两个参数可以计算出OSS功能占用的总内存最大为:loose_csv_max_oss_threads * loose_csv_oss_buff_size
说明 使用OSS功能时,OSS占用的总内存尽量不要超过当前节点内存的5%,否则可能会出现内存溢出问题。

操作步骤

  1. 上传CSV格式的数据至OSS。
    您可以通过命令行工具ossutil将本地CSV格式的数据上传到远程OSS引擎上。
    说明
    • 上传CSV文件的OSS目录需要与OSS server中DATABASEoss_prefix的目录保持一致。
    • 上传的CSV文件名需要设置为外表名+.CSV。例如,创建的OSS外表为t1,则上传的CSV文件名需要设置为t1.CSV
    • CSV文件中的数据字段与OSS外表字段需要匹配。例如:创建的OSS外表 t1表中只有一个字段id,类型为INT。则上传的CSV文件中也只能有一个INT类型的字段。
    • 建议您直接上传本地MySQL的数据文件,并依据表定义创建对应的OSS外表。
  2. 添加OSS连接信息。
    您可以通过创建OSS server来添加OSS连接信息。
    说明 通过其他方式连接OSS的功能由于存在安全风险已经被禁用。目前仅支持通过创建OSS server的方式来添加OSS连接信息,并与OSS建立连接。
    • PolarDB MySQL引擎为8.0.1版本且修订版本为8.0.1.1.28及以上。或PolarDB MySQL引擎为8.0.2版本且修订版本为8.0.2.2.5及以上时,语法如下:
      CREATE SERVER <server_name> 
      FOREIGN DATA WRAPPER oss OPTIONS 
      (   
      EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>","oss_bucket": "<my_oss_bucket>","oss_access_key_id": "<my_oss_acess_key_id>","oss_access_key_secret": "<my_oss_acess_key_secret>","oss_prefix":"<my_oss_prefix>","oss_sts_token":"<my_oss_sts_token>"}'
      );
      说明
      • PolarDB MySQL引擎为8.0.1版本且修订版本为8.0.1.1.29及以上,或PolarDB MySQL引擎为8.0.2版本且修订版本为8.0.2.2.6及以上时,支持使用my_oss_sts_token参数。
      • 该语法支持DATABASE参数,若您创建的OSS server中既存在DATABASE参数,又存在my_oss_prefix参数,则最终查找文件的路径为my_oss_prefix/DATABASE。添加DATABASE参数的方法请参见下文中的内容。
      参数说明如下表所示:
      参数名称 参数类型 参数说明
      server_name 字符串 OSS server名称。
      说明 该参数为全局参数,且全局唯一。该参数不区分大小写,最大长度不超过64个字符,超过64个字符的名称会被自动截断。您可以将OSS server名称指定为带引号的字符串。
      my_oss_endpoint 字符串 OSS对应区域的域名。
      说明 如果是从阿里云的主机访问数据库,应该使用内网域名(即带有“internal”的域名),避免产生公网流量。

      例如:华东1(杭州)OSS节点的内网域名:oss-cn-xxx-internal.aliyuncs.com

      my_oss_bucket 字符串 数据文件所在OSS的bucket,需要通过OSS预先创建。
      说明 OSS的bucket和PolarDB最好在同一个可用区内,以减少两者之间的网络延迟。
      my_oss_acess_key_id 字符串 OSS账号ID。
      my_oss_acess_key_secret 字符串 OSS账号KEY。
      my_oss_prefix 字符串 当前CSV数据文件在OSS中的目录。
      my_oss_sts_token 字符串 OSS临时访问凭证。获取OSS临时访问凭证详情请参见获取临时访问凭证
      说明 my_oss_sts_token参数值有默认的过期时间。如果my_oss_sts_token已过期,您需要通过以下命令重置EXTRA_SERVER_INFO中的全部参数值。
      ALTER SERVER server_name OPTION(EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>",
      "oss_bucket": "<my_oss_bucket>", "oss_access_key_id": "<my_oss_acess_key_id>",
      "oss_access_key_secret": "<my_oss_acess_key_secret>", "oss_prefix":"<my_oss_prefix>", "oss_sts_token": "<my_oss_sts_token>"}');
      说明
      • 创建OSS server时需要SERVERS_ADMIN权限,您可以通过show grants for 当前用户命令查看当前用户是否具有SERVERS_ADMIN权限。目前,高权限账户默认具有该权限,并且高权限账户可以给低权限账户赋予该权限。
      • 如果您是高权限用户,可以通过SELECT Server_name, Extra_server_info FROM mysql.servers;命令查看您创建的OSS Server信息,且oss_access_key_idoss_access_key_secret参数信息因为涉及安全信息会被加密处理,无法查看其详细信息。
    • PolarDB MySQL引擎为8.0.1版本且修订版本在8.0.1.1.25.4至8.0.1.1.28之间,或PolarDB MySQL引擎为8.0.2版本且修订版本在8.0.2.2.1至8.0.2.2.5之间时,语法如下:
      CREATE SERVER <server_name>
      FOREIGN DATA WRAPPER oss OPTIONS
      (DATABASE '<my_database_name>',
        EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>","oss_bucket": "<my_oss_bucket>","oss_access_key_id":"<my_oss_acess_key_id>","oss_access_key_secret":"<my_oss_acess_key_secret>"}'
      );                  
      说明 该版本的语法不支持oss_prefix参数。
      参数说明如下表所示:
      参数名称 参数类型 参数说明
      server_name 字符串 OSS server名称。
      说明 该参数为全局参数,且全局唯一。该参数不区分大小写,最大长度不超过64个字符,超过64个字符的名称会被自动截断。您可以将OSS server名称指定为带引号的字符串。
      my_database_name 字符串 当前CSV数据文件在OSS中的目录名称。
      my_oss_endpoint 字符串 OSS对应区域的域名。
      说明 如果是从阿里云的主机访问数据库,应该使用内网域名(即带有“internal”的域名),避免产生公网流量。

      例如:oss-cn-xxx-internal.aliyuncs.com

      my_oss_bucket 字符串 数据文件所在OSS的bucket,需要通过OSS预先创建。
      my_oss_acess_key_id 字符串 OSS账号ID。
      my_oss_acess_key_secret 字符串 OSS账号KEY。
  3. 创建OSS外表。
    定义了OSS Server之后,您需要在PolarDB上创建OSS外表,与OSS建立连接。示例如下:
    create table t1 (id int) engine=csv connection="connection_string";
    其中,connection_string由以下内容组成,且使用“/”来进行连接:
    • OSS Server名称。
    • (可选)OSS上的数据文件路径。
      说明PolarDB MySQL引擎为8.0.1版本且修订版本为8.0.1.1.28及以上。或PolarDB MySQL引擎为8.0.2版本且修订版本为8.0.2.2.5及以上时,支持配置OSS上的数据文件路径。
    • (可选)数据文件名称。
      说明 数据文件名称后面不能有 .CSV后缀。
    示例:
    create table t1 (id int) engine=csv connection="server_name/a/b/c/d/t1";
    通过示例可以看出:OSS上的数据文件路径为oss_prefix/a/b/c/d/,数据文件为t1.CSV
    说明
    • 您可以只使用数据文件名称来指定OSS外表对应的数据文件。例如:create table t1 (id int) engine=csv connection="server_name/t2",则PolarDB会在OSS上的oss_prefix路径下查找t2.CSV的文件。
    • 如果您在connection_string中添加了OSS上的数据文件路径,则您必须添加对应的数据文件名称。否则,查找对应的文件时会将路径的最后一段识别为文件名称。
    • 如果不指定数据文件名称,则当前表对应的OSS文件为当前表名.CSV;如果指定数据文件名称,则当前表对应的OSS文件为指定的数据文件名称.CSV
    OSS外表创建完成后,您可以通过show create table命令查看已创建的表。请检查已创建的表的引擎是否为CSV,如果不是,可能是您当前的PolarDB版本过低,不支持OSS引擎。
  4. 数据查询。
    以上述步骤示例中的t1表为例进行说明。
    #查询t1表内的数据数量
    SELECT count(*) FROM t1;
    
    #范围查询
    SELECT id FROM t1 WHERE id < 10 AND id > 1;
    
    #点查
    SELECT id FROM t1 where id = 3;
    
    #多表join
    SELECT id FROM t1 left join t2 on t1.id = t2.id WHERE t2.name like "%er%";
    查询数据的过程中,常见的报错信息及报错原因请参见下表:
    报错信息 报错原因 解决方案
    OSS error: No corresponding data file on the OSS engine. OSS上没有找到对应的数据文件。 您需要根据上述规则检查OSS上对应的路径下是否存在数据文件。若不存在,则需要将数据文件上传至目标路径。
    There is not enough memory space for OSS transmission. Currently requested memory %d. 没有足够的空间进行OSS查询。 您可以通过以下两种方式中的任意一种来修复该错误:
    • 在控制台的参数配置中通过修改loose_csv_max_oss_threads参数值来运行更多的OSS线程。
    • 通过flush table关闭某些OSS表的线程。
    ERROR 8054 (HY000): OSS error: error message : Couldn't connect to server.Failed connect to aliyun-mysql-oss.oss-cn-hangzhou-internal.aliyuncs.com:80; 当前的数据库实例无法连接OSS服务器。 检查当前的数据库实例与OSS bucket是否在同一个可用区。
    • 如果不在同一个可用区,则需要将当前的数据库实例与OSS bucket放在同一个可用区。
    • 如果在同一个可用区,您可以将endpoint修改为公网的endpoint。如果endpoint修改后仍然报错,请联系阿里云技术支持解决。

查询优化

OSS引擎在查询过程中,可以将部分的查询条件下推到远程引擎OSS上执行,以获得更好的查询效率,这个优化被称之为engine condition pushdown。可以下推的限制条件如下:
  • 目前仅支持UTF-8编码格式的CSV文本文件。
  • SQL语句中仅支持以下几种类型的算子和算数表达式:
    • 比较算子:><>=<===
    • 逻辑算子:LIKEINANDOR
    • 算数表达式:+ - * /
  • 仅支持单文件查询,不支持join、order by、group by、having子查询。
  • WHERE语句里不能包含聚合条件,例如where max(age) > 100是不允许的。
  • 支持的最大列数是1000,SQL中最大列名长度不能超过1024个字节。
  • 在LIKE语句中,支持最多5个%通配符。
  • 在IN语句中,最多支持1024个常量项。
  • CSV文件支持单行及单列的最大字符数均为256 KB。
  • SQL最大长度为16 KB,WHERE语句后面的表达式个数最多20个,聚合操作最多100个。
说明 该功能默认关闭,如需使用您可以通过执行SET SESSION optimizer_switch='engine_condition_pushdown=on'; 命令开启该功能。
符合以上条件的查询会被下推到OSS引擎去执行。您可以通过OSS外表的执行计划来查看哪些查询条件被下推到OSS引擎上执行。
  • 通过explain查看OSS外表的执行计划。示例如下:
    EXPLAIN SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" GROUP BY `id` ORDER BY `id` DESC;
    +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                                                                                                            |
    +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------+
    |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 15000 |     1.23 | Using where; With pushed engine condition ((`test`.`t1`.`id` > 5) and (`test`.`t1`.`id` < 100)); Using temporary; Using filesort |
    +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    其中,With pushed engine condition后面的条件可以被下推到远程OSS引擎上执行,其余的条件`name` LIKE "%1%%%%%"GROUP BY `id` ORDER BY `id` DESC不能被下推到OSS引擎上执行,只会在本地OSS server上执行。
  • 通过tree格式查看OSS外表的执行计划。示例如下:
    EXPLAIN FORMAT=tree  SELECT SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" Y `id` ORDER BY `id` DESC;
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | EXPLAIN                                                                                                                                                                                                                                                                                                           |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | -> Sort: <temporary>.id DESC
        -> Table scan on <temporary>
            -> Aggregate using temporary table
                -> Filter: (t1.`name` like '%1%%%%%')  (cost=1690.00 rows=185)
                    -> Table scan on t1, extra ( engine conditions: ((t1.id > 5) and (t1.id < 100)) )  (cost=1690.00 rows=15000)
     |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    其中,engine conditions:后面的条件可以被下推到远程OSS引擎上执行,其余的条件`name` LIKE "%1%%%%%"GROUP BY `id` ORDER BY `id` DESC不能被下推到OSS引擎上执行,只会在本地OSS server上执行。
    说明 集群版本需为PolarDB MySQL引擎8.0.2版本,您可以通过查询版本号确认集群版本。
  • 通过Json格式查看OSS外表的执行计划。示例如下:
    EXPLAIN FORMAT=json  SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" GROUP BY `id` ORDER BY `id` DESC;
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | {
      "query_block": {
        "select_id": 1,
        "cost_info": {
          "query_cost": "1875.13"
        },
        "ordering_operation": {
          "using_filesort": false,
          "grouping_operation": {
            "using_temporary_table": true,
            "using_filesort": true,
            "cost_info": {
              "sort_cost": "185.13"
            },
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows_examined_per_scan": 15000,
              "rows_produced_per_join": 185,
              "filtered": "1.23",
              "engine_condition": "((`test`.`t1`.`id` > 5) and (`test`.`t1`.`id` < 100))",
              "cost_info": {
                "read_cost": "1671.49",
                "eval_cost": "18.51",
                "prefix_cost": "1690.00",
                "data_read_per_join": "146K"
              },
              "used_columns": [
                "id",
                "name"
              ],
              "attached_condition": "(`test`.`t1`.`name` like '%1%%%%%')"
            }
          }
        }
      }
    } |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    同上,engine conditions:后面的条件可以被下推到远程OSS引擎上执行,其余的条件`name` LIKE "%1%%%%%"GROUP BY `id` ORDER BY `id` DESC不能被下推到OSS引擎上执行,只会在本地OSS server上执行。
如果出现以下错误,则表示当前OSS数据文件中的某些字符不符合OSS条件下推的要求。
OSS error: The current query does not support engine condition pushdown. You need to use NO_ECP() hint or set optimizer_switch = 'engine_condition_pushdown=OFF' to turn off the condition push down function.
您可以通过hints或者optimizer_switch手动关闭条件下推功能。
  • hints
    通过hints可以针对某个查询关闭条件下推功能。例如:关闭t1表的查询下推功能:
    SELECT /*+ NO_ECP(t1) */ `j` FROM `t1` WHERE `j` LIKE "%c%" LIMIT 10;
  • optimizer_switch
    通过optimizer_switch可以针对当前session,关闭所有查询的条件下推功能。
    SET SESSION optimizer_switch='engine_condition_pushdown=off'; #将engine_condition_pushdown设置为off,表示关闭当前session下所有查询的条件下推功能。
    您可以通过以下命令查看当前系统的optimizer_switch状态,以此来判断当前session下所有查询的条件下推功能状态:
    select @@optimizer_switch;

多节点之间同步OSS server信息

目前,PolarDB集群的主节点和只读节点共用一个OSS server,以保证在两个节点上都可以访问OSS上的数据。且两个节点间OSS Server信息同步是无锁的,以保证在两个节点上的操作不会互相影响。

当您修改OSS Server信息后,修改内容会无锁地同步到只读节点,如果只读节点上有线程持有OSS Server的锁,则可能会导致OSS server信息同步时间延迟。此时,您可以通过执行/*force_node='pi-bpxxxxxxxx'*/ flush privileges; /*force_node='pi-bpxxxxxxxx'*/flush table oss_foreign_table;命令来手动更新只读节点的OSS server信息。