免费试用

一、开发规范说明

本规范旨在为使用 TiDB 数据库的应用和系统提供统一参考,标准化 TiDB 数据库的开发使用及 SQL 优化流程,提高业务开发系统的规范性和代码的可读性,减轻维护工作量,提高工作效率。

规范的目标受众为:TiDB 数据库开发设计人员、数据库管理人员、数据库运营人员、数据分析人员及数据库架构师。

二、开发规范内容构成

本规范将根据 PingCAP 及众多 TiDB 用户的最佳实践持续更新,目前包括如下章节,想进一步了解详细信息,请下载阅读《 TiDB 数据库开发规范》白皮书 PDF。

(一)前言

  • 目的
  • 适用范围
  • 高亮示意
  • 注意事项

(二)对象命名规范

  • 原则
  • 数据库命名规范
  • 表命名规范
  • 字段命名规范
  • 索引命名规范

(三)数据库对象设计

  • 表的设计
  • 字段的设计
  • 字段默认值
  • 索引设计
  • 权限设计

(四)数据模型设计

  • 完整性
  • 性能
  • 扩展性

(五)SQL 开发规范

  • 建表删表规范
  • select * 使用规范
  • 大事务处理
  • Region 热点
  • 字段上使用函数规范
  • 数据删除规范
  • 其他规范

(六)事务限制

  • 隔离级别
  • SI 可以克服幻读
  • SI 不能克服写偏斜
  • 不支持 savepoint
  • 大事务限制

(七)隐式类型转换

  • 索引失效
  • 精度丢失

(八)结果集不稳定

  • group by
  • order by
  • 由于 group_concat() 中没有使用 order by 导致结果集不稳定

(九)索引的使用注意

  • TiDB 中的索引
  • 复合索引的设计

(十)自增列的使用注意

  • 原理
  • 最佳实践
  • 人为赋值的后果及挽救措施

(十一)TiDB 中的各种超时

  • GC 超时
  • 事务超时
  • SQL 超时

(十二) JDBC 最佳实践

  • MySQL Connector/J 推荐版本
  • JDBC 参数设置

(十三)缓解热点问题

  • 写入热点
  • 热点的观测和定位
  • 写入热点的缓解
  • 读取热点

(十四)分页的最佳实践

  • 分页查询
  • 单字段主键表的分页批处理
  • 复合主键表的分页批处理

(十五)唯一序列号生成方案

  • 自增列
  • 序列(Sequence)
  • 类 Snowflake 方案
  • 号段分配方案

(十六)流程规范

三、开发规范部分章节预览

(七)隐式类型转换

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

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

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

隐式类型转换增强了人机交互的易用性,但在应用代码中,我们应尽量避免隐式类型转换出现,这是由于隐式类型转换会导致:

  • 索引失效
  • 精度丢失

1)索引失效

如下案例,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)

2)精度丢失

如下案例,字段 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)

(十二)JDBC 最佳实践

1)MySQL Connector/J 推荐版本

TiDB 服务端兼容 MySQL 5.7,客户端推荐使用 5.1.36 或更高版本 的 5.1.x jdbc 驱动下载链接

2)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&prepStmtCacheSqlLimit=10000000000&useConfigs=maxPerformance&rewriteBatchedStatements=true&defaultfetchsize=-214783648

(十四)分页的最佳实践

2)单字段主键表的分页批处理
常规的分页更新 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 数据库开发规范》白皮书 PDF

新经济行业内容专区上线,为新经济企业数据库选型和应用提供深入洞察和可靠参考路径。