生成された列

このドキュメントでは、生成された列の概念と使用法を紹介します。

基本概念

一般的な列とは異なり、生成された列の値は、列定義の式によって計算されます。生成された列を挿入または更新する場合、値を割り当てることはできず、 DEFAULTのみを使用してください。

生成される列には、仮想列と格納列の 2 種類があります。仮想生成列はストレージを占有せず、読み取り時に計算されます。格納された生成列は、書き込み時 (挿入または更新時) に計算され、ストレージを占有します。仮想生成列と比較して、格納された生成列は読み取りパフォーマンスが優れていますが、より多くのディスク領域を占有します。

仮想列か格納列かに関係なく、生成された列に索引を作成できます。

使用法

生成された列の主な用途の 1 つは、JSON データ型からデータを抽出し、データのインデックスを作成することです。

MySQL 5.7と TiDB の両方で、JSON 型の列に直接インデックスを作成することはできません。つまり、次のテーブル スキーマはサポートされていません

CREATE TABLE person ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, address_info JSON, KEY (address_info) );

JSON 列にインデックスを付けるには、最初にそれを生成された列として抽出する必要があります。

例としてaddress_infocityフィールドを使用すると、仮想生成列を作成し、それにインデックスを追加できます。

CREATE TABLE person ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, address_info JSON, city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))), -- virtual generated column -- city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))) VIRTUAL, -- virtual generated column -- city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))) STORED, -- stored generated column KEY (city) );

このテーブルでは、 city列は仮想生成列であり、インデックスがあります。次のクエリでは、インデックスを使用して実行を高速化できます。

SELECT name, id FROM person WHERE city = 'Beijing';
EXPLAIN SELECT name, id FROM person WHERE city = 'Beijing';
+---------------------------------+---------+-----------+--------------------------------+-------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+---------+-----------+--------------------------------+-------------------------------------------------------------+ | Projection_4 | 10.00 | root | | test.person.name, test.person.id | | └─IndexLookUp_10 | 10.00 | root | | | | ├─IndexRangeScan_8(Build) | 10.00 | cop[tikv] | table:person, index:city(city) | range:["Beijing","Beijing"], keep order:false, stats:pseudo | | └─TableRowIDScan_9(Probe) | 10.00 | cop[tikv] | table:person | keep order:false, stats:pseudo | +---------------------------------+---------+-----------+--------------------------------+-------------------------------------------------------------+

クエリ実行プランから、 cityインデックスを使用して条件city ='Beijing'を満たす行のHANDLEを読み取り、次にこのHANDLEを使用して行のデータを読み取ることがわかります。

パス$.cityにデータが存在しない場合、 JSON_EXTRACTNULLを返します。 cityNOT NULLでなければならないという制約を適用する場合は、仮想生成列を次のように定義できます。

CREATE TABLE person ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, address_info JSON, city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))) NOT NULL, KEY (city) );

生成された列の検証

INSERTステートメントとUPDATEステートメントの両方で、仮想列の定義がチェックされます。検証に合格しない行はエラーを返します。

mysql> INSERT INTO person (name, address_info) VALUES ('Morgan', JSON_OBJECT('Country', 'Canada')); ERROR 1048 (23000): Column 'city' cannot be null

生成された列のインデックス置換規則

クエリ内の式がインデックス付きの生成された列と同等である場合、TiDB は式を対応する生成された列に置き換えます。これにより、オプティマイザは実行計画の構築中にそのインデックスを考慮に入れることができます。

たとえば、次の例では、式a+1の生成列を作成し、インデックスを追加します。

create table t(a int); desc select a+1 from t where a+1=3; +---------------------------+----------+-----------+---------------+--------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+----------+-----------+---------------+--------------------------------+ | Projection_4 | 8000.00 | root | | plus(test.t.a, 1)->Column#3 | | └─TableReader_7 | 8000.00 | root | | data:Selection_6 | | └─Selection_6 | 8000.00 | cop[tikv] | | eq(plus(test.t.a, 1), 3) | | └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo | +---------------------------+----------+-----------+---------------+--------------------------------+ 4 rows in set (0.00 sec) alter table t add column b bigint as (a+1) virtual; alter table t add index idx_b(b); desc select a+1 from t where a+1=3; +------------------------+---------+-----------+-------------------------+---------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------+---------+-----------+-------------------------+---------------------------------------------+ | IndexReader_6 | 10.00 | root | | index:IndexRangeScan_5 | | └─IndexRangeScan_5 | 10.00 | cop[tikv] | table:t, index:idx_b(b) | range:[3,3], keep order:false, stats:pseudo | +------------------------+---------+-----------+-------------------------+---------------------------------------------+ 2 rows in set (0.01 sec)

ノート:

式の型と生成された列の型が厳密に等しい場合にのみ、置換が実行されます。

上記の例では、 aの列の型は int であり、 a+1の列の型は bigint です。生成された列の型が int に設定されている場合、置換は行われません。

型変換規則については、 式評価の型変換を参照してください。

制限事項

JSON および生成された列の現在の制限は次のとおりです。

  • 保存された生成列をALTER TABLEから追加することはできません。
  • ALTER TABLEステートメントを使用して格納された生成列を通常の列に変換することも、通常の列を格納された生成列に変換することもできません。
  • ALTER TABLEステートメントを使用して、格納された生成列の式を変更することはできません。
  • JSON関数のすべてがサポートされているわけではありません。
  • 現在、生成列インデックス置換ルールは、生成列が仮想生成列の場合にのみ有効です。格納された生成列では無効ですが、生成列自体を直接使用してインデックスを使用することはできます。
Playground
新規
登録なしで TiDB の機能をワンストップでインタラクティブに体験できます。
製品
TiDB Cloud
TiDB
価格
PoC お問い合わせ
エコシステム
TiKV
TiFlash
OSS Insight
© 2024 PingCAP. All Rights Reserved.
Privacy Policy.