PolarDB:OSS 外部テーブルを使用した OSS データへのアクセス

クエリプロセス中に、クエリエンジンは特定の条件を持つクエリをリモート OSS バケットにプッシュダウンして、クエリ効率を高めることができます。この最適化は、エンジン条件プッシュダウン と呼ばれます。エンジン条件プッシュダウン機能には、次の制限があります。

• UTF-8 エンコードされた CSV ファイルのみがサポートされています。

• SQL 文では、次の種類の演算子のみがサポートされています。

  • 比較演算子: >、<、>=、<=、および ==

  • 論理演算子: LIKE、IN、AND、および OR

  • 算術演算子: +、-、*、および /

• SQL 文を使用する場合、クエリできるファイルは 1 つだけです。JOIN、ORDER BY、GROUP BY、および HAVING 句はサポートされていません。

• WHERE 句には集約条件を含めることはできません。たとえば、WHERE max(age) > 100 は許可されていません。

• SQL 文には最大 1,000 列を指定できます。SQL 文の列名は最大 1,024 バイトの長さにすることができます。

• LIKE 句では最大 5 つのワイルドカード（%）がサポートされています。

• IN 句では最大 1,024 の定数がサポートされています。

• 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 サーバーでのみ実行できます。

• 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 サーバーでのみ実行できます。

  説明

  データをクエリするには、PolarDB for MySQL 8.0.2 クラスタを使用する必要があります。 エンジンバージョン5.6、5.7、8.0 で、クラスタバージョンを確認できます。

• 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 データファイルの一部の文字がエンジン条件プッシュダウンの要件を満たしていません。

OSS エラー: 現在のクエリはエンジン条件プッシュダウンをサポートしていません。 NO_ECP() ヒントを使用するか、optimizer_switch = 'engine_condition_pushdown=OFF' を設定して、条件プッシュダウン機能をオフにする必要があります。

ヒントまたは optimizer_switch 変数を使用して、エンジン条件プッシュダウン機能を手動で無効にすることができます。

• ヒント

  ヒントを使用して、クエリのエンジン条件プッシュダウン機能を無効にします。次の例では、t1 テーブルのエンジン条件プッシュダウン機能が無効になっています。

  SELECT /*+ NO_ECP(t1) */ `j` FROM `t1` WHERE `j` LIKE "%c%" LIMIT 10;

• optimizer_switch

  optimizer_switch 変数を使用して、現在のセッションのすべてのクエリのエンジン条件プッシュダウン機能を無効にします。

  SET SESSION optimizer_switch='engine_condition_pushdown=off'; # engine_condition_pushdown パラメータを off に設定します。この場合、現在のセッションのすべてのクエリのエンジン条件プッシュダウン機能が無効になります。

  次の文を実行して、optimizer_switch 変数の値に基づいて、現在のセッションのすべてのクエリのエンジン条件プッシュダウン機能が有効になっているかどうかを確認できます。

  select @@optimizer_switch;