PolarDB における IMCI ソートキー最適化 - PolarDB - Alibaba Cloud - PolarDB

このトピックでは、本番環境をシミュレートし、インメモリ列指向インデックス (IMCI) を使用して大規模なデータセットのクエリパフォーマンスを向上させ、単一テーブルおよび複数テーブルでのスロークエリの問題に対処する方法を実演します。

インメモリ列指向インデックス (IMCI) とは

インメモリ列指向インデックス (IMCI) は、テーブルの列のすべてまたは一部を PolarDB for MySQL の読み取り専用ノードに列指向フォーマットで格納することで、ハイブリッド行・列ストレージモデルを構築します。クエリオプティマイザも、列指向ストレージ用に設計された新しい実行演算子によって強化されています。これにより、大規模なデータセットに対するデータ分析および複雑なクエリのパフォーマンスが大幅に向上します。詳細については、「インメモリ列指向インデックス (IMCI) とは」をご参照ください。

操作手順

前提条件

クラスター
- 製品バージョン： Enterprise Edition
- シリーズ： Cluster Edition (Dedicated)
- カーネルバージョン： 8.0.1.1.45.2
- ホットスタンバイクラスター：有効
- コンピューティングノード： 32 コア 256 GB (polar.mysql.x8.4xlarge)、プライマリノード 1 つと読み取り専用ノード 1 つ (ホットスタンバイ)
- ストレージタイプ： PSL5
- パラメーターテンプレート： MySQL_InnoDB_8.0_Standard Edition_Default parameter template

データ

TPC-H ベンチマークに基づく 100 GB のデータセットです。

-- データベース内のテーブルの行数とサイズを照会します。
+----------+----------+-----------+-----------------+
| 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 ベンチマークの結果と比較することはできません。

IMCI の設定

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

単一テーブルクエリ

実行速度の遅い SQL クエリのシナリオをシミュレートします。IMCI を作成する前に次の単一テーブルクエリを実行し、その実行時間を記録します。

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

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+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 を作成します。詳細については、「インメモリ列指向インデックスの作成」をご参照ください。

ALTER TABLE lineitem COMMENT 'COLUMNAR=1 lineitem table comment';

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

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)

lineitem テーブルの IMCI を作成した後、単一テーブルクエリを再度実行し、実行時間を記録します。

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

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+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+LIMIT)	12.80	744.22

IMCI を追加すると、単一テーブルの SQL クエリのパフォーマンスが大幅に向上します。

説明

このデータは SQL 実行パフォーマンスを評価するためのベンチマークであり、絶対的な基準ではありません。実際の SQL 実行時間は、クラスター設定、現在の接続数、同時クエリ数、リアルタイムのシステム負荷など、複数の動的要因に依存します。

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

このセクションでは、IMCI が必要なすべてのテーブルと列をカバーしていない低速クエリのシナリオをシミュレートします。次の SQL クエリを実行し、その実行時間を記録します。

説明

次の SQL クエリでは、IMCI は lineitem テーブルに対してのみ作成されています。他のテーブルには IMCI はありません。
クエリに必要なテーブルまたは列が IMCI によって完全にカバーされていない場合、高速化は適用されません。
クエリに必要なテーブルまたは列が完全にカバーされているかどうかわからない場合は、dbms_imci.check_columnar_index('<query_string>'); ストアドプロシージャを使用して確認できます。IMCI を迅速に作成できるように、PolarDB は IMCI 作成用の DDL ステートメントを取得するストアドプロシージャを提供しています。詳細については、「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 秒です。したがって、実行時間は 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 を一括作成します。詳細については、「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 の構築進捗の確認」をご参照ください。

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:43:27 | 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)

tpch データベースの IMCI を一括作成した後、複数テーブルクエリとサブクエリを再度実行し、実行時間を記録します。

