MySQL分区分表

一、为什么要分表？

数据库数据越来越大，随之而来的是单个表中数据太多。以至于查询速度慢，而且由于表的锁机制导致应用操作也受到影响，出现了数据库性能瓶颈。

MySQL中有一种机制是表锁定和行锁定，是为了保证数据的完整性。

表锁定：表示你们都不能对这张表进行操作，必须等我对表操作完成之后才可以进行操作；
行锁定：所表锁定差不多，别的sql语句必须等我这条数据操作完了，才能对这条数据进行操作；

当出现这种情况，我们就应该考虑分表或分区了！

二、MySQL分表

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的表名，然后去操作它。

将单个数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，根据一定的算法（如用hash的方式，也可以用求余（取模）的方式），让用户访问不同的表，这样数据分散到多个数据表中，减少了单个数据表的访问压力。提升了数据库访问性能。分表的目的就在于此，减小数据库的负担，缩短查询时间。

Mysql分表分为垂直切分和水平切分

垂直切分：指数据表列的拆分，把一张列比较多的表拆分为多张表通常我们按以下原则进行垂直拆分:
- 把不常用的字段单独放在一张表; 把text，blob（binary large object，二进制大对象）等大字段拆分出来放在附表中；
- 经常组合查询的列放在一张表中; 垂直拆分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用join关键起来即可；
水平拆分：指数据表行的拆分，把一张的表的数据拆成多张表来存放。水平拆分原则，通常情况下，我们使用hash、取模等方式来进行表的拆分比如一张有400W的用户表users，为提高其查询效率我们把其分成4张表users1，users2，users3，users4 通过用ID取模的方法把数据分散到四张表内Id%4= [0,1,2,3] 然后查询,更新,删除也是通过取模的方法来查询部分业务逻辑也可以通过地区，年份等字段来进行归档拆分; 进行拆分后的表，这时我们就要约束用户查询行为。比如我们是按年来进行拆分的,这个时候在页面设计上就约束用户必须要先选择年,然后才能进行查询。

三、利用merge存储引擎实现分表

注意：只有myisam引擎的原表才可以利用merge存储引擎实现分表！

merge分表，分为主表和子表，主表类似于一个壳子，逻辑上封装了子表，实际上数据都是存储在子表中的。我们可以通过主表插入和查询数据，如果清楚分表规律，也可以直接操作子表。

3.1 创建一个完整表

mysql> create database test;
mysql> create database test;
mysql> create table member(
id bigint auto_increment primary key,
name varchar(20),
sex tinyint not null default '0'
)engine=myisam default charset=utf8 auto_increment=1;
mysql> insert into member(name,sex) values('tom1',1);
mysql> insert into member(name,sex) select name,sex from member;
#将上面第二条插入语句多执行几次，即可插入大量的数据
mysql> select count(*) from member;
+----------+
| count(*) |
+----------+
|    32768 |
+----------+
#向表中插入了32768条数据

3.2 对上面完整的表进行分表

分表注意事项：

子表和主表字段定义需要一致，包括数据类型、数据长度等；

当分表完成后，所有的操作（增删改查）需要对主表进行，虽然主表并不存放实际的数据；

mysql> create table tb_member1 like member;
mysql> create table tb_member2 like member;
#创建两个分表，表结构必须和上面完整的表结构一致
mysql> create table tb_member(
id bigint auto_increment primary key,
name varchar(20),
sex tinyint not null default '0'
)engine=merge union=(tb_member1,tb_member2) insert_method=last charset=utf8;
#创建merge引擎的表作为主表，并关联上面的两个分表

注：在上面创建主表时，指定的“insert_method=last”有三个可选参数，分别是：last：表示插入到最后一张表里面；first：表示插入到第一张表里面；NO：表示该表不能做任何写入操作，只作为查询使用！

3.3 查看刚刚创建的三个表结构

mysql> desc tb_member;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| sex   | tinyint(4)  | NO   |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+
mysql> desc tb_member1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| sex   | tinyint(4)  | NO   |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+
mysql> desc tb_member2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| sex   | tinyint(4)  | NO   |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+

3.4 将数据分到两个表中

mysql> insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0;
mysql> insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;

3.5 查看主表和两个子表中的数据

