mysql索引表交换分区_MySQL5.6 新性能之交换分区(exchange partitions)

最新推荐文章于 2024-07-19 03:30:00 发布

weixin_39898150

最新推荐文章于 2024-07-19 03:30:00 发布

阅读量610

点赞数

文章标签： mysql索引表交换分区

本文链接：https://blog.youkuaiyun.com/weixin_39898150/article/details/114898352

版权

MySQL5.6 新性能之交换分区(exchange partitions)

说明：文章内容起源于网络并结合自己的实验而得；但参考的文章地址当时没记录下来，如果发现有侵权问题，请留言。

分区是MySQL5.1版本时添加了对分区的支持。

分区的过程是将一个表或者索引分解为多个更小，更可管理的部分，每个分区都是独立的对象，可以独立处理，也可以作为一个更大的对象的一部分进行处理。

MySQL数据库支持的分区类型为水平分区，并不支持垂直分区。

此外MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指数据存放在各个分区中，而索引存放在一个对象中。目前MySQL数据库还不支持全局分区。

MySQL5.6 ，让分区交换成了现实。只需要通过ALTER TABLE ...EXCHANGE PARTITION语句即可，也就是说，可以在短暂的时间内将某一个分区内的数据移到其他(独立)表中。

假设有分区表P和未分区表T，他们除去分区属性不同外，其他列属性都一样，那么可用EXCHANGE PARTITION将P的某个分区和T表中的数据进行互换；也就是P中某分区的数据会剪切的存入T，而T中数据会剪切的存入P分区。利用该功能可用将T表数据载入分区中，也可用将分区数据导出到T表中。

该操作需要永远ALTER, INSERT, CREATE, DROP权限。

一、实验

实验一：交换主分区

备注：matchedVideo中分区viacom有2410806行记录，如果要将这部分数据备份出来，MySQL 5.1需要新建临时表，然后把这部分数据导出去；

1>备份某一分区数据

mysql> create table matchedVideo4 like matchedVideo;

Query OK, 0 rows affected (1 min 13.84 sec)

mysql> insert into matchedVideo4 select * from matchedVideo where company_id = 14;

Query OK, 2410806 rows affected (12 min 28.41 sec)

Records: 2410806 Duplicates: 0 Warnings: 0

2>删除某一个分区数据(分区保留)

mysql> delete from matchedVideo4 where company_id = 14;

Query OK, 2410806 rows affected (8 min 47.76 sec)

-- 删除分区的时间为insert+delete的执行时间约为20分钟。

然而MySQL5.6在性能上有了很大的改进，大大的缩短了时间。

mysql> create table matchedVideo2 like matchedVideo;

Query OK, 0 rows affected (1 min 13.84 sec)

mysql> ALTER TABLE matchedVideo2 REMOVE PARTITIONING;

Query OK, 0 rows affected (16.88 sec)

Records: 0 Duplicates: 0 Warnings: 0

-- 删除分区信息，使matchedVideo2称为列属性相同的普通表，以便实现将单个分区数据放入单个表进行归档。

mysql> select * from matchedVideo where company_id = 14;

+----------+

| count(*) |

+----------+

| 2410806 |

+----------+

1 row in set (0.00 sec)

mysql> ALTER TABLE matchedVideo EXCHANGE PARTITION Viacom WITH TABLE matchedVideo2;

Query OK, 0 rows affected (0.67 sec)

-- 交换分区Viacom的数据到普通表中

mysql> select count(*) from matchedVideo2;

+----------+

| count(*) |

+----------+

| 2410806 |

+----------+

1 row in set (4.24 sec)

mysql> select * from matchedVideo where company_id = 14;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

二、交换分区应遵循以下原则：

1>被交换的表为没有分区，但是有相同结构的表

2>未分区表中的记录必须要在另一表的分区或子分区范围内

mysql> ALTER TABLE matchedVideo EXCHANGE PARTITION Viacom WITH TABLE matchedVideo2;

Query OK, 0 rows affected (0.58 sec)

mysql> update matchedVideo2 set company_id = 10 where id = 75537347;

