TiDB PCSD学习总结

一、模块 01：架构与原理

1. 整体架构：Shared-Nothing 计算存储分离

三层分层：应用层 → TiDB Server 层 → 存储调度层（PD/TiKV/TiFlash）

2. 四大核心组件区分

3. 三大底层核心机制

3.1 Region

Raft 复制、数据调度最小单元，默认 256MB；大表自动 Range 拆分为多个 Region，自动 Split/Merge。

3.2 Raft 共识协议

每个 Region 一组 Raft Group，1 个 Leader（读写）+ 多 Follower；写入需要多数派 (N/2+1) 确认，单节点故障集群无损，自动切换 Leader。

3.3 MVCC 多版本并发控制

存储格式：Key + TSO 时间戳 + Value；依靠 PD 分配的 TSO 区分事务版本，实现无锁快照读；回滚事务版本不会立即删除。

4. 分布式 SQL 核心特性

• 聚合、过滤、比较、数值运算可下推 TiKV，执行计划标识 cop[tikv]；
• LIKE 可下推，但 RLIKE/REGEXP 正则不能下推；
• COUNT(*) 大表并行下推 TiKV，大幅提升性能。

二、模块 02：数据类型

1. 数值类型精度区分

• FLOAT/DOUBLE 浮点数：近似值，精度丢失，不适合金额；示例 1.1E0+2.2E0 ≠ 3.3E0，运算可能返回 0。

• DECIMAL 精确类型：定点高精度，银行、余额、金额必须使用 DECIMAL。

• 整数范围：SMALLINT、INT、BIGINT 逐级扩容；INT(M) 中 M不限制存储数值范围，仅 zerofill 前导补零时生效。

• BIT (M)：代表 M 个比特存储，BIT (9) 即占用 9 位二进制。

-- FLOAT/DOUBLE 二进制浮点，精度丢失
SELECT 1.1E0 + 2.2E0 = 3.3E0; -- 返回 0(false)
​
-- DECIMAL 定点数，精确计算
SELECT 1.1 + 2.2 = 3.3; -- 返回1(true)
​
--  int(M)，M不限制存储取值范围,INT(9)、INT(4)存储范围完全一样，都可以存入INT最大值
CREATE TABLE t1 (a INT(9), b INT(4));
INSERT INTO t1 VALUES (2147483647,2147483647);
-- 只有开启 ZEROFILL（前导零填充），M 才会生效；不开启 ZEROFILL，M 完全无效
CREATE TABLE t2 (
a INT(4) ZEROFILL,  -- 显示 0001
b INT(9) ZEROFILL   -- 显示 000000001
);

2. 字符串类型 CHAR / VARCHAR / TEXT / ENUM

• CHAR (M)：定长，写入尾部截断空格；VARCHAR：变长存储；
• TEXT/MEDIUMTEXT：最大 16,777,215字节，受 raft-entry-max-size（单个日志最大大小，默认8MB） 参数限制；
• ENUM：枚举类型，元素只能是字符串，下标从 1 开始，不能存数字；建表ENUM('10','20')无法插入数值 20。

-- CHAR(M)定长字符，不足M位尾部补空格存储
CREATE TABLE t3(c CHAR(10));
INSERT INTO t3 VALUES('hi');
-- 实际内部存储：'hi'后面补8个空格，总占10字符。
​
CREATE TABLE t4(
size ENUM('S','M','L','XL')
);
-- ENUM只能插入定义内的字符串；下标从1开始，不是0。

3. 日期与时区

• DATETIME：不含时区，不受 time_zone 参数影响；
• TIMESTAMP：含时区，受 time_zone 系统变量控制；
• INTERVAL 不是数据类型，仅时间运算关键字。

4. 字符比较与 Collation 排序规则

• 空格忽略：'A'='A ' → 返回 0（空格不忽略）；空串''=' '（空串相等）；

• NULL 比较：NULL=NULL 结果仍为 NULL，不能用 = 判断空，只能 IS NULL/IS NOT NULL；

SELECT NULL = NULL;      -- 结果 NULL（不是1，不相等）
SELECT NULL = 1, NULL <>1; -- 结果 NULL, NULL
​
-- 判断NULL 只能用 IS NULL / IS NOT NULL
SELECT * FROM t WHERE col IS NULL;
SELECT * FROM t WHERE col IS NOT NULL;

• utf8mb4_bin降序：二进制排序规则，逐字节对比，区分大小写，直接按照字符的二进制编码值比较。

c > b > a > C > B > A > 3 > 2 > 1 > +

三、模块 03：SQL 与函数

1. NULL 全套规则

• NULL 和任何值运算、比较结果都是 NULL；8/0 + NULL 返回 NULL 并抛 Warning；
• 判断空值仅允许 IS NULL / IS NOT NULL / COALESCE()，禁止=NULL。

