mysql分区管理

--在mysql5.6之后查看分区采用
show plugins;
 
--不论创建何种类型的分区,如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分
mysql> create table t1(id int not null,id2 int not null,unique key(id)) partition by hash(id2) partitions 4;    
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table is partitioning function
 
 
--innodb与分区表不兼容
Partitioned InnoDB tables cannot have foreign key references, nor can they have columns referenced by foreign keys.
InnoDB tables which have or which are referenced by foreign keys cannot be partitioned
mysql> alter table t2 add foreign key(id) references t1(id);
ERROR 1215 (HY000): Cannot add foreign key constraint
 
--查看分区表
select * from information_schema.partitions p where p.partition_name is not null
and p.table_name='t1';
 
 
--查看某一分区执行计划
mysql> explain partitions select * from t1 where id2=1;  
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | p1         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where | 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
 
--查看某一分区内数据，如果有多个分区要查时，请使用分号隔开
mysql> select * from t1 partition(p1);                  
+----+-----+
| id | id2 |
+----+-----+
|  4 |   5 | 
|  8 |   9 | 
+----+-----+
 
 
--mysql不支持在日期类型上直接创建分区，必需借助于函数
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,
store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
 
--按月进行分区，还是利用的函数
CREATE TABLE t (
id INT NOT NULL,
fired_date DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY RANGE ( extract(YEAR_MONTH from fired_date) ) (
PARTITION p0 VALUES LESS THAN (201601),
PARTITION p1 VALUES LESS THAN (201602),
PARTITION p2 VALUES LESS THAN (201603),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
 
 
--通过使用RANGE COLUMNS而不再需要使用函数
The use of partitioning columns employing date or time types other than DATE or DATETIME is not supported with RANGE COLUMNS
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE 
)
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
);
 
--对于range partition的null，mysql默认把它当作最小的值看待，如果列值为空，其会把它插入到第一个分区中
MariaDB [test]> insert into members(firstname,lastname,username) values ('rudy','gao','rudy.gao');
Query OK, 1 row affected (0.01 sec)
 
MariaDB [test]> select * from members;
+-----------+----------+----------+-------+--------+
| firstname | lastname | username | email | joined |
+-----------+----------+----------+-------+--------+
| rudy      | gao      | rudy.gao | NULL  | NULL   |
+-----------+----------+----------+-------+--------+
1 row in set (0.00 sec)
--在第一个分区中查询
MariaDB [test]> select * from members partition(p0);
+-----------+----------+----------+-------+--------+
| firstname | lastname | username | email | joined |
+-----------+----------+----------+-------+--------+
| rudy      | gao      | rudy.gao | NULL  | NULL   |
+-----------+----------+----------+-------+--------+
 
 
--对于list partition的null，如果没有指定一个list存储null时，其是不允许插入null值的
--A table that is partitioned by LIST admits NULL values if and only if one of its partitions is defined using that value-list that contains NULL. 
--The converse of this is that a table partitioned by LIST which does not explicitly use NULL in a value list rejects rows resulting in a NULL value for the partitioning expression
mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
ERROR 1504 (HY000): Table has no partition for value NULL
 
--对于hash或者key分区，null值被当做0处理
--NULL is handled somewhat differently for tables partitioned by HASH or KEY. 
--In these cases, any partition expression that yields a NULL value is treated as though its return value were zero
 
 
 
--注意对于RANGE COLUMNS其不能是表达式，但其可以接受多个列
? RANGE COLUMNS does not accept expressions, only names of columns.
? RANGE COLUMNS accepts a list of one or more columns
--如果RANGE COLUMNS有多列时，其必须满足所有列都符合相应条件时，才放入相对应的分区，否则会默认分区
 
 
CREATE TABLE rc1 (a INT,b INT)
PARTITION BY RANGE COLUMNS(a, b) (
PARTITION p0 VALUES LESS THAN (5, 12),
PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);
mysql> INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);
--查看各个分区表中行数
mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'rc1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          2 |
| p3             |          1 |
+----------------+------------+
 
--其类似于如下的sql比较
mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);
 
 
--创建以key为分区的表，需要一个主键，如果没有，则mysql使用默认的虚拟主键
--KEY takes only a list of zero or more column names. Any columns used as the partitioning key must comprise part or all of the table's primary key, 
--if the table has one. Where no column name is specified as the partitioning key, the table's primary key is used, if there is one
CREATE TABLE k1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;
 
 
 
--对于已经创建的分区，最好不要改变它们的sql_mode
it is strongly recommended that you never change the server SQL mode after creating partitioned tables
Sometimes a change in the server SQL mode can make partitioned tables unusable
Differing SQL modes on master and slave can lead to partitioning expressions being evaluated differently