PolarDB：通過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;