## 模块 3: 分区表的全局唯一索引 (v8.5.0) 与索引类似，数据库中的表分区是一种用于加速数据检索操作的数据结构。当执行查询语句时，表分区使数据库能够快速找到满足查询条件的数据。与顺序扫描整个表（`TableFullScan`）相比，这可以节省时间，从而提高查询效率。 在引入全局索引之前，TiDB 为每个分区支持本地索引，这导致主键和唯一键必须包含分区键以确保数据唯一性。此外，在跨多个分区查询数据时，TiDB 需要扫描每个分区的数据才能返回结果。 为了解决上述问题，TiDB 引入了全局索引功能。全局索引使用单个索引覆盖整个表的数据，允许主键和唯一键在不包含所有分区键的情况下保持全局唯一性。此外，全局索引可以在单个操作中访问跨多个分区的索引数据，显著提高非分区键的查询性能，而不是在每个分区的本地索引中查找。 ### 步骤 1. 查看分区表的特征 + 在模块 1 中，我们已经创建了一个分区表 - `books`。它在 `id` 列上使用 8 个哈希分区。 + 通过执行以下 SQL 命令验证表分区。注意输出中的 `PARTITION BY` 子句。 ```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) ``` + 由于 `books` 表被分成 8 个分区，它至少有 8 个 TiKV regions 用于存储数据行。 ```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) ``` + 由于 `books` 表是基于主键 `id` 进行哈希分区的，数据行应该大致均匀地分布在 8 个分区中。执行以下查询来确认这一点。 ```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) ``` ### 步骤 2. 在分区表上添加全局二级索引 + 使用以下命令尝试在 `books` 表上创建全局唯一索引。例如，您想要确保书籍标题和类型的组合是唯一的。预期会出现**错误**。 ```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 ``` + 错误信息显示，您想要创建一个本地唯一索引（没有给出 `GLOBAL` 关键字），并且没有包含分区列（本地唯一索引必须包含分区列，在我们的例子中是 `id`），TiDB 无法执行。要解决此问题，您可以执行以下操作之一： > 在 `title` 和 `type` 上创建一个**本地唯一索引**，由于它是一个本地索引，您必须额外包含 `id` 列，尽管实际上您不需要在索引中包含 `id` 列。 或者 > 在 `title` 和 `type` 上创建一个**全局唯一索引**，不需要包含 `id`，它是一个单列索引。**这正是我们在本模块中讨论的新功能。** + 让我们再次尝试通过添加 `GLOBAL` 关键字来创建全局唯一索引，这次不应出现错误。 ```sql CREATE UNIQUE INDEX book_title_uk ON bookshop.books (title, type) GLOBAL; ``` ```output Query OK, 0 rows affected (3.06 sec) ``` + 通过查询 `information_schema.tidb_indexes` 表并检查输出中的 `is_global` 列，验证您的索引是否为 `GLOBAL`。 ```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) ``` ### 步骤 3. 全局唯一索引的典型场景 + 请记住，在 `books` 表中，`title` 和 `type` 的组合是唯一的。 + 让我们删除全局唯一索引 `book_title_uk` 并运行查询解释。请注意，所有分区都被扫描以获取结果。注意解释输出中 `access object` 列下的 `partition:all`。如果我们的数据集很大，这个操作在 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) ``` + 让我们在 `(type, title)` 上添加一个**本地**唯一索引，并再次运行查询解释。这次我们发现 `IndexRangeScan` 被用于所有分区。好消息是索引被利用来加速查询，坏消息是所有 8 个分区上的 8 个本地索引都被范围扫描以获取结果。对于 `type` 和 `title` 的唯一组合，这是不必要的。请注意解释输出中 `IndexLookUp` 行的 `access object` 列下的 `partition:all`。这比之前好，但仍不是我们案例的最佳解决方案。 ```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) ``` + 让我们将**本地**索引更改为**全局**唯一索引。 ```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) ``` + 运行以下查询以验证全局索引是否正常工作。您将看到在解释输出的 `access object` 列下访问了 `index:book_title_uk` 对象。操作是 `Point_Get`。没有在所有分区上进行 `IndexLookUp` 扫描，也没有在所有分区上进行 `TableFullScan`，只有一个 `Point_Get`，这是在数据集很大时该模块中最有效的查询。 ```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) ``` + 到此为止，您已经了解了使用全局唯一索引的典型场景。保持 mysql 客户端连接打开，并继续下一个模块。