IMCI 機能を使用するためのベストプラクティス - PolarDB

このトピックでは、大規模なデータセットを特徴とするビジネス本番環境をシミュレートし、その環境でインメモリ列インデックス（IMCI）機能を使用して単一テーブルクエリと複数テーブルクエリのパフォーマンスを向上させる方法について説明します。

IMCI 機能とは

IMCI 機能は、テーブルのすべての列または特定の列を、PolarDB 読み取り専用ノードに列指向ストレージ形式で保存します。この機能により、クラスターは行と列のストレージを組み合わせたハイブリッドストレージモデルを使用できます。 PolarDB はまた、クエリオプティマイザーを列指向ストレージ形式で効果的に動作するように最適化し、列指向ストレージをより効率的に管理するための新しい実行オペレーターを追加しました。これにより、データ分析と大規模データセットを含む複雑なクエリのパフォーマンスが大幅に向上します。詳細については、「IMCI 機能とは」をご参照ください。

手順

準備

テスト用のビジネス本番環境をシミュレートします。

クラスター
- データベースエディション：Enterprise Edition。
- エディション：クラスターエディション（専用）
- データベースエンジン：8.0.1.1.45.2。
- ホットスタンバイストレージクラスター機能：有効。
- 計算ノードの仕様：32 コア、256 GB メモリ（polar.mysql.x8.4xlarge）。1 つのプライマリノードと 1 つの読み取り専用ノード（ホットスタンバイ）。
- ストレージタイプ：PSL5。
- パラメータテンプレート：MySQL_InnoDB_8.0_ Standard Edition_ デフォルトパラメータテンプレート。
データ
100 GB TPC-H ベンチマークテストデータ。
```
-- データベース内のテーブルの行数とサイズをクエリします。
+----------+----------+-----------+-----------------+
| Database | Table    | Rows      | Total Size (GB) |
+----------+----------+-----------+-----------------+
| tpch     | customer |  13179406 |            2.59 |
| tpch     | lineitem | 590446240 |           87.52 |
| tpch     | nation   |        25 |            0.00 |
| tpch     | orders   | 142929780 |           18.70 |
| tpch     | part     |  19354445 |            3.11 |
| tpch     | partsupp |  67862725 |           20.45 |
| tpch     | region   |         5 |            0.00 |
| tpch     | supplier |    986923 |            0.17 |
+----------+----------+-----------+-----------------+
```
説明
- データベース内のテーブルの行数とサイズは、インデックス、ストレージエンジン、統計情報、システムテーブルなど、さまざまな要因の影響を受けます。したがって、実際の出力は上記のクエリ結果と異なる場合があります。
- このトピックで説明されている TPC-H パフォーマンステストは、TPC-H ベンチマークテストに基づいて実装されていますが、TPC-H ベンチマークテストのすべての要件を満たしているわけではありません。したがって、テスト結果は TPC-H ベンチマークテストの公開結果と比較することはできません。

IMCI の構成

読み取り専用 IMCI ノードを追加します。この例では、32 コア、256 GB メモリ（polar.mysql.x8.4xlarge）の読み取り専用 IMCI ノードが追加されます。読み取り専用ノードはプライマリノードと同じ仕様です。詳細については、「読み取り専用 IMCI ノードを追加する」をご参照ください。

単一テーブルクエリ

ビジネスにおける SQL クエリの非効率的な実行をシミュレートします。IMCI が構成されていないときに、次の単一テーブル SQL クエリを実行し、実行時間を記録します。

単一テーブルのスキャンとフィルタリング

SELECT * FROM lineitem WHERE L_COMMENT > 'aaaaaaaa' AND L_COMMENT < 'aaaaaaz';

-- 結果例
Empty set (8 min 47.29 sec)

単一列の集約（AGG）

SELECT SUM(L_DISCOUNT) from lineitem;

-結果例
+-----------------+
| SUM(L_DISCOUNT) |
+-----------------+
|     30001636.44 |
+-----------------+
1 row in set (2 min 6.64 sec)

グループ化された集約（GROUP BY）

SELECT AVG(L_DISCOUNT) FROM lineitem WHERE L_SHIPDATE <= date '1998-12-01' - interval '90' day GROUP BY L_RETURNFLAG, L_LINESTATUS;

-- 結果例
+-----------------+
| AVG(L_DISCOUNT) |
+-----------------+
|        0.049998 |
|        0.050001 |
|        0.050002 |
|        0.049985 |
+-----------------+
4 rows in set (6 min 28.96 sec)

ページネーション（ORDER BY and LIMIT）

SELECT L_ORDERKEY, SUM(L_QUANTITY) FROM lineitem GROUP BY L_ORDERKEY ORDER BY SUM(L_QUANTITY) DESC LIMIT 1000000, 100;