Query OK, 1 row affected (0.23 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> ALTER TABLE matchedVideo EXCHANGE PARTITION Viacom WITH TABLE matchedVideo2;

ERROR 1737 (HY000): Found a row that does not match the partition

3>交换表中不允许有外键

分区在不同引擎中的情况：

分区功能并不是在存储引擎层完成，因此不是只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM，NDB等都支持，但也不是所有的都支持，CSV，FEDORATED，MERGE等就不支持。

下面我们来看下InnoDB，MyISAM的区别：

不管是5.1还是5.6版本，引擎在处理数据不存在分区的情况是一样的

Innodb

mysql> create table t (

-> id int) engine=innodb

-> partition by range (id) (

-> partition p0 values less than (10),

-> partition p1 values less than (20));

Query OK, 0 rows affected (3.25 sec)

root@test 11:30:42>insert into t(id) values(3),(4),(10),(13),(21);

ERROR 1526 (HY000): Table has no partition for value 21

root@test 11:32:18>select * from t;

Empty set (0.00 sec)

MyISAM

mysql> create table tt (

-> id int) engine=myisam

-> partition by range (id) (

-> partition p0 values less than (10),

-> partition p1 values less than (20));

Query OK, 0 rows affected (0.10 sec)

root@test 11:31:10>insert into tt(id) values(3),(4),(10),(13),(21);

ERROR 1526 (HY000): Table has no partition for value 21

root@test 11:31:59>select * from tt;

+------+

| id |

+------+

| 3 |

| 4 |

| 10 |

| 13 |

+------+

4 rows in set (0.00 sec)

由此可见，当插入多行记录数据时不存在与分区中，InnoDB会全部回滚，而MyISAM会从出错的数据那行开始回滚，之前的都会插入。

三、分区分类

分区分类包括：Range，List，Hash，Key分区四类

mysql5.5开始支持COLUMNS分区 ,视为RANGE分区和LIST分区的一种进化，columns分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型，此外，columns分区可以对多个列的值进行分区支持INT,SMALLINT,TINYINT,BIGINT.DATE,DATETIME,CHAR,VARCHAR,BINARY,VARBINARY类型

MySQL5.1

root@test 11:57:19> CREATE TABLE t_columns_range51(

-> a INT,

-> b DATETIME) ENGINE=INNODB

-> PARTITION BY RANGE (TO_DAYS(b))

-> (PARTITION p0 VALUES LESS THAN(TO_DAYS('2009-01-01')),

-> PARTITION p1 VALUES LESS THAN(TO_DAYS('2010-01-01')));

Query OK, 0 rows affected (0.42 sec)

root@test 11:57:19>insert into t_columns_range51(a,b) values(1,'2009-02-01');

Query OK, 1 row affected (0.00 sec)

root@test 11:58:18>select table_name,partition_name,table_rows from information_schema.partitions where table_name ='t_columns_range51';

+-------------------+----------------+------------+

| table_name | partition_name | table_rows |

+-------------------+----------------+------------+

| t_columns_range51 | p0 | 0 |

| t_columns_range51 | p1 | 1 |

+-------------------+----------------+------------+

2 rows in set (0.00 sec)

MySQL5.6

mysql> CREATE TABLE t_columns_range(

-> a INT,

-> b DATETIME) ENGINE=INNODB

-> PARTITION BY RANGE COLUMNS (b)

-> (PARTITION p0 VALUES LESS THAN('2009-01-01'),

-> PARTITION p1 VALUES LESS THAN('2010-01-01'));

Query OK, 0 rows affected (0.26 sec)

mysql> insert into t_columns_range(a,b) values (1,'2009-02-01');

Query OK, 1 row affected (0.05 sec)

mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name ='t_columns_range';

+-----------------+----------------+------------+

| table_name | partition_name | table_rows |

+-----------------+----------------+------------+

| t_columns_range | p0 | 0 |

| t_columns_range | p1 | 1 |

+-----------------+----------------+------------+

2 rows in set (0.03 sec)

mysql> create table c1(

-> a varchar(25),

-> b varchar(25),

-> city varchar(15)

-> )

-> partition by list columns(city)(

-> partition p1 values in('xx','yy'),

-> partition p2 values in('uu','vv'));

Query OK, 0 rows affected (0.46 sec)

mysql> insert into c1(a,b,city) values('a','a','xx');

Query OK, 1 row affected (5.41 sec)

mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name ='c1';

+------------+----------------+------------+

| table_name | partition_name | table_rows |

+------------+----------------+------------+

| c1 | p1 | 1 |

| c1 | p2 | 0 |

+------------+----------------+------------+

2 rows in set (0.00 sec)

对于range columns分区，可以使用多个列进行分区，如

mysql> create table c2(

-> a int,

-> b int,

-> c varchar(15)

-> )

-> partition by range columns(a,b,c)(

-> partition p1 values less than (5,10,'xbb'),

-> partition p2 values less than (10,20,'icey'),

-> partition p5 values less than (maxvalue,maxvalue,maxvalue));

Query OK, 0 rows affected (0.16 sec)

mysql> insert into c2(a,b,c) values(3,20,'xbb');

Query OK, 1 row affected (0.04 sec)

mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name ='c2';

+------------+----------------+------------+

| table_name | partition_name | table_rows |

+------------+----------------+------------+

| c2 | p1 | 1 |

| c2 | p2 | 0 |

| c2 | p5 | 0 |

+------------+----------------+------------+

3 rows in set (0.00 sec)

mysql> insert into c2(a,b,c) values(12,22,'xbb');

Query OK, 1 row affected (0.02 sec)

mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name ='c2';

+------------+----------------+------------+

| table_name | partition_name | table_rows |

+------------+----------------+------------+

| c2 | p1 | 1 |

| c2 | p2 | 0 |

| c2 | p5 | 1 |

+------------+----------------+------------+

3 rows in set (0.00 sec)

当行列中有一半以上满足某一个分区，则存与那个分区中

四、注意：

1.分区优化器只对YEAR()，TO_DAYS()，TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择，如果人为的定义分区，如按照年月做分区(201102)，year(date)+month(date),优化器不会根据分区进行选择，会扫描所有的分区。正确的应该用to_days来进行分区。

2.分区中的null不同的类型处理也不同

1>对于Range分区，如果向分区列插入NULL值，则MySQL数据库会将该值放入最左边的分区。但删除分区时，删除的将是小于分区的值，并且还有Null的值。

2>对于List分区，如果要使用NULL值，则必须在List中标明哪个分区中放入NULL值，否则插入报错。。

3>Hash和key分区对于NULL值的处理方法和list和range分区不一样，任何函数都会将含有NUll值的记录返回0.

完毕！