Choosing a Shard Key
There are two major factors to take into account when defining a shard key:
Using a column or set of columns unique enough to minimize skew.
Sharding on columns which you expect to filter or join on often.
This allows the optimizer to minimize network traffic during the execution of the query (see Query Execution for more details).
Let’s say we have the following table:
CREATE TABLE events(event_id INT, user_id INT, time TIMESTAMP);
If we aren’t sure yet what our workload will look like, just using the first create table statement without specifying a shard key will shard rows uniformly.
Let’s say we have this query which we would like to be extremely performant:
SELECT COUNT(DISTINCT first.user_id)FROM events AS first JOIN events AS secondWHERE first.user_id = second.user_idAND TIMESTAMPDIFF(MINUTE, first.time, second.time) < 20;
In this case, we’ll want to modify our table schema to shard on
CREATE TABLE events(event_id INT, user_id INT, time TIMESTAMP, SHARD KEY(user_id));
Last modified: January 17, 2023