基于 TiDB 的常见应用开发误区

本文基于 TiDB 4.0 之前的版本编写，TiDB 从 4.0 版本开始默认开启悲观事务，并且支持在悲观锁模式下的 RC 隔离级别，本文中描述的大部分 OLTP 联机应用部分的改造在 TiDB 4.0 版本中不再适用。

一、前言

二、事务
1. TiDB 隔离级别详解
2. 显式事务中 DML 语句返回的 affected rows 不可信
3. 避开丢失更新影响的应用开发方法
4. 热点账户场景的处理方法
5. “嵌套事务”
6. 不支持 Spring 框架的 PROPAGATION_NESTED 传播行为（依赖 savepoint 机制）
7. 大事务
8. 读取超时
9. 事务超时
10. SQL 执行超时

三、自增 ID
1. TiDB 中的自增 ID 分配原理
2. 自增 ID 设计最佳实践
3. TiDB 中的自增 ID 使用方法
4. 人为向自增列写入值后 TiDB 分配自增值时报 “Duplicate entry” 错误的处理方法
5. 高并发的唯一序列号生成方案

四、唯一性约束
1. 主键与唯一索引
2. 外键
3. OGG（Oracle GoldenGate）用户需要注意 INSERT 语句默认只在提交时进行唯一性约束校验

五、索引
1. TiDB 中的索引
2. 复合索引的设计

六、批量计算场景的写入优化
1. 配置 SHARD_ROW_ID_BITS 拆散写入热点
2. 使用分区表拆散写入热点

七、SQL 语法注意事项
1. 暂不支持 create table as select 语法
2. 使用 full group by 语法保障结果集稳定
3. 使用 order by 语法保障结果集输出顺序

一、前言

本文基于大量 TiDB 客户案例编写，总结了 TiDB 上常见的开发误区，主要受众为在 TiDB 上进行应用开发或将业务代码迁移至 TiDB 的架构师和工程师。

二、事务

1. TiDB 隔离级别详解

Image source: Lecture #04 - Concurrency Control (Part II) [CMU Database Systems Spring 2016]

几种数据库的默认隔离级别：

Image source: A Critique of ANSI SQL Isolation Levels

TiDB 支持的隔离级别是 Snapshot Isolation（SI），它和 Repeatable Read（RR）隔离级别基本等价，详细情况如下：
TiDB 的 SI 隔离级别可以克服幻读异常（Phantom Reads），但 ANSI/ISO SQL 标准中的 RR 不能。所谓幻读是指：事务 A 首先根据条件查询得到 n 条记录，然后事务 B 改变了这 n 条记录之外的 m 条记录或者增添了 m 条符合事务 A 查询条件的记录，导致事务 A 再次发起请求时发现有 n+m 条符合条件记录，就产生了幻读。
TiDB 的 SI 隔离级别不能克服写偏斜异常（Write Skew），需要使用 Select for update 语法来克服写偏斜异常。写偏斜异常是指两个并发的事务读取了不同但相关的记录，接着这两个事务各自更新了自己读到的数据，并最终都提交了事务，如果这些相关的记录之间存在着不能被多个事务并发修改的约束，那么最终结果将是违反约束的。例如，值班表有两列，姓名以及值班状态，0 代表不值班，1 代表值班姓名值班状态张三 0李四 0王五 0有这样一个事务，它的逻辑是判断当前无人值班，则分配一个值班人。当该程序顺序执行时，只会分配一个值班人。但当它并行执行时，就可能出现多人同时为值班状态的错误，造成这个错误的原因就是写偏斜。
TiDB v3.0 以下的版本在默认配置下（tidb_disable_txn_auto_retry=0）不能克服丢失更新异常（Lost Updates），这是由于在事务冲突中提交较晚的事务被自动重试导致的（重试时会获取最新的 tso，相当于在重试当时开启了一个新的事务），该默认行为将在 3.0 版本中修正。所谓丢失更新是指：两个事务 A，B 读取相同记录并更新同一列的值，若 A 先于 B 提交事务，当 B 事务提交后 A 再次查询时发现自己的更新丢失了。

