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

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

2.1. TiDB 数据库表结构设计

2.1.1. 数据库对象

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

	MySQL	TiDB	Oracle
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）”，在Oracle 中称为“索引组织表（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 使用限制
1. 定义的列必须为主键或者索引的首列。
2. 只能定义在类型为整数、FLOAT 或 DOUBLE 的列上。
3. 不支持与列的默认值 DEFAULT 同时指定在同一列上。
4. 不支持使用 ALTER TABLE 来添加 AUTO_INCREMENT 属性。
5. 支持使用 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 设计最佳实践

![TableDesign](vx_images/8942015248976.png =600x)

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：RowID

2.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 映射示例。

![索引的KV映射](vx_images/381532515244112.png =600x)

2.2.2. 索引

2.2.2.1. 索引的创建

TiDB 中创建索引的语法与 MySQL 的语法保持兼容。可以在建表时一并创建，也可以通过 “ALTERTABLE <TableName> ADD ... INDEX” 或 “CREATE INDEX <IndexName> ON ...” 的语法为存量表创建索引。创建索引时，不会阻塞表数据的读写。

CREATE TABLE books (
    id    bigint(20) AUTO_RANDOM NOT NULL,
    title varchar(100) NOT NULL,
    type  enum('Magazine', 'Novel', 'Life', 'Arts','Kids', 'Sports') NOT NULL,
    published_at datetime NOT NULL,
    stock int(11) DEFAULT '0',
    price decimal(15,2) DEFAULT '0.0',
    PRIMARY KEY (id) CLUSTERED        /* 创建聚簇索引 */
) ;

