038、TiDB特性_聚簇索引和非聚簇索引

数哥

已于 2023-12-25 21:34:54 修改

阅读量893

点赞数

分类专栏： TiDB从入门到精通文章标签： tidb 分布式数据库 sql 数据库

于 2023-07-13 06:22:33 首次发布

本文链接：https://blog.csdn.net/wangzhicheng987/article/details/130785294

版权

TiDB从入门到精通专栏收录该内容

67 篇文章 9 订阅

订阅专栏

聚簇表

表中的行数据才能出顺序与主键存储的顺序一致
表的主键即为KV映射中Key的一部分
通过主键访问行记录时，可以直接获取行记录

create table t( a biging primary key clustered ,b varchar(255)); # a列为主键列，聚簇列

在这里插入图片描述

聚簇表（且ID为主键）

达到96M 分裂
创建聚簇表user

mysql> create table user(id int not null primary key clustered,name varchar(20),role varchar(20),age int ,key idxage(age));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into user values(1,'TiDB','SQL Layer',10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(2,'TiKV','KV Engine',20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into user values(3,'PD','Manager',30);
Query OK, 1 row affected (0.00 sec)

数据的KV映射关系

非聚簇表

表中的行数据存储顺序与主键存储的顺序不一定一致的表即为非聚簇表
行数据的键由TiDB内部隐式分配的_tidb_rowid构成，而主键本质上是唯一索引
通过主键访问行记录时，不可以直接获取行记录，需要先从额外存储的主键获取行的_tidb_rowid，再通过此行的_tidb_rowid获取行记录。比聚簇表多一次回表操作。

create table t( a biging primary key nonclustered ,b varchar(255));

在这里插入图片描述

查看是否为聚簇索引

show create table
show index from

mysql> show index from cluster_order;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
| Table         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | Clustered |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
| cluster_order |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       | YES       |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
1 row in set (0.00 sec)

查询系统表information_schema.tables 中的tidb_pk_type列

mysql> select TABLE_NAME,TIDB_PK_TYPE from information_schema.tables where table_name='cluster_order';
+---------------+--------------+
| TABLE_NAME    | TIDB_PK_TYPE |
+---------------+--------------+
| cluster_order | CLUSTERED    |
+---------------+--------------+
1 row in set (0.03 sec)

添加删除非聚簇索引

支持在建表之后添加或删除非聚簇索引。关键字nonclustered 可省略不写

alter table t add primary key(b, a) nonclustered;
alter table t add primary key(b,a);
alter table t drop primary key ;
alter table t drop index 'xx';

添加删除聚簇索引

目前TiDB不支持在建表之后添加或删除聚簇索引，也不支持聚簇索引和非聚簇索引互相转换

shard_row_id_bits

非聚簇表nonclustered,TiDB会使用一个隐式的自增rowid，造成写入热点。
shard_row_id_bits 通过调整隐式生成的_tidb_rowid的高位，从而更容易拆分不同的region来打散热点。
- shard_row_id_bits = 4 表示16个分片
- shard_row_id_bits = 5 表示32个分片
使用shard_row_id_bits的同时，配合使用pre_split_regions. 即建表后就立即拆分成2^pre_split_regions,pre_split_regions : 提前分配的空region数量

创建一个非聚簇表,并添加shard_row_id_bits 来打散数据，并在建表后将表切分成4个Region。

create table t (c int primary key nonclustered) shard_row_id_bits=4 pre_split_region =2;

修改表的打散随机位到5

alter table t shard_row_id_bits = 5;

评判主键是否是聚簇索引

建表的时候，如果有主键，是否是聚簇表

mysql> select @@global.tidb_enable_clustered_index;
+--------------------------------------+
| @@global.tidb_enable_clustered_index |
+--------------------------------------+
| INT_ONLY                             |
+--------------------------------------+
1 row in set (0.00 sec)
表示当主键为int类型的时候，才会自动将其建立为聚簇表

主键具有以下约束:
- 唯⼀标识表中的⾏
- 不允许 NULL
- 每个表只允许⼀个主键
- 如果声明的主键不是 INTEGER 数据类型,TiDB 会为表创建⾮聚簇索引
  - _tidb_rowid: ⾮聚簇索引的键
- 如果没有声明主键,TiDB 会为表创建⼀个隐式主键
  - _tidb_rowid: 隐式键值
主键聚簇索引: 表的主键即为聚簇索引,存储⼀⾏⾄少需要⼀个键值对。clusterd
主键⾮聚簇索引: ⾏数据的键由隐式分配的 _tidb_rowid 构成, 主键在 _tidb_rowid 上额外再建⽴索引,存储⼀⾏⾄少需要两个键值对。non-clustered

注意：这个跟MySQL不太一样。或者说TiDB当中分的更细。

有主键（并且数据类型类型为整型）为聚簇索引，通过主键可以找到对应行（主键简单理解为就是key）
没有主键（或者数据类型不是整型）则是非聚簇索引，需要先找到rowid,再通过rowid找到对应行。性能比没有主键的差。

示例

/* Table t1 Clustered */
# 聚簇索引
DROP TABLE IF EXISTS test.auto_random_t1_clustered;
CREATE TABLE test.auto_random_t1_clustered (
 id bigint PRIMARY KEY AUTO_RANDOM,
 id2 bigint,
 name char(255),
 varname varchar(200));

/* Table t2 Non-Clustered */
# 虽然符合要求，但明确强制转为非聚簇索引，所以是 非聚簇索引
DROP TABLE IF EXISTS test.t2_nonclustered;
CREATE TABLE test.t2_nonclustered (
 id bigint PRIMARY KEY NONCLUSTERED,
 id2 bigint,
 name char(255),
 varname char(200));
 
/* Table t3 Non-Clustered */
# 虽然有主键，但类型不是整型。所以不是聚簇索引
DROP TABLE IF EXISTS test.t3_nonclustered;
CREATE TABLE test.t3_nonclustered (
 id varchar(32) PRIMARY KEY,
 id2 bigint,
 name char(255),
 varname char(200));


/* Table t4 Clustered */
# 是聚簇索引，虽然类型不是整型，但强制转为聚簇索引
DROP TABLE IF EXISTS test.t4_clustered;
CREATE TABLE test.t4_clustered (
 id varchar(32) PRIMARY KEY CLUSTERED,
 id2 bigint,
 name char(255),
 varname char(200));
 
/* Table t5 Clustered with no primary key */
# 非聚簇索引，因为没有主键
DROP TABLE IF EXISTS test.t5_nonclustered;
CREATE TABLE test.t5_nonclustered (
 id bigint,
 id2 bigint,
 name char(255),
 varname char(200));

/* Table t6 Non-Clustered Composite PK */
# 非聚簇索引，虽然有主键，但它的主键是复合索引，联合主键
DROP TABLE IF EXISTS test.t6_nonclustered;
CREATE TABLE test.t6_nonclustered (
 id bigint,
 id2 bigint,
 name char(255),
 varname char(200),
 PRIMARY KEY (id, id2));

/* Table t7 Clustered Composite PK */
# 聚簇索引，虽然默认情况下联合主键不是聚簇索引，但可以强制设置。
DROP TABLE IF EXISTS test.t7_clustered;
CREATE TABLE test.t7_clustered (
 id bigint,
 id2 bigint,
 name char(255),
 varname char(200),
 PRIMARY KEY (id, id2) CLUSTERED);

支持的数据对象

数据库类型	table	partition	view	index	sequence	User Function	Procedure	Trigger
MySQL	✔	✔	✔	✔	×	✔	✔	✔
TiDB	✔	部分	部分	✔	✔	×	×	×
Oracle	✔	✔	✔	✔	✔	✔	✔	✔

kv 映射原理

TiDB中的数据是再RocksDB中以kv键值对的方式存储的
数据存储管理的基本单位为Region
- 每一个region 默认大小为96M
- 每一个region按照左闭右开的区间划分数据存储范围，如[a_d),[dg)
- 每一个schema都会分配一个唯一的TableID
非聚簇表KV的映射规则
KV存储中value存储真实的行数据

Key: tablePrefix{TableID}_recordPrefixSep{_Tidb_RowID}
Value: [col1, col2, col3, col4]

聚簇表KV的映射规则

Key: tablePrefix{TableID}_recordPrefixSep{Col1}
Value: [col2, col3, col4]

所谓聚簇就是指以某个列为基准，把拥有相同聚簇键值的所有行都存储再相同位置上的物理存储方法，在指定的聚簇中只创建一个表的聚簇结构叫做单表聚簇。

对象设计建议

建表时创建非聚簇表
- 为表添加shard_row_id_bits 和 pre_split_regions 表提示
- 其他列保持原有设计

create table noncluster_order( id bigint(20) unsigned auto_increment not null,
code varchar(30) not null,
order_no varchar(200) not null default '',
status int(4) not null,
cancel_flag int(4) default null,
 create_time datetime default null,
primary key(id) nonclustered
 ) engine=innodb shard_row_id_bits=4 pre_split_regions =3;

建表是创建聚簇表
- 主键使用具有强随机性的列，或在使用自生成ID,使用AUTO_RANDOM
- 准确选择列的数据类型。用整数型，避免使用字符串类型
- 避免创建无效的索引

CREATE TABLE `cluster_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_random,
  `code` varchar(30) NOT NULL,
  `order_no` varchar(200) NOT NULL DEFAULT '',
  `status` int(4) NOT NULL,
  `cancel_flag` int(4) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `create_user` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`) clustered
) ENGINE=InnoDB;

