テーブルを作成する

このドキュメントでは、SQL文を使用してテーブルを作成する方法と、関連するベストプラクティスを紹介します。ベストプラクティスを説明するために、TiDBベースの書店アプリケーションの例を示します。

始める前に

このドキュメントを読む前に、次のタスクが完了していることを確認してください。

テーブルとは何か

テーブル TiDB クラスター内のデータベースに従属する論理オブジェクトです。SQL 文から送信されたデータを格納するために使用されます。テーブルは行と列の形式でデータレコードを保存します。テーブルには少なくとも 1 つの列が含まれます。5 n列を定義した場合、各データ行にはn列と全く同じフィールドが含まれます。

テーブルに名前を付ける

テーブルを作成する最初のステップは、テーブルに名前を付けることです。将来的に自分自身や同僚に大きな迷惑をかけるような、意味のない名前は使用しないでください。会社または組織のテーブル命名規則に従うことをお勧めします。

CREATE TABLEステートメントは通常、次の形式になります。

CREATE TABLE {table_name} ( {elements} );

パラメータの説明

{table_name} : 作成するテーブルの名前。
{elements} : 列定義や主キー定義などのテーブル要素のコンマ区切りリスト。

bookshopデータベースにユーザー情報を格納するためのテーブルを作成する必要があるとします。

まだ列が 1 つも追加されていないため、次の SQL ステートメントを実行できないことに注意してください。

CREATE TABLE `bookshop`.`users` (
);

列を定義する

列はテーブルに従属します。各テーブルには少なくとも1つの列が含まれます。列は、各行の値を単一のデータ型の小さなセルに分割することで、テーブルに構造を提供します。

カラム定義は通常、次の形式になります。

{column_name} {data_type} {column_qualification}

パラメータの説明

{column_name} : 列名。
{data_type} : 列データ型。
{column_qualification} :列レベルの制約や生成された列句などのカラム修飾。

usersテーブルに、一意の識別子id 、 balance 、 nicknameなどのいくつかの列を追加できます。

CREATE TABLE `bookshop`.`users` (
  `id` bigint,
  `nickname` varchar(100),
  `balance` decimal(15,2)
);

上記の文では、名前がid 、型がビッグインテントフィールドが定義されています。これは、一意のユーザー識別子を表すために使用されます。つまり、すべてのユーザー識別子はbigint型である必要があります。

次に、 nicknameというフィールドが定義されます。これはvarchar型で、長さ制限は100文字です。つまり、ユーザーのうちnicknamesはvarchar型を使用し、100文字を超えないことを意味します。

最後に、 balanceという名前のフィールドが追加されます。これは小数点型で、精度は15 、スケールは2です。精度はフィールド内の桁数を表し、スケールは小数点以下の桁数を表します。たとえば、 decimal(5,2)精度が5 、スケールが2で、範囲は-999.99から999.99です。 decimal(6,1)精度が6 、スケールが1で、範囲は-99999.9から99999.9です。小数点は固定小数点型で、数値を正確に格納するために使用できます。正確な数値が必要なシナリオ（ユーザープロパティ関連など）では、必ず小数点型を使用してください。

TiDBは、整数型、浮動小数点型、固定小数点型、日付と時刻の型、列挙型など、他の多くの列データ型をサポートしています。サポートされている列データ型を参照し、データベースに保存するデータに一致するデータ型を使用できます。

もう少し複雑にするには、 bookshopデータの中核となるbooksテーブルを定義します。5番目booksテーブルには、書籍のID、タイトル、種類（例：雑誌、小説、生活、芸術）、在庫、価格、出版日などのフィールドが含まれます。

CREATE TABLE `bookshop`.`books` (
  `id` bigint NOT NULL,
  `title` varchar(100),
  `type` enum('Magazine', 'Novel', 'Life', 'Arts', 'Comics', 'Education & Reference', 'Humanities & Social Sciences', 'Science & Technology', 'Kids', 'Sports'),
  `published_at` datetime,
  `stock` int,
  `price` decimal(15,2)
);

このテーブルには、 usersテーブルよりも多くのデータ型が含まれています。

整数 : ディスクの使用量が増えたり、パフォーマンスに影響したり (型の範囲が大きすぎる)、データのオーバーフロー (データ型の範囲が小さすぎる) しないように、適切なサイズの型を使用することをお勧めします。
日時 : datetime型は時刻値を保存するために使用できます。
列挙型 : 列挙型は、限られた値の選択を格納するために使用できます。

主キーを選択

主キーテーブル内の列または列セットであり、その値によってテーブル内の行が一意に識別されます。

