## Module 3: Global Unique Indexes for Partitioned Tables (v8.5.0) Similar to indexes, table partitions in databases are data structures designed to accelerate data retrieval operations. When a query statement is executed, table partitions enable the database to quickly find data that meets the query conditions. This leads to time savings as opposed to scanning the entire table sequentially (`TableFullScan`), thus enhancing query efficiency. Before the introduction of global indexes, TiDB supports local indexes for each partition, leading to a limitation that primary keys and unique keys had to include the partition key to ensure data uniqueness. Additionally, when querying data across multiple partitions, TiDB needed to scan the data of each partition to return results. To address above issues, TiDB introduces the global indexes feature. A global index covers the data of the entire table with a single index, allowing primary keys and unique keys to maintain global uniqueness without including all partition keys. Moreover, global indexes can access index data across multiple partitions in a single operation, significantly improving query performance for non-partitioned keys instead of looking up in one local index for each partition. ### Step 1. Review the characteristics of a partitioned table + In module 1, we already created a partitioned table - `books`. It is partitioned on column `id` with 8 hash partitions. + Verify the table partitions by executing following SQL commands. Note the `PARTITION BY` clause in the output. ```sql SHOW CREATE TABLE books\G ``` ```output *************************** 1. row *************************** Table: books Create Table: CREATE TABLE `books` ( `id` bigint NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */, `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `type` enum('Magazine','Novel','Life','Arts','Comics','Education & Reference','Humanities & Social Sciences','Science & Technology','Kids','Sports') COLLATE utf8mb4_unicode_ci NOT NULL, `published_at` datetime NOT NULL, `stock` int DEFAULT '0', `price` decimal(15,2) DEFAULT '0.0', PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=30002 */ PARTITION BY HASH (`id`) PARTITIONS 8 1 row in set (0.00 sec) ``` ```sql SELECT table_name, partition_name, partition_method FROM information_schema.partitions WHERE table_name='books'; ``` ```output +------------+----------------+------------------+ | table_name | partition_name | partition_method | +------------+----------------+------------------+ | books | p0 | HASH | | books | p1 | HASH | | books | p2 | HASH | | books | p3 | HASH | | books | p4 | HASH | | books | p5 | HASH | | books | p6 | HASH | | books | p7 | HASH | +------------+----------------+------------------+ 8 rows in set (0.01 sec) ``` + Because `books` table is partitioned by 8 partitions, it at least has 8 TiKV regions for storing rows. ```sql SHOW TABLE books REGIONS; ``` ```output +-----------+-----------+---------+-----------+-----------------+---------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+ | REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS | SCHEDULING_CONSTRAINTS | SCHEDULING_STATE | +-----------+-----------+---------+-----------+-----------------+---------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+ | 286 | t_119_ | t_120_ | 287 | 1 | 287, 288, 289 | 0 | 0 | 1301 | 1 | 8 | | | | 290 | t_120_ | t_121_ | 291 | 1 | 291, 292, 293 | 0 | 39 | 1321 | 1 | 0 | | | | 294 | t_121_ | t_122_ | 295 | 1 | 295, 296, 297 | 0 | 0 | 1329 | 1 | 44 | | | | 298 | t_122_ | t_123_ | 299 | 1 | 299, 300, 301 | 0 | 39 | 1332 | 1 | 8 | | | | 302 | t_123_ | t_124_ | 305 | 2 | 303, 304, 305 | 0 | 39 | 1374 | 1 | 125 | | | | 306 | t_124_ | t_125_ | 307 | 1 | 307, 308, 309 | 0 | 0 | 1278 | 1 | 2 | | | | 310 | t_125_ | t_126_ | 313 | 2 | 311, 312, 313 | 0 | 0 | 1231 | 1 | 14 | | | | 314 | t_126_ | t_128_ | 315 | 1 | 315, 316, 317 | 0 | 39 | 1248 | 1 | 9 | | | +-----------+-----------+---------+-----------+-----------------+---------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+ 8 rows in set (0.02 sec) ``` + Since `books` table is hash partitioned on its primary key - `id`, the rows should be approximately evenly distributed across 8 partitions. Execute the following query to confirm that. ```sql SELECT 'P0_COUNT', COUNT(1) FROM books PARTITION (p0) UNION ALL SELECT 'P1_COUNT', COUNT(1) FROM books PARTITION (p1) UNION ALL SELECT 'P2_COUNT', COUNT(1) FROM books PARTITION (p2) UNION ALL SELECT 'P3_COUNT', COUNT(1) FROM books PARTITION (p3) UNION ALL SELECT 'P4_COUNT', COUNT(1) FROM books PARTITION (p4) UNION ALL SELECT 'P5_COUNT', COUNT(1) FROM books PARTITION (p5) UNION ALL SELECT 'P6_COUNT', COUNT(1) FROM books PARTITION (p6) UNION ALL SELECT 'P7_COUNT', COUNT(1) FROM books PARTITION (p7) ORDER BY 1; ``` ```output +----------+----------+ | P0_COUNT | COUNT(1) | +----------+----------+ | P0_COUNT | 12 | | P1_COUNT | 13 | | P2_COUNT | 13 | | P3_COUNT | 13 | | P4_COUNT | 13 | | P5_COUNT | 12 | | P6_COUNT | 12 | | P7_COUNT | 12 | +----------+----------+ 8 rows in set (0.00 sec) ``` ### Step 2. Add a global secondary index on a partitioned table + Use the following command to try to create a global unique index on table `books`. For example, you want to ensure that the book title and type combinations are unique. An **ERROR** is expected. ```sql CREATE UNIQUE INDEX book_title_uk ON bookshop.books (title, type); ``` ```output ERROR 8264 (HY000): Global Index is needed for index 'book_title_uk', since the unique index is not including all partitioning columns, and GLOBAL is not given as IndexOption ``` + The error message shows that you want to create a local unique index (no `GLOBAL` keyword is given) and you did not include the partitioning column (local unique index must include the partitioning column(s), which is `id` for our case), TiDB can not comply the execution. To solve this issue, you can do one of the followings: > Create a **local unique index** on `title` and `type`, since it is a local index you have to additionally include column `id`, despite you don't actually need the `id` column in the index. OR > Create a **global unique index** on `title` and `type`, no need to include `id`, it is a single column index. **This is exactly the new feature we are discussing in the module.** + Let's try to create the global unique index again by adding the `GLOBAL` keyword, this time no error is expected. ```sql CREATE UNIQUE INDEX book_title_uk ON bookshop.books (title, type) GLOBAL; ``` ```output Query OK, 0 rows affected (3.06 sec) ``` + Verity that your index is `GLOBAL` by querying `information_schema.tidb_indexes` table and check the `is_global` column in the output. ```sql SELECT key_name, seq_in_index, column_name, clustered, is_global FROM information_schema.tidb_indexes WHERE table_name='books'; ``` ```output +---------------+--------------+-------------+-----------+-----------+ | key_name | seq_in_index | column_name | clustered | is_global | +---------------+--------------+-------------+-----------+-----------+ | PRIMARY | 1 | id | YES | 0 | | book_title_uk | 1 | title | NO | 1 | | book_title_uk | 2 | type | NO | 1 | +---------------+--------------+-------------+-----------+-----------+ 3 rows in set (0.00 sec) ``` ### Step 3. Typical scenarios for global unique indexes + Keep in mind that the combination of `title` and `type` is unique in `books` table. + Let's drop the global unique index `book_title_uk` and run the query explanation. Note that all partitions are scanned for the result. Pay attention to the `partition:all` under the `access object` column in the explain output. In case our dataset is huge, this operation is expensive regarding IO. ```sql DROP INDEX book_title_uk ON bookshop.books; EXPLAIN SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology' AND title = 'Play That Funky Music'; ``` ```output Query OK, 0 rows affected (0.14 sec) +---------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------+ | TableReader_11 | 0.00 | root | partition:all | data:Projection_5 | | └─Projection_5 | 0.00 | cop[tikv] | | bookshop.books.stock, bookshop.books.price | | └─Selection_10 | 0.00 | cop[tikv] | | eq(bookshop.books.title, "Play That Funky Music"), eq(bookshop.books.type, "Science & Technology") | | └─TableFullScan_9 | 100.00 | cop[tikv] | table:books | keep order:false, stats:pseudo | +---------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) ``` + Let's add a **local** unique index on the `(type, title)`, and run the query explanation again. This time we find out that `IndexRangeScan` is used on all partitions. The good news is indexes are leveraged to speed up the query, the bad news is all 8 local indexes on 8 partitions are range scanned for the results. For the `type` and `title` unique combination, this is not necessary. Pay attention to the `partition:all` under the `access object` column on the row of `IndexLookUp` in the explain output. It's better than before, but still not the best solution for our case. ```sql CREATE UNIQUE INDEX book_title_idx ON bookshop.books (type, title, id); EXPLAIN SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology' AND title = 'Play That Funky Music'; ``` ```output Query OK, 0 rows affected (2.98 sec) +---------------------------------+---------+-----------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+---------+-----------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_4 | 0.00 | root | | bookshop.books.stock, bookshop.books.price | | └─IndexLookUp_8 | 0.00 | root | partition:all | | | ├─IndexRangeScan_6(Build) | 0.00 | cop[tikv] | table:books, index:book_title_idx(type, title, id) | range:["Science & Technology" "\x0f\xa7\x0f.\x0e3\x10^\x02\t\x10\x02\x0e\xe1\x0e3\x10\x02\x02\t\x0e\xb9\x10\x1f\x0fd\x0f!\x10^\x02\t\x0f[\x10\x1f\x0f\xea\x0e\xfb\x0e`","Science & Technology" "\x0f\xa7\x0f.\x0e3\x10^\x02\t\x10\x02\x0e\xe1\x0e3\x10\x02\x02\t\x0e\xb9\x10\x1f\x0fd\x0f!\x10^\x02\t\x0f[\x10\x1f\x0f\xea\x0e\xfb\x0e`"], keep order:false, stats:pseudo | | └─TableRowIDScan_7(Probe) | 0.00 | cop[tikv] | table:books | keep order:false, stats:pseudo | +---------------------------------+---------+-----------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) ``` + Let's change the **local** index to **global** unique index. ```sql DROP INDEX book_title_idx ON bookshop.books; CREATE UNIQUE INDEX book_title_uk ON bookshop.books (title, type) GLOBAL; ``` ```output Query OK, 0 rows affected (0.17 sec) Query OK, 0 rows affected (2.38 sec) ``` + Run the following query to verity that the global index is now working. You will see that `index:book_title_uk` object is accessed under `access object` column in the explain output. And the operation is a `Point_Get`. No `IndexLookUp` scans on all partitions, neither `TableFullScan` on all partitions, just one `Point_Get`, which is the most efficient query in this module, especially when the dataset is huge. ```sql EXPLAIN SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology' AND title = 'Play That Funky Music'; ``` ```output +-------------+---------+------+-----------------------------------------------+---------------+ | id | estRows | task | access object | operator info | +-------------+---------+------+-----------------------------------------------+---------------+ | Point_Get_1 | 1.00 | root | table:books, index:book_title_uk(title, type) | | +-------------+---------+------+-----------------------------------------------+---------------+ 1 row in set (0.00 sec) ``` + At this point, you have learned the typical scenario for using the global unique index. Keep the mysql client connection open, and proceed to the next module.