TiDB 面试题库

graph LR
    Client[客户端] --> TiDB1[TiDB Server]
    Client --> TiDB2[TiDB Server]
    TiDB1 --> PD[Placement Driver]
    TiDB2 --> PD
    TiDB1 --> TiKV1[TiKV Node]
    TiDB1 --> TiKV2[TiKV Node]
    TiDB2 --> TiKV2
    TiDB2 --> TiKV3[TiKV Node]
    TiDB1 --> TiFlash1[TiFlash Node]
    TiDB2 --> TiFlash2[TiFlash Node]
    PD -.->|调度| TiKV1
    PD -.->|调度| TiKV2
    PD -.->|调度| TiKV3
    TiKV1 -.->|Raft Learner| TiFlash1
    TiKV2 -.->|Raft Learner| TiFlash2

graph LR
    A[SQL 请求] --> B[Protocol Layer<br/>MySQL 协议解析]
    B --> C[Parser<br/>语法解析 AST]
    C --> D[Optimizer<br/>查询优化<br/>CBO/RBO]
    D --> E[Executor<br/>执行计划]
    E --> F{数据来源}
    F -->|OLTP| G[TiKV<br/>点查/范围扫描]
    F -->|OLAP| H[TiFlash<br/>列存批量扫描]
    G --> I[结果聚合返回]
    H --> I

graph LR
    A[Region 大小超过阈值] --> B[PD 触发 Split 调度]
    B --> C[选定 Split Key]
    C --> D[原 Region 一分为二]
    D --> E[新 Region 分配副本]
    E --> F[PD 重新均衡分布]

graph LR
    A[事务开始<br/>获取 start_ts] --> B[Prewrite 阶段]
    B --> C[锁定所有 Key<br/>写入数据 + Primary Lock]
    C --> D[Commit 阶段]
    D --> E[提交 Primary Key<br/>获取 commit_ts]
    E --> F[异步清理 Secondary Lock]
    F --> G[事务完成]

-- 悲观事务示例
BEGIN PESSIMISTIC;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;  -- 加悲观锁
UPDATE orders SET status = 'paid' WHERE id = 1;
COMMIT;

graph LR
    A[Client 写入] --> B[TiKV Raft Leader]
    B --> C[TiKV Follower]
    B --> D[TiFlash Learner]
    D --> E[异步写入列存引擎]
    E --> F[OLAP 查询读取]

-- 强制走 TiFlash 列存
SET @@tidb_isolation_read_engines = 'tiflash';
-- 强制走 MPP
SET @@tidb_enforce_mpp = 1;
-- 自动选择（默认）
SET @@tidb_isolation_read_engines = 'tikv,tiflash,tidb';

graph LR
    A[新 TiKV 加入集群] --> B[Store 向 PD 注册]
    B --> C[PD 检测新 Store]
    C --> D[生成 Balance Region 调度]
    D --> E[Region 迁移到新 Store]
    E --> F[集群重新均衡]

-- 将某个表的 Region 放置到指定 Store
ALTER TABLE orders PLACEMENT POLICY='local_dc';

graph LR
    A[TiKV Region Heartbeat] --> B[PD 收集信息]
    B --> C[构建全局视图]
    C --> D[调度器计算]
    D --> E[生成 Operator]
    E --> F[下发到 TiKV]
    F --> G[执行 Region 迁移/分裂]

# 全量备份到 S3
br backup full \
  --pd "172.16.5.10:2379" \
  --storage "s3://backup/tidb-full/" \
  --s3.endpoint "http://minio:9000"

# 恢复全量备份
br restore full \
  --pd "172.16.5.10:2379" \
  --storage "s3://backup/tidb-full/"

# 增量备份（基于上一次备份的 last-backup-ts）
br backup full \
  --pd "172.16.5.10:2379" \
  --storage "s3://backup/tidb-incr/" \
  --last-backup-ts 431434047666876416

graph LR
    A[读取 CSV/SQL] --> B[数据编码为 KV]
    B --> C[本地排序]
    C --> D[生成 SST 文件]
    D --> E[上传 SST 到 TiKV]
    E --> F[Ingest SST 到 RocksDB]
    F --> G[PD 更新 Region 元数据]

graph LR
    A[TiKV 变更事件] --> B[TiCDC Capture<br/>拉取 Kv Change Log]
    B --> C[排序与过滤]
    C --> D{下游类型}
    D -->|TiDB/MySQL| E[SQL 写入]
    D -->|Kafka| F[Avro/Canal/OpenProtocol]
    D -->|S3| G[存储为 Parquet/CSV]