注記：
TiDB の主キーのデフォルト定義は、インノDB (MySQL の共通storageエンジン) のものと異なります。
InnoDBの場合:主キーは一意であり、null ではなく、インデックスがクラスター化されます。
TiDBの場合：主キーは一意であり、NULLではありません。ただし、主キーがクラスター化インデックスであるとは限りません。代わりに、別NONCLUSTEREDキーワードセットCLUSTEREDによって、主キーが**クラスター化インデックス**であるかどうかが制御されます。このキーワードが指定されていない場合は、クラスター化インデックスで説明されているように、システム変数@@global.tidb_enable_clustered_indexによって制御されます。

主キーはCREATE TABLEステートメントで定義されます。5 主キー制約ステートメントでは、制約されたすべての列にNULL以外の値のみが含まれることが要求されます。

テーブルは主キーなしで、または整数以外の主キーで作成できます。この場合、TiDBは暗黙的な主キーとして_tidb_rowid作成します。暗黙的な主キー_tidb_rowid単調に増加するため、書き込み集中型のシナリオでは書き込みホットスポットが発生する可能性があります。したがって、アプリケーションが書き込み集中型の場合は、 SHARD_ROW_ID_BITSとPRE_SPLIT_REGIONSパラメータを使用してデータをシャーディングすることを検討してください。ただし、これはリードアンプリフィケーションにつながる可能性があるため、ご自身でトレードオフを検討する必要があります。

テーブルの主キーが整数型で、かつAUTO_INCREMENT使用されている場合、 SHARD_ROW_ID_BITSを使用してもホットスポットを回避することはできません。ホットスポットを回避する必要があり、連続した増分主キーを必要としない場合は、 AUTO_INCREMENTではなくAUTO_RANDOM使用して行IDの連続性を排除できます。

ホットスポットの問題の処理方法の詳細については、ホットスポットの問題のトラブルシューティングを参照してください。

主キーの選択に関するガイドラインに続いて、次の例はusersテーブルでAUTO_RANDOM主キーがどのように定義されるかを示しています。

CREATE TABLE `bookshop`.`users` (
  `id` bigint AUTO_RANDOM,
  `balance` decimal(15,2),
  `nickname` varchar(100),
  PRIMARY KEY (`id`)
);

クラスター化されているかどうか

TiDBはバージョン5.0以降、クラスター化インデックス機能をサポートしています。この機能は、主キーを含むテーブルへのデータの格納方法を制御します。これにより、TiDBは特定のクエリのパフォーマンスを向上させる方法でテーブルを整理できるようになります。

ここでの「クラスター化」という用語は、データの格納方法の構成を指し、連携して動作するデータベースサーバーのグループを指すものではありません。一部のデータベース管理システムでは、クラスター化インデックステーブルをインデックス構成テーブル（IOT）と呼びます。

現在、TiDB の主キーを含むテーブルは次の 2 つのカテゴリに分類されます。

NONCLUSTERED : テーブルの主キーは非クラスター化インデックスです。非クラスター化インデックスを持つテーブルでは、行データのキーはTiDBによって暗黙的に割り当てられた内部_tidb_rowidで構成されます。主キーは本質的に一意のインデックスであるため、非クラスター化インデックスを持つテーブルでは、行を格納するために少なくとも2つのキーと値のペアが必要です。これらのペアは次のとおりです。
- _tidb_rowid (キー) - 行データ (値)
- 主キーデータ（キー） - _tidb_rowid （値）
CLUSTERED : テーブルの主キーはクラスター化インデックスです。クラスター化インデックスを持つテーブルでは、行データのキーはユーザーが指定した主キーデータで構成されます。したがって、クラスター化インデックスを持つテーブルでは、行を格納するために必要なキーと値のペアは1つだけです。これは次のようになります。
- 主キーデータ（キー） - 行データ（値）

主キーを選択で説明したように、クラスター化インデックスは TiDB ではキーワードCLUSTEREDとNONCLUSTERED使用して制御されます。

注記：
TiDB は、テーブルのPRIMARY KEYによるクラスタリングのみをサポートします。クラスター化インデックスが有効になっている場合、「 PRIMARY KEYと「クラスター化インデックス」は同じ意味で使用できます。 PRIMARY KEYは制約（論理プロパティ）を指し、「クラスター化インデックス」はデータの格納方法の物理的な実装を表します。

次の例では、クラスター化インデックスの選択に関するガイドラインに続いて、 booksとusers （ book × usersのratings表す）を関連付けたテーブルを作成します。この例では、テーブルを作成し、 book_idとuser_id使用して複合主キーを構築し、その主キーにクラスター化インデックスを作成します。

CREATE TABLE `bookshop`.`ratings` (
  `book_id` bigint,
  `user_id` bigint,
  `score` tinyint,
  `rated_at` datetime,
  PRIMARY KEY (`book_id`,`user_id`) CLUSTERED
);

