【NewSQL】無料トレーニング：TiDBでの表設計

| 1 PingCAP Education: TiDB での表設計  本多康夫 Technical support engineer
at PingCAP Japan

| 2 本日の位置づけ https://pingcap.co.jp/event/     2022年6月16日(木) 14:00-15:00 PingCAP Education：TiKV-
RocksDBについて 2022年6月23日(木) 14:00-15:00 アプリケーション開発観点からの TiDB - PHP編 2022年6月30日(木) 14:00-15:00 HTAPを体験！TiDBのカラムナストアで分析してみよう！ 2022年7月14日(木) 14:00-15:30 TiDB Cloudの紹介・デモ 2022年7月21日(木) 14:00-15:00 TiDB Cloud リリースノートについて 2022年7月28日(木) 14:00-15:00 PingCAP Education：TiDBでの表設計 ※過去開催アーカイブ　：　https://www.youtube.com/channel/UCatxrGZANSnii2fe7FeEwvg/playlists

| 3 Copyright © 2022, PingCAP. All rights reserved. アジェンダ
• TiDBでのテーブル格納のアーキテクチャを理解する • TiDBにてサポートされているデータ型および MySQLとの比較 • TiDBの各種類のテーブル、インデックスにおける特徴、メリットとデメリット

| 5 Copyright © 2022, PingCAP. All rights reserved. スキーマのKVマッピングの原則
• TiDBのデータはRocksDBのKey Valueペアとして格納されます。 • ストレージ管理の基本単位は Region (リージョン) です。 • 各リージョンのデフォルトサイズは 96MB です。 • 各リージョンは、左閉じと右開きの Range、例えば、[a~ d), [d ~ g) のように分割されます。 • 各スキーマ(テーブル)には一意のTableIDが付与されます。

| 14 Copyright © 2022, PingCAP. All rights reserved. スキーマのKVマッピングの原則(B)
• クラスタリングとは、ある列を基準に、クラスタ化された同じキー値を持つすべての行を同じ場所に保存する物理的な保存方法です • KV ペアValueに実際の行データを格納している • 非クラスタ表を KV にマッピングするルール • クラスタ表を KV にマッピングするルール Key: tablePrefix{ TableID }_recordPrefixSep{ _Tidb_RowID } Value: [col1, col2, col3, col4] Key: tablePrefix{ TableID }_recordPrefixSep{ Col1 } Value: [col2, col3, col4]

| 15 Copyright © 2022, PingCAP. All rights reserved. スキーマのKVマッピングの原則(C)
• クラスタ表の例- Userテーブルを作成する • ( ID 列がプライマリーキー，クラスタ) • 3行データをinsertする • データの KV マッピング CREATE TABLE User ( ID int not null PRIMARY KEY clustered, Name varchar(20), Role varchar(20), Age int, KEY idxAge (Age) ); Insert into User values ( 1, "TiDB", "SQL Layer", 10); Insert into User values (2, "TiKV", "KV Engine", 20); Insert into User values (3, "PD", "Manager", 30); t10_r1 --> ["TiDB", "SQL Layer", 10] t10_r2 --> ["TiKV", "KV Engine", 20] t10_r3 --> ["PD", "Manager", 30]

| 16 Copyright © 2022, PingCAP. All rights reserved. スキーマ設計
- クラスタ表と非クラスタ表（A) • クラスタ表 • テーブルの行が主キーと同じ順序で格納されている場合、そのテーブルはクラスタ化される • テーブルの主キーは、KVマッピングのキーの一部になる • 主キーで行レコードにアクセスする場合、行レコードを直接取得することができる • (a列は主キー列、クラスタ化された列) CREATE TABLE t ( a BIGINT PRIMARY KEY CLUSTERED, b VARCHAR(255) );

- クラスタ表と非クラスタ表（A) • 名称の説明

