# CLUSTERED and NONCLUSTERED Primary Key ### Overview + In this exercise, you compare the difference between `CLUSTERED` and `NONCLUSTERED` primary keys. + You first create a table with `CLUSTERED` primary key, and then you insert 0.5 million records into the table. Secondly, you create another table with `NONCLUSTERED` primary key. Next, you copy the data from the first table to the second one. Finally, you compare the TiKV region counts and the execution plans between two tables. ### Notes + Some of the steps require that you type commands in two different terminal windows. You might find it convenient to launch two terminals and keep them open for the duration of the exercises. + Your environment may be different with these exercises, including but not limited to `IP`, `port`, `username`, `password` and `directory`. + The `$` in your terminal window is an OS prompt. + The "`tidb>`" or "`mysql>`" in your terminal window is SQL command prompt. + These exercises can be run on a single machine, such as your laptop. + This exercise cannot use TiDB Cloud Serverless Tier, because you need to query `information_schema.tikv_region_status` which is blocked by Serverless Tier. ### Assumptions + You are using macOS or Linux. + You have cloned the [lab repository](https://github.com/pingcap/tidb-course-201-lab.git). + You have completed the Exercises - Creating a TiDB Cluster for Testing Purposes. ### Duration + This exercise should take you approximately 15 minutes to complete. ### Tasks 1. Connect to the TiDB server. 2. Check and understand the demo script. 3. Compare the difference between `CLUSTERED` and `NONCLUSTERED` primary keys. ### Solution 1. Connect to the TiDB server. For TiDB Playground Cluster users (if the connection fails, please double check Exercises 1) ``` $ cd tidb-course-201-lab/scripts $ ./connect-4000.sh ... tidb:4000> ``` 2. Run the demo script `07-demo-compare-clustered-and-nonclustered-pk.sql` and observe the results. Try to explain why the execution plans are different for the same predicate `WHERE id BETWEEN 10 AND 100` between these two tables. Table `test.auto_increment_t1_clustered` has a `CLUSTERED` primary key with around 530,000 rows populated, while table `test.t2_nonclustered` has a `NONCLUSTERED` primary key with only 1 row. Pay attention to the `tidb_pk_type` column of the `information_schema` query, there are `CLUSTERED` and `NONCLUSTERED` for two tables respectively. Table `test.auto_increment_t1_clustered` has around 530,000 rows, as its total size is below 96 MB, it is occupying only one TiKV region because the table and the primary key index are the same structure (the result of `CLUSTERED` primary key). On the other hand, table `test.t2_nonclustered` has only one row, yet it still occupies two TiKV regions, one for the table data and the other for the primary key index (the result of `NONCLUSTERED` primary key). As a result, in the last portion, you can expect to see a difference in their execution plans for the identical predicate from these two tables, both using the primary key as the predicate. `NONCLUSTERED` primary key table needs one more step in the execution plan. Finally, note that the pseudo-column `_tidb_rowid` is present in tables with a `NONCLUSTERED` primary key, whereas tables with a `CLUSTERED` primary key do not contain this column. ``` 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' ``` ## Summary `CLUSTERED` primary index provides TiDB the ability to organize tables in a way that can improve the performance of certain queries. To learn more about the `CLUSTERED` and `NONCLUSTERED` primary keys, please refer to the [TiDB Documentation](https://docs.pingcap.com/tidb/stable/clustered-indexes). ## End of Exercises