2. 在开启重试时显式事务中 DML 语句返回的 affected rows 不可信
与所有使用了乐观锁机制的分布式数据库（PXC, MGC, MGR 等）一样，在显式执行的事务中（设置为非自动提交 autocommit=0，或使用 begin 语句显式声明事务开始），DML 操作所返回的 affected rows 并不保证与最终提交事务时所影响的数据行数一致。
如下案例，事务 B 在并发中丢失了它的更新：

这是由于在显式执行的事务中 DML 操作与提交操作分开被执行，在事务提交过程中，如果由于事务冲突而发生了重试（tidb_disable_txn_auto_retry=0），TiDB 将获取新的 tso 后重新执行本事务中的 DML 操作，原本的 SI 隔离级别在重试后被破坏了，会产生类似 RC 隔离级别的不可重复读与幻读异常现象。
由于重试机制在内部完成，如果最终本事务提交成功，用户一般是无法感知到是否发生了重试的，因此不能通过 affected rows 来作为程序执行逻辑的判断条件。
而隐式事务中（以单条 SQL 为单位进行提交），语句的返回是提交之后的结果，因此隐式事务中的 affected rows 是可信的。

3. 避开丢失更新影响的应用开发方法
TiDB 使用了乐观锁机制，乐观锁仅在提交时才会进行冲突检测和数据上锁。在使用了非 select for update 的 SQL 语句时，为了保障提交成功率 TiDB 会对提交时遇到冲突而发生退避的事务进行自动重试（由 tidb_disable_txn_auto_retry 变量控制，默认行为是自动重试），当事务达到退避次数限制（默认 10 次）依然不能成功提交时，事务会被回滚。
发生了退避的事务会重新获取时间戳，重新执行事务中的增删改语句，这样设计是为了规避上一次造成提交失败的原因（包括但不限于事务冲突），提高事务提交的成功率，但也因此导致了并发事务可能出现丢失更新异常。TiDB 将在 3.0 版本开始提供独立的，决定事务冲突是否重试的开关（tidb_disable_txn_auto_retry），与非事务冲突重试（如网络异常，region leader 调度等引起的重试）的开关独立开来，用户将能够对事务冲突后的行为进行配置。
可以通过妥善的应用实现方式来避免丢失更新造成的影响。

场景一，在不做余额检查的类似转账交易场景中，一般通过账号筛选出需要修改余额的记录，然后直接在数据库中进行数学运算的 SQL 来实现对账户余额的更新，诸如此类写法的事务即使在并发执行时遇到了丢失更新异常，也可以正确的完成转账操作，并不会被用户感知到：

create table account (cuno varchar(20) primary key, realtimeremain decimal(17,2));
insert into account values('A',1000), ('B',1000), ('C',1000);

事务 A：

update account set realtimeremain = realtimeremain-100 where cuno='A';
update account set realtimeremain = realtimeremain+100 where cuno='B';
commit;

事务 B：

update account set realtimeremain = realtimeremain-100 where cuno='C';
update account set realtimeremain = realtimeremain+100 where cuno='B';
commit;

同上转账场景，如果实现方式是应用获取了当前转入转出账户的余额后，在应用中计算出转账后两账户的余额，使用常值写入余额字段，这样的实现方式在事务并发执行时将会导致错误：

事务 A：

select realtimeremain from account where cuno='A'; --返回 1000
select realtimeremain from account where cuno='B'; --返回 1000
--应用中计算出两账户转账后的余额分别为 900 和 1100
update account set realtimeremain = 900 where cuno='A';
update account set realtimeremain = 1100 where cuno='B';
commit;

事务 B：

