mysql分区清理存储过程 mysql 分区语句

转载

架构领航博主 2023-09-28 20:25:31

文章标签 mysql分区清理存储过程 mysql MySQL 分区表 文章分类 MySQL 数据库

文章目录

引言
MYSQL分区是什么？
MySQL分区的优点
MySQL分区的缺点
MySQL分区类型

RANGE分区
LIST分区
HASH分区
KEY分区
COLUMN分区
子分区

分区管理
参考文献

引言

学习一个新知识点的时候，首先要明白的是他是什么，优点和缺点。再具体到怎么去使用。所以本文的顺序大致如上。

MYSQL分区是什么？

在逻辑上，表的使用不受影响，但是在物理上(体现在文件上)，原本一个表对应一个数据文件的，但是分区后一个表对应了几个数据文件。例如我们用以下语句创建一个表：

mysql> create table test(
    -> id int auto_increment primary key,
    -> message varchar(255)
    -> );
Query OK, 0 rows affected (0.14 sec)

在系统中便会为我们创建一个文件：

[root@VM_0_6_centos test]# ls
test.ibd

修改数据库使用分区(具体的语句稍后再解释)：

mysql> alter table test partition by hash(id) partitions 4;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

我们会发现在系统中，文件发生了一些改变：

[root@VM_0_6_centos test]#  ls
test#P#p0.ibd  test#P#p1.ibd  test#P#p2.ibd  test#P#p3.ibd

也就是在物理上发生了一些变化，然而在逻辑上的使用不会改变，例如查询表：

mysql> select * from test where id = 1;
Empty set (0.01 sec)

在使用者的逻辑上并未改变，但是对数据库来说，原本一个表有100行，经过HASH分区后，也许均匀点分4个分区也就是一个分区只有25行，只需要定位到对应的分区后检索这25条数据就行了。

MySQL分区的优点

分区使得在一个表中存储比单个磁盘或文件系统分区中存储更多的数据成为可能。不知道可不可以这么理解，假如每个文件上限100M，那么分区4个，就可以存400M！
原本一些有用的数据无效了，通过移除相应的分区就可以快捷的移除相应的数据。同样的为特别的数据添加分区也是非常便捷的。
优化查询。在使用分区指定列进行查询时，数据库可以根据指定列定位到相应的物理文件进行查询。另外MySQL同样支持显式的分区查询，例如：SELECT * FROM t PARTITION (p0,p1) WHERE c < 5只会去查询p0,p1分区。

MySQL分区的缺点

分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁。
一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难。

MySQL分区类型

RANGE分区
LIST分区
HASH分区
KEY分区
COLUMNS分区

表上的每个唯一键必须使用表的分区表达式中的每一列！ 这句话是分区键设计的一个非常重要的原则，包括很多方面，所以这里先不详细解释。

RANGE分区

RANGE分区代表的是范围，区间要连续并且不能互相重叠，使用VALUES LESS THAN进行分区定义。以上面的test表的id为例进行分区:

mysql> alter table test partition by range(id)(
    -> partition p0 values less than (10),
    -> partition p1 values less than (20),
    -> partition p2 values less than maxvalue);
