更自由地建立次要索引

Question

有一个表t_bl具有以下字段(id，a，b)。

select a,b from t_bl where a = "XXX";

在这种情况下，b字段i不被用作检索，的基础，而是需要出现在检索结果中。

然后，有以下两种指标建设方案。

1. Create字段的索引()

Advantage：使索引的每个节点减少存储b字段的空间开销。

返回查询结果的Disadvantage：要求通过辅助索引的主键id将表返回到聚集索引，以查询b字段的值，这会影响查询performance.。

1. Create a a，b字段的联合索引

Advantage：覆盖索引可以减少返回表，提高查询效率。

Disadvantage：使辅助索引中的每个非节点都增加了存储b字段的不必要的空间开销(因为b字段不是检索的基础)。

那么，为什么不提供一种机制，使用户能够创建一个非叶节点和叶节点之间不对称的辅助索引呢？

例如，在本例中，用户更好的选择是创建一个非叶节点来存储a字段，而叶节点存储两个字段a和b的的索引。

Answer 1

SQL数据库的某些实现与您所描述的完全一样，只向叶节点添加列，因此它在非叶节点中不占用空间，但可以用于覆盖索引。

实现此功能的产品的一个示例是Microsoft，它支持语法，允许您在辅助索引中为INCLUDE()定义可选的非键列。请参阅https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-ver16

但是，目前还没有实现InnoDB来做到这一点。据我所知，它没有理由不能这样做，但他们没有实现它。我想其他功能是更重要的。

就其价值而言，SQL标准不包括任何有关索引的内容，因此每个供应商都将其索引功能作为标准的扩展来实现。因此，它们可以根据自己的优先次序自由地执行索引功能。

Answer 2

只需做一个“复合”“覆盖”指数：

INDEX(a, b)

这个特定的查询将运行得更快，因为它不需要在索引的BTree和数据的BTree之间反弹。

在一些类似的情况下，我建议

PRIMARY KEY(a, id),  -- all columns are efficiently accessed via a=..
INDEX(id)   -- to keep AUTO_INCREMENT happy

至于第二个缺点--它太小了。我喜欢经验法则，MySQL的BTrees有大约100的粉丝。也就是说，每个节点都有100个子节点。由此得出的结论是，非叶节点仅占整个磁盘空间的1%。(这可能是开发人员说“让我们麻烦地实现INCLUDE”的关键论点。)