通过实例学习MySQL分区表原理及常用操作

我们试着想一想, 在生产环境中什么最重要? 我感觉在生产环境中应该没有什么比数据跟更为重要. 那么我们该如何保证数据不丢失、或者丢失后可以快速恢复呢?只要看完这篇大家应该就能对MySQL中数据备份有一定了解

1、分区表含义

分区表定义指根据可以设置为任意大小的规则,跨文件系统分配单个表的多个部分。实际上,表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数,这在MySQL中它可以是模数,或者是简单的匹配一个连续的数值区间或数值列表,或者是一个内部HASH函数,或一个线性HASH函数。

分表与分区的区别在于:分区从逻辑上来讲只有一张表,而分表则是将一张表分解成多张表

2、分区表优点

1)分区表更容易维护。对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。

2)一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。

3)优化查询。涉及到例如SUM()和COUNT(),可以在多个分区上并行处理,最终结果只需通过总计所有分区得到的结果。

4)通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。

3、分区表限制

1)一个表最多只能有1024个分区;

2) MySQL5.1中,分区表达式必须是整数,或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持;

3)如果分区字段中有主键或者唯一索引的列,那么多有主键列和唯一索引列都必须包含进来。即:分区字段要么不包含主键或者索引列,要么包含全部主键和索引列;

4)分区表中无法使用外键约束;

5)MySQL的分区适用于一个表的所有数据和索引,不能只对表数据分区而不对索引分区,也不能只对索引分区而不对表分区,也不能只对表的一部分数据分区。

6)分区键必须是INT类型,或者通过表达式返回INT类型,可以为NULL。唯一的例外是当分区类型为KEY分区的时候,可以使用其他类型的列作为分区键(BLOB or TEXT 列除外)

7)如果表中有主键和唯一索引,按主键字段进行分区时,唯一索引列应该包含分区键。

8)目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引。

9)对象限制(分区表达式不能出现Stored functions, stored procedures, UDFs, orplugins,Declared variables or user variables.)

10)运算限制(支持加减乘等运算出现在分区表达式,但是运算后的结果必须是一个INT或者NULL。支持DIV,不支持/,|, &, ^, <<, >>, and ~ 不允许出现在分区表达式中)

11)sql_mode限制(官方强烈建议你在创建分区表后,永远别改变mysql的sql_mode。因为在不同的模式下,某些函数或者运算返回的结果可能会不一样)

12)不支持query_cache和INSERT DELAYED

13)分区键不能是一个子查询(即使是子查询返回的是int值或者null.)

14)子分区限制(只有RANG和LIST分区能进行子分区。HASH和KEY分区不能进行子分区并且子分区必须是HASH 或 KEY类型)

4、分区类型

1)水平分区(根据列属性按行分)

如:一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录。

水平分区的几种模式:

* Range(范围):这种模式允许DBA将数据划分不同范围。

如:可以将一个表通过年份划分成三个分区,80年代(1980's)的数据,90年代(1990's)的数据以及任何在2000年(包括2000年)后的数据。

* Hash(哈希):这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。

如:可以建立一个对表主键进行分区的表。

* Key(键值):上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。

* List(预定义列表):这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如:DBA建立了一个横跨三个分区的表,分别根据2004年2005年和2006年值所对应的数据。

* Columns分区是对range,list分区的补充,弥补了后两者只支持整型数分区(或者通过转换为整型数),使得支持数据类型增加很多(所有整数类型,日期时间类型,字符类型),还支持多列分区。

注:在多列分区表上插入数据时,采用元组的比较,即多列排序,先根据field1排序,再根据field2排序,根据排序结果来来分区存储数据。

* Composite(复合模式):以上模式的组合使用。

如:在初始化已经进行了Range范围分区的表上,可以对其中一个分区再进行hash哈希分区。

垂直分区(按列分):

如:一个包含了大text和BLOB列的表,这些text和BLOB列又不经常被访问,可以把这些不经常使用的text和BLOB划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。

