# Partitioned Table

### Overview
+ Through these exercises, you will learn to work with partitioned tables to improve query performance.
+ Partitioned tables can be classified into three types, they are hash-partitioned, range-partitioned and list-partitioned 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` as well as the row values present in the `rid` column of tables `test.t0` and `test.t1`.
+ 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.

### 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.

## Exercise 1: Hash-Partitioned Table

### Overview
+ Through this exercise, you will learn the characteristics of hash-partitioned tables, which ensures that data is evenly distributed among a specified number of partitions.
+ First, create a non-partitioned table and a hash-partitioned table with the same mock data populated. Then, analyze the execution plans for specific queries.

### Duration
+ This exercise should take you approximately 15 minutes to complete.

### Tasks
1. Connect to the TiDB server.
2. Create a hash-partitioned table.
3. Observe the behavior of the hash-partitioned table under different query predicates by checking the execution plans.

### Solution
1. Connect to the TiDB server. If the TiDB Playground Cluster is not running, run script `playground-start.sh` under folder `tidb-course-201-lab/scripts/` to start the Playground cluster:

   ```
   $ cd tidb-course-201-lab/scripts
   $ ./connect-4000.sh
   ...
   tidb:4000>
   ```

2. For comparison purposes, create a non-partitioned table named `test.t0`.
   
   ```sql
   DROP TABLE IF EXISTS test.t0;
   CREATE TABLE test.t0 (rid INT NOT NULL, gid INT NOT NULL);
   ```
   ```
   tidb:4000> DROP TABLE IF EXISTS test.t0;
   --------------
   DROP TABLE IF EXISTS test.t0
   --------------

   tidb:4000> CREATE TABLE test.t0 (rid INT NOT NULL, gid INT NOT NULL);
   --------------
   CREATE TABLE test.t0 (rid INT NOT NULL, gid INT NOT NULL)
   --------------

   Query OK, 0 rows affected (1.17 sec)
   ```

3. Create a table named `test.t1` using hash partitioning with 16 partitions without a primary key. The column named `rid` will serve as the hash key.

   ```sql
   DROP TABLE IF EXISTS test.t1;
   CREATE TABLE test.t1 (rid INT NOT NULL, gid INT NOT NULL)
   PARTITION BY HASH(rid)
   PARTITIONS 16;
   ```
   ```
   tidb:4000> DROP TABLE IF EXISTS test.t1;
   --------------
   DROP TABLE IF EXISTS test.t1
   --------------

   tidb:4000> CREATE TABLE test.t1 (rid INT NOT NULL, gid INT NOT NULL)
   -> PARTITION BY HASH(rid)
   -> PARTITIONS 16;
   --------------
   CREATE TABLE test.t1 (rid INT NOT NULL, gid INT NOT NULL)
   PARTITION BY HASH(rid)
   PARTITIONS 16
   --------------

   Query OK, 0 rows affected (1.09 sec)
   ```

4. Populate tables `test.t0` and `test.t1` with 524288 rows for each. Column `rid` will be populated with random integers.

   ```sql
   SOURCE 07-demo-populate-t0.sql
   SOURCE 07-demo-populate-t1.sql
   ```
   ```
   ...
   --------------
   ANALYZE TABLE test.t0
   --------------

   Query OK, 0 rows affected, 1 warning (1.60 sec)
   ...
   --------------
   ANALYZE TABLE test.t1
   --------------

   Query OK, 0 rows affected, 32 warnings (4.30 sec)
   ```

5. On `test.t1`, the given execution plan demonstrates that in order to execute the query, scanning only one partition in `test.t1` is sufficient.

   Pay attention to the `actRows` column of `TableFullScan - id` and the `access object` column of `TableReader - id` in the `EXPLAIN ANALYZE` output. It indicates that only 1/16th of the data was scanned, implying that it is not a `FULL TABLE SCAN`, but rather a `PARTITION SCAN`.
   ```sql
   PAGER less -S
   EXPLAIN ANALYZE SELECT * FROM test.t1 WHERE rid=7777777;
   ```
   ```
      +-------------------------+------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------->
      | id                      | estRows    | actRows | task      | access object | execution info                                                                                                       >
      +-------------------------+------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------->
      | TableReader_7           | 2.01       | 0       | root      | partition:p1  | time:28.7ms, loops:1, cop_task: {num: 1, max: 28.6ms, proc_keys: 32722, tot_proc: 28ms, rpc_num: 1, rpc_time: 28.6ms,>
      | └─Selection_6           | 2.01       | 0       | cop[tikv] |               | tikv_task:{time:28ms, loops:36}, scan_detail: {total_process_keys: 32722, total_process_keys_size: 1439752, total_key>
      |   └─TableFullScan_5     | 1048576.00 | 32722   | cop[tikv] | table:t1      | tikv_task:{time:27ms, loops:36}                                                                                      >
      +-------------------------+------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------->
      ```
   ```

   Hit `Q` to exit the `PAGER` view.

6. On `test.t0`, the given execution plan demonstrates that in order to execute the query, scanning the entire table in `test.t0` is required.
Pay attention to the `actRows` column of `TableFullScan - id` and the `access object` column of `TableReader - id` in the `EXPLAIN ANALYZE` output. It indicates that the entire table data was scanned (recall that the table has 524288 rows), implying that it is a `FULL TABLE SCAN`.
   ```sql
   EXPLAIN ANALYZE SELECT * FROM test.t0 WHERE rid=7777777;
   ```
   ```
   +-------------------------+-----------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------->
   | id | estRows | actRows | task | access object | execution info >
   +-------------------------+-----------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------->
   | TableReader_7 | 1.02 | 0 | root | | time:302.7ms, loops:1, cop_task: {num: 1, max: 302.3ms, proc_keys: 524288, tot_proc: 301ms, rpc_num: 1, rpc_time: 302.>
   | └─Selection_6 | 1.02 | 0 | cop[tikv] | | tikv_task:{time:302ms, loops:517}, scan_detail: {total_process_keys: 524288, total_process_keys_size: 23068292, total_>
   | └─TableFullScan_5 | 524288.00 | 524288 | cop[tikv] | table:t0 | tikv_task:{time:293ms, loops:517} >
   +-------------------------+-----------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------->
   ```

   Hit `Q` to exit the `PAGER` view.

7. Verify that the rows are evenly distributed among the partitions of `test.t1`. The example uses `p0`, `p6` and `p12` for partition names, you can choose any partition number between `0 `and `15`.

   ```sql
   NOPAGER
   SELECT COUNT(*) FROM test.t1 PARTITION (p0);
   SELECT COUNT(*) FROM test.t1 PARTITION (p6);
   SELECT COUNT(*) FROM test.t1 PARTITION (p12);
   ```
   ```
   tidb:4000> NOPAGER
   PAGER set to stdout
   tidb:4000> SELECT COUNT(*) FROM test.t1 PARTITION (p0);
   --------------
   SELECT COUNT(*) FROM test.t1 PARTITION (p0)
   --------------

   +----------+
   | COUNT(*) |
   +----------+
   | 32602 |
   +----------+
   1 row in set (0.02 sec)

   tidb:4000> SELECT COUNT(*) FROM test.t1 PARTITION (p6);
   --------------
   SELECT COUNT(*) FROM test.t1 PARTITION (p6)
   --------------

   +----------+
   | COUNT(*) |
   +----------+
   | 32661 |
   +----------+
   1 row in set (0.04 sec)

   tidb:4000> SELECT COUNT(*) FROM test.t1 PARTITION (p12);
   --------------
   SELECT COUNT(*) FROM test.t1 PARTITION (p12)
   --------------

   +----------+
   | COUNT(*) |
   +----------+
   | 32801 |
   +----------+
   1 row in set (0.03 sec)
   ```


8. Verify the correspondence between the partition and the TiKV region. Currently, there should be 16 regions for table `test.t1`. On the other hand, for the non-partitioned table `test.t0`, there should only be one region at the moment.

   ```sql
   SHOW TABLE test.t1 REGIONS\G
   SHOW TABLE test.t0 REGIONS\G
   ```
   ```
   tidb:4000> SHOW TABLE test.t1 REGIONS\G
   --------------
   SHOW TABLE test.t1 REGIONS
   --------------

   *************************** 1. row ***************************
   REGION_ID: 1083
   START_KEY: t_314_
   END_KEY: t_315_
   LEADER_ID: 1084
   LEADER_STORE_ID: 3
   PEERS: 1084
   SCATTERING: 0
   WRITTEN_BYTES: 0
   READ_BYTES: 0
   APPROXIMATE_SIZE(MB): 1
   APPROXIMATE_KEYS: 0
   SCHEDULING_CONSTRAINTS:
   SCHEDULING_STATE:
   .
   .
   .
   *************************** 16. row ***************************
   REGION_ID: 22
   START_KEY: t_329_
   END_KEY: t_281474976710651_
   LEADER_ID: 262
   LEADER_STORE_ID: 3
   PEERS: 262
   SCATTERING: 0
   WRITTEN_BYTES: 6737142
   READ_BYTES: 6562299
   APPROXIMATE_SIZE(MB): 1
   APPROXIMATE_KEYS: 0
   SCHEDULING_CONSTRAINTS:
   SCHEDULING_STATE:
   16 rows in set (0.03 sec)

   tidb:4000> SHOW TABLE test.t0 REGIONS\G
   --------------
   SHOW TABLE test.t0 REGIONS
   --------------

   *************************** 1. row ***************************
   REGION_ID: 1081
   START_KEY: t_310_
   END_KEY: t_314_
   LEADER_ID: 1082
   LEADER_STORE_ID: 3
   PEERS: 1082
   SCATTERING: 0
   WRITTEN_BYTES: 0
   READ_BYTES: 33554052
   APPROXIMATE_SIZE(MB): 45
   APPROXIMATE_KEYS: 680109
   SCHEDULING_CONSTRAINTS:
   SCHEDULING_STATE:
   1 row in set (0.01 sec)
   ```

## Exercise 2: Range-Partitioned Table

### Overview
+ Through this exercise, you will learn the characteristics of range-partitioned tables, which ensures that data is distributed by range values among a specified number of partitions.
+ First, create a range-partitioned table with mock data populated. Then, analyze the results of specific queries.

### Duration
+ This exercise should take you approximately 10 minutes to complete.

### Tasks
1. Create a range-partitioned table.
2. Observe the behavior of the range partitioned table.

### Solution
1. Create a range-partitioned table `test.t1` with four partitions, without any indexes on it. The column named `gid` will serve as the range key.

   ```sql
   DROP TABLE IF EXISTS test.t1;
   CREATE TABLE test.t1 (rid INT NOT NULL, gid INT NOT NULL) PARTITION BY RANGE (gid) (
   PARTITION p0 VALUES LESS THAN (4),
   PARTITION p1 VALUES LESS THAN (9),
   PARTITION p2 VALUES LESS THAN (14),
   PARTITION p3 VALUES LESS THAN (MAXVALUE));
   ```
   ```
   tidb:4000> DROP TABLE IF EXISTS test.t1;
   --------------
   DROP TABLE IF EXISTS test.t1
   --------------

   tidb:4000> CREATE TABLE test.t1 (rid INT NOT NULL, gid INT NOT NULL) PARTITION BY RANGE (gid) (
   -> PARTITION p0 VALUES LESS THAN (4),
   -> PARTITION p1 VALUES LESS THAN (9),
   -> PARTITION p2 VALUES LESS THAN (14),
   -> PARTITION p3 VALUES LESS THAN (MAXVALUE));
   --------------
   CREATE TABLE test.t1 (rid INT NOT NULL, gid INT NOT NULL) PARTITION BY RANGE (gid) (
   PARTITION p0 VALUES LESS THAN (4),
   PARTITION p1 VALUES LESS THAN (9),
   PARTITION p2 VALUES LESS THAN (14),
   PARTITION p3 VALUES LESS THAN (MAXVALUE))
   --------------

   Query OK, 0 rows affected (1.05 sec)
   ```


2. Populate mock data into the `test.t1` table with 524288 rows and verify that the distribution of row count with respect to `gid` is identical to the output displayed below.

   ```sql
   SOURCE 07-demo-populate-t1.sql
   SELECT gid, COUNT(*) FROM test.t1 GROUP BY gid ORDER BY 1;
   ```
   ```
   ...
   +-----+----------+
   | gid | COUNT(*) |
   +-----+----------+
   | 0 | 1 |
   | 1 | 19 |
   | 2 | 171 |
   | 3 | 969 |
   | 4 | 3876 |
   | 5 | 11628 |
   | 6 | 27132 |
   | 7 | 50388 |
   | 8 | 75582 |
   | 9 | 92378 |
   | 10 | 92378 |
   | 11 | 75582 |
   | 12 | 50388 |
   | 13 | 27132 |
   | 14 | 11628 |
   | 15 | 3876 |
   | 16 | 969 |
   | 17 | 171 |
   | 18 | 19 |
   | 19 | 1 |
   +-----+----------+
   20 rows in set (0.21 sec)
   ```


3. Please analyze the output of the query below, considering the partition schema design from step 1 and the `gid` distribution result from step 2. Can you provide an explanation as to why partition `p2` has the highest number of rows?

   ```sql
   SELECT 'P0_COUNT', COUNT(*) FROM test.t1 PARTITION (p0)
   UNION ALL
   SELECT 'P1_COUNT', COUNT(*) FROM test.t1 PARTITION (p1)
   UNION ALL
   SELECT 'P2_COUNT', COUNT(*) FROM test.t1 PARTITION (p2)
   UNION ALL
   SELECT 'P3_COUNT', COUNT(*) FROM test.t1 PARTITION (p3)
   ORDER BY 1;
   ```
   ```
   ...
   +----------+----------+
   | P0_COUNT | COUNT(*) |
   +----------+----------+
   | P0_COUNT | 1160 |
   | P1_COUNT | 168606 |
   | P2_COUNT | 337858 |
   | P3_COUNT | 16664 |
   +----------+----------+
   4 rows in set (0.16 sec)
   ```


4. Observe the following execution plan, pay attention to the `actRows` column of `TableFullScan - id` and the `access object` column of `TableReader - id`, as only one partition will be scanned since the rows of interest are within the same partition, partition `p3`, it contains the values from 15 to maximum.

   ```sql
   PAGER less -S
   EXPLAIN ANALYZE SELECT * FROM test.t1 WHERE gid BETWEEN 15 AND 18;
   ```
   ```
   +-------------------------+-----------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------->
   | id | estRows | actRows | task | access object | execution info >
   +-------------------------+-----------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------->
   | TableReader_7 | 6305.07 | 5035 | root | partition:p3 | time:27.7ms, loops:7, cop_task: {num: 5, max: 8.65ms, min: 4.08ms, avg: 5.5ms, p95: 8.65ms, max_proc_keys: 10136, p95_>
   | └─Selection_6 | 6305.07 | 5035 | cop[tikv] | | tikv_task:{proc max:8ms, min:4ms, avg: 5.2ms, p80:8ms, p95:8ms, iters:36, tasks:5}, scan_detail: {total_process_keys: >
   | └─TableFullScan_5 | 587292.00 | 16664 | cop[tikv] | table:t1 | tikv_task:{proc max:8ms, min:4ms, avg: 4.8ms, p80:8ms, p95:8ms, iters:36, tasks:5} >
   +-------------------------+-----------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------->
   ```

   Hit `Q` to exit the `PAGER` view.


5. Verify the correspondence between the partition and the TiKV region. Four regions are expected in the result.

   ```sql
   NOPAGER
   SHOW TABLE test.t1 REGIONS\G
   ```
   ```
   tidb:4000> NOPAGER
   PAGER set to stdout
   tidb:4000> SHOW TABLE test.t1 REGIONS\G
   --------------
   SHOW TABLE test.t1 REGIONS
   --------------
   ...
   *************************** 4. row ***************************
   REGION_ID: 22
   START_KEY: t_343_
   END_KEY: t_281474976710651_
   LEADER_ID: 262
   LEADER_STORE_ID: 3
   PEERS: 262
   SCATTERING: 0
   WRITTEN_BYTES: 42
   READ_BYTES: 2132968
   APPROXIMATE_SIZE(MB): 1
   APPROXIMATE_KEYS: 0
   SCHEDULING_CONSTRAINTS:
   SCHEDULING_STATE:
   4 rows in set (0.01 sec)
   ```


## Exercise 3: List-Partitioned Table (v6.1.0 and later)
### Overview
+ Through this exercise, you will learn the characteristics of list-partitioned tables.
+ First, create a list-partitioned table with mock data populated. Then, analyze the execution plans for specific queries.

### Duration
+ This exercise should take you approximately 10 minutes to complete.

### Tasks
1. Create a list-partitioned table.
2. Observe the behavior of the list-partitioned table under different query predicates by checking the execution plans.

### Solution
1. Enable list-partitioned table in TiDB server system variable.

   ```sql
   SET GLOBAL tidb_enable_list_partition = ON;
   SET SESSION tidb_enable_list_partition = ON;
   ```
   ```
   tidb:4000> SET GLOBAL tidb_enable_list_partition = ON;
   --------------
   SET GLOBAL tidb_enable_list_partition = ON
   --------------

   Query OK, 0 rows affected (0.05 sec)

   tidb:4000> SET SESSION tidb_enable_list_partition = ON;
   --------------
   SET SESSION tidb_enable_list_partition = ON
   --------------

   Query OK, 0 rows affected (0.00 sec)
   ```

2. Create a list-partitioned table `test.t1` with 7 partitions, without any indexes on it. The column named `gid` will serve as the list key.

   ```sql
   DROP TABLE IF EXISTS test.t1;
   CREATE TABLE test.t1 (rid INT NOT NULL, gid INT NOT NULL) PARTITION BY LIST (gid) (
   PARTITION p0 VALUES IN (9),
   PARTITION p1 VALUES IN (10),
   PARTITION p2 VALUES IN (5,8),
   PARTITION p3 VALUES IN (6,7),
   PARTITION p4 VALUES IN (12,13),
   PARTITION p5 VALUES IN (11,14),
   PARTITION p6 VALUES IN (0,1,2,3,4,15,16,17,18,19));
   ```
   ```
   tidb:4000> DROP TABLE IF EXISTS test.t1;
   --------------
   DROP TABLE IF EXISTS test.t1
   --------------

   tidb:4000> CREATE TABLE test.t1 (rid INT NOT NULL, gid INT NOT NULL) PARTITION BY LIST (gid) (
   -> PARTITION p0 VALUES IN (9),
   -> PARTITION p1 VALUES IN (10),
   -> PARTITION p2 VALUES IN (5,8),
   -> PARTITION p3 VALUES IN (6,7),
   -> PARTITION p4 VALUES IN (12,13),
   -> PARTITION p5 VALUES IN (11,14),
   -> PARTITION p6 VALUES IN (0,1,2,3,4,15,16,17,18,19));
   --------------
   CREATE TABLE test.t1 (rid INT NOT NULL, gid INT NOT NULL) PARTITION BY LIST (gid) (
   PARTITION p0 VALUES IN (9),
   PARTITION p1 VALUES IN (10),
   PARTITION p2 VALUES IN (5,8),
   PARTITION p3 VALUES IN (6,7),
   PARTITION p4 VALUES IN (12,13),
   PARTITION p5 VALUES IN (11,14),
   PARTITION p6 VALUES IN (0,1,2,3,4,15,16,17,18,19))
   --------------

   Query OK, 0 rows affected (0.58 sec)
   ```

3. Populate mock data into the `test.t1` table with 524288 rows and verify that the distribution of row count with respect to `gid` is identical to the output displayed below.

   ```sql
   SOURCE 07-demo-populate-t1.sql
   SELECT gid, COUNT(*) FROM test.t1 GROUP BY gid ORDER BY 1;
   ```
   ```
   ...
   +-----+----------+
   | gid | COUNT(*) |
   +-----+----------+
   | 0 | 1 |
   | 1 | 19 |
   | 2 | 171 |
   | 3 | 969 |
   | 4 | 3876 |
   | 5 | 11628 |
   | 6 | 27132 |
   | 7 | 50388 |
   | 8 | 75582 |
   | 9 | 92378 |
   | 10 | 92378 |
   | 11 | 75582 |
   | 12 | 50388 |
   | 13 | 27132 |
   | 14 | 11628 |
   | 15 | 3876 |
   | 16 | 969 |
   | 17 | 171 |
   | 18 | 19 |
   | 19 | 1 |
   +-----+----------+
   20 rows in set (0.08 sec)
   ```


4. Please analyze the output of the query below, considering the partition schema design from step 1 and the `gid` distribution result from step 2. Can you explain why partitions `p0` and `p1` have the highest number of rows even though they only cover single values, which are 9 and 10 respectively?

   ```sql
   SELECT 'P0_COUNT', COUNT(*) FROM test.t1 PARTITION (p0)
   UNION ALL
   SELECT 'P1_COUNT', COUNT(*) FROM test.t1 PARTITION (p1)
   UNION ALL
   SELECT 'P2_COUNT', COUNT(*) FROM test.t1 PARTITION (p2)
   UNION ALL
   SELECT 'P3_COUNT', COUNT(*) FROM test.t1 PARTITION (p3)
   UNION ALL
   SELECT 'P4_COUNT', COUNT(*) FROM test.t1 PARTITION (p4)
   UNION ALL
   SELECT 'P5_COUNT', COUNT(*) FROM test.t1 PARTITION (p5)
   UNION ALL
   SELECT 'P6_COUNT', COUNT(*) FROM test.t1 PARTITION (p6)
   ORDER BY 1;
   ```
   ```
   ...
   +----------+----------+
   | P0_COUNT | COUNT(*) |
   +----------+----------+
   | P0_COUNT | 92378 |
   | P1_COUNT | 92378 |
   | P2_COUNT | 87210 |
   | P3_COUNT | 77520 |
   | P4_COUNT | 77520 |
   | P5_COUNT | 87210 |
   | P6_COUNT | 10072 |
   +----------+----------+
   7 rows in set (0.08 sec)
   ```


## Summary
In addition to `HASH`, `RANGE`, and `LIST` partitioning, TiDB also supports various other partitioning options. For more information, please refer to the [TiDB Documentation](https://docs.pingcap.com/tidb/stable/partitioned-table).

## End of Exercises