【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 时如果条件是以通配符 % 开头，也无法使用索引
• 隐式类型转换会导致：
  • 索引失效
  • 精度丢失