分布式SQL数据库中部分索引的好处

在优锐课的学习分享中，探讨了分布式SQL数据库中部分索引的优势，并探讨了性能测试，结果等。实用干货非常多，分享给大家参考学习。

站在用户的角度思考问题，与客户深入沟通，找到贾汪网站设计与贾汪网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：成都网站建设、网站建设、企业官网、英文网站、手机端网站、网站推广、域名注册、网络空间、企业邮箱。业务覆盖贾汪地区。

如果使用局部索引而不是常规索引，则在可为空的列上（其中只有一小部分行的该列不具有空值），然后可以大大缩短插入，更新和删除的响应时间。 另外，单行选择的响应时间也缩短了一点。 这篇文章解释了什么是部分索引，显示了如何创建部分索引，描述了要求使用部分索引的规范用例，描述了一些简单的性能测试，并显示了结果证明有理由建议使用部分索引。 适当的用例。

**介绍**

有时，业务分析会确定实体类型具有可选属性，该属性在典型情况下仍未设置，需要标识将可选属性设置为某个值的实体出现，并且从不要求 识别未设置的事件。 现在出现了一个典型的例子，一些办公设施在一个很小的地下室里提供有限的自行车存放空间，并为很少有人利用这一点提供了编号的钥匙。 当看门人交错了一把钥匙时，确实需要找出“谁被分配了42号钥匙？”，但是不需要识别不使用自行车存放处的人员。

这样的实体类型将被实现为具有可为空的列以表示可选属性的表。并且该列将需要辅助索引来支持快速执行查询，该查询通过此列的非非空值来标识行。

如果你有一个同时包含“开票”和“未开票”订单的表，则会出现另一种情况（与可选属性的情况稍有不同），其中“未开票”的订单只占总表的一小部分，但通常在OLTP场景中唯一要选择的对象-正是这样以便可以对其进行处理并将其设置为“开票”。使用限制定义了部分索引，以便仅对表的某些行进行索引。在当前情况下，在第一种情况下，限制将指定仅对该列具有不为空值的行建立索引。或者，在第二种情况下，将仅索引“未开票”的行。

这将是有益的，因为部分索引比常规索引要小得多。最重要的是，在插入或删除行时，在典型情况下将避免索引的维护成本。

**“创建索引”语法**

假设我们创建了一个表：

create table t(k int primary key, v int);

（我将在本文中以尾部分号显示所有SQL语句，因为它们将以ysqlsh命令形式显示。）请注意，t.v列是可为空的。

创建常规二级索引：

create index t_v on t(v);

并创建了部分二级索引：

create index t_v on t(v) where v is not null;

where子句限制可以是任何仅提及要索引表中的列的单行表达式。 例如，``其中v不为null且v！= 0''。 就这么简单！

性能测试

我用一百万行填充了表t，其中只有百分之十的行的t.v不为空。 这是用PostgreSQL语法编写的SQL，其中％运算符用于表示取模函数。

insert into t(k, v)
select
a.v,
case a.v % 10
  when 0 then a.v
  else        null
end
from
(select generate_series(1, 1000000) as v) as a;

测试1：创建索引

首先，我首先在t.v上创建了一个常规索引，对``创建索引''操作进行计时，然后运行计时脚本。 （此脚本实现了测试＃2至＃4。）然后我删除了索引并在t.v上创建了部分索引，也对该操作进行了计时，并再次运行了计时脚本.

测试2：单键选择

\timing on
call select_not_null_rows(100000);
\timing off

过程select_not_null_rows（）通过运行实现的“ for循环”来模拟OLTP单行选择语句：

for j in 1..num_rows loop
  select t.k into the_k from t where t.v = the_v;
  the_v := the_v + sparseness_step;
end loop;

当然，没有人会写像这样的真实程序-尤其是因为在没有唯一约束ont.v的情况下，select语句可能会返回多个行！ 但是这种幼稚的方法足以用于计时目的。 sparseness_step的值设置为10，反映了我的选择，表示为：

case a.v % 10

在上面显示的插入语句中，我曾用来填充表t。在程序运行时首次遇到该问题时，该程序反复发出的选择语句是在幕后准备的，因此SQL执行确实遵循最佳的准备- 执行范例

测试3：单行插入

\timing on
call insert_rows(100000);
\timing off

过程insert_rows（）通过运行以下实现的“ for循环”来模拟OLTP单行插入语句：

for j in 1..num_rows loop
  insert into t(k, v) values(1000000 + j, null);
  commit;
end loop;

没有人会在实际程序中提交每一行。 但这是模拟单行OLTP的设备。 在这里，SQL执行也遵循最佳的执行准备范式。

测试4：批量删除

\timing on
delete from t where k > 1000000;
\timing off

我在这里没有使用prepare-execute范例，因为SQL编译和计划所需的时间只是删除十万行所需时间的一小部分。

测试环境

我在两个环境中记录了时间。

· 首先，我使用本地MacBook运行单节点YugabyteDB群集（版本2.0.1），复制因子（RF）为1。 ysqlsh客户端在同一本地计算机上运行。

· 然后，我使用委托在AWS上的，RF = 3的实际三节点集群来运行它们。 我将所有三个节点部署在相同的``us-west-2''（俄勒冈）可用性区域中。 节点的类型为“ c5.large”，每个节点都有足够的存储空间来满足我的测试要求。 我在同一可用区中的另一个节点上运行了ysqlsh客户端。

结果

我记录了在两种测试条件（常规索引和部分索引）和两种测试环境（“本地”和“云”）下上述测试的经过时间。对于每个测量对，常规索引时间都大于部分索引时间。可以预料，“云”环境中的时间绝对值大于“本地”环境中的时间绝对值。请注意，使用Raft共识算法和分布式事务，在RF = 3的三节点群集上的SQL操作预期比单个节点要慢，RF = 1且没有节点间通信且代码路径较短。如此有弹性和可扩展的系统有很多有据可查的好处，而这些好处可能会超过成本。

我通过将它们表示为速比来标准化结果。例如，如果使用常规索引进行的测试花费60秒，而使用部分索引进行的测试花费20秒，则部分索引速度是常规索引速度的3倍。在我的测量精度范围内，两个测试环境中的速度比是相同的。

列出测试的部分索引与常规索引速度比

当然，你的结果可能会有所不同。 索引列中的非零值稀疏密度将使速度比更大，而稀疏密度较小将使它们变得更小。

结论

这篇文章展示了如何在适当的用例中使用部分索引，可以减少二级索引维护的成本，从而加快有关表的插入，更新和删除操作。 通过使用部分索引来减少二级索引维护操作的数量在分布式SQL数据库（例如YugabyteDB）中特别有利。 这是因为在索引维护期间，看似仅插入，更新或删除单个行的事务会自动成为涉及主行和辅助索引行的分布式事务。

当主行和辅助索引行存在于群集的潜在两个不同节点上的两个不同分片上时，这种分布式事务可以是多分片事务。 可以想象，多分片事务必然比单分片事务昂贵。

文章写到这里，如有不足之处，欢迎补充评论！