注意:子分区(关键字subparttition):使用RANGE或LIST分区可以再次分割形成子分区,子分区可以是HASH分区或者KEY分区。建议在多磁盘上使用。

查看是否有支持Partition分区表

?

1

2

3

4

5

6

7

8

9

10

mysql> SHOW PLUGINS ;

+—————————-+———-+——————–+———+———+

| Name | Status | Type | Library | License |

+—————————-+———-+——————–+———+———+

| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |

+—————————-+———-+——————–+———+———+

或使用

mysql> SELECT PLUGIN_NAME as Name, PLUGIN_VERSION as Version, PLUGIN_STATUS as Status

-> FROM INFORMATION_SCHEMA.PLUGINS

-> WHERE PLUGIN_TYPE='STORAGE ENGINE';

注意:MySQL 5.6.1 之前的版本,可以下命令查看 have_partitioning 参数,新的版本已移除该参数。

?

1

mysql> SHOW VARIABLES LIKE '%partition%';

5、实战常用分区表几种模式

1)使用RANGE分区模式

####创建测试表t1,并插入接近400万行数据,再没有分区的情况下,对查询某一条件耗时

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

mysql> CREATE TABLE `t1` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',

`pid` int(10) unsigned NOT NULL COMMENT '产品ID',

`price` decimal(15,2) NOT NULL COMMENT '单价',

`num` int(11) NOT NULL COMMENT '购买数量',

`uid` int(10) unsigned NOT NULL COMMENT '客户ID',

`atime` datetime NOT NULL COMMENT '下单时间',

`utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间',

`isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识',

PRIMARY KEY (`id`,`atime`)

)

INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());

INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00');

INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00');

INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00');

INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00');

INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00');

INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00');

INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00');

/**********************************主从复制大量数据******************************/

mysql> INSERT INTO `t1`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `t1`;

mysql> SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();

1048576 rows in set (5.62 sec) #没有分区表情况耗时5.62s

如果是针对已有的表进行表分区,可以使用ALTER TABLE来进行更改表为分区表,这个操作会创建一个分区表,然后自动进行数据copy然后删除原表。

注: 这种会使服务器资源消耗比较大(400多万数据要1分多钟)

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

mysql> ALTER TABLE t1 PARTITION BY RANGE (YEAR(atime))

-> (

-> PARTITION p0 VALUES LESS THAN (2016),

-> PARTITION p1 VALUES LESS THAN (2017),

-> PARTITION p2 VALUES LESS THAN (2018),

-> PARTITION p3 VALUES LESS THAN MAXVALUE );

Query OK, 4194304 rows affected (1 min 8.32 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM `t1`; #查看分区情况

+—-+————-+——-+————-+——+—————+——+———+——+———+———-+——-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————-+——+—————+——+———+——+———+———-+——-+

| 1 | SIMPLE | t1 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 4180974 | 100.00 | NULL |

+—-+————-+——-+————-+——+—————+——+———+——+———+———-+——-+

1 row in set, 2 warnings (0.00 sec)

同样用上面的查询测试结果

?

1

2

3

4

5

6

7

8

9

mysql> SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();

1048576 rows in set (4.46 sec) #与上面没有分区查询执行的时间相比少了接近1s

mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); #查看查询使用的分区情况

+—-+————-+——-+————+——+—————+——+———+——+———+———-+————-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————+——+—————+——+———+——+———+———-+————-+

| 1 | SIMPLE | t1 | p0,p1,p2 | ALL | NULL | NULL | NULL | NULL | 3135804 | 3.33 | Using where |

+—-+————-+——-+————+——+—————+——+———+——+———+———-+————-+

1 row in set, 2 warnings (0.00 sec)

同时也要注意,进行表分区以后,mysql存放的数据文件夹中该表的存放文件也被拆分为多个

?

1

2

3

4

5

-rw-r—– 1 mysql mysql 8.7K 2月 14 14:49 t1.frm

-rw-r—– 1 mysql mysql 36M 2月 14 14:50 t1#P#p0.ibd