select realtimeremain from account where cuno='C'; --返回 1000
select realtimeremain from account where cuno='B'; --返回 1000
--应用中计算出两账户转账后的余额分别为 900 和 1100
update account set realtimeremain = 900 where cuno='C';
update account set realtimeremain = 1100 where cuno='B';
commit;

场景二，想象这样一个场景，某公司决定给所有部门为 IT 的员工加薪 100，如下的实现的事务因某种原因而并发执行时，假设操作员 A 执行了加薪操作，事务还未提交时操作员 B 检查发现还没有人执行加薪操作，B 重复执行了程序，在 A 提交事务之后 B 也提交了事务，A 查看 IT 部门的最新薪资发现员工被加薪了 200，这样就会导致重复加薪的错误：

update compensation set salary=salary+100 where dept=’IT’;
commit;

在使用了 select for update 时，TiDB 不会重试提交时遇到冲突的事务，在提交时检查到冲突（数据已经被修改），事务会被回滚。
据此，将场景二的 SQL 改写如下，在并发执行时，先提交的事务执行成功，后提交的事务被回滚：

select * from compensation where dept=’IT’ for update;
update compensation set salary=salary+100 where dept=’IT’;
commit;

4. 热点账户场景的处理方法
如上一段所讲，TiDB 采用了乐观锁机制，在事务的并发处理中，TiDB 会自动重试提交时遇到冲突而发生退避的事务；而在使用了 select for update 或关闭 tidb_disable_txn_auto_retry 变量时，这种退避机制会失效，后提交的事务会被回滚。
select for update 被使用于热点账户，秒杀，公用账户、理财产品、国债的余额扣减等场景，技术特点是并发的对同一行数据进行修改。传统的单机 DBMS 多使用悲观锁来实现 select for update，在事务开始的时候即进行锁检查，如果事务所需要的锁和数据上当前的锁不兼容，就会发生锁等待，等当前的锁释放后本事务才能执行。TiDB 在执行 select for update 时相当于悲观锁系统中将锁等待时间设置为 0，遇到锁冲突的事务会执行失败。
综上，TiDB 不适合用于处理高并发的对同一行数据进行修改，事务使用了 select for update 语句，可以保证数据的一致性，但并发执行的事务中，只有最先提交的事务会成功，其余的并发请求都会被回滚。
处理热点账户场景的最佳实践是将热点账户功能转移到缓存（redis，codis 等）中实现，如购买国债产品场景中，将国债余额读取到缓存中，在缓存中根据余额与购买额度对请求队列进行控制，向合格的请求发放访问数据库的令牌，向购买额度超过余额的请求返回余额不足的错误，拿到令牌的请求可以并发去修改数据库中的产品余额。
在应用了悲观锁的 DBMS 中，并发的 select for update 事务实际上是被排成队列以串行的方式执行的，因此性能不高，而使用缓存来处理热点账户场景也有着较大的性能优势。

5. “嵌套事务”
遵照 ACID 理论，并发事务间应彼此相互隔离，避免互相干扰。即事务不能“嵌套”。
在 Read Committed 隔离级别下，同一事务中如果存在多次读取，每次读到的都是当时已经提交的数据，在多个事务并发执行时，一个事务内多次读取的结果可能千差万别，这种现象被称为“不可重复读（Non-repeatable Reads）”。
应用于传统金融行业的 RDBMS 产品中，默认隔离级别为 RC 的产品占有绝大部分市场份额，而应用开发中也很少有人注意到隔离级别的设置，因此“不可重复读”往往被应用开发人员认为是一种功能，甚至据此开发了基于“嵌套事务”的应用。
下图中的案例描述了一个典型的“嵌套事务”的执行逻辑（红色箭头）。session 1 和 session 2 是该程序开启的两个会话，左侧的 T1 ~ T8 是时间轴。程序在 T1 的时候开启了一个会话 session 1，然后执行了一个查询（注意，在 MySQL 协议中，begin 的下一条有数据访问的语句被视为事务的开始）。之后的 T3 ~ T5，程序开启了另一个会话 session 2，写入了一行数据后提交。然后程序继续操作 session 1，在 T6 时它试图更新这行刚刚写入的数据，并在 T7 时提交了 T2 时开启的本事务。
T8 时，session 1 执行了一条查询语句，来检查最初在 T4 时由 session 2 写入的 k=1 对应行的 val 值。

