SQL 开发规范及基本原则

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

对象命名规范

  • 命名建议使用具有意义的英文词汇,词汇中间以下划线分隔;
  • 命名只能使用英文字母、数字、下划线;
  • 避免用 TiDB 的关键字,如 group,error,rank 等作为对象名;
  • 建议所有的数据库对象使用小写字母;
  • 所有的数据库对象的命名请注意标识符长度硬限制

数据库命名规范

  • 按照业务模块、产品线和访问权限隔离需求来创建数据库;
  • 建议数据库名称不要超过 16 个字符,如:基础信息库(basicinfo_db)、认证中心库(certifycenter_db);

表命名规范

  • 同一业务或者模块的表尽可能使用相同的前缀,表名称尽可能表达含义;
  • 多个单词以下划线分隔,不推荐超过32个字符;
  • 建议对表的用途进行注释说明,以便于统一认识; 临时用统计表(tmp_t_crm_relation_0425) 备份表(bak_t_crm_relation_20170425)
  • 只支持将 lower-case-table-names 值设为 2,即数字字典中记录的表名区分大小写,匹配查找表名时不区分大小写;

字段命名规范

  • 字段命名需要表示其实际含义的英文单词或简写;
  • 建议各表之间相同意义的字段应同名,并且一定使用相同的字段类型;
  • 字段也尽量添加注释,枚举型需指明主要值的含义,如”0 - 离线,1 - 在线”;
  • 布尔值列命名为 [is_描述]。如 member 表上表示为 enabled 的会员的列命名为 is_enabled;
  • 字段名不建议超过 32 个字符;

索引命名规范

  • 主键索引:pk[表名称简写][字段名简写]
  • 唯一索引:uk[表名称简写][字段名简写]
  • 普通索引:idx[表名称简写][字段名简写]
  • 多单词组成的字段名,使用能代表意义的缩写 ;

表的设计

  • 表需要有主键或者非空唯一索引,能与各项复制工具更好地兼容;
  • 主键不要带有业务含义;
  • 业务表使用自增主键时,字段类型推荐使用 bigint unsigned,最大值可达 18446744073709551615;
  • 出于为性能考虑,尽量避免存储超宽表,数据长度过大的字段最好拆到独立的表存储,建议表字段数不超过 60 个,建议单行的总数据大小不要超过 64K,TiDB 硬限制单行的总数据大小不超过 6 MB;
  • 不推荐使用复杂的数据类型,如 blob 或者 json;
  • 进行 join 的关联字段,数据类型保证一致,避免隐式转换;
  • 不能以范式作为唯一标准或者指导,在设计过程中,需要从实际需求出发,以性能提升为根本目标来展开设计工作。为了提升性能减少表关联,可以适当保存冗余数据做反范式设计;
  • 表对象的设计请注意单个 Table 的限制

字符集

  • 建表时只使用默认的 utf8mb4 编码;
  • utf8mb4 的默认排序规则为 utf8mb4_bin(区分大小写);
  • 支持 utf8mb4_general_ci(不区分大小写),但是需要在集群环境部署时配置 新的排序规则框架
  • 其他的排序规则仅为语法支持,实际不支持;

列的自增属性

  • 列的自增属性仅保证唯一,仅能保证在单个计算节点中自增,不保证多个计算节点中自增,不保证自动分配的值的连续性。业务不应该依赖自增属性的连续性和有序性,如记录的插入顺序排序应按照记录的创建时间。带有自增属性的列出现空洞和跳跃插入的现象是正常现象;
  • 不要在语句中显式指定具有自增属性的列的值,由 TiDB 自动分配,否则可能会出现值重复冲突;
  • 允许移除列的 AUTO_INCREMENT 属性,但是请谨慎评估,移除该属性后不可恢复;

创建、删除表规范

  • 表的建立在遵循表命名规范前提下,如果业务应用内部封装建表删表语句,需要增加判断逻辑,防止业务流程异常中断。例如:create table if not exists table_name 或者 drop table if exists table_name 语句建议增加 if 判断,避免应用侧由于表的改动造成的异常中断;
  • 不支持 create table as select 语法,需要改写为表结构复制 create table like … 和将数据写入的 insert into select … 的组合语句;

变更表规范

  • 不支持单条 ALTER TABLE 语句中完成多个操作,不能在单个语句中添加多个列或索引,需要更换成多个单个列或索引的操作;
  • 不支持对字段类型的有损修改或修改为超集,请参考ddl-的限制