- クラスタ表と非クラスタ表（B) • 非クラスタ表 • 行の格納順が主キーの格納順と必ずしも一致しない場合、そのテーブルは非クラスタ化されています。 • 行データのキーTiDBが内部で暗黙的に割り当てる_tidb_rowidで構成され、主キーは基本的にユニークインデックスです。 • 主キー経由で行レコードにアクセスする場合、行レコードを直接取得することはできません。追加で保存した主キーから行の _tidb_rowid を取得し、この行の _tidb_rowid を経由して行レコードを取得する必要があります。これは、クラスタ化されたテーブルの場合よりも、テーブルへの操作が1つ多くなります。 CREATE TABLE t (a BIGINT PRIMARY KEY NONCLUSTERED, b VARCHAR(255));

- クラスタ表と非クラスタ表（C） • 主キーがクラスタ化インデックスかどうかを確認する • SHOW CREATE TABLE コマンド • SHOW INDEX FROM コマンド • information_schema.tables の TIDB_PK_TYPE 列 SHOW CREATE TABLE t; +-------+---------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------+ | t | CREATE TABLE `t` ( `a` bigint(20) NOT NULL, `b` varchar(255) DEFAULT NULL, PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-------+---------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------+ 1 row in set (0.01 sec)

- クラスタ表と非クラスタ表（D） • 非クラスタ化インデックスの追加と削除 • TiDB は、テーブルを作成した後に非クラスタ化インデックスの追加または削除をサポートしています。 • 追加や削除をする場合、NONCLUSTERED キーワードを明示的に指定するか、キーワードを省略する必要があります。 • クラスタ化インデックスの追加と削除 • TiDBは現在、テーブル作成後のクラスタ化インデックスの追加や削除をサポートしておらず、クラスタ化インデックスと非クラスタ化インデックスの相互変換もサポートしていません。 ALTER TABLE t ADD PRIMARY KEY(b, a) NONCLUSTERED; ALTER TABLE t ADD PRIMARY KEY(b, a); /* キーワードを指定しない場合は、非クラスタ化インデックスを使用します */ ALTER TABLE t DROP PRIMARY KEY; ALTER TABLE t DROP INDEX `PRIMARY`;

- クラスタ表と非クラスタ表（E） • SHARD_ROW_ID_BITS • 非クラスタ表は、TiDBは暗黙的に自己インクリメントするrowidを使用し、大量に連続書き込みを行う場合、ホットスポットが発生する場合があります。 • SHARD_ROW_ID_BITSは、暗黙的に生成される_tidb_rowidの上位ビット値をランダムに生成することにより、異なる region に分けることによりホットスポットを分散可能にします。 • SHARD_ROW_ID_BITS = 4 16分割を意味します • SHARD_ROW_ID_BITS = 5 32分割を意味します • 一般に、SHARD_ROW_ID_BITSはPRE_SPLIT_REGIONSと一緒に使用され、テーブルが作成されると、 2^(PRE_SPLIT_REGIONS) のリージョンに分割されます。 • 例：非クラスタ表を作成し、 SHARD_ROW_ID_BITSを使用してデータを分割し、テーブル作成後に 4つの領域にあらかじめスライスしておく。 • 例：ランダムビットを5つに設定するようにテーブルを修正する ALTER TABLE t SHARD_ROW_ID_BITS = 5; CREATE TABLE t (c int PRIMARY KEY NONCLUSTERED) SHARD_ROW_ID_BITS = 4 pre_split_regions = 2;

| 22 Copyright © 2022, PingCAP. All rights reserved. スキーマ設計-
パーティション表 • 現在サポートされているパーティションタイプは、レンジパーティション、リストパーティション、リスト COLUMNS パーティション、ハッシュパーティションです。 • レンジパーティション、リストパーティション、リスト COLUMNSパーティションは、ビジネスにおける大量削除に関連するパフォーマンス問題を解決するために使用することができます。 • ハッシュパーティションは、大量書き込みのシナリオでデータを分割するために使用できます。 • パーティションされたテーブルの各ユニークキーまたはプライマリーキーは、パーティション式で使用されるすべてのカラムを含まなければなりません。 • 有効なパーティションテーブルと無効なパーティションテーブル - 有効 CREATE TABLE t1 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2, col3) ) PARTITION BY HASH(col3) PARTITIONS 4; - 無効 CREATE TABLE t1 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2) ) PARTITION BY HASH(col3) PARTITIONS 4; (ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

- TiDBがサポートするデータ型 • TiDBは、SPATIALを除くすべてのMySQLデータ型をサポートしています。 • 数値 • 文字列 • 時刻と日付の種類 • JSON

- TiDBのデフォルト値 • 数値型、ほとんどの文字列型は、デフォルト値として定数である必要があります。 • 時刻と日付の列は、デフォルトとして関数を使用することができます。 • BLOB、TEXT、JSONはデフォルト値を持つことができません。

- TiDB 自動インクリメント ID（A） • AUTO_INCREMENT の基本的な使い方 CREATE TABLE t(id int PRIMARY KEY AUTO_INCREMENT, c int); INSERT INTO t(c) VALUES (1); INSERT INTO t(c) VALUES (2); INSERT INTO t(c) VALUES (3), (4), (5); SELECT * FROM t; +----+---+ | id | c | +----+---+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | +—--—+---+

- TiDB 自動インクリメント ID（B） • AUTO_INCREMENT の実装について • 自己インクリメントの各カラムは、グローバルに見えるキーと値のペアを使用して、現在割り当てられている最大のIDを記録します。 • 分散システムで自己インクリメント型IDを割り当てる際のネットワークオーバーヘッドを削減するため、各TiDBノードは重複のないIDセグメントをキャッシュしています。 • 現在割り当て済みのIDセグメントが使い果たされた時、またはTiDBが再起動されたときに、新しいIDセグメントが再度要求されます。

- TiDB 自動インクリメント ID（B） • AUTO_INCREMENT の実装について • 自己インクリメントの各カラムは、グローバルに見えるキーと値のペアを使用して、現在割り当てられている最大のIDを記録します。 • 分散システムで自己インクリメント型IDを割り当てる際のネットワークオーバーヘッドを削減するため、各TiDBノードは重複のないIDセグメントをキャッシュしています。 • 現在割り当て済みのIDセグメントが使い果たされた時、またはTiDBが再起動されたときに、新しいIDセグメントが再度要求されます。 •

- TiDB 自動インクリメント ID（C） • 使用限制： • 主キーまたはユニークインデックスのカラムに定義する必要があります。 • integer, FLOAT, DOUBLE 型のカラムにのみ定義できます。 • カラムのデフォルトDEFAULT値と同じカラムに指定された場合はサポートされません。 • ALTER TABLE による AUTO_INCREMENT 属性の追加はサポートされていません。 • セッション変数 @@tidb_allow_remove_auto_inc で、ALTER TABLE で AUTO_INCREMENT 属性の削除を許可するかどうかを制御します; デフォルトは削除不可となります。

| 33 Copyright © 2022, PingCAP. All rights reserved. スキーマの設計
- TiDBランダムID (A) • AUTO_RANDOM 、TiDBに大量のデータを書き込む際に、整数の自己インクリメント主キー列を持つテーブルが引き起こすホットスポットを解決するために使用されます。 create table t (a int primary key auto_random); insert into t values (), (); select * from t; +-----------+ | a | +-----------+ | 201326593 | | 201326594 | +-----------+

- TiDBランダムID (B) • AUTO_RANDOM の実装方法 • AUTO_RANDOM は 8 バイトの bigint 整数です。 • 最上位ビットは符号ビットです。 • 異なる長さのランダムビットを使用する場合はAUTO_RANDOMの後の括弧でビット数を調整します。 CREATE TABLE t (a bigint PRIMARY KEY AUTO_RANDOM(3), b varchar(255))

- TiDBランダムID (C) • 制限事項： • AUTO_RANDOMカラムの型はBIGINTのみです。 • 主キー属性がNONCLUSTEREDの場合、整数の主キーカラムであってもAUTO_RANDOMは指定できません。 • ALTER TABLE を使用したAUTO_RANDOM 属性の変更（追加・削除を含む）はサポートされていません。 • AUTO_RANDOM 属性を含む主キーカラムのカラム型の変更はサポートされていません。 • AUTO_INCREMENT と同じカラムに指定できません。 • DEFAULT と同じカラムを指定することはサポートされません。 • データを挿入する際、AUTO_RANDOMを含むカラムの値を明示的に指定することは推奨されません。これにより、自動割り当てられた値が早期に枯渇することがあります。

| 36 Copyright © 2022, PingCAP. All rights reserved. スキーマに関する制限事項
- データ型

| 37 Copyright © 2022, PingCAP. All rights reserved. スキーマに関する制限事項
- オブジェクトに関する制限事項

- 互換性を重視したスキーマ • MySQLからTiDBデータベースへ移行するテーブルの場合に検討します。 • テーブル構築時に非クラスタ化されたテーブルを作成します。 • SHARD_ROW_ID_BITS と PRE_SPLIT_REGIONS オプションをテーブルに追加します。 • その他は従来設計通りとします。 CREATE TABLE noncluster_order ( id bigint(20) unsigned AUTO_INCREMENT NOT NULL , code varchar(30) NOT NULL, order_no varchar(200) NOT NULL DEFAULT '', status int(4) NOT NULL, cancle_flag int(4) DEFAULT NULL, create_user varchar(50) DEFAULT NULL, update_user varchar(50) DEFAULT NULL, create_time datetime DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) NONCLUSTERED ) ENGINE=InnoDB SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3;

