TiDB日常使用规范

子林super
137 阅读1小时+

概述

作为一家全球性的连锁餐饮企业，公司在处理海量数据、复杂业务流程方面面临巨大的挑战。为了解决这些问题，公司中国选择了分布式数据库TiDB来支持部分核心中台系统和财务系统的数据存储和管理。

TiDB是一种极具可扩展性和灵活性的分布式数据库，可以有效地解决公司的数据存储和处理问题。TiDB还拥有更加丰富的数据管理和分析功能，可以帮助公司进行更深入的业务分析和预测，为企业提供更好的业务决策支持。为了规范TiDB的部署和运维工作、指导开发人员正确地使用TiDB，IT运维团队结合TiDB的技术特点以及公司实际现状，制定了此份技术白皮书。

本技术白皮书可以供DBA，系统应用运维人员，以及应用开发者使用。随着公司的TiDB管理策略不断进化，扩展和更新内容。




一、TiDB介绍

  1. TiDB简介

TiDB 是 PingCAP 公司自主设计、研发的开源分布式关系型数据库，是一款同时支持在线事务处理与在线分析处理 (Hybrid Transactional and Analytical Processing, HTAP) 的融合型分布式数据库产品，具备水平扩容或者缩容、金融级高可用、实时 HTAP、云原生的分布式数据库、兼容 MySQL 5.7 协议和 MySQL 生态等重要特性。目标是为用户提供一站式 OLTP (Online Transactional Processing)、OLAP (Online Analytical Processing)、HTAP 解决方案。TiDB 适合高可用、强一致要求较高、数据规模较大等各种应用场景。

  1. 通用架构

TiDB Server：SQL 层，对外暴露 MySQL 协议的连接 endpoint，负责接受客户端的连接，执行 SQL 解析和优化，最终生成分布式执行计划。TiDB 层本身是无状态的，实践中可以启动多个 TiDB 实例，通过负载均衡组件（如 LVS、HAProxy 或 F5）对外提供统一的接入地址，客户端的连接可以均匀地分摊在多个 TiDB 实例上以达到负载均衡的效果。TiDB Server 本身并不存储数据，只是解析 SQL，将实际的数据读取请求转发给底层的存储节点 TiKV（或 TiFlash）。

PD (Placement Driver) Server：整个 TiDB 集群的元信息管理模块，负责存储每个 TiKV 节点实时的数据分布情况和集群的整体拓扑结构，提供 TiDB Dashboard 管控界面，并为分布式事务分配事务 ID。PD 不仅存储元信息，同时还会根据 TiKV 节点实时上报的数据分布状态，下发数据调度命令给具体的 TiKV 节点，可以说是整个集群的“大脑”。此外，PD 本身也是由至少 3 个节点构成，拥有高可用的能力。建议部署奇数个 PD 节点。

TiKV Server：负责存储数据，从外部看 TiKV 是一个分布式的提供事务的 Key-Value 存储引擎。存储数据的基本单位是 Region，每个 Region 负责存储一个 Key Range（从 StartKey 到 EndKey 的左闭右开区间）的数据，每个 TiKV 节点会负责多个 Region。TiKV 的 API 在 KV 键值对层面提供对分布式事务的原生支持，默认提供了 SI (Snapshot Isolation) 的隔离级别，这也是 TiDB 在 SQL 层面支持分布式事务的核心。TiDB 的 SQL 层做完 SQL 解析后，会将 SQL 的执行计划转换为对 TiKV API 的实际调用。所以，数据都存储在 TiKV 中。另外，TiKV 中的数据都会自动维护多副本（默认为三副本），天然支持高可用和自动故障转移。

TiFlash：TiFlash 是一类特殊的存储节点。和普通 TiKV 节点不一样的是，在 TiFlash 内部，数据是以列式的形式进行存储，主要的功能是为分析型的场景加速。

  1. 核心特性

  • 一键水平扩缩容

得益于 TiDB 存储计算分离的架构的设计，可按需对计算、存储分别进行在线扩容或者缩容，扩容或者缩容过程中对应用运维人员透明。

  • 金融级高可用

数据采用多副本存储，数据副本通过 Multi-Raft 协议同步事务日志，多数派写入成功事务才能提交，确保数据强一致性且少数副本发生故障时不影响数据的可用性。可按需配置副本地理位置、副本数量等策略，满足不同容灾级别的要求。

  • 实时 HTAP

提供行存储引擎 TiKV、列存储引擎 TiFlash 两款存储引擎，TiFlash 通过 Multi-Raft Learner 协议实时从 TiKV 复制数据，确保行存储引擎 TiKV 和列存储引擎 TiFlash 之间的数据强一致。TiKV、TiFlash 可按需部署在不同的机器，解决 HTAP 资源隔离的问题。

  • 云原生的分布式数据库

专为云而设计的分布式数据库，通过 TiDB Operator 可在公有云、私有云、混合云中实现部署工具化、自动化。

  • 兼容 MySQL 协议和 MySQL 生态

兼容 MySQL 协议、MySQL 常用的功能、MySQL 生态，应用无需或者修改少量代码即可从 MySQL 迁移到 TiDB。提供丰富的数据迁移工具帮助应用便捷完成数据迁移。

  1. 适用场景

1.对数据一致性及高可靠、系统高可用、可扩展性、容灾要求较高的金融行业属性的场景

众所周知，金融行业对数据一致性及高可靠、系统高可用、可扩展性、容灾要求较高。传统的解决方案是同城两个机房提供服务、异地一个机房提供数据容灾能力但不提供服务，此解决方案存在以下缺点：资源利用率低、维护成本高、RTO (Recovery Time Objective) 及 RPO (Recovery Point Objective) 无法真实达到企业所期望的值。TiDB 采用多副本 + Multi-Raft 协议的方式将数据调度到不同的机房、机架、机器，当部分机器出现故障时系统可自动进行切换，确保系统的 RTO <= 30s 及 RPO = 0。

2.对存储容量、可扩展性、并发要求较高的海量数据及高并发的 OLTP 场景

随着业务的高速发展，数据呈现爆炸性的增长，传统的单机数据库无法满足因数据爆炸性的增长对数据库的容量要求，可行方案是采用分库分表的中间件产品或者 NewSQL 数据库替代、采用高端的存储设备等，其中性价比最大的是 NewSQL 数据库，例如：TiDB。TiDB 采用计算、存储分离的架构，可对计算、存储分别进行扩容和缩容，计算最大支持 512 节点，每个节点最大支持 1000 并发，集群容量最大支持 PB 级别。

3.Real-time HTAP 场景

随着 5G、物联网、人工智能的高速发展，企业所生产的数据会越来越多，其规模可能达到数百 TB 甚至 PB 级别，传统的解决方案是通过 OLTP 型数据库处理在线联机交易业务，通过 ETL 工具将数据同步到 OLAP 型数据库进行数据分析，这种处理方案存在存储成本高、实时性差等多方面的问题。TiDB 在 4.0 版本中引入列存储引擎 TiFlash 结合行存储引擎 TiKV 构建真正的 HTAP 数据库，在增加少量存储成本的情况下，可以在同一个系统中做联机交易处理、实时数据分析，极大地节省企业的成本。

4.数据汇聚、二次加工处理的场景

当前绝大部分企业的业务数据都分散在不同的系统中，没有一个统一的汇总，随着业务的发展，企业的决策层需要了解整个公司的业务状况以便及时做出决策，故需要将分散在各个系统的数据汇聚在同一个系统并进行二次加工处理生成 T+0 或 T+1 的报表。传统常见的解决方案是采用 ETL + Hadoop 来完成，但 Hadoop 体系太复杂，运维、存储成本太高无法满足用户的需求。与 Hadoop 相比，TiDB 就简单得多，业务通过 ETL 工具或者 TiDB 的同步工具将数据同步到 TiDB，在 TiDB 中可通过 SQL 直接生成报表。

  1. YUM TiDB架构

TiDB集群是可以跨机房布署的，但考虑到跨机房布署集群对机房间的网络传输稳定性及性能是有较高要求的，因此在公司，TiDB集群是单机房布署的。为了做到跨机房高可用，在多机房布署多套TiDB集群，并且通过TiCDC进行准实时数据同步，这样多机房的TiDB集群可以互为备份，以下为中台TiDB架构图。

在正常情况下，多机房的服务是读写单机房的TiDB集群，在该集群发生故障时，可以直接切换数据请求到其它机房的备集群，从而实现数据库的故障在线实时恢复。

但是该架构存在如下风险：

  • TiCDC为异步复制，主备库数据无法保证完全一致，若发生线上主备切换场景，存在数据覆盖风险
  • KSA/WGA两个集群互为主备，切换后单个集群是否可以同时承载三个Hub的业务压力有待验证

TiCDC为单向同步，为了保证主备数据一致性，业务账号禁止写入备库；

为了避免人为操作风险&主备切换异常，主备业务账号信息必须一致，且在备库锁定。

TiDB 分布式数据库通过 Raft 算法原生支持两地三中心架构的建设，并保证数据库集群数据的一致性和高可用性。以北京、西安两地三中心配置为例详解：

北京有两个机房 IDC1 和 IDC2，异地西安一个机房 IDC3。北京同城两机房之间网络延迟低于 3 ms，北京与西安之间的网络使用 ISP 专线，延迟约 20 ms。

  • 集群采用 5 副本模式，其中 IDC1 和 IDC2 分别放 2 个副本，IDC3 放 1 个副本；TiKV 按机柜打 Label，确保每个机柜上有一份副本

  • 副本间通过 Raft 协议保证数据的一致性和高可用，对用户完全透明；可保证任一数据中心失效后，服务可用并且不发生数据丢失

  • 由于使用了网络专线，该架构下网络设施成本较高。此外，双区域三 AZ 需设置 5 副本，数据冗余度增加，空间成本攀升




二 、技术规范

  1. 版本规范

目前公司运维团队支持对如下TiDB版本的交付与维护工作：

  1. 版本升级策略

  1. 升级原则

  • 场景 1：当前版本已经超过厂商产品支持周期

数据运维团队会主动告知项目/业务组，当前数据库已经超过原厂数据版本维护周期。

告知风险：数据库不再提供问题修复新补丁，短期无法解决，需要升级解决。

如果项目/业务组判断业务代码不再有修改，风险较低，无需升级。其中风险项目/业务组进行承担。

  • 场景 2：有重大 BUG

数据运维团队发现当前版本有重大 bug，会影响：结果正确性、数据可用性等，导致数据库不可用的情况下，会主动推进升级事项。

  • 场景 3：项目组主动推动升级

当项目/业务组明确需要产品升级，满足一些性能/功能需求，则可以主动发起升级请求。

  1. 升级流程

暂时无法在飞书文档外展示此内容

  1. 其他说明

【测试】

  • 原则上要求进行非功能测试

  • 条件有限情况下至少完成功能测试

【版本回退】

  • 备份还原方式：接受长时间停机的情况下，升级后，如果发现问题，可以全量备份还原方式将数据库降级回原版本

  • 主从方式：接受短时间停机的情况下，申请新的服务器资源后搭建主从迁移方式进行升级

  • 原数据库版本回退：需要数据库支持原地版本回退功能

  1. 流程规范

