Global Secondary Index feature recommendations

[sunkai-cai]

全局二级索引（Global Secondary Index，GSI）是分布式数据库的一个重要特性，通过全局二级索引，能够支持 减少全路由查询、实现全局唯一约束、按需增加拆分维度（垂直分片）等特性。

全局二级索引的本质是空间换时间，通过索引表，加速查询的速度。为此，我们需要实现以下几个步骤：

1. 支持在逻辑库上，创建二级索引表，并与逻辑表进行绑定。
2. 当存在二级索引，执行DML语句时，开启分布式事务，按索引规则，写入索引表。
3. 当存在二级索引，执行DQL语句时，在分库分表路由，通过优化器选择路由（分片路由、二级索引路由、全路由）。其中，二级索引路由区分是否回表。
	a. 回表路由, 需在SQL执行器返回后，再提交到“SQL改写”进行回表查询操作。
	b. 不回表路由, 直接下发到SQL执行器完成后进行合并返回。
4. 当对GSI 执行 表结构修改（schema change）时，需重新同步 GSI 与 主表，实现一致性。

Global Secondary Index (GSI) is an important feature of distributed databases, through which it can support a variety of features, e.g the reduction of full route , the implementation of global unique constraints, and the increase of split dimensions (vertical sharding) ,etc.

The essence of global secondary indexes is to exchange space for time, speed up queries by the index table. To do this, we need to implement the following steps:

1. On the logical database, create a secondary index table and bind it to the logical table.
2. When a secondary index exists and a DML statement  executing, start distributed transaction and the index table is written according .
3. When a secondary index exists and a DQL statement  executing, In SQL Route, select routes (shard routes, secondary index routes, and full routes) through the new optimizer. Secondary index routes distinguish whether to  TABLE ACCESS BY INDEX ROWID.
	a.  When need table access by rowid, after the SQL executor returns, it must be submitted to SQL Rewrite for table  access by rowid.
	b. When  access by rowid might not occur, directly deliver to the SQL executor for merger and return.
4. When performing schema changes on GSI, resynchronize GSI with the primary table to achieve consistency.