mysql> select * from tb_member1 where id < 20;
#查看第一个子表的数据
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  2 | tom  |   1 |
|  4 | tom  |   1 |
|  6 | tom  |   1 |
|  8 | tom  |   1 |
| 10 | tom  |   1 |
| 12 | tom  |   1 |
| 14 | tom  |   1 |
| 16 | tom  |   1 |
| 18 | tom  |   1 |
+----+------+-----+
mysql> select * from tb_member2 where id < 20;
#查看第二个子表的数据
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  1 | tom  |   1 |
|  3 | tom  |   1 |
|  5 | tom  |   1 |
|  7 | tom  |   1 |
|  9 | tom  |   1 |
| 11 | tom  |   1 |
| 13 | tom  |   1 |
| 15 | tom  |   1 |
| 17 | tom  |   1 |
| 19 | tom  |   1 |
+----+------+-----+
mysql> select * from tb_member where id < 20;
#查看主表的数据
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  1 | tom  |   1 |
|  2 | tom  |   1 |
|  3 | tom  |   1 |
|  4 | tom  |   1 |
|  5 | tom  |   1 |
|  6 | tom  |   1 |
|  7 | tom  |   1 |
|  8 | tom  |   1 |
|  9 | tom  |   1 |
| 10 | tom  |   1 |
| 11 | tom  |   1 |
| 12 | tom  |   1 |
| 13 | tom  |   1 |
| 14 | tom  |   1 |
| 15 | tom  |   1 |
| 16 | tom  |   1 |
| 17 | tom  |   1 |
| 18 | tom  |   1 |
| 19 | tom  |   1 |
+----+------+-----+
mysql> select count(*) from tb_member;
+----------+
| count(*) |
+----------+
|    32768 |
+----------+
mysql> select count(*) from tb_member1;
+----------+
| count(*) |
+----------+
|    16384 |
+----------+
mysql> select count(*) from tb_member2;
+----------+
| count(*) |
+----------+
|    16384 |
+----------+
#查看子表及主表的数据行

注意：总表只是一个外壳，存取数据发生在一个一个的子表里面。每个子表都有自已独立的相关表文件，而主表只是一个壳，并没有完整的相关表文件，当确定主表中可以查到的数据和分表之前查到的数据完全一致时，就可以将原来的表删除了，之后对表的读写操作，都可以对分表后的主表进行。

上面三个表对应的本地文件如下：

[root@db01 ~]# cd /usr/local/mysql/data/test/
[root@db01 test]# ll tb*
-rw-r----- 1 mysql mysql   8614 4月  16 15:11 tb_member1.frm
-rw-r----- 1 mysql mysql 327680 4月  16 15:12 tb_member1.MYD
-rw-r----- 1 mysql mysql 238592 4月  16 15:12 tb_member1.MYI
-rw-r----- 1 mysql mysql   8614 4月  16 15:11 tb_member2.frm
-rw-r----- 1 mysql mysql 327680 4月  16 15:12 tb_member2.MYD
-rw-r----- 1 mysql mysql 238592 4月  16 15:12 tb_member2.MYI
-rw-r----- 1 mysql mysql   8614 4月  16 15:06 tb_member.frm
-rw-r----- 1 mysql mysql     42 4月  16 15:06 tb_member.MRG
#可以看出，能够查询到所有数据的主表的本地数据文件是非常小的，这也验证了，数据并没有存在这个主表中

3.6 对主表进行插入数据操作

mysql> insert into tb_member values(32769,'tom2',0),(32770,'tom3',0);
mysql> select count(*) from tb_member;
+----------+
| count(*) |
+----------+
|    32770 |
+----------+
mysql> select count(*) from tb_member1;
+----------+
| count(*) |
+----------+
|    16384 |
+----------+
mysql> select count(*) from tb_member2;
+----------+
| count(*) |
+----------+
|    16386 |
+----------+

可以看出，新增的两条数据都插入在了第二张表中，因为在创建主表的时候，指定的“insert_method”是last，也就是所有插入数据的操作都是对最后一张表里进行的，可以通过alter指令修改插入方法，如下：

mysql> alter table tb_member insert_method=first;

修改插入方法后，再自行对表进行插入数据的操作，可以发现所有的数据都写入了第一个表（我这里插入了四条数据），查看如下：

mysql> insert into tb_member values(32771,'tom4',0),(32772,'tom5',0),(32773,'tom6',0),(32774,'tom7',0);
mysql> select count(*) from tb_member1;
+----------+
| count(*) |
+----------+
|    16388 |
+----------+
mysql> select count(*) from tb_member2;
+----------+
| count(*) |
+----------+
|    16386 |
+----------+