複数テーブルの結合 (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 を追加すると、複数テーブルクエリとサブクエリのパフォーマンスが大幅に向上します。
説明
このデータは SQL 実行パフォーマンスを評価するためのベンチマークであり、絶対的な基準ではありません。実際の SQL 実行時間は、クラスター設定、現在の接続数、同時クエリ数、リアルタイムのシステム負荷など、複数の動的要因に依存します。

HTAP リクエストルーティング

読み取り専用の IMCI ノードを追加すると、デフォルトで [クラスターのエンドポイント] が [クラスターのエンドポイント] 用に設定されます。この設定は、オンライン分析処理 (OLAP) とオンライントランザクション処理 (OLTP) の両方のリクエストが、同じアプリケーションを介してデータベースにアクセスするシナリオに適しています。読み取りリクエストは、スキャンされる行数に基づいて、IMCI ノードまたは行ストアノードのいずれかに自動的にルーティングされます。OLAP と OLTP のワークロードが異なるアプリケーションから発生する場合、手動ルーティングを設定できます。その場合、各アプリケーション用に個別のエンドポイントを作成し、対応するエンドポイントの ノード設定 に行ストアノードと IMCI ノードを割り当てる必要があります。これにより、行ストアと列ストア間の効果的なワークロード分離が保証されます。詳細については、「行ストアノードと IMCI ノード間での HTAP ベースのリクエスト分散」をご参照ください。

次の図は、自動および手動のリクエストルーティングを示しています。

高度な使用方法

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

ソートキー

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

IMCI データは行グループで構成され、各行グループにはデフォルトで 64,000 行が含まれます。各行グループ内で、異なる列は別々の列データブロックにパックされます。これらのブロックは、元の行ストアデータのプライマリキーの順序に基づいて並列に構築されるため、順序付けられていません。ソートキーを設定すると、列データブロックが並べ替えられ、クエリパフォーマンスが向上します。

imci_enable_pack_order_key パラメーターを ON に設定して、IMCI ソート機能を有効にします。これにより、新しい IMCI が作成されるときにデータがソートされます。
説明
- imci_enable_pack_order_key パラメーターのデフォルト値は ON です。このパラメーターを変更していない場合は、この手順をスキップできます。
- PolarDBコンソールでは、クラスターパラメーターには MySQL 設定ファイルとの互換性を確保するために loose_ というプレフィックスが付きます。PolarDBコンソールで imci_enable_pack_order_key パラメーターを変更する必要がある場合は、loose_ プレフィックスが付いたパラメーター (loose_imci_enable_pack_order_key) を選択してください。詳細については、「クラスターとノードのパラメーターの設定」をご参照ください。

ソートキーを追加する前に、次の 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)

order_key 属性を lineitem テーブルに追加して、ソートされた IMCI データを構築します。
```
ALTER TABLE lineitem COMMENT='COLUMNAR=1 order_key=L_RECEIPTDATE,L_SHIPMODE lineitem table comment';
```
ソートされた IMCI データの構築が完了するまで待ちます。詳細については、「ソートされた 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 のサーバーレス機能は、動的なエラスティックスケーリング機能を提供します。クラスター内のノードは数秒以内にエラスティックスケーリングし、ビジネスオペレーションを中断することなく突然のワークロードの急増に対応できます。ワークロードが少ない期間には、このメカニズムが自動的にリソースをスケールダウンしてコストを削減します。サーバーレス機能の詳細については、「サーバーレス」をご参照ください。

ビジネスのワークロードが大幅に変動する場合や、現在のクラスター設定が突然のワークロードの急増に対応できない懸念がある場合は、クラスターの 基本情報 > データベースノード セクションで Serverless の有効化 できます。詳細については、「固定仕様クラスターでサーバーレス機能を有効にする」をご参照ください。

補足情報

課金

IMCI 機能は無料でご利用いただけます。課金対象は読み取り専用 IMCI ノードのみで、標準のコンピューティングノードとして課金されます。詳細については、「コンピューティングノードの課金」をご参照ください。IMCI はストレージ領域も消費します。詳細については、「ストレージ領域の課金」をご参照ください。

説明

行ストアと比較して、IMCI は通常 3:1 から 10:1 の圧縮率を達成し、同等の行ストアのストレージ領域の約 10% から 30% を占めます。これにより、データストレージの使用量がさらに 10% から 30% 増加します。

パフォーマンス

クエリパフォーマンス
- IMCI はほとんどの複雑なクエリを大幅に高速化し、パフォーマンスが最大で 100 倍向上することもあります。
- 従来の OLAP データベースである ClickHouse と比較して、IMCI を有効にした PolarDB for MySQL クラスターのパフォーマンスは同等であり、特定のシナリオでは優位性があります。IMCI は、単一テーブルのスキャン、集約 (AGG)、結合などのシナリオで優れています。今後の IMCI バージョンでは、集約の高速化、ウィンドウ関数、その他の機能について引き続き最適化していきます。
説明
詳細については、「パフォーマンスの向上」をご参照ください。
書き込みパフォーマンス
IMCI を追加した場合の書き込みパフォーマンスへの影響は、通常 5% 以内です。Sysbench テストスイートで oltp_insert workload をテストした場合、IMCI を追加した後の書き込みパフォーマンスは約 3% 低下します。

専門家によるサポートの利用

IMCI に関するご質問がある場合は、グループ ID 27520023189 を検索して、当社の DingTalk グループに参加できます。@メンション機能を使用して、グループ内の専門家に直接質問できます。

よくある質問

クエリが IMCI を使用しない場合

読み取り専用 IMCI ノードを追加した後、クエリが高速化のために IMCI を使用するには、クエリ内のすべてのテーブルに IMCI が作成され、クエリの推定実行コストが特定のしきい値を超え、かつクエリが読み取り専用 IMCI ノードにルーティングされる必要があります。クエリが IMCI を使用しない場合は、次の手順に従って問題のトラブルシューティングを行ってください：

