MySQL分区介绍
不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分
MySQL> create table t1(
-> col1 int not null,col2 date not null,col3 int not null,col4 int not null,unique key(col1,col2)) partition by hash(col3) partitions 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> create table t1(
-> col1 int not null,col2 date not null,col3 int not null,col4 int not null,unique key(col1,col2,col3)) partition by hash(col3) partitions 4;
Query OK, 0 rows affected (0.49 sec)
mysql> create table t2(
-> col1 int null,
-> col2 date null,
-> col3 int null,
-> col4 int null
-> ) engine=innodb
-> partition by hash(col3)
-> partitions 4;
Query OK, 0 rows affected (0.40 sec)
mysql> create table t3(
-> col1 int null,
-> col2 date null,
-> col3 int null,
-> col4 int null,
-> key (col4)
-> ) engine=innodb
-> partition by hash(col3)
-> partitions 4;
Query OK, 0 rows affected (0.23 sec)
--查看数据库是否支持分区
MariaDB [test]> show plugins;
+-------------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
.....
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-------------------------------+----------+--------------------+---------+---------+
MariaDB [test]> select * from INFORMATION_SCHEMA.plugins where plugin_name='partition'\G
*************************** 1. row ***************************
PLUGIN_NAME: partition
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: STORAGE ENGINE
PLUGIN_TYPE_VERSION: 100114.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: Mikael Ronstrom, MySQL AB
PLUGIN_DESCRIPTION: Partition Storage Engine Helper
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.0
1 row in set (0.00 sec)
--范围分区
MariaDB [test]> CREATE TABLE members (
-> firstname VARCHAR(25) NOT NULL,
-> lastname VARCHAR(25) NOT NULL,
-> username VARCHAR(16) NOT NULL,
-> email VARCHAR(35),
-> joined DATE NOT NULL
-> )
-> PARTITION BY RANGE COLUMNS(joined) (
-> PARTITION p0 VALUES LESS THAN ('1960-01-01'),
-> PARTITION p1 VALUES LESS THAN ('1970-01-01'),
-> PARTITION p2 VALUES LESS THAN ('1980-01-01'),
-> PARTITION p3 VALUES LESS THAN ('1990-01-01'),
-> PARTITION p4 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.45 sec)
MariaDB [test]> CREATE TABLE employees (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job_code INT NOT NULL,
-> store_id INT NOT NULL
-> )
-> PARTITION BY RANGE (store_id) (
-> PARTITION p0 VALUES LESS THAN (6),
-> PARTITION p1 VALUES LESS THAN (11),
-> PARTITION p2 VALUES LESS THAN (16),
-> PARTITION p3 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.49 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(1,'John','Terry',10,100);
Query OK, 1 row affected (0.06 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(2,'Tom','Carl',10,1);
Query OK, 1 row affected (0.06 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(2,'Lily','Berg',20,7);
Query OK, 1 row affected (0.03 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(3,'Lucy','Phynix',20,10);
Query OK, 1 row affected (0.13 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(4,'Bill','Jones',20,15);
Query OK, 1 row affected (0.02 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(5,'Jill','Deco',30,12);
Query OK, 1 row affected (0.08 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(6,'Emily','Aaron',30,20);
Query OK, 1 row affected (0.02 sec)
MariaDB [test]> select * from employees;
+----+-------+--------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+-------+--------+------------+------------+----------+----------+
| 2 | Tom | Carl | 1970-01-01 | 9999-12-31 | 10 | 1 |
| 2 | Lily | Berg | 1970-01-01 | 9999-12-31 | 20 | 7 |
| 3 | Lucy | Phynix | 1970-01-01 | 9999-12-31 | 20 | 10 |
| 4 | Bill | Jones | 1970-01-01 | 9999-12-31 | 20 | 15 |
| 5 | Jill | Deco | 1970-01-01 | 9999-12-31 | 30 | 12 |
| 1 | John | Terry | 1970-01-01 | 9999-12-31 | 10 | 100 |
| 6 | Emily | Aaron | 1970-01-01 | 9999-12-31 | 30 | 20 |
+----+-------+--------+------------+------------+----------+----------+
7 rows in set (0.00 sec)
MariaDB [test]> show create table employees\G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`id` int(11) NOT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL,
`hired` date NOT NULL DEFAULT '1970-01-01',
`separated` date NOT NULL DEFAULT '9999-12-31',
`job_code` int(11) NOT NULL,
`store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (store_id)
(PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (16) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
按照年进行分区
mysql> create table sales(
-> money int unsigned not null,
-> date datetime
-> ) engine=innodb
-> partition by range (year(date)) (
-> partition p2008 values less than (2009),
-> partition p2009 values less than (2010),
-> partition p2010 values less than (2011)
-> );
Query OK, 0 rows affected (0.31 sec)
mysql> insert into sales values (100, '2008-01-01'),(100, '2008-02-01'),(200, '2008-01-02'), (100, '2009-03-01'), (200, '2010-03-01');
Query OK, 5 rows affected (0.13 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table sales drop partition p2008;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain partitions
-> select * from sales
-> where date>='2009-01-01' and date<='2009-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
partitions: p2009
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
1 row in set (0.00 sec)
优化器只能对YEAR()、TO_DAYS()、TO_SECONDS()和UNIX_TIMESTAMP()这类函数进行优化选择
下面这个例子中的分区创建有问题，在分区扫描的时候会扫描多个分区
按照每年每月来进行分区
mysql> create table sales2(
-> money int unsigned not null,
-> date datetime
-> ) engine=innodb
-> partition by range (year(date)*100+month(date)) (
-> partition p201001 values less than (201002),
-> partition p201002 values less than (201003),
-> partition p201003 values less than (201004)
-> );
Query OK, 0 rows affected (0.20 sec)
mysql> explain partitions select * from sales2 where date>='2010-01-01' and date <= '2010-01-31';
+----+-------------+--------+-------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | sales2 | p201001,p201002,p201003 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+-------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
下面例子为上面例子的正确创建方法
mysql> create table sales1(
-> money int unsigned not null,
-> date datetime) engine=innodb
-> partition by range(to_days(date)) (
-> partition p201001
-> values less than(to_days('2010-02-01')),
-> partition p201002
-> values less than(to_days('2010-03-01')),
-> partition p201003
-> values less than (to_days('2010-04-01'))
-> );
Query OK, 0 rows affected (0.20 sec)
mysql> explain partitions select * from sales1 where date>='2010-01-01' and date<='2010-01-31';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | sales1 | p201001 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
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 (0.55 sec)
mysql> system ls -lrt /var/lib/mysql/test
-rw-rw----. 1 mysql mysql 8556 Nov 3 14:22 t.frm
-rw-rw----. 1 mysql mysql 28 Nov 3 14:22 t.par
-rw-rw----. 1 mysql mysql 98304 Nov 3 14:22 t#P#p0.ibd
-rw-rw----. 1 mysql mysql 98304 Nov 3 14:22 t#P#p1.ibd
mysql> select * from information_schema.partitions
-> where table_schema=database() and table_name='t'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: t
PARTITION_NAME: p0
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: id
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 10
TABLE_ROWS: 1
AVG_ROW_LENGTH: 16384
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-11-03 14:22:00
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: t
PARTITION_NAME: p1
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: id
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 20
TABLE_ROWS: 2
AVG_ROW_LENGTH: 8192
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-11-03 14:22:00
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
2 rows in set (0.00 sec)
mysql> insert into t values(50);
ERROR 1526 (HY000): Table has no partition for value 50
mysql> alter table t
-> add partition(
-> partition p2 values less than maxvalue );
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t values(50);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
--LIST分区
MariaDB [test]> CREATE TABLE employees5 (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job_code INT,
-> store_id INT
-> )
-> PARTITION BY LIST(store_id) (
-> PARTITION pNorth VALUES IN (3,5,6,9,17),
-> PARTITION pEast VALUES IN (1,2,10,11,19,20),
-> PARTITION pWest VALUES IN (4,12,13,14,18),
-> PARTITION pCentral VALUES IN (7,8,15,16)
-> );
Query OK, 0 rows affected (5.13 sec)
--COLUMN分区
字段分区是范围分区和列表分区的一种变体，字段分区可以使用多个字段作为分区键。
范围字段分区和列表字段分区支持非整数字段，支持的数据类型如下：
所有整数类型：TINYINT, SMALLINT, MEDIUMINT, INT，BIGINT。
DATE，DATETIME。
CHAR, VARCHAR, BINARY,VARBINARY。
MariaDB [test]> CREATE TABLE rc2 (
-> a INT,
-> b INT
-> )
-> 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)
-> );
Query OK, 0 rows affected (0.27 sec)
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.20 sec)
mysql> create table customers_1 (
-> first_name varchar(25),
-> last_name varchar(25),
-> street_1 varchar(30),
-> street_2 varchar(30),
-> city varchar(15),
-> renewal date
-> )
-> partition by list columns(city) (
-> partition pRegion_1
-> values in ('Oskarshamn', 'Hogsby', 'Monsters'),
-> partition pRegion_2
-> values in ('Vimmerby', 'Hultsfred', 'Vastervik'),
-> partition pRegion_3
-> values in ('Nassjo', 'Eksjo', 'Vetlanda'),
-> partition pRegion_4
-> values in ('Uppvidinge', 'Alvesta', 'Vaxjo')
-> );
Query OK, 0 rows affected (0.23 sec)
mysql> create table rcx(
-> a int,
-> b int,
-> c char(3),
-> d int
-> )engine=innodb
-> partition by range columns(a,d,c) (
-> partition p0 values less than (5,10,'ggg'),
-> partition p1 values less than (10,20,'mmmm'),
-> partition p2 values less than (15,30,'sss'),
-> partition p3 values less than (MAXVALUE,MAXVALUE,MAXVALUE)
-> );
Query OK, 0 rows affected (0.32 sec)
--哈希分区
哈希分区主要确保分区表中的数据均匀分布在各个分区之中。
mysql> create table t_hash(a int,b datetime)engine=innodb
-> partition by hash(year(b))
-> partitions 4;
Query OK, 0 rows affected (7.81 sec)
MariaDB [test]> CREATE TABLE employees7 (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job_code INT,
-> store_id INT
-> )
-> PARTITION BY HASH(store_id)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.22 sec)
MySQL数据库还支持一种称为LINEAR HASH的分区，它使用一个更加复杂的算法来确定新行插入到已经分区的表中的位置
LINEAR HASH分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，这有利于处理含有大量数据的表。LINEAR HASH分区的缺点在于，
与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡
mysql> create table t_linear_hash(
-> a int,
-> b datetime
-> )engine=innodb
-> partition by linear hash(year(b))
-> partitions 4;
Query OK, 0 rows affected (0.23 sec)
--KEY分区
KEY分区类似哈希分区，除了哈希分区使用用户自定义的表达式。分区键列必须包含部分或所有的表的主键。
MariaDB [test]> CREATE TABLE k1 (
-> id INT NOT NULL,
-> name VARCHAR(20),
-> UNIQUE KEY (id)
-> )
-> PARTITION BY KEY()
-> PARTITIONS 2;
Query OK, 0 rows affected (0.11 sec)
--复合分区
MySQL数据库允许在RANGE和LIST的分区上再进行HASH或KEY的子分区
MariaDB [test]> CREATE TABLE ts (id INT, purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) )
-> SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990) (
-> SUBPARTITION s0,
-> SUBPARTITION s1
-> ),
-> PARTITION p1 VALUES LESS THAN (2000) (
-> SUBPARTITION s2,
-> SUBPARTITION s3
-> ),
-> PARTITION p2 VALUES LESS THAN MAXVALUE (
-> SUBPARTITION s4,
-> SUBPARTITION s5
-> )
-> );
Query OK, 0 rows affected (0.51 sec)
mysql> create table ts(a int,b date) engine=innodb
-> partition by range(year(b))
-> subpartition by hash(to_days(b))
-> 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.24 sec)
mysql> system ls -lh /var/lib/mysql/test/ts*
-rw-rw----. 1 mysql mysql 8.4K Nov 4 15:44 /var/lib/mysql/test/ts.frm
-rw-rw----. 1 mysql mysql 96 Nov 4 15:44 /var/lib/mysql/test/ts.par
-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 /var/lib/mysql/test/ts#P#p0#SP#p0sp0.ibd
-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 /var/lib/mysql/test/ts#P#p0#SP#p0sp1.ibd
-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 /var/lib/mysql/test/ts#P#p1#SP#p1sp0.ibd
-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 /var/lib/mysql/test/ts#P#p1#SP#p1sp1.ibd
-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 /var/lib/mysql/test/ts#P#p2#SP#p2sp0.ibd
-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 /var/lib/mysql/test/ts#P#p2#SP#p2sp1.ibd
mysql> create table ts (a int, b date)
-> partition by range (year(b))
-> subpartition by hash( to_days(b)) (
-> partition p0 values less than (1990) (
-> subpartition s0,
-> subpartition s1
-> ),
-> partition p1 values less than (2000) (
-> subpartition s2,
-> subpartition s3
-> ),
-> partition p2 values less than MAXVALUE (
-> subpartition s4,
-> subpartition s5
-> )
-> );
Query OK, 0 rows affected (0.15 sec)
mysql> create table ts (a int,b date) engine=innodb
-> partition by range(year(b))
-> subpartition by hash(to_days(b)) (
-> partition p0 values less than (2000) (
-> subpartition s0
-> data directory = '/disk0/data'
-> index directory ='/disk0/idx',
-> subpartition s1
-> data directory = '/disk1/data'
-> index directory = '/disk1/idx'
-> ),
-> partition p1 values less than (2010) (
-> subpartition s2
-> data directory = '/disk2/data'
-> index directory = '/disk2/idx',
-> subpartition s3
-> data directory = '/disk3/data'
-> index directory = '/disk3/idx'
-> ),
-> partition p2 values less than maxvalue (
-> subpartition s4
-> data directory = '/disk4/data'
-> index directory = '/disk4/idx',
-> subpartition s5
-> data directory = '/disk5/data'
-> index directory = '/disk5/idx'
-> )
-> );
Query OK, 0 rows affected, 6 warnings (0.32 sec)
mysql> show warnings;
+---------+------+----------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------+
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
+---------+------+----------------------------------+
6 rows in set (0.00 sec)
--查看分区
MariaDB [test]> select * from INFORMATION_SCHEMA.PARTITIONS where table_name = 'employees'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: employees
PARTITION_NAME: p0
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: store_id
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 6
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-07-04 00:42:16
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: employees
PARTITION_NAME: p1
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: store_id
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 11
TABLE_ROWS: 2
AVG_ROW_LENGTH: 8192
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-07-04 00:42:16
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 3. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: employees
PARTITION_NAME: p2
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: store_id
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 16
TABLE_ROWS: 2
AVG_ROW_LENGTH: 8192
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-07-04 00:42:16
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 4. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: employees
PARTITION_NAME: p3
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 4
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: store_id
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: MAXVALUE
TABLE_ROWS: 2
AVG_ROW_LENGTH: 8192
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-07-04 00:42:16
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
4 rows in set (0.00 sec)
--查看分区表执行计划
MariaDB [test]> explain partitions select * from employees;
+------+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | employees | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 7 | |
+------+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
MariaDB [test]> explain partitions select * from employees where store_id < 5;
+------+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employees | p0 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+------+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
--增加分区
MariaDB [test]> alter table employees add partition (partition p3 values less than (20));
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> alter table employees add partition (partition p5 values less than maxvalue);
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
--TRUNCATE指定分区
MariaDB [test]> alter table employees truncate partition p0;
Query OK, 0 rows affected (0.12 sec)
--删除指定分区
MariaDB [test]> alter table employees drop partition p0;
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
--将一个分区拆分成多个分区
MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='employees';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p1 | store_id | 11 | 2 |
| p2 | store_id | 16 | 2 |
| p3 | store_id | 20 | 0 |
| p5 | store_id | MAXVALUE | 0 |
+----------------+----------------------+-----------------------+------------+
4 rows in set (0.00 sec)
MariaDB [test]> ALTER TABLE employees
-> REORGANIZE PARTITION p1 INTO (
-> PARTITION n0 VALUES LESS THAN (5),
-> PARTITION n1 VALUES LESS THAN (11)
-> );
Query OK, 2 rows affected (0.49 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='employees';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| n0 | store_id | 5 | 0 |
| n1 | store_id | 11 | 2 |
| p2 | store_id | 16 | 2 |
| p3 | store_id | 20 | 0 |
| p5 | store_id | MAXVALUE | 0 |
+----------------+----------------------+-----------------------+------------+
5 rows in set (0.06 sec)
--将多个分区合并成一个分区
MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='employees';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| n0 | store_id | 5 | 0 |
| n1 | store_id | 11 | 2 |
| p2 | store_id | 16 | 2 |
| p3 | store_id | 20 | 0 |
| p5 | store_id | MAXVALUE | 0 |
+----------------+----------------------+-----------------------+------------+
5 rows in set (0.00 sec)
MariaDB [test]> ALTER TABLE employees
-> REORGANIZE PARTITION n0,n1,p2 INTO (
-> PARTITION p2 VALUES LESS THAN (16));
Query OK, 4 rows affected (0.28 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='employees';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p2 | store_id | 16 | 4 |
| p3 | store_id | 20 | 0 |
| p5 | store_id | MAXVALUE | 0 |
+----------------+----------------------+-----------------------+------------+
3 rows in set (0.03 sec)
--减少哈希分区的数量
MariaDB [test]> create table emp2(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.60 sec)
MariaDB [test]> alter table emp2 coalesce partition 2;
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> show create table emp2\G
*************************** 1. row ***************************
Table: emp2
Create Table: CREATE TABLE `emp2` (
`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
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (store_id)
PARTITIONS 2 */
1 row in set (0.00 sec)
增加哈希分区的数量
MariaDB [test]> alter table emp2 add partition partitions 5;
Query OK, 0 rows affected (0.54 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> show create table emp2\G
*************************** 1. row ***************************
Table: emp2
Create Table: CREATE TABLE `emp2` (
`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
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (store_id)
PARTITIONS 7 */
1 row in set (0.00 sec)
在表和分区间交换数据
mysql> create table e (
-> id int not null,
-> fname varchar(30),
-> lname varchar(30)
-> )
-> partition by range(id) (
-> partition p0 values less than (50),
-> partition p1 values less than (100),
-> partition p2 values less than (150),
-> partition p3 values less than (MAXVALUE)
-> );
Query OK, 0 rows affected (0.32 sec)
mysql> insert into e values (1669,"Jim","Smith"),(337,"Mary","Jones"),(16,"Frank","White"),(2005,"Linda","Black");
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
创建交换表
mysql> create table e2 like e;
Query OK, 0 rows affected (0.29 sec)
mysql> show create table e2\G
*************************** 1. row ***************************
Table: e2
Create Table: CREATE TABLE `e2` (
`id` int(11) NOT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
将分区表改成普通表
mysql> alter table e2 remove partitioning;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table e2\G
*************************** 1. row ***************************
Table: e2
Create Table: CREATE TABLE `e2` (
`id` int(11) NOT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
将e表的分区p0中的数据移动到
名称栏目：MySQL分区介绍
当前路径：http://myzitong.com/article/ijhghh.html
MySQL> create table t1(
-> col1 int not null,col2 date not null,col3 int not null,col4 int not null,unique key(col1,col2)) partition by hash(col3) partitions 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> create table t1(
-> col1 int not null,col2 date not null,col3 int not null,col4 int not null,unique key(col1,col2,col3)) partition by hash(col3) partitions 4;
Query OK, 0 rows affected (0.49 sec)
mysql> create table t2(
-> col1 int null,
-> col2 date null,
-> col3 int null,
-> col4 int null
-> ) engine=innodb
-> partition by hash(col3)
-> partitions 4;
Query OK, 0 rows affected (0.40 sec)
mysql> create table t3(
-> col1 int null,
-> col2 date null,
-> col3 int null,
-> col4 int null,
-> key (col4)
-> ) engine=innodb
-> partition by hash(col3)
-> partitions 4;
Query OK, 0 rows affected (0.23 sec)
--查看数据库是否支持分区
MariaDB [test]> show plugins;
+-------------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
.....
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-------------------------------+----------+--------------------+---------+---------+
MariaDB [test]> select * from INFORMATION_SCHEMA.plugins where plugin_name='partition'\G
*************************** 1. row ***************************
PLUGIN_NAME: partition
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: STORAGE ENGINE
PLUGIN_TYPE_VERSION: 100114.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: Mikael Ronstrom, MySQL AB
PLUGIN_DESCRIPTION: Partition Storage Engine Helper
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
PLUGIN_MATURITY: Stable
PLUGIN_AUTH_VERSION: 1.0
1 row in set (0.00 sec)
--范围分区
MariaDB [test]> CREATE TABLE members (
-> firstname VARCHAR(25) NOT NULL,
-> lastname VARCHAR(25) NOT NULL,
-> username VARCHAR(16) NOT NULL,
-> email VARCHAR(35),
-> joined DATE NOT NULL
-> )
-> PARTITION BY RANGE COLUMNS(joined) (
-> PARTITION p0 VALUES LESS THAN ('1960-01-01'),
-> PARTITION p1 VALUES LESS THAN ('1970-01-01'),
-> PARTITION p2 VALUES LESS THAN ('1980-01-01'),
-> PARTITION p3 VALUES LESS THAN ('1990-01-01'),
-> PARTITION p4 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.45 sec)
MariaDB [test]> CREATE TABLE employees (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job_code INT NOT NULL,
-> store_id INT NOT NULL
-> )
-> PARTITION BY RANGE (store_id) (
-> PARTITION p0 VALUES LESS THAN (6),
-> PARTITION p1 VALUES LESS THAN (11),
-> PARTITION p2 VALUES LESS THAN (16),
-> PARTITION p3 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.49 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(1,'John','Terry',10,100);
Query OK, 1 row affected (0.06 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(2,'Tom','Carl',10,1);
Query OK, 1 row affected (0.06 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(2,'Lily','Berg',20,7);
Query OK, 1 row affected (0.03 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(3,'Lucy','Phynix',20,10);
Query OK, 1 row affected (0.13 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(4,'Bill','Jones',20,15);
Query OK, 1 row affected (0.02 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(5,'Jill','Deco',30,12);
Query OK, 1 row affected (0.08 sec)
MariaDB [test]> insert into employees(id,fname,lname,job_code,store_id) values(6,'Emily','Aaron',30,20);
Query OK, 1 row affected (0.02 sec)
MariaDB [test]> select * from employees;
+----+-------+--------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+-------+--------+------------+------------+----------+----------+
| 2 | Tom | Carl | 1970-01-01 | 9999-12-31 | 10 | 1 |
| 2 | Lily | Berg | 1970-01-01 | 9999-12-31 | 20 | 7 |
| 3 | Lucy | Phynix | 1970-01-01 | 9999-12-31 | 20 | 10 |
| 4 | Bill | Jones | 1970-01-01 | 9999-12-31 | 20 | 15 |
| 5 | Jill | Deco | 1970-01-01 | 9999-12-31 | 30 | 12 |
| 1 | John | Terry | 1970-01-01 | 9999-12-31 | 10 | 100 |
| 6 | Emily | Aaron | 1970-01-01 | 9999-12-31 | 30 | 20 |
+----+-------+--------+------------+------------+----------+----------+
7 rows in set (0.00 sec)
MariaDB [test]> show create table employees\G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`id` int(11) NOT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL,
`hired` date NOT NULL DEFAULT '1970-01-01',
`separated` date NOT NULL DEFAULT '9999-12-31',
`job_code` int(11) NOT NULL,
`store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (store_id)
(PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (16) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
按照年进行分区
mysql> create table sales(
-> money int unsigned not null,
-> date datetime
-> ) engine=innodb
-> partition by range (year(date)) (
-> partition p2008 values less than (2009),
-> partition p2009 values less than (2010),
-> partition p2010 values less than (2011)
-> );
Query OK, 0 rows affected (0.31 sec)
mysql> insert into sales values (100, '2008-01-01'),(100, '2008-02-01'),(200, '2008-01-02'), (100, '2009-03-01'), (200, '2010-03-01');
Query OK, 5 rows affected (0.13 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table sales drop partition p2008;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain partitions
-> select * from sales
-> where date>='2009-01-01' and date<='2009-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
partitions: p2009
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
1 row in set (0.00 sec)
优化器只能对YEAR()、TO_DAYS()、TO_SECONDS()和UNIX_TIMESTAMP()这类函数进行优化选择
下面这个例子中的分区创建有问题，在分区扫描的时候会扫描多个分区
按照每年每月来进行分区
mysql> create table sales2(
-> money int unsigned not null,
-> date datetime
-> ) engine=innodb
-> partition by range (year(date)*100+month(date)) (
-> partition p201001 values less than (201002),
-> partition p201002 values less than (201003),
-> partition p201003 values less than (201004)
-> );
Query OK, 0 rows affected (0.20 sec)
mysql> explain partitions select * from sales2 where date>='2010-01-01' and date <= '2010-01-31';
+----+-------------+--------+-------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | sales2 | p201001,p201002,p201003 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+-------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
下面例子为上面例子的正确创建方法
mysql> create table sales1(
-> money int unsigned not null,
-> date datetime) engine=innodb
-> partition by range(to_days(date)) (
-> partition p201001
-> values less than(to_days('2010-02-01')),
-> partition p201002
-> values less than(to_days('2010-03-01')),
-> partition p201003
-> values less than (to_days('2010-04-01'))
-> );
Query OK, 0 rows affected (0.20 sec)
mysql> explain partitions select * from sales1 where date>='2010-01-01' and date<='2010-01-31';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | sales1 | p201001 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
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 (0.55 sec)
mysql> system ls -lrt /var/lib/mysql/test
-rw-rw----. 1 mysql mysql 8556 Nov 3 14:22 t.frm
-rw-rw----. 1 mysql mysql 28 Nov 3 14:22 t.par
-rw-rw----. 1 mysql mysql 98304 Nov 3 14:22 t#P#p0.ibd
-rw-rw----. 1 mysql mysql 98304 Nov 3 14:22 t#P#p1.ibd
mysql> select * from information_schema.partitions
-> where table_schema=database() and table_name='t'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: t
PARTITION_NAME: p0
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: id
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 10
TABLE_ROWS: 1
AVG_ROW_LENGTH: 16384
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-11-03 14:22:00
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: t
PARTITION_NAME: p1
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: id
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 20
TABLE_ROWS: 2
AVG_ROW_LENGTH: 8192
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-11-03 14:22:00
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
2 rows in set (0.00 sec)
mysql> insert into t values(50);
ERROR 1526 (HY000): Table has no partition for value 50
mysql> alter table t
-> add partition(
-> partition p2 values less than maxvalue );
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t values(50);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
--LIST分区
MariaDB [test]> CREATE TABLE employees5 (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job_code INT,
-> store_id INT
-> )
-> PARTITION BY LIST(store_id) (
-> PARTITION pNorth VALUES IN (3,5,6,9,17),
-> PARTITION pEast VALUES IN (1,2,10,11,19,20),
-> PARTITION pWest VALUES IN (4,12,13,14,18),
-> PARTITION pCentral VALUES IN (7,8,15,16)
-> );
Query OK, 0 rows affected (5.13 sec)
--COLUMN分区
字段分区是范围分区和列表分区的一种变体，字段分区可以使用多个字段作为分区键。
范围字段分区和列表字段分区支持非整数字段，支持的数据类型如下：
所有整数类型：TINYINT, SMALLINT, MEDIUMINT, INT，BIGINT。
DATE，DATETIME。
CHAR, VARCHAR, BINARY,VARBINARY。
MariaDB [test]> CREATE TABLE rc2 (
-> a INT,
-> b INT
-> )
-> 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)
-> );
Query OK, 0 rows affected (0.27 sec)
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.20 sec)
mysql> create table customers_1 (
-> first_name varchar(25),
-> last_name varchar(25),
-> street_1 varchar(30),
-> street_2 varchar(30),
-> city varchar(15),
-> renewal date
-> )
-> partition by list columns(city) (
-> partition pRegion_1
-> values in ('Oskarshamn', 'Hogsby', 'Monsters'),
-> partition pRegion_2
-> values in ('Vimmerby', 'Hultsfred', 'Vastervik'),
-> partition pRegion_3
-> values in ('Nassjo', 'Eksjo', 'Vetlanda'),
-> partition pRegion_4
-> values in ('Uppvidinge', 'Alvesta', 'Vaxjo')
-> );
Query OK, 0 rows affected (0.23 sec)
mysql> create table rcx(
-> a int,
-> b int,
-> c char(3),
-> d int
-> )engine=innodb
-> partition by range columns(a,d,c) (
-> partition p0 values less than (5,10,'ggg'),
-> partition p1 values less than (10,20,'mmmm'),
-> partition p2 values less than (15,30,'sss'),
-> partition p3 values less than (MAXVALUE,MAXVALUE,MAXVALUE)
-> );
Query OK, 0 rows affected (0.32 sec)
--哈希分区
哈希分区主要确保分区表中的数据均匀分布在各个分区之中。
mysql> create table t_hash(a int,b datetime)engine=innodb
-> partition by hash(year(b))
-> partitions 4;
Query OK, 0 rows affected (7.81 sec)
MariaDB [test]> CREATE TABLE employees7 (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job_code INT,
-> store_id INT
-> )
-> PARTITION BY HASH(store_id)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.22 sec)
MySQL数据库还支持一种称为LINEAR HASH的分区，它使用一个更加复杂的算法来确定新行插入到已经分区的表中的位置
LINEAR HASH分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，这有利于处理含有大量数据的表。LINEAR HASH分区的缺点在于，
与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡
mysql> create table t_linear_hash(
-> a int,
-> b datetime
-> )engine=innodb
-> partition by linear hash(year(b))
-> partitions 4;
Query OK, 0 rows affected (0.23 sec)
--KEY分区
KEY分区类似哈希分区，除了哈希分区使用用户自定义的表达式。分区键列必须包含部分或所有的表的主键。
MariaDB [test]> CREATE TABLE k1 (
-> id INT NOT NULL,
-> name VARCHAR(20),
-> UNIQUE KEY (id)
-> )
-> PARTITION BY KEY()
-> PARTITIONS 2;
Query OK, 0 rows affected (0.11 sec)
--复合分区
MySQL数据库允许在RANGE和LIST的分区上再进行HASH或KEY的子分区
MariaDB [test]> CREATE TABLE ts (id INT, purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) )
-> SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990) (
-> SUBPARTITION s0,
-> SUBPARTITION s1
-> ),
-> PARTITION p1 VALUES LESS THAN (2000) (
-> SUBPARTITION s2,
-> SUBPARTITION s3
-> ),
-> PARTITION p2 VALUES LESS THAN MAXVALUE (
-> SUBPARTITION s4,
-> SUBPARTITION s5
-> )
-> );
Query OK, 0 rows affected (0.51 sec)
mysql> create table ts(a int,b date) engine=innodb
-> partition by range(year(b))
-> subpartition by hash(to_days(b))
-> 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.24 sec)
mysql> system ls -lh /var/lib/mysql/test/ts*
-rw-rw----. 1 mysql mysql 8.4K Nov 4 15:44 /var/lib/mysql/test/ts.frm
-rw-rw----. 1 mysql mysql 96 Nov 4 15:44 /var/lib/mysql/test/ts.par
-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 /var/lib/mysql/test/ts#P#p0#SP#p0sp0.ibd
-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 /var/lib/mysql/test/ts#P#p0#SP#p0sp1.ibd
-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 /var/lib/mysql/test/ts#P#p1#SP#p1sp0.ibd
-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 /var/lib/mysql/test/ts#P#p1#SP#p1sp1.ibd
-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 /var/lib/mysql/test/ts#P#p2#SP#p2sp0.ibd
-rw-rw----. 1 mysql mysql 96K Nov 4 15:44 /var/lib/mysql/test/ts#P#p2#SP#p2sp1.ibd
mysql> create table ts (a int, b date)
-> partition by range (year(b))
-> subpartition by hash( to_days(b)) (
-> partition p0 values less than (1990) (
-> subpartition s0,
-> subpartition s1
-> ),
-> partition p1 values less than (2000) (
-> subpartition s2,
-> subpartition s3
-> ),
-> partition p2 values less than MAXVALUE (
-> subpartition s4,
-> subpartition s5
-> )
-> );
Query OK, 0 rows affected (0.15 sec)
mysql> create table ts (a int,b date) engine=innodb
-> partition by range(year(b))
-> subpartition by hash(to_days(b)) (
-> partition p0 values less than (2000) (
-> subpartition s0
-> data directory = '/disk0/data'
-> index directory ='/disk0/idx',
-> subpartition s1
-> data directory = '/disk1/data'
-> index directory = '/disk1/idx'
-> ),
-> partition p1 values less than (2010) (
-> subpartition s2
-> data directory = '/disk2/data'
-> index directory = '/disk2/idx',
-> subpartition s3
-> data directory = '/disk3/data'
-> index directory = '/disk3/idx'
-> ),
-> partition p2 values less than maxvalue (
-> subpartition s4
-> data directory = '/disk4/data'
-> index directory = '/disk4/idx',
-> subpartition s5
-> data directory = '/disk5/data'
-> index directory = '/disk5/idx'
-> )
-> );
Query OK, 0 rows affected, 6 warnings (0.32 sec)
mysql> show warnings;
+---------+------+----------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------+
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
| Warning | 1618 | option ignored |
+---------+------+----------------------------------+
6 rows in set (0.00 sec)
--查看分区
MariaDB [test]> select * from INFORMATION_SCHEMA.PARTITIONS where table_name = 'employees'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: employees
PARTITION_NAME: p0
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: store_id
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 6
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-07-04 00:42:16
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: employees
PARTITION_NAME: p1
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: store_id
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 11
TABLE_ROWS: 2
AVG_ROW_LENGTH: 8192
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-07-04 00:42:16
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 3. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: employees
PARTITION_NAME: p2
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: store_id
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 16
TABLE_ROWS: 2
AVG_ROW_LENGTH: 8192
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-07-04 00:42:16
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 4. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: employees
PARTITION_NAME: p3
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 4
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: store_id
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: MAXVALUE
TABLE_ROWS: 2
AVG_ROW_LENGTH: 8192
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2016-07-04 00:42:16
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
4 rows in set (0.00 sec)
--查看分区表执行计划
MariaDB [test]> explain partitions select * from employees;
+------+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | employees | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 7 | |
+------+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
MariaDB [test]> explain partitions select * from employees where store_id < 5;
+------+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employees | p0 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+------+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
--增加分区
MariaDB [test]> alter table employees add partition (partition p3 values less than (20));
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> alter table employees add partition (partition p5 values less than maxvalue);
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
--TRUNCATE指定分区
MariaDB [test]> alter table employees truncate partition p0;
Query OK, 0 rows affected (0.12 sec)
--删除指定分区
MariaDB [test]> alter table employees drop partition p0;
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
--将一个分区拆分成多个分区
MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='employees';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p1 | store_id | 11 | 2 |
| p2 | store_id | 16 | 2 |
| p3 | store_id | 20 | 0 |
| p5 | store_id | MAXVALUE | 0 |
+----------------+----------------------+-----------------------+------------+
4 rows in set (0.00 sec)
MariaDB [test]> ALTER TABLE employees
-> REORGANIZE PARTITION p1 INTO (
-> PARTITION n0 VALUES LESS THAN (5),
-> PARTITION n1 VALUES LESS THAN (11)
-> );
Query OK, 2 rows affected (0.49 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='employees';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| n0 | store_id | 5 | 0 |
| n1 | store_id | 11 | 2 |
| p2 | store_id | 16 | 2 |
| p3 | store_id | 20 | 0 |
| p5 | store_id | MAXVALUE | 0 |
+----------------+----------------------+-----------------------+------------+
5 rows in set (0.06 sec)
--将多个分区合并成一个分区
MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='employees';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| n0 | store_id | 5 | 0 |
| n1 | store_id | 11 | 2 |
| p2 | store_id | 16 | 2 |
| p3 | store_id | 20 | 0 |
| p5 | store_id | MAXVALUE | 0 |
+----------------+----------------------+-----------------------+------------+
5 rows in set (0.00 sec)
MariaDB [test]> ALTER TABLE employees
-> REORGANIZE PARTITION n0,n1,p2 INTO (
-> PARTITION p2 VALUES LESS THAN (16));
Query OK, 4 rows affected (0.28 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='employees';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p2 | store_id | 16 | 4 |
| p3 | store_id | 20 | 0 |
| p5 | store_id | MAXVALUE | 0 |
+----------------+----------------------+-----------------------+------------+
3 rows in set (0.03 sec)
--减少哈希分区的数量
MariaDB [test]> create table emp2(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.60 sec)
MariaDB [test]> alter table emp2 coalesce partition 2;
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> show create table emp2\G
*************************** 1. row ***************************
Table: emp2
Create Table: CREATE TABLE `emp2` (
`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
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (store_id)
PARTITIONS 2 */
1 row in set (0.00 sec)
增加哈希分区的数量
MariaDB [test]> alter table emp2 add partition partitions 5;
Query OK, 0 rows affected (0.54 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> show create table emp2\G
*************************** 1. row ***************************
Table: emp2
Create Table: CREATE TABLE `emp2` (
`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
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (store_id)
PARTITIONS 7 */
1 row in set (0.00 sec)
在表和分区间交换数据
mysql> create table e (
-> id int not null,
-> fname varchar(30),
-> lname varchar(30)
-> )
-> partition by range(id) (
-> partition p0 values less than (50),
-> partition p1 values less than (100),
-> partition p2 values less than (150),
-> partition p3 values less than (MAXVALUE)
-> );
Query OK, 0 rows affected (0.32 sec)
mysql> insert into e values (1669,"Jim","Smith"),(337,"Mary","Jones"),(16,"Frank","White"),(2005,"Linda","Black");
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
创建交换表
mysql> create table e2 like e;
Query OK, 0 rows affected (0.29 sec)
mysql> show create table e2\G
*************************** 1. row ***************************
Table: e2
Create Table: CREATE TABLE `e2` (
`id` int(11) NOT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
将分区表改成普通表
mysql> alter table e2 remove partitioning;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table e2\G
*************************** 1. row ***************************
Table: e2
Create Table: CREATE TABLE `e2` (
`id` int(11) NOT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
将e表的分区p0中的数据移动到
名称栏目：MySQL分区介绍
当前路径：http://myzitong.com/article/ijhghh.html