【PCSD 考试笔记】

作者： panlonyin 原文来源：https://tidb.net/blog/f493defd

连接池参数

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

预处理语句 (Prepared Statements)

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

useServerPrepStmts = truecachePrepStmts = 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 indexoptimization)。

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

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

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

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

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

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

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

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