SQL 开发规范及基本原则

本文主要介绍在开发业务及应用时,需要遵守的 SQL 规范及基本原则。

对象命名规范

  • 命名建议使用具有意义的英文词汇,词汇中间以下划线分隔;
  • 命名只能使用英文字母、数字、下划线;
  • 避免用 TiDB 的保留字如 group,order 等作为单个字段名;
  • 建议所有数据库对象使用小写字母。

创建、删除表规范

基本原则:表的建立在遵循表命名规范的前提下,建议业务应用内部封装建表删表语句来增加判断逻辑,防止业务流程异常中断。例如:create table if not exists table_name 或者 drop table if exists table_name 语句建议增加 if 判断,避免应用侧由于表的改动造成的异常中断。

SELECT * 使用规范

基本原则:禁止使用 SELECT * 进行查询。建议按需求选择合适的字段列,杜绝直接 SELECT * 读取全部字段,减少网络带宽消耗,有效利用覆盖索引。

大事务处理

基本原则:

  • 按需求选择合适的字段列,杜绝直接用 SELECT * 读取全部字段,以减少网络带宽消耗,有效利用覆盖索引。

  • 拒绝大事务,TiDB 对单个事务的大小有限制,这层限制是在 KV 层面,反映在 SQL 层面的话,一行数据会映射为一个 KV entry,每多一个索引,也会增加一个 KV entry,所以这个限制反映在 SQL 层面如下:

    • 单行数据不大于 6MB
    • 默认一次提交的全部数据 100MB,可以通过调整 txn-total-size-limit 更改,最大不超过 10GB
    • 在 4.0 以前的版本,TiDB 限制了单个事务的键值对的总数量不超过 30 万条,从 4.0 版本起 TiDB 取消了这项限制。

注意:

无论是大小限制还是行数限制,还要考虑 TiDB 做编码以及事务额外 Key 开销,在使用的时候,建议每个事务的行数不要超过 1 万行,否则有可能性能不佳。建议无论是 InsertUpdate 还是 Delete 语句,都通过分 Batch 或者是加 Limit 的方式限制,启用 Batch 操作步骤参考:set @@session.tidb_distsql_scan_concurrency=5

注意:

由于执行事务时 TiDB 进程的内存消耗大约是事务大小的 6 倍以上,事务设置过大,或者 Batch 过高,可能导致 tidb oom,需要评估好内存容量。

Region 热点

产生热点的原因:

  • 这条 SQL 涉及到的 Region 的 leader 全部在这个 TiKV 上。
  • 这条 SQL 只涉及到一个 Region,并且有大量的请求使用同样或者类似的 SQL 语句。

基本原则:

  • 如果表的数据量比较小,数据存储大概率只涉及到一个 region,大量请求对该表进行写入或者读取都会造成该 region 热点,可以通过手工拆分 region 方式进行调整,调整方式如下:
operator add split-region 1   // 将 region 1 对半拆分成两个 region
  • 如果表的数据量比较大,region leader 分布不均衡,某些 tikv 节点 region leader 比较多,不均衡导致的热点需要通过某种机制平衡 leader 分布,平衡方式参考如下:

    自动均衡:

    curl -G "host:status_port/tables/DB_NAME/TABLE_NAME/scatter"  // 打散相邻 region

    手动均衡:

    operator add transfer-leader 1 2   // 把 region 1 的 leader 调度到 store 2

建议事项:

  • Handle ID 设计时避免连续自增主键的设计,建议采用置换算法,例如时间 12010120180601。

  • TiDB Partition: 通过设置表分区方式来避免热点,支持按照 Hash 以及按照 Range 分区。

  • SHARD_ROW_ID_BITS: 这个 TABLE OPTION 是用来设置隐式 _tidb_rowid 的分片数量的 bit 位数。对于 PK 非整数或没有 PK 的表,TiDB 会使用一个隐式的自增 rowid,大量 INSERT 时会把数据集中写入单个 region,造成写入热点。通过设置 SHARD_ROW_ID_BITS 可以把 rowid 打散写入多个不同的 region,缓解写入热点问题。 但是设置的过大会造成 RPC 请求数放大,增加 CPU 和网络开销。SHARD_ROW_ID_BITS = 4 代表 16 个分片, SHARD_ROW_ID_BITS = 6 表示 64 个分片,SHARD_ROW_ID_BITS = 0 就是默认值 1 个分片 。操作语句如下:

    CREATE TABLE 语句示例:

    CREATE TABLE t (c int) SHARD_ROW_ID_BITS = 4

    ALTER TABLE 语句示例:

    ALTER TABLE t SHARD_ROW_ID_BITS = 4

字段上使用函数规范

基本原则:在取出字段上可以使用相关函数,但是在 Where 条件中的过滤条件字段上严禁使用任何函数,包括数据类型转换函数。示例如下:

  • 错误的写法:

    select gmt_create
    from .. .
    where date_format(gmt_create,'%Y­%m­%d %H:%i:%s') = '2009­01­01 00:00:0';
  • 正确的写法:

    select date_format(gmt_create,'%Y­%m­%d %H:%i:%s')
    from .. .
    where gmt_create = str_to_date('2009­01­01 00:00:00''%Y­%m­%d %H:%i:s');

数据删除规范

基本原则:删除表中全部的数据时,使用 TRUNCATE 或者 DROP 后重建方式,不要使用 DELETE

详细说明:DELETETRUNCATEDROP 都不会立即释放空间,对于 TRUNCATEDROP 操作,在达到 TiDB 的 GC (garbage collection) 时间后(默认 10 分钟),TiDB 的 GC 机制会删除数据并释放空间。对于 DELETE 操作 TiDB 的 GC 机制会删除数据,但不会释放空间,而是当后续数据写入 RocksDB 且进行 compact 时对空间重新利用。

分页查询规范

基本原则:

  • 分页查询语句全部都需要带有排序条件,除非业务方明确要求不要使用任何排序来随机展示数据。常规分页语句写法(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 from
    where
    a.column_a,a.column_b .. . b.column_a,b.column_b .. . (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
    a.column_c = b.column_d;
    select * from t limit 10000,10;
    select * from t order by c limit 10000,10;

其他规范

  • WHERE 条件中不在索引列上进行数学运算或函数运算。
  • in() /union 替换 or,并注意 in 的个数小于 300。
  • 禁止使用 % 前缀进行模糊前缀查询。