一、PCSD认证说明

‍

Tidb认证（平凯数据库认证）考试说明：

https://learn.pingcap.cn/learner/certification-center

价值：TiDB的国产数据库排名在前3，TiDB在国际DB排行榜排名在72（高于国内其他数据库），可见TiDB是被国内外都认可的。本身的认证证书同样具有公信力。

然后，如果你想去的公司想用或者在用Tidb，那么你就赚大了！

‍

考试报名

https://learn.pingcap.cn/learner/exam-market/list?category=PCSD

‍

积分兑换

https://asktug.com/t/topic/663967

积分攻略

https://asktug.com/t/topic/1024366

‍

PCSD考试范围指引

https://asktug.com/t/topic/994111

备考需要学习课程 ，在考试范围内写的很清楚。如果你是java开发的话，那么学习201.1~201.5

‍‍

学习课程

https://learn.pingcap.cn/learner/course

‍二、201课程总结

‍

• 学习路径

‍

• 课程涉及脚本

201.2

• KeySet Seeker 操作深度分页解决方式： 每次记住下次分页的开始位置这种方式其实只适合于ID严格递增MySQL高性能中给出了深度分页的解决方式： 使用in (select id from ... limit m , offset n)
• NULL 是order by的最小值
• 窗口函数

• partion by
• LAG/LEAD

# 在门店状态变化记录表中，取最后一次变更的类型（即，在滚动记录表中取最新值）
select t.store_id, t.type
from (select store_id, type, rank() over (partition by store_id order by begin_date desc) as rk
      from erp_fi_store_type_period) t
where t.rk = 1
order by t.store_id;

select abbr, LAG(abbr, 1, '-') over (order by sort) AS 前一个, LEAD(abbr, 1, '-') over (order by sort) AS 后一个
from erp_area
order by sort;

• 表连接

• UNION默认 union DISTINCT ， 保留重复记录 Union ALL
• TIDB支持差集(Except)和交集(Intercept)

# 差集 (前 - 后)
SELECT id FRoM a EXCEPT sELECT id FRoM c;
(in a and not in c)
# 交集
SELECT id FRoM a intersect sELECT id FRoM c;
(in a and in c)

• 子查询

• IN 和 EXISTS 区别： IN 先子查询，后匹配； EXISTS先外查询，再匹配
• WITH (创建临时表，提升可读性)

WITH temp_table AS (
    SELECT * FROM my_table WHERE column1 = 'value'
)
SELECT * FROM temp_table WHERE column2 = 'another_value';

‍

‍

201.3

• TiDB 以区分大小写的方式存储对象名，但以不区分大小写的方式进行比较

show variables like 'lower_case_%';

• TiDB 中的默认字符集是 utf8mb4，与 MySQL 8.0 及更高版本中的默认字符集匹配

CREATE DATABASE universe DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

• 排序规则 (collation) 是在字符集中比较字符以及字符排序顺序的规则， 如果字符集不一致也会导致JOIN的时候无法走索引utf8mb4_bin 和 utf8mb4_general_ci 差异
• SHOW CREATE TABLE 语句用于显示创建已有表的确切语句， 能够看到没有显性声明的、但实际生效的属性
• 复制表

create table test.dropme2 like test.dropme;

• 临时表：仅对创建它的会话可见， 不会出现在 information_schema.tables

CREATE TEMPORARY TABLE:

# 全局临时表
CREATE GLOBAL TEMPORARY TABLE

• 闪回表：垃圾回收生命周期窗口之内还原被丢弃的表和数据 (机制： MVCC)

flashback table test.r1 to r2

• AUTO INCREMENT

1. 递增，非严格递增，分段分配给每个Server
2. show variables like 'auto increment %';
3. LAST INSERT ID(): 检索上次插入操作所使用的值

• AUTO_RANDOM

是一个字段属性，用于自动随机填充默认列值以解决热点问题

随机分片，仅支持 BIGINT 数据类型

类似GUID， 但是是BIGINT类型，效率更高，最佳实践

DML不会阻塞DDL，原理：在线变更 DDL，是在两个状态（旧DDL->新DDL）之间引入多个相互兼容的小版本状态， 通过多个小版本演进的方式以 ADD INDEX 为例，整个变更状态流程如下：absent -> delete only -> write only -> write reorg -> public

• 主键（如果没有主键或主键不是Integer，则会创建非聚簇的隐藏主键）聚簇索引和非聚簇索引差异：

1. 聚簇索引将数据行按照索引键的顺序存储，因此查询聚簇索引键的操作效率较高。
2. 非聚簇索引存储索引键及其对应的指针，指向数据行的存储位置。数据行的存储顺序与索引键的顺序无关，因此非聚簇索引通常需要更多的磁盘 I/O 操作来定位实际数据行。

# 使用UUID作为主键
explain select * from t_s_log where id='297e3dd9899f9cea01899fa11d8e0000';
# 使用自增ID为主键
explain select * from erp_sys_conf where id=1;

