035、TiDB特性_没有主键的随机ID

表相关选项

可以向Create table 语句中添加选项，例如：

create table test.shard_test1(
id bigint,
name varchar(30)
) shard_row_id_bits = 2;

• comment: 注释选项
• DEFAULT CHARACTER SET: 指定表所使用的字符集
• SHARD_ROW_ID_BITS： 用来设置字段_tidb_rowid的分片数量的bit位数，设置为3，则2^3(8),用于解决热点问题，随机生成唯一标识。
• _tidb_rowid: 当没有主键的时候，用于表识行的唯一id。用于解决没有主键的表如何插入到TiKV的Region中。

示例

• 当表有主键的时候，是没有这个_tidb_rowid列

mysql> create table t2(id int primary key,name char(30));
Query OK, 0 rows affected (0.72 sec)

mysql> insert into t2 values(1,'AAA');
Query OK, 1 row affected (0.11 sec)

mysql> select _tidb_rowid from t2;
ERROR 1054 (42S22): Unknown column '_tidb_rowid' in 'field list'

• 当表没有有主键的时候，是有这个_tidb_rowid列，并且这个列初始值是1，递增也是1

mysql> create table t3(id int,name char(30));
Query OK, 0 rows affected (0.64 sec)

mysql> insert into t3 values(1,'AAA');
Query OK, 1 row affected (0.08 sec)

mysql> select _tidb_rowid from t3;
+-------------+
| _tidb_rowid |
+-------------+
|           1 |
+-------------+
1 row in set (0.03 sec)

• 如果再插入多条记录，它的效果是auto_increment.这样可能会有热点问题。即数据只在一个region中处理。

mysql> insert into t3 values(10,'AAA');
Query OK, 1 row affected (0.02 sec)

mysql> select _tidb_rowid from t3;
+-------------+
| _tidb_rowid |
+-------------+
|           1 |
|           2 |
+-------------+
2 rows in set (0.02 sec)

• shared_row_in_bits
  shared_row_in_bits: 分配多个分片(region),效果类似auto_random,解决热点问题。

create table test.shard_rb(
name varchar(30)
) shard_row_id_bits = 4;

mysql> select count(*) from shard_rb;
+----------+
| count(*) |
+----------+
|       88 |
+----------+
1 row in set (0.04 sec)

• 通过数据字典查看分片数

mysql> select tidb_row_id_sharding_info,tidb_pk_type  from information_schema.tables where table_schema='test' and table_name='shard_rb';
+---------------------------+--------------+
| tidb_row_id_sharding_info | tidb_pk_type |
+---------------------------+--------------+
| SHARD_BITS=4              | NONCLUSTERED |
+---------------------------+--------------+
1 row in set (0.01 sec)

mysql> SELECT substr(cast( _tidb_rowid as CHAR),1,2) as id_prefix, count(*) as
    -> approx_rows_in_shard
    -> FROM shard_rb
    -> GROUP BY id_prefix
    -> HAVING approx_rows_in_shard > 1
    -> ORDER BY id_prefix;
+-----------+----------------------+
| id_prefix | approx_rows_in_shard |
+-----------+----------------------+
| 11        |                    6 |
| 17        |                    6 |
| 23        |                    7 |
| 28        |                    5 |
| 34        |                    5 |
| 40        |                    5 |
| 46        |                    7 |
| 51        |                    5 |
| 57        |                    9 |
| 63        |                    6 |
| 69        |                    8 |
| 74        |                    5 |
| 80        |                    2 |
| 86        |                    7 |
+-----------+----------------------+
14 rows in set (0.09 sec)