AnalyticDB:Table schema design

Choosing a distribution key

Select a distribution key based on these criteria, in order of priority:

1. Even value distribution — pick a high-cardinality column so rows spread evenly across nodes. Good choices include transaction IDs, device IDs, user IDs, and auto-increment columns. Uneven distribution causes data skew and degrades write performance.

2. Join columns — if a query joins two tables on a specific column, use that column as the distribution key for both tables. This routes matching rows to the same node and eliminates cross-node data shuffles. For example, to query historical orders by customer, use customer_id as the distribution key.

3. Select fields used for grouping (GROUP BY) as the distribution key. During a group operation, the query optimizer can use the data distribution property to perform local grouping. This reduces network overhead between inner nodes and can save up to 60% of the computing time for the group operator.

4. Frequent filter columns — columns that appear often in WHERE clauses let AnalyticDB for MySQL read only the relevant shards and skip the rest.

5. Fewest fields possible — each table has exactly one distribution key, but it can span multiple columns. Fewer columns in the distribution key make it useful across a wider variety of queries.

Default behavior when no distribution key is specified:

• If the table has a primary key, AnalyticDB for MySQL uses the primary key as the distribution key.

• If the table has no primary key, the system adds an __adb_auto_id__ column and uses it as both the primary key and the distribution key.

• For AnalyticDB for MySQL clusters in Elastic mode with 8 or 16 cores, the default number of shards is 32.

• For AnalyticDB for MySQL clusters in Elastic mode with 32 or more cores, the default number of shards depends on the number of Elastic I/O Units (EIUs). The relationship is as follows:

  Number of EIUs	Number of shards
  EIU=1	32
  2 <= EIU < 4	64
  4 <= EIU < 8	128
  8 <= EIU < 16	192
  16 <= EIU < 32	256
  32 <= EIU < 64	512
  EIU >= 64	1024

• For AnalyticDB for MySQL clusters in Reserved mode, the default number of shards depends on the number of node groups. The relationship is as follows:

  Number of node groups	Number of shards
  Number of node groups=1	16
  2 <= Number of node groups < 4	32
  4 <= Number of node groups < 8	64
  8 <= Number of node groups < 16	128
  16 <= Number of node groups < 32	256
  32 <= Number of node groups < 64	512
  Number of node groups >= 64	1024

  Note

  For more information about the default number of shards, see FAQ.

Choosing a partition key

• Supported granularities: year, month, day, or raw value. There is no finer granularity.

• A data volume of 300 million to 1 billion rows for each subpartition is considered a reasonable division. If the data volume is less than 300 million rows, the partitioning granularity is too small. You can increase the granularity, for example, from day to month. If the data volume is greater than 1 billion rows, the partitioning granularity is too large. You can decrease the granularity, for example, from month to day.

• Match granularity to data volume: too coarse a granularity (for example, partitioning by year for a table that grows by millions of rows per day) creates oversized partitions that degrade both query and write performance. Too fine a granularity creates an excessive number of small partitions with the same result.

• Treat partitions as stable units: avoid designs where many historical partitions are updated frequently every day. Frequent updates across many partitions indicate a poor partition column choice.

• Set a lifecycle: because the maximum number of partitions per table is limited, data in a partitioned table cannot be retained indefinitely. Always define a LIFECYCLE value to prevent hitting this limit. For partition limits, see Limits.

SELECT 
partition_id,    -- Partition name
row_count,       -- Total number of rows in the partition
local_data_size, -- Size of the local storage occupied by the partition
index_size,      -- Index size of the partition
pk_size,         -- Size of the primary key index of the partition
remote_data_size -- Size of the remote storage occupied by the partition 
FROM information_schema.kepler_partitions 
WHERE schema_name = '<db_name>' AND table_name ='<tbl_name>';

To diagnose whether your partition column is a good choice, use Distribution field reasonability diagnostics.

Choosing a primary key

• Must include the distribution key and partition key: the primary key must contain every column in the distribution key and every column in the partition key. Place the distribution key and partition key columns first in the composite primary key definition.

• Prefer numeric columns: numeric primary key fields improve lookup performance compared to string fields.

• Use as few columns as possible: a narrower primary key reduces index overhead.

For details on the PRIMARY KEY syntax, see CREATE TABLE.

When to use a clustered index

Clustered indexes are not intended for all tables. Each table supports only one clustered index, and maintaining it consumes CPU resources. Use a clustered index only when:

• Queries consistently filter on a specific column or set of columns, and

• The query response time improvement justifies the additional CPU cost of maintaining the index.

Example: in a student information system where each student queries only their own grades, define the student ID as the clustered index key. All rows for a given student are stored contiguously, so the query reads a single sequential block instead of scanning the table.

For the syntax to create a clustered index, see CREATE TABLE.