• 外键： 支持语法，但不检查
• 缓存表，分区表（针对全表扫描的优化方式）

缓存表 -- 将不频繁修改小表放入缓存， 注意：

1. 对缓存表执行 DDL 语句会失败。若要对缓存表执行 DDL 语句，需要先去掉缓存属性，将缓存表设回普通表后，才能对其执行 DDL 语句
2. 目前 TiDB 对于每张缓存表的大小限制为 64 MB
3. 往缓存表写入数据时，有可能出现秒级别的写入延迟

• 分区表 （可以将全表扫描优化为分区扫描，节省IO ；针对传统冗余大表优化效果应该很好）

1. Range分区（Range 必须是连续的，并且不能有重叠）
2. Hash分区

# 修改表格是否缓存
ALTER TABLE table_name CACHE | INOCACHE ;

# 确认表是否开启缓存方式一，带有 CACHED ON 属性
SHOW CREATE TABLE users;

# 确认表是否开启缓存方式二，不带有regionRequest.SendReqCtx，表示不从TiKV读取
TRACE SELECT * FROM users;

create table test.t1(x int) partition by range(x)
(partition p0 values less than(5),
 partition p1 values less than(10)
);

# 创建一个 Hash 分区表，按 store_id 分成 4 个分区
CREATE TABLE employees (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

• Placement Policy (针对集群多区域部署)

1. 用户可以将表指定部署至不同的地域、主机
2. 支持应用跨地域部署
3. 保证本地的数据副本可用于本地 Stale read 读取

• TiFlash（优化分析查询，对于列很多，优势明显；原理：kvRaft中作为learner参与复制）

• 加速聚合

• DML

1. REPLACE 语句
2. 避免重复行记录错误（INSERT ... ON DUPLICATE KEY UPDATE）
3. TRUNCATE非事务方式从表中删除所有数据，无法回滚！！语义可以认为（DROP TABLE + CREATE TABLE）

# 主键或唯一约束重复则 [替换行] ，否则插入 ; 简化删除+插入操作
REPLACE INTO test.r1 (id,name) VALUES (2,'C');

# 类似于 REPLACE, 但它使用 UPDATE 而不是 DELETE 来处理重复的行记录
insert into test.r1 (id, name) values (2, 'B') on duplicate key update name='B';

• 事务

1. 手动事务（无法嵌套）、隐性事务
2. 因果一致性事务在因果一致性的情况下，只有事务 1 和事务 2 加锁或写入的数据有交集时，才能保证事务的提交顺序与事务的发生顺序保持一致
3. 隔离级别（REPEATABLE-READ）
4. 预编译

# 如果2步骤包含 权限管理、DDL、Begin(START TRANSACTION) 则会触发隐式Commit
1) Begin
2) ...(DML)
3) Commit / Rollback

PREPARE pName SQL1;

EXECUTE SQL1 USING @p1,@p2;

‍

201.4

• JDBCjava -cp .:misc/mysql-connector-java-5.1.36-bin.jar DemoJdbcConnection5.1.36兼容Mysql5.7最高版本
• 处理SQL中特殊字符

1. \
2. `` 反单引号包围
3. 占位符+预编译

• jdbc预编译
• jdbc批量，参数：rewriteBatchedStatements=true ，实例测试总结
• SQL模式（select @@sql_mode;）

• ONLY_FULL_GROUP_BY在Mysql5.7版本中，ONLY_FULL_GROUP_BY默认为Flase，而Tidb中默认为True。可以通过修改sql_mode改为一致如果取消了ONLY_FULL_GROUP_BY限制，那么group by取的非group列值为主键排序第一行

• ‍
• 异常处理（设置不自动提交，通过try catch，获取错误码，灵活控制数据更新逻辑）

‍

201.5

• SQL最佳实践

1. 事务

1. 二级索引创建索引最佳实践

• Java开发最佳实践

1. 启用服务端预编译 （useServerPrepStmts=true）
2. 批量插入，启用Insert重写（rewriteBatchedStatements=true）
3. 大结果集的场景中使用 StreamingResultJDBC 默认提前获取查询结果并将其保存在客户端内存中两种推荐的处理方式

1. 设置 Fechsize 为 Integer.MIN VALUE 让客户端不缓存,并使用 streamingResult
2. 设置 JDBC 参数 usecursorFetch为 true,并设置语句参数 Fetchsize 为正整数

5. 推荐连接参数汇总

// 使用utf-8编码，注意：这是java中的编码，在mysql中utf8mb4对应的也是这个
characterEncoding=utf8
// 不使用证书
useSSL=false
// 使用服务器端预处理语句
useServerPrepStmts=true
// 指定单个预处理语句缓存的大小限制，默认256
&prepStmtCacheSglLimit=10000000000
// 指定要使用的预定义配置:高性能
&useConfigs=maxPerformance
// 指定是否将批量插入语句重写为多行插入语句
&rewriteBatchedStatements=true
// 从服务器返回行数，默认0，即默认一次返回所有行
&defaultfetchsize=-214783648

‍