大事务处理

  • 单个事务的总大小默认不超过 100 MB,最大支持 10 GB,实际的单个事务大小限制还取决于服务器剩余可用内存的大小,执行事务时 TiDB 进程的内存消耗大约是事务大小的 6 倍以上;
  • 由于执行事务时 TiDB 进程的内存消耗大约是事务大小的 6 倍以上,事务设置过大,或者 Batch 过高,可能导致 tidb-server OOM,需要评估好内存容量;
  • 为了使性能达到最优,需要对大事务按某个业务维度进行拆分,每 100~500 行提交一个事务;

SELECT * 使用规范

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

防范写入热点创建规范

  • 对于写入量非常大的表,应当通过应用性能压测等方法在测试环境模拟表的热点情况;
  • 通过以下三种手段进行配置,规避表的主键写入热点。 避免连续自增主键的设计,建议采用雪花算法生成 UUID 主键; 主键是非整数的表或者启用 alt-primarykey=true 配置后创建的所有表,使用 SHARD_ROW_ID_BITS 语法创建基于 rowid 的分片方案,例如:CREATE TABLE t (c int) SHARD_ROW_ID_BITS = 4 或 ALTER TABLE t SHARD_ROW_ID_BITS = 4; 创建按照 Hash 或 Range 分区表避免热点;

数据删除规范

  • 删除表中全部的数据时,使用 TRUNCATE 或者 DROP 后重建方式,不要使用 DELETE。以上几种数据删除方法执行后,都不会立即释放空间,需要等待 TiDB 后台的 GC (garbage collection) 和 Compaction 机制对空间回收后重新利用;
  • 对于按范围进行部分数据的删除,如果超过大事务的限制,可以参考以下窗口函数的方法,分成批量小任务进行数据的删除; a. 将数据按照主键排序,然后调用窗口函数 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 | | ........ |.......... | ........ | | 201900019 | 201950018 | 50000 | | 201950019 | 201999003 | 48985 | +-----------+-----------+-----------+ 40 rows in set (1.51 sec) b. 借助计算好的分组信息,使用 serialno between start_key and end_key 操作每个分组的数据,实现高效数据删除或者更新。

分页查询 order by 语法使用规范

  • 分页查询语句需要带有排序条件,除业务排序条件外还应包含主键或者其他唯一键以保证分页稳定,规避没有业务排序字段或者一个业务排序字段值匹配多条记录导致结果集不稳定; 常规分页语句写法(start:起始记录数,page_offset:每页记录数): select * from table_a t order by gmt_modified desc,pk limit start,page_offset;

group by 语法使用规范

  • select 字段中不得引用未在 group by 子句中声明的非聚集字段,即不得使用 MySQL non-full group by 语法; 以下的语句是不被允许的,select class,stuname,max(score) as max_score from score group by class;

模糊匹配 like 语法使用规范

  • 使用 like 模糊匹配时,查找字符串中通配符 % 放首位会导致无法使用索引。业务语句中使用 like 查找字符串不使用 % 放首位,或者使用时结合其他有效的约束条件;

视图使用规范

  • 支持为应用程序建立专门的视图而不必非要应用程序直接访问数据表;
  • 视图不可更新,不支持 UPDATE、INSERT、DELETE 等写入操作;

多表关联查询规范

  • 多表关联应该显式使用join子句,避免漏掉关联条件,造成笛卡尔积;
  • 嵌套SQL语句应该为不同表指定不同别名;
  • 高并发交易场景,单条语句关联表不超过两张,使用执行计划为 IndexJoin 的多表关联语句,外表建立了正确的条件过滤索引,内表建立了正确的关联和条件过滤索引;
  • 低并发的分析场景,单条语句关联表不超过 10 张,其中亿级表不超过 2 张。需要注意tidb_mem_quota_query 参数指定的单条语句内存使用限制,默认是 1GB,生产建议不超过 16 GB;

分区表规范

  • 当数据需要按时间进行归档清理时,可按某个业务时间段对表进行分区,对分区进行 truncate 操作满足数据清理要求;
  • 按时间进行分区表的粒度应该将分区记录数控制在十亿级别,不应该配置太小,如日分区,也不应太大,如年分区;
  • 在分区表上进行查找时,查找条件必须包含分区字段的查找条件。分区表的二级索引属于分区内索引,需要先通过分区裁剪的方式,定位到具体分区后再经过二级索引回表查找;