## Module 2: Using AUTO_RANDOM for Auto Sharding

![demo_203-tidb-x-beyond-mysql.jpg 85%](https://lab-static.pingcap.com/images/2025/12/2/33bc3e42e21c28d8601cc5c8dcdb7ea51ee60ea4.jpg)

TiDB offers a high level of compatibility with MySQL, but there are important differences due to its distributed architecture—the "Not Only MySQL" aspect. A major benefit of TiDB is that it eliminates the need for manual sharding, as sharding is handled automatically.

If the table's primary key uses TiDB's **AUTO_RANDOM** instead of a monotonically increasing MySQL **AUTO_INCREMENT**, TiDB can automatically distribute data across shards and effectively alleviate the insertion hotspot issue commonly caused by **AUTO_INCREMENT**.

### Step 1. Understand the AUTO_RANDOM behavior

- In TiDB, you can specify `AUTO_RANDOM(<shard_bits>)` to enable automatic sharding for a primary key column. Within each shard, the column behaves like MySQL's **auto_increment**. This feature requires that the column be of BIGINT type (64 bits): the first bit is the sign bit, the next set of bits are shard bits, and the remaining bits are used for **auto_increment** values.

![AutoRandomValueStructure_aa_001_20240125.png 70%](https://lab-static.pingcap.com/images/2025/11/19/357034cd28824fe7d2950338331c5d0f3f2eb2c6.png)

- For example, the column definition `bigint AUTO_RANDOM(5)` means TiDB will use 5 bits for sharding, resulting in 2 to the power of 5, or 32 positive number and 32 negative number shards.

### Step 2. Examine the AUTO_RANDOM property in the bookshop schema

- In [Terminal 1](ssh://monitor1-ssh#1), connect to the lab database again.

  ```bash
  mysql \
   --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \
   -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \
   --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt \
   -D ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}
  ```

- Review the SQL statement from the script used to create the `books` table. Notice the column `id` must be of type BIGINT to use AUTO_RANDOM.

  ```sql
  SYSTEM cat create_bookshop_schema_in_current_db.sql | egrep -A18 'CREATE TABLE `books`' | egrep '(`books`|`id`)';
  ```

  ```output
  CREATE TABLE `books` (
      `id` bigint(20) AUTO_RANDOM,
      PRIMARY KEY (`id`)
  ```

- Review the `books` table definition, notice that the primary key `id` is defined using AUTO_RANDOM. And, AUTO_RANDOM is declared as table hint.

  ```sql
  SHOW CREATE TABLE books\G
  ```

  ```output
  *************************** 1. row ***************************
  ...
    `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
  ...
    PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
  ...
  ```

- Confirm the AUTO_RANDOM setting from information schema as well. You will find the default random bits is 5.

  ```sql
  SELECT TABLE_NAME, TIDB_ROW_ID_SHARDING_INFO
  FROM information_schema.tables
  WHERE TABLE_NAME='books' AND TABLE_SCHEMA='${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}';
  ```

  ```output
  +------------+---------------------------+
  | TABLE_NAME | TIDB_ROW_ID_SHARDING_INFO |
  +------------+---------------------------+
  | books      | PK_AUTO_RANDOM_BITS=5     |
  +------------+---------------------------+
  ` rows in set (0.01 sec)
  ```

### Step 3. Explain AUTO_RANDOM behavior

- In the script used to create the `books` table, we use the system variable `allow_auto_random_explicit_insert` to allow explicit assignment of values to the AUTO_RANDOM column. This makes it more convenient to migrate data from an existing system to TiDB.

  ```sql
  SYSTEM head -n 1 create_bookshop_schema_in_current_db.sql;
  ```

  ```output
  SET @@allow_auto_random_explicit_insert = true;
  ```

- As a result, the `id` values of the existing 100 books in the table were explicitly assigned by the executed SQL script. They range from 1 to 100 and were not generated by the AUTO_RANDOM mechanism.

  ```sql
  SELECT MIN(id), MAX(id), COUNT(id) FROM books;
  ```

  ```output
  +---------+---------+-----------+
  | MIN(id) | MAX(id) | COUNT(id) |
  +---------+---------+-----------+
  |       1 |     100 |       100 |
  +---------+---------+-----------+
  1 row in set (0.01 sec)
  ```

- When you insert new books, the AUTO_RANDOM feature automatically enables auto-sharding for the primary key. To observe this behavior, we'll perform 10 separate INSERT operations in multiple transactions, copying existing books and prefixing `v2` to their titles.

  ```sql
  INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 1 and 10;
  INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 11 and 20;
  INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 21 and 30;
  INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 31 and 40;
  INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 41 and 50;
  INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 51 and 60;
  INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 61 and 70;
  INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 71 and 80;
  INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 81 and 90;
  INSERT INTO books (title, type, published_at, stock, price) SELECT CONCAT('v2_',title), type, NOW(), 0, price FROM books WHERE id BETWEEN 91 and 100;
  ```

  ```output
  Records: 10  Duplicates: 0  Warnings: 0
  ...
  Records: 10  Duplicates: 0  Warnings: 0
  ```

- Recall that when using AUTO_RANDOM(5), 5 bits are allocated for sharding, along with 1 sign bit as the prefix.
- By right-shifting the `id` value by 58 bits, you can view the 6 most significant bits used for sharding, illustrating how rows are distributed across different shards. The following query also displays the first `id` in each shard and focuses on books with `_v2` in their title. In the example output, 100 rows are spread across 9 shards; your specific output may differ slightly, in your case it might be **8**, **9**, or **10** shards were utilized by the previous 10 separate insert transactions.

  ```sql
  SELECT
      MIN(id) first_id,
      id >> 58 as shard,
      COUNT(1) as rows_by_shard
  FROM books WHERE title LIKE 'v2_%'
  GROUP BY shard
  ORDER BY first_id;
  ```

  ```output
  +---------------------+-------+---------------+
  | first_id            | shard | rows_by_shard |
  +---------------------+-------+---------------+
  | 1729382256910270585 |     6 |            10 |
  | 2017612633061982359 |     7 |            10 |
  | 2594073385365405887 |     9 |            10 |
  | 4323455642275676261 |    15 |            10 |
  | 4611686018427388065 |    16 |            10 |
  | 5188146770730811573 |    18 |            10 |
  | 5764607523034235011 |    20 |            20 |
  | 6341068275337658479 |    22 |            10 |
  | 8070450532247929003 |    28 |            10 |
  +---------------------+-------+---------------+
  9 rows in set (0.01 sec)
  ```

### Step 4. Compare the differences before and after using AUTO_RANDOM

- Try to interpret the results of the following query on your own. Note that your output may differ because AUTO_RANDOM generates values randomly.

  ```sql
  SELECT 'books', MIN(id), MAX(id), COUNT(id)
  FROM books WHERE title NOT LIKE 'v2_%'
  UNION ALL
  SELECT 'books_v2', MIN(id), MAX(id), COUNT(id)
  FROM books WHERE title LIKE 'v2_%'
  ORDER BY 1;
  ```

  ```output
  +----------+---------------------+---------------------+-----------+
  | books    | MIN(id)             | MAX(id)             | COUNT(id) |
  +----------+---------------------+---------------------+-----------+
  | books    |                   1 |                 100 |       100 |
  | books_v2 | 1729382256910270585 | 8070450532247929012 |       100 |
  +----------+---------------------+---------------------+-----------+
  2 rows in set (0.01 sec)
  ```

### Step 5. Cleanup

- Keep the first 100 books, delete additional rows from `books` table.

  ```sql
  DELETE FROM books WHERE id > 100;
  ANALYZE TABLE books;
  ```

  ```output
  Query OK, 100 rows affected (0.2 sec)
  ```

- You can explore a deeper dive into how **AUTO_RANDOM** mitigates concurrent insertion hotspots in the instructor-led hands-on lab [Clustered and Non-Clustered Indexes](/labs/dba_307_lab_ff0). For now, keep your database connection open and proceed to the next module.