在 RC 隔离级别下，T8 时查询的返回值为 102，看上去似乎满足了“嵌套事务”的功能需求。但实际上这是错误的，案例中仅使用单线程模拟了“嵌套事务”的场景，在实际业务的并发请求下，多个事务在时间轴上交错执行，交错提交，将使“嵌套事务”的执行结果变得不可预知。
在 SI 或 RR 隔离级别下，直到提交或回滚之前的任何读取（不限于 tab1 表）所返回的结果都对应事务开始的那个瞬间的一致性状态。也就是说，在 T2 时，session 1 中的事务所能读取到的数据就已经确定了，它就像是给数据库在 T2 时的留下了一个快照，即使之后的 T3 ~ T5 开启了新的 session 2，写入数据并提交，也不会影响 T6 时 session 1 所读取到的数据，T6 未读取到 k=1 的行，因此更新了 0 行。在 T8 时，查询的返回值为 2。在 SI 或 RR 隔离级别下，事务间的隔离度更高了，在并发请求下，其结果也是可预期的。
在这个案例中，如果只是想实现 session 1 能够更新到 session 2 写入的数据的需求，只需要控制程序逻辑，在 T2 时的查询语句之后添加 commit 步骤，及时提交这个查询事务，再执行后续步骤即可。

6. 不支持 Spring 框架的 PROPAGATION_NESTED 传播行为（依赖 savepoint 机制）
Spring 支持的 PROPAGATION_NESTED 传播行为会启动一个嵌套的事务，它是当前事务之上独立启动的一个子事务。嵌套事务开始时会记录一个 savepoint，如果嵌套事务执行失败，事务将会回滚到 savepoint 的状态，嵌套事务是外层事务的一部分，它将会在外层事务提交时一起被提交。下面案例展示了 savepoint 机制：

mysql> BEGIN;
mysql> INSERT INTO T2 VALUES(100);
mysql> SAVEPOINT svp1;
mysql> INSERT INTO T2 VALUES(200);
mysql> ROLLBACK TO SAVEPOINT svp1;
mysql> RELEASE SAVEPOINT svp1;
mysql> COMMIT;
mysql> SELECT * FROM T2;
+------+
| ID |
+------+
| 100 |
+------+

TiDB 不支持 savepoint 机制，因此也不支持 PROPAGATION_NESTED 传播行为，基于 Java Spring 框架的应用如果使用了 PROPAGATION_NESTED 传播行为，需要在应用端做出调整，将嵌套事务的逻辑移除。

7. 大事务
基于日志的数据库在面对大事务时，需要手动调大可用日志的容量，以避免日志被单一事务占满。
TiDB 中对于事务量有着硬限制，由于 TiDB 分布式两阶段提交的要求，修改数据的大事务可能会出现一些问题。因此，TiDB 对事务大小设置了一些限制以减少这种影响（一行数据是一个键值对，一行索引也是一个键值对，当一张表只有 9 个索引时，每 insert 一行数据会写入 10 个键值对）：

每个事务内的 SQL 数量不超过 5000 条（可配置）
每个键值对不超过 6MB
键值对的总大小不超过 100MB
键值对的总数不超过 300,000

据此，涉及大量数据增删改的事务（如批量的对账事务等），需要进行缩减事务量的改造，最佳实践是将大事务改写为分页 SQL，分段提交，TiDB 中可以利用 order by 配合 limit 的 offset 实现分页功能，写法如下：

update tab set value=’new_value’ where id in (select id from tab order by id limit 0,10000);
commit;
update tab set value=’new_value’ where id in (select id from tab order by id limit 10000,10000);
commit;
update tab set value=’new_value’ where id in (select id from tab order by id limit 20000,10000);
commit;
... ...