流程规范主要针对生产集群上线、变更等操作流程规范定义，避免测试或者其它误操作情况造成集群性系列影响。

  • 推广活动或上线新功能必须提前通知 DBA 进行流量评估；

  • sql类变更需求均需通过公司云变更平台或公司云发布平台(pms)提交工单，当sql中存在error级别错误时，工单则不予通过。完整的sql审核规则清单请见本文档结尾的附录1。

  1. 事务限制

  • 隔离级别

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

核心业务的隔离级别使用 RC。

  • 大事务限制

TiDB 处理大事务的性能相较于处理小事务的性能要差一些，因此 TiDB 中对于事务量设定了一些限制：

  1. 最大单行记录容量为 120MB（v5.0 及更高的版本可通过 tidb-server 配置项 performance.txn-entry-size-limit 调整，单行容量默认为 6MB，不建议自行调整）

  2. 当使用 binlog 向下游 kafka 系统同步数据时，Kafka 消息限制 1G，建议 TiDB 事物大小不能超过 1G，一个事务中修改的行数不超过 5000 行（行数 * row size < 1G），大事务同步性能较差，可能导致同步失败

  3. 权限设计

为保障数据库安全，禁止程序使用root用户连接数据库，请使用专用的程序生产账号进行连接。线上所需用户建议按照用户或者业务场景划分，根据实际情况对每个用户授予相应权限。

例如：

序号用户名涵义用途
1root超级用户全局管理，禁止对外开放
2dba数据库管理员数据库 DBA
3app应用开发应用开发
4tempuser临时统计线上业务临时统计，只读用户
5other其他用户第三方人员访问

  1. 数据库模型设计

数据库模型设计是指对于一个给定的应用环境，构造合理的数据库模式，建立数据库及其应用系统，有效存储数据，满足用户信息要求和处理要求。数据库模型设计在开发过程中处于非常重要的地位，一个高效的数据库模型需要满足完整性、性能、扩展性。

  1. 完整性

数据库完整性是指数据库中数据的正确性和相容性，数据库完整性是由完整性约束来保证的，数据库完整性对于数据库应用系统非常关键，其作用主要体现在以下几个方面：

  • 利用完整性控制机制来实现业务规则，易于定义，容易理解，而且可以降低应用程序的复杂性，提高应用程序的运行效率。
  • 合理的数据库完整性设计，能够同时兼顾数据库的完整性和系统的效能。在应用软件的功能测试中，完善的数据库完整性有助于尽早发现应用软件的错误。

为了在数据库和应用程序代码之间提供另一层抽象，可以为应用程序建立专门的视图而不必非要应用程序直接访问数据表，这样做等于在处理数据库变更时提供了更多的自由。

  1. 性能

性能是衡量一个系统的关键因素，在设计阶段就在性能方面就应该多关注，尽量减少后期的烦恼。在数据库设计阶段，性能上的考虑时需要注意：不能以范式作为唯一标准或者指导，在设计过程中，需要从实际需求出发，以性能提升为根本目标来展开设计工作，一些时候为了提升性能，甚至会做反范式设计。

另外还有一些设计上的方法和技巧：

  • 设置合理的字段类型和长度。字段类型在满足需求后应尽量短，比如，能用 int 就尽量不要用 bigint。另外不同数据库在 varchar 和 text 类型在长度和性能上也是不同的，选择时要谨慎。

  • 选择高效的主键和索引。由于对表记录的读取都是直接或者间接地通过主键或索引来获取，因此应该该根据具体应用特性来设计合理的主键或索引。同时索引长度的也应该关注，尽量减少索引长度。

  • 适度冗余。适度的冗余可以避免关联查询，减少 join 查询。

  1. 扩展性

在大规模系统中，除了性能，可扩展性也是设计的关键点，而数据库表扩展性主要包含表逻辑结构、功能字段的增加、分表等。在扩展性上要把握的原则如下：

  • 一表一实体。如果不同实体之间有关联时，可增加一个单独的表，不会影响以前的功能。

  • 使用分区表。当前支持的类型包括 Range 分区、 Hash 分区和 List 分区。Range 分区可以用于解决业务中大量删除带来的性能问题，支持快速删除分区。Hash 分区则可以用于大量写入场景下的数据打散。List 分区和 Range 分区有很多相似的地方。不同之处主要在于 List 分区中，对于表的每个分区中包含的所有行，按分区表达式计算的值属于给定的数据集合。




三 、使用规范

本节主要介绍实践中的公司TiDB最佳实践，并从数据库表结构设计、索引设计、sql使用和应用规约等方面，描述公司TiDB运维和使用规范。提高业务开发系统的规范性和代码的可读性，减轻维护工作量，提高工作效率。

为了数据安全和误删除快速恢复，推荐集群 gc 统一改为 12h +

TiDB 暂时不支持的特性：

  • 存储过程

  • 触发器

  • 自定义函数

  • 外键约束

  • 全文索引

  • 空间索引

  • 非 UTF8 字符集

  • v5.4 以上版本支持 GBK

  • 实验特性生产不允许使用

  1. 对象命名规范

用于规范数据库对象的命名，如数据库（DATABASE）、表（TABLE）、索引（INDEX）、用户（USER）等的命名约定。

【原则】

  • 命名建议使用具有意义的英文词汇，词汇中间以下划线分隔
  • 命名只能使用英文字母、数字、下划线，
  • 建议所有数据库对象使用小写字母
  • 避免用 TiDB 的保留字如：group，order 等作为单个字段名，部分保留字受窗口函数功能是否打开影响

a.【建议】数据库命名规范

建议按照业务、产品线或者其它指标进行区分，一般不要超过 20 个字符

如：临时库（tmp_crm）、测试库（test_crm）

b.【建议】表命名规范

  • 同一业务或者模块的表尽可能使用相同的前缀，表名称尽可能表达含义
  • 多个单词以下划线分隔，不推荐超过 32 个字符
  • 建议对表的用途进行注释说明，以便于统一认识，如：临时表（tmp_xxx）、备份表（bak_xxx）
  • 不同业务模块的表单独建立 DATABASE，并增加相应注释
  • 目前 TiDB 只支持将 lower-case-table-names 值设为 2，即按照大小写来保存表名，按照小写来比较（不区分大小写）

c.【建议】字段命名规范

  • 字段命名需要表示其实际含义的英文单词或简写
  • 建议各表之间相同意义的字段应同名
  • 字段也尽量添加注释，枚举型需指明主要值的含义，如”0 - 离线，1 - 在线”
  • 布尔值列命名为 [is_描述]。如 member 表上表示为 enabled 的会员的列命名为 is_enabled
  • 字段名不建议超过 30 个字符，字段个数不建议大于 80
  • 尽量避免使用保留字，如 order、from、desc 等，请参考官方保留字

d.【建议】索引命名规范

  • 唯一索引：uk_[表名称简写]_[字段名简写]
  • 普通索引：idx_[表名称简写]_[字段名简写]
  • 多单词组成的 column_name，取尽可能代表意义的缩写
  • 索引名不建议超过 32 个字符，上限 64 个字符

e.【建议】建表规范

  • create table if not exists table_name 或者 drop table if exists table_name 语句建议增加 if 判断，避免应用侧由于表的改动造成的异常中断
  • 表需要有主键或者唯一索引，需要唯一索引所有字段非空（避免出现多条空值的重复记录）
  • 出于为性能考虑，尽量避免存储超宽表，表字段数不建议超过 80 个，建议单行的总数据大小不要超过 64K
  • 不推荐使用复杂的数据类型，如 TEXT、BLOB 类型
  • TiDB 字符集默认是 utf8mb4，字符序为 utf8mb4_bin
  • 分区表主键和唯一索引需要包含分区键
  • 不得使用外键，在应用端实现

f.【建议】表字段设计规范

  • 浮点类型，推荐使用 DECIMAL 类型

float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果，不建议使用。

DECIMAL 在与 VARCHAR、CHAR 类型比较时会转换为 DOUBLE 类型进行比较，也存在精度损失问题，建议在进行比较时使用显示类型转换，如 CAST 避免精度损失。

  • 日期时间类型，禁止使用 TIMESTAMP 类型（TIMESTAMP 数据类型受 2038 年问题的影响）

DATE：所有只需要精确到天的字段全部使用 DATE 类型，而不应该使用 TIMESTAMP 或者 DATETIME 类型。

DATETIME：所有需要精确到时间（时分秒）的字段均使用 DATETIME，不要使用 TIMESTAMP 类型。

TIME：仅有时分秒的数据使用 TIME 类型。

时间字段使用时间日期类型，不要使用字符串类型存储。

  • 字符串类型，推荐使用 VARCHAR（N）

VARCHAR(N)：所有动态长度字符串全部使用 VARCHAR 类型，N 表示的是字符数不是字节数，比如 VARCHAR(256)，需要根据实际的宽度来选择 N，N 尽可能小。

TEXT：仅仅当字符数量可能超过 20000 个的时候，才建议使用 TEXT 类型来存放字符类数据。所有使用 TEXT 类型的字段建议和原表进行分拆，与原表主键单独组成另外一个表进行存放。

不建议使用 ENUM、SET 类型，尽量使用 TINYINT 来代替。

  • 主键准则

表必须有主键或唯一索引且非空。

写入量较小，无热点问题时可以使用 AUTO_INCREMENT 自增主键，存在热点问题时可以使用 shard bit 特性进行打散。

写入量较大的表，应避免使用连续自增的值对主键进行填充。

不使用更新频繁的列作为主键。

主键越短越好，最好是整型或自增类型（优先使用业务主键），如果不能使用自增，禁止使用应用定义随机类型值。

尽量不选择字符串列作为主键。

主键列不超过 256 字节，过长可能会影响性能。

g.【建议】索引设计规范

  • 选择区分度大的列建立索引，不在低基数列上建立索引，例如：“性别”，“是否是 XXX”。
  • 单张表的索引数量控制在 5 个以内，建议优先考虑覆盖索引，避免冗余索引。
  • 索引中的字段数建议不超过 5 个。
  • 索引的总长度不超过 256 字节，否则过长可能会影响性能。
  • 尽量不要在频繁更新的列上创建索引。
  • 最左前缀原则，使用联合索引时，从左向右匹配

比如索引 idx_c1_c2_c3 (c1,c2,c3)，相当于创建了 (c1)、(c1,c2)、(c1,c2,c3) 三个索引

where 条件包含上面三种情况的字段比较则可以用到索引

但像 where c1=a and c3=c 只能用到 c1 列的索引

像 c2=b and c3=c 等情况就完全用不到这个索引

  • 很长的 VARCHAR 字段建立索引时，指定索引长度，没必要对全字段建立索引

根据实际文本区分度决定索引长度即可，比如 idx_table_name (name(10))

  • 定期删除一些长时间未使用过的索引
  • ORDER BY，GROUP BY，DISTINCT 的字段需要添加在索引的后面，形成覆盖索引。

  1. SQL语句规范

a.【推荐】用 UNION ALL 而不是 UNION

UNION 会将两个结果集中的记录进行排除重复记录的操作

UNION ALL 没有这个操作，因此使用后者可以更快返回记录

在业务上没有特殊要求的时候，应该优先使用 UNION ALL

b.【推荐】SQL 文本不宜过长

SQL 文本过长时会造成 SQL 编译耗时长、内存使用增加

严重时，还可能导致数据库无法解析，甚至内存泄漏耗尽操作系统可用内存

因此，建议 OLTP 系统 SQL 文本长度不超过 2000 字节，OLAP 系统不超过 20000 字节