- 性能を重視したスキーマ • TiDBデータベースに追加された新しいテーブルの場合に検討します。 • テーブル構築時にクラスタ化されたテーブルを作成します。 • 主キーは非常に Random な列にする、もしくは AUTO_INCREMENT 属性の代わりに AUTO_RANDOM 属性を使用します。 • カラムの正確なデータ型を選択します。整数型または日付型のカラムを使用できる場合、文字列型は避けてください。 • 無効なインデックスを作成しないようにします。 CREATE TABLE cluster_order ( id bigint(20) unsigned AUTO_RANDOM NOT NULL , code varchar(30) NOT NULL, order_no varchar(200) NOT NULL DEFAULT '', status int(4) NOT NULL, cancle_flag int(4) DEFAULT NULL, create_user varchar(50) DEFAULT NULL, update_user varchar(50) DEFAULT NULL, create_time datetime DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) CLUSTERED ) ENGINE=InnoDB;

| 40 Copyright © 2022, PingCAP. All rights reserved. テスト
• 1) TiDBデータベースのスキーマにおける KVマッピングの原則について、正しい記述は何か (正解は2つ) • A.クラスタ化されたテーブルKEY VALUEには、実際の行データが格納される • B. テーブルを作成する場合、デフォルトはクラスタ化されたテーブルとして作成されます。 • C. 各Regionは、左閉じ、右開きの区間、例えば [a ~ d), [d ~ g) に従って、データ格納範囲に分割される。 • D. クラスタ化されていないテーブルが主キーを持つ場合、KVのkeyには主キーカラムが含まれます。 • E. 非クラスタ表には where 条件に主キーを使用してフィルターしている場合、クラスタ表より操作が多くなる