8. 读取超时
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 的信息，请参考官网文档： https://pingcap.com/docs-cn/stable/reference/garbage-collection/overview/

9. 事务超时
含 DML 语句的事务，除了受 tikv_gc_life_time 限制之外，还受到另外一个参数 max-txn-time-use 的影响，这个参数位于 tidb-server 的配置文件 tidb.toml 中，用于控制单个事务允许的最大执行时间。该参数的默认值为 590（秒），需要注意必须控制该参数的值小于 tikv_gc_life_time 的值。
形如 insert into t10 select * from t1 的 SQL 语句，即使执行时间没有达到 tikv_gc_life_time 限制，但超过了 max-txn-time-use 的限制，会由于超时而回滚。

10. SQL 执行超时
TiDB 还提供了一个系统变量来限制单条 SQL 语句的执行时间：max_execution_time，它的默认值为 0，表示无限制。max_execution_time 目前对所有类型的 statement 生效，并非只对 SELECT 语句生效。其单位为 ms，但实际精度在 100ms 级别，而非更准确的毫秒级别。

三、自增 ID

1. TiDB 中的自增 ID 分配原理
TiDB 的自增 ID (auto_increment) 只保证自增且唯一，并不保证连续分配。TiDB 目前采用批量分配的方式，所以如果在多台 TiDB 上同时插入数据，分配的自增 ID 会不连续。当多个线程并发往不同的 tidb-server 插入数据的时候，有可能会出现后插入的数据自增 ID 小的情况。此外，TiDB允许给整型类型的列指定 auto_increment，且一个表只允许一个属性为 auto_increment 的列。

2. 自增 ID 设计最佳实践
设置自增 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'

3. TiDB 中的自增 ID 使用方法
如上面所讲，自增 ID 多被设计为主键或唯一索引，用户尽量不要手动为自增 ID 列赋值，手动赋值会导致大量全局最大自增 ID 的频繁更新请求，降低写入性能。
用户在写入数据时，无需指定自增 ID 列，TiDB 会自动为它赋值。
当然，用户也可以指定自增 ID 列写入数据，此时需要将写入的值设置为 NULL，TiDB 会自动为它赋值。

mysql> create table autoid(`auto_inc_id` bigint unsigned not null primary key auto_increment comment 'auto-increment ID', b int);
Query OK, 0 rows affected (0.25 sec)
mysql> insert into autoid(b) values(100);
Query OK, 1 row affected (0.19 sec)
mysql> insert into autoid(b) values(200);
Query OK, 1 row affected (0.03 sec)
mysql> insert into autoid(b) values(300);
Query OK, 1 row affected (0.06 sec)
mysql> insert into autoid values(null,1000);
Query OK, 1 row affected (0.04 sec)
mysql> insert into autoid values(null,2000);
Query OK, 1 row affected (0.03 sec)
mysql> insert into autoid values(null,3000);
Query OK, 1 row affected (0.03 sec)
mysql> select * from autoid;
+-------------+------+
| auto_inc_id | b |
+-------------+------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 4 | 1000 |
| 5 | 2000 |
| 6 | 3000 |
+-------------+------+
6 rows in set (0.01 sec)

4. 人为向自增列写入值后 TiDB 分配自增值时报 “Duplicate entry” 错误的处理方法
在集群中有多个 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 (c) (1)，这条语句中没有指定 id 的值，所以会由 A 分配，当前 A 缓存了 [1, 30000] 这段 ID，所以会分配 1 为自增 ID 的值，并把本地计数器加 1。而此时数据库中已经存在 id 为 1 的数据，最终返回 Duplicated Entry 错误。
处理该问题只需要调大表上的 AUTO_INCREMENT 属性值即可让所有 tidb-server 重新获取一段自增 ID：
确认表上自增值的最大值：show create table t;
修改表上的自增值最大值到一个更大的值：alter table t AUTO_INCREMENT=120000;

