关于mysql:第35期MySQL-数据垂直拆分

48次阅读

共计 4602 个字符，预计需要花费 12 分钟才能阅读完成。

引言

一般来说讲，提到数据拆分，能够归结为两个层面：一是垂直拆分，二是程度拆分。这里咱们来探讨下垂直拆分。

垂直拆分是以数据库、表、列等为单位进行拆分的办法。

注释

MySQL 里垂直拆分能够细分为：垂直拆库（实例级别）、垂直拆模（表级别）、垂直拆表（列级别）。
1、垂直拆库：
也即在业务层依照业务逻辑由大拆小，各个子业务之间无关联查问，仅查问单个子业务即可，相似微服务治理的思维。在 MySQL 里体现为将全副表依照业务关联严密水平拆分后存储在不同的数据库，每个数据库为一台 MySQL 实例，查问仅查对应的数据库实例即可。

如图 1 所示：

上图展现出原始业务数据的拆分示例，依照不同的业务逻辑分类划分为多个子业务，每个子业务对应一套 MySQL 实例组，每个 MySQL 实例组依照 MySQL 的 HA 架构部署（主从同步、组复制、MySQL CLUSTER 等）

图 1 的长处很显著，原始业务的压力扩散到各个小业务上，晋升了整体的性能。但也不能疏忽毛病：数据库对利用端不通明，利用端必须本人保护路由数据；当单个实例数据量以及申请再次达到下限时，后续拆分十分艰难。

2、垂直拆模：
垂直拆模和垂直拆库大体相似，不过垂直拆模的最小单元是 schema，而不是实例。大家晓得，MySQL 的数据库等同于 schema，一个数据库对应磁盘上的一个文件目录。这种拆分个别是为了解决文件系统中单个目录里文件个数过多导致的性能升高。

如图 2 所示：

图 2 显式了比方一个数据库 dbA，外面含有 10000 张表，每 1000 张表独自划分为一个数据库，比方 dbA1,dbA2，…，db10；每个数据库的数据无论从物理还是逻辑上都独立的。

举个例子阐明下在数据库端垂直拆模的步骤以及对写入和查问申请的影响：

数据库 ytta 下有 10000 张表，表名别离为 t1 到 t10000.

(debian-ytt1:3500)|(ytta)>select count(*) from information_schema.tables where table_schema='ytta';
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.04 sec)

此处把这 10000 张表均匀垂直拆分到数据库 ytt1 到 ytt10 里。

先创立 10 个数据库 ytt1-ytt10。

ytt@debian-ytt1:~/mysql-sandboxes/3500/sandboxdata$ for i in `seq 1 10`; \
> do mysql -S mysqld.sock -uroot -proot -e "create database ytt$i";done;

别离往这 10 个数据库中 COPY 原始数据库的表构造以及数据：

这里采纳拷贝表空间的做法（前提是数据库 ytta 下的表都是单表空间，如果非单表空间倡议提前转换为单表空间）

对这个 10 个数据库别离创立对应的表构造；完了删掉初始表空间文件。

root@debian-ytt1:/home/ytt# for i in `seq 0 9`; \
> do for j in `seq 1 1000`; \
> do x=$((j + i * 1000)); \
> mysql -uroot -proot -hdebian-ytt1 -P3500 \
>  -e "use ytta;create table ytt$((i+1)).t$x like ytta.t$x;alter table ytt$((i+1)).t$x discard tablespace;"; \
> done; \
> done;

拷贝表空间须要原始表的表空间文件以及.cfg 配置文件。批量 flush 10000 张表，导出对应的 .cfg 文件。

SESSION 1：
 MySQL  Py > conn1 = 'mysql://root:root@debian-ytt1:3500/ytta'
 MySQL  Py > rs = mysql.get_classic_session(conn1);
 MySQL  Py > tb_list = []
 MySQL  Py > for i in range(1,10001):tb_list.append('t' + str(i))
 MySQL  Py > tb_lists = ','.join(tb_list);
 MySQL  Py > rs.run_sql('flush tables' + tb_lists + 'for export');
 Query OK, 0 rows affected (46.0182 sec)

测验数据库 ytta 下所有表是否曾经生成.cfg 文件 

root@debian-ytt1:/home/ytt/mysql-sandboxes/3500/sandboxdata/ytta# ls -l t* | wc -l
20000

开始拷贝表空间文件 

root@debian-ytt1:/home/ytt/mysql-sandboxes/3500/sandboxdata/ytta# for i in `seq 0 9`; \
> do for j in `seq 1 1000`; \
> do x=$((j + i * 1000)); \
> cp -rfp t$x.cfg t$x.ibd ../ytt$((i + 1))/; \
> done; \
> done;
回到 SESSION 1 来解锁数据库 ytta 下的所有表 
 MySQL  Py > rs.run_sql('unlock tables')
Query OK, 0 rows affected (4.5235 sec)

别离在各个数据库下导入表空间 

 MySQL  Py > for i in range(1,11):
          ->      for j in range(1,1001):
          ->          x = j + (i - 1) * 1000
          ->          rs.run_sql('alter table ytt'+ str(i) +'.t' + str(x)  + 'import tablespace')
          ->
