統計クエリのIMCI最適化 - PolarDB - Alibaba Cloud ドキュメントセンター

クエリでインメモリ列インデックス (IMCI) プルーナー機能を使用して、クエリのパフォーマンスを向上させるためにデータベースでアクセスする必要のないデータブロックを除外できます。このトピックでは、IMCIプルーナー機能について説明します。

背景情報

IMCIが作成されると、列のデータはブロックとして格納されます。デフォルトでは、データブロックには64,000行が含まれます。次の式を使用して、データブロックの数を見積もることができます。テーブルの合計行数 /64,000。指定された列に関連するすべてのデータブロックをスキャンして、フィルタ条件を満たすデータを取得します。大きなテーブルをクエリすると、高いスキャンコストが発生します。テーブル内のデータをメモリに格納できない場合、スキャンコストはさらに増加する。統計にアクセスし、アクセスしないデータブロックを除外する条件を指定することで、クエリを高速化できます。このプロセスをPolarDBのIMCIに実装するために、プルーナーが使用されます。以下のタイプのプルーナーが提供されています。

ブルームフィルター
このプルーナータイプは、BIT配列を使用してコレクションを表し、要素がこのコレクションに属するかどうかを判断できます。
Minmaxインデックス
このプルーナタイプは、データブロックの最大数と最小数をチェックします。 Minmaxインデックスは、データブロックの最大数と最小数をフィルタ条件に対して調べて、データブロックをスキャンする必要があるかどうかを判断します。
token bloomフィルター
このプルーナータイプは、文字列をフィルタリングし、文字列を非英数字で分割します。たとえば、このプルーナーは文字列 "I am IMCI" をI | am | IMCIに分割します。これはLIKEクエリに適しています。
ngramブルームフィルター
このプルーナー型は、文字列をフィルタリングし、指定した長さで文字列を分割します。たとえば、ngramの値を3に設定した場合、このプルーナーは文字列 "I am IMCI" をI a | am | am | m I | IM | IMC | MCIに分割します。これはLIKEクエリに適しています。

シナリオ

ブルームフィルタは、等価条件およびIN条件に適しており、高い濾過性を有する等価条件に理想的である。たとえば、文字列IDを使用して等価性をフィルタリングできます。
Minmaxインデックスは、列データの局所性が高いシナリオに適しており、データまたはソートフィールドを含むwhere条件などの範囲条件および等価条件に最適です。
トークンブルームフィルタとngramブルームフィルタは、欠落したデータブロックをすばやくフィルタリングするLIKEクエリに適しています。

ストレージのオーバーヘッド

文字列データの場合、columnstoreインデックスプルーナーのクエリ最適化機能を有効にすると、ストレージのオーバーヘッドとメモリ領域が消費されます。ビジネスシナリオに基づいて、指定した列に対してブルームフィルター、minmaxインデックス、トークンブルームフィルター、またはngramブルームフィルターを選択できます。次の式を使用して、ブルームフィルターとminmaxインデックスのメモリ使用量を計算できます。

bloom filter /token bloom filter /ngram bloom filter
- データブロックのデフォルトサイズは64 KBです。行の合計に対する異なる値の比率が3% を超える場合は、次の式が使用されます。
  メモリ使用量=1.2 × ブルームフィルタを作成する列数 × テーブル行数 (単位: バイト)
- データブロックのデフォルトサイズは64 KBです。異なる値と合計行の比率が3% 以下の場合、次の式が使用されます。
  メモリ使用量=1.2 × ブルームフィルタを作成する列数 × 個別値の数 (単位: バイト)
  この場合、ブルームフィルタの結果は、データの局所性に依存する。ブルームフィルターは、データが均等に分散されるシナリオには適していません。
Minmaxインデックス

メモリ使用量=2 × minmaxインデックスが作成される列数 × (テーブル行数 /データブロックサイズ) × プレフィックス長 × 文字セット長
たとえば、テーブルの行数が20億の場合、minmaxインデックスは10列に作成され、プレフィックス長は20、ブロックサイズは64 KB、長さは4バイトのデフォルトの文字セットutf8mb4が使用されます。約46 MBのメモリが占有されています。