2. || 连接符与 CONCAT 函数差异

• || 默认等价 OR 逻辑（返回 0 或 1）；开启sql_mode=PIPES_AS_CONCAT后才作为字符串连接；

-- 默认：||代表OR，'a'非0，结果0,不是'ab'
SELECT 'a' || 'b';
​
-- 修改sql_mode，开启字符串拼接模式
SET sql_mode = 'PIPES_AS_CONCAT';
SELECT 'a' || 'b'; -- 输出 ab

• CONCAT(a,NULL,b) 结果直接为 NULL；

• CONCAT_WS(分隔符,a,NULL,b)：忽略 NULL 参数，仅分隔符为 NULL 时结果才为 NULL，更安全。

SELECT CONCAT('a',NULL,'b');       -- 返回 NULL
SELECT CONCAT_WS(',','a',NULL,'b');-- 返回 a,b

3. JOIN、UNION 语法考点

• LEFT JOIN：ON 条件过滤不丢失左表数据；WHERE 过滤会剔除 NULL 行；

-- ON中条件：保留a全部数据
SELECT * FROM a
LEFT JOIN b ON a.id = b.aid AND b.status = 1;
​
-- WHERE条件：过滤掉右表null行，等价内连接
SELECT * FROM a
LEFT JOIN b ON a.id = b.aid
WHERE b.status = 1;

• UNION：自动去重、性能更低；UNION ALL：不去重、性能更高；优先选 UNION ALL。

-- UNION：去重
SELECT name FROM t1
UNION
SELECT name FROM t2;
​
-- UNION ALL：不去重
SELECT name FROM t1
UNION ALL
SELECT name FROM t2;

• JOIN 与子查询：多表同名字段要加表别名，否则报 1052；IN子查询有NULL会返回空；EXISTS只判断行是否存在，优于大集合 IN。

4. 时区函数与表达式下推边界

• NOW () / CURTIME ()/TIMESTAMP 均受 time_zone 时区影响；DATETIME不受
• 可下推：逻辑运算、比较、数值、日期函数、LIKE；
• 不可下推：RLIKE、REGEXP 正则匹配。

5. 排序规则 COLLATE

• utf8mb4_bin：二进制字节比较，区分大小写，大写字母排在小写前面
• utf8mb4_general_ci：不区分大小写，Apple=apple

-- utf8mb4_bin 区分大小写
SELECT * FROM t ORDER BY name COLLATE utf8mb4_bin;
--升序 ASC（从小到大）：Apple < apple < banana < 中文，大写排在小写前面
​
-- utf8mb4_general_ci 不区分大小写
SELECT * FROM t ORDER BY name COLLATE utf8mb4_general_ci;
--Apple' 和 'apple'视为相等，排序会排在一起

四、模块 04：事务与并发

1. 事务隔离级别

• TiDB 仅支持：REPEATABLE READ 可重复读、READ COMMITTED 读已提交；不支持 Serializable 串行化；默认悲观锁模式。

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

• 元数据锁：v6.3+ DDL 会加元数据锁，等待旧版本 DML 全部提交后执行；TRUNCATE 不可回滚。

2. 隐式提交场景

• 第二个 BEGIN 会隐式提交上一个未提交事务；

BEGIN;
INSERT INTO t VALUES (1);
BEGIN; -- 隐式COMMIT上面的INSERT
UPDATE t SET col = 2;
ROLLBACK;
-- 结果：INSERT已提交，UPDATE被回滚

• DDL 语句、权限变更、外键约束 DML 校验均触发隐式 COMMIT；

• DML 执行时校验外键，不是提交时检查。

• foreign_key_checks 默认开启，设置 OFF 可临时跳过外键校验；

3.元数据锁与 DML

• v6.3 引入元数据锁 MDL，保护表结构元数据一致性。

• DDL（alter table 等）会等待正在运行的旧 DML 事务提交完成，再执行。

• TRUNCATE TABLE 不可回滚，不属于 DML，属于 DDL。

• REPLACE INTO 等价逻辑：DELETE + INSERT；主键冲突先删旧行，再插入新行。

REPLACE INTO t(id,name) VALUES(1,'test');
-- 等价：主键存在则delete该id，再insert；不存在直接insert

4. Prepared Statement（PS 预处理语句）

4.1 运行机制

• PREPARE：仅解析生成 AST 语法树，不生成执行计划；单句柄仅绑定 1 条 SQL，禁止嵌套使用。

• EXECUTE：传入参数生成执行计划并缓存，后续同模板执行可复用缓存计划。

4.2 核心优势

• 复用计划，减少重复 SQL 解析开销；

• 仅传参数，缩减网络传输流量；

