前言

Github：https://github.com/HealerJean

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

一、数据类型

1、介绍

1）数值类型

建议使用场景：

• 金额、计数、状态码等使用 DECIMAL 或整数类型；
• 不需要精确计算时可用 FLOAT 或 DOUBLE。

2）日期和时间类型

• 需要大范围时间存储用 DATETIME；
• 只需要日期或时间部分用 DATE 或 TIME。

3）字符串类型

4）枚举与集合类型

• 状态字段（如性别、订单状态）用 ENUM；

• 权限、标签等多选字段用 SET。

5）JSON 类型

• 动态字段、嵌套结构、配置信息等；
• 不适合频繁查询或索引的字段。

JSON 文档中的每个值都属于一种特定的数据类型。可以通过 [JSON_TYPE]的输出结果查看

6）空间数据类型

• 地理信息系统（GIS）、地图数据、空间查询等。

7）布尔类型

2、规范建议

1）表-字段选择建议

2）建表注意事项

二、创建表

1、选择主键

在数据库中，主键（Primary Key） 是用于唯一标识每一行数据的一列或多列组合。其基本特性包括：

• 唯一性（Unique）：主键值在整个表中必须是唯一的。
• 非空性（Not Null）：主键列不允许为 NULL。

1）TiDB 与 InnoDB 的主键差异

a、主键不默认聚簇索引

• 在 InnoDB 中，主键自动成为聚簇索引（Clustered Index），即数据按照主键顺序物理存储。
• 在 TiDB 中，主键不默认是聚簇索引。你可以通过以下关键字显式指定：
  • CLUSTERED：表示该主键是聚簇索引。
  • NONCLUSTERED：表示该主键不是聚簇索引，但它仍然是一个索引（准确地说，是一个二级索引）

如果不指定，默认由系统变量控制：这个变量有三种取值：

• ON：所有主键都默认是聚簇索引。
• OFF：所有主键都默认是非聚簇索引。
• INT_ONLY：只有整数类型的主键默认是聚簇索引。

CREATE TABLE t1 (
    id INT PRIMARY KEY CLUSTERED,
    name VARCHAR(255)
);

--  @@global.tidb_enable_clustered_index

b、Tidb 主键为啥不默认聚簇索引呢

在传统的单机数据库如 MySQL 的 InnoDB 存储引擎中，使用聚簇索引来组织数据能够带来较快的数据访问速度，因为相关的数据被物理地存储在一起，这减少了磁盘 I/O 操作。然而，在分布式数据库环境中，像TiDB这样设计的选择则需要考虑到更多的因素。

（1）分布式架构的需求：

• TiDB是一个分布式数据库系统，旨在支持水平扩展、高可用性和容错能力。在这种环境下，数据和负载都需要均匀分布到多个节点上以实现最佳性能和资源利用。
• 如果主键默认采用聚簇索引的方式，可能会导致某些节点上的负载过重（热点问题），特别是在大量写入的情况下，因为数据会按照主键的顺序写入，容易集中在特定的区域。

（2）灵活性与性能平衡：通过允许用户显式地选择是否使用聚簇索引（CLUSTERED 或 NONCLUSTERED ），TiDB 提供了更高的灵活性。对于某些应用场景来说，非聚簇索引可能更适合其工作负载和数据访问模式。例如，

• 在读多写少的场景：可能不需要聚簇索引带来的快速数据访问优势；
  • 虽然非聚簇索引需要额外的 I/O 来访问数据，但如果查询模式能够很好地利用索引，总体性能影响不大。更重要的是，避免了因维护聚簇索引而带来的额外写入开销和复杂性。
• 写密集型的场景：避免聚簇索引有助于减少写入时的竞争和冲突。
  • 对于频繁进行插入、更新或删除操作的应用程序，避免使用聚簇索引可以减少写入时的竞争和冲突。由于写操作不会强制要求数据按特定顺序存放，这样就能更高效地处理高并发的写入请求，降低锁定和等待的时间

c、隐式主键 _tidb_rowid

如果一张表没有定义任何主键，TiDB 会自动生成一个隐藏的 _tidb_rowid 列作为隐式主键。

• 特点：类型为 BIGINT UNSIGNED，自动递增，可以用于查询，但不能手动修改或插入

• 问题：写入热点问题：_tidb_rowid 是单调递增的，大量并发写入时会导致所有写操作集中在单个 Region 上，形成写入热点（Hotspot）。

2）解决写入热点的方法

