专栏 - TiDB库表设计和使用规范

tidb库表设计.png

库表命名规范

表名规范

 表名小写，禁止驼峰，比如 ad_Audit,jobSeq 等等，过长的可以用下划线（_）分割

字段名规范禁止使用 mysql 的关键字，比如 order，group、show、slave 等
```
  详见mysql官网：https://dev.mysql.com/doc/refman/8.0/en/keywords.html
```

索引命名规范

 普通索引：idx_开头，唯一索引：uniq_开头，简写/缩写，简明扼要。

 举例说明：给corp_id,corp_name这2个字段加联合索引，普通索引为：idx_corpid_name，唯一索引：uniq_corpid_name

 不建议：idx_corp_id_corp_name

注：库名、表名、字段名、索引名都小写，长度都限制在64字符以内（TiDB限制）

表结构设计规范

1、TiDB 表主键

每张表一定要有一个主键，跟 MySQL 建表不同，主键不一定是 int/bigint 自增，如果有写入性能问题更不见建议使用自增主键（写热点），可以使用 UUID、字符串、联合字段来做主键时需要在建表语句后面添加下面2个参数来打散 region：

SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS=3

另外对于写入量大的可以使用 auto random 主键来提升写入性能，如下

id bigint PRIMARY KEY AUTO_RANDOM。

2、必须标注表和字段的 comment

       比如：`mobile` varchar(20) DEFAULT NULL COMMENT '联系手机’

3、建表时提供表示创建时间和更新时间的 createdat updatedat 字段，并使用 mysql 内建的 CURRENT_TIMESTAMP 作为默认值，数仓的增量数据抽取依赖这2个字段。

4、字段能定义为非空的就定义为非空

  比如：user_name varchar(20) not null default ‘’ comment ‘用户名’

       uid int(10) not null default ‘0’ comment ‘用户id’

      注意：text类型必须default null

5、字段设置了 NOT NULL 的，一定要指定默认值，否则字段写入时肯定报错。

6、对于内容类字段优先考虑使用 utf8mb4 编码以支持 emoji 表情文字，如果预期数据量较大，尽量将内容较长且不用于查询的 BLOB、TEXT 列单独建表。

7、关于分区表使用，一般日志类、报表类业务都喜欢用基于时间的 range 分区表(可以用)，Hash 分区（用的少）可以用于大量写入场景下的数据打散，List 分区（5.X版本才有，实验特性，慎用！）

    为啥用分区表？drop/truncate partition这种快速清理数据不比delete from 大事务报错香？？

    分区表的限制：4.0版本最多支持1024个分区，5.X版本支持8192个分区。

8、字段类型选择，目的：合适的类型，合适的大小

   (1)能用 tinyint 不用 int，why？

     tinyint 能存-128~127，对于一些 status\type\gender等 业务字段完全够用

   (2)能用数值类型不用 varchar

     比如存手机号 bigint 就够了，存 ip 使用 int 类型来存。

   (3)字符串类型选择，char 还是 varchar ？

      定长用 char，比如像固定的 open_id char(32)，jid char(36)，cid char(36)，md5值

      变长用 varchar，比如 name varchar(40)

   (4)对于字符串类型长度够用即可。

      比如存 major，有的人用表结构生成器，表字段一水的 varchar(255)，其实varchar(50)足够

   (5)尽量不用 TEXT 类型（能用varchar(10000)也不要用text）
      需要强调：mediumtext 在 mysql 能支持最大 16M 的单行数据，tidb 因为 KV size 的限制，只能支持到 6M，超过这个 size 写入报错。如果必须要用，那就将涉及 text 的字段独立成表。

SQL 使用规范

1、TIDB 索引使用

联合索引使用：如果线上存在复合条件查询，务必通过复合索引，如果 SQL 查询的字段以及 where 条件覆盖到查询中的所有条件字段形成覆盖索引的话，性能更佳。

关于联合索引使用的问题：（A，B，C）的联合索引建立的情况，下面的SQL都可以使用到
（1）where A=xxx
（2）where A=XXX and B=xxx
（3）where A=XXX and B=xxx and C=xxx
PS：经常遇到有了（A，B，C）还单独创建A或者A+B联合索引的，这样就属于重复索引

务必将 ORDER BY 中的列覆盖在索引中，不然很容易出现对性能影响sort。
不推荐建立过多的索引，禁止冗余的索引、不使用的索引需要及时删除。推荐扩展现有索引，而不是建立新的索引。过多的索引容易影响优化器决策而形成严重性能问题。1)单张表中索引数量不超过5个；
```
2)单个索引中的字段数不超过5个；

3)对长字符串使用前缀索引，如：char(100)；

4) 对区分度较低（重复值很多）的字段一般不建立单独索引，如：type字段(取值只有几个)
```