-- 結果例
+------------+-----------------+
| L_ORDERKEY | SUM(L_QUANTITY) |
+------------+-----------------+
|   25226310 |          244.00 |
|     ...    |            ...  |
|  494738146 |          244.00 |
+------------+-----------------+
100 rows in set (12 min 24.22 sec)

IMCI を作成します。詳細については、「IMCI を作成する」をご参照ください。

ALTER TABLE lineitem COMMENT 'COLUMNAR=1 lineitem';

-- 結果例
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

IMCI の構築進捗状況を表示します。IMCI が作成されるまで待ちます。詳細については、「IMCI の構築進捗状況を表示する」をご参照ください。

SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;

-- IMCI を構築中です。
+-------------+------------+---------------------+---------------------+-------------+----------+------------------+--------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| SCHEMA_NAME | TABLE_NAME | CREATED_AT          | STARTED_AT          | FINISHED_AT | STATUS   | APPROXIMATE_ROWS | SCANNED_ROWS | SCAN_SECOND | SORT_ROUNDS | SORT_SECOND | BUILD_ROWS | BUILD_SECOND | AVG_SPEED | SPEED_LAST_SECOND | ESTIMATE_SECOND |
+-------------+------------+---------------------+---------------------+-------------+----------+------------------+--------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| tpch        | lineitem   | 2024-10-21 13:44:02 | 2024-10-21 13:44:02 |             | Building | 590446240        | 36718757(6%) | 19          | 0           | 0           | 0(0%)      | 0            | 1848522   | 0                 | 299             |
+-------------+------------+---------------------+---------------------+-------------+----------+------------------+--------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
1 row in set, 1 warning (0.00 sec)

-- IMCI が作成されました。
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| SCHEMA_NAME | TABLE_NAME | CREATED_AT          | STARTED_AT          | FINISHED_AT         | STATUS       | APPROXIMATE_ROWS | SCANNED_ROWS    | SCAN_SECOND | SORT_ROUNDS | SORT_SECOND | BUILD_ROWS | BUILD_SECOND | AVG_SPEED | SPEED_LAST_SECOND | ESTIMATE_SECOND |
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| tpch        | lineitem   | 2024-10-21 13:44:02 | 2024-10-21 13:44:02 | 2024-10-21 13:50:11 | Safe to read | 590446240        | 600037902(100%) | 369         | 0           | 0           | 0(0%)      | 0            | 1625058   | 0                 | 0               |
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
1 row in set, 1 warning (0.00 sec)

IMCI が作成された lineitem テーブルに対して単一テーブルクエリを実行し、実行時間を記録します。

単一テーブルのスキャンとフィルタリング

SELECT * FROM lineitem WHERE L_COMMENT > 'aaaaaaaa' AND L_COMMENT < 'aaaaaaz';

-- 結果例
Empty set (1.47 sec)

単一列の集約（AGG）

SELECT SUM(L_DISCOUNT) from lineitem;

-- 結果例
+-----------------+
| SUM(L_DISCOUNT) |
+-----------------+
|     30001636.44 |
+-----------------+
1 row in set (0.06 sec)

グループ化された集約（GROUP BY）

SELECT AVG(L_DISCOUNT) FROM lineitem WHERE L_SHIPDATE <= date '1998-12-01' - interval '90' day GROUP BY L_RETURNFLAG, L_LINESTATUS;

-- 結果例
+-----------------+
| AVG(L_DISCOUNT) |
+-----------------+
|        0.050001 |
|        0.050002 |
|        0.049985 |
|        0.049998 |
+-----------------+
4 rows in set (2.54 sec)

ページネーション（ORDER BY and LIMIT）

SELECT L_ORDERKEY, SUM(L_QUANTITY) FROM lineitem GROUP BY L_ORDERKEY ORDER BY SUM(L_QUANTITY) DESC LIMIT 1000000, 100;

-- 結果例
+------------+-----------------+
| L_ORDERKEY | SUM(L_QUANTITY) |
+------------+-----------------+
|  299074498 |          244.00 |
|     ...    |            ...  |
|  168679332 |          244.00 |
+------------+-----------------+
100 rows in set (12.80 sec)

クエリの実行時間（単位：秒）を比較します。

クエリタイプ	PolarDB IMCI	PolarDB 行インデックス
単一テーブルのスキャンとフィルタリング	1.47	527.29
単一列の集約（AGG）	0.06	126.64
グループ化された集約（GROUP BY）	2.54	388.96
ページネーション（ORDER BY and LIMIT）	12.80	744.22

上記のデータは、IMCI を作成した後、単一テーブル SQL クエリの実行効率が大幅に向上することを示しています。

説明

上記のデータは、SQL 実行効率を評価するための参照ベンチマークとして機能するものであり、絶対的な基準ではありません。SQL クエリの具体的な実行時間は、クラスター構成、現在の接続数、同時クエリの数、システムリソースのリアルタイム負荷など、さまざまな要因によって動的に影響を受けます。