c.【推荐】SELECT 语句规范

禁止使用 select * 进行查询

禁止使用 DISTINCT * 操作

避免对数据量较大的表重复值非常少的列进行 DISTINCT 操作，结果集过大容易 OOM

避免对数据量较大的表重复值非常少的列进行 group by 操作，结果集过大容易 OOM

单条语句表数量建议限制 3 个或 3 个以内

d.【推荐】UPDATE 语句规范

禁止单条 SQL 语句同时更新多个表

UPDATE 应仅针对有修改的字段进行

UPDATE 操作时，应该做到仅对有修改的字段 UPDATE，避免对表中无修改的字段也一并更新，后者的做法将耗费更多的 CPU、内存

e.【推荐】GROUP BY 使用规范

  • 建议为 GROUP BY 字段建索引
  • GROUP BY 的列最好是整型，或者非常短的字符串，例如 VARCHAR(10)
  • GROUP BY 的列不要计算， 例如禁止这样写 GROUP BY MOD(ID,5)
  • HAVING 子句能写在 WHERE 条件中时，最好不要写到 GROUP BY 里面

例如：

select max(a),b from t group by b having max(a) > 1000

可以在 WHERE 条件中添加 a> 1000

select max(a),b from t where a>1000 group by b

f.【推荐】ORDER BY 注意事项

  • 建议为 ORDER BY 字段建索引
  • ORDER BY 列不得做计算，不得使用 ORDER BY RAND()
  • 明确的把要排序的字段添加到 order by 子句保证按需排序
  • 在 group_concat()中添加 order by 保证结果集稳定
  • ORDER BY 各列的升降顺序与索引创建时一致

例如：ORDER BY F1 ASC, F2 DESC，则需创建索引：(F1 ASC, F2 DESC)

g.【推荐】避免使用标量子查询

  • 标量子查询，即 select 的字段列表中存在的一个子查询，且该子查询对于主查询一行记录只能返回一条记录，过于复杂或嵌套层次过多的标量子查询，可能会影响查询的执行性能，因此在设计查询时，应考虑简化查询结构或使用其他查询方法来达到相同的目的
  • 子查询数量限定 3 个或 3 个以内

h.【推荐】建议SQL_MODE不要去掉 ONLY_FULL_GROUP_BY

SQL_MODE 默认值如下ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

去掉 ONLY_FULL_GROUP_BY ，会导致查询结果不稳定

i.【推荐】避免隐式转换

  • WHERE 条件中不在索引列上进行数学运算或函数运算，包括数据类型转换函数

正例：where gmt_create = str_to_date('2009­01­01 00:00:00'，'%Y­%m­%d %H:%i:s')

反例：where date_format(gmt_create，'%Y­%m­%d %H:%i:%s') = '2009­01­01 00:00:0'

  • 确保 SQL 中 join 列数据类型一致
  • 确保 where 条件中传入值的类型与字段定义类型保持一致，否则会导致索引失效

例如：字段 city_id varchar(20) 建立了索引,而查询条件写成 where city_id=123456，则会发生隐式转换

j.【推荐】其他规范

  • 建议用 in() /union 替换 or，并注意 in 的个数小于 300
  • 不建议使用负向查询，负向查询无法使用索引，如 not in、 not like
  • 不使用%前导的查询，如 like "%abc"
  • 必须在 insert 语句中指定列名，减少表结构变更带来的影响

正例：insert into T （col1,col2 ...） values()

反例：insert into T values()

  • 自增列禁止人为赋值
  • 避免使用 right/left join，尽量使用内连接
  • 日期运算应使用 date_add()和 date_sub()函数，不能直接对日期进行加、减操作



四 、常见问题处理

  1. TiDB 中的各种超时

  • GC 超时

TiDB 的事务的实现采用了 MVCC（多版本并发控制）机制，当新写入的数据覆盖旧的数据时，旧的数据不会被替换掉，而是与新写入的数据同时保留，并以时间戳来区分版本。TiDB 通过定期 GC 的机制来清理不再需要的旧数据。

默认配置下 TiDB 可以保障每个 MVCC 版本（一致性快照）保存 10 分钟，读取时间超过 10 分钟的事务，会收到报错 GC life time is shorter than transaction duration

当用户确信自己需要更长的读取时间时，比如在使用了 Mydumper 做全量备份的场景中（Mydumper 备份的是一致性的快照），可以通过调整 TiDB 中 mysql.tidb 表中的 tikv_gc_life_time 的值来调大 MVCC 版本保留时间，需要注意的是 tikv_gc_life_time 的配置是立刻影响全局的，调大它会为当前所有存在的快照增加生命时长，调小它会立即缩短所有快照的生命时长。过多的 MVCC 版本会拖慢 TiKV 的处理效率，在使用 Mydumper 做完全量备份后需要及时把 tikv_gc_life_time 调整回之前的设置。

更多关于 GC 的信息，请参考官网文档。

  • 事务持锁超时

含 DML 语句的事务，除了受 tikv_gc_life_time 限制之外，还受到另外一个参数 max-txn-ttl 的影响，单个事务持锁的最长时间默认是 1 小时，超过该时间，该事务的锁可能会被其他事务清除，导致该事务无法成功提交，超过此时间的事务只能回滚。

  • 超时参数

wait_timeout 和 max_execution_time,这两个参数分别控制与 Java 应用连接的空闲超时时间和连接中 SQL 执行的超时时间，即控制 TiDB 与 Java 应用的连接最长闲多久和最长忙多久。这两个参数的默认值都是 0，即默认允许连接无限闲置以及无限忙碌（一个 SQL 语句执行无限长的时间）。

max_execution_time 目前对所有类型的 statement 生效，并非只对 SELECT 语句生效。其单位为 ms，但实际精度在 100ms 级别，而非更准确的毫秒级别。

但在实际生产环境中，空闲连接和一直无限执行的 SQL 对数据库和应用都有不好的影响。你可以通过在应用的连接字符串中配置这两个参数来避免空闲连接和执行时间过长的 SQL 语句。

例如，设置sessionVariables=wait_timeout=3600（1 小时）。

  1. 隐式转换

当 SQL 中谓词两侧的数据类型不一致时，TiDB 将隐式的将一侧或两侧的数据类型进行转换，将其变为兼容的数据类型，以进行谓词运算。

TiDB 中隐式类型转换规则如下：

  • 如果一个或两个参数都是 NULL，比较的结果是 NULL（NULL 安全的 <=> 相等比较运算符除外，对于 NULL <=> NULL，结果为 true，不需要转换）
  • 如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较
  • 如果两个参数都是整数，则将它们作为整数进行比较
  • 如果不与数字进行比较，则将十六进制值视为二进制字符串
  • 如果其中一个参数是十进制值，则比较取决于另一个参数。 如果另一个参数是十进制或整数值，则将参数与十进制值进行比较，如果另一个参数是浮点值，则将参数与浮点值进行比较
  • 如果其中一个参数是 TIMESTAMP 或 DATETIME 列，另一个参数是常量，则在执行比较之前将常量转换为时间戳
  • 在所有其他情况下，参数都是作为浮点数（double 类型）比较的

隐式类型转换增强了人机交互的易用性，但在应用代码中，我们应尽量避免隐式类型转换出现，这是由于隐式类型转换会导致：索引失效、精度丢失。

  • 索引失效

如下案例，account_id 为主键，其数据类型为 varchar。通过执行计划可见，该 SQL 发生了隐式类型转换，无法使用索引。

desc select * from account where account_id=6010000000009801;
 +-------------------------+----------------+-----------+---------------+------------------------------------------------------------+
 | id | estRows | task | access object | operator info |
 +-------------------------+----------------+-----------+---------------+------------------------------------------------------------+
 | TableReader_7 | 8000628000.00 | root | | data:Selection_6 |
 | └─Selection_6 | 8000628000.00 | cop[tikv] | | eq(cast(findpt.account.account_id), 6.010000000009801e+15) |
 | └─TableFullScan_5 | 10000785000.00 | cop[tikv] | table:account | keep order:false |
 +-------------------------+----------------+-----------+---------------+------------------------------------------------------------+
 3 rows in set (0.00 sec)
  • 精度丢失

如下案例，字段 a 的数据类型为 decimal(32,0)，从执行计划可以得知，出现了隐式类型转换，decimal 字段和字符串常值都被转换为 double 类型，而 double 类型的精度没有 decimal 高，出现了精度丢失，在这个 case 中，造成了筛选出范围之外的结果集的错误。

desc select * from t1 where a between '12123123' and '1111222211111111200000';
 +-------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------+
 | id | estRows | task | access object | operator info |
 +-------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------+
 | TableReader_7 | 0.80 | root | | data:Selection_6 |
 | └─Selection_6 | 0.80 | cop[tikv] | | ge(cast(findpt.t1.a), 1.2123123e+07), le(cast(findpt.t1.a), 1.1112222111111112e+21) |
 | └─TableFullScan_5 | 1.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
 +-------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------+
 3 rows in set (0.00 sec)
 MySQL [findpt]> select * from t1 where a between '12123123' and '1111222211111111200000';
 +------------------------+
 | a |
 +------------------------+
 | 1111222211111111222211 |
 +------------------------+
 1 row in set (0.01 sec)

3. ## 结果集不稳定

  • group by

出于便捷的考量，MySQL “扩展” 了 group by 语法，使 select 子句可以引用未在 group by 子句中声明的非聚集字段，也就是 non-full group by 语法，在其他数据库中，这被认为是一种语法错误，因为这会导致结果集不稳定。

在下例的 3 条 SQL 语句中，第一条 SQL 使用了 full group by 语法，所有在 select 子句中引用的字段，都在 group by 子句中有所声明，它的结果集是稳定的，可以看到 class 与 stuname 的全部组合共有三种；第二条与第三条是同一个 SQL，但它在两次执行时得到了不同的结果，这条 SQL 的 group by 子句中仅声明了一个 class 字段，因此结果集只会针对 class 进行聚集，class 的唯一值有两个，也就是说结果集中只会包含两行数据，而 class 与 stuname 的全部组合共有三种，班级 2018_CS_03 有两位同学，每次执行时返回哪位同学是没有语义上的限制的，都是符合语义的结果。

mysql> select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class, a.stuname order by a.class, a.stuname;
 +------------+--------------+------------------+
 | class | stuname | max(b.courscore) |
 +------------+--------------+------------------+
 | 2018_CS_01 | MonkeyDLuffy | 95.5 |
 | 2018_CS_03 | PatrickStar | 99.0 |
 | 2018_CS_03 | SpongeBob | 95.0 |
 +------------+--------------+------------------+
 3 rows in set (0.00 sec)
 mysql> select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class order by a.class, a.stuname;
 +------------+--------------+------------------+
 | class | stuname | max(b.courscore) |
 +------------+--------------+------------------+
 | 2018_CS_01 | MonkeyDLuffy | 95.5 |
 | 2018_CS_03 | SpongeBob | 99.0 |
 +------------+--------------+------------------+
 2 rows in set (0.01 sec)
 mysql> select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class order by a.class, a.stuname;
 +------------+--------------+------------------+
 | class | stuname | max(b.courscore) |
 +------------+--------------+------------------+
 | 2018_CS_01 | MonkeyDLuffy | 95.5 |
 | 2018_CS_03 | PatrickStar | 99.0 |
 +------------+--------------+------------------+
 2 rows in set (0.01 sec)