Query OK, 0 rows affected, 0 warning (0.0973 sec)

以上即为在 MySQL 里垂直拆模的大抵步骤。

垂直拆模的长处是没有动表构造和数据，只是把表构造和数据隔离到不同的数据库，在逻辑层面上可读性更强，代码改写量十分小。比方原来在数据库 ytta 的 SQL 如下：

select * from t1 join t1001 using(id) join t2001 using(id);

能够更改为：

select * from ytt1.t1 join ytt2.t1001 using(id) join ytt3.t2001 using(id);

跨库写入也一样，间接带入数据库名即可。

3、垂直拆表：
拆分的根本单元是表，而非实例或者数据库模式，将一张表依照字段的被拜访频次拆分为不同的表。比方一张表有 1000 个字段，依照字段被拜访的频次来讲，前 100 个字段常常被拜访，独自划分为一个分表；前面 900 个字段不常常拜访，划分为另外一个分表。

如图 3 所示：

上图表 A 含有 5 个字段，其中 1 个主键字段，另外 4 个非主键字段；依照字段被拜访频次不同拆分为两张表 A11,A12，两张表领有同样的主键字段，有点相似咱们常说的热表与冷表。

来看看在 MySQL 里实现垂直拆表的简略示例：

建设一张 1000 个字段的表 t_large（蕴含主键 ID，字段为 1001 个），并插入 1W 行记录：

 MySQL  Py > field_list=[]
 MySQL  Py > for i in range(1,1001):field_list.append('r' + str(i) + 'int');
 MySQL  Py > field_lists=','.join(field_list);
 MySQL  Py > rs.run_sql('create table t_large(id serial primary key,' + field_lists + ')');
Query OK, 0 rows affected (0.6012 sec)
 MySQL  Py > v_list=[]
 MySQL  Py > for i in range(1000,2000):v_list.append(str(i));
 MySQL  Py > v_lists=','.join(v_list)

 MySQL  Py > for i in range(1,10001):rs.run_sql('insert into t_large select null,'+ v_lists);
Query OK, 1 row affected (0.0109 sec)

Records: 1  Duplicates: 0  Warnings: 0
 MySQL  Py > rs.run_sql('select count(*) from t_large');
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.0671 sec)

依照字段个数，把这表拆成 100 张小表，每张表字段数为 11 个（蕴含主键字段）, 并且从原表 t_large 里抽取相应的记录。

 MySQL  Py > for i in range(1,101):
          ->     f_list1 = []
          ->     f_list2 = []
          ->     for j in range(1,11):
          ->         f_list1.append('r' + str(j + (i-1)*10) + 'int')
          ->         f_list2.append('r' + str(j + (i-1)*10))
          ->     rs.run_sql('create table t_large' + str(i) +'(id serial primary key,'+ ','.join(f_list1) + ')')
          ->     rs.run_sql('insert into t_large' + str(i) +'select id,' + ','.join(f_list2) + 'from t_large')
          ->
Query OK, 10000 rows affected (1.0415 sec)

Records: 10000  Duplicates: 0  Warnings: 0

假如表 t_large 原先 10 个字段频繁的被读取，那之后值须要读取表 t_large1 即可，写法上也简洁许多。

原 SQL：

select id,r1,r2,r3,r4,r5,r6,r7,r8,r9,r10 from t_large;

能够替换为：

select * from t_large1;

表更新的性能也有肯定晋升。原表更新：

 MySQL  Py > rs.run_sql('update t_large set r1 = ceil(rand()*10000)');
Query OK, 10000 rows affected (8.4471 sec)

Rows matched: 10000  Changed: 10000  Warnings: 0

仅更新字段 r1，只需更新对应的拆分表即可。工夫上比更新原表要快几十倍。

 MySQL  Py > rs.run_sql('update t_large1 set r1 = ceil(rand()*10000)');
Query OK, 10000 rows affected (0.5160 sec)

Rows matched: 10000  Changed: 10000  Warnings: 0
总结：

垂直拆分办法基本上分为三类：垂直拆库（MySQL 里能够叫垂直拆实例）、垂直拆模（MySQL 里的垂直拆库）、垂直拆表。总体来说，垂直拆分的优缺点大抵如下：

长处：

  1. 逻辑上业务更清晰，更容易梳理。
  2. 对 IO 以及连接数在肯定水平上可能失去改善。

毛病：

  1. 单个分片性能如果达到瓶颈，很难进行更细粒度的切分。
  2. 拆分很难做到足够彻底，各个分片防止不了低频次的表关联。

对于 MySQL 的技术内容，你们还有什么想晓得的吗？连忙留言通知小编吧！

正文完
mysql
发表至： mysql
2021-09-17
 0
技术分享-MySQL-数据库如何改名
关于mysql:GreatSQL季报20211226
关于mysql:MySQL-悲观锁与乐观锁的业务应用
关于mysql:MySQL的密码修改方式