How to choose a shard key

A shard key is a column that defines horizontal sharding rules. PolarDB-X 1.0 applies a sharding function to each row's shard key value and routes that row to the corresponding ApsaraDB RDS for MySQL instance.

Choosing the right shard key determines both how evenly data distributes across shards and how efficiently your core queries route to the right shard without broadcasting to all shards.

How it works

When you submit a query, PolarDB-X 1.0 evaluates whether the query includes the shard key:

Shard key present: The query routes directly to the shard that holds the matching data.
Shard key absent: The query fans out to all shards and merges the results (a scatter-gather query). Scatter-gather queries are less efficient and do not scale linearly as you add more shards.

A good shard key keeps most of your core SQL operations on a single shard.

Choose a shard key

Start with your business entity

The primary principle: identify the central entity your application is built around, then use that entity's ID field as the shard key.

Most applications have a clear dominant entity—the object that appears in most, core, or concurrent SQL operations. Once you identify it, most of your core SQL operations will naturally include its ID, enabling precise shard routing.

The following table shows common application types and their natural shard keys:

Application type	Business entity	Shard key
User-oriented internet applications	User	user ID
Seller-oriented e-commerce applications	Seller	seller ID
Gaming applications	Gamer	gamer ID
Online Internet of Vehicles (IoV) applications	Vehicle	vehicle ID
Online tax applications	Taxpayer	taxpayer ID

Note Do not use a column that allows null values as a shard key.

Example: E-commerce order table

For a seller-oriented e-commerce application, sellers are the central entity. Use sellerId as the shard key so that all queries for a given seller's orders route to a single shard.

Original table:

CREATE TABLE sample_order (
  id INT(11) NOT NULL,
  sellerId INT(11) NOT NULL,
  trade_id INT(11) NOT NULL,
  buyer_id INT(11) NOT NULL,
  buyer_nick VARCHAR(64) DEFAULT NULL,
  PRIMARY KEY (id)
)

Sharded table using sellerId as the shard key:

CREATE TABLE sample_order (
  id INT(11) NOT NULL,
  sellerId INT(11) NOT NULL,
  trade_id INT(11) NOT NULL,
  buyer_id INT(11) NOT NULL,
  buyer_nick VARCHAR(64) DEFAULT NULL,
  PRIMARY KEY (id)
) DBPARTITION BY HASH(sellerId)

When no clear entity exists

In traditional enterprise applications, the dominant entity is not always obvious. Use the following approaches:

Base the shard key on the most common access pattern

Identify the query your application runs most frequently. Choose the field that appears in that query's WHERE clause as the shard key. This keeps the most performance-critical queries on a single shard.

Choose a field whose values distribute evenly across your dataset. Uneven distribution concentrates data on a small number of shards, creating hotspots that limit horizontal scaling.

PolarDB-X 1.0 supports global secondary indexes to maintain strong consistency when your primary query patterns do not align with the shard key. Combined with parallel queries, global secondary indexes improve SQL concurrency and reduce response time.

Use string or date/time fields for log-style data

For workloads such as log retrieval, use a string data type field or a date and time data type field as the shard key. Note that the fields of the string data type store numeric values. This approach partitions data by time range, which matches the access pattern of most log queries.

Example: User operation log table

For a log system, use both userId and actionDate to partition the table—sharding by user ID at the database level and by week at the table level:

Original table:

CREATE TABLE user_log (
  userId INT(11) NOT NULL,
  name VARCHAR(64) NOT NULL,
  operation VARCHAR(128) DEFAULT NULL,
  actionDate DATE DEFAULT NULL
)

Sharded table using userId for database partitioning and actionDate for weekly table partitioning:

CREATE TABLE user_log (
  userId INT(11) NOT NULL,
  name VARCHAR(64) NOT NULL,
  operation VARCHAR(128) DEFAULT NULL,
  actionDate DATE DEFAULT NULL
) DBPARTITION BY HASH(userId) TBPARTITION BY WEEK(actionDate) TBPARTITIONS 7

This configuration divides the table data by week using the user ID and time fields.

What's next

CREATE TABLE — full DDL syntax, parameters, and sharding rule options
Overview — sharding functions and PolarDB-X architecture