因此，想保障 group by 语句结果集的稳定，请使用 full group by 语法。

MySQL 提供了一个 SQL_MODE 开关 only_full_group_by 来控制是否进行 full group by 语法的检查，TiDB 也兼容了这个 SQL_MODE 开关：

mysql> select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class order by a.class, a.stuname;
 +------------+--------------+------------------+
 | class | stuname | max(b.courscore) |
 +------------+--------------+------------------+
 | 2018_CS_01 | MonkeyDLuffy | 95.5 |
 | 2018_CS_03 | PatrickStar | 99.0 |
 +------------+--------------+------------------+
 2 rows in set (0.01 sec)
 mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
 Query OK, 0 rows affected (0.01 sec)
 mysql> select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class order by a.class, a.stuname;
 ERROR 1055 (42000): Expression #2 of ORDER BY is not in GROUP BY clause and contains nonaggregated column '' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
  • order by

在 SQL 的语义中，只有使用了 order by 语法才会保障结果集的顺序输出。而单机数据库由于数据都存储在一台服务器上，在不进行数据重组时，多次执行的结果往往是稳定的，有些数据库（尤其是 MySQL InnoDB 存储引擎）还会按照主键或索引的顺序进行结果集的输出。

TiDB 是分布式数据库，数据被存储在多台服务器上，另外 TiDB 层不缓存数据页，因此不含 order by 的 SQL 语句的结果集展现顺序容易被感知到不稳定。想要按顺序输出的结果集，需明确的把要排序的字段添加到 order by 子句中，这符合 SQL 的语义。

在下面的案例中，用户只在 order by 子句中添加了一个字段，TiDB 只会按照这一个字段进行排序。

mysql> select a.class, a.stuname, b.course, b.courscore from stu_info a join stu_score b on a.stuno=b.stuno order by a.class;
 +------------+--------------+-------------------------+-----------+
 | class | stuname | course | courscore |
 +------------+--------------+-------------------------+-----------+
 | 2018_CS_01 | MonkeyDLuffy | PrinciplesofDatabase | 60.5 |
 | 2018_CS_01 | MonkeyDLuffy | English | 43.0 |
 | 2018_CS_01 | MonkeyDLuffy | OpSwimming | 67.0 |
 | 2018_CS_01 | MonkeyDLuffy | OpFencing | 76.0 |
 | 2018_CS_01 | MonkeyDLuffy | FundamentalsofCompiling | 88.0 |
 | 2018_CS_01 | MonkeyDLuffy | OperatingSystem | 90.5 |
 | 2018_CS_01 | MonkeyDLuffy | PrincipleofStatistics | 69.0 |
 | 2018_CS_01 | MonkeyDLuffy | ProbabilityTheory | 76.0 |
 | 2018_CS_01 | MonkeyDLuffy | Physics | 63.5 |
 | 2018_CS_01 | MonkeyDLuffy | AdvancedMathematics | 95.5 |
 | 2018_CS_01 | MonkeyDLuffy | LinearAlgebra | 92.5 |
 | 2018_CS_01 | MonkeyDLuffy | DiscreteMathematics | 89.0 |
 | 2018_CS_03 | SpongeBob | PrinciplesofDatabase | 88.0 |
 | 2018_CS_03 | SpongeBob | English | 79.0 |
 | 2018_CS_03 | SpongeBob | OpBasketball | 92.0 |
 | 2018_CS_03 | SpongeBob | OpTennis | 94.0 |
 | 2018_CS_03 | PatrickStar | LinearAlgebra | 6.5 |
 | 2018_CS_03 | PatrickStar | AdvancedMathematics | 5.0 |
 | 2018_CS_03 | SpongeBob | DiscreteMathematics | 72.0 |
 | 2018_CS_03 | PatrickStar | ProbabilityTheory | 12.0 |
 | 2018_CS_03 | PatrickStar | PrincipleofStatistics | 20.0 |
 | 2018_CS_03 | PatrickStar | OperatingSystem | 36.0 |
 | 2018_CS_03 | PatrickStar | FundamentalsofCompiling | 2.0 |
 | 2018_CS_03 | PatrickStar | DiscreteMathematics | 14.0 |
 | 2018_CS_03 | PatrickStar | PrinciplesofDatabase | 9.0 |
 | 2018_CS_03 | PatrickStar | English | 60.0 |
 | 2018_CS_03 | PatrickStar | OpTableTennis | 12.0 |
 | 2018_CS_03 | PatrickStar | OpPiano | 99.0 |
 | 2018_CS_03 | SpongeBob | FundamentalsofCompiling | 43.0 |
 | 2018_CS_03 | SpongeBob | OperatingSystem | 95.0 |
 | 2018_CS_03 | SpongeBob | PrincipleofStatistics | 90.0 |
 | 2018_CS_03 | SpongeBob | ProbabilityTheory | 87.0 |
 | 2018_CS_03 | SpongeBob | Physics | 65.0 |
 | 2018_CS_03 | SpongeBob | AdvancedMathematics | 55.0 |
 | 2018_CS_03 | SpongeBob | LinearAlgebra | 60.5 |
 | 2018_CS_03 | PatrickStar | Physics | 6.0 |
 +------------+--------------+-------------------------+-----------+
 36 rows in set (0.01 sec)
  • group_concat()

没有使用 order by 导致结果集不稳定，是因为 TiDB 是并行地从存储层读取数据，所以 group_concat() 不加 order by 的情况下得到的结果集展现顺序容易被感知到不稳定。group_concat() 要获取到按顺序输出的结果集，需要把用于排序的字段添加到 order by 子句中，这样才符合 SQL 的语义。

在下面的案例中，使用 group_concat() 不加 order by 的情况下拼接 customer_id 结果集不稳定：

  1. 不加 order by

第一次查询：

mysql> select GROUP_CONCAT( customer_id SEPARATOR ',' ) FROM customer where customer_id like '200002%';
 +-------------------------------------------------------------------------+
 | GROUP_CONCAT(customer_id SEPARATOR ',') |
 +-------------------------------------------------------------------------+
 | 20000200992,20000200993,20000200994,20000200995,20000200996,20000200... |
 +-------------------------------------------------------------------------+

第二次查询：

mysql> select GROUP_CONCAT( customer_id SEPARATOR ',' ) FROM customer where customer_id like '200002%';
 +-------------------------------------------------------------------------+
 | GROUP_CONCAT(customer_id SEPARATOR ',') |
 +-------------------------------------------------------------------------+
 | 20000203040,20000203041,20000203042,20000203043,20000203044,20000203... |
 +-------------------------------------------------------------------------+

2. 加 order by

第一次查询：

mysql> select GROUP_CONCAT( customer_id order by customer_id SEPARATOR ',' ) FROM customer where customer_id like '200002%';
 +-------------------------------------------------------------------------+
 | GROUP_CONCAT(customer_id SEPARATOR ',') |
 +-------------------------------------------------------------------------+
 | 20000200000,20000200001,20000200002,20000200003,20000200004,20000200... |
 +-------------------------------------------------------------------------+

第二次查询：

mysql> select GROUP_CONCAT( customer_id order by customer_id SEPARATOR ',' ) FROM customer where customer_id like '200002%';
 +-------------------------------------------------------------------------+
 | GROUP_CONCAT(customer_id SEPARATOR ',') |
 +-------------------------------------------------------------------------+
 | 20000200000,20000200001,20000200002,20000200003,20000200004,20000200... |
 +-------------------------------------------------------------------------+

4. ## 索引使用

  • TiDB 中的索引

索引也是数据，也要占用存储空间。和表中的数据一样，TiDB 中表的索引在存储引擎中也被作为 kv 来存储，一行索引是一个 kv 对。例如一张有 10 个索引的表，每插入一行数据的时候，会写入 11 个 kv 对。

TiDB 支持主键索引，唯一索引，也支持二级索引，构成以上索引的可以是单一列，也可以是多个列（复合索引）。

TiDB 目前还不支持反转索引，索引倒序，全文索引，分区表的全局索引。

TiDB 中在查询的谓词是 =，>，<，>=，<=，like ‘…%’，not like ‘…%’，in，not in，<>，!=，is null，<=>，is not null，between…and … 时能够使用索引，使用与否由优化器来决策。

TiDB 中在查询的谓词是 like ‘%…’，like ‘%…%’，not like ‘%…’，not like ‘%…%’ 时，都无法使用索引。

  • 复合索引的设计

与其他的 RDBMS 一样，TiDB 需要通过索引才能保障数据读取的性能，而 TiDB 往往被用于承载单机 RDBMS 难以承载的数据量，因此索引尤其是符合索引的设计尤为关键。

TiDB 中的复合索引形如 key tablekeyname (a,b,c) ，与其他数据库一样，设计复合索引的一般原则是尽可能的把使用频率比较高的字段放在前面。在当前版本（v5.0 及以下的全部版本）使用中需要特别注意，复合索引中前一列的范围查询会中止后续索引列的使用，可以通过下面的案例来理解这个特性。

在如下的查询中：select a,b,c from tablename where a’’ and b’’ and c’’;

  1. 如果 a 条件的谓词（语句中的 predicate）是 = 或 in，那么在 b 的查询条件上就可以利用到组合索引 (a,b,c) 。例：select a,b,c from tablename where a=1 and b<5 and c=’abc’;
  2. 同样的，如果 a 条件和 b 条件的谓词都是 = 或 in，那么在 c 上的查询就可以利用到组合索引 (a,b,c) 。例：select a,b,c from tablename where a in (1,2,3) and b=5 and c=’abc’;
  3. 如果 a 条件的谓词不是 = 也不是 in，那么 b 上的查询就无法利用到组合索引 (a,b,c) 。此时 b 条件将在 a 条件筛选后的数据中进行无索引的数据扫描。例：select a,b,c from tablename where a>1 and b<5 and c=’abc’;

这是由于在 TiDB 中，复合索引中排在前面的列如果被用于范围查询，那么后续列的查询就会在前一列筛选后的数据范围中进行非索引的扫描。综上，在 TiDB 中进行复合索引设计时，需要尽可能的将使用频率高的，经常被点查使用的列排在前面，将经常进行范围查询的列排在后面。

另外形如 select c, count(*) from tabname where a=1 and b=2 group by c order by c; 的查询可以利用到索引 (a,b,c)，同样遵循上面的原则。

  1. 自增列使用

  • 原理

TiDB 的自增 ID (auto_increment) 在 TiDB 只保证唯一，并不能保证严格自增，由于缓存的存在，有出现自增值回退的可能。TiDB 目前采用批量分配的方式，所以如果在多台 TiDB 上同时插入数据，分配的自增 ID 会不连续。

当多个线程并发往不同的 tidb-server 插入数据的时候，有可能会出现后插入的数据自增 ID 小的情况。此外，TiDB 允许给数值类型的列指定 auto_increment，且一个表只允许一个属性为 auto_increment 的列。

v6.4 版本开始支持全局自增，能够保证 id 严格自增，需要将表 AUTO_ID_CACHE 设置为 1 。

  • 最佳实践

设置自增 ID 的目的，一般是将它作为表内数据的唯一性约束，因此被设计为主键或唯一索引，此类列属性应带有 not null。

