MySQL Partition--分区基础

=================================================================================

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

## 使用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

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

## 使用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

CREATE TABLE TB001 (
    C1 INT NOT NULL,
    C2 VARCHAR(30),
    C3 INT
)partition BY HASH(C3) PARTITIONS 4;

 

=================================================================================

分区交换

在MYSQL 5.6版本开始，提供了分区交换的功能。

分区交换Demo

 

-- ======================================
-- 创建分区表
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;