a：打散 _tidb_rowid

适用于没有显式主键的情况：

• 此方法可能导致读放大（Read Amplification），需权衡利弊。

• SHARD_ROW_ID_BITS：打散 _tidb_rowid 的高位，使写入分布到多个 Region。
• PRE_SPLIT_REGIONS：预切分多个 Region，为写入做准备，提升并发写入能力。

示例：

CREATE TABLE t1 (
    name VARCHAR(255)
) SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS = 3;

b：使用 AUTO_RANDOM 替代 AUTO_INCREMENT

如果你使用的是 整数类型主键 + AUTO_INCREMENT，仍然可能出现热点问题。

• AUTO_RANDOM 生成的 ID 不连续，不适合对连续性有要求的场景。
• AUTO_RANDOM 生成的 ID 是随机分布的，可以有效避免写入热点。

• 使用 AUTO_RANDOM 替换 AUTO_INCREMENT：

CREATE TABLE t1 (
    id BIGINT PRIMARY KEY AUTO_RANDOM,
    name VARCHAR(255)
);

3）_tidb_rowid 热点解决：

a、 SHARD_ROW_ID_BITS = N 分桶

• 作用：将 _tidb_rowid 的高位进行哈希分片，从而将写入分布到多个不同的 Region 中。

• 原理：

• 默认 _tidb_rowid 是单调递增的整数（类似 AUTO_INCREMENT）。

• 设置 SHARD_ROW_ID_BITS = N 后，TiDB 会将 _tidb_rowid 的高位进行 2^N 个桶的哈希。

• 这样生成的 _tidb_rowid 就不再连续，而是分布在 2^N 个不同的范围内，从而避免所有写入集中在同一个 Region。

• 注意：

  • N 最大为 6，也就是说最多可以分 2^6 = 64 个桶。

  • 分桶越多，写入越分散，但也可能导致读放大（因为数据更分散）。

b、PRE_SPLIT_REGIONS = M 切分 Region

作用：在建表时预切分 Region，提高写入并发能力。

原理：

• TiDB 表的数据在底层存储（TiKV）中是以 Region 为单位进行管理的。
• 默认一个表初始只有一个 Region，所有写入都集中在这个 Region。
• 设置 PRE_SPLIT_REGIONS = M 后，TiDB 会在建表时将表的 Region 预切分为 M 个，这样写入可以一开始就分布到多个 Region 上。

注意：

• M 最大为 1 << 20（即 1048576），但实际推荐值一般在 16 到 128 之间。
• 切分太多 Region 会增加管理开销，建议根据写入并发量和集群规模合理设置。

示例：

• 会将 _tidb_rowid 打散成 16 个桶，并预切分为 8 个 Region。
• 数据写入时会分布到多个 Region，提升并发写入性能。

CREATE TABLE t1 (
    name VARCHAR(255)
) SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS = 8;

c、注意事项

1. 可能导致读放大。
   • 数据分布太散，某些查询可能需要跨多个 Region，影响读性能。
   • 建议根据实际业务场景权衡。
2. 只对新表生效：已存在的表不能通过 ALTER TABLE 修改这两个参数，只能重建表。

d、执行流程

1、_tidb_rowid 生成顺序是：10000, 10001, 10002, 10003, …

2、SHARD_ROW_ID_BITS = 4，打散后变成 16 个桶：

• 桶 0: 10000, 10016, 10032, …
• 桶 1: 10001, 10017, 10033, …
• …
• 桶 15: 10015, 10031, …

而表被预切分为 8 个 Region，每个 Region 接收 2 个桶的数据：

• Region 1: 桶 0 和 桶 1
• Region 2: 桶 2 和 桶 3
• …
• Region 8: 桶 14 和 桶 15

这样，写入请求就均匀地分散到了 8 个 Region 中。

4）选择建议

• 在表内定义一个主键或唯一索引。尽量选择有意义的列作为主键。
• 避免在单个单调数据列上定义主键。
  • 如果你使用单个单调数据列（例如：AUTO_INCREMENT 的列）来定义主键，有可能会对写性能产生负面影响。
  • 可能的话，使用 AUTO_RANDOM 替换 AUTO_INCREMENT（这会失去主键的连续和递增特性）。
• 如果你 必须 在单个单调数据列上创建索引，且有大量写入的话。
  • 请不要将这个单调数据列定义为主键，
  • 而是使用 AUTO_RANDOM 创建该表的主键，或使用 SHARD_ROW_ID_BITS 打散 _tidb_rowid。

