文档

通过OSS外表访问OSS数据

更新时间:

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~8388607

    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。单位:Byte。

取值范围:4096~134217728

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,且文件名后缀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_access_key_id>","oss_access_key_secret": "<my_oss_access_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_access_key_id

      字符串

      RAM用户或阿里云账号的AccessKey ID。

      如何创建AccessKey请参见创建AccessKey

      my_oss_access_key_secret

      字符串

      RAM用户或阿里云账号的AccessKey Secret。

      如何创建AccessKey请参见创建AccessKey

      my_oss_prefix

      字符串

      当前CSV数据文件在OSS中的目录。

      my_oss_sts_token

      字符串

      STS临时访问凭证。

      说明
      • 使用STS临时访问凭证访问OSS时,该参数必填。

      • my_oss_sts_token参数值有默认的过期时间。如果my_oss_sts_token已过期,您需要通过以下命令重置EXTRA_SERVER_INFO中的全部参数值。

        ALTER SERVER server_name OPTIONS(EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>",
        "oss_bucket": "<my_oss_bucket>", "oss_access_key_id": "<my_oss_access_key_id>",
        "oss_access_key_secret": "<my_oss_access_key_secret>", "oss_prefix":"<my_oss_prefix>", "oss_sts_token": "<my_oss_sts_token>"}');
      说明
      • 创建OSS server时需要SERVERS_ADMIN权限,您可以通过show grants for 当前用户命令查看当前用户是否具有SERVERS_ADMIN权限。目前,高权限账户默认具有该权限,并且高权限账户可以给低权限账户赋予该权限。

      • 如果您当前没有SERVERS_ADMIN权限,会提示错误信息:Access denied; you need (at least one of) the SERVERS_ADMIN OR SUPER privilege(s) for this operation

      • 如果您是普通账户没有SERVERS_ADMIN权限,可以使用高权限账户执行:GRANT SERVERS_ADMIN ON *.* TO `users`@`%` WITH GRANT OPTION;如果您是高权限账户没有SERVERS_ADMIN权限,您可以点击集群列表找到所需要设置的集群ID/名称>配置与管理>账号管理>重置权限以重置权限,等待一段时间后,再查看高权限账户,此时就有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_access_key_id>","oss_access_key_secret":"<my_oss_access_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_access_key_id

      字符串

      RAM用户或阿里云账号的AccessKey ID。

      如何创建AccessKey请参见创建AccessKey

      my_oss_access_key_secret

      字符串

      RAM用户或阿里云账号的AccessKey Secret。

      如何创建AccessKey请参见创建AccessKey

  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%";

    查询数据的过程中,常见的报错信息及报错原因请参见下表:

    说明

    如果在查询数据的过程中,没有报错信息但有警告信息时,您需要通过SHOW WARNINGS;命令查看报错信息。

    报错信息

    报错原因

    解决方案

    OSS error: No corresponding data file on the OSS engine.

    OSS上没有找到对应的数据文件。

    您需要根据上述规则检查OSS上对应的路径下是否存在数据文件。

    • 若存在,确认数据文件格式是否符合命名规则。即符合外表名.CSV,且文件名后缀CSV必须为大写格式。

    • 若不存在,则需要将数据文件上传至目标路径。

    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信息。