テーブルを作成する

このドキュメントでは、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タイプである必要があります。

次に、長さ制限が 100 文字のvarchar型であるnicknameという名前のフィールドが定義されます。つまり、ユーザーのうち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 の主キーのデフォルト定義は、翻訳 (MySQL の共通storageエンジン) の主キーの定義とは異なります。
InnoDBの場合:主キーは一意であり、null ではなく、インデックスがクラスター化されます。
TiDB の場合:主キーは一意であり、null ではありません。ただし、主キーがクラスター化インデックスであるとは限りません。代わりに、別のキーワードセットCLUSTERED NONCLUSTERED主キーが**クラスター化インデックス**であるかどうかをさらに制御します。キーワードが指定されていない場合は、クラスター化インデックスで説明されているように、システム変数@@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制約 (論理プロパティ) を指し、クラスター化インデックスはデータの格納方法の物理的な実装を表します。

次の例では、クラスター化インデックスを選択するためのガイドラインに続いて、 book x usersのratingsを表すbooksとusersの関連付けを持つテーブルを作成します。この例では、テーブルを作成し、 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後には現在の時間に関する発言のみが入力でき、 DEFAULTの後にはその他のオプションがサポートされます)。

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 T) の場合、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 。 TiDB Cloudサーバーレスクラスタを構築する使用して、 TiFlash を含むTiDB Cloud Serverless クラスターを作成できます。

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

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              |
+--------------------+