重要

このページは英語版のページを機械翻訳しています。原文はこちらからご覧ください。

テーブルを作成する

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

始める前に

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

テーブルとは

テーブルデータベースに従属する TiDB クラスター内の論理オブジェクトです。 SQL ステートメントから送信されたデータを保存するために使用されます。テーブルはデータレコードを行と列の形式で保存します。テーブルには少なくとも 1 つの列があります。 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} :列レベルの制約や生成された列句などのカラム修飾。

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

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

上記のステートメントでは、フィールドは名前idとタイプビギントで定義されています。これは、一意のユーザー識別子を表すために使用されます。これは、すべてのユーザー識別子がbigintタイプである必要があることを意味します。

次に、 nicknameという名前のフィールドが定義されます。これはタイプ可変長文字で、長さの制限は 100 文字です。これは、ユーザーのうちnicknames人がvarcharタイプを使用しており、文字数が 100 文字以下であることを意味します。

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

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

もう少し複雑にするために、 bookshopのデータの中核となるbooksテーブルを定義できます。テーブル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型を使用して時刻値を保存できます。
列挙型 : enum 型は、限られた値の選択を保存するために使用できます。

主キーを選択

主キーは、値がテーブル内の行を一意に識別するテーブル内の列または列のセットです。

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

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

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

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

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

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

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

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

TiDB は v5.0 以降クラスター化インデックス機能をサポートしています。この機能は、主キーを含むテーブルにデータを格納する方法を制御します。これにより、特定のクエリのパフォーマンスを向上できる方法でテーブルを編成する機能が TiDB に提供されます。

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

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

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

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

ノート：
TiDB は、テーブルのPRIMARY KEYによるクラスタリングのみをサポートします。クラスター化インデックスが有効になっている場合、 5とクラスター化インデックスという用語は同じ意味で使用される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`)
);

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

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) の場合、 TiFlash MPP がHTAP の主要なソリューションとして推奨され、 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 サーバーレスクラスタを構築するを使用すると、 TiFlashを含む TiDB サーバーレスクラスターを作成できます。

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

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