前言

Github：https://github.com/HealerJean

博客：http://blog.healerjean.com

一、分区类型

• 删除性能问题：Range 分区、Range COLUMNS 分区、List 分区和 List COLUMNS 分区

• 写入数据打散：Hash 分区和 Key 分区

1、Range 分区

一个表按 Range 分区是指，对于表的每个分区中包含的所有行，按分区表达式计算的值都落在给定的范围内。Range 必须是连续的，并且不能有重叠，通过使用 VALUES LESS THAN 进行定义。

• 支持的分区键：整数、表达式返回整数

1）特点

• 基于单列：通常使用整数表达式作为分区键。
• 适合有序数据：如日期、时间戳、自增ID等。
• 支持快速删除：通过删除整个分区而不是逐行删除来高效地移除旧数据。
• 分区裁剪优化：查询优化器可以根据查询条件自动跳过不相关的分区，提高查询效率。

2）使用示例

a、按年份分区

• p0 分区包含了所有在 1990 年之前离职的员工记录。
• p1 分区包含了所有在 1991 年离职的员工记录。
• p2 分区包含了所有在 1992 年离职的员工记录。
• p3 分区包含了所有在 1993 年及以后离职的员工记录。

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    separated DATE
)
PARTITION BY RANGE (YEAR(separated)) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1992),
    PARTITION p2 VALUES LESS THAN (1993),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

b、按订单金额分区

• p0 分区包含了所有金额小于 100 的订单。
• p1 分区包含了所有金额在 100 到 499.99 之间的订单。
• p2 分区包含了所有金额在 500 到 999.99 之间的订单。
• p3 分区包含了所有金额大于等于 1000 的订单。

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (amount) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (500),
    PARTITION p2 VALUES LESS THAN (1000),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

3）适用场景

4）注意事项

2、Range COLUMNS 分区

Range COLUMNS 分区是 Range 分区的一种变体。你可以使用一个或者多个列作为分区键，分区列的数据类型可以是整数 (integer)、字符串（CHAR/VARCHAR），DATE 和 DATETIME。不支持使用任何表达式。

支持的分区键：任意类型（如 DATE、VARCHAR）

1）特点

2）示例

a、按日期分区（单列）

• 插入 log_date = '2024-01-15' 的记录会进入 p202401。
• 插入 log_date = '2024-02-15' 的记录会进入 p202402。
• 插入未来日期会进入 pmax。

