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中的資料移動到表e2中，p0分割槽中的資料被移到表e2中
mysql> alter table e exchange partition p0 with table e2;
Query OK, 0 rows affected (0.17 sec)
mysql> select partition_name,table_rows from information_schema.partitions where table_name='e';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0 | 0 |
| p1 | 0 |
| p2 | 0 |
| p3 | 2 |
+----------------+------------+
4 rows in set (0.00 sec)
mysql> select * from e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)
--查詢指定分割槽
MariaDB [test]> select * from employees partition(p1);
+----+-------+--------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+-------+--------+------------+------------+----------+----------+
| 2 | Lily | Berg | 1970-01-01 | 9999-12-31 | 20 | 7 |
| 3 | Lucy | Phynix | 1970-01-01 | 9999-12-31 | 20 | 10 |
+----+-------+--------+------------+------------+----------+----------+
2 rows in set (0.00 sec)
--將非分割槽錶轉換成分割槽表
MariaDB [test]> CREATE TABLE employees2 (
-> 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
-> );
Query OK, 0 rows affected (0.08 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(1,'John','Terry',10,100);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(2,'Tom','Carl',10,1);
Query OK, 1 row affected (0.03 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(2,'Lily','Berg',20,7);
Query OK, 1 row affected (0.04 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(3,'Lucy','Phynix',20,10);
Query OK, 1 row affected (0.06 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(4,'Bill','Jones',20,15);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(5,'Jill','Deco',30,12);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into employees2(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 employees2;
+----+-------+--------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+-------+--------+------------+------------+----------+----------+
| 1 | John | Terry | 1970-01-01 | 9999-12-31 | 10 | 100 |
| 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 |
| 6 | Emily | Aaron | 1970-01-01 | 9999-12-31 | 30 | 20 |
+----+-------+--------+------------+------------+----------+----------+
7 rows in set (0.00 sec)
MariaDB [test]> alter table employees2
-> 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, 7 rows affected (0.59 sec)
Records: 7 Duplicates: 0 Warnings: 0
--測試NULL值在分割槽中的儲存
RANGE分割槽中，NULL值會被當作最小值來處理；LIST分割槽中，NULL值必須出現在列舉列表中；HASH/KEY分割槽中，NULL值會被當作零值來處理
MariaDB [test]> create table tb_range(id int,name varchar(5))
-> partition by range(id)
-> (
-> partition p0 values less than(-6),
-> partition p1 values less than(0),
-> partition p2 values less than(1),
-> partition p3 values less than maxvalue
-> );
Query OK, 0 rows affected (0.69 sec)
MariaDB [test]> insert into tb_range values(null,'null');
Query OK, 1 row affected (0.02 sec)
MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='tb_range';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0 | id | -6 | 1 |
| p1 | id | 0 | 0 |
| p2 | id | 1 | 0 |
| p3 | id | MAXVALUE | 0 |
+----------------+----------------------+-----------------------+------------+
4 rows in set (0.00 sec)
MariaDB [test]> create table tb_list(id int,name varchar(5))
-> partition by list(id)
-> (
-> partition p1 values in (0),
-> partition p2 values in (1)
-> );
Query OK, 0 rows affected (0.15 sec)
MariaDB [test]> insert into tb_list values(null,'null');
ERROR 1526 (HY000): Table has no partition for value NULL
MariaDB [test]> insert into tb_list values(null,'null');
ERROR 1526 (HY000): Table has no partition for value NULL
MariaDB [test]> create table tb_hash(id int,name varchar(5))
-> partition by hash(id)
-> partitions 2;
Query OK, 0 rows affected (0.13 sec)
MariaDB [test]> insert into tb_hash values(null, 'null');
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='tb_hash';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0 | id | NULL | 1 |
| p1 | id | NULL | 0 |
+----------------+----------------------+-----------------------+------------+
2 rows in set (0.00 sec)
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中的資料移動到表e2中，p0分割槽中的資料被移到表e2中
mysql> alter table e exchange partition p0 with table e2;
Query OK, 0 rows affected (0.17 sec)
mysql> select partition_name,table_rows from information_schema.partitions where table_name='e';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0 | 0 |
| p1 | 0 |
| p2 | 0 |
| p3 | 2 |
+----------------+------------+
4 rows in set (0.00 sec)
mysql> select * from e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)
--查詢指定分割槽
MariaDB [test]> select * from employees partition(p1);
+----+-------+--------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+-------+--------+------------+------------+----------+----------+
| 2 | Lily | Berg | 1970-01-01 | 9999-12-31 | 20 | 7 |
| 3 | Lucy | Phynix | 1970-01-01 | 9999-12-31 | 20 | 10 |
+----+-------+--------+------------+------------+----------+----------+
2 rows in set (0.00 sec)
--將非分割槽錶轉換成分割槽表
MariaDB [test]> CREATE TABLE employees2 (
-> 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
-> );
Query OK, 0 rows affected (0.08 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(1,'John','Terry',10,100);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(2,'Tom','Carl',10,1);
Query OK, 1 row affected (0.03 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(2,'Lily','Berg',20,7);
Query OK, 1 row affected (0.04 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(3,'Lucy','Phynix',20,10);
Query OK, 1 row affected (0.06 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(4,'Bill','Jones',20,15);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into employees2(id,fname,lname,job_code,store_id) values(5,'Jill','Deco',30,12);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into employees2(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 employees2;
+----+-------+--------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+-------+--------+------------+------------+----------+----------+
| 1 | John | Terry | 1970-01-01 | 9999-12-31 | 10 | 100 |
| 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 |
| 6 | Emily | Aaron | 1970-01-01 | 9999-12-31 | 30 | 20 |
+----+-------+--------+------------+------------+----------+----------+
7 rows in set (0.00 sec)
MariaDB [test]> alter table employees2
-> 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, 7 rows affected (0.59 sec)
Records: 7 Duplicates: 0 Warnings: 0
--測試NULL值在分割槽中的儲存
RANGE分割槽中，NULL值會被當作最小值來處理；LIST分割槽中，NULL值必須出現在列舉列表中；HASH/KEY分割槽中，NULL值會被當作零值來處理
MariaDB [test]> create table tb_range(id int,name varchar(5))
-> partition by range(id)
-> (
-> partition p0 values less than(-6),
-> partition p1 values less than(0),
-> partition p2 values less than(1),
-> partition p3 values less than maxvalue
-> );
Query OK, 0 rows affected (0.69 sec)
MariaDB [test]> insert into tb_range values(null,'null');
Query OK, 1 row affected (0.02 sec)
MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='tb_range';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0 | id | -6 | 1 |
| p1 | id | 0 | 0 |
| p2 | id | 1 | 0 |
| p3 | id | MAXVALUE | 0 |
+----------------+----------------------+-----------------------+------------+
4 rows in set (0.00 sec)
MariaDB [test]> create table tb_list(id int,name varchar(5))
-> partition by list(id)
-> (
-> partition p1 values in (0),
-> partition p2 values in (1)
-> );
Query OK, 0 rows affected (0.15 sec)
MariaDB [test]> insert into tb_list values(null,'null');
ERROR 1526 (HY000): Table has no partition for value NULL
MariaDB [test]> insert into tb_list values(null,'null');
ERROR 1526 (HY000): Table has no partition for value NULL
MariaDB [test]> create table tb_hash(id int,name varchar(5))
-> partition by hash(id)
-> partitions 2;
Query OK, 0 rows affected (0.13 sec)
MariaDB [test]> insert into tb_hash values(null, 'null');
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> select partition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='tb_hash';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0 | id | NULL | 1 |
| p1 | id | NULL | 0 |
+----------------+----------------------+-----------------------+------------+
2 rows in set (0.00 sec)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2121610/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 分割槽介紹總結MySql
- MySQL Key分割槽表建立方法介紹MySql
- mysql表分割槽技術詳細介紹MySql
- mysql分割槽功能、例項詳細介紹MySql
- Oracle分割槽表介紹Oracle
- 分割槽表基礎介紹
- Linux Swap交換分割槽介紹總結Linux
- Oracle的分割槽修剪介紹：Partition PruningOracle
- mysql 分割槽MySql
- MySQL分割槽MySql
- windows11怎麼分割槽硬碟 win11硬碟分割槽大小方法介紹Windows硬碟
- 理解MySQL分割槽MySql
- 搞懂MySQL分割槽MySql
- 【MYSQL】 分割槽表MySql
- MySql建立分割槽MySql
- MySQL 子分割槽MySql
- MySQL KEY分割槽MySql
- MySQL HASH分割槽MySql
- MySQL COLUMNS分割槽MySql
- MySQL LIST分割槽MySql
- MySQL RANGE分割槽MySql
- MySQL 分割槽表MySql
- MySQL分割槽表MySql
- mysql分割槽nullMySqlNull
- mysql 分割槽示例MySql
- MySql資料分割槽操作之新增分割槽操作MySql
- MySQL 分割槽表 partition線上修改分割槽欄位MySql
- MySQL的分割槽（一）MySql
- MySQL的分割槽（二）MySql
- MySQL 分割槽表探索MySql
- mysql 分表 分割槽MySql
- MySQL分割槽學習MySql
- MySQL 分割槽建索引MySql索引
- mysql 分割槽表用法MySql
- MySQL表分割槽管理MySql
- MySQL分割槽表的分割槽原理和優缺點MySql
- 將mysql非分割槽錶轉換為分割槽表MySql
- MySQL分割槽如何遷移MySql