mysql分区管理

最新推荐文章于 2024-08-08 14:13:02 发布

rudy_gao

最新推荐文章于 2024-08-08 14:13:02 发布

阅读量838

点赞数

分类专栏： mysql_优化

本文链接：https://blog.csdn.net/rudygao/article/details/50470256

版权

本文介绍了MySQL 5.6后的分区管理，包括查看分区、创建分区的限制、在不同分区类型中处理NULL值的方式，以及RANGE COLUMNS、HASH和KEY分区的特性。还展示了创建和查询分区表的示例。

摘要由CSDN通过智能技术生成

--在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