# 创建同步任务到 MySQL
cdc cli changefeed create \
  --pd="http://172.16.5.10:2379" \
  --sink-uri="mysql://user:pass@downstream:3306" \
  --changefeed-id="sync-to-mysql"

# 创建同步任务到 Kafka
cdc cli changefeed create \
  --pd="http://172.16.5.10:2379" \
  --sink-uri="kafka://kafka:9092/cdc-topic?protocol=canal"

graph LR
    A[上游 MySQL] --> B[全量导出<br/>Mydumper/dumpling]
    B --> C[全量导入<br/>Loader/TiDB Lightning]
    A --> D[增量同步<br/>读取 Binlog]
    D --> E[Relay Log 持久化]
    E --> F[Binlog 解析转换]
    F --> G[写入下游 TiDB]

apiVersion: pingcap.com/v1alpha1
kind: TidbCluster
metadata:
  name: basic
spec:
  version: v7.5.0
  pd:
    replicas: 3
    requests:
      storage: "10Gi"
  tikv:
    replicas: 3
    requests:
      storage: "100Gi"
  tidb:
    replicas: 2

# Region 副本数不足告警
- alert: TiKV_Region_Unavailable
  expr: pd_regions_status{type="miss_peer_region_count"} > 0
  for: 5m
  labels:
    severity: critical
  annotations:
    summary: "存在副本缺失的 Region"

-- 查看执行计划
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;

-- 绑定执行计划
CREATE SQL_BINDING FOR
  SELECT * FROM orders WHERE user_id = 100
USING
  SELECT /*+ USE_INDEX(orders, idx_user) */ * FROM orders WHERE user_id = 100;

{
  "group_id": "pd",
  "id": "rule1",
  "index": 100,
  "start_key": "",
  "end_key": "",
  "role": "voter",
  "is_witness": false,
  "count": 2,
  "label_constraints": [
    {"key": "zone", "op": "in", "values": ["z1", "z2"]}
  ],
  "location_labels": ["zone", "host"]
}

-- 创建放置策略
CREATE PLACEMENT POLICY dc1_followers
  FOLLOWERS=2
  CONSTRAINTS='[+zone=dc1]';

-- 应用到表
CREATE TABLE orders (...) PLACEMENT POLICY=dc1_followers;

-- 创建资源组
CREATE RESOURCE GROUP rg_batch
  RU_PER_SEC = 1000
  PRIORITY = LOW;

CREATE RESOURCE GROUP rg_online
  RU_PER_SEC = 5000
  PRIORITY = HIGH;

-- 绑定用户到资源组
ALTER USER batch_user RESOURCE GROUP rg_batch;
ALTER USER app_user RESOURCE GROUP rg_online;

-- 查看资源消耗
SELECT * FROM information_schema.resource_groups;

# spark-defaults.conf
spark.sql.extensions org.apache.spark.sql.TiExtensions
spark.tispark.pd.addresses 172.16.5.10:2379
spark.tispark.tidb.addr 172.16.5.10
spark.tispark.tidb.port 4000
spark.tispark.tidb.user root
spark.tispark.tidb.password ""

-- 加速建索引（调大回填并发）
SET @@global.tidb_ddl_reorg_worker_cnt = 16;
SET @@global.tidb_ddl_reorg_batch_size = 1024;

-- 查看DDL进度
ADMIN SHOW DDL JOBS;

# 禁用透明大页
echo never > /sys/kernel/mm/transparent_hugepage/enabled

# 调整 IO 调度器为 none（NVMe）
echo none > /sys/block/nvme0n1/queue/scheduler

# 网络内核参数优化
sysctl -w net.core.somaxconn=32768
sysctl -w net.ipv4.tcp_max_syn_backlog=16384
sysctl -w vm.swappiness=0

# 查看 Region 健康状态
pd-ctl region --check

# 查看 Store 状态
pd-ctl store

# 查看 Raft 状态
tikv-ctl raft region -r <region_id>

# 查看慢查询
SELECT query_time, stats, query
FROM information_schema.slow_query
WHERE time > '2026-05-27 00:00:00'
ORDER BY query_time DESC LIMIT 10;

# 分析执行计划
EXPLAIN ANALYZE <慢查询SQL>;

graph LR
    A[升级前检查] --> B[备份元数据<br/>ETCD / PD]
    B --> C[升级 PD]
    C --> D[升级 TiKV<br/>逐个滚动]
    D --> E[升级 TiDB Server<br/>逐个滚动]
    E --> F[升级 TiFlash<br/>逐个滚动]
    F --> G[验证集群状态]
    G --> H[更新监控组件]