自增 ID 列的类型必须为整型，在几种整型类型中，我们建议使用 bigint，这是由于即使在单机数据库中也屡见 int 类型的自增 ID 被耗光的情况，而 TiDB 被用于处理比单机数据大得多的数据量，此外 TiDB 采用多线程的方式分配自增 ID，因此 int 类型无法满足需求。

另外自增 ID 一般不需要存储负值，为列增加 unsigned 属性可以扩充一倍的 id 存储容量。int 无符号的范围是 0 到 4294967295，bigint 无符号的范围是 0 到 18446744073709551615

综上，自增 ID 设计的最佳实践如下：

auto_inc_id bigint unsigned not null unique key auto_increment comment '自增 ID'
  • 人为赋值的后果及挽救措施

在集群中有多个 tidb-server 时，人为向自增列写入值之后，可能会导致 TiDB 分配自增值冲突而报 “Duplicate entry” 错误。

假设有这样一个带有自增 ID 的表：

create table t(id int unique key auto_increment, c int);

TiDB 实现自增 ID 的原理是每个 tidb-server 实例缓存一段 ID 值用于分配（目前会缓存 30000 个 ID），用完这段值再去取下一段。假设集群中有两个 tidb-server 实例 A 和 B（A 缓存 [1,30000] 的自增 ID，B 缓存 [30001,60000] 的自增 ID），依次执行如下操作：

客户端向 B 插入一条将 id 设置为 1 的语句 insert into t values (1, 1)，并执行成功。

客户端向 A 发送 Insert 语句 insert into t values (1)，这条语句中没有指定 id 的值，所以会由 A 分配，当前 A 缓存了 [1, 30000] 这段 ID，所以会分配 1 为自增 ID 的值，并把本地计数器加 1。而此时数据库中已经存在 id 为 1 的数据，最终返回 Duplicated Entry 错误。

处理该问题只需要调大表上的 AUTO_INCREMENT 属性值即可让所有 tidb-server 重新获取一段自增 ID：

  1. 确认表上自增值的最大值：show create table t;

  2. 修改表上的自增值最大值到一个更大的值：alter table t AUTO_INCREMENT=120000;

  3. 热点问题

  • 产生写入热点的原因

大部分单机 RDBMS 采用 B+ tree 数据结构，主键往往是用于组织数据的关键索引（此时表被称作索引组织表），同一数据页内的记录按主键顺序存放。因此单机 RDBMS 产品一般推荐写入连续的序列号，这样每次写入新的记录，都会顺序添加到当前 B+ tree 索引节点的后续位置，当前的数据页写满时，会自动开始新一页的写入。相反，过于随机的主键值，会导致新记录被写入到数据页的某个中间位置，造成数据的移动而带来了额外的开销。

虽然 TiDB 具有不同于单机 RDBMS 的数据结构，但顺序的主键值写入，在 TiDB 上也会产生类似的效果：TiKV 上一个的 region 被写满，进而分裂出一个新的 region，后续的写入转由新的 reigon 来承载。但甲之蜜糖，乙之砒霜，单机 RDBMS 的最佳实践放到 TiDB 上，会使写入压力总是集中在一个 region 上，这样就构成了持续的写入热点，无法发挥出 TiDB 这种分布式数据库的并行写入能力，降低了业务写入瓶颈，造成了系统资源的浪费。

TiDB 发生写入热点的原因主要有以下几种：

  1. 小表，整个表只有一个 region，高并发的请求会造成读写热点。

  2. 大量写入时 Key 值的离散程度不足以跳过一个 region 大小（96MB）而引起写入热点。

  • 热点的观测和定位

TiDB 从 v4.0 版本开始提供便于迅速识别集群负载的 Dashboard 流量可视化页面（Key Visualizer），下图展示了写入热点的显示效果，中间一条明亮的曲线即标志着存在一张连续写入 Key 值的表。而右上侧的一组线条则显示出一个写入压力较为均匀的负载。Key Visualizer 的具体使用方法请参考官方文档

写入热点在 Dashboard Key Visualizer 中的显示效果

  • 写入热点的缓解
  1. 配置 SHARD_ROW_ID_BITS 参数打散写入热点

对于非索引组织表（默认行为），TiDB 会使用一个隐式的自增 rowid，大量 INSERT 时会把数据集中写入单个 region，造成写入热点。 通过设置 SHARD_ROW_ID_BITS 可以把 rowid 打散写入多个不同的 region，缓解写入热点问题。 但是设置的过大会造成 RPC 请求数放大，增加 CPU 和网络开销。

用 SHARD_ROW_ID_BITS 来设置隐藏列 _tidb_rowid 分片数量的 bit 位数：

默认值为 0，即 2^0 = 1 个分片

SHARD_ROW_ID_BITS = 0 就是默认值 1 个分片

SHARD_ROW_ID_BITS = 4 代表 16 个分片

SHARD_ROW_ID_BITS = 6 表示 64 个分片

CREATE TABLE 语句示例： CREATE TABLE t (c int) SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS=4;

ALTER TABLE 语句示例： ALTER TABLE t SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS=4;

  1. 避免连续自增的主键设计

对索引组织表来说，它无法利用到 SHARD_ROW_ID_BITS 的优化，可以通过修改序列号的方式来构成多个写入分片来分散写入热点。

*AUTO_INCREMENT 主键可通过 AUTO_RANDOM 机制来打散写入热点，详见 官网文档。

如下表所示，左侧为原本要写入表中的序列号值，右侧为转换后的序列号。经过转换后的序列号不再连续且离散程度足以跳过一个 region 大小，以此方式来分散写入热点：

原始序列号转换后的序列号
561632371724517376566163237172451737
561632371728711680506163237172871168
561632371728711681516163237172871168
561632371728711682526163237172871168
561632371732905984546163237173290598
561632371732905985556163237173290598
561632371732905986566163237173290598
561632371732905987576163237173290598
561632371732905988586163237173290598
561632371737100288586163237173710028

表 1. 将连续的写入转换为 10 个分片写入的案例

  1. 分区表

分区表（partitioned table）可以将一张表的数据分散到多张物理表中，而多张物理表的数据是分散在多个 region 中的，因此通过合理的涉及分区规则，可以进一步避免写入热点问题。

  • 读取热点

即使使用了上述的 SHARD_ROW_ID_BITS 方式打散数据在 region 上的分布，有些小表的热点问题也可能依然存在，典型场景是被频繁读取的参数配置表，高并发的读取集中在一个或几个 region 上，有可能成为整笔交易的性能瓶颈。

推荐做法是在应用侧做缓存处理，使用如 redis 这样的产品进行缓存。

tidb coprocessor cache 功能也能有所缓解。

  1. 分页优化

  • 基本原则

分页查询语句全部都需要带有排序条件，除非业务方明确要求不要使用任何排序来随机展示数据。

  • 详细说明

    • 常规分页语句写法(start:起始记录数,page_offset:每页记录数)：
    •       select * from table_a t order by gmt_modified desc limit start，page_offset;
    • 多表 Join 的分页语句，如果过滤条件在单个表上，内查询语句必须走覆盖索引，先分页，再 Join：
    •   错误的写法：
    •       select a.column_a，a.column_b .. . b.column_a，b.column_b .. .
       from table_t a，table_b b
       where a.xxx.. .
       and a.column_c = b.column_d
       order by a.yyy
       limit start，page_offset

正确的写法：

select a.column_a，a.column_b .. . b.column_a，b.column_b .. .
 from
 (select t.column_a，t.column_b .. .
 from table_t t
 where t.xxx.. .
 order by t.yyy
 limit start，page_offerset) a,
 table_b b
 where a.column_c = b.column_d;
 
 select * from t limit 10000,10;
 
 select * from t order by c limit 10000,10;
  • 单字段主键表的分页批处理

常规的分页更新 SQL 一般使用主键或者唯一索引进行排序，再配合 MySQL limit 语法中非常好用的 offset 功能按固定行数拆分页面，然后把页面包装进独立的事务中，从而实现灵活的分页更新。但是，劣势也很明显：由于需要对主键或者唯一索引进行排序，越靠后的页面参与排序的行数就会越多，尤其当批量处理涉及的数据体量较大时，可能会占用过多计算资源。

首先将数据按照主键排序，然后调用窗口函数 row_number() 为每一行数据生成行号，接着调用聚合函数按照设置好的页面大小对行号进行分组，最终计算出每页的最小值和最大值。

MySQL [demo]> select min(t.serialno) as start_key, max(t.serialno) as end_key, count(*) as page_size from ( select *, row_number () over (order by serialno) as row_num from tmp_loan ) t group by floor((t.row_num - 1) / 50000) order by start_key;
 +-----------+-----------+-----------+
 | start_key | end_key | page_size |
 +-----------+-----------+-----------+
 | 200000000 | 200050001 | 50000 |
 | 200050002 | 200100007 | 50000 |
 | 200100008 | 200150008 | 50000 |
 | 200150009 | 200200013 | 50000 |
 | 200200014 | 200250017 | 50000 |
 | ........ |.......... | ........ |
 | 201900019 | 201950018 | 50000 |
 | 201950019 | 201999003 | 48985 |
 +-----------+-----------+-----------+
 40 rows in set (1.51 sec)

接下来，只需要使用 serialno between start_key and end_key 查询每个分片的数据即可。如果修改数据时，也可以借助上面计算好的分片信息，实现高效数据更新。改进方案由于规避了频繁的数据排序操作造成的性能损耗，显著改善了批量处理的效率。

  • 复合主键表的分页批处理

对于非索引组织表，可以使用隐藏字段 _tidb_rowid 做分页使用。

SELECT
    floor((t.row_num - 1) / 1000) + 1 AS page_num,
    min(t._tidb_rowid) AS start_key,
    max(t._tidb_rowid) AS end_key,
    count(*) AS page_size
 FROM (
    SELECT _tidb_rowid, row_number() OVER (ORDER BY _tidb_rowid) AS row_num
    FROM users
 ) t
 GROUP BY page_num
 ORDER BY page_num;

 +----------+-----------+---------+-----------+
 | page_num | start_key | end_key | page_size |
 +----------+-----------+---------+-----------+
 |        1 |         1 |    1000 |      1000 |
 |        2 |      1001 |    2000 |      1000 |
 |        3 |      2001 |    3000 |      1000 |
 |        4 |      3001 |    4000 |      1000 |
 |        5 |      4001 |    5000 |      1000 |
 |        6 |      5001 |    6000 |      1000 |
 |        7 |      6001 |    7000 |      1000 |
 |        8 |      7001 |    8000 |      1000 |
 |        9 |      8001 |    9000 |      1000 |
 |       10 |      9001 |    9990 |       990 |
 +----------+-----------+---------+-----------+
 10 rows in set (0.00 sec)

对于索引组织表，可以用如下方式进行分页计算：

制作元信息表，因为组成 key 的 book_id 列和 user_id 列都是 bigint 类型，转换为字符串是并不是等宽的，因此需要根据 bigint 类型的最大位数 19，使用 LPAD 函数在长度不够时用 0 补齐。如果列是字符类型，可以用 - 号补齐。

