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 partitionedmysql> 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 nulland 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 COLUMNSCREATE 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 expressionmysql> 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 oneCREATE TABLE k1 (id INT NOT NULL PRIMARY KEY,name VARCHAR(20))PARTITION BY KEY()PARTITIONS 2;--对于已经创建的分区，最好不要改变它们的sql_modeit is strongly recommended that you never change the server SQL mode after creating partitioned tablesSometimes a change in the server SQL mode can make partitioned tables unusableDiffering SQL modes on master and slave can lead to partitioning expressions being evaluated differently