-rw-r—– 1 mysql mysql 64M 2月 14 14:50 t1#P#p1.ibd

-rw-r—– 1 mysql mysql 92M 2月 14 14:50 t1#P#p2.ibd

-rw-r—– 1 mysql mysql 64M 2月 14 14:50 t1#P#p3.ibd

实际生产环境中,大多是采用另外一种方式:新建一个和原来表一样的分区表,然后把数据从原表导出,接着导入新表,最后建立普通索引。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

mysql> CREATE TABLE `t2` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',

`pid` int(10) unsigned NOT NULL COMMENT '产品ID',

`price` decimal(15,2) NOT NULL COMMENT '单价',

`num` int(11) NOT NULL COMMENT '购买数量',

`uid` int(10) unsigned NOT NULL COMMENT '客户ID',

`atime` datetime NOT NULL COMMENT '下单时间',

`utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间',

`isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识',

PRIMARY KEY (`id`,`atime`)

)

PARTITION BY RANGE COLUMNS(atime) (

PARTITION p0 VALUES LESS THAN ('2016-01-01'),

PARTITION p1 VALUES LESS THAN ('2016-02-01'),

PARTITION p2 VALUES LESS THAN ('2016-03-01'),

PARTITION p3 VALUES LESS THAN ('2016-04-01'),

PARTITION p4 VALUES LESS THAN ('2016-05-01'),

PARTITION p5 VALUES LESS THAN ('2016-06-01'),

PARTITION p6 VALUES LESS THAN ('2016-07-01'),

PARTITION p7 VALUES LESS THAN ('2016-08-01'),

PARTITION p8 VALUES LESS THAN ('2016-09-01'),

PARTITION p9 VALUES LESS THAN ('2016-10-01'),

PARTITION p10 VALUES LESS THAN ('2016-11-01'),

PARTITION p11 VALUES LESS THAN ('2016-12-01'),

PARTITION p12 VALUES LESS THAN ('2017-01-01'),

PARTITION p13 VALUES LESS THAN ('2017-02-01'),

PARTITION p14 VALUES LESS THAN ('2017-03-01'),

PARTITION p15 VALUES LESS THAN ('2017-04-01'),

PARTITION p16 VALUES LESS THAN ('2017-05-01'),

PARTITION p17 VALUES LESS THAN ('2017-06-01'),

PARTITION p18 VALUES LESS THAN ('2017-07-01'),

PARTITION p19 VALUES LESS THAN ('2017-08-01'),

PARTITION p20 VALUES LESS THAN ('2017-09-01'),

PARTITION p21 VALUES LESS THAN ('2017-10-01'),

PARTITION p22 VALUES LESS THAN ('2017-11-01'),

PARTITION p23 VALUES LESS THAN ('2017-12-01'),

PARTITION p24 VALUES LESS THAN ('2018-01-01'),

PARTITION p25 VALUES LESS THAN ('2018-02-01'),

PARTITION p26 VALUES LESS THAN ('2018-03-01'),

PARTITION p27 VALUES LESS THAN ('2018-04-01'),

PARTITION p28 VALUES LESS THAN ('2018-05-01'),

PARTITION p29 VALUES LESS THAN ('2018-06-01'),

PARTITION p30 VALUES LESS THAN ('2018-07-01'),

PARTITION p31 VALUES LESS THAN ('2018-08-01'),

PARTITION p32 VALUES LESS THAN ('2018-09-01'),

PARTITION p33 VALUES LESS THAN ('2018-10-01'),

PARTITION p34 VALUES LESS THAN ('2018-11-01'),

PARTITION p35 VALUES LESS THAN ('2018-12-01'),

PARTITION p36 VALUES LESS THAN MAXVALUE

);

注:表主键只有id,而分区字段是atime, 这里主键要修改为 id,stsdate 联合主键,分区表要求分区字段要是主键或者是主键的一部分!!!

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