5. 高并发的唯一序列号生成方案
TiDB 目前版本（v3.0）还不支持序列（sequence），预计在 v4.0 版本正式提供序列功能。
在高并发序列号生成策略的设计中，即使有 sequence 功能，数据库也可能会由于高并发的 sequence 分配请求而遇到性能瓶颈。
目前业界比较成熟的方案是设计一张序列号生成表，每个序列使用一行数据来控制，这张表需要具有序列名称，序列最大值，序列获取步长（step）等字段，应用程序每次按配置好的步长来获取一批序列号，并同时更新该序列最大值，在应用内存中完成最终的序列号加工及分配。在预期并发变高时，可以通过调大序列获取步长的方式来降低这行记录上的更新并发。
这里需要注意，在 TiDB 中，必须使用 select for update 后再更新序列最大值，以防数据库自动重试事务来保障数据的安全。

四、唯一性约束

1. 主键与唯一索引
和其他 DBMS 一样，TiDB 中的主键和唯一索引都是表中数据的唯一性约束。一些可能的不同点包括：
TiDB 中的主键必须在建表时声明，目前版本（v3.0）还不能为已有的表添加，修改或删除主键。唯一索引没有此限制。
Drop Column 操作不支持删除的列为主键列

2. 外键
TiDB 不支持外键，如果建表语句中包含外键，语法解析器会导入这些外键信息，并存储在 information_schema 中给用户提供参考，外键的级联操作多表数据的功能需要在应用中完成。

3. OGG（Oracle GoldenGate）用户需要注意 INSERT 语句默认只在提交时进行唯一性约束校验
TiDB 采用乐观事务模型，在 COMMIT 阶段会尝试落实所有写操作到存储引擎中，相对于悲观事务模型的数据库，TiDB 的 COMMIT 是很重的。
DML 语句中，UPDATE，DELETE，MERGE / UPSERT（TiDB 暂不支持），INSERT IGNORE / INSERT ON DUPLICATE KEY UPDATE / INSERT IGNORE ON DUPLICATE KEY UPDATE 都涉及对表内数据的读操作，当表上有主键或唯一索引时，SQL 语义上隐含着对于表唯一性约束的校验，因此以上 DML 语句在读取数据后即进行唯一性约束校验，在 TiDB 中也是这样实现的。
DML 语句中只有 INSERT（INSERT IGNORE / INSERT ON DUPLICATE KEY UPDATE / INSERT IGNORE ON DUPLICATE KEY UPDATE 这些特殊的 INSERT 除外）是纯写操作，而当表上有主键或唯一索引时，INSERT 语句也隐含着读取表内对应记录的语义，读不到数据说明这行记录可以写入，而一旦读到数据，就意味着这行数据不满足表内的唯一性约束。为了提高执行效率，TiDB 默认在 INSERT 的执行过程中，不去与表内记录进行比对，只在事务提交时进行校验，这样就省去了一部分读操作，当 INSERT 的记录很多时（如 batch insert），这个性能优势会逐渐凸显出来。当然，这样做的缺点也很明显，就是当一个事务的写入记录数很多时，如果其中的数据和表内已有数据有主键或唯一索引冲突，就只能等到 COMMIT 时才会报错回滚整个事务，另外有些程序可能会抓取 INSERT 语句的返回信息，并以此决定程序后续的执行逻辑，而默认配置下，TiDB 不会在 INSERT 语句中返回主键或唯一索引重复的报错，这些信息都只在 COMMIT 中返回。
综上，TiDB 在默认配置下会出现如下图所示的行为，第一条报错是由于在事务内出现了两条具有相同主键的记录，这个报错说明 TiDB 对于本事务范围内的所有记录是有唯一性校验的；第二调报错是在事务提交时出现的，这个报错说明 TiDB 在默认配置下，只会在提交时把本事务内 INSERT 的记录与表内记录进行比对：