使用上の注意

PolarDB for MySQLクラスターのバージョンが8.0.1.1.32以前または8.0.2.2.13以前の場合、ヌルデータブロックのプルーナーは作成されず、is nullまたはIS NOT NULLフィルター条件はサポートされません。
デフォルトでは、PolarDB for MySQLクラスターのバージョンが8.0.1.1.35以降または8.0.2.2.16以降の場合、IMCIの作成時に文字列フィールドのプルーナーが作成されます。 最小使用頻度キャッシュ (LRUキャッシュ) は、ブルームフィルタのメモリ使用量を管理するためにも使用されます。 8.0.1.1.35以降または8.0.2.2.16以降にアップグレードされたクラスターの場合、IMCIを再作成する必要があります。このようにして、文字列列用のプルーナーを作成できます。
PolarDB for MySQLクラスターのバージョンが8.0.1.1.34以前または8.0.2.2.15以前の場合、既存のプルーナーはメモリに存在します。デフォルトでは、IMCIの作成時に文字列フィールドのプルーナーは作成されません。
文字列フィールドのプルーナーを作成する場合は、これらのフィールドの文字列に "\0" が含まれていないことを確認してください。例: polar\0db。
既定では、INT、DECIMAL、DATETIMEなどの数値データのminmaxインデックスが作成されます。
JSON型およびGEOMETRY型のフィールドには、Minmaxインデックスを作成できません。
INT、DECIMAL、DATETIMEなどの数値型、JSON型、BLOB型、TEXT型のフィールドには、ブルームフィルターを作成できません。

構文

テーブルを作成するときにプルーナーを作成することも、既存のテーブルでプルーナーを作成または削除することもできます。既存のテーブルでプルーナーを作成または削除するときは、IMCIを削除してからIMCIを再作成する必要があります。

説明

DDLステートメントを実行して、テーブルのスキーマのCOMMENT属性を変更し、テーブルの文字列列のプルーナーを作成または削除できます。
列レベルのCOMMENT属性は、テーブルレベルのCOMMENT属性よりも常に優先されます。

テーブルを作成するときにプルーナーを作成する

ブルームフィルターの作成

サポートされているすべてのフィールドのブルームフィルターを作成します。サンプルコード：

