Spanner单表查询同时使用多个FORCE_INDEX的实现及SQLAlchemy方案

Spanner 不支持在同一个FROM子句中为单表指定多个FORCE_INDEX提示，你可以通过拆分查询后取交集的方式实现需求，共有两种常用实现：

方案1：INTERSECT 取交集 #

适合两个过滤条件返回的结果集规模偏小的场景，开销更低：

WITH prop1_match AS (
  -- 强制走prop1_index过滤prop1条件，仅返回主键uid
  SELECT uid FROM mycolumn@{FORCE_INDEX=prop1_index} 
  WHERE prop1 BETWEEN 100 AND 101
),
prop2_match AS (
  -- 强制走prop2_index过滤prop2条件，仅返回主键uid
  SELECT uid FROM mycolumn@{FORCE_INDEX=prop2_index} 
  WHERE prop2 BETWEEN 50 AND 1000
),
matched_uids AS (
  -- 取两个结果集的交集，得到同时满足两个条件的uid
  SELECT uid FROM prop1_match
  INTERSECT DISTINCT
  SELECT uid FROM prop2_match
)
-- 回表查询需要的完整字段
SELECT uid, prop1, prop2 
FROM mycolumn 
WHERE uid IN (SELECT uid FROM matched_uids)

如果prop1_index已经包含prop2字段、prop2_index已经包含prop1字段，你可以省略最后的回表步骤，直接在INTERSECT后返回所需字段。

方案2：INNER JOIN 关联子查询 #

适合主键关联效率更高的场景，如果两个索引为覆盖索引可以直接省去回表：

-- 两个别名表分别强制走不同索引，通过主键关联
SELECT c.uid, c.prop1, c.prop2
FROM mycolumn@{FORCE_INDEX=prop1_index} AS p1
INNER JOIN mycolumn@{FORCE_INDEX=prop2_index} AS p2
  ON p1.uid = p2.uid
-- 主键回表拉取完整字段，若索引覆盖可删除该关联
INNER JOIN mycolumn AS c
  ON p1.uid = c.uid
WHERE 
  p1.prop1 BETWEEN 100 AND 101
  AND p2.prop2 BETWEEN 50 AND 1000

注意：如果业务允许新建索引，优先创建(prop1, prop2)联合索引，性能远高于上述拆分查询的方案。

以下代码基于sqlalchemy-spanner方言实现：

INTERSECT 方案代码 #

from sqlalchemy import select

# 假设你已经提前定义好了MyColumn表模型，包含uid、prop1、prop2字段
# 构造prop1过滤子查询，指定强制走prop1_index
prop1_subq = select(MyColumn.uid).with_hint(
    MyColumn,
    "FORCE_INDEX=prop1_index",
    dialect_name="spanner"
).where(MyColumn.prop1.between(100, 101)).subquery()

# 构造prop2过滤子查询，指定强制走prop2_index
prop2_subq = select(MyColumn.uid).with_hint(
    MyColumn,
    "FORCE_INDEX=prop2_index",
    dialect_name="spanner"
).where(MyColumn.prop2.between(50, 1000)).subquery()

# 构造交集子查询
matched_uid_subq = select(prop1_subq.c.uid).intersect(select(prop2_subq.c.uid)).subquery()

# 最终查询回表取数
final_query = select(MyColumn.uid, MyColumn.prop1, MyColumn.prop2).where(
    MyColumn.uid.in_(select(matched_uid_subq.c.uid))
)

# 执行查询
results = session.execute(final_query).all()

INNER JOIN 方案代码 #

from sqlalchemy import select, aliased

p1 = aliased(MyColumn)
p2 = aliased(MyColumn)

final_query = select(MyColumn.uid, MyColumn.prop1, MyColumn.prop2).select_from(
    p1.join(p2, p1.uid == p2.uid)
    .join(MyColumn, p1.uid == MyColumn.uid)
).with_hint(
    p1, "FORCE_INDEX=prop1_index", dialect_name="spanner"
).with_hint(
    p2, "FORCE_INDEX=prop2_index", dialect_name="spanner"
).where(
    p1.prop1.between(100, 101),
    p2.prop2.between(50, 1000)
)

# 执行查询
results = session.execute(final_query).all()

dialect_name="spanner"参数可以保证hint仅在Spanner方言下生效，不会影响其他数据库的兼容性。

内容的提问来源于stack exchange，提问作者hadim