简介
表结构设计的好坏会影响到最终的结果。
SCHEMA的设计
非聚簇索引数据热定问题
- 问题来源，由于非聚簇索引的row_id是系统自动生成的只增加序列，这个时候就会都往一个region里面开始写入数据，就会产生写入热点的问题。
- SHARD_ROW_ID_BITS是预分区，它只是一个逻辑的概念，PRE_SPLIT_REGIONS是建表以后就直接生成对应的region，减少数据插入的时候生成region的时间。
- 如果表是自增的主键的时候，也会出现数据插入热点的问题。
分区表
自增主键的原理
使用限制
自增主键解决数据热点问题
原理
后面的auto_random(3)的3是生成随机位的位数，越大越随机。
使用限制
设计建议
高兼容性（从mysql迁移到TiDB）解决写入热点问题
因为这里用到的 SHARD_ROW_ID_BITS是预分区，它只是一个逻辑的概念，PRE_SPLIT_REGIONS是建表以后就直接生成对应的region，减少数据插入的时候生成region的时间。 就会把AUTO_INCREMENT数据给打散。
高性能（新业务的情况）减少回表查询的情况
实操
非聚簇表数据打散操作
use test;
create table test.`noncluster_order`(
`id` bigint(20) unsigned not null auto_increment,
`code` varchar(30) not null,
primary key (`id`) nonclustered
) ;
#查看创建表的region
show table noncluster_order regions;
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 |
+-----------+-----------+---------+-----------+-----------------+-------------+------------+---------------+------------+----------------------+------------------+
| 66 | t_69_ | | 95 | 4 | 67, 95, 133 | 0 | 238 | 994 | 1 | 0 |
+-----------+-----------+---------+-----------+-----------------+-------------+------------+---------------+------------+----------------------+------------------+
drop table noncluster_order;
#创建一个预分片表
create table test.`noncluster_order`(
`id` bigint(20) unsigned not null auto_increment,
`code` varchar(30) not null,
primary key (`id`) nonclustered
) engine=InnoDB shard_row_id_bits=4 pre_split_regions=3;
#查看创建表的region
show table noncluster_order regions;
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 |
+-----------+----------------------------+----------------------------+-----------+-----------------+------------------+------------+---------------+------------+----------------------+------------------+
| 2009 | t_72_i_1_ | t_72_r_1152921504606846976 | 2011 | 4 | 2010, 2011, 2012 | 0 | 0 | 0 | 1 | 0 |
| 2013 | t_72_r_1152921504606846976 | t_72_r_2305843009213693952 | 2015 | 4 | 2014, 2015, 2016 | 0 | 27 | 0 | 1 | 0 |
| 2017 | t_72_r_2305843009213693952 | t_72_r_3458764513820540928 | 2019 | 4 | 2018, 2019, 2020 | 0 | 27 | 0 | 1 | 0 |
| 2021 | t_72_r_3458764513820540928 | t_72_r_4611686018427387904 | 2023 | 4 | 2022, 2023, 2024 | 0 | 27 | 0 | 1 | 0 |
| 2025 | t_72_r_4611686018427387904 | t_72_r_5764607523034234880 | 2027 | 4 | 2026, 2027, 2028 | 0 | 27 | 0 | 1 | 0 |
| 2029 | t_72_r_5764607523034234880 | t_72_r_6917529027641081856 | 2030 | 1 | 2030, 2031, 2032 | 0 | 0 | 0 | 1 | 0 |
| 2033 | t_72_r_6917529027641081856 | t_72_r_8070450532247928832 | 2035 | 4 | 2034, 2035, 2036 | 0 | 27 | 0 | 1 | 0 |
| 66 | t_72_r_8070450532247928832 | | 95 | 4 | 67, 95, 133 | 0 | 0 | 0 | 1 | 0 |
+-----------+----------------------------+----------------------------+-----------+-----------------+------------------+------------+---------------+------------+----------------------+------------------+
8 rows in set (0.01 sec)
#插入数据进行测试看是否分散到不同的region
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
#下面可以看到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 |
+-----------+----------------------------+----------------------------+-----------+-----------------+------------------+------------+---------------+------------+----------------------+------------------+
| 2009 | t_72_i_1_ | t_72_r_1152921504606846976 | 2011 | 4 | 2010, 2011, 2012 | 0 | 2903 | 0 | 1 | 0 |
| 2013 | t_72_r_1152921504606846976 | t_72_r_2305843009213693952 | 2015 | 4 | 2014, 2015, 2016 | 0 | 27 | 0 | 1 | 0 |
| 2017 | t_72_r_2305843009213693952 | t_72_r_3458764513820540928 | 2019 | 4 | 2018, 2019, 2020 | 0 | 27 | 0 | 1 | 0 |
| 2021 | t_72_r_3458764513820540928 | t_72_r_4611686018427387904 | 2023 | 4 | 2022, 2023, 2024 | 0 | 27 | 0 | 1 | 0 |
| 2025 | t_72_r_4611686018427387904 | t_72_r_5764607523034234880 | 2027 | 4 | 2026, 2027, 2028 | 0 | 595 | 0 | 1 | 0 |
| 2029 | t_72_r_5764607523034234880 | t_72_r_6917529027641081856 | 2030 | 1 | 2030, 2031, 2032 | 0 | 607 | 0 | 1 | 0 |
| 2033 | t_72_r_6917529027641081856 | t_72_r_8070450532247928832 | 2035 | 4 | 2034, 2035, 2036 | 0 | 27 | 0 | 1 | 0 |
| 66 | t_72_r_8070450532247928832 | | 95 | 4 | 67, 95, 133 | 0 | 0 | 0 | 1 | 0 |
+-----------+----------------------------+----------------------------+-----------+-----------------+------------------+------------+---------------+------------+----------------------+------------------+
聚簇索引打散数据操作
use test;
#创建聚簇表
create table test.`noncluster_order`(
`id` bigint(20) unsigned auto_random not null,
`code` varchar(30) not null,
primary key (`id`) clustered
)engine=InnoDB ;
show table noncluster_order regions;
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 |
+-----------+-----------+---------+-----------+-----------------+-------------+------------+---------------+------------+----------------------+------------------+
| 66 | t_75_ | | 133 | 5 | 67, 95, 133 | 0 | 42 | 0 | 1 | 0 |
+-----------+-----------+---------+-----------+-----------------+-------------+------------+---------------+------------+----------------------+------------------+
1 row in set (0.02 sec)
#手动分配预分区
split table noncluster_order between (0) and (47193724923494234) regions 16;
mysql> split table noncluster_order between (0) and (47193724923494234) regions 16;
+--------------------+----------------------+
| TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO |
+--------------------+----------------------+
| 15 | 1 |
+--------------------+----------------------+
1 row in set (0.04 sec)
#查看下现在的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 |
+-----------+--------------------------+--------------------------+-----------+-----------------+------------------+------------+---------------+------------+----------------------+------------------+
| 2045 | t_75_ | t_75_r_2949607807718389 | 2047 | 4 | 2046, 2047, 2048 | 0 | 39 | 0 | 1 | 0 |
| 2049 | t_75_r_2949607807718389 | t_75_r_5899215615436778 | 2050 | 1 | 2050, 2051, 2052 | 0 | 39 | 0 | 1 | 0 |
| 2053 | t_75_r_5899215615436778 | t_75_r_8848823423155167 | 2056 | 5 | 2054, 2055, 2056 | 0 | 27 | 0 | 1 | 0 |
| 2057 | t_75_r_8848823423155167 | t_75_r_11798431230873556 | 2060 | 5 | 2058, 2059, 2060 | 0 | 27 | 0 | 1 | 0 |
| 2061 | t_75_r_11798431230873556 | t_75_r_14748039038591945 | 2064 | 5 | 2062, 2063, 2064 | 0 | 27 | 0 | 1 | 0 |
| 2065 | t_75_r_14748039038591945 | t_75_r_17697646846310334 | 2066 | 1 | 2066, 2067, 2068 | 0 | 0 | 0 | 1 | 0 |
| 2069 | t_75_r_17697646846310334 | t_75_r_20647254654028723 | 2071 | 4 | 2070, 2071, 2072 | 0 | 39 | 0 | 1 | 0 |
| 2073 | t_75_r_20647254654028723 | t_75_r_23596862461747112 | 2075 | 4 | 2074, 2075, 2076 | 0 | 27 | 0 | 1 | 0 |
| 2077 | t_75_r_23596862461747112 | t_75_r_26546470269465501 | 2078 | 1 | 2078, 2079, 2080 | 0 | 0 | 0 | 1 | 0 |
| 2081 | t_75_r_26546470269465501 | t_75_r_29496078077183890 | 2083 | 4 | 2082, 2083, 2084 | 0 | 27 | 0 | 1 | 0 |
| 2085 | t_75_r_29496078077183890 | t_75_r_32445685884902279 | 2086 | 1 | 2086, 2087, 2088 | 0 | 0 | 0 | 1 | 0 |
| 2089 | t_75_r_32445685884902279 | t_75_r_35395293692620668 | 2092 | 5 | 2090, 2091, 2092 | 0 | 27 | 0 | 1 | 0 |
| 2093 | t_75_r_35395293692620668 | t_75_r_38344901500339057 | 2096 | 5 | 2094, 2095, 2096 | 0 | 39 | 0 | 1 | 0 |
| 2097 | t_75_r_38344901500339057 | t_75_r_41294509308057446 | 2098 | 1 | 2098, 2099, 2100 | 0 | 39 | 0 | 1 | 0 |
| 2101 | t_75_r_41294509308057446 | t_75_r_44244117115775835 | 2103 | 4 | 2102, 2103, 2104 | 0 | 39 | 0 | 1 | 0 |
| 66 | t_75_r_44244117115775835 | | 133 | 5 | 67, 95, 133 | 0 | 0 | 0 | 1 | 0 |
+-----------+--------------------------+--------------------------+-----------+-----------------+------------------+------------+---------------+------------+----------------------+------------------+
16 rows in set (0.02 sec)
#下面是分开插入如果是insert into table_name values(),(),()那么最后生成的id就是连续的。
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
insert into noncluster_order(code) values('1');
#查看插入数据的情况
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 |
+-----------+--------------------------+--------------------------+-----------+-----------------+------------------+------------+---------------+------------+----------------------+------------------+
| 2045 | t_75_ | t_75_r_2949607807718389 | 2047 | 4 | 2046, 2047, 2048 | 0 | 645 | 0 | 1 | 0 |
| 2049 | t_75_r_2949607807718389 | t_75_r_5899215615436778 | 2050 | 1 | 2050, 2051, 2052 | 0 | 39 | 0 | 1 | 0 |
| 2053 | t_75_r_5899215615436778 | t_75_r_8848823423155167 | 2056 | 5 | 2054, 2055, 2056 | 0 | 27 | 0 | 1 | 0 |
| 2057 | t_75_r_8848823423155167 | t_75_r_11798431230873556 | 2060 | 5 | 2058, 2059, 2060 | 0 | 27 | 0 | 1 | 0 |
| 2061 | t_75_r_11798431230873556 | t_75_r_14748039038591945 | 2064 | 5 | 2062, 2063, 2064 | 0 | 27 | 0 | 1 | 0 |
| 2065 | t_75_r_14748039038591945 | t_75_r_17697646846310334 | 2066 | 1 | 2066, 2067, 2068 | 0 | 0 | 0 | 1 | 0 |
| 2069 | t_75_r_17697646846310334 | t_75_r_20647254654028723 | 2071 | 4 | 2070, 2071, 2072 | 0 | 39 | 0 | 1 | 0 |
| 2073 | t_75_r_20647254654028723 | t_75_r_23596862461747112 | 2075 | 4 | 2074, 2075, 2076 | 0 | 27 | 0 | 1 | 0 |
| 2077 | t_75_r_23596862461747112 | t_75_r_26546470269465501 | 2078 | 1 | 2078, 2079, 2080 | 0 | 0 | 0 | 1 | 0 |
| 2081 | t_75_r_26546470269465501 | t_75_r_29496078077183890 | 2083 | 4 | 2082, 2083, 2084 | 0 | 27 | 0 | 1 | 0 |
| 2085 | t_75_r_29496078077183890 | t_75_r_32445685884902279 | 2086 | 1 | 2086, 2087, 2088 | 0 | 0 | 0 | 1 | 0 |
| 2089 | t_75_r_32445685884902279 | t_75_r_35395293692620668 | 2092 | 5 | 2090, 2091, 2092 | 0 | 27 | 0 | 1 | 0 |
| 2093 | t_75_r_35395293692620668 | t_75_r_38344901500339057 | 2096 | 5 | 2094, 2095, 2096 | 0 | 39 | 0 | 1 | 0 |
| 2097 | t_75_r_38344901500339057 | t_75_r_41294509308057446 | 2098 | 1 | 2098, 2099, 2100 | 0 | 39 | 0 | 1 | 0 |
| 2101 | t_75_r_41294509308057446 | t_75_r_44244117115775835 | 2103 | 4 | 2102, 2103, 2104 | 0 | 39 | 0 | 1 | 0 |
| 66 | t_75_r_44244117115775835 | | 133 | 5 | 67, 95, 133 | 0 | 458 | 0 | 1 | 0 |
+-----------+--------------------------+--------------------------+-----------+-----------------+------------------+------------+---------------+------------+----------------------+------------------+
select id,id>>58 from noncluster_order;
扫一扫
6188
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
