すべてのプロダクト
Search

このプロダクト

    PolarDB:生成された列

    ドキュメントセンター

    PolarDB:生成された列

    最終更新日:Jun 04, 2024

    生成された列は、式の計算結果を表す値を含む列です。 このトピックでは、生成列の作成方法と生成列のインデックスの作成方法について説明します。

    生成された列の作成

    構文 

    col_name data_type [常に生成] AS (expr)
  [VIRTUAL | STORED | LOGICAL] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [コメント '文字列']

    次のタイプの生成された列を作成できます。

    • VITRUAL: 生成された列の値は格納されず、ストレージ領域を占有しません。 値は、列が読み取られるたびにデータノードによって計算されます。

      説明

      型を指定しない場合、VITRUAL型の生成された列がデフォルトで作成されます。

    • STORED: 生成された列の値は、データ行が挿入または更新されたときにデータノードによって計算されます。 結果はデータノードに格納され、ストレージスペースを占有します。

    • LOGICAL: STORED型と同様に、生成された列の値は、データ行が挿入または更新されたときに計算されます。 ただし、値は計算ノードによって計算され、データノードに共通列として格納されます。 このタイプの生成された列は、パーティションキーとして使用できます。

    の考慮事項

    PolarDB-X Enterprise Edition V5.4.17以降のインスタンスのみがこの機能をサポートしています。

    MySQLの列と同様に、生成される列には通常の列と比較して次の制限があります。

    • MySQLと同じ制限

      • 生成された列にデフォルト値を指定することはできません。

      • 生成された列のAUTO_INCREMENT属性を設定したり、生成された列のAUTO_INCREMENT属性を含む列を参照したりすることはできません。

      • 生成された列の式では、UUID() 、CONNECTION_ID() 、NOW() などの決定論的関数を使用できません。

      • 生成された列の式で変数を使用することはできません。

      • 生成された列の式でサブクエリを使用することはできません。

      • INSERTおよびUPDATEステートメントでは、生成された列の値を明示的に指定できません。 生成された列の値は、データベースによってのみ自動的に計算できます。

    • MySQLの制限とは異なる制限

      • コールドデータアーカイブが有効になっているテーブルには、生成された列を追加できません。

      • 生成されたVIRTUAL型またはSTORED型の列は、パーティションキー、プライマリキー、または一意キーとして使用できません。

      • VIRTUAL型またはstored型の生成された列では、ストアド関数を参照できません。

      • グローバルセカンダリインデックスにVIRTUAL型またはSTORED型の生成された列が含まれている場合、グローバルセカンダリインデックスには、生成された列の式で参照されるすべての列も含める必要があります。

      • LOGICAL型の生成された列の式では、VIRTUAL型またはSTORED型の生成された列を参照することはできません。

      • LOGICAL型の生成された列の型と、生成された列の式で参照される列の型を変更することはできません。

      • LOGICAL型の生成された列と、その式で参照される列に使用できるのは、次の型のみです。

        • 整数型 (BIGINT、INT、MEDUMINT、SMALLINT、TINYINT);

        • 日付タイプ (DATETIME、Date、およびTIMESTAMP) 。 ON UPDATE CURRENT_TIMESTAMP属性を含む日付タイプの列はサポートされていません。

        • 文字列型 (CHARおよびVARCHAR) 。

    例: VIRTUALまたはSTORED型の生成された列

    例 1

    生成された列を使用して、直角三角形の斜辺の長さを自動的に計算します (斜辺の長さは、2つの直角辺の二乗の合計の平方根に等しくなります) 。 

    テーブルの三角形を作成 (
  sidea DOUBLE,
  sidebDOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
INSERT INTOトライアングル (sidea, sideb) 値 (1,1) 、(3,4) 、(6,8);

    hive_hbase_tableテーブルにデータを挿入します。 

    INSERT INTO三角形 (sidea, sideb) 値 (1,1) 、(3,4) 、(6,8);
三角形から * を選択します。+ ------ ------- + -------------------- +
| sidea | sideb | sidec |
+ ------ ------- + -------------------- +
| 1.0 | 1.0 | 1.4142135623730951 |
| 3.0 | 4.0 | 5.0 |
| 6.0 | 8.0 | 10.0 |
+ ------ ------- + -------------------- +

    例 2

    生成された列bを含むパーティションテーブルt1を作成します。 

    テーブル 't1' の作成 (
'a' int (11) NOT NULL,
'b' int(11) 常に ('a' + 1) として生成、主要なキー ('a')
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4ハッシュによるdbpartition ('a')

    hive_hbase_tableテーブルにデータを挿入します。 

    t1(a) 値に挿入 (1);
SELECT * からt1;
+ --- + --- +
| a | b |
+ --- + --- +
| 1 | 2 |
+ --- + --- +

    例: LOGICAL型の生成された列

    LOGICAL型の生成された列をパーティションキーとして使用して、より柔軟なパーティション分割戦略を実装できます。 LOGICAL型の生成された列でユーザー定義関数を参照することもできます。

    例1: 文字列フィールドの最後の2文字をパーティションキーとして選択します。 

    テーブル 't2' の作成 (
'a' int (11) NOT NULL,
'b' varchar (32) DEFAULT NULL、'c' varchar(2) 常に生成された (SUBSTR('b' 、-2)) 論理、主要なキー ('a') 、キー 'auto_shard_key_c '使用してBTREE ('c')
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4ハッシュによるdbpartition ('c')

    例2: ユーザー定義関数を参照するLOGICAL型の生成された列を作成します。

    ユーザー定義関数my_absを作成します。 

    DELIMITER &&
関数my_absの作成 (
INT
)
リターンINT
開始
IF a < 0 THEN
リターン-a;
エルセ
リターンa;
エンドIF;
エンド&&
DELIMITER ;

    b. 生成された列を含むテーブルt3を作成する。 列の式には、ユーザー定義関数my_absが含まれます。 

    テーブル 't3' の作成 (
'a' int (11) NOT NULL,
'b' int(11) 常に生成された (MY_ABS('a')) ローカル、主要なキー ('a')
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4ハッシュによるdbpartition ('b');

t3 (a) 値 (1) 、(-1) に挿入する。EXPLAIN SELECT * FROM t3 b = 1;
+ ----------------------------------------------------------------------------------------------------------- +
| ローカル実行 |
+ ----------------------------------------------------------------------------------------------------------- +
| LogicalView(tables="TEST_000002_GROUP.t3_WHHZ", sql="SELECT 'a', 'b' FROM 't3' WHERE ('b' = ?)") |
+ ----------------------------------------------------------------------------------------------------------- +

SELECT * FROM t3 WHERE b = 1;
+ ---- + ------
| a | b |
+ ---- + ------
| -1 | 1 |
| 1 | 1 |
+ ---- + ------ +

    生成された列にインデックスを作成する

    PolarDB-Xは、生成された列でインデックスの作成をサポートします。

    の考慮事項

    • PolarDB-X Enterprise Edition V5.4.17以降のインスタンスのみがこの機能をサポートしています。

    • 生成されたすべてのタイプの列にローカルインデックスを作成できます。

    • LOGICALタイプの生成された列にグローバルインデックスを作成できます。

    • 生成されたVIRTUAL型およびSTORED型の列にグローバルインデックスを作成することはできません。

    例

    例1: VIRTUAL型またはSTORED型の生成された列にローカルインデックスを作成します。 

    テーブルt4の作成 (
    BIGINT NOT NULL AUTO_INCREMENT PRIMARYキー、
    c JSON,
    g INT AS (c->"$.id") バーチャル
) ハッシュによるDBPARTITION (a);

CREATE INDEX 'i' ON 't4'('g');

t4 (c) 値に挿入する
  ('{"id": "1", "name": "Fred"}') 、
  ('{"id": "2", "name": "Wilma"}') 、
  ('{"id": "3", "name": "Barney"}') 、
  ('{"id": "4", "name": "Betty"}');

EXPLAIN EXECUTE SELECT c->>"$.name" AS name FROM t4 WHERE g > 2;
---------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t4 | NULL | range | i | i | 5 | NULL | 1 | 100 | Using where |
--------------------------------------------------------------------------------------------------------------------------------

    例2: LOGICAL型の生成された列にローカルインデックスを作成します。 

    作成テーブルt5 (
    BIGINT NOT NULL AUTO_INCREMENT PRIMARYキー、
    c varchar(32) 、
    g char(2) AS (substr('c', 2)) LOGICAL
) ハッシュによるDBPARTITION (a);

CREATE INDEX 'i' ON 't5'('g');

t5 (c) 値に挿入する
  ('1111') 、
  ('1112') 、
  ('1211') 、
  ('1311');

EXPLAIN EXECUTE SELECT c AS name FROM t5 WHERE g = '11';
------ ----------- ----------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------ ----------- ----------------------------------------------------------------------------------
| 1 | SIMPLE | t5 | NULL | ref | i | 8 | NULL | 4 | 100.00 | XPlanの使用 |
------ ----------- ------------------------------------------------------- ------------------------------...

    例3: LOGICAL型の生成された列にグローバルインデックスを作成します。 

    テーブルを作成t6 (
    BIGINT NOT NULL AUTO_INCREMENT PRIMARYキー、
    c varchar(32) 、
    g char(2) AS (substr('c', 2)) LOGICAL
) ハッシュによるDBPARTITION (a);

グローバルインデックス 'g_i' ON 't6'('g') COVERING('c') DBPARTITION BY HASH('g') を作成します。t6 (c) 値に挿入する
  ('1111') 、
  ('1112') 、
  ('1211') 、
  ('1311');

EXPLAIN SELECT c AS name FROM t6 WHERE g = '11';
+ --------------------------------------------------------------------------------------------------------------------- +
| ローカル実行 |
+ --------------------------------------------------------------------------------------------------------------------- +
| IndexScan(tables="TEST_DRDS_000000_GROUP.g_i_J1MT", sql="SELECT 'c' AS 'name' FROM 'g_i' AS 'g_i' WHERE ('g' = ?)") |
+ --------------------------------------------------------------------------------------------------------------------- +

    式インデックス

    PolarDB-Xがインデックスを作成しているときに、インデックスエントリが列ではなく式である場合、PolarDB-Xはその式をVIRTUAL型の生成された列に自動的に変換し、その列をテーブルに追加します。 すべてのインデックスエントリが処理された後、PolarDB-Xは定義に基づいてインデックスを作成し続けます。 インデックス定義の式は、対応する生成された列に置き換えられます。

    の考慮事項

    • PolarDB-X Enterprise Edition V5.4.17以降のインスタンスのみがこの機能をサポートしています。

    • デフォルトでは、式インデックス機能は無効になっています。 この機能を有効にするには、スイッチENABLE_CREATE_EXPRESSION_INDEXをオンにします。 

      セットグローバルENABLE_CREATE_EXPRESSION_INDEX=TRUE;

    • グローバルインデックスはサポートされていません。

    • 一意のインデックスはサポートされていません。

    • テーブル作成ステートメントを実行して式インデックスを作成することはできません。 テーブルの作成後、ALTER tableまたはCREATE INDEXステートメントを実行して、式インデックスを作成できます。

    • 既定では、DROP INDEXステートメントを使用して式インデックスを削除すると、式インデックスの作成時に自動的に作成される生成された列は削除されません。 生成された列を手動で削除するには、ALTER TABLEステートメントをDRDSまたはAUTOモードで実行する必要があります。 詳細については、ALTER TABLE (DRDSモード) またはALTER TABLE (AUTOモード) 操作をご参照ください。

    例

    例1: 式インデックスを作成します。

    1. テーブルt7を作成します。 

      テーブルt7の作成 (
    BIGINT NOT NULL AUTO_INCREMENT PRIMARYキー、
    c varchar (32)
) ハッシュによるDBPARTITION (a);

    2. 式インデックスiを作成します。 

      CREATE INDEX 'i' ON 't7' (substream ('c', 2));

    3. 式インデックスが作成されると、テーブルは次の構造になります。 

      テーブル 't7' の作成 (
'a' bigint (20) NOT NULL AUTO_INCREMENT BY GROUP,
'c' varchar(32) DEFAULT NULL、'i$0 'varchar(32) 常に生成された (subr ('c', 2)) 仮想、主要なキー ('a') 、キー 'i' ('i$0 ')
) ENGINE=ハッシュ ('a')
      によるInnoDB dbpartition

    インデックスiのインデックスエントリは式であるため、生成された列i$0がテーブルに追加されます。 この生成された列の式は、インデックスエントリの式と同じである。 その後、インデックスiが作成される。 インデックスエントリは、対応する生成された列に置き換えられます。

    式インデックスを作成すると、次のSQL文のパフォーマンスが向上します。 

    EXPLAIN EXECUTE SELECT * FROM t7 WHERE substr('c', 2) = '11';
------ ----------- -----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------ ----------- -----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t7 | NULL | ref | i | 131 | const | 1 | 100 | NULL |
-----------------------------------------------------------------------------------------------------------------------

    EXPLAIN Executeステートメントを実行して、データノードの実行計画を取得します。 結果は、インデックスiが選択されたことを示します。 

    EXPLAIN EXECUTE SELECT * FROM t7 WHERE substr('c', 2) = '11';
------ ----------- -----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------ ----------- -----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t7 | NULL | ref | i | 131 | const | 1 | 100 | NULL |
-----------------------------------------------------------------------------------------------------------------------

    例2: インデックスで複数の式を使用します。

    テーブルt8を作成します。

    インデックスidxを作成t8で (
  a + 1, 
  b, 
  SUBSTR(c, 2)
);

    式インデックスが作成されると、テーブルは次の構造になります。

    テーブル 't8' の作成 (
'a' int (11) NOT NULL,
'b' int(11) DEFAULT NULL、'c' varchar(32) DEFAULT NULL、'idx$0 'bigint(20) 常に ('a' 1) 仮想として生成、'idx$2 'varchar(32) 常に生成された (subr ('c' 、2)) 仮想、主要なキー ('a') 、KEY 'idx' ('idx$0 '、'b' 、'idx$2')
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4ハッシュによるdbpartition ('a')

    index idxの場合、第1および第3のインデックスエントリは式である。 したがって、2つの生成された列idx$0およびidx$2がテーブルに追加されます。 これらの2つの生成された列の式は、第1および第3のインデックスエントリの式と同じである。 その後、index idxが作成されます。 第1および第3のインデックスエントリは、対応する生成された列で置き換えられる。

    式インデックスを作成すると、次のSQL文のパフォーマンスが向上します。 

    SELECT * FROM t8 WHERE a + 1=10 AND b=20 AND SUBSTR(c,2)='ab';

    EXPLAIN Executeステートメントを実行して、データノードの実行計画を取得します。 結果は、インデックスidxが選択されていることを示します。 

    EXPLAIN EXECUTE SELECT * からt4 WHERE a + 1=10およびb=20およびSUBSTR(c、2)='ab';
-------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t8 | NULL | ref | idx | idx | 145 | const、const、const | 1 | 100 | NULL |
--------------------------------------------------------------------------------------------------------------------------------