MySQL 分区
一 查看是否支持分区
-
mysql> SHOW VARIABLES LIKE \'%partition%\';
-
+-------------------+-------+
-
| Variable_name | Value |
-
+-------------------+-------+
-
| have_partitioning | YES |
-
+-------------------+-------+
- 1 row in set (0.00 sec)
二 各大分区
1 Range分区
-
CREATE TABLE emp3 (
-
id int(11) NOT NULL,
-
ename varchar(30) DEFAULT NULL,
-
hired date NOT NULL DEFAULT \'1970-01-01\',
-
separated date NOT NULL DEFAULT \'9999-12-31\',
-
job varchar(30) NOT NULL,
-
store_id int(11) NOT NULL
-
) PARTITION BY RANGE (store_id)
-
(PARTITION p0 VALUES LESS THAN (10) ,
-
PARTITION p1 VALUES LESS THAN (20) ,
-
PARTITION p2 VALUES LESS THAN (30)
-
);
增加分区
-
alter table emp add partition( PARTITION p3 VALUES LESS THAN MAXVALUE);
- alter table emp add partition( PARTITION p3 VALUES LESS THAN MAXVALUE);
-
mysql> select partition_name part, partition_expression expr,partition_description descr, table_rows from information_schema.partitions where table_schema=schema() and table_name='emp3';
-
+------+----------+----------+------------+
-
| part | expr | descr | table_rows |
-
+------+----------+----------+------------+
-
| p0 | store_id | 10 | 0 |
-
| p1 | store_id | 20 | 0 |
-
| p2 | store_id | 30 | 0 |
-
| p3 | store_id | MAXVALUE | 1 |
-
+------+----------+----------+------------+
-
4 rows in set (0.01 sec)
-
mysql> select partition_name part, partition_expression expr,partition_description descr, table_rows from information_schema.partitions where table_schema=schema() and table_name='emp3';
-
+------+----------+----------+------------+
-
| part | expr | descr | table_rows |
-
+------+----------+----------+------------+
-
| p0 | store_id | 10 | 0 |
-
| p1 | store_id | 20 | 0 |
-
| p2 | store_id | 30 | 0 |
-
| p3 | store_id | MAXVALUE | 1 |
-
+------+----------+----------+------------+
- 4 rows in set (0.01 sec)
-
alter table emp_date drop partition p2;
- alter table emp_date drop partition p2;
2 List分区
-
CREATE TABLE expenses (
-
expense_date date NOT NULL,
-
category int(11) DEFAULT NULL,
-
amount decimal(10,3) DEFAULT NULL
-
) PARTITION BY LIST (category)
-
(PARTITION p0 VALUES IN (3,5) ,
-
PARTITION p1 VALUES IN (1,10),
-
PARTITION p2 VALUES IN (4,9),
-
PARTITION p3 VALUES IN (2) ,
-
PARTITION p4 VALUES IN (6)
- );
增加分区和重定义分区
-
alter table expenses add partition (partition p6 values in(11));
-
-
alter table expenses reorganize partition p4,p5,p6 into(
-
partition p4 values in (6,11),
-
partition p5 values in (7,8)
- );
-
mysql> show create table expenses\\G
-
*************************** 1. row ***************************
-
Table: expenses
-
Create Table: CREATE TABLE `expenses` (
-
`expense_date` date NOT NULL,
-
`category` int(11) DEFAULT NULL,
-
`amount` decimal(10,3) DEFAULT NULL
-
) ENGINE=InnoDB DEFAULT CHARSET=latin1
-
/*!50100 PARTITION BY LIST (category)
-
(PARTITION p0 VALUES IN (3,5) ENGINE = InnoDB,
-
PARTITION p1 VALUES IN (1,10) ENGINE = InnoDB,
-
PARTITION p2 VALUES IN (4,9) ENGINE = InnoDB,
-
PARTITION p3 VALUES IN (2) ENGINE = InnoDB,
-
PARTITION p4 VALUES IN (6,11) ENGINE = InnoDB,
-
PARTITION p5 VALUES IN (7,8) ENGINE = InnoDB) */
- 1 row in set (0.00 sec)
3 Hash分区
-
mysql> CREATE TABLE emp_hash ( id int not null , ename varchar(30), hired date not null default '1970-01-01', separated date not null default '9999-12-31', job varchar(30) not null, store_id int not null)partition by hash(store_id) partitions 4;
-
Query OK, 0 rows affected (0.06 sec)
-
mysql> CREATE TABLE emp_hash ( id int not null , ename varchar(30), hired date not null default '1970-01-01', separated date not null default '9999-12-31', job varchar(30) not null, store_id int not null)partition by hash(store_id) partitions 4;
- Query OK, 0 rows affected (0.06 sec)
减少分区数为2
-
mysql> alter table emp_hash coalesce partition 2;
-
Query OK, 0 rows affected (0.18 sec)
-
Records: 0 Duplicates: 0 Warnings: 0
增加分区数（注意跟减少分区数命令不同）
-
mysql> alter table emp_hash add partition partitions 8;
-
Query OK, 0 rows affected (0.21 sec)
-
Records: 0 Duplicates: 0 Warnings: 0
-
mysql> alter table emp_hash coalesce partition 2;
-
Query OK, 0 rows affected (0.18 sec)
- Records: 0 Duplicates: 0 Warnings: 0
-
mysql> alter table emp_hash add partition partitions 8;
-
Query OK, 0 rows affected (0.21 sec)
- Records: 0 Duplicates: 0 Warnings: 0
4 key分区
用partition by key(expr) 子句来创建一个Key分区表，expr是零个或者多个字段名名的表：
-
mysql> CREATE TABLE emp_key(
-
-> id int not null ,
-
-> ename varchar(30),
-
-> hired date not null default \'1970-01-01\',
-
-> separated date not null default \'9999-12-31\',
-
-> job varchar(30) not null,
-
-> stored_id int not null
-
-> )partition by key(job) partitions 4;
- Query OK, 0 rows affected (0.05 sec)
5 子分区
创建子分区-
mysql> create table ts(id int,purchased date) partition by range(year(purchased)) subpartition by hash(to_days(purchased)) subpartitions 2 ( partition p0 values less than (1990), partition p1 values less than (2000), partition p2 values less than MAXVALUE );
- Query OK, 0 rows affected (0.08 sec)
6 columns分区
columns分区可以细分为 range columns分区 和 list columns分区，这两种分区都支持整数、日期时间、字符串三大数据类型。columns分区最大亮点是支持多列分区。如下：
插入字段比较：
-
CREATE TABLE rc3 (
-
a int(11) DEFAULT NULL,
-
b int(11) DEFAULT NULL
-
) PARTITION BY RANGE COLUMNS(a,b)
-
(PARTITION p01 VALUES LESS THAN (0,10) ,
-
PARTITION p02 VALUES LESS THAN (10,10),
-
PARTITION p03 VALUES LESS THAN (10,20) ,
-
PARTITION p04 VALUES LESS THAN (10,35) ,
-
PARTITION p05 VALUES LESS THAN (10,MAXVALUE),
- PARTITION p06 VALUES LESS THAN (MAXVALUE,MAXVALUE));
插入字段比较：
-
mysql> select (1,10) < (10,10) from dual;
-
+------------------+
-
| (1,10) < (10,10) |
-
+------------------+
-
| 1 |
-
+------------------+
- 1 row in set (0.00 sec)
-
mysql> select partition_name part, partition_expression expr,partition_description descr, table_rows from information_schema.partitions where table_schema=schema() and table_name=\'rc3\';
-
+------+---------+-------------------+------------+
-
| part | expr | descr | table_rows |
-
+------+---------+-------------------+------------+
-
| p01 | `a`,`b` | 0,10 | 0 |
-
| p02 | `a`,`b` | 10,10 | 2 |
-
| p03 | `a`,`b` | 10,20 | 1 |
-
| p04 | `a`,`b` | 10,35 | 0 |
-
| p05 | `a`,`b` | 10,MAXVALUE | 0 |
-
| p06 | `a`,`b` | MAXVALUE,MAXVALUE | 0 |
-
+------+---------+-------------------+------------+
- 6 rows in set (0.01 sec)
总结
本篇博客重点介绍MySQL的分区类型和常规管理维护命令，分区通过“分而治之”的方法管理数据库表，提高了数据处理的并行度从而能够提升性能。来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26435490/viewspace-1266171/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