複数テーブルクエリとサブクエリ

ビジネスにおける SQL クエリの非効率的な実行をシミュレートします。IMCI に完全には含まれていないテーブルまたは列に対して次の SQL クエリを実行し、実行時間を記録します。

説明

次の例では、IMCI は lineitem テーブルに対してのみ作成されます。他のテーブルに対しては IMCI は作成されません。
SQL クエリに含まれるテーブルまたは列が IMCI に完全には含まれていない場合、IMCI はその SQL クエリに対して有効になりません。
SQL クエリに含まれるテーブルまたは列が IMCI に完全には含まれているかどうかがわからない場合は、dbms_imci.check_columnar_index('<query_string>'); ストアドプロシージャを呼び出して、SQL 文で指定されたテーブルに対して IMCI が作成されているかどうかを確認できます。 PolarDB はまた、IMCI を作成するために使用される DDL 文を取得または一括取得するのに役立つストアドプロシージャも提供しています。取得した DDL 文を使用して、IMCI をすばやく作成できます。詳細については、「IMCI 用の DDL ツール」をご参照ください。

複数テーブル結合（JOIN）

SELECT
  COUNT(l3.L_DISCOUNT)
FROM
  (
    (
      (
        (
          (
            nation n1 STRAIGHT_JOIN nation n2 on n1.N_NATIONKEY = n2.N_NATIONKEY
          )
          STRAIGHT_JOIN supplier on n2.N_NATIONKEY = supplier.S_NATIONKEY and S_SUPPKEY < 2000
        )
        STRAIGHT_JOIN lineitem AS l1 on l1.L_SUPPKEY = supplier.S_SUPPKEY
      )
      STRAIGHT_JOIN lineitem AS l2 on l1.L_ORDERKEY = l2.L_ORDERKEY and l1.L_LINENUMBER = l2.L_LINENUMBER
    )
    STRAIGHT_JOIN lineitem AS l3 on l2.L_ORDERKEY = l3.L_ORDERKEY and l2.L_LINENUMBER = l3.L_LINENUMBER
  )
GROUP BY 
  n1.N_NAME;

クエリが最大実行時間を超えたため停止されました。最大実行時間は 7,200 秒です。

サブクエリを含むクエリ

SELECT 
  O_ORDERPRIORITY, COUNT(*) as ORDER_COUNT 
FROM
  orders
WHERE
  O_ORDERDATE >= '1995-01-01' AND
  O_ORDERDATE < date_add('1995-01-01', interval '3' month) AND
  EXISTS 
    (
      SELECT * FROM lineitem WHERE L_ORDERKEY = O_ORDERKEY AND L_COMMITDATE < L_RECEIPTDATE
    )
GROUP BY 
  O_ORDERPRIORITY
ORDER BY
  O_ORDERPRIORITY;

-- サンプル出力
+-----------------+-------------+
| O_ORDERPRIORITY | ORDER_COUNT |
+-----------------+-------------+
| 1-URGENT        |     1028353 |
| 2-HIGH          |     1030059 |
| 3-MEDIUM        |     1028615 |
| 4-NOT SPECIFIED |     1028496 |
| 5-LOW           |     1029615 |
+-----------------+-------------+
5 rows in set (4 min 9.51 sec)

サブクエリを含む複数テーブル結合

SELECT 
  C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE, SUM(L_QUANTITY)
FROM 
  (
    SELECT * FROM orders WHERE O_ORDERKEY IN 
      (
        SELECT L_ORDERKEY FROM lineitem GROUP BY L_ORDERKEY HAVING SUM(L_QUANTITY) > 300
      ) 
  ) AS tmp, customer, lineitem 
WHERE 
  C_CUSTKEY = O_CUSTKEY AND 
  O_ORDERKEY = L_ORDERKEY
GROUP BY 
  C_NAME, 
  C_CUSTKEY, 
  O_ORDERKEY, 
  O_ORDERDATE, 
  O_TOTALPRICE
ORDER BY 
  O_TOTALPRICE DESC, 
  O_ORDERDATE;

+--------------------+-----------+------------+-------------+--------------+-----------------+
| C_NAME             | C_CUSTKEY | O_ORDERKEY | O_ORDERDATE | O_TOTALPRICE | SUM(L_QUANTITY) |
+--------------------+-----------+------------+-------------+--------------+-----------------+
| Customer#011472112 |  11472112 |  458304292 | 1998-02-05  |    591036.15 |          322.00 |
|        ...         |     ...   |     ...    |     ...     |       ...    |            ...  |
| Customer#003777694 |   3777694 |  470363105 | 1997-04-06  |    349914.00 |          302.00 |
| Customer#009446411 |   9446411 |  592379937 | 1995-12-29  |    343496.05 |          304.00 |
+--------------------+-----------+------------+-------------+--------------+-----------------+
6398 rows in set (12 min 46.15 sec)