SQL クエリが読み取り専用 IMCI ノードにルーティングされていることを確認してください。
- エンドポイントのノード設定に読み取り専用の IMCI ノードが含まれているかどうかを確認します。
- SQL Explorer 機能を使用して、クエリが読み取り専用 IMCI ノードにルーティングされたかどうかを確認してください。
[トランザクションと分析処理の分離]が有効になっている クラスターのエンドポイント を使用する場合、データベースプロキシは、クエリの推定実行コストが loose_imci_ap_threshold または loose_cost_threshold_for_imci で設定されたしきい値を超えると、そのクエリを読み取り専用 IMCI ノードにルーティングします。また、SELECT キーワードの前に /*FORCE_IMCI_NODES*/ ヒントを追加して、クエリを強制的に読み取り専用 IMCI ノードにルーティングすることもできます。詳細については、「自動リクエストルーティングのしきい値の設定」をご参照ください。例：
カーネルバージョン 8.0.1.1.39 および 8.0.2.2.23 以降では、loose_imci_ap_threshold パラメーターは非推奨です。代わりに loose_cost_threshold_for_imci パラメーターを使用してください。
```
/*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;
```
新しいエンドポイントを作成すると、SQL クエリが常に読み取り専用 IMCI ノードへルーティングされるようにできます。詳細については、「カスタムエンドポイントの作成」をご参照ください。
クエリの推定実行コストが設定されたしきい値を超えていることを確認してください。
読み取り専用 IMCI ノードでは、オプティマイザがクエリのコストを推定します。推定実行コストが loose_imci_ap_threshold または loose_cost_threshold_for_imci で設定されたしきい値よりも高い場合、クエリは IMCI を使用します。そうでない場合は、元の行ベースのインデックスを使用します。
SQL クエリが IMCI 読み取り専用ノードにルーティングされることを確認した後、EXPLAIN を使用して実行計画を表示しても IMCI が使用されていない場合は、推定実行コストを事前に設定されたしきい値と比較して、推定実行コストが低すぎるために IMCI が使用されていないかどうかを判断できます。Last_query_cost_for_imci 変数をクエリすることで、最後の SQL クエリの推定実行コストを取得できます：
```
-- EXPLAIN を使用して SQL クエリの実行計画を表示します。
EXPLAIN SELECT * FROM t1;
-- 最後のクエリの推定実行コストを取得します。
SHOW STATUS LIKE 'Last_query_cost_for_imci';
```
クラスターエンドポイントを使用してデータベースに接続する場合、SHOW STATUS LIKE 'Last_query_cost_for_imci' の前に HINT 構文 /*ROUTE_TO_LAST_USED*/ を追加して、正しいノードで前のステートメントの推定実行コストをクエリできるようにすることを推奨します。例： /*ROUTE_TO_LAST_USED*/SHOW STATUS LIKE 'Last_query_cost_for_imci';
クエリの推定実行コストがしきい値を下回る場合は、loose_imci_ap_threshold または loose_cost_threshold_for_imci の値を調整することを検討してください。たとえば、ヒントを使用して単一クエリのしきい値を調整できます：
```
/*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;
```
クエリ内のテーブルと列が IMCI によって完全にカバーされていることを確認してください。
組み込みのストアドプロシージャ dbms_imci.check_columnar_index('<query_string>') を使用して、クエリ内のテーブルと列の IMCI カバレッジを確認できます。詳細については、「クエリ内のテーブルと列に対して IMCI が作成されているかどうかの確認」をご参照ください。例：
```
CALL dbms_imci.check_columnar_index('SELECT COUNT(*) FROM t1 WHERE t1.a > 1');
```
クエリが完全にカバーされていない場合、このプロシージャはカバーされていないテーブルと列を返します。その場合、それぞれに対して IMCI を作成する必要があります。クエリが完全にカバーされている場合、このプロシージャは空の結果セットを返します。
サポートされていない SQL 機能を確認してください。
IMCI の構文と制限を確認し、特定の SQL 機能が IMCI でサポートされているかどうかを確認してください。詳細については、「IMCI の構文と制限」をご参照ください。

これらの手順を実行しても SQL クエリが IMCI を使用しない場合は、専門家によるサポートを利用するか、お問い合わせください。

適切な IMCI の作成

SQL ステートメントで必要な列に対してインメモリ列指向インデックス (IMCI) を追加してください。詳細については、「SQL ステートメント内のテーブルまたは列に対して IMCI が作成されているかどうかの確認」をご参照ください。

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

説明

dbms_imci.columnar_advise() ストアドプロシージャを使用して、特定の SQL ステートメントに対して IMCI を作成するために必要なデータ定義言語 (DDL) ステートメントを取得できます。この DDL ステートメントを使用して IMCI を作成すると、その SQL ステートメントは IMCI によって完全にカバーされることが保証されます。詳細については、「IMCI 作成用の DDL ステートメントの取得」をご参照ください。
```
dbms_imci.columnar_advise('<query_string>');
```
dbms_imci.columnar_advise_begin()、dbms_imci.columnar_advise_end()、および dbms_imci.columnar_advise() ストアドプロシージャを使用して、一連の SQL ステートメントに対して IMCI を作成するために必要な DDL ステートメントを取得できます。詳細については、「IMCI をバッチで作成するための DDL ステートメントの取得」をご参照ください。

ソート済みデータセット	未ソートのデータセット
0.88	4.35