| 41 Copyright © 2022, PingCAP. All rights reserved. テスト
• 2）TiDBデータベースのスキーマの設計について、間違っているものは何ですか？ • A. AUTO_RANDOMとAUTO_INCREMENTは、同じ列に同時に設定することはできません。 • B. NOW機能は、時刻と日付のタイプのデフォルト値として使用することができます。 • C. クラスタ化されていないインデックスは、テーブルを構築した後に追加または削除することができます。 • D. PRE_SPLIT_REGIONSはカラム属性で、使用時はAuto Increment 列の後ろに指定します。

| 43 Q&A/アンケートのお願い • Q&A 下のQ&Aボタンから質問いただけます。 • アンケートアンケートの回答をお願いいたします！ご意見・感想をいただけますと大変助かります！

| 44 おしらせ

| 45 Webinar : 今後の予定 https://pingcap.co.jp/event/ 2022年9月9日 14:00 - 15:00
PingCAP Education：TiKV - トランザクション & MVCC ※過去開催アーカイブ　：　https://www.youtube.com/channel/UCatxrGZANSnii2fe7FeEwvg/playlists

| 46 Webinar : アーカイブ 2022年4月14日(木) 14:00-15:30 TiDB ソフトウェアの紹介・デモ 2022年4月21日(木)
14:00-15:00 アプリケーション開発観点からの TiDB - Ruby編 2022年5月12日(木) 14:00-15:30 TiDB Cloudの紹介・デモ 2022年5月19日(木) 14:00-15:00 最新TiDB 6.0のポイント 2022年5月26日(木) 14:00-15:00 アプリケーション開発観点からの TiDB - Java編 2022年6月9日(木) 14:00-15:30 TiDB ソフトウェアの紹介・デモ 2022年6月16日(木) 14:00-15:00 PingCAP Education：TiKV- RocksDBについて 2022年6月23日(木) 14:00-15:00 アプリケーション開発観点からの TiDB - PHP編 2022年6月30日(木) 14:00-15:00 HTAPを体験！TiDBのカラムナストアで分析してみよう！ 2022年7月21日(木) 14:00-15:00 TiDB Cloudリリースノートについて https://www.youtube.com/channel/UCatxrGZANSnii2fe7FeEwvg/featured

