テーブルを作成する

このドキュメントでは、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}

パラメータの説明

一意の識別子idbalancenicknameなどのいくつかの列を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.999999.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 ではクラスター化インデックスはキーワードCLUSTEREDNONCLUSTEREDを使用して制御されます。

ノート:

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

次の例では、 クラスター化インデックスを選択するためのガイドラインに続いて、 book x usersratingsを表すbooksusersを関連付けたテーブルを作成します。この例では、テーブルを作成し、 book_iduser_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テーブル全体のscorerated_atフィールドをクエリする必要があります。この操作は、OLTP 専用データベースの場合、リソースを大量に消費します。または、ETL またはその他のデータ同期ツールを使用して、分析のために OLTP データベースから専用の OLAP データベースにデータをエクスポートすることもできます。

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

列ベースのデータをレプリケートする

現在、TiDB は、 TiFlashTiSpark という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 | +--------------------+

テーブルを作成するときに従うべきガイドライン

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

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

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

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

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

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

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

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

  • 主キーを選択するためのガイドラインに従ってクラスター化インデックスを構築します。

  • 非クラスター化インデックスを含むテーブルと比較して、クラスター化インデックスを含むテーブルは、次のシナリオでパフォーマンスとスループットの利点が大きくなります。

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

    • 近い値を持つ主キーを多数挿入すると、書き込みホットスポットの問題が発生する可能性があります。 主キーを選択するときに従うべきガイドラインに従ってください。
    • 主キーのデータ型が 64 ビットより大きい場合、特に複数のセカンダリ インデックスがある場合、テーブル データはより多くのstorage領域を占有します。
  • クラスター化インデックスを使用するかどうかのデフォルトの動作を制御するには、システム変数@@global.tidb_enable_clustered_indexと構成alter-primary-keyを使用する代わりに、クラスター化インデックスを使用するかどうかを明示的に指定できます。

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

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

もう一歩

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

このページは役に立ちましたか?