CREATE TABLE tab04 (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    c1 INT NOT NULL,
    c2 INT NOT NULL,
    c3 INT NOT NULL,
    KEY idx_t1_c1(c1)                 /* 创建非唯一的二级索引idx_t1_c1
);

/* 创建普通索引 */
ALTER TABLE tab04 ADD INDEX idx_t04_c2(c2);

/* 创建唯一索引 */
ALTER TABLE tab04 ADD UNIQUE INDEX uidx_t04_c3(c3);

2.2.2.2. 组合索引

可以为多个列创建组合索引，组合索引在使用时，需要满足最左匹配原则。

/* 为列 title, published_at 新建一个组合索引 */
CREATE INDEX title_published_at_idx ON books (title, published_at);

/* 可以利用上索引查询 */
SELECT * FROM books WHERE title = 'database';
SELECT * FROM books WHERE title = 'database' and published_at = '2018-08-18 21:42:08';

/* 未指定组合索引最左列，无法利用上索引 */
SELECT * FROM books WHERE published_at = '2018-08-18 21:42:08';

创建一个联合索引 (c1,c2,c3)，实际上相当于同时创建了 3 个索引：(c1)、(c1,c2)、(c1,c2,c3)。

2.2.2.3. 表达式索引

表达式索引是一种特殊的索引，能将索引建立于应用了表达式的列上。

CREATE INDEX idx1 ON tab04 ((lower(col1)));

【注意】

表达式索引中的表达式，需要用括号()括起来，否则会报语法错误。如用()将表达式“lower(col1)’‘括起来，即"(lower(col1))’’。

可通过查询变量 tidb_allow_function_for_expression_index 来得知哪些函数可用于创建表达式索引。

2.2.2.4. 索引覆盖

索引覆盖指通过索引即可完整获取所需数据，而无需二次回表，减少了I/O操作。所以，实际应用中，索引覆盖时主要的提升性能的优化手段之一。为了最大化利用索引覆盖，因避免使用"SELECT * FROM <TableName>’'的语法来读取数据。

2.2.2.5. 索引不可见

可通过 “ALTER INDEX ... VISIBLE/INVISIBLE” 来修改索引对优化器的可见（VISIBLE）与不可见（INVISIBLE）。不可见的索引，不会被优化器所使用。不允许将主键索引设置为不可见（INVISIBLE）。

CREATE TABLE tab05 (c1 INT, c2 INT, UNIQUE(c2));
CREATE UNIQUE INDEX uidx_c1 ON tab05(c1) VISIBLE;
ALTER INDEX uidx_c1 INVISIBLE;

2.2.2.6. 索引的注意事项

不支持 FULLTEXT、HASH、SPATIAL（地理信息）索引；
不支持反转索引；
无法向表中添加 CLUSTERED 类型的 PRIMARY KEY；也无法从表中删除 CLUSTERED 类型的 PRIMARY KEY；
不支持类似 MySQL 中提供的优化器开关 use_invisible_indexes=on（将所有的不可见索引重新设置为可见）。

2.2.2.7. 查看索引的 Region 分布

SHOW TABLE <TableName> INDEX <IndexName> REGIONS <Where Clause Optional>

2.3. TiDB 数据库系统表使用

2.3.1. TiDB 系统表存储位置

TiDB 中系统表主要存储在 mysql、information_schema、metrics_schema、performance_schema 数据库中：

mysql 数据库，存储 TiDB 的系统表，如 mysql.user 用户表等；
INFORMATION_SCHEMA 数据库3，提供了一种查看系统元数据的 ANSI 标准方法：
- 与 MySQL 兼容的表，如 TABLES、PROCESSLIST、COLUMNS 等；
- TiDB 自定义的系统表，如 CLUSTER_CONFIG、CLUSTER_HARDWARE、TIFLASH_REPLICA 等。
METRICS_SCHEMA 数据库4，存储的是基于 Prometheus 中 TiDB 监控指标的一组视图；
PERFORMANCE_SCHEMA 数据库，目前为与 MySQL 兼容保留了部分视图（大部分内部使用）。

2.3.2. mysql 数据库

mysql.user 数据库用户表，包含全局权限以及其他一些非权限的列；
mysql.db 数据库级别的权限；
mysql.tables_priv 表级的权限；
mysql.columns_priv 列级的权限（5.3 以下的版本不支持）
mysql.GLOBAL_VARIABLES：
- 通过系统表方式查看 TiDB 全局变量，与 SHOW GLOBAL VARIABLES 查看的内容一致；
- 支持对系统表中 VARIABLE_VALUE 的修改；
mysql.tidb
- 以 KV 形式存储集群状态的表；
- 通过修改 mysql.tidb 中相关 VARIABLE_VALUE 可以调整相关系统行为

2.3.3. INFORMATION_SCHEMA 数据库

2.3.3.1. INFORMATION_SCHEMA.CLUSTER_INFO

NFORMATION_SCHEMA.CLUSTER_INFO 表，提供了当前集群拓扑的详细信息。

USE information_schema;
desc cluster_info;

+----------------+-------------+------+------+---------+-------+
| Field          | Type        | Null | Key  | Default | Extra |
+----------------+-------------+------+------+---------+-------+
| TYPE           | varchar(64) | YES  |      | NULL    |       |
| INSTANCE       | varchar(64) | YES  |      | NULL    |       |
| STATUS_ADDRESS | varchar(64) | YES  |      | NULL    |       |
| VERSION        | varchar(64) | YES  |      | NULL    |       |
| GIT_HASH       | varchar(64) | YES  |      | NULL    |       |
| START_TIME     | varchar(32) | YES  |      | NULL    |       |
| UPTIME         | varchar(32) | YES  |      | NULL    |       |
+----------------+-------------+------+------+---------+-------+
7 rows in set (0.00 sec)

SELECT * FROM cluster_info;
+------+-----------------+-----------------+-------------+----------+-----------------+----------------+
| TYPE | INSTANCE        | STATUS_ADDRESS  | VERSION     | GIT_HASH | START_TIME      | UPTIME         |
+------+-----------------+-----------------+-------------+----------+-----------------+----------------+
| tidb | 0.0.0.0:4000    | 0.0.0.0:10080   | 4.0.0-beta  | 0df3...f7| 2020-07-05T09:25| 26h39m4.3...3s |
| pd   | 127.0.0.1:2379  | 127.0.0.1:2379  | 4.1.0-alpha | 1ad5...62| 2020-07-05T09:25| 26h39m10.3...3s|
| tikv | 127.0.0.1:20165 | 127.0.0.1:20180 | 4.1.0-alpha | b45e...df| 2020-07-05T09:25| 26h39m7.3...3s |
+------+-----------------+-----------------+-------------+----------+-----------------+----------------+
3 rows in set (0.00 sec)

字段解释：
- TYPE：节点类型，目前节点的可取值为 tidb，pd 和 tikv。
- INSTANCE：实例地址，为 IP:PORT 格式的字符串。
- STATUS_ADDRESS：HTTP API 的服务地址。部分 tikv-ctl、pd-ctl 或 tidb-ctl 命令会使用到 HTTP API 和该地址。用户也可以通过该地址获取一些额外的集群信息，详情可参考 HTTP API 文档。
- VERSION：对应节点的语义版本号。TiDB 版本为了兼容 MySQL 的版本号，以 ${mysql-version}-${tidb-version} 的格式展示版本号。
- GIT_HASH：编译节点版本时的 Git Commit Hash，用于识别两个节点是否是绝对一致的版本。
- START_TIME：对应节点的启动时间。
- UPTIME：对应节点已经运行的时间。

2.3.3.2. INFORMATION_SCHEMA.CLUSTER_CONFIG

TiDB v4.0 引入了 CLUSTER_CONFIG 表，用于获取集群当前所有组件实例的配置。

USE information_schema;
DESC cluster_config;

+----------+--------------+------+------+---------+-------+
| Field    | Type         | Null | Key  | Default | Extra |
+----------+--------------+------+------+---------+-------+
| TYPE     | varchar(64)  | YES  |      | NULL    |       |
| INSTANCE | varchar(64)  | YES  |      | NULL    |       |
| KEY      | varchar(256) | YES  |      | NULL    |       |
| VALUE    | varchar(128) | YES  |      | NULL    |       |
+----------+--------------+------+------+---------+-------+

/* 查询 TiKV 实例的 coprocessor 相关配置： */
SELECT * FROM cluster_config WHERE type='tikv' AND key LIKE 'coprocessor%';

+------+-----------------+-----------------------------------+---------+
| TYPE | INSTANCE        | KEY                               | VALUE   |
+------+-----------------+-----------------------------------+---------+
| tikv | 127.0.0.1:20165 | coprocessor.batch-split-limit     | 10      |
| tikv | 127.0.0.1:20165 | coprocessor.region-max-keys       | 1440000 |
| tikv | 127.0.0.1:20165 | coprocessor.region-max-size       | 144MiB  |
| tikv | 127.0.0.1:20165 | coprocessor.region-split-keys     | 960000  |
| tikv | 127.0.0.1:20165 | coprocessor.region-split-size     | 96MiB   |
| tikv | 127.0.0.1:20165 | coprocessor.split-region-on-table | false   |
+------+-----------------+-----------------------------------+---------+
6 rows in set (0.00 sec)

字段解释：
- TYPE：节点的类型，可取值为 tidb，pd 和 tikv。
- INSTANCE：节点的服务地址。
- KEY：配置项名。
- VALUE：配置项值。

2.3.3.3. INFORMATION_SCHEMA.DDL_JOBS

DDL_JOBS 表为 ADMIN SHOW DDL JOBS 命令提供了一个 INFORMATION_SCHEMA 接口。它提供了 TiDB 集群中 DDL 操作的当前状态和简短历史记录。

USE information_schema;
DESC cluster_config;

+--------------+-------------+------+------+---------+-------+
| Field        | Type        | Null | Key  | Default | Extra |
+--------------+-------------+------+------+---------+-------+
| JOB_ID       | bigint(21)  | YES  |      | NULL    |       |
| DB_NAME      | varchar(64) | YES  |      | NULL    |       |
| TABLE_NAME   | varchar(64) | YES  |      | NULL    |       |
| JOB_TYPE     | varchar(64) | YES  |      | NULL    |       |
| SCHEMA_STATE | varchar(64) | YES  |      | NULL    |       |
| SCHEMA_ID    | bigint(21)  | YES  |      | NULL    |       |
| TABLE_ID     | bigint(21)  | YES  |      | NULL    |       |
| ROW_COUNT    | bigint(21)  | YES  |      | NULL    |       |
| START_TIME   | datetime    | YES  |      | NULL    |       |
| END_TIME     | datetime    | YES  |      | NULL    |       |
| STATE        | varchar(64) | YES  |      | NULL    |       |
| QUERY        | varchar(64) | YES  |      | NULL    |       |
+--------------+-------------+------+------+---------+-------+
12 rows in set (0.00 sec)

SELECT * FROM ddl_jobs LIMIT 2\G

*************************** 1. row ***************************
      JOB_ID: 44
     DB_NAME: mysql
  TABLE_NAME: opt_rule_blacklist
    JOB_TYPE: create table
SCHEMA_STATE: public
   SCHEMA_ID: 3
    TABLE_ID: 43
   ROW_COUNT: 0
  START_TIME: 2020-07-06 15:24:27
    END_TIME: 2020-07-06 15:24:27
       STATE: synced
       QUERY: CREATE TABLE IF NOT EXISTS mysql.opt_rule_blacklist (
        name char(100) NOT NULL
    );
*************************** 2. row ***************************
      JOB_ID: 42
     DB_NAME: mysql
  TABLE_NAME: expr_pushdown_blacklist
    JOB_TYPE: create table
SCHEMA_STATE: public
   SCHEMA_ID: 3
    TABLE_ID: 41
   ROW_COUNT: 0
  START_TIME: 2020-07-06 15:24:27
    END_TIME: 2020-07-06 15:24:27
       STATE: synced
       QUERY: CREATE TABLE IF NOT EXISTS mysql.expr_pushdown_blacklist (
        name char(100) NOT NULL,
        store_type char(100) NOT NULL DEFAULT 'tikv,tiflash,tidb',
        reason varchar(200)
    );
2 rows in set (0.01 sec)

2.3.4. 常用运维查询

2.3.4.1. 查询某个用户的 Top N 慢 SQL

SELECT query_time, query FROM information_schema.slow_query 
WHERE is_internal = false AND user = "user1" ORDER BY query_time DESC LIMIT 2;

+----------------+-----------------------------------------------------------------+
|   query_time   |                    query                                        |
+----------------+-----------------------------------------------------------------+
|  12.77583857   | select * from t_slim, t_wide where t_slim.c0=t_wide.c0;         |
|  0.734982725   | select t0.c0, t1.c1 from t_slim t0, t_wide t1 where t0.c0=t1.c0;|
+----------------+-----------------------------------------------------------------+

2.3.4.2. 根据SQL指纹，查询某类慢SQL

digest字段即为SQL指纹，根据SQL指纹可以查询到同一类SQL。

SELECT query_time, query, digest FROM information_schema.slow_query 
WHERE is_internal = false AND time between '2021-09-21' AND '2021-09-02' ORDER BY query_time desc limit 1;

+----------------+-----------------------------+--------------------------------------+
|   query_time   |             query           |                 digest               |
+----------------+-----------------------------+--------------------------------------+
|  0.734982725   | select * from t1 where a=1; | 4751cb6008fda383e22dacb...bafb46a6fa |
+----------------+--------------------------------------------------------------------+

SELECT query, query_time FROM information_schema.slow_query
WHERE digest = "4751cb6008fda383e22dacb...bafb46a6fa"
+----------------+-----------------------------+
|             query           |   query_time   |
+-----------------------------+----------------+
| select * from t1 where a=1; |  0.734982725   |
| select * from t1 where a=2; |  0.414564635   |
+----------------------------------------------+

2.3.4.3. 统计读写热点表

SELECT db_name, table_name, index_name, type, sum(flow_bytes), count(1), 
       group_concat(h.region_id), count(distinct p.store_id), group_concat(p.store_id)
  FROM tidb_hot_regions h JOIN tikv_region_peers p
    ON h.region_id = p.region_id
   AND p.is_leader=1
 GROUP BY db_name,table_name,index_name,type;

2.3.4.4. 统计读写热点store

SELECT p.store_id, sum(flow_bytes), count(1) 
  FROM tidb_hot_regions h JOIN tikv_region_peers p 
    ON h.region_id = p.region_id AND p.is_leader=1 
 GROUP BY p.store_id 
 ORDER BY 2 desc;

2.3.4.5. DATA_LOCK_WAITS

information_schema.DATA_LOCK_WAITS 表展示了集群中所有 TiKV 节点上当前正在发生的悲观锁等锁的情况。

DESC data_lock_waits;
+------------------------+---------------------+------+------+---------+-------+
| Field                  | Type                | Null | Key  | Default | Extra |
+------------------------+---------------------+------+------+---------+-------+
| KEY                    | text                | NO   |      | NULL    |       |
| KEY_INFO               | text                | YES  |      | NULL    |       |
| TRX_ID                 | bigint(21) unsigned | NO   |      | NULL    |       |
| CURRENT_HOLDING_TRX_ID | bigint(21) unsigned | NO   |      | NULL    |       |
| SQL_DIGEST             | varchar(64)         | YES  |      | NULL    |       |
| SQL_DIGEST_TEXT        | text                | YES  |      | NULL    |       |
+------------------------+---------------------+------+------+---------+-------+

DATA_LOCK_WAITS 表中各列的字段含义如下：
- KEY：正在发生等锁的 key，以十六进制编码的形式显示。
- KEY_INFO：对 KEY 进行解读得出的一些详细信息，见 KEY_INFO。
- TRX_ID：正在等锁的事务 ID，即 start_ts。
- CURRENT_HOLDING_TRX_ID：当前持有锁的事务 ID，即 start_ts。
- SQL_DIGEST：当前正在等锁的事务中被阻塞的 SQL 语句的 Digest。
- SQL_DIGEST_TEXT：当前正在等锁的事务中被阻塞的 SQL 语句的归一化形式，即去除了参数和格式的 SQL 语句。与 SQL_DIGEST 对应。

【注意】:

仅拥有 PROCESS 权限的用户可以查询该表。

目前该表仅能记录悲观锁等锁的情况

DATA_LOCK_WAITS 表中的信息是在查询时，从所有 TiKV 节点实时获取的。目前，即使加上了 WHERE 查询条件，也无法避免对所有 TiKV 节点都进行信息收集。

来自不同 TiKV 节点的信息不能保证是同一时间点的快照。

如果集群规模很大、负载很高，查询该表有造成性能抖动的潜在风险，因此请根据实际情况使用。

2.3.4.6. DEADLOCKS

DEADLOCKS 表提供当前 TiDB 节点上最近发生的若干次死锁错误的信息。默认容纳最近 10 次死锁错误的信息。

USE information_schema;
DESC deadlocks;

+-------------------------+---------------------+------+------+---------+-------+
| Field                   | Type                | Null | Key  | Default | Extra |
+-------------------------+---------------------+------+------+---------+-------+
| DEADLOCK_ID             | bigint(21)          | NO   |      | NULL    |       |
| OCCUR_TIME              | timestamp(6)        | YES  |      | NULL    |       |
| RETRYABLE               | tinyint(1)          | NO   |      | NULL    |       |
| TRY_LOCK_TRX_ID         | bigint(21) unsigned | NO   |      | NULL    |       |
| CURRENT_SQL_DIGEST      | varchar(64)         | YES  |      | NULL    |       |
| CURRENT_SQL_DIGEST_TEXT | text                | YES  |      | NULL    |       |
| KEY                     | text                | YES  |      | NULL    |       |
| KEY_INFO                | text                | YES  |      | NULL    |       |
| TRX_HOLDING_LOCK        | bigint(21) unsigned | NO   |      | NULL    |       |
+-------------------------+---------------------+------+------+---------+-------+

DEADLOCKS 表中各列的字段含义如下：
- DEADLOCK_ID：死锁事件的 ID。当表内存在多次死锁错误的信息时，需要使用该列来区分属于不同死锁错误的行。
- OCCUR_TIME：发生该次死锁错误的时间。
- RETRYABLE：该次死锁错误是否可重试。关于可重试的死锁错误的说明，参见可重试的死锁错误小节。
- TRY_LOCK_TRX_ID：试图上锁的事务 ID，即事务的 start_ts。
- CURRENT_SQL_DIGEST：试图上锁的事务中当前正在执行的 SQL 语句的 Digest。
- CURRENT_SQL_DIGEST_TEXT：试图上锁的事务中当前正在执行的 SQL 语句的归一化形式。
- KEY：该事务试图上锁、但是被阻塞的 key，以十六进制编码的形式显示。
- KEY_INFO：对 KEY 进行解读得出的一些详细信息，详见 KEY_INFO。
- TRX_HOLDING_LOCK：该 key 上当前持锁并导致阻塞的事务 ID，即事务的 start_ts。

2.3.4.7. TIDB_TRX

TIDB_TRX 表提供了当前 TiDB 节点上正在执行的事务的信息。

USE information_schema;
DESC tidb_trx;

+-------------------------+--------------------------------------+------+------+---------+-------+
| Field                   | Type                                 | Null | Key  | Default | Extra |
+-------------------------+--------------------------------------+------+------+---------+-------+
| ID                      | bigint(21) unsigned                  | NO   | PRI  | NULL    |       |
| START_TIME              | timestamp(6)                         | YES  |      | NULL    |       |
| CURRENT_SQL_DIGEST      | varchar(64)                          | YES  |      | NULL    |       |
| CURRENT_SQL_DIGEST_TEXT | text                                 | YES  |      | NULL    |       |
| STATE                   | enum('Idle','Running','LockWaiting',
                                     'Committing','RollingBack') | YES  |      | NULL    |       |
| WAITING_START_TIME      | timestamp(6)                         | YES  |      | NULL    |       |
| MEM_BUFFER_KEYS         | bigint(64)                           | YES  |      | NULL    |       |
| MEM_BUFFER_BYTES        | bigint(64)                           | YES  |      | NULL    |       |
| SESSION_ID              | bigint(21) unsigned                  | YES  |      | NULL    |       |
| USER                    | varchar(16)                          | YES  |      | NULL    |       |
| DB                      | varchar(64)                          | YES  |      | NULL    |       |
| ALL_SQL_DIGESTS         | text                                 | YES  |      | NULL    |       |
+-------------------------+--------------------------------------+------+------+---------+-------+

字段含义：
- ID：事务 ID，即事务的开始时间戳 start_ts。
- START_TIME：事务的开始时间，即事务的 start_ts 所对应的物理时间。
- CURRENT_SQL_DIGEST：该事务当前正在执行的 SQL 语句的 Digest。
- CURRENT_SQL_DIGEST_TEXT：该事务当前正在执行的 SQL 语句的归一化形式，即去除了参数和格式的 SQL 语句。与 CURRENT_SQL_DIGEST 对应。
- STATE：该事务当前所处的状态。其可能的值包括:
  - Idle：事务处于闲置状态，即正在等待用户输入查询。
  - Running：事务正在正常执行一个查询。
  - LockWaiting：事务处于正在等待悲观锁上锁完成的状态。需要注意的是，事务刚开始进行悲观锁上锁操作时即进入该状态，无论是否有被其它事务阻塞。
  - Committing：事务正在提交过程中。
  - RollingBack：事务正在回滚过程中。
- WAITING_START_TIME：当 STATE 值为 LockWaiting 时，该列显示等待的开始时间。
- MEM_BUFFER_KEYS：当前事务写入内存缓冲区的 key 的个数。
- MEM_BUFFER_BYTES：当前事务写入内存缓冲区的 key 和 value 的总字节数。
- SESSION_ID：该事务所属的 session 的 ID。
- USER：执行该事务的用户名。
- DB：执行该事务的 session 当前的默认数据库名。
- ALL_SQL_DIGESTS：该事务已经执行过的语句的 Digest 的列表，表示为一个 JSON 格式的字符串数组。每个事务最多记录前 50 条语句。通过 TIDB_DECODE_SQL_DIGESTS 函数可以将该列的信息变换为对应的归一化 SQL 语句的列表。

创建主题取消

PCTP考试学习笔记之二：TiDB 数据库 schema 设计