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

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

View Slide

| 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

View Slide

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

View Slide

| 4
データベースオブジェクト

View Slide

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

View Slide

| 6
スキーマのKVマッピングの原則(続)

View Slide

| 7

View Slide

| 8

View Slide

| 9

View Slide

| 10

View Slide

| 11

View Slide

| 12

View Slide

| 13

View Slide

| 14
スキーマの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]

View Slide

| 15
スキーマの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]

View Slide

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

View Slide

| 17
スキーマ設計 - クラスタ表と非クラスタ表（A)
• 名称の説明

View Slide

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

View Slide

| 19
スキーマ設計 - クラスタ表と非クラスタ表（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)

View Slide

| 20
スキーマ設計 - クラスタ表と非クラスタ表（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`;

View Slide

| 21
スキーマ設計 - クラスタ表と非クラスタ表（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;

View Slide

| 22
スキーマ設計- パーティション表
• 現在サポートされているパーティションタイプは、レンジパーティション、リストパーティション、リスト 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

View Slide

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

View Slide

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

View Slide

| 25
スキーマ設計 - 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 |
+—--—+---+

View Slide

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

View Slide

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

View Slide

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

View Slide

| 29
します。
されます。
•

View Slide

| 30
します。
されます。
•

View Slide

| 31
します。
されます。

View Slide

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

View Slide

| 33
スキーマの設計 - 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 |
+-----------+

View Slide

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

View Slide

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

View Slide

| 36
スキーマに関する制限事項 - データ型

View Slide

| 37
スキーマに関する制限事項 - オブジェクトに関する制限事項

View Slide

| 38
スキーマ設計 - 互換性を重視したスキーマ
• 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;

View Slide

| 39
スキーマ設計 - 性能を重視したスキーマ
• 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;

View Slide

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

View Slide

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

View Slide

| 42
Summary
• TiDBスキーマの構築方法
• TiDBがサポートするデータ型と、 MySQLの同じデータ型との違いの理解
• TiDBの様々なテーブルデザインの特徴とそのメリット・デメリット

View Slide

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

View Slide

| 44
おしらせ

View Slide

| 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

View Slide

| 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

View Slide

| 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の理解を深めることができます。

View Slide

| 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 予定数に達し次第終了する可能性があります。 

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

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

PingCAP-Japan

More Decks by PingCAP-Japan

Other Decks in Technology

Featured

Transcript