SELECT
    floor((t1.row_num - 1) / 10000) + 1 AS page_num,
    min(mvalue) AS start_key,
    max(mvalue) AS end_key,
    count(*) AS page_size
 FROM (
    SELECT
        concat('(', LPAD(book_id, 19, 0), ',', LPAD(user_id, 19, 0), ')') AS mvalue,
        row_number() OVER (ORDER BY book_id, user_id) AS row_num
    FROM ratings
 ) t1
 GROUP BY page_num
 ORDER BY page_num;

 +----------+-------------------------------------------+-------------------------------------------+-----------+
 | page_num | start_key                                 | end_key                                   | page_size |
 +----------+-------------------------------------------+-------------------------------------------+-----------+
 |        1 | (0000000000000268996,0000000000092104804) | (0000000000140982742,0000000000374645100) |     10000 |
 |        2 | (0000000000140982742,0000000000456757551) | (0000000000287195082,0000000004053200550) |     10000 |
 |        3 | (0000000000287196791,0000000000191962769) | (0000000000434010216,0000000000237646714) |     10000 |
 |        4 | (0000000000434010216,0000000000375066168) | (0000000000578893327,0000000002167504460) |     10000 |
 |        5 | (0000000000578893327,0000000002457322286) | (0000000000718287668,0000000001502744628) |     10000 |
 ...
 |       29 | (0000000004002523918,0000000000902930986) | (0000000004147203315,0000000004090920746) |     10000 |
 |       30 | (0000000004147421329,0000000000319181561) | (0000000004294004213,0000000003586311166) |      9972 |
 +----------+-------------------------------------------+-------------------------------------------+-----------+
 30 rows in set (0.28 sec)

批量处理 SQL 案例：

SELECT * FROM ratings
 WHERE
    (book_id > 268996 AND book_id < 140982742)
    OR 
    (book_id = 268996 AND user_id >= 92104804)
    OR 
    (book_id = 140982742 AND user_id <= 374645100)
 ORDER BY book_id, user_id;

8. ## 唯一序列号生成

  • 自增列

自增（auto_increment）是大多数兼容 MySQL 协议的 RDBMS 上列的一种属性，通过配置该属性来使数据库为该列的值自动赋值，用户不需要为该列赋值，该列的值随着表内记录增加会自动增长，并确保唯一性。在大多数场景中，自增列被作为无业务含义的代理主键使用。

自增列的局限性在于：自增列只能采用整型字段，所赋的值也只能为整型。假设业务所需要的序列号由字母、数字及其他字符拼接而成，用户是难以通过自增列来获取序列号中所需的数字自增值的。

  • 序列（Sequence）

序列是一种数据库对象，应用程序通过调用某个序列可以产生递增的序列值，应用程序可以灵活的使用这个序列值为一张表或多张表赋值，也可以使用序列值进行更复杂的加工，来实现文本和数字的组合，来赋予代理键以一定的跟踪和分类的意义。

TiDB 从 v4.0 版本开始提供序列功能，详情请参考官方文档。

  • 类 Snowflake 方案

Snowflake 是 Twitter 提出的分布式 ID 生成方案。目前有多种实现，较流行的是百度的 uid-generator 和美团的 leaf。下面以 uid-generator 为例展开说明。

uid-generator 生成的 64 位 ID 结构如下：

  • sign：长度固定为 1 位。固定为 0，表示生成的 ID 始终为正数
  • delta seconds：默认 28 位。当前时间，表示为相对于某个预设时间基点 （默认 “2016-05-20”） 的增量值，单位为秒。28 位最多可支持约 8.7 年
  • worker node id：默认 22 位。表示机器 id，通常在应用程序进程启动时从一个集中式的 ID 生成器取得。常见的集中式 ID 生成器是数据库自增列或者 Zookeeper。默认分配策略为用后即弃，进程重启时会重新获取一个新的 worker node id，22 位最多可支持约 420 万次启动
  • sequence：默认 13 位。表示每秒的并发序列，13 位可支持每秒 8192 个并发

使用类 Snowflake 方案时需要注意几个问题：

  • delta seconds 完全本地生成，强依赖机器时钟。如果发生时钟回拨， 会导致发号重复或者服务会处于不可用状态。
  • 可根据数据预期寿命调整 delta seconds 位数， 一般在 28 位至 44 位之间。
  • delta seconds 时间基点不要使用默认值，应该尽量贴近当前时间。
  • worker node id 位数有限，对应数值不超过 500 万。 如果使用 TiDB 的自增列实现 worker node id，每次 TiDB 实例的重启都会让自增列返回值增加至少 3 万，这样最多 500 / 3 = 166 次实例重启后，自增列返回值就比 worker node id 可接受的最大值要大。这时就不能直接使用这个过大的值，需要清空自增列所在的表，把自增列值重置为零，也可以在 Snowflake 实现层解决这个问题。
  • 号段分配方案

号段分配方案可以理解为从数据库批量获取自增 ID。本方案需要一张序列号生成表，每行记录表示一个序列对象。表定义示例如下：

字段名字段类型字段说明
SEQ_NAMEvarchar(128)序列名称，用来区分不同业务
MAX_IDbigint(20)当前序列已被分配出去的最大值
STEPint(11)步长，表示每次分配的号段长度

应用程序每次按配置好的步长获取一段序列号，并同时更新数据库以持久化保存当前序列已被分配出去的最大值，然后在应用程序内存中即可完成序列号加工及分配动作。

待一段号码耗尽之后，应用程序才会去获取新的号段，这样就有效降低了数据库写入压力。实际使用过程中，还可以适度调节步长以控制数据库记录的更新频度。

最后，需要注意的是，上述两种方案生成的 ID 都不够随机，不适合直接作为 TiDB 表的主键。实际使用过程中可以对生成的 ID 进行位反转（bit-reverse）后得到一个较为随机的新 ID。

  1. 表类型选择

  • 聚簇表

    • 聚簇表通常有两种使用形式，主键为 auto random 或者业务主键。如果业务主键是连续递增，则可能遇到热点问题，如果业务主键为完全随机值，在大数据量的情况下会导致数据过于离散，影响性能

    • 聚簇表无法使用 shard rowid bit 进行打散，遇到热点问题时需要重建表

    • 聚簇表可以少维护一个索引，基于主键查询可以少一次回表，但通常不会基于非业务主键进行查询，需考虑是否需要这部分性能提升

    • 非聚簇表 rowid 自增的特点会导致在大量插入时会遇到热点问题，应同时设置 shard row id bit 避免

    • 使用聚簇表应明确聚簇表的限制以及收益，合理使用

  • 分区表

    • 分区表功能与运维特性在 6.1 之后逐渐 GA 与完善，动态裁剪特性对分区表使用帮助较大，6.5 版本特定场景推荐使用分区表

    • 动态裁剪功能保持版本默认值，6.3 版本之后默认开启

    • 使用分区表需满足主要的与表相关的 SQL 语句均会使用分区字段作为查询条件

    • 分区表使用典型场景

      • 数据生命周期管理，需要按时间范围删除数据

      • 需要扫描大段连续范围的数据，如果通过索引访问回表代价比较高，整个分区扫描效果更好

      • 查询场景中必然有某个固定等值条件，如某个月份、某个地区，分区表可以减少组合索引的使用，仅需要单列索引即可

      • 大量写入存在写入热点，同时是点查场景的，hash 分区表可以解决热点问题，也不会影响查询性能

  • 临时表

    • 临时表数据存储在 tidb 内存中，默认最大允许 64M ，需控制内存使用

    • 临时表无法收集统计信息，需考虑执行计划是否稳定

    • 临时表数据存储在 tidb 内存中，创建销毁数据性能较好

    • 临时表不会产生日志数据无需同步至下游

  1. 主键选择

  • 是否应该选择聚簇

    • 聚簇表下主键太随机会导致聚簇因子比较大，会造成回表 coptask 非常多，对于 tikv 的读请求放大很严重
    • 非聚簇表下 rowid 为隐式自增类型，在设置 shard row id bit 的情况下，也不会导致过份离散，此时主键值随机不影响

  • 业务唯一字段作为主键

    • 业务主键为递增不宜使用聚簇表，业务主键完全随机不宜使用聚簇表，如需使用聚簇表，业务主键应该具备分段递增的特性
    • 联合主键使用聚簇表应避免长度过长

  • 自增列作为主键

    • 如对自增列主键是否递增无明确要求，可以使用聚簇表加 auto random 特性

  • 用序列填充主键

    • 使用聚簇表时，用序列值填充主键应尽量使用分段递增的值避免热点

  1. 数据删除

删除表中全部的数据时，使用 TRUNCATE 或者 DROP 后重建方式，不要使用 DELETE。

DELETE，TRUNCATE 和 DROP 都不会立即释放空间，对于 TRUNCATE 和 DROP 操作，在达到 TiDB 的 GC (garbage collection) 时间后（默认 10 分钟），TiDB 的 GC 机制会删除数据并释放空间。对于 DELETE 操作 TiDB 的 GC 机制会删除数据，但不会释放空间，而是当后续数据写入 RocksDB 且进行 compact 时对空间重新利用。




五、监控告警

  1. 监控维度

IT运维团队会基于以下KPI对TiDB进行监控和告警：

  • 主机资源监控：CPU、内存、磁盘、网络

  • TiDB服务指标：实例存活情况、TiCDC延迟情况、集群Duration 99线

  • TiDB性能指标：QPS、TPS、慢查数量、CPU开销、内存开销、网络开销、连接数

  1. 监控告警架构

目前正在对接公司云监控体系，通过夜莺发送告警，并对核心指标提供监控大屏。

监控屏地址：Techops / TechOps TiDB DashBoard(ALL)

  1. 服务等级

IT运维团队对TiDB的服务支持由DBA团队提供，服务等级定义同线上业务应用分级。

原则上TiDB的服务等级等于或低于所属应用的分级。

附录

  1. SQL变更审核规则清单

sql变更的规则清单请见下表：