mysql> EXPLAIN PARTITIONS SELECT * FROM `t2`\\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t2

partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 2 warnings (0.00 sec)

*******************************************插入数据*************************************************

INSERT INTO `t2`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `t1`;

Query OK, 4194304 rows affected (1 min 18.54 sec)

Records: 4194304 Duplicates: 0 Warnings: 0

或采用导出数据再导入数据,可再添加索引

?

1

mysqldump -u dbname -p –no-create-info dbname t2 > t2.sq

修改表名,导入数据,测试下ok,删除原来的表。

2)使用LIST分区模式(如果原表存在主键强烈创建新表时,把原主键和要分区字段作为联合主键一并创建)

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

mysql> CREATE TABLE `tb01` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',

`pid` int(10) unsigned NOT NULL COMMENT '产品ID',

`price` decimal(15,2) NOT NULL COMMENT '单价',

`num` int(11) NOT NULL COMMENT '购买数量',

`uid` int(10) unsigned NOT NULL COMMENT '客户ID',

`atime` datetime NOT NULL COMMENT '下单时间',

`utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间',

`isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识',

PRIMARY KEY (`id`,`num`)

);

*****************************插入测试数据******************************************************

INSERT INTO `tb01`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `tb`;

Query OK, 3145728 rows affected (46.26 sec)

Records: 3145728 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tb01 PARTITION BY LIST(num)

(

PARTITION pl01 VALUES IN (1,3),

PARTITION pl02 VALUES IN (2,4),

PARTITION pl03 VALUES IN (5,7),

PARTITION pl04 VALUES IN (6,8),

PARTITION pl05 VALUES IN (9,10)

);

Query OK, 3145728 rows affected (48.86 sec)

Records: 3145728 Duplicates: 0 Warnings: 0

存放mysql数据文件中生成,以下文件

-rw-r—– 1 mysql mysql 8.7K 2月 15 11:35 tb01.frm

-rw-r—– 1 mysql mysql 56M 2月 15 11:36 tb01#P#pl01.ibd

-rw-r—– 1 mysql mysql 32M 2月 15 11:36 tb01#P#pl02.ibd

-rw-r—– 1 mysql mysql 36M 2月 15 11:36 tb01#P#pl03.ibd

-rw-r—– 1 mysql mysql 36M 2月 15 11:36 tb01#P#pl04.ibd

-rw-r—– 1 mysql mysql 52M 2月 15 11:36 tb01#P#pl05.ibd

?

1

2

3

4

5

6

7

mysql> EXPLAIN PARTITIONS SELECT * FROM `tb01`;

+—-+————-+——-+————————–+——+—————+——+———+——+———+———-+——-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————————–+——+—————+——+———+——+———+———-+——-+

| 1 | SIMPLE | tb01 | pl01,pl02,pl03,pl04,pl05 | ALL | NULL | NULL | NULL | NULL | 3136392 | 100.00 | NULL |

+—-+————-+——-+————————–+——+—————+——+———+——+———+———-+——-+

1 row in set, 2 warnings (0.00 sec)

3)COLUMNS分区

创建多列分区表tb02,这里两列都不是联合主键

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

mysql> CREATE TABLE tb02(

-> a int not null,

-> b int not null

-> )

-> PARTITION BY RANGE COLUMNS(a,b)(

-> partition p0 values less than(0,10),

-> partition p1 values less than(10,20),

-> partition p2 values less than(10,30),

-> partition p3 values less than(maxvalue,maxvalue)

-> );

mysql> EXPLAIN PARTITIONS SELECT * FROM `tb02`; #查看

+—-+————-+——-+————-+——+—————+——+———+——+——+———-+——-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————-+——+—————+——+———+——+——+———-+——-+

| 1 | SIMPLE | tb02 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |

+—-+————-+——-+————-+——+—————+——+———+——+——+———-+——-+

1 row in set, 2 warnings (0.00 sec)

mysql> insert into tb02 values (11,13); #手工插入测试数据

Query OK, 1 row affected (0.01 sec)

