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

テーブルを作成する

このドキュメントでは、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 つの列があります。列は、各行の値を 1 つのデータ型の小さなセルに分割することで、テーブルに構造を提供します。

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

{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で定義されています。これは、一意のユーザー識別子を表すために使用されます。これは、すべてのユーザー ID がbigint型である必要があることを意味します。

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

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

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型を使用して時刻値を格納できます。
  • 列挙 : 列挙型を使用して、限定された値の選択を格納できます。

主キーを選択

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

ノート：

TiDB の主キーのデフォルト定義は、 InnoDB (MySQL の共通ストレージ エンジン) とは異なります。

  • InnoDBの場合:主キーは一意であり、null ではなく、インデックスがクラスター化されています。

  • TiDB の場合:主キーは一意であり、null ではありません。ただし、主キーがクラスター化インデックスであるとは限りません。代わりに、別の一連のキーワードNONCLUSTEREDが追加で主キーがクラスター化インデックスかどうかを制御しCLUSTERED 。キーワードが指定されていない場合は、 クラスター化インデックスで説明されているように、システム変数@@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 つだけです。
    • 主キーデータ（キー） - 行データ（値）

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

ノート：

TiDB は、テーブルのPRIMARY KEYによるクラスタリングのみをサポートします。クラスター化インデックスを有効にすると、 PRIMARY KEYとクラスター化インデックスという用語が同じ意味で使用される場合があります。 PRIMARY KEYは制約 (論理プロパティ) を参照し、クラスター化インデックスはデータの格納方法の物理的な実装を記述します。

クラスタ化インデックスを選択するためのガイドラインに続いて、次の例では、 booksとusersの間の関連付けを持つテーブルを作成します。これは、 book x 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 はヌルではない制約、 ユニークキー制約、および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 (Hybrid Transactional and Analytical Processing)データベースである TiDB が、理想的なワンストップ データベース ソリューションです。

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

現在、TiDB はTiFlashとTiSparkの 2 つのデータ分析エンジンをサポートしています。大規模なデータ シナリオ (100 T) では、 TiFlash MPPが HTAP の主要なソリューションとして推奨され、 TiSparkが補完的なソリューションとして推奨されます。 TiDB HTAP機能の詳細については、次のドキュメントを参照してください: TiDB HTAPのクイック スタート ガイドおよびHTAP を調べる 。

この例では、 bookshopデータベースのデータ分析エンジンとしてティフラッシュが選択されています。

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(開発者層) で TiDB クラスターを構築するを使用して、 TiFlashを含む無料のクラスタを作成できます。

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

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

表を作成する際に従うべきガイドライン

このセクションでは、テーブルを作成する際に従う必要があるガイドラインを示します。

テーブルに名前を付ける際に従うべきガイドライン

  • 完全修飾されたテーブル名 (たとえば、 CREATE TABLE {database_name}. {table_name} ) を使用します。データベース名を指定しない場合、TiDB はSQL セッションで現在のデータベースを使用します。 SQL セッションでデータベースを指定するためにUSE {databasename};を使用しない場合、TiDB はエラーを返します。
  • 意味のあるテーブル名を使用してください。たとえば、ユーザー テーブルを作成する必要がある場合は、名前user 、 t_user 、 usersを使用するか、会社または組織の命名規則に従うことができます。会社または組織に命名規則がない場合は、 テーブルの命名規則を参照できます。 t1 、 table1などのテーブル名は使用しないでください。
  • 複数の単語はアンダースコアで区切ります。名前は 32 文字以内にすることをお勧めします。
  • 異なるビジネス モジュールのテーブル用に別のDATABASEを作成し、それに応じてコメントを追加します。

列を定義する際に従うべきガイドライン

  • 列でサポートされているデータ型を確認し、データ型の制限に従ってデータを整理します。列に格納する予定のデータに適したタイプを選択します。
  • 主キーを選択するための従うべきガイドラインを確認し、主キー列を使用するかどうかを決定します。
  • クラスター化インデックスを選択するための従うべきガイドラインを確認し、クラスター化インデックスを指定するかどうかを決定します。
  • 列の制約を追加するを確認し、列に制約を追加するかどうかを決定します。
  • 意味のある列名を使用してください。会社または組織のテーブル命名規則に従うことをお勧めします。会社または組織に対応する命名規則がない場合は、 列の命名規則を参照してください。

主キーを選択する際に従うべきガイドライン

  • テーブル内に主キーまたは一意のインデックスを定義します。
  • 主キーとして意味のある列を選択するようにしてください。
  • パフォーマンス上の理由から、幅の広いテーブルを格納しないようにしてください。テーブル フィールドの数が60を超え、1 行の合計データ サイズが64Kを超えることはお勧めしません。データ長が多すぎるフィールドを別のテーブルに分割することをお勧めします。
  • 複雑なデータ型を使用することはお勧めしません。
  • フィールドを結合するには、データ型が一貫していることを確認し、暗黙的な変換を避けてください。
  • 単一の単調なデータ列に主キーを定義することは避けてください。単一のモノトニック データ列 (たとえば、 AUTO_INCREMENT属性を持つ列) を使用して主キーを定義すると、書き込みパフォーマンスに影響を与える可能性があります。可能であれば、主キーの連続属性と増分属性を破棄するAUTO_INCREMENTの代わりにAUTO_RANDOMを使用してください。
  • 書き込みが集中するシナリオで単一のモノトニック データ列にインデックスを作成する必要がある場合は、このモノトニック データ列を主キーとして定義する代わりに、 AUTO_RANDOMを使用してそのテーブルの主キーを作成するか、 SHARD_ROW_ID_BITSとPRE_SPLIT_REGIONSを使用できます。破片へ_tidb_rowid 。

クラスター化インデックスを選択する際に従うべきガイドライン

  • クラスター化インデックスを作成するには、 主キーを選択するためのガイドラインに従ってください。

  • クラスター化されていないインデックスを含むテーブルと比較して、クラスター化されたインデックスを含むテーブルは、次のシナリオでより優れたパフォーマンスとスループットの利点を提供します。

    • データが挿入されると、クラスター化インデックスは、ネットワークからのインデックス データの 1 回の書き込みを減らします。
    • 同等の条件を持つクエリに主キーのみが含まれる場合、クラスター化インデックスは、ネットワークからのインデックス データの 1 回の読み取りを減らします。
    • 範囲条件を含むクエリに主キーのみが含まれる場合、クラスター化インデックスにより、ネットワークからのインデックス データの複数回の読み取りが削減されます。
    • 同等または範囲条件を含むクエリに主キー プレフィックスのみが含まれる場合、クラスター化インデックスは、ネットワークからのインデックス データの複数回の読み取りを減らします。

  • 一方、クラスター化インデックスを含むテーブルには、次の問題がある場合があります。

    • 近い値を持つ多数の主キーを挿入すると、書き込みホットスポットの問題が発生する可能性があります。 主キーを選択する際に従うべきガイドラインに従ってください。
    • 主キーのデータ型が 64 ビットより大きい場合、特に複数のセカンダリ インデックスがある場合、テーブル データはより多くのストレージ領域を占有します。

  • クラスタ化インデックスを使用するかどうかのデフォルトの動作を制御するには、システム変数@@global.tidb_enable_clustered_indexと構成alter-primary-keyを使用する代わりに、クラスター化インデックスを使用するかどうかを明示的に指定できます。

CREATE TABLEステートメントを実行する際に従うべきガイドライン

  • クライアント側のDriverまたは ORM を使用してデータベース スキーマの変更を実行することはお勧めしません。 MySQL クライアントを使用するか、GUI クライアントを使用してデータベース スキーマの変更を実行することをお勧めします。このドキュメントでは、 MySQL クライアントを使用して SQL ファイルを渡し、ほとんどのシナリオでデータベース スキーマの変更を実行します。
  • SQL 開発に従ってくださいテーブルの作成と削除の仕様 。 build 文と delete 文をビジネス アプリケーション内にラップして、判断ロジックを追加することをお勧めします。

もう一歩

このドキュメントで作成されたすべてのテーブルにセカンダリ インデックスが含まれているわけではないことに注意してください。副次索引を追加するためのガイドについては、 セカンダリ インデックスの作成を参照してください。

このページの内容