实验1：创建非聚簇表，并查看每个Region的key范围

1、创建一张非聚簇表

create table noncluster_order( id bigint(20) unsigned auto_increment not null,
code varchar(30) not null,
order_no varchar(200) not null default '',
status int(4) not null,
cancel_flag int(4) default null,
 create_time datetime default null,
primary key(id) nonclustered
 ) engine=innodb shard_row_id_bits=4 pre_split_regions =3;

2、查看表结构

mysql> show create table noncluster_order;
| noncluster_order | CREATE TABLE `noncluster_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `order_no` varchar(200) NOT NULL DEFAULT '',
  `status` int(4) NOT NULL,
  `cancel_flag` int(4) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T! SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3 */ |

3、查看每个region 的 start_key和end_key，和每个Region的written_bytes数据

mysql> show table noncluster_order regions;
+-----------+----------------------------+----------------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY                  | END_KEY                    | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+----------------------------+----------------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
|      3067 | t_69_i_1_                  | t_69_r_1152921504606846976 |      3068 |            1001 | 3068  |          0 |            27 |          0 |                    1 |                0 |
|      3069 | t_69_r_1152921504606846976 | t_69_r_2305843009213693952 |      3070 |            1001 | 3070  |          0 |             0 |          0 |                    1 |                0 |
|      3071 | t_69_r_2305843009213693952 | t_69_r_3458764513820540928 |      3072 |            1001 | 3072  |          0 |             0 |          0 |                    1 |                0 |
|      3073 | t_69_r_3458764513820540928 | t_69_r_4611686018427387904 |      3074 |            1001 | 3074  |          0 |             0 |          0 |                    1 |                0 |
|      3075 | t_69_r_4611686018427387904 | t_69_r_5764607523034234880 |      3076 |            1001 | 3076  |          0 |             0 |          0 |                    1 |                0 |
|      3077 | t_69_r_5764607523034234880 | t_69_r_6917529027641081856 |      3078 |            1001 | 3078  |          0 |             0 |          0 |                    1 |                0 |
|      3079 | t_69_r_6917529027641081856 | t_69_r_8070450532247928832 |      3080 |            1001 | 3080  |          0 |             0 |          0 |                    1 |                0 |
|      1002 | t_69_r_8070450532247928832 |                            |      1003 |            1001 | 1003  |          0 |           995 |          0 |                    1 |                0 |
+-----------+----------------------------+----------------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
8 rows in set (0.04 sec)

4、插入10行数据

mysql> insert into noncluster_order(code,order_no,status,create_user,create_time) values(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now());
Query OK, 1 row affected (0.08 sec)

mysql> insert into noncluster_order(code,order_no,status,create_user,create_time) values(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now());
Query OK, 1 row affected (0.02 sec)

mysql> insert into noncluster_order(code,order_no,status,create_user,create_time) values(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now());
Query OK, 1 row affected (0.01 sec)

mysql> insert into noncluster_order(code,order_no,status,create_user,create_time) values(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now());
Query OK, 1 row affected (0.02 sec)

mysql> insert into noncluster_order(code,order_no,status,create_user,create_time) values(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now());
Query OK, 1 row affected (0.02 sec)

mysql> insert into noncluster_order(code,order_no,status,create_user,create_time) values(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now());
Query OK, 1 row affected (0.02 sec)

mysql> insert into noncluster_order(code,order_no,status,create_user,create_time) values(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now());
Query OK, 1 row affected (0.01 sec)

mysql> insert into noncluster_order(code,order_no,status,create_user,create_time) values(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now());
Query OK, 1 row affected (0.01 sec)

mysql> insert into noncluster_order(code,order_no,status,create_user,create_time) values(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now());
Query OK, 1 row affected (0.02 sec)

mysql> insert into noncluster_order(code,order_no,status,create_user,create_time) values(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now());
Query OK, 1 row affected (0.02 sec)

5、再次查看每个region的writen_bytes 数据变化，变化的written_bytes代表数据被随机插入到不同的region上

mysql> show table noncluster_order regions;
+-----------+----------------------------+----------------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY                  | END_KEY                    | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+----------------------------+----------------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
|      3067 | t_69_i_1_                  | t_69_r_1152921504606846976 |      3068 |            1001 | 3068  |          0 |          3069 |          0 |                    1 |                0 |
|      3069 | t_69_r_1152921504606846976 | t_69_r_2305843009213693952 |      3070 |            1001 | 3070  |          0 |             0 |          0 |                    1 |                0 |
|      3071 | t_69_r_2305843009213693952 | t_69_r_3458764513820540928 |      3072 |            1001 | 3072  |          0 |           897 |          0 |                    1 |                0 |
|      3073 | t_69_r_3458764513820540928 | t_69_r_4611686018427387904 |      3074 |            1001 | 3074  |          0 |             0 |          0 |                    1 |                0 |
|      3075 | t_69_r_4611686018427387904 | t_69_r_5764607523034234880 |      3076 |            1001 | 3076  |          0 |             0 |          0 |                    1 |                0 |
|      3077 | t_69_r_5764607523034234880 | t_69_r_6917529027641081856 |      3078 |            1001 | 3078  |          0 |             0 |          0 |                    1 |                0 |
|      3079 | t_69_r_6917529027641081856 | t_69_r_8070450532247928832 |      3080 |            1001 | 3080  |          0 |             0 |          0 |                    1 |                0 |
|      1002 | t_69_r_8070450532247928832 |                            |      1003 |            1001 | 1003  |          0 |             0 |          0 |                    1 |                0 |
+-----------+----------------------------+----------------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
8 rows in set (0.04 sec)

实验2、创建一个聚簇索引的表并查看每个region的key的范围

CREATE TABLE cluster_order (
id bigint(20) unsigned NOT NULL AUTO_random,
code varchar(30) NOT NULL,
order_no varchar(200) NOT NULL DEFAULT ‘’,
status int(4) NOT NULL,
cancel_flag int(4) DEFAULT NULL,
create_time datetime DEFAULT NULL,
create_user varchar(32) DEFAULT NULL,
PRIMARY KEY (id) clustered
) ENGINE=InnoDB;

实验2：创建聚簇索引的表并查看每个Region的key范围

mysql> show create table cluster_order;

| Table         | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                             
+---------------+
| cluster_order | CREATE TABLE `cluster_order` (
  `id` bigint(20) unsigned NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
  `code` varchar(30) NOT NULL,
  `order_no` varchar(200) NOT NULL DEFAULT '',
  `status` int(4) NOT NULL,
  `cancel_flag` int(4) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `create_user` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------------+
1 row in set (0.01 sec)

3、查看table region的数量,和region的start_key和 end_key:

mysql> show table cluster_order regions;
+-----------+-----------+---------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+-----------+---------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
|      1002 | t_72_     |         |      1003 |            1001 | 1003  |          0 |             0 |          0 |                    1 |                0 |
+-----------+-----------+---------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
1 row in set (0.00 sec)

4、手工分裂reghion

mysql> split table cluster_order between (0) and (9223372036854775807) regions 16;
+--------------------+----------------------+
| TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO |
+--------------------+----------------------+
|                 15 |                    1 |
+--------------------+----------------------+
1 row in set (1.64 sec)

5、查看table region的数量，和region的start_key和end_key,记录每个region的written_bytes数据

mysql> show table cluster_order regions;
+-----------+----------------------------+----------------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY                  | END_KEY                    | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+----------------------------+----------------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
|      3085 | t_72_                      | t_72_r_576460752303423487  |      3086 |            1001 | 3086  |          0 |             0 |          0 |                    1 |                0 |
|      3087 | t_72_r_576460752303423487  | t_72_r_1152921504606846974 |      3088 |            1001 | 3088  |          0 |             0 |          0 |                    1 |                0 |
|      3089 | t_72_r_1152921504606846974 | t_72_r_1729382256910270461 |      3090 |            1001 | 3090  |          0 |             0 |          0 |                    1 |                0 |
|      3091 | t_72_r_1729382256910270461 | t_72_r_2305843009213693948 |      3092 |            1001 | 3092  |          0 |             0 |          0 |                    1 |                0 |
|      3093 | t_72_r_2305843009213693948 | t_72_r_2882303761517117435 |      3094 |            1001 | 3094  |          0 |             0 |          0 |                    1 |                0 |
|      3095 | t_72_r_2882303761517117435 | t_72_r_3458764513820540922 |      3096 |            1001 | 3096  |          0 |             0 |          0 |                    1 |                0 |
|      3097 | t_72_r_3458764513820540922 | t_72_r_4035225266123964409 |      3098 |            1001 | 3098  |          0 |             0 |          0 |                    1 |                0 |
|      3099 | t_72_r_4035225266123964409 | t_72_r_4611686018427387896 |      3100 |            1001 | 3100  |          0 |             0 |          0 |                    1 |                0 |
|      3101 | t_72_r_4611686018427387896 | t_72_r_5188146770730811383 |      3102 |            1001 | 3102  |          0 |             0 |          0 |                    1 |                0 |
|      3103 | t_72_r_5188146770730811383 | t_72_r_5764607523034234870 |      3104 |            1001 | 3104  |          0 |             0 |          0 |                    1 |                0 |
|      3105 | t_72_r_5764607523034234870 | t_72_r_6341068275337658357 |      3106 |            1001 | 3106  |          0 |             0 |          0 |                    1 |                0 |
|      3107 | t_72_r_6341068275337658357 | t_72_r_6917529027641081844 |      3108 |            1001 | 3108  |          0 |             0 |          0 |                    1 |                0 |
|      3109 | t_72_r_6917529027641081844 | t_72_r_7493989779944505331 |      3110 |            1001 | 3110  |          0 |             0 |          0 |                    1 |                0 |
|      3111 | t_72_r_7493989779944505331 | t_72_r_8070450532247928818 |      3112 |            1001 | 3112  |          0 |             0 |          0 |                    1 |                0 |
|      3113 | t_72_r_8070450532247928818 | t_72_r_8646911284551352305 |      3114 |            1001 | 3114  |          0 |             0 |          0 |                    1 |                0 |
|      1002 | t_72_r_8646911284551352305 |                            |      1003 |            1001 | 1003  |          0 |          1857 |          0 |                    1 |                0 |
+-----------+----------------------------+----------------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
16 rows in set (0.07 sec)

6、插入10行数据，每次插入5行

mysql> insert into cluster_order(code,order_no,status,create_user,create_time) values(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now()),(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now()),(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now()),(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now()),(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now());
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into cluster_order(code,order_no,status,create_user,create_time) values(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now()),(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now()),(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now()),(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now()),(concat('CODE_',LPAD(round(rand()*1000),5,0)),uuid(),round((rand()*100)),concat('USERID_',LPAD(round(rand()*1000),5,0)),now());
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select count(*) from cluster_order;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.03 sec)

7、再次查看每个Region的Written_bytes

mysql> show table cluster_order regions;
+-----------+----------------------------+----------------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
| REGION_ID | START_KEY                  | END_KEY                    | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS |
+-----------+----------------------------+----------------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
|      3085 | t_72_                      | t_72_r_576460752303423487  |      3086 |            1001 | 3086  |          0 |           739 |        675 |                    1 |                0 |
|      3087 | t_72_r_576460752303423487  | t_72_r_1152921504606846974 |      3088 |            1001 | 3088  |          0 |             0 |          0 |                    1 |                0 |
|      3089 | t_72_r_1152921504606846974 | t_72_r_1729382256910270461 |      3090 |            1001 | 3090  |          0 |             0 |          0 |                    1 |                0 |
|      3091 | t_72_r_1729382256910270461 | t_72_r_2305843009213693948 |      3092 |            1001 | 3092  |          0 |           739 |        675 |                    1 |                0 |
|      3093 | t_72_r_2305843009213693948 | t_72_r_2882303761517117435 |      3094 |            1001 | 3094  |          0 |             0 |          0 |                    1 |                0 |
|      3095 | t_72_r_2882303761517117435 | t_72_r_3458764513820540922 |      3096 |            1001 | 3096  |          0 |             0 |          0 |                    1 |                0 |
|      3097 | t_72_r_3458764513820540922 | t_72_r_4035225266123964409 |      3098 |            1001 | 3098  |          0 |             0 |          0 |                    1 |                0 |
|      3099 | t_72_r_4035225266123964409 | t_72_r_4611686018427387896 |      3100 |            1001 | 3100  |          0 |             0 |          0 |                    1 |                0 |
|      3101 | t_72_r_4611686018427387896 | t_72_r_5188146770730811383 |      3102 |            1001 | 3102  |          0 |             0 |          0 |                    1 |                0 |
|      3103 | t_72_r_5188146770730811383 | t_72_r_5764607523034234870 |      3104 |            1001 | 3104  |          0 |             0 |          0 |                    1 |                0 |
|      3105 | t_72_r_5764607523034234870 | t_72_r_6341068275337658357 |      3106 |            1001 | 3106  |          0 |             0 |          0 |                    1 |                0 |
|      3107 | t_72_r_6341068275337658357 | t_72_r_6917529027641081844 |      3108 |            1001 | 3108  |          0 |             0 |          0 |                    1 |                0 |
|      3109 | t_72_r_6917529027641081844 | t_72_r_7493989779944505331 |      3110 |            1001 | 3110  |          0 |             0 |          0 |                    1 |                0 |
|      3111 | t_72_r_7493989779944505331 | t_72_r_8070450532247928818 |      3112 |            1001 | 3112  |          0 |             0 |          0 |                    1 |                0 |
|      3113 | t_72_r_8070450532247928818 | t_72_r_8646911284551352305 |      3114 |            1001 | 3114  |          0 |             0 |          0 |                    1 |                0 |
|      1002 | t_72_r_8646911284551352305 |                            |      1003 |            1001 | 1003  |          0 |             0 |          0 |                    1 |                0 |
+-----------+----------------------------+----------------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+
16 rows in set (0.06 sec)

8、思考为什么只有2个region的written_bytes有变化
注意：如果一条 insert 语句插入多行，这个多行并不会受random影响，不会分散。

mysql> select id,id>>58 from cluster_order;
+----------------------+--------+
| id                   | id>>58 |
+----------------------+--------+
| 13835058055282163718 |     48 |
| 13835058055282163719 |     48 |
| 13835058055282163720 |     48 |
| 13835058055282163721 |     48 |
| 13835058055282163722 |     48 |
|  1729382256910270465 |      6 |
|  1729382256910270466 |      6 |
|  1729382256910270467 |      6 |
|  1729382256910270468 |      6 |
|  1729382256910270469 |      6 |
+----------------------+--------+
10 rows in set (0.03 sec)

数哥

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
038、TiDB特性_聚簇索引和非聚簇索引

主键⾮聚簇索引: ⾏数据的键由隐式分配的 _tidb_rowid 构成, 主键在 _tidb_rowid 上额外再建⽴索引,存储⼀⾏⾄少需要两个键值对。5、查看table region的数量，和region的start_key和end_key,记录每个region的written_bytes数据。性能比没有主键的差。3、查看每个region 的 start_key和end_key，和每个Region的written_bytes数据。实验2、创建一个聚簇索引的表并查看每个region的key的范围。
复制链接

扫一扫