列制約を追加する

TiDBは、主キー制約に加えて、 NULLではない制約、ユニークキー制約、 DEFAULTなどの他の列制約もサポートしています。完全な制約については、 TiDB制約ドキュメントを参照してください。

デフォルト値を設定する

列にデフォルト値を設定するには、 DEFAULT制約を使用します。デフォルト値を使用すると、各列に値を指定せずにデータを挿入できます。

DEFAULTとサポートされているSQL関数組み合わせることで、デフォルト値の計算をアプリケーションレイヤーから移動し、アプリケーションレイヤーのリソースを節約できます。計算に消費されたリソースは消滅せず、TiDBクラスターに移動されます。通常、デフォルトの時刻でデータを挿入できます。以下は、 ratingsテーブルでデフォルト値を設定する例です。

CREATE TABLE `bookshop`.`ratings` (
  `book_id` bigint,
  `user_id` bigint,
  `score` tinyint,
  `rated_at` datetime DEFAULT NOW(),
  PRIMARY KEY (`book_id`,`user_id`) CLUSTERED
);

さらに、データの更新時に現在の時刻もデフォルトで入力される場合は、次のステートメントを使用できます (ただし、 ON UPDATE後には現在の時刻に関連する式のみを入力できます)。

CREATE TABLE `bookshop`.`ratings` (
  `book_id` bigint,
  `user_id` bigint,
  `score` tinyint,
  `rated_at` datetime DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (`book_id`,`user_id`) CLUSTERED
);

さまざまなデータ型のデフォルト値の詳細については、デフォルト値参照してください。

重複した値を防ぐ

列内の値の重複を防ぐ必要がある場合は、 UNIQUE制約を使用できます。

たとえば、ユーザーのニックネームが一意であることを確認するには、 usersテーブルのテーブル作成 SQL ステートメントを次のように書き換えます。

CREATE TABLE `bookshop`.`users` (
  `id` bigint AUTO_RANDOM,
  `balance` decimal(15,2),
  `nickname` varchar(100) UNIQUE,
  PRIMARY KEY (`id`)
);

同じnickname usersテーブルに挿入しようとすると、エラーが返されます。

NULL値を防ぐ

列内の NULL 値を防ぐ必要がある場合は、 NOT NULL制約を使用できます。

ユーザーのニックネームを例に挙げてみましょう。ニックネームが一意であるだけでなく、nullではないことを保証するには、 usersのテーブルを作成するSQL文を次のように書き換えます。

CREATE TABLE `bookshop`.`users` (
  `id` bigint AUTO_RANDOM,
  `balance` decimal(15,2),
  `nickname` varchar(100) UNIQUE NOT NULL,
  PRIMARY KEY (`id`)
);

HTAP機能を使用する

注記：
このガイドに記載されている手順は、テスト環境での迅速な開始のみを目的としています。本番環境では、 HTAPを探索するを参照してください。

例えば、 bookshopアプリケーションを使用してratingsテーブルでOLAP分析を実行し、ある書籍の評価と評価時刻に有意な相関関係があるかどうかを照会するとします。これは、ユーザーによる書籍の評価が客観的かどうかを分析するためです。この場合、 ratingsテーブル全体のscoreフィールドとrated_atフィールドを照会する必要があります。この操作は、OLTP専用データベースでは多くのリソースを消費します。あるいは、ETLなどのデータ同期ツールを使用して、OLTPデータベースから専用のOLAPデータベースにデータをエクスポートし、分析に利用することもできます。

このシナリオでは、OLTP と OLAP の両方のシナリオをサポートするHTAP (ハイブリッドトランザクションおよび分析処理)データベースである TiDB が理想的なワンストップデータベースソリューションです。

列ベースのデータを複製する

現在、TiDBはTiFlashとTiSparkという2つのデータ分析エンジンをサポートしています。大規模データシナリオ（100 TB）の場合、HTAPの主要ソリューションとしてTiFlash MPPを推奨し、補完ソリューションとしてTiSparkを推奨します。

TiDB HTAP機能の詳細については、次のドキュメントを参照してください: TiDB HTAPクイックスタートガイドおよびHTAPを探索する。

この例では、 bookshopデータベースのデータ分析エンジンとしてTiFlash選択されています。

TiFlashはデプロイメント後にデータを自動的に複製しません。そのため、複製するテーブルを手動で指定する必要があります。

ALTER TABLE {table_name} SET TIFLASH REPLICA {count};

パラメータの説明

{table_name} : テーブル名。
{count} : 複製されたレプリカの数。0の場合、複製されたレプリカは削除されます。

