Google Cloud Spanner是否支持索引交叉点/组合/合并? [英] Does Google Cloud Spanner support Index Intersection/Combination/Merge?

问题描述

有关功能的说明:

Postgres(索引组合)，MySQL(索引合并)& MongoDB(索引交集)具有一项功能，其中当在 where 子句中具有多个列的给定查询中找不到多列索引时，DB将使用多个单列索引(索引).这是Postgres的文档中有关此功能的内容- https://www .postgresql.org/docs/8.3/indexes-bitmap-scans.html

Postgres (Index Combination), MySQL (Index Merge) & MongoDB (Index Intersection) have a functionality, where the DB uses multiple single-column indices (indexes) when there is no multi-column index found for a given query that has multiple columns in the where clause. Here is what Postgres' documentation speaks about this feature - https://www.postgresql.org/docs/8.3/indexes-bitmap-scans.html

链接摘录

从版本8.1开始，PostgreSQL可以合并 多个索引(包括对同一索引的多次使用)来处理 单索引扫描无法实现的情况.系统可以 在多个索引扫描中形成与"和或"条件.例如，一个 像WHERE x = 42 OR x = 47 OR x = 53 OR x = 99之类的查询可能被破坏 分为对x的索引的四次单独扫描，每次扫描都使用以下方法之一 查询子句.然后将这些扫描的结果进行或"运算 产生结果.另一个例子是，如果我们有单独的 x和y上的索引，一种查询的可能实现，例如WHERE x = 5 AND y = 6是将每个索引与适当的查询子句一起使用 然后将索引结果与AND一起确定结果行.

Beginning in release 8.1, PostgreSQL has the ability to combine multiple indexes (including multiple uses of the same index) to handle cases that cannot be implemented by single index scans. The system can form AND and OR conditions across several index scans. For example, a query like WHERE x = 42 OR x = 47 OR x = 53 OR x = 99 could be broken down into four separate scans of an index on x, each scan using one of the query clauses. The results of these scans are then ORed together to produce the result. Another example is that if we have separate indexes on x and y, one possible implementation of a query like WHERE x = 5 AND y = 6 is to use each index with the appropriate query clause and then AND together the index results to identify the result rows.

我的用例:

我想构建一个UI，用户可以在其中使用表中的多个字段(当前和正在增长的30多个字段)搜索(过滤)实体.需要在UI&中显示已过滤实体的数量.用户对过滤器进行的每次更新都会刷新.因此，隐含的是它需要快速(最好＜1s).为所有可能的组合创建多个列索引是不可行的，即使这样做也可能无效.

I would like to build a UI where a user can search (filter) for an entity using multiple fields (30+ fields currently and growing) in the table. The number of filtered entities will need to be shown in the UI & refreshed on each update the user makes to the filter. So, it is implicit that it needs to be fast (preferably < 1s). Creating multiple column indices for all possible combinations is not feasible and might be inefficent even if it is done.

以下是我通过运行一些查询观察到的结果.

Following is what I have observed by running a few queries.

Case 1:
select count(*) from TableName@{FORCE_INDEX=_BASE_TABLE} where stringColumn = 'str1';
Table Scan: TableName (full scan: true)    ~11.72s


Case 2:
select count(*) from TableName where stringColumn = 'str1';
Index Scan: IndexForStringColumn    1.55s


Case 3:
select count(*) from TableName where ts > '2019-01-01';
Index Scan: IndexForTS    450902    1    985.66 ms


Case 4:
select count(*) from TableName where stringColumn = 'str1' and ts > '2019-01-01';
Index Scan: IndexForTS    450903    1    1.07 s

• 情况1至3.与预期的一样.情况1没有使用任何索引，因此 TableScan持续11.72秒.
• 情况4是异常情况.说只用了 IndexForTS.但是运行时间似乎要低得多(1.07s).看起来像 这也使用了IndexForStringColumn.
• Case 1 to 3. As expected. Case 1 did not use any index and hence a TableScan for 11.72s.
• Case 4 is the anomaly. It says it only used IndexForTS. But the runtime seems to be far lower (1.07s). Looks like this used IndexForStringColumn as well.

问题:

1. Google Cloud Spanner是否支持针对单个查询使用多个单列索引的功能?当我尝试在Cloud Spanner中运行一些基准测试时，它看起来像受支持，但没有官方文档.
2. 如果不支持，是否还有其他方法可以使用Google Cloud spanner构建此功能?

提前感谢您的帮助！

推荐答案

1. 不幸的是，索引交集和联合在积压中.云扳手将选择一个索引(如果适用)，但范围仅限于单个索引.如果您有很大的合词，则将使用最有选择性的单列索引.

1. Unfortunately, index intersection and union are in the backlog. Cloud spanner will choose an index if applicable but the scope is limited to a single index. If you have a big conjunct, the most selective single column index will be used.

您始终可以通过重写SQL语句来设计索引交集和并集.例如，

You can always craft index intersection and union by rewriting your SQL statement. For example,

从* = 1且y = 1的位置选择*;

SELECT * FROM A WHERE x = 1 AND y = 1;

可以改写为

SELECT * FROM WHERE键IN(((WHERE x = 1的SELECT键)INTERSECT(Y FROM A y的SELECT键= 1));

SELECT * FROM A WHERE key IN ((SELECT key FROM A WHERE x = 1) INTERSECT (SELECT key FROM A FROM y = 1));

类似地，

从* = 1或y = 1的位置选择*;

SELECT * FROM A WHERE x = 1 OR y = 1;

可以改写为

SELECT * FROM WHERE键输入((UNION的SELECT键(来自x = 1的选择键)UNION(y键等于1的FROM的选择键))； -如果不希望满足任何一个谓词的大量行，则可以添加ALL.

SELECT * FROM A WHERE key IN ((SELECT key FROM A WHERE x = 1) UNION (SELECT key FROM A FROM y = 1)); -- You may add ALL if you do not expect large number of rows satisfying either predicate.

希望获得帮助.

这篇关于Google Cloud Spanner是否支持索引交叉点/组合/合并?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！