tpch データベース内のテーブルに対して IMCI をバッチ追加します。

CREATE COLUMNAR INDEX FOR TABLES IN tpch;

-- サンプル出力
+------------+-------------------+
| Table_Name | Result            |
+------------+-------------------+
| customer   | Ok                |
| lineitem   | Skip by no change |
| nation     | Ok                |
| orders     | Ok                |
| part       | Ok                |
| partsupp   | Ok                |
| region     | Ok                |
| supplier   | Ok                |
+------------+-------------------+
8 rows in set (56.74 sec)

IMCI の構築進捗状況を表示します。IMCI が作成されるまで待ちます。詳細については、「IMCI の構築進捗状況を表示する」をご参照ください。

SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;

-- IMCI が作成されました。
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| SCHEMA_NAME | TABLE_NAME | CREATED_AT          | STARTED_AT          | FINISHED_AT         | STATUS       | APPROXIMATE_ROWS | SCANNED_ROWS    | SCAN_SECOND | SORT_ROUNDS | SORT_SECOND | BUILD_ROWS | BUILD_SECOND | AVG_SPEED | SPEED_LAST_SECOND | ESTIMATE_SECOND |
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
| tpch        | region     | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | Safe to read | 5                | 5(100%)         | 0           | 0           | 0           | 0(0%)      | 0            | 150       | 0                 | 0               |
| tpch        | lineitem   | 2024-10-21 14:36:13 | 2024-10-21 14:36:13 | 2024-10-21 14:42:23 | Safe to read | 590446240        | 600037902(100%) | 370         | 0           | 0           | 0(0%)      | 0            | 1620776   | 0                 | 0               |
| tpch        | supplier   | 2024-10-21 14:44:16 | 2024-10-21 14:44:16 | 2024-10-21 14:44:17 | Safe to read | 986923           | 1000000(100%)   | 1           | 0           | 0           | 0(0%)      | 0            | 784971    | 0                 | 0               |
| tpch        | part       | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:43:38 | Safe to read | 19354445         | 20000000(100%)  | 11          | 0           | 0           | 0(0%)      | 0            | 1784854   | 0                 | 0               |
| tpch        | customer   | 2024-10-21 14:43:19 | 2024-10-21 14:43:19 | 2024-10-21 14:43:27 | Safe to read | 13179406         | 15000000(100%)  | 7           | 0           | 0           | 0(0%)      | 0            | 2051651   | 0                 | 0               |
| tpch        | nation     | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:4327 | Safe to read | 25               | 25(100%)        | 0           | 0           | 0           | 0(0%)      | 0            | 739       | 0                 | 0               |
| tpch        | partsupp   | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:44:16 | Safe to read | 67862725         | 80000000(100%)  | 49          | 0           | 0           | 0(0%)      | 0            | 1620131   | 0                 | 0               |
| tpch        | orders     | 2024-10-21 14:43:27 | 2024-10-21 14:43:27 | 2024-10-21 14:44:27 | Safe to read | 142929780        | 150000000(100%) | 59          | 0           | 0           | 0(0%)      | 0            | 2501701   | 0                 | 0               |
+-------------+------------+---------------------+---------------------+---------------------+--------------+------------------+-----------------+-------------+-------------+-------------+------------+--------------+-----------+-------------------+-----------------+
8 rows in set, 1 warning (0.00 sec)

IMCI が作成された tpch データベースに対して、複数テーブルクエリとサブクエリを含むクエリを実行し、実行時間を記録します。

複数テーブル結合（JOIN）

SELECT
  COUNT(l3.L_DISCOUNT)
FROM
  (
    (
      (
        (
          (
            nation n1 STRAIGHT_JOIN nation n2 on n1.N_NATIONKEY = n2.N_NATIONKEY
          )
          STRAIGHT_JOIN supplier on n2.N_NATIONKEY = supplier.S_NATIONKEY and S_SUPPKEY < 2000
        )
        STRAIGHT_JOIN lineitem AS l1 on l1.L_SUPPKEY = supplier.S_SUPPKEY
      )
      STRAIGHT_JOIN lineitem AS l2 on l1.L_ORDERKEY = l2.L_ORDERKEY and l1.L_LINENUMBER = l2.L_LINENUMBER
    )
    STRAIGHT_JOIN lineitem AS l3 on l2.L_ORDERKEY = l3.L_ORDERKEY and l2.L_LINENUMBER = l3.L_LINENUMBER
  )
GROUP BY 
  n1.N_NAME;

+----------------------+
| COUNT(l3.L_DISCOUNT) |
+----------------------+
|                56930 |
|                 ...  |
|                49995 |
+----------------------+
25 rows in set (6.25 sec)

