Vitess 9.0.0等价表（Table Equivalence）路由配置冲突及本地连接优化咨询

First, let’s break down why you’re hitting those issues:

• The "table %v has more than one target" error occurs because your routing rule maps a single unqualified table name (product) to two distinct tables in separate keyspaces. VTGate can’t automatically resolve which target to use when queries don’t specify a keyspace, leading to the conflict.
• Scattered joins persist because Vitess wasn’t informed the two product tables are equivalent—so it defaults to querying the unsharded keyspace directly instead of using the local reference table in the sharded keyspace.

Here’s how to fix this step-by-step:

1. Establish Table Equivalence #

First, explicitly tell Vitess that the reference table in your sharded keyspace is a copy of the unsharded table. Use the vtctlclient command to register this equivalence:

vtctlclient AddTableEquivalence --keyspace sharded_keyspace --table product unsharded_keyspace.product

This marks sharded_keyspace.product (your replicated reference table) as equivalent to unsharded_keyspace.product. Vitess will use this metadata to optimize joins by leveraging the local reference table instead of reaching out to the unsharded keyspace.

2. Fix Routing Rules to Resolve Conflicts #

Instead of mapping product to both tables, adjust your routing rule to align with your app’s primary query pattern:

• If most of your app’s product queries are part of joins with sharded tables (the use case where you want local joins), set the default route to the sharded reference table:

  { "from_table": "product", "to_tables": ["sharded_keyspace.product"] }
  

• If you still need to access the unsharded product table directly in some queries:
  • Either modify those queries to explicitly specify the keyspace (e.g., SELECT * FROM unsharded_keyspace.product WHERE ...), or
  • Add a conditional routing rule (supported in Vitess 9.0.0) to direct specific queries to the unsharded keyspace. For example:

    {
      "from_table": "product",
      "to_tables": ["unsharded_keyspace.product"],
      "match": { "sql": "^SELECT .* FROM product WHERE id = [0-9]+$" }
    }
    

    (Adjust the regex in the match clause to match your specific unsharded query patterns.)

3. Verify Local Joins Are Working #

Run a sample join query (e.g., joining a sharded table like orders with product) and use EXPLAIN in VTGate to check the execution plan. You should see that the join is processed locally on each shard, rather than using a scattered join to the unsharded keyspace.

Additional Tips #

• Ensure your Materialize workflow keeps sharded_keyspace.product in sync with unsharded_keyspace.product—out-of-sync data will lead to incorrect query results.
• If you need to remove the table equivalence later, use:

  vtctlclient RemoveTableEquivalence --keyspace sharded_keyspace --table product unsharded_keyspace.product
  

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