mysql> create table t1 (a int key);
Query OK, 0 rows affected (0.23 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.15 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> commit;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

TiDB 提供一个开关 tidb_constraint_check_in_place 来控制这个行为，默认值为 0，即 INSERT 数据时不做唯一性校验，只在事务提交过程中做校验，如果用户确认自己需要在 INSERT 当场就进行唯一性校验，可以将这个变量的值改为 1，注意尽量对每个应用进行 session 级的设置，以防止影响 batch insert 事务的写入效率。set @@session.tidb_constraint_check_in_place=1;
我们至今只发现 OGG（Oracle GoldenGate）需要在 insert 语句执行当场做一致性校验，因此建议 OGG 用户修改 jdbc 连接串，方法如下：

jdbc:mysql://192.168.1.20:4000/dbname?tidb_constraint_check_in_place=1

五、索引

1. TiDB 中的索引
索引也是数据，也要占用存储空间。和表中的数据一样，TiDB 中表的索引在存储引擎中也被作为 kv 来存储，一行索引是一个 kv 对。例如一张有 10 个索引的表，每插入一行数据的时候，会写入 11 个 kv 对。
TiDB 支持主键索引，唯一索引，也支持二级索引，构成以上索引的可以是单一列，也可以是多个列（复合索引）。
TiDB 目前（v3.0）还不支持反向/双向索引，全文索引，分区表的全局索引。

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

TiDB 中在查询的谓词是 like ‘%...’，like ‘%...%’，not like ‘%...’，not like ‘%...%’，<=> 时，都无法使用索引。*注：目前版本（v3.0）还没有对 <=> 做处理，无法像 is null 一样使用索引。

TiDB 目前（v3.0）对于一张表的查询还不能同时利用到这张表上的两个索引，相关优化还在开发中。

2. 复合索引的设计
由于目前的版本（v3.0）还没有 Index Merge 的功能，因此符合索引的设计尤为重要。
TiDB 中的复合索引形如 key tablekeyname (a,b,c) ，与其他数据库一样，设计复合索引的一般原则是尽可能的把使用频率比较高的字段放在前面。在当前版本（v3.0 及以下的全部版本）使用中需要特别注意，复合索引中前一列的范围查询会中止后续索引列的使用，可以通过下面的案例来理解这个特性。在如下的查询中：select a,b,c from tablename where a<predicate>’<value1>’ and b<predicate>’<value2>’ and c<predicate>’<value3>’;
如果 a 条件的谓词（语句中的 predicate）是 = 或 in，那么在 b 的查询条件上就可以利用到组合索引 (a,b,c) 。例：select a,b,c from tablename where a=1 and b<5 and c=’abc’;
同样的，如果 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’;
如果 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)，同样遵循上面的原则。

六、批量计算场景的写入优化
批量计算的回写操作往往产生写入热点。TiKV 是一个按 range 切分的 KV 系统，KV 的 Key 决定了写入位置在哪个 region，Key 的值取决于两种情况：

1. 当主键为整型（int, bigint, ...）时，Key 就是主键。
2. 其他情况 TiDB 为表创建隐藏列（_tidb_rowid），Key 是该隐藏列。

1. 配置 SHARD_ROW_ID_BITS 拆散写入热点
对于 PK 非整数或没有 PK 的表，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 = 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;

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

七、SQL 语法注意事项

1. 暂不支持 create table as select 语法
目前版本（v3.0）还不支持 create table as select … 语法，需要改写为 create table like … 和 insert into select … 的组合语句。create table like … 支持对等的表结构复制。

2. 使用 full 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

3. 使用 order by 语法保障结果集输出顺序
在 SQL 的语义中，只有使用了 order by 语法才会保障结果集的顺序输出。而单机数据库由于数据都存储在一台服务器上，在不进行数据重组时，返回结果往往是稳定的，有些数据库还会按照主键或索引的顺序进行结果集的输出。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)