0
2
4
0
专栏/.../

【PCSD考试笔记】

 panlonyin  发表于  2023-07-28

连接池参数

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

0
2
4
0

版权声明:本文为 TiDB 社区用户原创文章,遵循 CC BY-NC-SA 4.0 版权协议,转载请附上原文出处链接和本声明。

评论
暂无评论