分库分表后索引如何设计？在全局索引和二级索引

日期：2023-10-07 21:37

分布式数据库架构下，索引的设计也需要调整，否则无法充分发挥分布式架构线性扩展优势。今天我们来聊聊“分布式数据库架构中如何正确设计索引？”

主键选择

对于主键来说，它在所有分片中必须是唯一的。它本质上是一个全球唯一索引。如果用大多数同学喜欢的自增方式作为主键，你会发现存在很大的问题。

因为自增无法获取插入前的值，所以必须先填充NULL值，然后通过函数last_insert_id()获取自增值。因此，如果主键在每个分片上都采用自增的方式实现，则不同分片上可能存在相同的自增值。

以电商订单表orders为例，表结构如下（分片键为o_custkey，表主键为o_orderkey）：

创建 表 `订单` (
  `O_ORDERKEY`  int 不是 NULL 自动增量,
  `O_CUSTKEY` int NOT NULL,
  ` O_ORDERSTATUS` char(1) NOT NULL,
  `O_TOTALPRICE` 十进制(15,2) NOT NULL,
  `O_ORDERDATE` 日期 不是 NULL,
  `O_ORDERPRIORITY` char(15)NOT NULL,
  `O_CLERK` char(15) NOT NULL,
  `O_SHIPPRIORITY` int NOT NULL,
  `O_COMMENT` varchar(79)NOT NULL,
  主KEY（`O_ORDERKEY`）,   KEY（`O_CUSTKEY` )
  ......
) 引擎=InnoDB

如果把o_orderkey设计成上图所示的自增，那么很可能o_orderkey 同为1个记录在不同的分片中出现，如下图所示：

因此，在分布式数据库架构中，尽量不要使用自增作为表的主键：自增性能较差，安全性较低，不适合分布式架构。至此，我们已经解释了“主键自增”的所有问题，那么主键该如何设计呢？仍然使用全局唯一键作为主键，如MySQL自动生成有序UUID；由企业（例如号码发行者）生成的全球唯一密钥；或者开源的UUID生成算法，比如雪花算法（但是存在时间回溯的问题）。总之，以有序全局唯一性代替自增，是这个时代数据库主键的主流设计标准。如果你还在使用自增作为主键，可能意味着你已经落后于时代的发展了。。索引设计SQL查询可以通过分片键路由到指定分片，但在实际生产环境中，业务必须通过其他索引来访问表。还是以之前的桌单为例。如果业务还需要根据o_orderkey字段进行查询，例如查询订单ID为1的订单详情：SELECT * FROM 订单 WHERE o_orderkey = 1我们可以看到，由于分片规则不是分片键，所以需要查询4个分片才能得到最终结果。如果下面有1000个分片，那么这样的SQL需要执行1000次。这时候的表现就比较差了。但是，我们知道o_orderkey是主键，返回的记录应该只有一条，即o_orderkey只存在于一个分片中。这时候你可以有以下两种设计：

同样的数据，表orders使用o_orderkey作为分片key，然后实现分库分表；

向索引添加额外的分片键信息。

两种设计的本质都是通过冗余来达到以空间换时间的效果。否则，需要扫描所有分片。当分片数据很多时，效率会变得极差。第一种方法是使表变得冗余。对于o_orderkey的查询，只需要直接在o_orderkey = 1的分片中查询即可，效率最高，但设计的缺点是冗余量数据太大。所以，改进的方法之一就是实现一个索引表，只包含o_orderkey和分片键o_custkey，如：创建表 idx_orderkey_custkey ( o_orderkey INT o_custkey INT、主 KEY (o_orderkey ) )如果这个索引表很大，也可以分库分表，但是它的分片键是o_orderkey。如果再根据字段 o_orderkey 进行查询，就可以实现类似二级索引的回表：先查询索引表，获取该记录对应的分片键 o_custkey 的值o_orderkey = 1，然后根据o_custkey进行查询。最后找到想要的数据，如：SELECT * FROM 订单 WHERE o_orderkey = 1 => # 步骤 1 选择 o_custkey FROM idx_orderkey_custkey WHERE o_orderkey = 1 # 步骤 2 SELECT * FROM 订单哪里 o_custkey = ? AND o_orderkey = 1本示例是将一条SQL语句拆分为2条SQL语句，但是拆分后的两条SQL语句可以通过分片键进行查询，这样保证了查询操作只需要在单个分片中完成。无论有多少个分片，只需要查询2个分片的信息，因此可以大大提高SQL的查询性能。通过索引表的方式，虽然存储容量比冗余全表小很多，但基于另一个分片键存储数据还是不够优雅。所以，最优化的设计不是创建索引表，而是将分片键信息保存在想要查询的列中，这样就可以通过查询的列直接知道分片信息。如果我们将订单表orders的主键设计为字符串，则该字符串的最后一部分包含分片键的信息，例如：o_orderkey = 字符串(o_orderkey + o_custkey) 那么如果根据o_orderkey查询：选择 * 来自订单地点 o_orderkey = '1000-1';由于o_orderkey字段的设计直接包含了分片key信息，所以我们可以直接知道这个订单在分片1中，直接查询分片1即可。同样，插入时，由于插入时可以知道o_custkey对应的值，所以只需要在业务层拼接一次字符，然后插入数据库即可。这种实现方式比冗余表和索引表的设计效率更高。查询可以提前知道数据对应的分片信息，只需一次查询即可得到想要的结果。这种实现的缺点是主键值会变大，存储也会相应变大。但只要主键值是有序的，插入的性能就不会差。但通过将分片信息保存在主键值中，可以大大提高后续的查询效率。这种以空间换时间的设计总体来说是非常值得的。当然，我们这里所说的设计都是针对独特的索引设计。如果是非唯一二级索引查询，遗憾的是仍然需要扫描所有分片才能得到最终结果，如：选择 * 来自订单在哪里 o_orderate >= ? o_orderdate < ?所以我再次提醒大家，分布式数据库架构设计的要求是：大部分业务请求能够根据分片键定位到一个分片上。如果大部分业务请求需要扫描所有分片信息才能得到最终结果，那么就不适合分布式架构的改造或设计。最后我们回顾一下淘宝用户订单的设计：