=================================================================================
Mysql在5.1版本时增加对分区表的支持,但并不是所有存储引擎都支持分区表,常见的Innodb/MyISAM/NDB等引擎都支持分区表。
使用SHOW PLUGINS来查看数据库是否启用分区功能。
当前Mysql支持一下分区方式:
1、 Range分区,基于连续的范围区间来分区。 2、 List分区,基于离散值集合来分区。 3、 Hash分区,基于用户定义的表达式的返回值来进行选择的分区。 4、 Key分区,类似于HASH分区,只支持对单列或多列使用MySQL自身HASH函数。
=================================================================================
查看分区
--====================================== --查看表中每个分区的数据量 SELECT table_name,partition_name,table_rows FROM information_schema.PARTITIONS WHERE table_schema=DATABASE() AND table_name='' \G --====================================== --查看当前实例中使用分区的表 SELECT TABLE_SCHEMA AS database_name, TABLE_NAME AS table_name, COUNT(1) AS partition_count FROM INFORMATION_SCHEMA.PARTITIONS WHERE partition_name IS NOT NULL GROUP BY TABLE_SCHEMA ,TABLE_NAME;
=================================================================================
分区与唯一索引
无论使用何种类型的分区,如果表中存在主键或唯一索引建时,分区列必须是唯一索引的一个组成部分。
对于SQL SERVER表而言,可以是表分区但表上的部分非聚集索引不分区,也可以表不分区但表上部分非聚集索引分区。
但对MySQL而言,如果表分区,则该分区表上所有索引都是分区索引,在表上建唯一索引时,唯一索引键必须包含表的分区列,否则会报以下错误:
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
=================================================================================
分区与NULL值
在分区中,NULL值小于任何非NULL值,与ORDER BY 排序类似。
对于RANGE分区,NULL值会被放置当分区中的最左边的分区。
对于LIST分区,必须在定义时指明NULL分区放置到哪一个分区下,否则插入NULL值会报错。
对于KEY和HASH分区,NULL值对任何分区函数都会返回值0。
=================================================================================
创建分区
Range分区Demo
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
## 使用Range分区的注意事项
## 1、避免分区表达式过度复杂,复杂表达式可能会导致查询优化器无法判断分区使用
## 2、必须保证所有表达式的值都被RANGE覆盖,否则会报:ERROR 1526 (HY000): Table has no partition for value xxx
CREATE TABLE TB001 (
C1 INT NOT NULL,
C2 VARCHAR(30),
C3 INT
)partition BY RANGE (C3%400) (
partition p0 VALUES LESS THAN (100),
partition p1 VALUES LESS THAN (200),
partition p2 VALUES LESS THAN (300),
partition p3 VALUES LESS THAN (400)
);
List分区Demo
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE TABLE TB001 (
C1 INT NOT NULL,
C2 VARCHAR(30),
C3 INT
)partition BY LIST (C3%8) (
partition p0 VALUES LESS THAN (0,1),
partition p1 VALUES LESS THAN (2,3),
partition p2 VALUES LESS THAN (4,5),
partition p3 VALUES LESS THAN (6,7)
);
Hash分区Demo
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
## 使用HASH分区的注意事项
## 1、必须保证Exprssion返回的值为正数
## 2、通过partitionNumbers指明要分区的数量
CREATE TABLE TB001 (
C1 INT NOT NULL,
C2 VARCHAR(30),
C3 INT
)partition BY HASH(C2) PARTITIONS 4;
Key分区Demo
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE TABLE TB001 (
C1 INT NOT NULL,
C2 VARCHAR(30),
C3 INT
)partition BY HASH(C3) PARTITIONS 4;
=================================================================================
分区交换
在MYSQL 5.6版本开始,提供了分区交换的功能。
分区交换Demo
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
-- ====================================== -- 创建分区表 DROP TABLE PB_001; CREATE TABLE PB_001 ( ID INT AUTO_INCREMENT PRIMARY KEY, C1 VARCHAR(200) )PARTITION BY RANGE(ID) ( PARTITION P0 VALUES LESS THAN (100), PARTITION P1 VALUES LESS THAN (200), PARTITION P2 VALUES LESS THAN (1000), PARTITION P3 VALUES LESS THAN (MAXVALUE) ); -- ===================================== -- 插入测试数据 INSERT INTO PB_001(C1) SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS \G -- ===================================== -- 查看每个分区的数据 SELECT table_name,partition_name,table_rows FROM information_schema.PARTITIONS WHERE table_schema=DATABASE() AND table_name='' \G -- ===================================== -- 创建分区切换的表 CREATE TABLE PB_001_His LIKE PB_001; -- ===================================== -- 去除PB_001_His 分区 ALTER TABLE PB_001_His REMOVE PARTITIONING; -- ===================================== -- 切换分区 ALTER TABLE PB_001 EXCHANGE PARTITION P0 WITH TABLE PB_001_His;
=================================================================================
删除分区
## 删除指定分区
ALTER TABLE table_name DROP PARTITION parition_name ;
## 将分区表修改为非分区表
alter table table_name remove partitioning;