审核等级规则描述规则说明
notice检查绑定变量数量因为过度使用绑定变量会增加查询的复杂度，从而降低查询性能。过度使用绑定变量还会增加维护成本。最大绑定变量数量:100
warn表的初始AUTO_INCREMENT值不为0创建表时AUTO_INCREMENT设置为0则自增从1开始，避免数据空洞。另外在导出表结构DDL时，表结构内AUTO_INCREMENT通常为当前的自增值，通过该DDL进行建表操作会导致自增值从一个无意义数字开始。建议自增ID统一从1开始
warnBLOB 和 TEXT 类型的字段不可指定非 NULL 的默认值在SQL_MODE严格模式下BLOB 和 TEXT 类型无法设置默认值，如插入数据不指定值，字段会被设置为NULL
notice不建议使用 BLOB 或 TEXT 类型BLOB 或 TEXT 类型消耗大量的网络和IO带宽，同时在该表上的DML操作都会变得很慢
warnBLOB 和 TEXT 类型的字段不建议设置为 NOT NULLBLOB 和 TEXT 类型的字段无法指定默认值，如插入数据不指定字段默认为NULL，如果添加了 NOT NULL 限制，写入数据时又未对该字段指定值会导致写入失败
noticechar长度大于20时，必须使用varchar类型varchar是变长字段，存储空间小，可节省存储空间，同时相对较小的字段检索效率显然也要高些
notice表中包含有太多的列避免在OLTP系统上做宽表设计，后期对性能影响很大；具体规则阈值可根据业务需求调整， 最大列数: 64
warn主键中的列过多主建中的列过多，会导致二级索引占用更多的空间，同时增加索引维护的开销；具体规则阈值可根据业务需求调整，主键应当不超过多少列: 2
warntimestamp 类型的列必须添加默认值timestamp添加默认值，可避免出现全为0的日期格式与业务预期不符
error列建议添加注释列添加注释能够使列的意义更明确，方便日后的维护
warn除了自增列及大字段列之外，每个列都必须添加默认值列添加默认值，可避免列为NULL值时对查询的影响
error禁止使用自定义函数自定义函数，维护较差，且依赖性高会导致SQL无法跨库使用
error禁止使用存储过程存储过程在一定程度上会使程序难以调试和拓展，各种数据库的存储过程语法相差很大，给将来的数据库移植带来很大的困难，且会极大的增加出现BUG的概率
warn建表DDL必须包含创建时间字段且默认值为CURRENT_TIMESTAMP使用CREATE_TIME字段，有利于问题查找跟踪和检索数据，同时避免后期对数据生命周期管理不便 ，默认值为CURRENT_TIMESTAMP可保证时间的准确性
error禁止使用触发器触发器难以开发和维护，不能高效移植，且在复杂的逻辑以及高并发下，容易出现死锁影响业务
error禁止使用视图视图的查询性能较差，同时基表结构变更，需要对视图进行维护，如果视图可读性差且包含复杂的逻辑，都会增加维护的成本
notice精确浮点数建议使用DECIMAL对于浮点数运算，DECIMAL精确度较高
warn字段约束为not null时必须带默认值如存在not null且不带默认值的字段，insert时不包含该字段，会导致插入报错
error检测DDL语句中是否使用了中文全角引号建议开启此规则，可避免MySQL会将中文全角引号识别为命名的一部分，执行结果与业务预期不符
error禁止将blob类型的列加入索引blob类型属于大字段类型，作为索引会占用很大的存储空间
notice索引个数建议不超过阈值在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销，太多与不充分、不正确的索引对性能都毫无益处；具体规则阈值可以根据业务需求调整，最大索引个数：5
notice索引字段需要有非空约束索引字段上如果没有非空约束，则表记录与索引记录不会完全映射。
notice建议选择可选性超过阈值字段作为索引选择区分度高的字段作为索引，可快速定位数据；区分度太低，无法有效利用索引，甚至可能需要扫描大量数据页，拖慢SQL；具体规则阈值可以根据业务需求调整，默认值：70
notice普通索引必须使用固定前缀通过配置该规则可以规范指定业务的索引命名规则，具体命名规范可以自定义设置，默认提示值：idx_
notice检查DDL创建的新索引对应字段是否已存在过多索引在有单字段索引的情况下，过多的复合索引，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大；具体规则阈值可以根据业务需求调整，单字段的索引数最大值: 3
notice使用LIMIT分页时,避免使用LIMIT M,NLIMIT M,N当偏移量m过大的时候，查询效率会很低，因为MySQL是先查出m+n个数据，然后抛弃掉前m个数据；对于有大数据量的mysql表来说，使用LIMIT分页存在很严重的性能问题
notice数据库对象命名不建议大小写字母混合数据库对象命名规范，不推荐采用大小写混用的形式建议词语之间使用下划线连接，提高代码可读性
notice表名、列名、索引名的长度不能大于指定字节通过配置该规则可以规范指定业务的对象命名长度，具体长度可以自定义设置，默认最大长度：64。是MySQL规定标识符命名最大长度为64字节
error数据库对象命名只能使用英文、下划线或数字，首字母必须是英文通过配置该规则可以规范指定业务的数据对象命名规则
warn数据库对象命名禁止使用保留字通过配置该规则可以规范指定业务的数据对象命名规则，避免发生冲突，以及混淆
error表必须有主键主键使数据达到全局唯一，可提高数据检索效率
warn主键建议使用自增自增主键，数字型速度快，而且是增量增长，占用空间小，更快速的做数据插入操作，避免增加维护索引的开销
warn主键建议使用 bigint 无符号类型，即 bigint unsignedbigint unsigned拥有更大的取值范围，建议开启此规则，避免发生溢出
warn检查DDL是否创建冗余的索引MySQL需要单独维护重复的索引，冗余索引增加维护成本，并且优化器在优化查询时需要逐个进行代价计算，影响查询性能
notice必须使用指定数据库字符集通过该规则约束全局的数据库字符集，避免创建非预期的字符集，防止业务侧出现“乱码”等问题。建议项目内库表使用统一的字符集和字符集排序，部分连表查询的情况下字段的字符集或排序规则不一致可能会导致索引失效且不易发现；数据库字符集: utf8mb4
warn检查DDL操作的表是否超过指定数据量大表执行DDL，耗时较久且负载较高，长时间占用锁资源，会影响数据库性能；具体规则阈值可以根据业务需求调整， 表空间大小（MB）: 10240
error表建议添加注释表添加注释能够使表的意义更明确，方便日后的维护
warn新建表必须加入 if not exists，保证重复执行不报错新建表如果表已经存在，不添加if not exists create执行SQL会报错，建议开启此规则，避免SQL实际执行报错
noticeunique索引名必须使用 IDX_UK_表名_字段名通过配置该规则可以规范指定业务的unique索引命名规则
noticeunique索引必须使用固定前缀通过配置该规则可以规范指定业务的unique索引命名规则，具体命名规范可以自定义设置，默认提示值：uniq_
warn建表DDL必须包含更新时间字段且默认值为CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP使用更新时间字段，有利于问题查找跟踪和检索数据，同时避免后期对数据生命周期管理不便 ，默认值为UPDATE_TIME可保证时间的准确性
warnVARCHAR 定义长度过长MySQL建立索引时没有限制索引的大小，索引长度会默认采用的该字段的长度，VARCHAR 定义长度越长建立的索引存储大小越大；具体规则阈值可以根据业务需求调整，VARCHAR最大长度: 1024
warnalter表字段禁止使用first,afterfirst,after 的alter操作通过Copy Table的方式完成，对业务影响较大
warn禁止除索引外的drop操作DROP是DDL，数据变更不会写入日志，无法进行回滚；建议开启此规则，避免误删除操作
warn禁止使用外键外键在高并发场景下性能较差，容易造成死锁，同时不利于后期维护（拆分、迁移）
error禁止进行删除列的操作业务逻辑与删除列依赖未完全消除，列被删除后可能导致程序异常（无法正常读写）的情况；开启该规则，SQLE将提醒删除列为高危操作
error禁止进行删除外键的操作删除已有约束会影响已有业务逻辑；开启该规则，SQLE将提醒删除外键为高危操作
error禁止进行删除主键的操作删除已有约束会影响已有业务逻辑；开启该规则，SQLE将提醒删除主键为高危操作
warn修改表的默认字符集不会改表各个字段的字符集修改表的默认字符集，只会影响后续新增的字段，不会修表已有字段的字符集；如需修改整张表所有字段的字符集建议开启此规则
warn禁止使用rename或change对表名字段名进行修改rename/change 表名/列名会对线上业务不停机发布造成影响，如需这种操作应当DBA手工干预
warn建议列与表使用同一个字符集统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效
notice单条insert语句，建议批量插入不超过阈值避免大事务，以及降低发生回滚对业务的影响；具体规则阈值可以根据业务需求调整，最大插入行数：100
warn查询的扫描不建议超过指定行数查询的扫描行数多大，可能会导致优化器选择错误的索引甚至不走索引；具体规则阈值可以根据业务需求调整，最大扫描行数: 100000
warn该查询使用了文件排序大数据量的情况下，文件排序意味着SQL性能较低，会增加OS的开销，影响数据库性能
error检查是否存在索引跳跃扫描索引扫描是跳跃扫描，未遵循最左匹配原则
warn该查询使用了临时表大数据量的情况下，临时表意味着SQL性能较低，会增加OS的开销，影响数据库性能
warn检查是否存在全索引扫描在数据量大的情况下索引全扫描严重影响SQL性能。
error禁止使用全模糊搜索或左模糊搜索使用全模糊搜索或左模糊搜索将导致查询无法使用索引，导致全表扫描
warnwhere条件内in语句中的参数个数不能超过阈值当IN值过多时，有可能会导致查询进行全表扫描，使得MySQL性能急剧下降；具体规则阈值可以根据业务需求调整， in语句参数最大个数: 100
errorinsert 语句必须指定column当表结构发生变更，INSERT请求不明确指定列名，会发生插入数据不匹配的情况；建议开启此规则，避免插入结果与业务预期不符
warn建议使用UNION ALL,替代UNIONunion会按照字段的顺序进行排序同时去重，union all只是简单的将两个结果合并后就返回，从效率上看，union all 要比union快很多；如果合并的两个结果集中允许包含重复数据且不需要排序时的话，建议开启此规则，使用union all替代union
warnJOIN字段类型不一致JOIN字段类型不一致会导致类型不匹配发生隐式准换，建议开启此规则，避免索引失效
warn连接操作未指定连接条件指定连接条件可以确保连接操作的正确性和可靠性，如果没有指定连接条件，可能会导致连接失败或连接不正确的情况。
errorLIMIT的偏移offset过大因为offset指定了结果集的起始位置，如果起始位置过大，那么 MySQL 需要处理更多的数据才能返回结果集，这可能会导致查询性能下降。offset 大小: 100
warn请使用'<>'代替'!=''!=' 是非标准的运算符，'<>' 才是SQL中标准的不等于运算符
error使用JOIN连接表查询建议不超过阈值表关联越多，意味着各种驱动关系组合就越多，比较各种结果集的执行成本的代价也就越高，进而SQL查询性能会大幅度下降；具体规则阈值可以根据业务需求调整，最大连接表个数: 3
notice禁止对长字段排序对例如VARCHAR(2000)这样的长字段进行ORDER BY、DISTINCT、GROUP BY、UNION之类的操作，会引发排序，有性能隐患；可排序字段的最大长度: 2000
notice建议避免使用select for updateselect for update 会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞，在高并发下，容易造成数据库大量锁等待，影响数据库查询性能
warn引号中的字符串开头或结尾包含空格字符串前后存在空格将可能导致查询判断逻辑出错，如在MySQL 5.5中'a'和'a '在查询中被认为是相同的值
warn检查DML操作的表是否超过指定数据量DML操作大表，耗时较久且负载较高，容易影响数据库性能；具体规则阈值可以根据业务需求调整， 表空间大小（MB）: 10240
notice避免对条件字段使用函数操作对条件字段做函数操作，可能会破坏索引值的有序性，导致优化器选择放弃走索引，使查询性能大幅度降低
notice条件字段存在数值和字符的隐式转换隐式转化会导致查询有无法命中索引的风险，在高并发、大数据量的情况下，不走索引会使得数据库的查询性能严重下降
notice不建议对条件字段使用 NULL 值判断使用 IS NULL 或 IS NOT NULL 可能导致查询放弃使用索引而进行全表扫描
noticedelete/update 语句不能有limit条件delete/update 语句使用limit条件将随机选取数据进行删除或者更新，业务无法预期
noticedelete/update 语句不能有order bydelete/update 存在order by会使用排序，带来无谓的开销
notice不建议使用select *当表结构变更时，使用*通配符选择所有列将导致查询行为会发生更改，与业务期望不符；同时select * 中的无用字段会带来不必要的磁盘I/O，以及网络开销，且无法覆盖索引进而回表，大幅度降低查询效率
warn使用explain加强预检查能力通过 explain 的形式将待上线的DML进行SQL是否能正确执行的检查，提前发现语句的错误，提高上线成功率
error避免使用 COUNT(COL)建议使用COUNT(*)，因为使用 COUNT(col) 需要对表进行全表扫描，这可能会导致性能下降。
notice使用DELETE/DROP/TRUNCATE等操作时注意备份DROP/TRUNCATE是DDL，操作立即生效，不会写入日志，所以无法回滚，在执行高危操作之前对数据进行备份是很有必要的
error避免使用 IN (NULL) 或者 NOT IN (NULL)查询条件永远非真，这将导致查询无匹配到的结果
warn不建议使用没有通配符的 LIKE 查询不包含通配符的 LIKE 查询逻辑上与等值查询相同，建议使用等值查询替代
notice不推荐使用子查询有些情况下，子查询并不能使用到索引，同时对于返回结果集比较大的子查询，会产生大量的临时表，消耗过多的CPU和IO资源，产生大量的慢查询
error不要 UPDATE 主键主键索引数据列的顺序就是表记录的物理存储顺序，频繁更新主键将导致整个表记录的顺序的调整，会耗费相当大的资源
notice停用上线审核模式启用该规则来兼容事后审核的场景，对于事后采集的DDL 和 DML 语句将不再进行上线校验。例如库表元数据的扫描任务可开启该规则
warn检查 UPDATE/DELETE 操作影响指定行数如果 DML 操影响行数过多，会导致查询性能下降，因为需要扫描更多的数据。最大影响行数: 100000
warnUPDATE/DELETE操作缺失where条件因为这些语句的目的是修改数据库中的数据，需要使用 WHERE 条件来过滤需要更新或删除的记录，以确保数据的正确性。另外，使用 WHERE 条件还可以提高查询性能。

  1. TiDB参数清单