2、选择聚簇索引

聚簇索引决定了表中数据的物理存储顺序。换句话说，行数据是按照主键的顺序进行组织和存储的。

在 TiDB 中，根据主键是否为聚簇索引，可以将表分为以下两类：

1） CLUSTERED：主键是聚簇索引

行数据的键由用户定义的主键列组成，数据按主键排序存储。

存储结构示例：

主键列数据（键） -> 行数据（值）

特点：

• 数据按主键顺序存储，查询效率高（尤其是范围查询）。
• 减少一次回表操作（因为主键就是数据）。
• 更适合读多写少、频繁基于主键查询的场景。

示例：

CREATE TABLE ratings (
    book_id BIGINT,
    user_id BIGINT,
    score TINYINT,
    rated_at DATETIME,
    PRIMARY KEY (book_id, user_id) CLUSTERED
);

2） NONCLUSTERED：主键是非聚簇索引

主键只是一个唯一索引，真正的行 ID 是 _tidb_rowid。

存储结构示例：

_tidb_rowid（键） -> 行数据（值）
主键列数据（键） -> _tidb_rowid（值）

特点：

• 主键只是逻辑上的约束，不决定数据的物理存储位置。
• 插入性能更好（避免因插入顺序导致的数据重排）。
• 更适合写密集型或主键非连续的场景。

示例：

CREATE TABLE ratings (
    book_id BIGINT,
    user_id BIGINT,
    score TINYINT,
    rated_at DATETIME,
    PRIMARY KEY (book_id, user_id) NONCLUSTERED
);

3）如何控制主键是否为聚簇索引？

a：建表时显式指定

PRIMARY KEY (col1, col2) CLUSTERED
-- 或
PRIMARY KEY (col1, col2) NONCLUSTERED

b：通过系统变量控制

@@global.tidb_enable_clustered_index

该变量有三种取值：

推荐做法：显式指定 CLUSTERED 或 NONCLUSTERED，以获得更清晰的行为控制。

4）选择建议

• 推荐使用 CLUSTERED（聚簇索引）的场景
  • 读多写少，主键查询频繁
  • 范围查询频繁
    • 主键是连续的，数据物理有序，范围扫描效率高。
    • 聚簇索引能显著提升这类查询性能。
  • 复合主键 + 固定查询模式
• 推荐使用 NONCLUSTERED（非聚簇索引）的场景
  • 写密集型应用（如日志类表）：高频插入日志记录。几乎没有基于 log_id 的查询
  • 主键非整数类型（如 UUID）：聚簇索引要求主键有序，UUID 是随机分布的，会导致频繁的数据重排和写入冲突
  • 主键用于约束，实际查询依赖其他字段

三、约束

1、CHECK 约束

CHECK 约束用于限制表中某列的取值范围或表达式条件。插入或更新数据时，TiDB 会检查是否满足该条件，不满足则报错。

• 默认情况下，TiDB 是关闭 CHECK 约束功能的，需要手动开启：

SET GLOBAL tidb_enable_check_constraint = ON

1）语法格式

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

• 表达式可以包含多个列、运算符、函数等，只要最终能返回 TRUE /FALSE / UNKNOWN。

2）使用

a、添加 CHECK 约束

1. 在 CREATE TABLE 中添加

CREATE TABLE t (
    a INT CHECK (a > 10),
    b INT,
    c INT,
    CONSTRAINT c1 CHECK (b > c)
);

• a > 10 是一个匿名约束；
• c1 是一个命名约束，检查 b > c。

1. 在 ALTER TABLE 中添加

ALTER TABLE t ADD CONSTRAINT CHECK (1 < c);
ALTER TABLE t ADD CONSTRAINT check_b_gt_5 CHECK (b > 5);

b、删除 CHECK 约束

ALTER TABLE t DROP CONSTRAINT t_chk_1;

c、启用/禁用 CHECK 约束

• 在数据迁移、修复或临时维护时可以临时关闭约束；
• 不用删除约束，避免误操作；
• 实现“灰度上线”或“临时绕过”的灵活控制。

-- 禁用
ALTER TABLE t ALTER CONSTRAINT c1 NOT ENFORCED;

-- 启用
ALTER TABLE t ALTER CONSTRAINT c1 ENFORCED;

3）建议

a、常见 CHECK 约束示例

b、注意事项

4）MySQL 兼容性差异

四、表属性