mysql> select PARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.partitions where table_schema=schema() and table_name='tb02';

+—————-+———————-+————+

| PARTITION_NAME | PARTITION_EXPRESSION | TABLE_ROWS |

+—————-+———————-+————+

| p0 | `a`,`b` | 0 |

| p1 | `a`,`b` | 0 |

| p2 | `a`,`b` | 0 |

| p3 | `a`,`b` | 1 |

+—————-+———————-+————+

4 rows in set (0.03 sec)

4)Hase分区

HASH主要是为了让数据在设定个数的分区中尽可能分布平均,执行哈希分区时,mysql会对分区键执行哈希函数,以确定数据放在哪个分区中。HASH分区分为常规HASH分区和线性HASH分区,前者使用取模算法,后者使用线性2的幂的运算规则。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

CREATE TABLE `tb03` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',

`pid` int(10) unsigned NOT NULL COMMENT '产品ID',

`price` decimal(15,2) NOT NULL COMMENT '单价',

`num` int(11) NOT NULL COMMENT '购买数量',

`uid` int(10) unsigned NOT NULL COMMENT '客户ID',

`atime` datetime NOT NULL COMMENT '下单时间',

`utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间',

`isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识',

PRIMARY KEY (`id`)

)

PARTITION BY HASH(id) partitions 4;

插入2行数据:

INSERT INTO tb03(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());

INSERT INTO tb03(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());

mysql> explain partitions select * from tb03 where id=1;

+—-+————-+——-+————+——-+—————+———+———+——-+——+———-+——-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————+——-+—————+———+———+——-+——+———-+——-+

| 1 | SIMPLE | tb03 | p1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |

+—-+————-+——-+————+——-+—————+———+———+——-+——+———-+——-+

1 row in set, 2 warnings (0.00 sec)

mysql> explain partitions select * from tb03 where id=2;

+—-+————-+——-+————+——-+—————+———+———+——-+——+———-+——-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————+——-+—————+———+———+——-+——+———-+——-+

| 1 | SIMPLE | tb03 | p2 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |

+—-+————-+——-+————+——-+—————+———+———+——-+——+———-+——-+

1 row in set, 2 warnings (0.00 sec)

注意:HASH分区虽然尽可能让数据平均地分布在每个分区上,提高了查询效率,但增加了分区管理的代价,比如以前有5个分区,现在要加上一个分区,算法有mod(expr,5)变成(expr,6),原5个分区的数据大部分要重新计算重新分区。虽然使用线性HASH分区会降低分区管理的代价,但是数据却没有常规HASH分布得那么均匀。

5)KEY分区

KEY分区类似与HASH分区,但是不能自定义表达式,不过支持分区键的类型很多,除Text,Blob等文本类型。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

CREATE TABLE `tb04` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',

`pid` int(10) unsigned NOT NULL COMMENT '产品ID',

`price` decimal(15,2) NOT NULL COMMENT '单价',

`num` int(11) NOT NULL COMMENT '购买数量',

`uid` int(10) unsigned NOT NULL COMMENT '客户ID',

`atime` datetime NOT NULL COMMENT '下单时间',

`utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间',

`isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识',

PRIMARY KEY (`id`)

)

PARTITION BY KEY(id) partitions 4;

插入2行数据:

INSERT INTO tb04(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());

INSERT INTO tb04(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());

#用执行任务查看记录落在分区情况

mysql> explain partitions select * from tb04 where id=1;

+—-+————-+——-+————+——-+—————+———+———+——-+——+———-+——-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————+——-+—————+———+———+——-+——+———-+——-+

| 1 | SIMPLE | tb04 | p0 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |

+—-+————-+——-+————+——-+—————+———+———+——-+——+———-+——-+

1 row in set, 2 warnings (0.00 sec)

mysql> explain partitions select * from tb04 where id=2;

+—-+————-+——-+————+——-+—————+———+———+——-+——+———-+——-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————+——-+—————+———+———+——-+——+———-+——-+

