本系列学习笔记根据官方课程《TiDB 高级系统管理 [TiDB v5]》整理，感谢官方精心制作的视频课程。相关课程介绍，详见官方课程链接：https://learn.pingcap.com/learner/course/120005

2. 第二章 TiDB 数据库 schema 设计

2.1. TiDB 数据库表结构设计

2.1.1. 数据库对象

TiDB 与 MySQL、*** 数据库的数据库对象比较如表2.1所示。

MySQLTiDB***Table支持支持支持Partition支持部分支持支持View支持部分支持支持Index支持支持支持Sequence不支持支持支持User Function支持不支持支持Procedure支持不支持支持Trigger支持不支持支持2.1.1.1. 分区表

TiDB5.0 当前支持的分区类型包括 Range 分区、List 分区、List COLUMNS 分区、Hash 分区。其中，Range、List、List COLUMNS 分区适用于需要按范围或指定值大量删除数据的业务场景（直接删除分区）；Hash 分区则可用于大量写入场景下的数据打散。

分区表的每个唯一键或主键，必须包含分区表达式中用到的所有列。示例如下：

# 有效的分区表 CREATE TABLE tab03 ( 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; # 无效的分区表 CREATE TABLE tab03 ( 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.1.2. 聚簇索引

聚簇表（Clustered Table）在 TiDB 官方文档中称为 “聚簇索引表（index-organized tables）”，在*** 中称为“索引组织表（IOT，Index-Organized Tables）”。聚簇表具有如下特点：

聚簇表中行数据的物理存储顺序与主键的存储顺序一致；

通过主键访问行数据时，可以直接获取行数据，即只需 1 次 I/O；

TiDB 数据库中的聚簇表，其主键列也是 Region 中 KEY 的一部分；

创建示例：“CREATE TABLE tab01(a BIGINT PRIMARY KEY CLUSTERED , b VARCHAR(255));”

非聚簇表（Non-clustered Table）具有如下的特点：

表中行数据的物理存储顺序与主键（主键本质上是唯一索引）的存储顺序无关；

通过主键访问非聚簇表的行数据时，无法直接获取行数据，需要 2 次 I/O（第一次扫主键索引，获取数据行的 RowID；第二次通过 RowID 回表获取行数据）；

创建示例：“CREATE TABLE tab02(a BIGINT PRIMARY KEY NONCLUSTERED ,b VARCHAR(255));”。

查询主键是否为聚簇索引的方式：

show create table <TableName>;

show index from <TableName>;

select TIDB_PK_TYPE from information_schema.tables where table_name = <TableName>;

mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> CREATE TABLE tab01 ( a BIGINT PRIMARY KEY CLUSTERED , b VARCHAR(255) ); Query OK, 0 rows affected (0.60 sec) mysql> show create -> table -> tab01 ; +-------+-----------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------+ | tab01 | CREATE TABLE `tab01` ( `a` bigint(20) NOT NULL, `b` varchar(255) DEFAULT NULL, PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-------+-----------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> show index from tab01 ; +------+-----------+---------+-------------+------------+----------+------------+---------+-------+-----------+--------+-----------+----------+ | Table| Non_unique| Key_name| Seq_in_index| Column_name| Collation| Cardinality| Sub_part| Packed| Index_type| Visible| Expression| Clustered| +------+-----------+---------+-------------+------------+----------+-------------+--------+-------+-----------+--------+-----------+----------+ | tab01| 0 | PRIMARY | 1 | a | A | 0 | NULL | NULL | BTREE | YES | NULL | YES | +------+-----------+---------+-------------+------------+----------+------------+---------+-------+-----------+--------+-----------+----------+ 1 row in set (0.01 sec) mysql> select TIDB_PK_TYPE from information_schema.tables where table_name = tab01; +--------------+ | TIDB_PK_TYPE | +--------------+ | CLUSTERED | +--------------+ 1 row in set (0.04 sec)

【注意】

目前，TiDB 不支持在建表之后添加或删除聚簇索引，也不支持聚簇索引和非聚簇索引的互相转换。因为，非聚簇索引就是二级索引，所以，可以在建表之后，为其添加或删除非聚簇索引（二级索引）。此时，可指定 NONCLUSTERED 关键字或省略关键字。

2.1.2.1. 非聚簇表的热点写入问题

在非聚簇表中，TiDB 会为每行数据隐式增加一个 RowID 列（_tidb_rowid），默认 RowID 为单调递增。大量执行 INSERT 插入语句时会把数据集中写入单个 Region，造成写入热点。要避免因 RowID 带来的写入热点问题，可以在建表时，设置 SHARD_ROW_ID_BITS 和 PRE_SPLIT_REGIONS 这两个建表选项：

SHARD_ROW_ID_BITS=N 用于将 TiDB 为数据行隐式增加的 RowID 随机打散成 2^N个分片（范围区间），以防止一个连续的范围区间，导致大量 INSERT 都查入到一个 Region 中。如SHARD_ROW_ID_BITS=4，表示将 RowID 打散成 2^4=16 个分片。默认值为 0，表示 2^0=1 个分片。

PRE_SPLIT_REGIONS=N 用于在建完表后预先进行 Split Region。即建表后直接预先创建 2^N 个 Region。如 PRE_SPLIT_REGIONS=3 表示预先创建 2^3=8 个 Region。

【注意】

PRE_SPLIT_REGIONS 的值必须小于或等于 SHARD_ROW_ID_BITS。

全局变量 tidb_scatter_region 会影响 PRE_SPLIT_REGIONS的行为。该变量用于控制建表完成后是否等待预切分和打散 Region 完成后再返回结果。如果建表后有大批量写入，需要设置该变量值为 1，表示等待所有 Region 都切分和打散完成后再返回结果给客户端。否则未打散完成就进行写入，会对写入性能影响有较大的影响。

应用示例：

CREATE TABLE tab02( a int , b int ) SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS=3;

SHARD_ROW_ID_BITS = 4 表示 _tidb_rowid 的值会随机打散成 2^4=16 个范围区间。

PRE_SPLIT_REGIONS = 3 表示建完表后提前切分出 2^3=8 个 Region。

开始写数据进表 tab02 后，数据会被写入提前切分好的 8 个 Region 中。这样也避免了刚开始建表完后，因为只有一个 Region 而存在的写热点问题。

2.1.3. 数据类型

TiDB 支持除空间类型 (SPATIAL) 之外的所有 MySQL 数据类型，包括数值型类型、字符串类型、时间和日期类型、JSON 类型，详细内容可参考如下链接：

数值类型：https://docs.pingcap.com/zh/tidb/stable/data-type-numeric

字符串类型：https://docs.pingcap.com/zh/tidb/stable/data-type-string

时间和日期类型：https://docs.pingcap.com/zh/tidb/stable/data-type-date-and-time

JSON 类型：https://docs.pingcap.com/zh/tidb/stable/data-type-json

在数据类型描述中的 DEFAULT value 段描述了一个列的默认值。针对数据类型的默认值，有如下几点需要注意：

数值型类型、大部分字符串类型列的默认值，必须是常量；

时间、日期类型的列，可以使用 NOW、CURRENT_TIMESTAMP、LOCALTIME、LOCALTIMES-TAMP 等函数作为 DATETIME 或者 TIMESTAMP 的默认值。。

BLOB、TEXT 以及 JSON 不可以设置默认值。

若在列的定义中未指定 DEFAULT 的设置，则 TiDB 按如下规则决定：

若类型可以使用 NULL 作为值，则 TiDB 为该列添加隐式的默认值设置 DEFAULT NULL；

若类型不可使用 NULL 作为值，则 TIDB 不会为其添加隐式的默认值设置。

对于设置了 NOT NULL，但未显示设置 DEFAULT 的列，当 INSERT、REPLACE 未指定该列的值时，TiDB 根据当时的 SQL_MODE 进行不同的行为：

若设置是 strict sql_mode，则在事务中的语句会导致事务失败并回滚；非事务中的语句直接报错；

若设置不是 strict sql_mode，则 TiDB 会为该列赋值为列数据类型的隐式默认值。

数据类型的隐式默认值规则：

对于数值类型，其隐式默认值为 0。当有 AUTO_INCREMENT 参数时，默认值会自动递增；

对于除了时间戳外的日期时间类型，其隐式默认值是该类型的“零值”。时间戳类型的隐式默认值是当前的时间。

对于除枚举以外的字符串类型，其隐式默认值是空字符串。对于枚举类型，隐式默认值是枚举中的第一个值。

2.1.3.1. 自增列（AUTO_INCREMENT）

基本概念

AUTO_INCREMENT 是用于以自增方式，自动填充缺省列值的列属性。当 INSERT 语句没有指定 AUTO_INCREMENT 列的具体值时，系统会自动地为该列分配一个自增值。此外，AUTO_INCREMENT 还支持显式指定列值的插入语句，此时 TiDB 会保存显式指定的值：

mysql> CREATE TABLE t( id int PRIMARY KEY AUTO_INCREMENT, c int ); mysql> INSERT INTO t(c) VALUES(1); mysql> INSERT INTO t(c) VALUES(2),(3); mysql> INSERT INTO t(id,c) VALUES(4,4); mysql> SELECT * FROM t ; +----+------+ | id | c | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 4 rows in set (0.00 sec)

出于性能原因，自增编号是系统批量分配给每台 TiDB 服务器的值（默认 3 万个值），因此自增编号能保证唯一性，但分配给 INSERT 语句的值仅在单台 TiDB 服务器上具有单调性。即 AUTO_INCREMENT 无法保证自增列的连续性。

【注意】

使用 AUTO_INCREMENT 可能会给生产环境带热点问题，因此推荐使用 AUTO_RANDOM 代替。详情请参考《tidb-热点问题处理》。另外，使用 TiSpark 的小伙伴需要特别注意，截止到 TiDB v6.1 为止，TiSPark 暂时不支持写入含有AUTO_RANDOM 列的表。

实现原理

对于每一个自增列，都使用一个全局可见的 KV 键值对用于记录当前已分配的 MaxID。由于分布式环境下的节点通信存在一定开销，为了避免写请求放大的问题，每个 TiDB 节点在分配 ID时，都申请一段 ID（默认 3 万个）作为缓存，用完之后再去取下一段。因此，自增编号可以保证全局唯一性，但分配给 INSERT 语句的值仅在单台 TiDB 服务器上具有单调性，无法保证全局单调性和连续性。例如，假设集群中有两个 TiDB 实例 A 和 B，则实例 A 可能会缓存[1,30000] 的自增 ID，而实例 B 则可能缓存 [30001,60000] 的自增 ID。各自实例缓存的 ID 将随着执行将来的插入语句被作为缺省值，顺序地填充到 AUTO_INCREMENT 列中。AUTO_INCREMENT 缓存不会持久化，重启会导致缓存值失效。所以，当 TiDB Server 重启后，会重新申请一段新的 AUTO_INCREMENT 缓存值。频繁的重启可能会导致 AUTO_INCREMENT缓存值被快速消耗，也会导致表中自增列的值出现跳跃。另外，当手动为自增列指定值，也可能导致 TiDB 重新选取一段新的 AUTO_INCREMENT 缓存值。

AUTO_ID_CACHE

TiDB 自增 ID 的缓存大小在早期版本中是对用户透明的。从 v3.1.2、v3.0.14 和 v4.0.rc-2 版本开始，TiDB 引入了 AUTO_ID_CACHE 表选项来允许用户自主设置自增 ID 分配缓存的大小。

CREATE TABLE t( a int AUTO_INCREMENT key ) AUTO_ID_CACHE 100;

AUTO_INCREMENT 使用限制

定义的列必须为主键或者索引的首列。

只能定义在类型为整数、FLOAT 或 DOUBLE 的列上。

不支持与列的默认值 DEFAULT 同时指定在同一列上。

不支持使用 ALTER TABLE 来添加AUTO_INCREMENT 属性。

支持使用 ALTER TABLE 来移除 AUTO_INCREMENT 属性。但从 TiDB 2.1.18 和 3.0.4 版本开始，TiDB 通过 session 变量 @@tidb_allow_remove_auto_inc 控制是否允许通过 ALTER TABLE MODIFY 或 ALTER TABLE CHANGE 来移除列的 AUTO_INCREMENT 属性，默认是不允许移除。

2.1.3.2. 随机自增列（AUTO_RANDOM）

基本概念

AUTO_RANDOM是用于以随机自增方式，自动填充缺省列值（BIGINT 类型）的列属性。当INSERT 语句没有指定AUTO_RANDOM 列的具体值时，系统会自动地为该列分配一个随机值。此外，AUTO_RANDOM 还支持显式指定列值的插入语句，此时 TiDB 会保存显式指定的值。单个事务中的多条 INSERT 语句所产生的 AUTO_RANDOM 值是连续自增的，而多个事务中的单条 INSERT 语句所产生的 AUTO_RANDOM 值是随机的。因此，AUTO_RANDOM 可用于解决大批量写数据入 TiDB 时因含有整型自增主键列（AUTO_INCREMENT）的表而产生的热点问题。详情参阅 《TiDB 高并发写入场景最佳实践》。

【注意】

若要显式设置 AUTO_RANDOM 列的值为 0，需要在系统变量 @@sql_mode 中设置 NO_AUTO_VALUE_ON_ZERO，否则 TiDB 会为该列分配随机值。从 v4.0.3 开始，要使用显式插入的功能，需将系统变量 @@allow_auto_random_explicit_insert 的值设置为 1（默认值为 0，不支持显示插入）。

实现原理AUTO_RANDOM 值在二进制形式下，最高第 64 位为符号位，63-59 共五位（称为 shard bits）为随机位，由当前事务的 start_ts 决定，取值范围 1～32；剩下的位数按照自增的顺序分配。若要使用一个不同位数（bits）的 shard bits ，可以在 AUTO_RANDOM后面加一对括号，并在括号中指定shard bits 的位数，示例如下：

CREATE TABLE t( a bigint PRIMARY KEY AUTO_RANDOM(3) , b varchar(255) );

使用 SHOW WARNINGS 可以查看当前表可支持的最大隐式分配的次数。为保证可隐式分配的次数最大，从 v4.0.3 开始，AUTO_RANDOM 列类型只能为 BIGINT。

查看某表 AUTO_RANDOM 属性的 shard bits 数量，可以在系统表 information_ schema.tables 中 TIDB_ROW_ID_SHARDING_INFO一列看到模式为 PK_AUTO_RANDOM_BITS=x 的值，其中 x 为shard bits 的数量。

可以使用 SELECT last_insert_id() 获取上一次 TiDB 隐式分配的 ID。

INSERT INTO t(b) VALUES("b"); SELECT * FROM t; SELECT last_insert_id();

AUTO_RANDOM 使用限制

该属性必须指定在 BIGINT 类型的主键列上，否则会报错。此外，当主键属性为 NONCLUSTERED 时，即使是整型主键列，也不支持使用 AUTO_RANDOM。

不支持使用 ALTER TABLE 来修改 AUTO_RANDOM 属性，包括添加或移除该属性。

支持将 AUTO_INCREMENT 属性改为 AUTO_RANDOM 属性。但在 AUTO_INCREMENT 的列数据最大值已接近 BIGINT 类型最大值的情况下，修改可能会失败。

不支持修改含有 AUTO_RANDOM 属性的主键列的列类型。

不支持与 AUTO_INCREMENT 同时指定在同一列上。

不支持与列的默认值 DEFAULT 同时指定在同一列上。

AUTO_RANDOM 列的数据很难迁移到 AUTO_INCREMENT 列上，因为 AUTO_RANDOM 列自动分配的值通常都很大。

插入数据时，不建议自行显式指定含有 AUTO_RANDOM列的值。不恰当地显式赋值，可能会导致该表提前耗尽用于自动分配的数值。

TiSPark 不支持写入含有 AUTO_RANDOM 列属性的表。详见 asktug：《TiSpark 不支持 auto_random 做主键的表批量写入》

2.1.4. Schema 设计最佳实践


2.2. TiDB 数据库索引设计

2.2.1. 索引的 KV 映射原理

2.2.1.1. 非聚簇表主键（或唯一索引）的 KV 映射

非聚簇表主键（或唯一索引）的 KEY 值唯一，TiDB 在为其做 KV 映射时，将 TableID、IndexID、索引列值作为索引 KEY，Value 为数据行的 RowID。映射方式如下：

Key：tablePrefix{TableID}_indexPrefixSep{IndexID}_indexedColumnsValue Value：RowID2.2.1.2. 二级索引的 KV 映射

二级索引的索引键 KEY 可能有重复值，TiDB 在为二级索引做 KV 映射时，会为每条重复的行数据生成一个索引 KEY，Value 为 Null。映射方式如下：

Key：tablePrefix{TableID}_indexPrefixSep{IndexID}_indexedColumnsValue_{RowID} Value：NULL

比如，为Users表的 “UserName” 列创建二级索引 “idxUsr”。假设存在 3 个同名用户 “Tom”，则该二级索引会生成 3 个索引 KEY：

Key：tablePrefixUsers_indexPrefixSepidxUsr_Tom_xxxx1，Value：NULL； Key：tablePrefixUsers_indexPrefixSepidxUsr_Tom_xxxx2，Value：NULL； Key：tablePrefixUsers_indexPrefixSepidxUsr_Tom_xxxx3，Value：NULL；

其中，xxxx1、xxxx2、xxxx3 为 “UserName=Tom” 的 3 条重复数据的 RowID。

【注意】

只有当二级索引是非唯一索引，其 KV 中的 Value 值才为 “NULL”。如果二级索引为唯一索引，如 unique 索引，则其 KV 中的 Value 存储的为主键索引。

如图2.1所示，为主键索引与二级索引的 KV 映射示例。


2.2.2. 索引