CREATE TABLE logs (
    id INT,
    log_date DATE
)
PARTITION BY RANGE COLUMNS (log_date) (
    PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
    PARTITION p202402 VALUES LESS THAN ('2024-03-01'),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

b、按多列分区（地区 + 时间）

• 插入 region='North', sale_date='2024-06-01' → p_north_2024
• 插入 region='South', sale_date='2024-08-01' → p_south_2024
• 插入 region='East' 或其他未定义范围的值 → p_other

CREATE TABLE sales (
    sale_id INT,
    region VARCHAR(10),
    sale_date DATE
)
PARTITION BY RANGE COLUMNS (region, sale_date) (
    PARTITION p_north_2024 VALUES LESS THAN ('North', '2025-01-01'),
    PARTITION p_south_2024 VALUES LESS THAN ('South', '2025-01-01'),
    PARTITION p_other VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

3）适用场景

4）注意事项

3、RANGE INTERVAL 分区

RANGE INTERVAL 分区是 TiDB 提供的一种自动化分区策略，它允许你根据时间字段（如 DATE, DATETIME, TIMESTAMP）按照指定的时间间隔（如天、月、年）对表进行分区。这种分区方式非常适合于需要定期归档或删除旧数据的应用场景，例如日志记录、订单系统等。

1）核心特点：

• 自动创建新分区：当插入的数据超出现有分区范围时，数据库会自动创建新的分区。
• 简化分区管理：减少了手动定义每个分区的需求，降低了维护成本。
• 高效清理旧数据：通过简单的 ALTER TABLE ... DROP PARTITION 命令即可快速删除不再需要的数据。

2）使用示例

a、按月分区的日志表

假设我们有一个日志表，希望按月进行分区，并且能够自动创建新分区以存储未来的日志数据：

• 数据将根据 log_time 列按月分区；
• 当插入一条 log_time 超过 '2023-01-31' 的记录时，TiDB 自动创建一个新的分区来容纳这条记录。

CREATE TABLE logs (
    id INT,
    log_time DATETIME
)
PARTITION BY RANGE (INTERVAL log_time MONTH) (
    PARTITION p202301 VALUES LESS THAN ('2023-02-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

b、按年分区的订单表

如果我们有一个订单表，希望按年进行分区：

• 数据将根据 order_date 列按年份分区；
• 当插入一条 order_date 超过 '2023-12-31' 的记录时，TiDB 自动创建一个新的分区来容纳这条记录。

CREATE TABLE orders (
    order_id INT,
    order_date DATE
)
PARTITION BY RANGE (INTERVAL order_date YEAR) (
    PARTITION p2023 VALUES LESS THAN ('2024-01-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

3）应用场景

4）注意事项

4、List 分区

LIST 分区基于一个列的值是否属于预定义的值列表来对表进行分区。每个分区定义了一个包含若干个具体值的集合，只有当列的值在这个集合中时，该行数据才会被存储到对应的分区中。

1）特点：

• 基于单列：通常使用整数、枚举类型作为分区键。
• 适合离散值：如地区、状态、用户类型等。
• 支持快速删除：通过删除整个分区而不是逐行删除来高效地移除特定类别的数据。
• 分区裁剪优化：查询优化器可以根据查询条件自动跳过不相关的分区，提高查询效率。

2）使用示例

a、按地区分区

假设我们有一个销售记录表，希望按销售地区进行分区：

CREATE TABLE sales (
    sale_id INT,
    region VARCHAR(50),
    amount DECIMAL(10,2)
)
PARTITION BY LIST (region) (
    PARTITION p_north VALUES IN ('North', 'Northeast'),
    PARTITION p_south VALUES IN ('South', 'Southeast'),
    PARTITION p_west VALUES IN ('West', 'Northwest'),
    PARTITION p_other VALUES IN (DEFAULT)  -- 使用 DEFAULT 处理未列出的所有其他值
);

在这个例子中：

• p_north 分区包含了所有 region 为 'North' 或 'Northeast' 的记录。
• p_south 分区包含了所有 region 为 'South' 或 'Southeast' 的记录。
• p_west 分区包含了所有 region 为 'West' 或 'Northwest' 的记录。
• p_other 分区包含了所有未在前面分区中列出的 region 值。

b、按订单状态分区

假设我们有一个订单表，希望按订单状态进行分区：

CREATE TABLE orders (
    order_id INT,
    status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled')
)
PARTITION BY LIST (status) (
    PARTITION p_pending VALUES IN ('Pending'),
    PARTITION p_shipped VALUES IN ('Shipped'),
    PARTITION p_delivered VALUES IN ('Delivered'),
    PARTITION p_cancelled VALUES IN ('Cancelled')
);

在这个例子中：

• p_pending 分区包含了所有 status 为 'Pending' 的订单。
• p_shipped 分区包含了所有 status 为 'Shipped' 的订单。
• p_delivered 分区包含了所有 status 为 'Delivered' 的订单。
• p_cancelled 分区包含了所有 status 为 'Cancelled' 的订单。

3）适用场景

4）注意事项

5、LIST COLUMNS 分区

除了基本的 LIST 分区外，TiDB 还支持 LIST COLUMNS 分区，它可以处理多个列和非整数类型的列。

CREATE TABLE sales (
    sale_id INT,
    region VARCHAR(50),
    product_category VARCHAR(50)
)
PARTITION BY LIST COLUMNS (region, product_category) (
    PARTITION p_north_electronics VALUES IN (('North', 'Electronics'), ('Northeast', 'Electronics')),
    PARTITION p_south_clothing VALUES IN (('South', 'Clothing'), ('Southeast', 'Clothing')),
    PARTITION p_other VALUES IN (DEFAULT)
);

在这个例子中：

• p_north_electronics 分区包含了
  • 所有region 为 'North' 或 'Northeast'
  • 且 product_category 为 'Electronics' 的记录。
• p_south_clothing 分区包含了
  • 所有 region 为 'South' 或 'Southeast'
  • 且 product_category 为 'Clothing' 的记录。
• p_other 分区包含了所有未在前面分区中列出的组合值。

6、HASH 分区

HASH 分区基于一个表达式的哈希值来对表进行分区。每个插入的数据行都会根据该表达式的哈希结果被分配到特定的分区中。这种方式可以确保数据在各个分区之间均匀分布，从而提高查询性能和写入效率。

1）特点：

• 基于表达式：通常使用列名或返回整数的表达式作为分区键。
• 均匀分布：通过哈希算法保证数据均匀分布在各个分区中。
• 适合高并发写入：有助于分散写入负载，避免单一分区成为瓶颈（热点）。
• 支持快速查询：虽然不如 RANGE 或 LIST 分区那样可以通过条件直接裁剪分区，但对于某些查询仍能提供一定的优化。

2）使用示例

b、按用户 ID 哈希分区

假设我们有一个订单表，希望根据用户ID进行哈希分区，以确保订单数据均匀分布在多个分区中：

CREATE TABLE orders (
    order_id INT,
    user_id INT,
    amount DECIMAL(10,2)
)
PARTITION BY HASH (user_id)
PARTITIONS 4;

在这个例子中：

• 数据将根据 user_id 列的哈希值被分配到 4 个不同的分区中。
• 这种方式有助于平衡写入负载，避免某个分区成为写入热点。

c、按组合字段哈希分区

如果我们想基于多个字段进行哈希分区，比如用户 ID 和订单日期的组合：

• 虽然 HASH 分区不直接支持多列，但可以通过组合表达式实现类似效果。

CREATE TABLE orders (
    order_id INT,
    user_id INT,
    order_date DATE
)
PARTITION BY HASH (YEAR(order_date) * 10000 + MONTH(order_date) * 100 + DAY(order_date) + user_id)
PARTITIONS 8;

3）优势

• 均匀分布数据：HASH 分区通过哈希算法将数据均匀地分布到各个分区中，这对于大规模数据集来说非常重要，因为它可以帮助避免某些分区成为写入或查询的瓶颈。

• 避免写入热点：在高并发写入场景下，如果所有数据都写入同一个分区，可能会导致该分区成为写入热点，严重影响性能。HASH 分区可以有效地分散写入负载，避免这种情况的发生。

• 支持快速查询：尽管 HASH 分区不像 RANGE 或 LIST 分区那样可以直接通过条件裁剪分区，但在某些情况下，仍然可以通过索引优化查询性能。

4）适用场景

5）注意事项

7、KEY 分区

KEY 分区通过 TiDB 内置的哈希函数对指定的一个或多个列的值进行计算，并根据计算结果将数据分配到不同的分区中。这种方式特别适合用于需要自动且均匀地分布数据的场景，而不需要手动指定复杂的哈希表达式。

1）特点

• 基于内置哈希函数：TiDB 使用其内部的哈希算法来确定数据应存储在哪个分区。
• 支持多列：可以使用多个列作为分区键，提供了更大的灵活性。
• 均匀分布数据：通过内置算法确保数据尽可能均匀分布在各个分区中。
• 避免写入热点：有助于分散写入负载，防止单一分区成为瓶颈。

2）使用示例

a、按单列进行 KEY 分区

假设我们有一个订单表，希望根据订单ID进行 KEY 分区：

CREATE TABLE orders (
    order_id INT,
    user_id INT,
    amount DECIMAL(10,2)
)
PARTITION BY KEY (order_id)
PARTITIONS 4;

在这个例子中：

• 数据将根据 order_id 列的值，通过 TiDB 的内置哈希算法被分配到 4 个不同的分区中。

b、按多列进行 KEY 分区

如果我们想基于多个字段（如用户ID和订单日期）进行 KEY 分区：

CREATE TABLE orders (
    order_id INT,
    user_id INT,
    order_date DATE
)
PARTITION BY KEY (user_id, order_date)
PARTITIONS 8;

在这个例子中：

• 数据将根据 user_id 和 order_date 列的组合值，通过TiDB的内置哈希算法被分配到8个不同的分区中。

二、分区管理

1、操作概览

2、分区管理操作

1）添加分区（ADD PARTITION）

a、支持类型：

• RANGE / RANGE COLUMNS
• LIST / LIST COLUMNS
• HASH / KEY（添加分区数量）

b、示例：

-- Range 分区
ALTER TABLE members ADD PARTITION (PARTITION p2010 VALUES LESS THAN (2020));

-- List 分区
ALTER TABLE member_level ADD PARTITION (PARTITION l6 VALUES IN (6));

-- Hash 分区（添加一个分区）
ALTER TABLE t ADD PARTITION;

c、注意事项：

• 对于 Range 分区，新分区的 VALUES LESS THAN 值必须 大于最后一个分区的值。

  ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
  

2）删除分区（DROP PARTITION）

a、支持类型：

• RANGE / RANGE COLUMNS
• LIST / LIST COLUMNS
• HASH / KEY（不支持删除单个分区）

b、示例：

ALTER TABLE members DROP PARTITION p1990;
ALTER TABLE member_level DROP PARTITION l5;

c、注意事项：

• 删除分区会永久删除分区中的数据。
• 对于 Hash / Key 分区，只能通过 COALESCE 减少分区数量，不能单独删除某个分区。

3）清空分区（TRUNCATE PARTITION）

a、支持类型：

• 所有分区类型（RANGE / LIST / HASH / KEY）

b、示例：

ALTER TABLE members TRUNCATE PARTITION p1980;
ALTER TABLE member_level TRUNCATE PARTITION l4;

c、注意事项：

• 类似于 TRUNCATE TABLE，但只作用于指定的分区。
• 操作不可回滚，数据会被立即清除。

4）分区重组（REORGANIZE PARTITION）

a、支持类型：

• RANGE / RANGE COLUMNS
• LIST / LIST COLUMNS

b、 示例：

拆分分区：

ALTER TABLE members REORGANIZE PARTITION p1990to2010 INTO (
    PARTITION p1990 VALUES LESS THAN (2000),
    PARTITION p2000 VALUES LESS THAN (2010),
    PARTITION p2010 VALUES LESS THAN (2020)
);

合并分区：

ALTER TABLE members REORGANIZE PARTITION pBefore1950, p1950 INTO (
    PARTITION pBefore1960 VALUES LESS THAN (1960)
);

修改分区定义：

ALTER TABLE members REORGANIZE PARTITION pBefore1960, p1960 INTO (
    PARTITION pOld VALUES LESS THAN (1970)
);

c、注意事项：

• 只能对相邻的分区进行合并

  ERROR 8200 (HY000): Unsupported REORGANIZE PARTITION of RANGE; not adjacent partitions
  

• 新分区的范围或值集合必须能覆盖原分区中的所有数据，否则报错：

  ERROR 1526 (HY000): Table has no partition for value X
  

• 分区重组后，统计信息会过期，建议执行 ANALYZE TABLE 更新统计信息。

5）分区扩容/缩容（ADD / COALESCE）

支持类型：HASH / KEY 分区

扩容：

ALTER TABLE t ADD PARTITION;

缩容：

ALTER TABLE t COALESCE PARTITION 2;

注意事项：

• 操作会将所有数据重新分布到新的分区数量中。
• 是一个 全量重组操作，性能开销较大。

6）交换分区（EXCHANGE PARTITION）

一个分区与一个独立表进行“交换”

支持类型：所有分区类型

示例：

ALTER TABLE partitioned_table EXCHANGE PARTITION p1 WITH TABLE non_partitioned_table;

注意事项：

• 需要满足以下一致性要求：
  • 表结构相同（包括列、索引等）
  • 主键/唯一键一致
  • TiDB 特有功能一致：
    • Placement Rules（Placement Policy）一致
    • TiFlash 副本数量一致
    • 聚簇索引（CLUSTERED/NONCLUSTERED）一致
• 组件兼容性限制：
  • TiFlash：定义不一致时不能执行
  • TiCDC：只有主键/唯一键一致时才会同步
  • TiDB Lightning / BR：导入/恢复过程中不能执行 EXCHANGE

8）分区表转换为非分区表

ALTER TABLE members REMOVE PARTITIONING;

作用：

• 将一个分区表 转换为普通的非分区表。
• 保留所有数据和索引。
• 删除所有分区结构，并将数据合并到一个单一的表中。

注意事项：

1. 这是一个全量操作：会复制整个表的数据，并重建索引。
2. 会锁表（在线操作，性能受影响）：执行期间可能影响性能。
3. 不能回滚：一旦执行完成，无法恢复分区结构。
4. 适用于：
   • 不再需要分区功能时。
   • 分区结构不合理，想重新设计分区策略。
   • 迁移或归档数据后清理分区。

9）对现有表进行分区或重新分区

语法：

ALTER TABLE <table_name>
PARTITION BY <new_partition_type> (<partition_expr>)
<partition_definitions>
[UPDATE INDEXES (<index_list>)];

示例 1：将普通表转为 HASH 分区表

ALTER TABLE members PARTITION BY HASH(id) PARTITIONS 10;

示例 2：将普通表或分区表转为 RANGE 分区表

ALTER TABLE member_level PARTITION BY RANGE(level) (
    PARTITION pLow VALUES LESS THAN (1),
    PARTITION pMid VALUES LESS THAN (3),
    PARTITION pHigh VALUES LESS THAN (7),
    PARTITION pMax VALUES LESS THAN (MAXVALUE)
);

示例 3：同时指定索引类型（LOCAL / GLOBAL）

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY uidx12(col1, col2),
    UNIQUE KEY uidx3(col3)
);

ALTER TABLE t1 PARTITION BY HASH(col1) PARTITIONS 3
UPDATE INDEXES (uidx12 LOCAL, uidx3 GLOBAL);

作用：

1. 对非分区表进行分区。
2. 对已有分区表更改分区类型或分区数量。
3. 支持指定索引为 LOCAL（每个分区一个索引） 或 GLOBAL（全局索引）。

注意事项：

1. 全量操作：会复制整个表的数据，并重建索引。
2. 性能开销大：尤其在大表上，执行时间较长。
3. 索引类型影响查询性能：
   • LOCAL：索引按分区存储，适合分区裁剪。
   • GLOBAL：全局索引，适合跨分区查询。
4. 索引更新必须在分区语句中显式指定，否则默认行为可能不符合预期。
5. 不支持在临时表上进行分区操作

3、分区的约束和限制

1）不支持 [ ALTER TABLE ... CHANGE COLUMN] 更改分区表的列类型

这意味着如果你有一个分区表，并且希望更改某一列的数据类型，你将无法直接通过 ALTER TABLE ... CHANGE COLUMN 来实现。这是因为分区表的结构变更涉及到数据重分布等复杂操作，TiDB 目前不支持这种类型的变更。

解决方法：

• 如果必须改变列类型，可以考虑创建一个新表，然后将旧表的数据迁移过去。
• 或者，如果可能的话，重新设计你的数据库模式以避免需要进行此类变更。

2）分区与 TiDB 的临时表功能不兼容

这意味着你不能将分区表定义为临时表，也不能基于临时表创建分区表。临时表主要用于会话或事务级别的短期存储，而分区表则用于更长期的数据组织和管理。

解决方法：对于需要临时存储的数据，考虑使用常规的非分区表作为临时解决方案。

3）不支持在分区表上创建外键

外键用于强制参照完整性，即确保一个表中的值必须匹配另一个表中的值。然而，在 TiDB 分区表中，由于其分布式架构的特点，目前不支持外键约束。

解决方法：在应用层面上实现类似外键的功能，比如通过触发器或者应用程序逻辑来保证数据的一致性。

5）ORDER_INDEX Hint 对分区表及其相关索引不生效

这个提示用于指示数据库按照索引顺序读取数据。但是，由于分区表的特殊性质，特别是当涉及到跨多个节点的数据分布时，即使每个分区内部都按索引顺序读取了数据，合并后的整体结果集不一定还是有序的。因为分区之间是并行读取的，顺序是不确定的。

解决方法：

• 如果你需要有序的数据输出，可以在查询后对结果集进行排序（例如使用 ORDER BY 子句），而不是依赖于索引顺序读取。

  • SELECT * FROM partitioned_table ORDER BY create_time;
    

4、分区裁剪

分区裁剪是一种查询优化技术，只访问与查询条件匹配的分区，跳过不相关的分区，从而减少扫描的数据量，提升查询性能。

原理：

• 每个分区都有明确的边界（如 VALUES LESS THAN (100)）。
• 查询优化器会根据 WHERE 条件判断哪些分区可能包含匹配的数据。
• 跳过那些不可能包含数据的分区，从而减少 I/O 和计算开销。

如何验证是否触发分区裁剪？：使用 EXPLAIN 查看执行计划：

EXPLAIN SELECT * FROM t1 WHERE region_code > 125 AND region_code < 130;

输出中如果显示：说明只扫描了 p1 和 p2 分区，裁剪成功

TableReader (table=t1)
└─Selection
  └─TableScan (table=t1, partitions=[p1, p2])

1）分区裁剪生效的关键条件

2）分区裁剪只在特定条件下生效

a、裁剪发生在查询计划生成阶段，不是执行阶段

• 查询计划生成时，优化器需要静态地判断哪些分区可能命中。

• 如果查询条件依赖于执行阶段才能确定的值（比如子查询、变量等），优化器无法提前判断哪些分区需要扫描。

  • -- 错误
    SELECT * FROM t2 WHERE x < (SELECT x FROM t1 WHERE t2.x < t1.x);
    

b、查询条件必须能下推到分区表

• 在 JOIN 查询中，如果条件不能下推到分区表所在的表，就无法裁剪。
• 优化器需要知道这个条件作用于哪个表。

正例：优化器推导出 t1.x > 5，因此可以裁剪分区。

SELECT * FROM t1 LEFT JOIN t2 ON t1.x = t2.x WHERE t2.x > 5;

反例：t2.x > 5 是 ON 条件的一部分，没有下推到 t1，无法裁剪。

SELECT * FROM t1 LEFT JOIN t2 ON t1.x = t2.x AND t2.x > 5;

c、函数表达式必须是单调函数

• 分区表达式可以是列，也可以是函数，但必须是单调函数，否则无法判断分区边界。
• 单调函数是指：如果 x > y，那么 fn(x) > fn(y)（或 >=）
  • TO_DAYS()
  • UNIX_TIMESTAMP()
  • EXTRACT(YEAR FROM date)、EXTRACT(HOUR FROM time) 等
  • 特殊处理：FLOOR(UNIX_TIMESTAMP(ts))

d、RANGE 分区对表达式有要求

• RANGE 分区依赖明确的边界（如 VALUES LESS THAN (100)）。
• 如果分区表达式不是单调函数，无法判断哪些分区的边界可以包含查询条件。

✅ 举例：TO_DAYS() 是单调函数，可以判断 log_time > '2023-01-01' 会落在哪些分区。

PARTITION BY RANGE (TO_DAYS(log_time))

❌ 举例：每次执行结果不同，无法判断数据属于哪个分区，裁剪失效。

PARTITION BY RANGE (FLOOR(RAND() * 100))

5、分区选择

分区选择是指在 SELECT、INSERT ... SELECT 等语句中，显式指定要访问的分区，从而跳过其他分区。

1）分区选择的用途

2）应用场景建议

6、分区键、主键与唯一键

索引
├── 主键（Primary Key）
│   └── 默认是聚簇索引（Clustered Index）
│
└── 二级索引（Secondary Index）
    ├── 普通索引（Non-Unique）
    ├── 唯一索引（Unique Index）
    │
    └── （在分区表中）按作用域分为：
        ├── 本地索引（Local Index） ← 默认？否！TiDB 默认是 GLOBAL
        └── 全局索引（Global Index） ← ✅ TiDB 推荐且默认
            ├── 普通全局索引
            └── 唯一全局索引（用于跨分区唯一约束）

1）本地索引必须包含分区键？

在分区表中，所有的唯一键（包括主键）必须包含分区表达式中使用的所有列，但全局索引可以不包含分区键。

为什么本地索引必须包含分区键？？

• 本地索引（Local Index） 是每个分区独立维护的。
• 如果索引列不包含分区键，数据库无法知道该去哪个分区查数据。
• 所以：本地索引通常要求查询条件包含分区键，否则会全分区扫描（性能差）。

不合法示例：

• UNIQUE KEY (col1, col2) 没有包含分区键 col3
• 无法保证不同分区中 (col1, col2) 的唯一性 → 报错

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

合法示例：

• 唯一键包含分区键 col3，可以保证全局唯一性 → 合法

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

2）主键与分区键的关系

主键本质上是唯一且非空的键，所以也必须满足上述规则。

不合法示例：主键 (col1, col2) 不包含分区键 col3 → 报错

CREATE TABLE t5 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

合法修改：主键包含分区键 col3 → 合法

CREATE TABLE t5 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

3）多个唯一键与分区键的关系

如果表中存在多个唯一键，每个唯一键都必须包含分区键。

报错示例：

• 分区键为 col1 + col3
• 两个唯一键都没有包含所有分区键列 → 报错

CREATE TABLE t3 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2),
    UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

修改后合法：每个唯一键都包含分区键 col1 + col3 → 合法

CREATE TABLE t3 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3),
    UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

4）全局索引

为了解决上述限制，TiDB 从 v8.3.0 开始引入 全局索引（Global Index）。

1. 无需唯一键包含分区键，即可保证全局唯一性。
2. 跨分区查询效率更高：只需访问一次索引，而非多个分区的局部索引。
3. 支持更灵活的分区策略。

a、语法示例：

• uidx12 是全局索引，可以不包含分区键
• uidx3 是局部索引，仍需包含分区键

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY uidx12(col1, col2) GLOBAL,
    UNIQUE KEY uidx3(col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

b、全局索引的限制

（1）未显式指定 GLOBAL → 默认是局部索引

（2）GLOBAL / LOCAL 对非分区表无意义

• 原因：非分区表只有一个物理存储结构，不存在“分区”的概念，所以索引自然就是“全局”的。
• 影响：在非分区表中使用 GLOBAL 或 LOCAL 关键字不会报错，但不会产生任何实际效果。

（3）仅支持唯一列创建全局索引

• 原因：全局索引需要保证全局唯一性，只有唯一索引才能满足这一要求。

• 非唯一列的解决方法：结合主键组成复合唯一索引。

  • ALTER TABLE t ADD UNIQUE INDEX idx_col3_pk (col3, id) GLOBAL;
    

  • col3 是非唯一列，但与主键 id 组成复合唯一索引，可以创建全局索引。

（4）某些 DDL 操作会触发全局索引更新

• 影响：这些操作的执行时间会变长，尤其是当索引数据量大时。

（5）不支持 EXCHANGE PARTITION

• 原因：EXCHANGE PARTITION 是将一个分区与一个独立表进行“交换”，但全局索引跨多个分区，无法单独交换。
• 影响：在需要频繁进行分区交换的场景中，不能使用全局索引。

（6） 主键默认是聚簇索引，不能是全局索引

• 原因：
  • 聚簇索引决定了表的物理存储结构。
  • 分区表的聚簇索引必须包含分区键，以保证分区归属明确。
  • 全局索引是表级别的，与分区结构冲突。
• 解决方法：使用 NONCLUSTERED GLOBAL 显式指定主键为非聚簇的全局索引。

CREATE TABLE t (
    id INT NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY (id) NONCLUSTERED GLOBAL
)
PARTITION BY RANGE (id) (...);

二、FAQ

1、RANGE COLUMNS 与 RANGE 的区别

2、 HASH 分区 vs KEY 分区

3、MAXVALUE 是个啥？

• MAXVALUE 并不是 SQL 中的常量或函数，而是一个关键字；
• 它在分区定义中表示“无穷大”，用于定义最后一个分区，确保所有未匹配到前面分区的数据都能被容纳；
• 它适用于任何数据类型，包括字符串（CHAR / VARCHAR）、日期（DATE / DATETIME）等。

CREATE TABLE logs (
    log_id INT,
    region VARCHAR(10),
    log_date DATE
)
PARTITION BY RANGE COLUMNS (region, log_date) (
    PARTITION p_north VALUES LESS THAN ('North', '2025-01-01'),
    PARTITION p_south VALUES LESS THAN ('South', '2025-01-01'),
    PARTITION p_max VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

4、分区对 NULL 值的处理