【PCSD考试笔记】

连接池参数

maximumPoolSize：连接池最大连接数 minimumIdle：连接池最小空闲连接数

预处理语句 (Prepared Statements)

对于 OLTP 场景，程序发送给数据库的 SQL 语句在去除参数变化后都是可穷举的某几类。因此建议使用预处理 语句 (Prepared Statements) 代替普通的文本执行，并复用预处理语句来直接执行，从而避免 TiDB 重复解析和生 成 SQL 执行计划的开销。

useServerPrepStmts = true cachePrepStmts = true useConfigs = maxPerformance #最优性能配置，包含cachePrepStmts = true

Batch批量插入更新

rewriteBatchedStatements = true

注：因为一个客户端bug，批量更新时如果要配置rewriteBatchedStatements = true和useServerPrepStmts ,→ = true，推荐同时配置 allowMultiQueries = true 参数来避免这个 bug

使用 StreamingResult 流式获取执行结果

设置 FetchSize 为 Integer.MIN_VALUE 让客户端不缓存，客户端通过 StreamingResult 的方式从网络连接上流 式读取执行结果。

使用 Cursor Fetch，首先需设置 FetchSize 为正整数，且在 JDBC URL 中配置 useCursorFetch = true。

注：推荐使用第一种将 FetchSize 设置为 Integer.MIN_VALUE 的方式，比第二种功能 实现更简单且执行效率更高。

超时参数

sessionVariables=wait_timeout=3600 #（1 小时）与Java应用连接的空闲超时时间 sessionVariables=max_execution_time=300000 #（5 分钟）连接中SQL执行的超时时间

索引Index

Primary Key: 即主键索引，即标识在主键列上的索引

Primary Key 的定义为：唯一，不为空。

不保证为聚簇索引，由另一组 关键字 CLUSTERED、NONCLUSTERED 额外控制 Primary Key 是否为聚簇索引，若不指定，则由系统 变量 @@global.tidb_enable_clustered_index 影响

Secondary Index: 即二级索引，即在非主键上标识的索引

聚簇索引：（索引组织表）

主键列数据（键）- 行数据（值）

非聚簇索引：

_tidb_rowid（键）- 行数据（值）

主键列数据（键）- _tidb_rowid（值）

主键

一个表可以没有主键，主键也可以是非整数类型。但此时 TiDB 就会创建一个tidb_rowid 作为隐式主键。隐式主键 tidb_rowid 因为其单调递增的特性，可能在大批量写入场景下会导致写入热点，如果你写入量密集， 可考虑通过SHARD_ROW_ID_BITS 和PRE_SPLIT_REGIONS 两参数控制打散。但这可能导致读放大，请自行取舍。 表的主键为整数类型且使用了 AUTO_INCREMENT 时，无法使用 SHARD_ROW_ID_BITS 消除热点。需解决此热点问 题，且无需使用主键的连续和递增时，可使用AUTO_RANDOM 替换 AUTO_INCREMENT 属性来消除行 ID 的连续性。

临时表

本地临时表：表定义和表内数据只对当前会话可见，适用于暂存会话内的中间数据

本地临时表会在会话结束后连同数据和表结构都进行自动清理

全局临时表：全局临时表的表定义对整个 TiDB 集群可见，表内数据只对当前事务可见，适用于暂存事务内的中间数 据

全局临时表在事务结束后会自动清除数据，但是表结构依然保留，需要手动删除。

INSERT ON DUPLICATE KEY UPDATE 最佳实践

在仅有一个唯一键的表上使用 INSERT ON DUPLICATE KEY UPDATE。此语句在检测到任何 唯一键 (包括主 键) 冲突时，将更新数据。在不止匹配到一行冲突时，将只会一行数据。因此，除非能保证仅有一行冲 突，否则不建议在有多个唯一键的表中使用 INSERT ON DUPLICATE KEY UPDATE 语句。

批量更新

你需要更新大量行 (数万或更多) 的时候，建议使用一个迭代，每次都只更新一部分数据，直到更新全 部完成。这是因为 TiDB 单个事务大小限制为txn-total-size-limit（默认为 100MB），且一次性过多的数据更新，将 导致持有锁时间过长（悲观事务），或产生大量冲突（乐观事务）。你可以在程序或脚本中使用循环来完成操 作。