サブクエリを含むクエリ

SELECT 
  O_ORDERPRIORITY, COUNT(*) as ORDER_COUNT 
FROM
  orders
WHERE
  O_ORDERDATE >= '1995-01-01' AND
  O_ORDERDATE < date_add('1995-01-01', interval '3' month) AND
  EXISTS 
    (
      SELECT * FROM lineitem WHERE L_ORDERKEY = O_ORDERKEY AND L_COMMITDATE < L_RECEIPTDATE
    )
GROUP BY 
  O_ORDERPRIORITY
ORDER BY
  O_ORDERPRIORITY;

-- サンプル出力
+-----------------+-------------+
| O_ORDERPRIORITY | ORDER_COUNT |
+-----------------+-------------+
| 1-URGENT        |     1028353 |
| 2-HIGH          |     1030059 |
| 3-MEDIUM        |     1028615 |
| 4-NOT SPECIFIED |     1028496 |
| 5-LOW           |     1029615 |
+-----------------+-------------+
5 rows in set (2.49 sec)

サブクエリを含む複数テーブル結合

SELECT 
  C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE, SUM(L_QUANTITY)
FROM 
  (
    SELECT * FROM orders WHERE O_ORDERKEY IN 
      (
        SELECT L_ORDERKEY FROM lineitem GROUP BY L_ORDERKEY HAVING SUM(L_QUANTITY) > 300
      ) 
  ) AS tmp, customer, lineitem 
WHERE 
  C_CUSTKEY = O_CUSTKEY AND 
  O_ORDERKEY = L_ORDERKEY
GROUP BY 
  C_NAME, 
  C_CUSTKEY, 
  O_ORDERKEY, 
  O_ORDERDATE, 
  O_TOTALPRICE
ORDER BY 
  O_TOTALPRICE DESC, 
  O_ORDERDATE;

-- サンプル出力
+--------------------+-----------+------------+-------------+--------------+-----------------+
| C_NAME             | C_CUSTKEY | O_ORDERKEY | O_ORDERDATE | O_TOTALPRICE | SUM(L_QUANTITY) |
+--------------------+-----------+------------+-------------+--------------+-----------------+
| Customer#011472112 |  11472112 |  458304292 | 1998-02-05  |    591036.15 |          322.00 |
|        ...         |     ...   |     ...    |     ...     |       ...    |            ...  |
| Customer#003777694 |   3777694 |  470363105 | 1997-04-06  |    349914.00 |          302.00 |
| Customer#009446411 |   9446411 |  592379937 | 1995-12-29  |    343496.05 |          304.00 |
+--------------------+-----------+------------+-------------+--------------+-----------------+
6398 rows in set (16.16 sec)

クエリの実行時間（単位：秒）を比較します。

クエリタイプ	PolarDB IMCI	PolarDB 行インデックス
複数テーブル結合（JOIN）	6.25	>7200
サブクエリを含むクエリ	2.49	249.51
サブクエリを含む複数テーブル結合	16.16	766.15

上記のデータは、テーブルに IMCI を作成した後、複数テーブルクエリとサブクエリを含むクエリの実行効率が大幅に向上することを示しています。

説明

行ストアノードと IMCI ノード間のリクエスト分散

デフォルトでは、読み取り専用 IMCI ノードをクラスターに追加した後、[クラスターエンドポイント] を使用してクラスターへのリクエストは、行ストアノードと IMCI ノード間で自動的に分散されます。自動リクエスト分散は、分析処理（OLAP）とオンライントランザクション処理（OLTP）リクエストが同じアプリケーションから発生するビジネスシナリオに適しています。システムは、スキャンされた行数に基づいて、OLAP および OLTP リクエストを IMCI ノードと行ストアノードに自動的に分散できます。OLAP リクエストと OLTP リクエストが異なるアプリケーションから発生する場合は、アプリケーションごとに異なるエンドポイントを構成し、行ストアノードと IMCI ノードをエンドポイントの [選択済みノード] に追加できます。詳細については、「HTAP ベースの行ストアと IMCI ノード間のリクエスト分散」をご参照ください。

次の図は、行ストアノードと IMCI ノード間の自動および手動のリクエスト分散プロセスを示しています。

高度な使用方法

詳細については、「IMCI の高度な使用方法」をご参照ください。

IMCI のソートキーを構成する

詳細については、「IMCI のソートキーを構成する」をご参照ください。

IMCI データは、行グループによって編成されます。デフォルトでは、各行グループには 64,000 行のデータが含まれています。各行グループでは、各列が列データブロックにパックされます。列データブロックは、行データのプライマリキーの順序に基づいて並列に構築されるため、全体的には順序付けられていない状態になります。クエリパフォーマンスを向上させるために、ソートキーを構成して列データブロックのデータをソートできます。