| 47 PingCAP Education トレーニングトレーニング全体リンク　　https://en.pingcap.com/education/ 無償コース（推奨）　　TiDB Technical
Essentials 101: 内容　　- TiDB全体のアーキテクチャ　　- TiDBの各コンポーネントのアーキテクチャ　　- HTAP Overview & TiFlashのアーキテクチャ　　- TiDB Cloudの始め方　　- TiDB 6.0情報 Course: TiDB Essentials 101 トレーニングを通じ、TiDBの理解を深めることができます。

| 48 クラウドアプリのスタートをご支援！ TiDB Cloud 1年無料キャンペーン日本進出から１周年を記念し、TiDB Cloudをご利用いただいたことがないお客様を対象にTiDB Cloud1年間相当の値引きキャンペーンを実施します。キャンペーン期間
2022年5月18日～2023年3月31日　申込み分までキャンペーン内容最大700万円相当（TiDB Cloud典型構成で1年分相当）の値引きを提供します。対象・適用条件下記条件をみたすお客様 ※1（最大10社※2）　- TiDB Cloudを使用したことがないお客様　- 事例・プロモーションにご協力いただけるお客様　- 設立から1年以上経過しているお客様適用方法フォームにご入力の上、キャンペーンにお申し込みください。審査結果およびキャンペーンの詳細について担当者より連絡いたします。 URL https://pingcap.co.jp/start-dash-202205/ ※1 別途所定の審査あり  ※2 予定数に達し次第終了する可能性があります。 

| 49 TiDB Cloud : 無償トライアルのご案内 TiDB Cloud Free Trialはこちら
https://tidbcloud.com/signup Developer Tierでは TiDB Cloudを無償で1年間ご利用頂けます。容量制限はありますが、本番と同等の機能を提供しているため、アプリとの接続試験などが容易に。

| 50 OSS Insight : 活発度が分かる分析サービス 48億を超えるGitHub上のイベント分析するデータベースとしてTiDB (TiFlash)を活用 https://ossinsight.io/ ①各イベント発生の推移
①GitHubイベントデータをリアルタイムに同期 +TiFlash https://ossinsight.io/ ②Pull requests地域の表示 ②分析クエリを TiFlash(カラムストア ) で高速処理 ③ジャンル内での比較（人気言語等） etc…

| 51 TiDB/TiDB Cloud : 日本語版ドキュメントのご案内 TiDB/TiDB Cloud 日本語版ドキュメントはこちら↓ https://docs.pingcap.com/ja/tidb/stable/overview

| 52 Thank You!  https://www.pingcap.com/  [email protected]  PingCAP Education 

【NewSQL】無料トレーニング：TiDBでの表設計

【NewSQL】無料トレーニング：TiDBでの表設計

More Decks by PingCAP-Japan

Other Decks in Technology

Featured

Transcript