若将插入方法修改为no，则表示这个表不能再插入任何数据，如下：

mysql> alter table tb_member insert_method=no;
mysql> insert into tb_member values(32775,'tom10',0);
ERROR 1036 (HY000): Table 'tb_member' is read only
#报错，不可插入，是一个只读的表

四、MySQL分区

4.1 什么是分区

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，分区后，表还是一张表，但数据散列到多个位置了。app读写的时候操作的还是表名字，db自动去组织分区的数据。

分区主要有以下两种形式：

水平分区：这种形式分区是对表的行进行分区，所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录；
垂直分区：这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

4.2 查看当前数据库是否支持分区

MySQL 5.6之前，使用下面的参数查看当前配置是否支持分区（如果为yes则表示支持分区）：

mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+---------------+
|Variable_name | Value |
+-----------------------+---------------+
| have_partition_engine | YES |
+-----------------------+------------------+

在5.6及以后采用以下方式查看：

mysql> show plugins;
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
#返回的结果中，有以上字段（如果status列为“ACTIVE”，则表示支持分区）

4.3 按照范围（range）方式的表分区

mysql> create database test01;
mysql> use test01;
mysql> create table user(
id int not null auto_increment,
name varchar(30) not null default '',
sex int(1) not null default '0',
primary key(id)
)default charset=utf8 auto_increment=1
partition by range(id)(
partition p0 values less than (3),
partition p1 values less than (6),
partition p2 values less than (9),
partition p3 values less than (12),
partition p4 values less than maxvalue );
#在上面创建的表中，当id列的值小于3将会插入到p0分区，大于3小于6的记录将会插入到p1分区，以此类推，所有id值大于12的记录都会插入到p4分区

4.4 利用存储过程插入一些数据

mysql> delimiter //    #更改默认的阶段符
mysql> create procedure adduser()
begin
declare n int;
declare summary int;
set n = 0;
while n <= 20
do
insert into test01.user(name,sex) values('tom',0);
set n = n + 1;
end while;
end //
mysql> delimiter ;
mysql> call adduser();
mysql> select * from user;
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  1 | tom  |   0 |
|  2 | tom  |   0 |
|  3 | tom  |   0 |
|  4 | tom  |   0 |
|  5 | tom  |   0 |
|  6 | tom  |   0 |
|  7 | tom  |   0 |
|  8 | tom  |   0 |
|  9 | tom  |   0 |
| 10 | tom  |   0 |
| 11 | tom  |   0 |
| 12 | tom  |   0 |
| 13 | tom  |   0 |
| 14 | tom  |   0 |
| 15 | tom  |   0 |
| 16 | tom  |   0 |
| 17 | tom  |   0 |
| 18 | tom  |   0 |
| 19 | tom  |   0 |
| 20 | tom  |   0 |
| 21 | tom  |   0 |
+----+------+-----+

4.5 到存放数据表文件的目录下查看

[root@db01 ~]# cd /usr/local/mysql/data/test01
[root@db01 test01]# ll
总用量 496
-rw-r----- 1 mysql mysql    61 4月  16 15:41 db.opt
-rw-r----- 1 mysql mysql  8614 4月  16 15:41 user.frm
-rw-r----- 1 mysql mysql 98304 4月  16 15:51 user#P#p0.ibd
-rw-r----- 1 mysql mysql 98304 4月  16 15:51 user#P#p1.ibd
-rw-r----- 1 mysql mysql 98304 4月  16 15:51 user#P#p2.ibd
-rw-r----- 1 mysql mysql 98304 4月  16 15:51 user#P#p3.ibd
-rw-r----- 1 mysql mysql 98304 4月  16 15:51 user#P#p4.ibd
#可以看到数据是被分散存到不同的文件中的，本地的文件名都是“user#P#p0...”命名的，其中p0是自定义的分区名

4.6 从information_schema系统库中的partition表中查看分区信息

mysql> select * from information_schema.partitions 
where table_schema='test01' and table_name='user'\G
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test01  #库名
                   TABLE_NAME: user   #表名
               PARTITION_NAME: p0   #分区名
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1     #分区位置，1表示第一个分区
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE     #分区方式为range
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: id      #以id列进行分区
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 3   #分区范围是3
                   TABLE_ROWS: 2      #该分区中有两行数据
………………      #省略部分内容

4.7 从分区中查询数据