imci_enable_pack_order_key パラメータを ON に設定して、IMCI データのソートを有効にします。
説明
- imci_enable_pack_order_key パラメータのデフォルト値は ON です。このパラメータを変更していない場合は、この手順をスキップしてください。
- MySQL 構成ファイルとの互換性を確保するために、PolarDBコンソールコンソール内のすべてのパラメータには、loose_ というプレフィックスが付いています。imci_enable_pack_order_key パラメータを PolarDBコンソールで変更する場合は、loose_ プレフィックスが付いた MySQL 互換バージョン、つまり [loose_imci_enable_pack_order_key] を指定します。詳細については、「クラスターとノードのパラメータを構成する」をご参照ください。

IMCI にソートキーが指定されていない場合に、次の SQL クエリを実行し、実行時間を記録します。

SELECT
  L_SHIPMODE,
  SUM(CASE
      WHEN O_ORDERPRIORITY = '1-URGENT' OR O_ORDERPRIORITY = '2-HIGH'
      THEN 1
      ELSE 0
      END) AS high_line_count,
  SUM(CASE
      WHEN O_ORDERPRIORITY <> '1-URGENT' AND O_ORDERPRIORITY <> '2-HIGH'
      THEN 1
      ELSE 0
      END) AS low_line_count
FROM
    orders,
    lineitem
WHERE
    O_ORDERKEY = L_ORDERKEY
    AND L_SHIPMODE in ('MAIL', 'SHIP')
    AND L_COMMITDATE <  L_RECEIPTDATE
    AND L_SHIPDATE < L_COMMITDATE
    AND L_RECEIPTDATE >= date '1994-01-01'
    AND L_RECEIPTDATE < date '1994-01-01' + interval '1' year
GROUP BY
    L_SHIPMODE
ORDER BY
    L_SHIPMODE;

-- 結果例
+------------+-----------------+----------------+
| L_SHIPMODE | high_line_count | low_line_count |
+------------+-----------------+----------------+
| MAIL       |          623115 |         934713 |
| SHIP       |          622979 |         934534 |
+------------+-----------------+----------------+
2 rows in set (4.35 sec)

順序付けられたデータを持つ IMCI を作成するために、order_key 属性を lineitem テーブルに追加します。
```
ALTER TABLE lineitem COMMENT='COLUMNAR=1 order_key=L_RECEIPTDATE,L_SHIPMODE';
```
順序付けられた IMCI データが構築されるまで待ちます。詳細については、「消費時間」をご参照ください。

手順 2 の SQL クエリを再実行し、実行時間を記録します。

-- 結果例
+------------+-----------------+----------------+
| L_SHIPMODE | high_line_count | low_line_count |
+------------+-----------------+----------------+
| MAIL       |          623115 |         934713 |
| SHIP       |          622979 |         934534 |
+------------+-----------------+----------------+
2 rows in set (0.88 sec)

クエリの実行時間（単位：秒）を比較します。
順序付けられたデータセット
順序付けられていないデータセット
0.88
4.35
説明
上記のデータは、SQL 実行効率を評価するための参照ベンチマークとして機能するものであり、絶対的な基準ではありません。SQL クエリの具体的な実行時間は、クラスター構成、現在の接続数、同時クエリの数、システムリソースのリアルタイム負荷など、さまざまな要因によって動的に影響を受けます。

読み取り専用 IMCI ノードでサーバーレス機能を有効にする

詳細については、「読み取り専用 IMCI ノードでサーバーレス機能を有効にする」をご参照ください。

サーバーレス機能は、PolarDB クラスターに動的スケーリング機能を提供します。クラスター内の各ノードは、ビジネス運用に影響を与えることなく、ワークロードの急激な増加に対応するために数秒以内にスケールアップできます。ワークロードが減少すると、コストを節約するためにノードをスケールダウンできます。サーバーレス機能の詳細については、「サーバーレス機能の概要」をご参照ください。

ビジネス負荷が大きく変動する場合、または現在のクラスター構成ではビジネス負荷の急激な増加に対応できないことが懸念される場合は、クラスターの サーバーレスを有効にする[基本情報] > [データベースノード] セクションで固定仕様クラスターのサーバーレス機能を有効にするを選択できます。詳細については、「」をご参照ください。

詳細情報

払い戻し

IMCI 機能は無料で提供されます。ただし、読み取り専用 IMCI ノードを使用する場合は課金されます。読み取り専用 IMCI ノードは、通常の計算ノードとして課金されます。請求ルールの詳細については、「計算ノードの請求ルール」をご参照ください。IMCI はストレージ容量も占有します。ストレージの請求ルールの詳細については、「ストレージの請求ルール」をご参照ください。

説明