2、SQL 语句编写规范

避免使用select *，就算要用所有的表字段也建议都列出来，因为如果程序没有table字段对应关系的配置，表的字段增加删除都会导致业务取到的结果有问题，另外只查自己想要的字段也能降低SQL执行时间中的网络传输时间（可以拿带text类型的表对比测试）。
禁止执行没有where条件的表select/DML
避免在查询中使用 OR，OR两边的条件都需要有索引并且会产生会使用到性能较差的index merge
对于核心的OLTP业务，线上不建议使用 JOIN 操作，有可能引发集群抖动。
对于一些重要数据的“删除”，不推荐使用 DELETE，对于内容类数据优先考虑update软删除。
推荐Batch insert，根据表字段的情况，batch size控制在一定的数量，不建议太多(事务过大，引发性能问题或者报错)。
DML SQL要避免TiDB的大事务限制(单KV：6M，默认事务100M可调)
业务RD喜欢begin；多个DML SQL；commit；在乐观事务的情况下，默认只支持5000条DML，可以通过stmt-count-limit调整。另外也不建议多DML SQL一次commit这种方式写入数据。
TIDB的DDL不支持多列操作，所以：alter table不支持添加多个字段、多个索引。

最后强调下：禁止RD直连线上DB进行SQL操作(如果是DBA，肯定在职业经历中碰到过RD误删除要恢复的事故)，公司需要提供自研 or 开源的SQL审核和执行平台来解决问题。

3、不能用到索引的6种情况

  (1)字符串转义
      只在于表中是varchar、char字符串类型，执行时赋值为数值类型
  (2)函数包含
     各种函数比如常用的date函数，date_add,date_sub等等
     错误的方式：
     explain select  * from tb_dxl_test where date(update_date)='2016-10-06';
  (3)运算
    比如select * from t1 where start-end=10
  (4)Like ‘%dai’ / like ‘%dai%’ ,即最左的模糊匹配
      错误的方式：
       explain select  * from tb_dxl_test where name like '%好';
      正确的方式：
      explain select  * from tb_dxl_test where name like '代%';
  (5)对匹配度底的字段建立索引，也可能用不到
       比如一个type类型取值只有0、1
  (6)隐式转换
             表中字段是varchar/char字符串类型，业务将“数值”存入，然后基于数值查询
             explain select  * from tb_dxl_test where self_numb=110

TIDB与MySQL兼容性区别

推进大容量 mysql 或者分库分表业务迁移 TiDB 本来是好事儿，但是还是需要将兼容性区别也列入到 TIDB 规范中，这样业务会提前了解并对自己业务进行修改。

1、TiDB 的自增 id 不连续，存在 id 为1/30001/60001的数据都是同一时刻写入的，所以业务基于id order by 的规则需要调整为基于时间排序。

2、不支持外键、存储过程、触发器、全文索引等

3、排序规则不同( collation 是在字符集中比较字符以及字符排序顺序的规则）。在默认的二进制排序规则( utf8mb4_bin )中，比较 A 和 a 的结果是不一样的，mysql的排序规则是：utf8mb4_general_ci，where str=‘A’跟‘a’都能查到相同的结果,TiDB 在4.0的高版本和5.X支持了大小写不区分的排序规则，创建表时需要“显示”设定排序规则。

4、再次强调：TiDB 不能在单条 ALTER TABLE 语句中完成多字段操作。例如，不能在单个语句中添加多个列或索引，否则，可能会输出 Unsupported multi schema change 的错误;这个在使用基于 mysql 的审核平台时会经常遇到，需要修改平台进行兼容。

5、4.0 的 TiDB 不支持添加/删除主键，除非开启了 alter-primary-key 配置项;

6、不支持将字段类型修改为其超集，例如不支持从 INTEGER 修改为 VARCHAR，或者从 TIMESTAMP 修改为 DATETIME

7、更改/修改数据类型时，不支持“有损更改”，比如bigint→int，varchar(200)→varchar(100)

8、TIDB的事务限制，单kv最大支持6M，也就是说mysql表中mediumtext类型(最大支持16M)迁移到tidb时可能会因为记录过大而写入失败。

9、TIDB默认支持 100M size 的事务，这个默认值可以通过配置文件中的配置项 txn-total-size-limit 进行修改，最大支持 10 GB 的事务。

更详细的兼容性区别，详见官网链接 https://docs.pingcap.com/zh/tidb/stable/tidb-limitations