用于为表或分区添加一些元信息（key = value 的形式），从而控制某些行为。

• 目前 TiDB 仅支持 merge_option 属性，用于控制 Region 的合并行为。

1、表属性的覆盖关系

ALTER TABLE t ATTRIBUTES 'merge_option=deny';
ALTER TABLE t PARTITION p ATTRIBUTES 'merge_option=allow';

• 表 t：merge_option = deny
• 分区 p：merge_option = allow（优先）

2、使用场景

1）新建表或分区的写入热点问题

• 场景：新建表或分区的写入热点问题

• 问题：新建表或分区写入时出现集中在某些 Region 上。
• 解决方案：
  1. 手动分裂 Region；
  2. 设置 merge_option=deny 阻止 Region 自动合并；
  3. 写入时数据均匀分布到多个 Region。

• 示例：

  • -- 禁止表 t 的 Region 被合并
    ALTER TABLE t ATTRIBUTES 'merge_option=deny';
    

2）只读场景下的周期性读热点问题

• 场景：只读场景下的周期性读热点问题

• 问题：读取集中在某些 Region 上，导致性能瓶颈。
• 解决方案：
  1. 手动分裂热点 Region；
  2. 设置 merge_option=deny 防止 Region 被自动合并；
  3. 保持多个 Region 提升并发读性能。

• 示例：

  • -- 禁止分区 p 的 Region 被合并
    ALTER TABLE t PARTITION p ATTRIBUTES 'merge_option=deny';
    

3、建议

1）常用操作示例

2）注意事项

• 调整需谨慎：改变 merge_option 设置可能影响集群的整体性能和稳定性。特别是在高并发写入场景下，频繁的 Region 分裂和合并可能导致额外的开销。
• 监控与调优：定期检查系统状态，观察 Region 分裂和合并的情况，必要时进行调整。
• 结合业务需求：根据实际业务中的读写模式和负载情况，合理设置 merge_option 和 split-merge-interval 参数，以达到最佳性能。

六、TTL 定期删除

Time to Live (TTL ) 提供了行级别的生命周期控制策略。通过为表设置 TTL 属性，TiDB 可以周期性地自动检查并清理表中的过期数据。此功能在一些场景可以有效节省存储空间、提升性能。

（1）常见使用场景：

• 定期删除验证码、短网址记录

• 定期删除不需要的历史订单

• 自动删除计算的中间结果

（2）设计目标：在不影响在线读写负载的前提下，帮助用户周期性且及时地清理不需要的数据。

（3）关键特性：

• 以表为单位，并发地将清理任务分发到不同的 TiDB Server 节点上进行并行处理。

• 不保证立即删除：即使数据已过期，客户端在后台任务真正删除前仍可能读到这些数据。

1、语法

1）创建具有 TTL 属性的表

• created_at 作为 TTL 的时间列（记录创建时间）。
• INTERVAL 3 MONTH 设置行的最长存活时间为 3 个月。

CREATE TABLE t1 (
    id int PRIMARY KEY,
    created_at TIMESTAMP
) TTL = `created_at` + INTERVAL 3 MONTH;

2）开启/关闭清理功能：

• TTL_ENABLE = 'OFF'：关闭自动清理（即使设置了 TTL 规则）。
• 默认值为 ON。

CREATE TABLE t1 (
    id int PRIMARY KEY,
    created_at TIMESTAMP
) TTL = `created_at` + INTERVAL 3 MONTH TTL_ENABLE = 'OFF';

3）修改表的 TTL 属性

• 修改 TTL 规则：

  • 可用于修改现有 TTL 表，或为无 TTL 的表添加 TTL。

  ALTER TABLE t1 TTL = `created_at` + INTERVAL 1 MONTH;
  

• 单独修改 TTL_ENABLE：

  ALTER TABLE t1 TTL_ENABLE = 'OFF';
  

• 清除所有 TTL 属性：

  ALTER TABLE t1 REMOVE TTL;
  

4）TTL 与默认值

TTL 可与列的默认值结合使用：

• 基于创建时间：

  CREATE TABLE t1 (
      id int PRIMARY KEY,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 默认为当前时间
  ) TTL = `created_at` + INTERVAL 3 MONTH;
  

• 基于创建或更新时间：

  • 过期时间基于 created_at 的最后修改时间。

  CREATE TABLE t1 (
      id int PRIMARY KEY,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  ) TTL = `created_at` + INTERVAL 3 MONTH;