| 1 | SIMPLE | tb04 | p3 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |

+—-+————-+——-+————+——-+—————+———+———+——-+——+———-+——-+

1 row in set, 2 warnings (0.00 sec)

6)分区表管理

建议在生产环境中尽量不要修改分区,alter会读出存在旧表中的数据,再存入新定义的表中,过程IO将很大,而且全表都会锁住。

*1*删除分区:示例以上面tb01表

–未删除p05分区查询数据,主要验证当删除分区数据是否被删除

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

mysql> select count(1) from tb01 where num=10;

+———-+

| count(1) |

+———-+

| 524288 |

+———-+

1 row in set (0.37 sec)

mysql> alter table tb01 drop partition pl05; #删除pl05分区,如:一次性删除多个分区,alter table tb01 drop partition pl04,pl05;

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select count(1) from tb01 where num=10; #结果数据也被删除,慎重操作

+———-+

| count(1) |

+———-+

| 0 |

+———-+

1 row in set (0.01 sec)

注意:删除分区会删除数据,谨慎操作;不可以删除hash或者key分区。

*2*增加分区

注:新分区的值不能包含任意一个现有分区中值列表中的值,否则报错;新增分区会重新整理数据,原有数据不会丢失。有MAXVALUE值后,直接不能直接加分区,如示例以上面的t1表为例子。

?

1

2

3

4

5

6

mysql> ALTER TABLE t1 ADD PARTITION (PARTITION P4 VALUES LESS THAN (2018) ) ;

ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

示例:把tb01上面删除的pl05分区添加

mysql> ALTER TABLE tb01 ADD PARTITION(PARTITION pl05 VALUES IN (9,10));

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

*3*分解分区

注:Reorganize partition关键字可以对表的部分分区或全部分区进行修改,并且不会丢失数据。分解前后分区的整体范围应该一致。

示例:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

mysql> create table tb05

-> (dep int,

-> birthdate date,

-> salary int

-> )

-> partition by range(salary)

-> (

-> partition p1 values less than (1000),

-> partition p2 values less than (2000),

-> partition p3 values less than maxvalue

-> );

Query OK, 0 rows affected (0.08 sec)

****插入一条测试数据

mysql> insert tb05 values(1,'2016-03-06',80);

Query OK, 1 row affected (0.01 sec)

mysql>alter table tb05 reorganize partition p1 into(

partition p01 values less than (100),

partition p02 values less than (1000)

); —-不会丢失数据

mysql> explain partitions select * from tb05 where salary=80; #查看已经落在新的分区p01上

+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+

| 1 | SIMPLE | tb05 | p01 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+

1 row in set, 2 warnings (0.00 sec)

*4*合并分区

注:把2个分区合并为一个。

示例:把上面的tb05表中分解的p01和p02合并至p1上

?

1

2

3

4

5

6

7

8

9

10

mysql> alter table tb05 reorganize partition p01,p02 into(partition p1 values less than (1000)); –不会丢失数据

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain partitions select * from tb05 where salary=80;

+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+

| 1 | SIMPLE | tb05 | p1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+

1 row in set, 2 warnings (0.00 sec)

*5*重新定义hash分区表:

RANGE和LIST分区在重新定义时,只能重新定义相邻的分区,不可以跳过分区,并且重新定义的分区区间必须和原分区区间一致,也不可以改变分区的类型。

示例:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`;

+—-+————-+——-+————-+——+—————+——+———+——+——+———-+——-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————-+——+—————+——+———+——+——+———-+——-+

| 1 | SIMPLE | tb03 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |

+—-+————-+——-+————-+——+—————+——+———+——+——+———-+——-+

1 row in set, 2 warnings (0.00 sec)

mysql> Alter table tb03 partition by hash(id)partitions 8; #不会丢失数据

Query OK, 4 rows affected (0.13 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`;

+—-+————-+——-+————————-+——+—————+——+———+——+——+———-+——-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————————-+——+—————+——+———+——+——+———-+——-+