• 模板与参数隔离，有效防范 SQL 注入风险。

4.3 能力短板 & 使用边界

• 执行计划固化缓存，无法随参数、数据分布动态生成最优计划；

• 并非全场景适用：低频 SQL、数据倾斜剧烈的 SQL 盲目使用会产生额外开销、引发性能劣化。

4.4 语法

-- 方式一：PREPARE+EXECUTE
PREPARE stmt FROM 'SELECT * FROM t WHERE id = ?';
SET @id = 42;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;
--方式二：二进制协议（应用层）
JDBC:conn.prepareStatement(sql)
--实现，采用 MySQL 二进制协议传输参数，上层无显式 PREPARE 语法。
--优势：SQL注入防护+执行计划复用

5. Sequence 与 AUTO_INCREMENT 自增

5.1 Sequence

默认 cache=1000，多节点分片领取独立号段（节点 1 拿 1-1000、节点 2 拿 1001-2000），全局默认批量预分配 3 万个 ID，减少中心交互开销。

5.2 AUTO_INCREMENT

写入热点问题：自增主键持续往末尾同一个 Region 写入，造成单 Region 写入压力瓶颈；

分配规则：集群多节点默认批量预分配 3 万个 ID 号段；

序号特性：多节点号段分段分配，无法保证 ID 全局单调递增，存在 ID 空洞、序号乱序问题。

6. AUTO_RANDOM 解决方案

核心作用：专门解决自增主键带来的 Region 写入热点问题；

实现原理：对主键 ID 前置位随机打散，让数据均匀分散到不同 Region；

特性取舍：严格保证主键唯一性，但彻底放弃 ID 全局单调递增特性。

-- 解决自增主键热点问题
CREATE TABLE big_table (
    id BIGINT NOT NULL AUTO_RANDOM(5), -- 5位随机bit
    name VARCHAR(100),
    PRIMARY KEY (id)
) SHARD_ROW_ID_BITS = 5;
-- AUTO_INCREMENT;顺序写，单点热点
-- AUTO_RANDOM 分散写，均匀分布

五、模块 05：开发实践

1. 索引体系与聚簇索引

1.1 聚簇索引 Clustered Index

INT/BIGINT 主键默认聚簇；主键 = 行唯一标识 RowID，查询无需回表，减少 IO；

1.2 非聚簇索引 NonClustered

无主键表自动生成隐式_tidb_rowid作为行标识，二级索引需要回表；

1.3 二级索引

不宜过多创建，大量索引会拖慢 DML 写入；适合 WHERE 高基数过滤字段。

2.TiFlash、Placement、数据安全与特殊表型

2.1. TiFlash 核心要点

语法：添加 TiFlash 副本使用 ALTER TABLE t1 SET TIFLASH REPLICA 1，关键字为SET而非 ENABLE；

执行：优化器会自动判断选择 TiFlash 执行，业务侧无需额外添加 HINT。

2.2. Placement Rules 调度规则

副本配比：默认 3 副本对应 FOLLOWERS=2；

区域管控：REGIONS 可指定覆盖全部区域，支持配置 PRIMARY_REGION 设定主区域。

2.3. 数据恢复（Flashback）

支持范围：DROP、TRUNCATE 表可闪回；v6.4 及以上版本支持 DROP DATABASE 闪回恢复；

前置条件：操作时间点仍在 GC safe point 有效期内，过期无法恢复。

2.4. 三类特殊表约束

• CACHE TABLE

• 表缓存后无法直接执行 DDL 操作，执行 DDL 前必须先执行 NOCACHE 解除缓存。

• GLOBAL TEMPORARY TABLE

• 全局可见；事务结束自动清空数据；库内不允许重名。

• SQL MODE

• ANSI 模式默认包含 ONLY_FULL_GROUP_BY 严格分组校验规则。

3. 排序、分页与执行计划

• 排序稳定性（TiDB 分布式）

• 分布式查询必须写ORDER BY；排序条件要带上唯一字段（如_tidb_rowid），否则重复值会导致排序结果不稳定。

• 分页

• 大偏移量LIMIT offset,n大offset性能差；优先用Keyset Seeker（where + 索引范围扫描）分页，LIMIT offset不是最佳实践。

• 执行计划

  • EXPLAIN：只输出计划，不执行 SQL
  • EXPLAIN ANALYZE：真实执行，返回运行统计
  • root：TiDB 节点执行；cop[tikv]下推 TiKV；cop[tiflash]下推 TiFlash。

EXPLAIN SELECT * FROM t WHERE id > 100;
--结果中cop[tikv]表示下推到TiKV；

5. SQL 开发最佳实践

