通过OSS外表访问OSS数据

OSS引擎在查询过程中，可以将部分的查询条件下推到远程引擎OSS上执行，以获得更好的查询效率，这个优化被称之为engine condition pushdown。可以下推的限制条件如下：

• 目前仅支持UTF-8编码格式的CSV文本文件。

• SQL语句中仅支持以下几种类型的算子和算数表达式：

  • 比较算子：>、<、>=、<=、==

  • 逻辑算子：LIKE、IN、AND、OR

  • 算数表达式：+ 、- 、 * 、 /

• 仅支持单文件查询，不支持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个。

符合以上条件的查询会被下推到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;