IMCI データは列指向形式で編成されており、より高い圧縮率を実現できます。行ストアと比較して、3 ～ 10 倍の圧縮率を実現でき、行ストアが必要とするストレージ容量の約 10% ～ 30% を占有します。その結果、同じストレージ容量に保存できるデータ量が 10% ～ 30% 増加します。

パフォーマンス

クエリパフォーマンス
- IMCI 機能を使用すると、複雑なクエリの速度を最大 100 倍向上させることができます。
- 従来の OLAP データベースサービスである ClickHouse と IMCI 対応 PolarDB for MySQL クラスターには、それぞれ独自の利点があります。IMCI 対応 PolarDB for MySQL は、テーブルに対する SCAN 操作や AGGREGATE 操作、JOIN 操作などのシナリオで優れています。今後、PolarDB for MySQL の IMCI 機能は引き続き最適化され、集約の高速化とウィンドウ関数に関して飛躍的な進歩を遂げるでしょう。
説明
詳細については、「パフォーマンスの向上」をご参照ください。
書き込みパフォーマンス
IMCI を作成した後、書き込みパフォーマンスは 5% 未満低下します。sysbench テストセットを使用して oltp_insert ワークロード をテストすると、IMCI を作成した後、書き込みパフォーマンスは約 3% 低下します。

エキスパートサービス

IMCI について質問がある場合は、DingTalk グループ 27520023189 に参加してテクニカルサポートを受けてください。グループのエキスパートに相談して、質問への回答を得ることができます。

FAQ

SQL クエリで IMCI を使用して高速化されないのはなぜですか？

SQL クエリが高速化のために IMCI を使用するのは、次の条件がすべて満たされている場合のみです。読み取り専用の IMCI ノードがクラスターに追加されている。SQL クエリに関連するテーブルに IMCI が作成されている。[推定実行コスト] が指定されたしきい値を超えている。SQL クエリが読み取り専用の IMCI ノードに転送される。SQL クエリが IMCI を使用しない場合は、次の手順を実行して問題のトラブルシューティングを行うことができます。

SQL クエリが読み取り専用 IMCI ノードに転送されているかどうかを確認します。
- 読み取り専用 IMCI ノードがクラスターエンドポイントの [選択済みノード] に含まれているかどうかを確認します。
- SQL エクスプローラー機能を使用して、SQL クエリが読み取り専用 IMCI ノードに転送されているかどうかを確認します。詳細については、「SQL エクスプローラー」をご参照ください。
PolarProxy は、次の条件がすべて満たされた場合にのみ、読み取り専用の IMCI ノードに SQL クエリを転送します。SQL クエリが [クラスターエンドポイント] を使用してクラスターにアクセスしていること、[トランザクション/分析処理分割] がクラスターエンドポイントで有効になっていること、および SQL クエリの推定実行コストが loose_imci_ap_threshold パラメーターまたは loose_cost_threshold_for_imci パラメーターで指定されたしきい値を超えていることです。/*FORCE_IMCI_NODES*/ ヒントを SQL 文の SELECT キーワードの前に追加することで、SQL 文を強制的に読み取り専用の IMCI ノードに転送できます。詳細については、「自動リクエスト分散のしきい値を指定する」をご参照ください。例：
loose_imci_ap_threshold パラメーターは、マイナーバージョンが 8.0.1.1.39 以降、または 8.0.2.2.23 以降のデータベースエンジンでは、loose_cost_threshold_for_imci パラメーターの代わりに使用されます。
```
/*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;
```
HTAP ベースのリクエスト分散 (行ストアと IMCI ノード間) パラメータは、マイナーバージョンが 8.0.1.1.39 以降、または 8.0.2.2.23 以降のデータベースエンジンでは、パラメータに名前が変更されました。互換性を維持するために、パラメータは引き続き使用できます。ただし、パラメータとパラメータの両方が設定されている場合は、パラメータの値が使用されます。
SQL クエリの 推定実行コスト が指定されたしきい値よりも高いかどうかを確認します。
IMCI ノードでは、オプティマイザーは SQL クエリの実行コストを推定します。SQL クエリの推定実行コストが loose_imci_ap_threshold パラメーターまたは cost_threshold_for_imci パラメーターで指定されたしきい値よりも高い場合、オプティマイザーはクエリで IMCI を使用します。それ以外の場合、オプティマイザーはクエリで元の行インデックスを使用します。
EXPLAIN 文で、SQL クエリが読み取り専用 IMCI ノードに転送された後も実行計画で IMCI が使用されていないことが示されている場合は、推定実行コストを指定されたしきい値と比較して、推定実行コストが小さすぎるために IMCI が使用されていないかどうかを判断します。Last_query_cost 値をクエリして、前の SQL 文の推定実行コストを取得できます。例：
```
-- Execute the EXPLAIN statement to view the execution plan of an SQL query.
EXPLAIN SELECT * FROM t1;
-- Obtain the estimated execution cost of the previous SQL query.
SHOW STATUS LIKE 'Last_query_cost';
```
クラスターエンドポイントを使用してクラスターに接続する場合は、/*ROUTE_TO_LAST_USED*/ ヒントを SHOW STATUS LIKE 'Last_query_cost' 文の前に追加して、前の文の推定実行コストが正しいノードで見つかるようにすることをお勧めします。例：/*ROUTE_TO_LAST_USED*/SHOW STATUS LIKE 'Last_query_cost';
SQL クエリの推定実行コストが指定されたしきい値よりも小さい場合は、loose_imci_ap_threshold 値または loose_cost_threshold_for_imci 値を調整できます。HINT を使用して、単一の SQL クエリの実行コストのしきい値を調整することもできます。例：
```
/*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;
```
SQL クエリに含まれるテーブルまたは列が IMCI に完全に含まれているかどうかを確認します。
組み込みストアドプロシージャ dbms_imci.check_columnar_index('<query_string>') を呼び出して、SQL 文のテーブルまたは列に対して IMCI が作成されているかどうかを確認できます。詳細については、「SQL 文のテーブルに対して IMCI が作成されているかどうかを確認する」をご参照ください。例：
```
CALL dbms_imci.check_columnar_index('SELECT COUNT(*) FROM t1 WHERE t1.a > 1');
```
SQL クエリに含まれるテーブルまたは列が IMCI に完全に含まれていない場合、ストアドプロシージャは IMCI に含まれていないテーブルと列を返します。この場合、返されたテーブルと列に対して IMCI を作成する必要があります。 SQL クエリに含まれるテーブルまたは列が IMCI に完全に含まれている場合、ストアドプロシージャは空の結果セットを返します。
SQL クエリが IMCI をサポートしているかどうかを確認します。
IMCI の使用制限を表示して、SQL クエリが IMCI をサポートしているかどうかを確認します。詳細については、「IMCI の使用制限」をご参照ください。