1. 分页：大 OFFSET 分页性能极差，推荐 Seek+Limit 方式；
2. ORDER BY：必须包含唯一字段，才能保证排序稳定；
3. EXPLAIN：仅展示预估执行计划；ANALYZE 实际运行 SQL 并更新统计信息；
4. 数据恢复：DROP TABLE/TRUNCATE/DROP DATABASE 在 GC 快照未过期时，可用 FLASHBACK 恢复。

六、模块 06：高频速查

1. 架构基础模块（上册）

1.1. 核心组件状态分工

1.2. 数据类型与 SQL 基础坑点

• 浮点精度：1.1E0+2.2E0≠3.3E0，二进制浮点存储存在精度丢失，金额场景严禁用 FLOAT/DOUBLE

• 金额字段选型：必须使用DECIMAL定点类型，无精度损耗，适配金融余额核算

• int(M)：M 仅为展示宽度，不限制存储数值大小，和 MySQL 行为一致

• BIT(9)：占用 9 个比特位，用于存储位标识场景

• CHAR(10)：固定占用 10 个字符存储空间，尾部空格不会自动忽略

• ENUM 语法：仅支持字符串字面量定义ENUM('10','20')，ENUM(10,20)语法非法

• NULL 运算规则

  • NULL=NULL结果为 NULL，三值逻辑中 NULL 不等于任何值
  • CONCAT(NULL,任意值)整体返回 NULL，任一参数为 NULL 结果即为 NULL

• 运算特殊规则

  • 8/0返回 NULL 并附带警告，不会直接会话崩溃
  • ||默认是逻辑 OR 运算符，非字符串拼接，需调整 sql_mode 开启拼接能力

• 集合与函数

  • UNION自动去重排序，UNION ALL直接合并结果、无去重开销，性能更优
  • RLIKE正则匹配无法下推 TiKV 协处理器，全量数据拉取后在 TiDB 计算，大表查询性能差
  • INTERVAL是 SQL 关键字，不属于独立数据类型，用于时间偏移运算

2. 事务、开发运维高频考点

2.1. 事务核心规则

• 外键检查时机：DML 执行阶段校验，不是事务 COMMIT 时校验，违规 DML 直接报错阻断执行

• 重复 BEGIN 行为：TiDB 无嵌套事务，第二个 BEGIN 会隐式提交上一个活跃事务，极易造成误提交、回滚失效

• 支持隔离级别：默认 RR（可重复读），同时支持 READ COMMITTED 读已提交级别

• 悲观锁锁冲突：长事务 DML 持有元数据锁期间，DDL 会阻塞等待 DML 事务提交完成后执行

2.2. 主键与自增特性

• AUTO_RANDOM：主键高位加入随机分片 bit，打散自增主键末尾 Region 写入热点，高并发写入核心方案

• AUTO_INCREMENT：集群多节点批量分配号段，无法保证全局严格单调递增，仅单节点号段内有序

• 非聚簇主键：无显式主键时，TiDB 自动生成隐式列_tidb_rowid作为聚簇索引主键

2.3. Prepared Statement 预处理

• PS 无法提前锁定最优执行计划，参数不同可能触发计划重新生成
• 不支持预编译多条 SQL 语句，单 PREPARE 仅绑定单条 SQL 模板

2.4. 配套运维特性

• Sequence 序列：默认缓存 1000 个号段，不同 TiDB 节点分配独立号段，避免冲突

• TiFlash 运维规范

  • 启用语法：ALTER TABLE 表名 SET TIFLASH REPLICA n，禁止使用 ENABLE 关键字
  • 无需手动 HINT，优化器根据代价自动选择行存 TiFlash 执行查询

• Placement 副本规则：标准 3 副本配置对应FOLLOWERS=2，主副本 + 2 个跟随副本

• 闪回恢复能力：v6.4 及以上版本支持DROP DATABASE闪回，前提操作时间在 GC safe point 有效期内

• Cache 表约束：CACHE TABLE 缓存表无法直接执行 DDL，必须先执行 NOCACHE 解除缓存再变更表结构

• REPLACE 语义：主键冲突时删除旧行插入新行，和INSERT ... ON DUPLICATE KEY UPDATE在主键场景功能等效

• SQL 分析命令

  • EXPLAIN仅解析预估执行计划，不真实执行 SQL
  • ANALYZE真实执行 SQL，展示实际执行耗时、扫描行数，用于定位真实慢 SQL 瓶颈
  • JOIN 与分页规范
  • LEFT JOIN：ON 条件只做关联匹配、不过滤左表数据；WHERE 条件会过滤最终结果集，二者逻辑差异极大
  • LIMIT 大 offset 分页：offset 过大会全量扫描前置数据再截断，性能极差，推荐主键分页游标方案
  • ORDER BY 稳定排序：排序字段必须包含唯一字段（主键），避免分页结果错乱