Query OK, 0 rows affected (0.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

这里相当于把id分成了3个区间： <10的，10 ~ 20的，20 ~ 正整数最大值(相当于>=20的)。

我们看一下id=15的数据查询计划会发现：partition是p1！

mysql> explain select * from test where id = 15;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p1         | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

LIST分区

LIST分区会根据事先我们预定好的集合进行分区，例如我们如下修改分区：

mysql> alter table test partition by list(id)(
    -> partition p0 values in (1,2),
    -> partition p1 values in (3));
ERROR 1526 (HY000): Table has no partition for value 4

当id不在1,2,3中为4时，数据库不知道该怎么进行分区，所以会报错。我们做一点修改：

mysql> alter table test partition by list(id%2)( partition p0 values in (0), partition p1 values in (1));
Query OK, 30 rows affected (0.48 sec)
Records: 30  Duplicates: 0  Warnings: 0

这样我们便将表按id是否偶数进行了分区。

HASH分区

HASH分区的语法很简单：

mysql> alter table test partition by hash(id) partitions 5;
Query OK, 30 rows affected (1.11 sec)
Records: 30  Duplicates: 0  Warnings: 0

上面这条语句代表使用的是求余的hash算法，当我们查询id = 15时可以发现分区为 15%5 == 0，p0分区：

mysql> explain select * from test where id = 15;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p0         | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

取余的算法有点弊端在于当我们要添加新分区时需要对原本的分区进行重新hash。MySQL用线性hash作为一种优化，这里大致讲一下定位，例如我们查找是15，分区个数还是5。那么这种算法如下：

先找比5大的2次方的幂V：也就是8。然后将id & (V - 1),也就是 15 & 7 = 7(设为N)。如果(N)7 >= 5(分区数)，那么7(N) & （8(V)/2 - 1）= 3;

mysql> alter table test partition by linear hash(id) partitions 5;
Query OK, 30 rows affected (0.82 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> explain select * from test where id = 15;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p3         | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

如果我们把分区数改成6，同理：

先找比6大的2次方的幂V：也就是8。然后将id & (V - 1),也就是 15 & 7 = 7(设为N)。如果(N)7 >= 6(分区数)，那么7(N) & （8(V)/2 - 1）= 3;

mysql> alter table test partition by linear hash(id) partitions 6;
Query OK, 30 rows affected (1.00 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> explain select * from test where id = 15;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p3         | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

linear hash在分区维护时相比直接hash要更加快，但是不如直接hash均匀。

KEY分区

HASH分区允许用户自定义的表达式，而KEY分区不允许。和HASH分区不同，创建KEY分区时不指定分区键默认会使用主键作为分区键，在没有主键的情况下使用非空唯一主键，否则会报错。

mysql> alter table test partition by key();
Query OK, 30 rows affected (0.53 sec)
Records: 30  Duplicates: 0  Warnings: 0

COLUMN分区

COLUMN分为RANGE COLUMN 和 LIST COLUMN两种， COLUMN分区支持所有整数类型、date和datetime类型、字符类型char,varchar,binary和varbinary；除此之外还支持多列分区。

在这里我们不能直接通过test表来演示多列分区，因为在定义表的时候我们定义了主键id。而表上的每个唯一键必须使用表的分区表达式中的每一列！ 所以我们接下来用一个新的建表例子：

mysql> create table t1(
    -> a int,
    -> b int,
    -> c char(1))
    -> partition by range columns(a,c)(
    -> partition p0 values less than (10, 'k'),
    -> partition p1 values less than (20, 'r'),
    -> partition p2 values less than (maxvalue, 'z'));
Query OK, 0 rows affected (0.17 sec)

RANGE COLUMN是按元组进行比较的，就上面的例子来说会先比较a的值，如果a的值相同，这时候才会比较第二位，若还有更多的值，同样是按这样的道理进行比较。

mysql> alter table t1 partition by list columns(a,c)( 
			 partition p0 values in((1,'a'),(3,'c')),
			 partition p1 values in((2,'b')));
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

LIST COLUMN也和LIST大致相同，不过也支持多列。

子分区

子分区是对分区表中每个分区的再次分割，对RANGE和LIST分区进行HASH分区或者KEY分区。例如用回我们的test表进行举例：

mysql> alter table test partition by range(id) 
    -> subpartition by hash(id)
    -> subpartitions 5
    -> ( partition p0 values less than (10),
    ->   partition p1 values less than (20),
    ->   partition p2 values less than maxvalue);
Query OK, 30 rows affected (1.44 sec)
Records: 30  Duplicates: 0  Warnings: 0

我们将按RANGE分为3个分区，然后在某个小分区内划分HASH子分区，子分区大小为5。当我们查找id = 15时，首先显而易见的会到p1分区去，然后p1分区在进行取余的哈希，我们可以得到在子分区内为0。

mysql> explain select * from test where id = 15;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p1_p1sp0   | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

再给个例子：

mysql> explain select * from test where id = 29;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p2_p2sp4   | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

我们使用下列语句查看这些子分区的分布(id 从1 到 30)：

mysql> select PARTITION_NAME,SUBPARTITION_NAME,TABLE_ROWS from information_schema.PARTITIONS where TABLE_NAME = 'test';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          0 |
| p0             | p0sp1             |          2 |
| p0             | p0sp2             |          2 |
| p0             | p0sp3             |          2 |
| p0             | p0sp4             |          2 |
| p1             | p1sp0             |          2 |
| p1             | p1sp1             |          2 |
| p1             | p1sp2             |          2 |
| p1             | p1sp3             |          2 |
| p1             | p1sp4             |          2 |
| p2             | p2sp0             |          3 |
| p2             | p2sp1             |          2 |
| p2             | p2sp2             |          2 |
| p2             | p2sp3             |          2 |
| p2             | p2sp4             |          2 |
+----------------+-------------------+------------+
15 rows in set (0.00 sec)

这里我个人感觉有点问题，按道理说第一行table_rows应该是1！因为当id = 5时：

mysql> explain select * from test where id = 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p0_p0sp0   | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)