テーブルの作成
  t1 (
    id INT PRIMARYキー、
    str_col1 char(10) 、
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1 PRUNER_BLOOM=1"; /* コメントにはpruner_bloom属性が含まれています。*/

テーブル内の列のブルームフィルターを作成します。サンプルコード：

テーブルの作成
  t1 (
    id INT PRIMARYキー、
    str_col1 char(10) "PRUNER_BLOOM=1", /* コメントにはpruner_bloom属性が含まれています。* /
    str_col2 varchar(10)
  ) エンジンInnoDBコメント "COLUMNAR=1";

説明

PRUNER_BLOOM属性は、リビジョンバージョンが8.0.1.1.32以降または8.0.2.2.13以降のPolarDB for MySQL 8.0クラスターでサポートされています。

minmaxインデックスの作成
文字列フィールドは、多数の文字を含むことができる。 minmaxインデックスのメモリ使用量を減らすために、システムはフィールドの最初の20文字と最大255文字を自動的に抽出して、文字を最大値と最小値と比較します。 PRUNER_MINMAX属性を設定して文字列 "minmax" を生成するかどうかを指定し、PREFIX_LEN属性を設定してプレフィックス長を指定することができます。
説明
文字列の長さは、文字セットの長さとは無関係である。たとえば、PolarDBの最初の2文字はPoで、最初の5文字はPolarです。
- テーブル内のすべての文字列列にminmaxインデックスを作成します。サンプルコード：
```
テーブルの作成
  t1 (
    id INT PRIMARYキー、
    str_col1 char(10) 、
    str_col2 varchar(10)
  ) ENGINE InnoDB COMMENT "COLUMNAR=1 PRUNER_MINMAX=1 PREFIX_LEN=30"; /* コメントにはpruner_minmax属性が含まれ、プレフィックスの長さは30文字です。*/ 
```
- テーブル内の列のminmaxインデックスを作成します。サンプルコード：
```
テーブルの作成
  t1 (
    id INT PRIMARYキー、
    str_col1 char(10) "PRUNER_MINMAX=1 PREFIX_LEN=30", /* コメントにはpruner_minmax属性が含まれており、プレフィックスの長さは30文字です。* /
    str_col2 varchar (10) "PRUNER_MINMAX=1 PREFIX_LEN=10" /* コメントにはpruner_minmax属性が含まれており、プレフィックスの長さは10文字です。* /
  ) エンジンInnoDBコメント "COLUMNAR=1"; 
```
説明
PRUNER_MINMAXおよびPREFIX_LEN属性は、リビジョンバージョンが8.0.1.1.32以降または8.0.2.2.13以降のPolarDB for MySQL 8.0クラスターでサポートされています。
トークンbloomフィルターの作成
- サポートされているすべてのフィールドのトークンブルームフィルターを作成します。サンプルコード：
```
テーブルの作成
  t1 (
    id INT PRIMARYキー、
    str_col1 char(10) 、
    str_col2 varchar(10)
  ) エンジンInnoDBコメント "COLUMNAR=1 PRUNER_TOKEN_BLOOM=1"; 
```
- テーブル内の列のトークンブルームフィルターを作成します。サンプルコード：
```
テーブルの作成
  t1 (
    id INT PRIMARYキー、
    str_col1 char(10) "PRUNER_TOKEN_BLOOM=1" 、
    str_col2 varchar(10)
  ) エンジンInnoDBコメント "COLUMNAR=1"; 
```
説明
PRUNER_TOKEN_BLOOM属性は、次のバージョン要件のいずれかを満たすPolarDB for MySQLクラスターでサポートされています。
- リビジョンバージョンが8.0.1.1.39以降のPolarDB for MySQL 8.0.1クラスター。
- リビジョンバージョンが8.0.2.2.20以降のPolarDB for MySQL 8.0.2クラスター。
ngram bloomフィルターの作成
- サポートされているすべてのフィールドにngram bloomフィルターを作成します。サンプルコード：
```
テーブルの作成
  t1 (
    id INT PRIMARYキー、
    str_col1 char(10) 、
    str_col2 varchar(10)
  ) エンジンInnoDBコメント "COLUMNAR=1 PRUNER_NGRAM_BLOOM=2"; 
```
- テーブル内の列のngram bloomフィルターを作成します。サンプルコード：
```
テーブルの作成
  t1 (
    id INT PRIMARYキー、
    str_col1 char(10) "PRUNER_NGRAM_BLOOM=3" 、
    str_col2 varchar(10)
  ) エンジンInnoDBコメント "COLUMNAR=1"; 
```
説明
- PRUNER_TOKEN_BLOOM属性は、次のバージョン要件のいずれかを満たすPolarDB for MySQLクラスターでサポートされています。
  - リビジョンバージョンが8.0.1.1.39以降のPolarDB for MySQL 8.0.1クラスター。
  - リビジョンバージョンが8.0.2.2.20以降のPolarDB for MySQL 8.0.2クラスター。
- PRUNER_NGRAM_BLOOM=Nを設定できます。 Nが2以上の場合、それは文字列を分割するために使用される長さである。 LIKE "% string %" クエリで指定された文字列の長さ以上の値にNを設定する必要があります。それ以外の場合、ngramブルームフィルタは使用できません。

既存のテーブルでプルーナーを作成または削除する

既存のテーブルでプルーナーを作成または削除する場合は、IMCIを再作成する必要があります。したがって、IMCIを削除してからIMCIを再作成する必要があります。 IMCIを作成する前に、コメントにプルーナー属性を追加または削除する必要があります。次の例では、PRUNER_MINMAX属性を追加または削除する方法について説明します。

プルーナーの作成

テーブルは次の構造を使用します。

テーブル: t1
テーブルの作成: テーブル 't1' を作成 (
  'id' int (11) NOT NULL,
  'str_col1' char(10) DEFAULT NULL、
  'str_col2' varchar(10) DEFAULT NULL、
  主要なキー ('id') 、
  COLUMNAR INDEX ('id','str_col1','str_col2')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 '

t1テーブルのすべての文字列列にminmaxプルーナーを作成するには、次の手順を実行します。
1. 次のステートメントを実行して、t1テーブルからIMCIを削除します。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
```
2. 次のステートメントを実行して、t1テーブルのすべての文字列列に対してminmaxプルーナーを作成します。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=1 PRUNER_MINMAX=1";
```
3. (オプション) プルーナーの作成後、次のステートメントを実行してテーブル構造を表示します。
```
SHOW CREATE TABLE t1 FULL \G
```
  サンプル結果:
```
*************************** 1。 行 ***************************
       テーブル: t1
テーブルの作成: テーブル 't1' を作成 (
  'id' int (11) NOT NULL,
  'str_col1' char(10) DEFAULT NULL、
  'str_col2' varchar(10) DEFAULT NULL、
  主要なキー ('id') 、
  COLUMNAR INDEX ('id','str_col1','str_col2')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 PRUNER_MINMAX=1 ' 
```
  上記のテーブル構造は、PRUNER_MINMAX属性がt1テーブルに追加されたことを示しています。

t1テーブルのstr_col1列のminmaxプルーナーを作成するには、次の手順を実行します。

次のステートメントを実行して、str_col1列のプルーナーを作成します。
```
ALTER TABLE t1 MODIFY COLUMN str_col1 char(10) コメント 'PRUNER_MINMAX=1 ';
```
次のステートメントを順番に実行して、t1テーブルのIMCIを再作成します。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
ALTER TABLE t1 COMMENT = "COLUMNAR=1"; 
```

(オプション) 次のステートメントを実行して、IMCIの再作成後にテーブル構造を表示します。

SHOW CREATE TABLE t1 FULL \G

サンプル結果:

*************************** 1。 行 ***************************
       テーブル: t1
テーブルの作成: テーブル 't1' を作成 (
  'id' int (11) NOT NULL,
  'str_col1' char(10) DEFAULT NULL COMMENT 'PRUNER_MINMAX=1 '、
  'str_col2' varchar(10) DEFAULT NULL、
  主要なキー ('id') 、
  COLUMNAR INDEX ('id','str_col1','str_col2')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 '

上記のテーブル構造は、PRUNER_MINMAX属性がstr_col1列に追加されたことを示しています。

プルーナーの削除

テーブルからPRUNER_MINMAX属性を削除します。

たとえば、t1という名前のテーブルの構造は次のとおりです。

SHOW CREATE TABLE t1フル \G
*************************** 1。 行 ***************************
       テーブル: t1
テーブルの作成: テーブル 't1' を作成 (
  'id' int (11) NOT NULL,
  'str_col1' char(10) DEFAULT NULL、
  'str_col2' varchar(10) DEFAULT NULL、
  主要なキー ('id') 、
  COLUMNAR INDEX ('id','str_col1','str_col2')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 PRUNER_MINMAX=1 '

t1テーブルからPRUNER_MINMAX属性を削除するには、次の手順を実行します。

次のステートメントを実行して、t1テーブルからIMCIを削除します。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
```
次のステートメントを実行して、COMMENTのPRUNER_MINMAX属性を0に設定し、IMCIを再作成します。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=1 PRUNER_MINMAX=0";
```

(オプション) 次のステートメントを実行して、IMCIの再作成後にテーブル構造を表示します。

SHOW CREATE TABLE t1 FULL \G

サンプル結果:

*************************** 1。 行 ***************************
       テーブル: t1
テーブルの作成: テーブル 't1' を作成 (
  'id' int (11) NOT NULL,
  'str_col1' char(10) DEFAULT NULL、
  'str_col2' varchar(10) DEFAULT NULL、
  主要なキー ('id') 、
  COLUMNAR INDEX ('id','str_col1','str_col2')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 '

上記のテーブル構造は、PRUNER_MINMAX属性がt1テーブルから削除されたことを示しています。

列からPRUNER属性を削除します。

たとえば、PRUNER_MINMAX属性は、t1という名前のテーブルのstr_col1列にあります。テーブルは次の構造を使用します。

テーブル: t1
テーブルの作成: テーブル 't1' を作成 (
  'id' int (11) NOT NULL,
  'str_col1' char(10) DEFAULT NULL COMMENT 'PRUNER_MINMAX=1 '、
  'str_col2' varchar(10) DEFAULT NULL、
  主要なキー ('id') 、
  COLUMNAR INDEX ('id','str_col1','str_col2')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 '

str_col1列からPRUNER_MINMAX属性を削除するには、次の手順を実行します。

次のステートメントを実行して、str_col1列からPRUNER_MINMAX属性を削除します。
```
ALTER TABLE t1 MODIFY COLUMN str_col1 char(10) コメント 'PRUNER_MINMAX=0 ';
```
次のステートメントを順番に実行して、IMCIを再作成します。
```
ALTER TABLE t1 COMMENT = "COLUMNAR=0";
ALTER TABLE t1 COMMENT = "COLUMNAR=1"; 
```

(オプション) 次のステートメントを実行して、IMCIの再作成後にテーブル構造を表示します。

SHOW CREATE TABLE t1 FULL \G

サンプル結果:

*************************** 1。 行 ***************************
       テーブル: t1
テーブルの作成: テーブル 't1' を作成 (
  'id' int (11) NOT NULL,
  'str_col1' char(10) DEFAULT NULL、
  'str_col2' varchar(10) DEFAULT NULL、
  主要なキー ('id') 、
  COLUMNAR INDEX ('id','str_col1','str_col2')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 '

上記のテーブル構造は、PRUNER_MINMAX属性がstr_col1列から削除されたことを示しています。

IMCIプルーナー機能が有効かどうかを確認する

テーブルの文字列列にプルーナーが作成されているかどうかの確認

テーブル内のSTR_BLOOM_PRUNERフィールドとSTR_MINMAX_PRUNERフィールドのステータス値に基づいて、imci_secondary_indexesという名前のテーブルの文字列列にプルーナーが作成されているかどうかを確認できます。フィールドが1に設定されている場合、列のプルーナーが作成されます。例：

SELECT * FROM information_schema.imci_secondary_indexes WHERE schema_name='test_tmp' AND table_name='t1'\G

サンプル結果:

*************************** 1。 行 ***************************
         TABLE_ID: 1091
      SCHEMA_NAME: test_tmp
       タブレット名: t1
      COLUMN_NAME: str_col1
 STR_BLOOM_PRUNER: 1 -- str_col1列にブルームフィルタが作成されます。
STR_MINMAX_PRUNER: 1 -- str_col1列にMinmaxインデックスが作成されます。
    SINDEX_SWITCH: 0
*************************** 2. 行 ***************************
         TABLE_ID: 1091
      SCHEMA_NAME: test_tmp
       タブレット名: t1
      COLUMN_NAME: str_col2
 STR_BLOOM_PRUNER: 1 -- str_col2列にブルームフィルタが作成されます。
STR_MINMAX_PRUNER: 1 -- str_col2列にMinmaxインデックスが作成されます。
    SINDEX_SWITCH: 0
セットの2列 (0.00秒)

上記の結果は、STR_BLOOM_PRUNERフィールドが1に設定されていることを示しています。これは、str_col1列とstr_col2列にブルームフィルターが作成されることを示します。 STR_MINMAX_PRUNERフィールドも1に設定されます。これは、str_col1列とstr_col2列にminmaxインデックスが作成されることを示します。

IMCIプルーナー機能がSQL文で有効になるかどうかを確認する
SQLステートメントを実行する前後に、SHOW STATUS LIKE 'imci_pruner % 'ステートメントを実行して、データブロックのフィルタリングを表示し、IMCIプルーナー機能がステートメントで有効になるかどうかを確認できます。結果には、次のステータス値が含まれます。
- imci_pruner_accepted: フィルタ条件を満たすデータブロックの数。
- imci_pruner_rejected: フィルタ条件を満たさないデータブロックの数。
スキップされたデータブロックの数=受け入れられたデータブロックの数 + 拒否されたデータブロックの数
受け入れられたデータブロックは、条件によってフィルタリングされる必要はない。いくつかの列を具体化する必要がある場合、受け入れられたデータブロックは依然としてアクセスされる。拒否されたデータブロックはスキャンされない。したがって、I/O動作は実行されない。
例
次の例では、t1という名前のテーブルで実行されたSQL文に対してIMCIプルーナー機能が有効かどうかを確認する方法を説明します。 t1テーブルは、次の構造を使用します。
```
テーブル: t1
テーブルの作成: テーブル 't1' を作成 (
  'id' int (11) NOT NULL,
  'str_col1' char(10) DEFAULT NULL、
  'str_col2' varchar(10) DEFAULT NULL、
  主要なキー ('id') 、
  COLUMNAR INDEX ('id','str_col1','str_col2')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1 PRUNER=1 ' 
```
例えば、テーブルは10個のデータブロックを含み、str_col1='polardb' 条件を満たすレコードはデータブロックの1つに含まれる。次の手順を実行して、IMCIプルーナー機能がSELECT COUNT(1) FROM t1 WHERE str_col1='polardb' ステートメントで有効になるかどうかを確認します。
1. 次のステートメントを実行して、プルーナーのステータスを表示します。
```
「imci_pruner % 」のようなステータスを表示します。
```
  サンプル結果:
```
+ ---------------------- + -------
| Variable_name | 値 |
+ ---------------------- + -------
| imci_pruner_accepted | 0 |
| imci_pruner_rejected | 0 |
+ ---------------------- + -------
セットの2列 (0.00秒) 
```
2. 次のステートメントを実行して、str_col1='polardb' 条件を満たすレコードの数を表示します。
```
SELECT COUNT(1) FROM t1 WHERE str_col1='polardb';
```
  サンプル結果:
```
+ ---------
| count(1) |
+ ---------
| 1 |
+ ---------
1行セット (0.01秒) 
```
3. 次のステートメントを実行して、プルーナーのステータスを再度表示します。
```
「imci_pruner % 」のようなステータスを表示します。
```
  サンプル結果:
```
+ ---------------------- + -------
| Variable_name | 値 |
+ ---------------------- + -------
| imci_pruner_accepted | 0 |
| imci_pruner_rejected | 9 |
+ ---------------------- + -------
セットの2列 (0.00秒) 
```
  結果は、imci_pruner_accepted値が0であり、imci_pruner_rejected値が9であることを示す。クエリ中に9つのデータブロックがスキップされます。 IMCIプルーナー機能は、クエリに対して有効になります。

性能テスト

このテストでは、120万行のデータと約1,800のデータブロックを含むテーブルと、2コアと4 GBのメモリを持つクラスターを使用して、テーブルのcol列のブルームフィルターを作成する前後のパフォーマンスを比較します。 string型のcol列の個別値は80百万です。実行するステートメントは、次のとおりです。

SELECT COUNT(1) FROM t1 WHERE col='xxx'

次の表に、クエリ時間を示します。

ブルームフィルターが作成されました	ブルームフィルターが作成されません
0.15s	18.6s

ステートメント内のcol='xxx' の条件とブルームフィルタは、ほとんどのデータブロックがスキャンされないようにします。実際のクエリプロセスでは少数のデータブロックのみがスキャンされ、クエリのパフォーマンスが向上します。