TiFlashはテーブルを複製します。クエリが実行されると、TiDBはコスト最適化に基づいて、クエリに対してTiKV（行ベース）またはTiFlash （列ベース）を自動的に選択します。また、クエリでTiFlashレプリカを使用するかどうかを手動で指定することもできます。指定方法については、 TiDBを使用してTiFlashレプリカを読み取るを参照してください。

HTAP機能の使用例

ratingsテーブルはTiFlashの1レプリカを開きます。

ALTER TABLE `bookshop`.`ratings` SET TIFLASH REPLICA 1;

注記：
クラスターにTiFlashノードが含まれていない場合、この SQL 文はエラー1105 - the tiflash replica count: 1 should be less than the total tiflash server count: 0報告します。5 {{{ .starter }}}クラスタを構築する使用すると、 TiFlashを含む {{{ .starter }}} クラスターを作成できます。

次に、次のクエリを実行できます。

SELECT HOUR(`rated_at`), AVG(`score`) FROM `bookshop`.`ratings` GROUP BY HOUR(`rated_at`);

EXPLAIN ANALYZEステートメントを実行して、このステートメントがTiFlashを使用しているかどうかを確認することもできます。

EXPLAIN ANALYZE SELECT HOUR(`rated_at`), AVG(`score`) FROM `bookshop`.`ratings` GROUP BY HOUR(`rated_at`);

実行結果:

+-----------------------------+-----------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                          | estRows   | actRows | task         | access object | execution info                                                                                                                                                                                                                                                                                                                                                       | operator info                                                                                                                                  | memory   | disk |
+-----------------------------+-----------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| Projection_4                | 299821.99 | 24      | root         |               | time:60.8ms, loops:6, Concurrency:5                                                                                                                                                                                                                                                                                                                                  | hour(cast(bookshop.ratings.rated_at, time))->Column#6, Column#5                                                                                | 17.7 KB  | N/A  |
| └─HashAgg_5                 | 299821.99 | 24      | root         |               | time:60.7ms, loops:6, partial_worker:{wall_time:60.660079ms, concurrency:5, task_num:293, tot_wait:262.536669ms, tot_exec:40.171833ms, tot_time:302.827753ms, max:60.636886ms, p95:60.636886ms}, final_worker:{wall_time:60.701437ms, concurrency:5, task_num:25, tot_wait:303.114278ms, tot_exec:176.564µs, tot_time:303.297475ms, max:60.69326ms, p95:60.69326ms}  | group by:Column#10, funcs:avg(Column#8)->Column#5, funcs:firstrow(Column#9)->bookshop.ratings.rated_at                                         | 714.0 KB | N/A  |
|   └─Projection_15           | 300000.00 | 300000  | root         |               | time:58.5ms, loops:294, Concurrency:5                                                                                                                                                                                                                                                                                                                                | cast(bookshop.ratings.score, decimal(8,4) BINARY)->Column#8, bookshop.ratings.rated_at, hour(cast(bookshop.ratings.rated_at, time))->Column#10 | 366.2 KB | N/A  |
|     └─TableReader_10        | 300000.00 | 300000  | root         |               | time:43.5ms, loops:294, cop_task: {num: 1, max: 43.1ms, proc_keys: 0, rpc_num: 1, rpc_time: 43ms, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                        | data:TableFullScan_9                                                                                                                           | 4.58 MB  | N/A  |
|       └─TableFullScan_9     | 300000.00 | 300000  | cop[tiflash] | table:ratings | tiflash_task:{time:5.98ms, loops:8, threads:1}, tiflash_scan:{dtfile:{total_scanned_packs:45, total_skipped_packs:1, total_scanned_rows:368640, total_skipped_rows:8192, total_rs_index_load_time: 1ms, total_read_time: 1ms},total_create_snapshot_time:1ms}                                                                                                        | keep order:false                                                                                                                               | N/A      | N/A  |
+-----------------------------+-----------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+

フィールドcop[tiflash]が表示された場合、タスクは処理のためにTiFlashに送信されることを意味します。

`CREATE TABLE`文を実行する

上記のルールに従ってすべてのテーブルを作成すると、スクリプトデータベースの初期化は次のようになります。テーブルの詳細情報を確認したい場合は、表の説明を参照してください。

データベース初期化スクリプトinit.sql名前を付けて保存するには、次のステートメントを実行してデータベースを初期化します。

mysql
    -u root \
    -h {host} \
    -P {port} \
    -p {password} \
    < init.sql

bookshopデータベースの下にあるすべてのテーブルを表示するには、 SHOW TABLESステートメントを使用します。

SHOW TABLES IN `bookshop`;

実行結果:

+--------------------+
| Tables_in_bookshop |
+--------------------+
| authors            |
| book_authors       |
| books              |
| orders             |
| ratings            |
| users              |
+--------------------+