TiDB单个事务大小限制

限制为txn-total-size-limit （默认为 100MB）

删除数据

如果你需要删除表内的所有数据，请勿使用 DELETE 语句，而应该使用TRUNCATE 语句。

DELETE 语句运行之后 TiDB 并非立刻删除数据，而是将这些数据标记为可删除。然后等待 TiDB GC (Garbage Collection) 来清理不再需要的旧数据。因此，你的 DELETE 语句并不会立即减少磁盘用量。

在大批量的数据删除之后，很有可能会导致索引选择不准确的 情况发生。你可以使用手动收集的办法，更新统计信息。

TiDB 从 v6.1.0 版本开始支持非事务 DML 语句特性。非事务性删除可以提高性能。

使用 TTL (Time to Live) 定期删除过期数据（使用场景）

定期删除验证码、短网址记录

定期删除不需要的历史订单

自动删除计算的中间结果

不允许在临时表上设置 TTL 属性

不保证所有过期数据立即被删除，过期数据被删除的时间取决于后台清理任务的调度周期和调度窗口

日期时间类型问题

和 MySQL 一样，TIMESTAMP 数据类型受 2038 年问题的影响。如果存储的值大于 2038，建 议使用 DATETIME 类型。

在MySQL中，TIMESTAMP数据类型使用4字节来存储时间，这导致其能表示的时间范围有限，最大只能到2038年1月19日03:14:07（UTC）。这就是所谓的"2038年问题"或"Y2K38问题"。

与TIMESTAMP不同，DATETIME不受2038年问题的影响，它使用8字节存储日期和时间，可以表示从1000年1月1日00:00:00到9999年12月31日23:59:59的时间范围。

预处理语句

预处理语句是一种将多个仅有参数不同的 SQL语句进行模板化的语句，它让 SQL 语句与参数进行了分离

避免sql注入风险

提高sql执行性能

应用程序中sql语句是可以枚举的，所以预处理语句是最佳实践之一

通用场景的JDBC 连接字符串配置:jdbc:mysql://127.0.0.1:4000/test?user=root&useConfigs=maxPerformance&useServerPrepStmts=true&prepStmtCacheSqlLimit=2048&prepStmtCacheSize=256&rewriteBatchedStatements=true&allowMultiQueries=true

公共表表达式 (CTE)

一个临时的中间结果集，能够在 SQL 语句中引用多次，提高 SQL 语句的可读性与执行效 率

事务

实现了快照隔离 (Snapshot Isolation, SI) 级别的一致性

TiDB 支持的隔离级别是 RC（ Read Committed）与 SI（ Snapshot Isolation），其中 SI 与 RR（ Repeatable Read）隔离级别基本等价

SI 可以克服幻读，但 ANSI/ISO SQL 标准中的 RR 不能。

SI 不能克服写偏斜

支持的最大单个事务容量为 10GB

最大单行记录容量为 120MB

为了使性能达到最优，建议每 100 ～ 500 行写入一个事务。

优化 SQL 性能

使用单个语句多行数据操作

使用 PREPARE

避免查询不必要的信息

使用批量删除

使用批量更新

使用 TRUNCATE 语句代替 DELETE 全表数据

建立你需要使用的数据的所有列的组合索引，这种优化技巧被称为覆盖索引优化 (covering index optimization)。

区分度比较大的列，通过索引能显著地减少过滤后的行数。

有多个查询条件时，可以选择组合索引，注意需要把等值条件的列放在组合索引的前面

使用组合索引时，需要满足最左前缀原则

在查询条件中使用索引列作为条件时，不要在索引列上做计算，函数，或者类型转换的操作，会导致 优化器无法使用该索引。

尽量使用覆盖索引，即索引列包含查询列，避免总是 SELECT * 查询所有列的语句。

查询条件使用 !=， NOT IN 时，无法使用索引。

使用 LIKE 时如果条件是以通配符 % 开头，也无法使用索引

隐式类型转换会导致： • 索引失效 • 精度丢失