mysql> select * from user partition(p0);
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  1 | tom  |   0 |
|  2 | tom  |   0 |
+----+------+-----+
mysql> select * from user partition(p1);
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  3 | tom  |   0 |
|  4 | tom  |   0 |
|  5 | tom  |   0 |
+----+------+-----+

4.8 添加分区及合并分区（需要先合并分区再新增分区）

1）添加分区

注意：由于在创建表的时候，指定的最后一个分区range是maxvalue，所以是无法直接增加分区的，如下：

mysql> alter table user add partition (partition p5 values less than (20));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
#大意是：MAXVALUE只能在最后一个分区定义中使用

但也不可以将最后定义了maxvalue的分区直接删除，因为删除分区的话，分区中的数据也会丢失，所以，如果需要新增分区的正确做法，应该是先合并分区，再新增分区，这样才可以保证数据的完整性，如下：

mysql> alter table user  reorganize partition p4 into
(partition p03 values less than (15),partition p04 values less than maxvalue );
将最后一个分区分为两个分区，一个是自己所需要的分区，最后一个分区还是maxvalue（也必须是maxvalue），这样就完成了添加分区
[root@db01 test01]# ll      #查看本地表文件
总用量 592
-rw-r----- 1 mysql mysql    61 4月  16 15:41 db.opt
-rw-r----- 1 mysql mysql  8614 4月  16 16:16 user.frm
-rw-r----- 1 mysql mysql 98304 4月  16 16:16 user#P#p03.ibd
-rw-r----- 1 mysql mysql 98304 4月  16 16:16 user#P#p04.ibd
-rw-r----- 1 mysql mysql 98304 4月  16 15:51 user#P#p0.ibd
-rw-r----- 1 mysql mysql 98304 4月  16 15:51 user#P#p1.ibd
-rw-r----- 1 mysql mysql 98304 4月  16 15:51 user#P#p2.ibd
-rw-r----- 1 mysql mysql 98304 4月  16 15:51 user#P#p3.ibd
mysql> select * from user partition(p03);
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 12 | tom  |   0 |
| 13 | tom  |   0 |
| 14 | tom  |   0 |
+----+------+-----+
mysql> select * from user partition(p04);
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 15 | tom  |   0 |
| 16 | tom  |   0 |
| 17 | tom  |   0 |
| 18 | tom  |   0 |
| 19 | tom  |   0 |
| 20 | tom  |   0 |
| 21 | tom  |   0 |
+----+------+-----+
#查看新增分区中的数据

2）合并分区

mysql> alter table user
reorganize partition p0,p1,p2,p3 into
(partition p02 values less than (12));
#将p0、p1、p2、p3四个分区合并为p02
mysql> select * from user partition(p02);
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  1 | tom  |   0 |
|  2 | tom  |   0 |
|  3 | tom  |   0 |
|  4 | tom  |   0 |
|  5 | tom  |   0 |
|  6 | tom  |   0 |
|  7 | tom  |   0 |
|  8 | tom  |   0 |
|  9 | tom  |   0 |
| 10 | tom  |   0 |
| 11 | tom  |   0 |
+----+------+-----+
#查看合并后的数据
[root@db01 test01]# ll       #本地文件
总用量 304
-rw-r----- 1 mysql mysql    61 4月  16 15:41 db.opt
-rw-r----- 1 mysql mysql  8614 4月  16 16:20 user.frm
-rw-r----- 1 mysql mysql 98304 4月  16 16:20 user#P#p02.ibd
-rw-r----- 1 mysql mysql 98304 4月  16 16:16 user#P#p03.ibd
-rw-r----- 1 mysql mysql 98304 4月  16 16:16 user#P#p04.ibd

4.9 删除分区

mysql> alter table user drop partition p02;
#删除分区p02
[root@db01 test01]# ll
总用量 208
-rw-r----- 1 mysql mysql    61 4月  16 15:41 db.opt
-rw-r----- 1 mysql mysql  8614 4月  16 16:22 user.frm
-rw-r----- 1 mysql mysql 98304 4月  16 16:16 user#P#p03.ibd
-rw-r----- 1 mysql mysql 98304 4月  16 16:16 user#P#p04.ibd
#分区被删除后，分区中的数据也将被删除，删除分区p02的表中所有数据

posted @ 2020-12-27 14:10 吕振江阅读(251) 评论(0) 编辑收藏举报

刷新页面返回顶部

吕振江的博客