本系列学习笔记根据官方课程《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使用限制定义的列必须为主键或者索引的首列。
只能定义在类型为整数、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. 索引
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 语句的列表。
创建主题取消