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