Vitess doesn’t just send your SQL to a MySQL instance; it figures out which one should handle it, and that decision is a lot more sophisticated than you might think.
Let’s see Vitess route a query. Imagine we have a users table sharded by user_id.
SELECT * FROM users WHERE user_id = 123;
When VTGate receives this, it needs to know which shard holds user_id = 123. It consults its internal routing rules. If user_id is the primary vindex, Vitess uses the hash vindex for routing. The hash vindex maps 123 to a specific shard, say shard-0000000000. VTGate then sends this query only to the MySQL instance serving shard-0000000000.
If the query was SELECT * FROM users WHERE name = 'Alice', and name isn’t a primary vindex, Vitess might have to scatter the query. It would look up 'Alice' in its secondary vindex (if one exists) or, failing that, send the query to all shards, collecting the results and de-duplicating them. This is the "scatter-gather" pattern.
The core of Vitess’s query planning is the VSchema. This is a JSON configuration that tells Vitess about your schemas, how tables are sharded, and which vindexes are available.
Here’s a snippet of a VSchema for our users table:
{
"sharded_tables": {
"users": {
"column_vindexes": [
{
"column": "user_id",
"name": "hash"
}
]
}
},
"vindexes": {
"hash": {
"type": "hash",
"params": {
"num_shards": "1024"
}
}
}
}
This VSchema tells Vitess that users is sharded, the user_id column is associated with the hash vindex, and the hash vindex distributes data across 1024 shards.
When a query arrives, VTGate first parses it and identifies the tables involved. For each table, it checks the VSchema to see if it’s sharded. If it is, Vitess looks at the WHERE clause. It tries to find a column in the WHERE clause that is also listed as a column_vindex in the VSchema.
If a direct vindex lookup is possible (like WHERE user_id = 123), Vitess uses the vindex to determine the target shard(s). This is a "direct" route. If no direct vindex column is present, or if the vindex is non-deterministic (like a full text search), Vitess might resort to a "scatter" query, sending the query to all relevant shards.
The real magic happens when you have multiple vindexes or complex queries. Vitess can also plan queries that involve joins across different shards. If tables are joined on their primary vindex columns, Vitess can route parts of the query to individual shards and then perform the join locally on the MySQL instances. If the join is on non-vindexed columns, it might require a scatter-gather operation where results from multiple shards are aggregated and joined by VTGate.
Vitess also optimizes queries by pushing down operations to the MySQL servers whenever possible. For example, if a query involves a WHERE clause that can be satisfied by a vindex lookup, Vitess will execute that lookup first, identify the target shard, and then send the filtered query to that specific shard. This minimizes the amount of data that needs to be transferred.
One aspect that often surprises people is how Vitess handles INSERT statements that don’t specify the sharding key. If you have INSERT INTO users (name) VALUES ('Bob'), and user_id is the sharding key, Vitess cannot determine the shard. In such cases, Vitess will perform a "scatter-insert," sending the insert to all shards. This is inefficient and should be avoided by always providing the sharding key or using a sequence/auto-increment column managed by Vitess for sharded tables.
This planning process ensures that Vitess routes queries efficiently, minimizing network hops and processing load by leveraging the VSchema and vindexes to determine the most appropriate MySQL instances.
The next step in understanding query execution is how Vitess handles transactions across shards.