| 1 | SIMPLE | tb03 | p0,p1,p2,p3,p4,p5,p6,p7 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |

+—-+————-+——-+————————-+——+—————+——+———+——+——+———-+——-+

1 row in set, 2 warnings (0.02 sec)

*6*删除表的所有分区:

示例:删除tb03表所有分区

?

1

2

3

4

5

6

7

8

9

10

mysql> Alter table tb03 remove partitioning; #不会丢失数据

Query OK, 4 rows affected (0.07 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`;

+—-+————-+——-+————+——+—————+——+———+——+——+———-+——-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+————+——+—————+——+———+——+——+———-+——-+

| 1 | SIMPLE | tb03 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |

+—-+————-+——-+————+——+—————+——+———+——+——+———-+——-+

1 row in set, 2 warnings (0.00 sec)

*7*整理分区碎片

注:如果从分区中删除了大量的行,或者对一个带有可变长度的行(也就是说,有VARCHAR,BLOB,或TEXT类型的列)作了许多修改,可以使用“ALTER TABLE … OPTIMIZE PARTITION”来收回没有使用的空间,并整理分区数据文件的碎片。

?

1

ALTER TABLE tb03 optimize partition p1,p2;

*8*分析分区:

读取并保存分区的键分布。

?

1

2

3

4

5

6

7

mysql> ALTER TABLE tb04 CHECK partition p1,p2;

+————–+——-+———-+———-+

| Table | Op | Msg_type | Msg_text |

+————–+——-+———-+———-+

| testsms.tb04 | check | status | OK |

+————–+——-+———-+———-+

1 row in set (0.01 sec)

*9*检查分区:

可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。这个命令可以告诉tb04表分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况,使用“ALTER TABLE … REPAIR PARTITION”来修补该分区。

?

1

2

3

4

5

6

7

mysql> ALTER TABLE tb04 CHECK partition p1,p2;

+————–+——-+———-+———-+

| Table | Op | Msg_type | Msg_text |

+————–+——-+———-+———-+

| testsms.tb04 | check | status | OK |

+————–+——-+———-+———-+

1 row in set (0.01 sec)

6、实际生产简单应用

场景:之前有个没有分区的大数据量表SmsSend(例表,大概2800万行记录),统计过程非常的耗时,考虑用年分区,并且对历史数据库进行备份,把过去2014年的数据转移至新的备份表smssendbak。如在线重定义比较耗时间,可采用exchange处理!

1)查看当前SmsSend表

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

mysql> SHOW CREATE TABLE SmsSend; #查看创建信息,未进行分区

| SmsSend | CREATE TABLE `SmsSend` (

`Guid` char(36) NOT NULL COMMENT '唯一标识',

`SID` varbinary(85) DEFAULT NULL COMMENT '商家唯一编号',

`Mobile` longtext NOT NULL COMMENT '接收手机号(以","分割)',

`SmsContent` varchar(500) NOT NULL COMMENT '短信内容',

`SmsCount` int(11) NOT NULL DEFAULT '1' COMMENT '条数',

`Status` int(11) NOT NULL COMMENT '当前状态(0,等待发送;1,发送成功;-1,发送失败)',

`SendChanelKeyName` varchar(20) DEFAULT NULL COMMENT '发送通道标识',

`SendTime` datetime NOT NULL COMMENT '发送成功时间',

`SendType` int(11) NOT NULL DEFAULT '1' COMMENT '短信发送类型(1,单发;2,群发)',

`ReceiveTime` datetime DEFAULT NULL COMMENT '接收到回复报告的时间',

`Priority` int(11) NOT NULL DEFAULT '0' COMMENT '优先级',

`UserAccount` varchar(50) DEFAULT NULL COMMENT '操作员',

`ChainStoreGuid` char(36) DEFAULT NULL COMMENT '操作店面唯一标识',

`RelationKey` longtext COMMENT '回复报告关联标识',

`Meno` text COMMENT '备注',

`IsFree` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否免费'

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

mysql> SELECT COUNT(*) FROM SmsSend; #行记录

+———-+

| COUNT(*) |

+———-+

| 28259803 |

+———-+

1 row in set (1 min 52.60 sec)

#可得知大数据表下在线分区比较慢并且耗性能

mysql> ALTER TABLE SmsSend PARTITION BY RANGE (YEAR(SendTime))

-> (

-> PARTITION py01 VALUES LESS THAN (2015),

-> PARTITION py02 VALUES LESS THAN (2016),

-> PARTITION py03 VALUES LESS THAN (2017) );

Query OK, 28259803 rows affected (20 min 36.05 sec)

Records: 28259803 Duplicates: 0 Warnings: 0

#查看分区记录数

mysql> select count(1) from SmsSend partition(py01);

+———-+

| count(1) |

+———-+

| 10 |

+———-+

1 row in set (0.00 sec)

mysql> explain partitions select * from SmsSend where SendTime < '2015-01-01'; #2014年的数据落在第一分区

+—-+————-+———+————+——+—————+——+———+——+——+———-+————-+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+———+————+——+—————+——+———+——+——+———-+————-+

| 1 | SIMPLE | SmsSend | py01 | ALL | NULL | NULL | NULL | NULL | 10 | 33.33 | Using where |

+—-+————-+———+————+——+—————+——+———+——+——+———-+————-+

1 row in set, 2 warnings (0.00 sec)

mysql> select count(1) from SmsSend partition(py02);

+———-+

| count(1) |

+———-+

| 10 |

+———-+

1 row in set (0.00 sec)

2)快速创建一个smssendbak备份表与原SmsSend表结构一致,并删除备份表所有分区

?

1

2

3

4

5

mysql> CREATE TABLE smssendbak LIKE SmsSend;

Query OK, 0 rows affected (0.14 sec)

mysql> ALTER TABLE smssendbak REMOVE PARTITIONING;

Query OK, 0 rows affected (0.19 sec)

Records: 0 Duplicates: 0 Warnings: 0

3)使用EXCHANGE PARTITION转移分区数据至备份表,并查看原来表分区记录以及新备份表

smssendbak记录

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

mysql> ALTER TABLE SmsSend EXCHANGE PARTITION py01 WITH TABLE smssendbak;

Query OK, 0 rows affected (0.13 sec)

mysql> select count(1) from SmsSend partition(py01); #对比上面原SmsSend表分区的记录

+———-+

| count(1) |

+———-+

| 0 |

+———-+

1 row in set (0.00 sec)

mysql> SELECT COUNT(1) FROM smssendbak; #查看新smssendbak备份表转移记录

+———-+

| COUNT(1) |

+———-+

| 10 |

+———-+

1 row in set (0.00 sec)

*****************测试使用的表***********************************************************************

创建一个基础测试表:

CREATE TABLE `tb` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',

`pid` int(10) unsigned NOT NULL COMMENT '产品ID',

`price` decimal(15,2) NOT NULL COMMENT '单价',

`num` int(11) NOT NULL COMMENT '购买数量',

`uid` int(10) unsigned NOT NULL COMMENT '客户ID',

`atime` datetime NOT NULL COMMENT '下单时间',

`utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间',

`isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识',

) ;

插入数据:

INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());

INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00');

INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00');

INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00');

INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00');

INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00');

INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00');

************************************插入大量的数据(建议百万以上)*************************************

INSERT INTO `tb`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `tb`;

****注意,如果要删除自增长的主键id(修改过程中,建议该库改为只读),如下操作:

  Alter table tb change id id int(10); #先删除自增长

  Alter table tb drop primary key;#删除主建

Alter table tb change id id int not null auto_increment; #如果想重新设置为自增字段

Alter table tb auto_increment=1; #自增起始

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持钦钦技术栈。

原文链接:https://blog.51cto.com/daisywei/1900331

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/16157/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年 8月 28日 11:02:07
下一篇 2022年 8月 28日 11:02:14

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!