TiDB：tidb-server TiKV：tikv-server TiFlash：tiflash-server PD：pd-server

TiKV重要参数说明

echo "fs.file-max = 1000000">> /etc/sysctl.conf
echo "net.core.somaxconn = 32768">> /etc/sysctl.conf
echo "net.ipv4.tcp_tw_recycle = 0">> /etc/sysctl.conf
echo "net.ipv4.tcp_syncookies = 0">> /etc/sysctl.conf
echo "vm.overcommit_memory = 1">> /etc/sysctl.conf
echo "vm.min_free_kbytes = 10485760">> /etc/sysctl.conf
sysctl -p

#官方建议，在物理机环境部署下设置 min_free_kbytes 参数范围：操作系统内存 * (1%～3%)
#开启 numa 下，numa个数 * 1 GB，单个numa 单元内不小于 1 GB
#结合监控查看是否频繁发生allocstall事件而进行动态调整

特例说明：TiKV单机多实例场景

#针对单台物理机器(资源均衡的情况下)多个TiKV实例，TiKV实例重点参数建议
storage.block-cache.capacity
#TiKV block cache 缓存大小，建议物理机器或者NUMA单元内存60%以下，(Total memory)*0.6/TiKV实例个数

readpool.unified.max-thread-count
#读请求的线程池，建议物理机器或者NUMA单元内CPUv的80%，(Total CPUv)*0.8/TiKV实例个数

server.grpc-concurrency
#gRPC 工作线程的数量，正常默认值5，建议物理机器或者NUMA单元内CPUv的10%，(Total CPUv)*0.05/TiKV实例个数

raftstore.store-pool-size
#Raftstore 线程池的大小，正常默认值2即可

raftstore.apply-pool-size
#Apply 线程池的大小，正常默认值2即可

3. ## JDBC 最佳实践

MySQL Connector/J 推荐版本

TiDB 服务端兼容 MySQL 5.7，客户端推荐使用 5.1.49 或更高版本的 5.1.x jdbc 版本。

因为当前 8.0.29 版本有未合并的 Bug 修复，在与 TiDB 共同使用时可能会导致线程卡死。为了避免此问题，建议使用 MySQL Connector/J 8.0.32 或更高版本。

JDBC 参数设置

Java 应用常用的数据库连接池包括 weblogic、c3p0、Druid 等。使用连接池配置数据源时，需要配置一系列参数，其中比较重要的包括 jdbc 的 url 配置，超时探活机制等。

充分认识并理解各项参数有助于让 TiDB 发挥出更高的性能。Mysql 5.1 版本 jdbc configuration properties

一个建议的 url 配置如下：

spring.datasource.url=JDBC:mysql://{TiDBIP}:{TiDBPort}/{DBName}?characterEncoding=utf8&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSqlLimit=10000&prepStmtCacheSize=1000&useConfigs=maxPerformance&rewriteBatchedStatements=true&defaultfetchsize=-2147483648&allowMultiQueries=true

【Prepare 相关参数】

useServerPrepStmts

默认情况下，useServerPrepStmts 的值为 false，即尽管使用了 Prepare API，也只会在客户端做 “prepare”。因此为了避免服务器重复解析的开销，如果同一条 SQL 语句需要多次使用 Prepare API，则建议设置该选项为 true。

cachePrepStmts

如果 jdbc 使用 loadbalance 模式，该参数要设置为 false。

loadbalance 模式下缓存 prepare statement 会出现 prepare 和 execute 分别发给不同会话，执行出错。

虽然 useServerPrepStmts = true 能让服务端执行预处理语句，但默认情况下客户端每次执行完后会 close 预处理语句，并不会复用，这样预处理的效率甚至不如文本执行。所以建议开启 useServerPrepStmts = true 后同时配置 cachePrepStmts = true，这会让客户端缓存预处理语句。

prepStmtCacheSqlLimit

在配置 cachePrepStmts 后还需要注意 prepStmtCacheSqlLimit 配置（默认为 256），该配置控制客户端缓存预处理语句的最大长度，超过该长度将不会被缓存。

prepStmtCacheSize

prepStmtCacheSize 控制缓存的预处理语句数目（默认为 25），如果应用需要预处理的 SQL 种类很多且希望复用预处理语句，可以调大该值，建议配置为 1000。

使用 Batch 批量插入更新

对于批量插入更新，如果插入记录较多，可以选择使用 addBatch/executeBatch API。通过 addBatch 的方式将多条 SQL 的插入更新记录先缓存在客户端，然后在 executeBatch 时一起发送到数据库服务器。

【注意】对于 MySQL Connector/J 实现，默认 Batch 只是将多次 addBatch 的 SQL 发送时机延迟到调用 executeBatch 的时候，但实际网络发送还是会一条条的发送，通常不会降低与数据库服务器的网络交互次数。如果希望 Batch 网络发送，需要在 JDBC 连接参数中配置 rewriteBatchedStatements = true。

通过 addBatch/executeBatch API 方式执行 update 语句时，由于 jdbc 驱动 bug ，如果要同时配置 rewriteBatchedStatements = true 和 useServerPrepStmts = true，推荐同时配置 allowMultiQueries = true 参数来避免这个 bug 。

Druid 连接池建议配置

XML
 <bean id="dataSource"   class="com.alibaba.druid.pool.DruidDataSource"   init-method="init" destroy-method="close"> 
     <!--jdbc_url是AnalyticDB for   MySQL集群的连接地址URL，可以在控制台的集群信息页面获取连接URL。-->
     <property name="url"   value="${jdbc_url}" />

     <!--jdbc_user是AnalyticDB for MySQL集群中的用户账号：高权限账号或者普通账号。-->
     <property   name="username" value="${jdbc_user}" />

     <!--jdbc_password是AnalyticDB for MySQL集群中用户账号对应的密码。-->
     <property   name="password" value="${jdbc_password}" />

     <!--配置初始化连接池大小、最小连接数、最大连接数。-->
     <property   name="initialSize" value="5" />
     <property   name="minIdle" value="10" /> 
     <property name="maxActive"   value="50" />

     <!--配置获取连接等待超时的时间。-->
     <property   name="maxWait" value="1000" />

     <!--配置一个连接在连接池中的最小生存时间、最大生存时间，超过最大生存时间会被移除，单位毫秒。-->
     <property   name="minEvictableIdleTimeMillis" value="60000" />
     <property   name="maxEvictableIdleTimeMillis" value="300000" />

     <!--配置间隔多久进行一次检测，检测需要关闭的空闲连接，单位毫秒。   默认是60s，太长可能会导致无法及时检测到连接中断。-->
     <property   name="timeBetweenEvictionRunsMillis" value="2000" />

     <!--配置从连接池获取连接时，当连接空闲时间大于timeBetweenEvictionRunsMillis时是否检查连接有效性，true每次都检查；false不检查。-->
     <property   name="testWhileIdle" value="true" />

     <!--配置从连接池获取连接时和向连接池归还连接时，是否检查连接有效性。-->
     <!--每次获取或归还连接都检测太频繁，除非特别重要或网络特别不可靠等情况，建议用testWhileIdle + timeBetweenEvictionRunsMillis代替。-->
     <property   name="testOnBorrow" value="false" />
     <property name="testOnReturn"   value="false" />

     <!--配置是否定期探活、探活间隔。-->
     <property   name="keepAlive" value="true" />
     <property   name="keepAliveBetweenTimeMillis" value="30000"   />  <!--默认120s。-->
 
     <!--配置一个连接最大使用次数，避免长时间使用相同连接造成服务器端负载不均衡。-->
     <property   name="phyMaxUseCount" value="1000" />
  
     <!--探活、验证链接有效性的查询，新版本默认使用mysqlPing代替-->
     <property   name="validationQuery"    value="select 1" />
     
     <!--配置监控统计拦截的filters。-->
     <property   name="filters" value="stat" /> 
 </bean>

4. ## oracle 到 TiDB 字段类型映射

Oracle Data Type Mapping MySQL/TiDB Rule
ORACLEMySQL/TiDB
numberdecimal(65,30)
number(p,s)decimal(p,s)
p>0,s>0
number(p,0)tinyint
1<=p<3
number(p,0)smallint
3<=p<5
number(p,0)int
5<=p<9
number(p,0)bigint
9<=p<19
number(p,0)decimal(p)
19<=p<=38
number(p,0)decimal(p,4)
p>38
bfilevarchar(255)
char(length)char(length)
length<256
char(length)varchar(length)
length>=256
character(length)character(length)
length<256
character(length)varchar(length)
length>=256
cloblongtext
blobblob
dateDatetime
decimal(p,s)decimal
p=0,s=0
decimal(p,s)decimal(p,s)
dec(p,s)decimal
p=0,s=0
dec(p,s)decimal(p,s)
double precisiondouble precision
float(p)float
p=0
float(p)double
p>0
integerint
intint
longlongtext
long rawlong blob
binary_floatdouble
binary_doubledouble
nchar(length)nchar(length)
length<256
nchar(length)nvarchar(length)
length>256
nchar varyingnchar varying
nclobtext
numeric(p,s)numeric(p,s)
nvarchar2(p)nvarchar(p)
raw(length)binary(length)
length<256
raw(length)varbinary(length)
length>256
realdouble
rowidchar(10)
smallintdecimal(38)
urowid(length)varchar(length)
varchar2(length)varchar(length)
varchar(length)varchar(length)
xmltypelongtext
interval year(p) to monthvarchar(30)
interval day(p) to second(s)varchar(30)
timestamp(p)timestamp(p)【精度支持 6位】
timestamp(p) with time zonedatetime(p)【精度支持 6位】
timestamp(p) with local time zonedatetime(p)【精度支持 6位】
other data typetext
avatar
文章
阅读
粉丝