SQL クエリで依然として IMCI が使用されていない場合は、専門家に相談するか、お問い合わせください。

適切な IMCI を SQL クエリ用に作成するにはどうすればよいですか?

SQL クエリに含まれる列に基づいて IMCI を作成することをお勧めします。詳細については、「SQL 文のテーブルに IMCI が作成されているかどうかを確認する」をご参照ください。

SQL クエリは、IMCI に SQL クエリに含まれるすべての列が含まれている場合にのみ IMCI を使用します。SQL クエリに含まれる列が IMCI で完全にカバーされていない場合は、ALTER TABLE 文を使用して IMCI を追加できます。PolarDB は、この操作を支援するための組み込みストアドプロシージャを提供しています。

説明

dbms_imci.columnar_advise() ストアドプロシージャを使用すると、SQL クエリ用に IMCI を作成するために使用される DDL 文を取得できます。DDL 文を使用して IMCI を作成する場合、SQL クエリに含まれる列が IMCI に完全に含まれていることを確認できます。詳細については、「IMCI を作成するために使用される DDL 文を取得する」をご参照ください。
```
dbms_imci.columnar_advise('<query_string>');
```
次のストアドプロシージャを使用すると、一連の SQL クエリ用に IMCI を作成するために使用される DDL 文を取得できます。dbms_imci.columnar_advise_begin()、dbms_imci.columnar_advise_end()、および dbms_imci.columnar_advise()。詳細については、「IMCI を作成するために使用される DDL 文を一括取得する」をご参照ください。

順序付けられたデータセット	順序付けられていないデータセット
0.88	4.35

IMCI 機能とは

手順

準備

IMCI の構成

単一テーブルクエリ

単一テーブルのスキャンとフィルタリング

単一列の集約（AGG）

グループ化された集約（GROUP BY）

ページネーション（ORDER BY and LIMIT）

単一テーブルのスキャンとフィルタリング

単一列の集約（AGG）

グループ化された集約（GROUP BY）

ページネーション（ORDER BY and LIMIT）

複数テーブルクエリとサブクエリ

複数テーブル結合（JOIN）

サブクエリを含むクエリ

サブクエリを含む複数テーブル結合

複数テーブル結合（JOIN）

サブクエリを含むクエリ

サブクエリを含む複数テーブル結合

行ストア ノードと IMCI ノード間のリクエスト分散

高度な使用方法

IMCI のソートキーを構成する

読み取り専用 IMCI ノードでサーバーレス機能を有効にする

詳細情報

払い戻し

パフォーマンス

エキスパート サービス

FAQ

SQL クエリで IMCI を使用して高速化されないのはなぜですか？

適切な IMCI を SQL クエリ用に作成するにはどうすればよいですか?

行ストアノードと IMCI ノード間のリクエスト分散

エキスパートサービス