# CLUSTEREDとNONCLUSTEREDプライマリキー ### 概要 + この演習では、`CLUSTERED`と`NONCLUSTERED` プライマリキーの違いを比較します。 + 最初に`CLUSTERED` プライマリキーでテーブルを作成し、そのテーブルに50万レコードを挿入します。次に、`NONCLUSTERED` プライマリキーで別のテーブルを作成します。次に、最初のテーブルから2番目のテーブルにデータをコピーします。最後に、TiKV のリージョン数と2つのテーブル間の実行計画を比較します。 ### 注意 + いくつかのステップでは、2つの異なるターミナルウィンドウでコマンドを入力する必要があります。2つのターミナルを起動し、演習の間ずっと開いておくと便利でしょう。 + この演習で利用する、`IP`、`port`、`username`、`password`、`directory`などは、あなたの環境と異なる場合があります。 + ターミナルウィンドウの"`$`"とはOSプロンプトです。 + ターミナルウィンドウ内の "`tidb>`" または "`mysql>`" は、SQL コマンドプロンプトです。 + これらの演習は、ノートパソコンなどの単一のマシン上で実行することができます。 + この演習では、TiDB Cloud Serverless Tierによってブロックされている`information_schema.tikv_region_status`をクエリする必要があるため、TiDB Cloud Serverless Tierは使用できません。 ### 前提条件 + macOSまたはLinuxを使用していること。 + [lab repository](https://github.com/pingcap/tidb-course-201-lab.git)をクローン済みであること。 + 演習「テスト用にTiDBクラスタを作成する」を完了していること。 ### 所要時間 + この演習は約15分程度かかります。 ### タスク 1. TiDB サーバーに接続します。 2. デモスクリプトを確認して、理解します。 3. `CLUSTERED`と`NONCLUSTERED`のプライマリキーの違いを比較します。 ### 手順 1. TiDB サーバーに接続します。 TiDB Playgroundクラスタユーザーの場合、接続に失敗した場合は、演習1を再確認します。 ``` $ cd tidb-course-201-lab/scripts $ ./connect-4000.sh ... tidb:4000> ``` 2. デモスクリプト`07-demo-compare-clustered-and-nonclustered-pk.sql`を実行して、結果を確認します。これら2つのテーブルの同じ述語`WHERE id BETWEEN 10 AND 100`に対して実行計画が異なる理由を説明してみましょう。 テーブル`test.auto_increment_t1_clustered`には`CLUSTERED` プライマリキーがあり、約53万行が入力されています。一方、テーブル`test.t2_nonclustered`には`NONCLUSTERED`プライマリキーが1行しかありません。 `information_schema.tables`クエリの`tidb_pk_type`カラムに注目します。2つのテーブルに対してそれぞれ`CLUSTERED`と`NONCLUSTERED`があります。 テーブル`test.auto_increment_t1_clustered`は、約53万行あります。合計サイズが96 MB未満であり、テーブルとプライマリキーインデックスは同じ構造（`CLUSTERED` プライマリキーの結果）なので、1つのTiKVリージョンのみを占めています。一方、テーブル`test.t2_nonclustered`には1行しかありませんが、それでも2つのTiKVリージョンを占めています。1つはテーブルデータ用、もう1つはプライマリキーインデックス（`NONCLUSTERED` プライマリキーの結果）です。 その結果、最後の部分では、これら2つのテーブルからの同じ述語の実行計画に違いが見られます。どちらもプライマリキーを述語として使用しています。`NONCLUSTERED` プライマリキーテーブルでは、実行計画でもう1つのステップが必要です。 最後に、`_tidb_rowid`の疑似カラムは`NONCLUSTERED`プライマリキーのテーブルに存在しますが、`CLUSTERED`プライマリキーのテーブルにはこのカラムが含まれていないことに注意します。 ``` tidb:4000> SOURCE 07-demo-compare-clustered-and-nonclustered-pk.sql ... -------------- CREATE TABLE test.auto_increment_t1_clustered ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name CHAR(255), varname VARCHAR(200) ) -------------- ... -------------- CREATE TABLE test.t2_nonclustered ( id BIGINT PRIMARY KEY NONCLUSTERED, name CHAR(255), varname VARCHAR(200) ) -------------- ... -------------- SELECT COUNT(DISTINCT id), COUNT(*), MIN(id), MAX(id) FROM test.auto_increment_t1_clustered -------------- +--------------------+----------+---------+---------+ | COUNT(DISTINCT id) | COUNT(*) | MIN(id) | MAX(id) | +--------------------+----------+---------+---------+ | 524288 | 524288 | 1 | 524288 | +--------------------+----------+---------+---------+ 1 row in set (0.48 sec) -------------- SELECT tidb_row_id_sharding_info, tidb_pk_type FROM information_schema.tables WHERE table_name = 'auto_increment_t1_clustered' -------------- +---------------------------+--------------+ | tidb_row_id_sharding_info | tidb_pk_type | +---------------------------+--------------+ | NOT_SHARDED(PK_IS_HANDLE) | CLUSTERED | +---------------------------+--------------+ 1 row in set (0.01 sec) -------------- ... -------------- SELECT COUNT(DISTINCT id), COUNT(*), MIN(id), MAX(id) FROM test.t2_nonclustered -------------- +--------------------+----------+---------+---------+ | COUNT(DISTINCT id) | COUNT(*) | MIN(id) | MAX(id) | +--------------------+----------+---------+---------+ | 1 | 1 | 50 | 50 | +--------------------+----------+---------+---------+ 1 row in set (0.00 sec) -------------- SELECT tidb_row_id_sharding_info, tidb_pk_type FROM information_schema.tables WHERE table_name = 't2_nonclustered' -------------- +---------------------------+--------------+ | tidb_row_id_sharding_info | tidb_pk_type | +---------------------------+--------------+ | NOT_SHARDED | NONCLUSTERED | +---------------------------+--------------+ 1 row in set (0.00 sec) -------------- SELECT * FROM ( SELECT COUNT(region_id) AS "Clustered # of TiKV Regions" FROM information_schema.tikv_region_status WHERE table_name = 'auto_increment_t1_clustered' ) v1 join ( SELECT COUNT(region_id) AS "Non-Clustered # of TiKV Regions" FROM information_schema.tikv_region_status WHERE table_name = 't2_nonclustered' ) v2 -------------- +-----------------------------+---------------------------------+ | Clustered # of TiKV Regions | Non-Clustered # of TiKV Regions | +-----------------------------+---------------------------------+ | 1 | 2 | +-----------------------------+---------------------------------+ 1 row in set (0.01 sec) -------------- SELECT 'SELECT varname FROM test.auto_increment_t1_clustered WHERE id BETWEEN 10 AND 100;' AS "Clustered" -------------- +-----------------------------------------------------------------------------------+ | Clustered | +-----------------------------------------------------------------------------------+ | SELECT varname FROM test.auto_increment_t1_clustered WHERE id BETWEEN 10 AND 100; | +-----------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- EXPLAIN SELECT varname FROM test.auto_increment_t1_clustered WHERE id BETWEEN 10 AND 100 -------------- +--------------------------+---------+-----------+-----------------------------------+------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------+---------+-----------+-----------------------------------+------------------------------------------+ | Projection_4 | 85.98 | root | | test.auto_increment_t1_clustered.varname | | └─TableReader_6 | 85.98 | root | | data:TableRangeScan_5 | | └─TableRangeScan_5 | 85.98 | cop[tikv] | table:auto_increment_t1_clustered | range:[10,100], keep order:false | +--------------------------+---------+-----------+-----------------------------------+------------------------------------------+ 3 rows in set (0.00 sec) -------------- SELECT 'SELECT varname FROM test.t2_nonclustered WHERE id BETWEEN 10 AND 100;' AS "Non-Clustered" -------------- +-----------------------------------------------------------------------+ | Non-Clustered | +-----------------------------------------------------------------------+ | SELECT varname FROM test.t2_nonclustered WHERE id BETWEEN 10 AND 100; | +-----------------------------------------------------------------------+ 1 row in set (0.00 sec) -------------- EXPLAIN SELECT varname FROM test.t2_nonclustered WHERE id BETWEEN 10 AND 100 -------------- +---------------------------+---------+-----------+-----------------------+-------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+---------+-----------+-----------------------+-------------------------------------------------------------------+ | Projection_4 | 1.00 | root | | test.t2_nonclustered.varname | | └─TableReader_7 | 1.00 | root | | data:Selection_6 | | └─Selection_6 | 1.00 | cop[tikv] | | ge(test.t2_nonclustered.id, 10), le(test.t2_nonclustered.id, 100) | | └─TableFullScan_5 | 1.00 | cop[tikv] | table:t2_nonclustered | keep order:false | +---------------------------+---------+-----------+-----------------------+-------------------------------------------------------------------+ 4 rows in set (0.00 sec) -------------- SELECT _tidb_rowid FROM test.t2_nonclustered -------------- +-------------+ | _tidb_rowid | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) -------------- SELECT _tidb_rowid FROM test.auto_increment_t1_clustered -------------- ERROR 1054 (42S22): Unknown column '_tidb_rowid' in 'field list' ``` ## まとめ `CLUSTERED`プライマリインデックスにより、TiDBは特定のクエリのパフォーマンスを向上させるような方法でテーブルを整理することができます。`CLUSTERED`と`NONCLUSTERED`プライマリキーの詳細については、[TiDBのドキュメント](https://docs.pingcap.com/ja/tidb/stable/clustered-indexes)を参照してください。 ## 演習終わり