mysql 增加表分区（MySQL表分区）
mysql 增加表分区（MySQL表分区）# a.创建分区表 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 NOT NULL store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6) PARTITION p1 VALUES LESS THAN (11) PARTITION p2 VALUES LESS THAN (16) PARTITION
mysql可以通过SHOW PLUGINS语句的输出来确定MySQL Server是否支持分区。
mysql> SHOW PLUGINS;
------------ ----------
| Name | Status
------------ ----------
| partition | ACTIVE
------------ ----------
也可以通过INFORMATION_SCHEMA.PLUGINS来查看
mysql> SELECT
-> PLUGIN_NAME as Name
-> PLUGIN_VERSION as Version
-> PLUGIN_STATUS as Status
-> FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE PLUGIN_TYPE='STORAGE ENGINE';
--------------------
| Name | Status |
-------------------- --------- --------
| partition |ACTIVE |
-------------------- --------- --------
如果在输出中看到partition 的Status列为ACTIVE则说明MySQL支持分区功能。
1、MySQL分区概述分区是根据一定的规则，把一个表分解成多个更小的、更容易管理的部分。分区表逻辑上是一个表，但实际上可能有N个物理分区对象组成，每个分区都是一个独立的对象，可以独立处理。每个分区中既存放了数据又存放了索引。
MySQL数据库支持的常用分区类型：
- range分区：行数据根据列值在给定范围内将行分配给分区。
- list分区：和RANGE分区类似，区别在于list分区是基于一组离散值匹配的列进行选择的。
- hash分区：根据用户定义的表达式返回的值来选择一个分区，返回值不能是负数。
- key分区：根据MySQL服务器提供的哈希函数来进行分区。
不论何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的组成部分。
2、MySQL分区类型- range分区
- list分区
- hash分区
- key分区
- columns 分区
- 子分区
range分区，是最常用的分区类型。
示例：
# a.创建分区表
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 NOT NULL
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6)
PARTITION p1 VALUES LESS THAN (11)
PARTITION p2 VALUES LESS THAN (16)
PARTITION p3 VALUES LESS THAN (21)
);
查看表在磁盘的物理文件，启用分区之后，表变成由建立分区时的各个分区ibd文件组成。
分区表物理文件
# b.插入数据
insert into employees
values(2 'Mitchell' 'Wilson' '2020-04-19' '2020-04-19' 2 2);
# c.查看数据
select * from employees;
# d.查看分区信息
select table_name partition_name table_rows
from information_schema.partitions
where table_schema=database()
and table_name='employees';
range分区表信息
2.2 list分区list分区和range分区类似，只是分区列的值是离散的，而非连续的。
示例：
# a.建表
drop table if exists t_columns_range;
CREATE TABLE t_columns_range (
a INT
b datetime
)
PARTITION BY range columns(b)(
PARTITION p0 values less than ('2019-01-01')
PARTITION p1 values less than ('2020-01-01')
);
# b.插入数据
insert into t_columns_range values(1 '2018-01-01') (2 '2019-01-01');
# c.查看数据
select * from t_columns_range;
# d.查看分区信息
select table_name partition_name table_rows
from information_schema.partitions
where table_schema=database()
and table_name='t_columns_range';2.3 hash分区
hash分区的目的是将数据均匀分布到预先定义的各个分区中。
示例：
# a.建表
drop table if exists t_hash;
CREATE TABLE t_hash (col1 INT col2 CHAR(5) col3 DATE)
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;
# b.写入数据
insert into t_hash values(1 'a' '2020-01-01') (2 'b' '2021-01-02') (3 'c' '2022-01-03') (4 'a' '2023-01-01');
# c.查看数据
SELECT * FROM t_hash;
# d.查看分区信息
select table_name partition_name table_rows
from information_schema.partitions
where table_schema=database()
and table_name='t_hash';2.4 key分区
key分区和hash分区相似，key分区使用MySQL数据库提供的函数进行分区。
示例：
# a.建表
drop table if exists t_key;
CREATE TABLE t_key (
id INT NOT NULL
name VARCHAR(20)
UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;
# b.插入数据
insert into t_key values(1 'a') (2 'b') (3 'c') (4 'a');
# c.查看数据
SELECT * FROM t_key;
# d.查看分区信息
select table_name partition_name table_rows
from information_schema.partitions
where table_schema=database()
and table_name='t_key';2.5 columns分区
columns分区是在RANGE和LIST 分区的变体，可以直接使用非整型的数据进行分区，而range/list/hash/key这四种分区的分区条件必须是整型。
示例：
# a.建表
CREATE TABLE ts (id INT purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990)
PARTITION p1 VALUES LESS THAN (2000)
PARTITION p2 VALUES LESS THAN MAXVALUE
);
columns list分区
# a.建columns list分区表
CREATE TABLE customers_1 (
first_name VARCHAR(25)
last_name VARCHAR(25)
street_1 VARCHAR(30)
street_2 VARCHAR(30)
city VARCHAR(15)
renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
PARTITION pRegion_1 VALUES IN('Oskarshamn' 'Högsby' 'Mönsterås')
PARTITION pRegion_2 VALUES IN('Vimmerby' 'Hultsfred' 'Västervik')
PARTITION pRegion_3 VALUES IN('Nässjö' 'Eksjö' 'Vetlanda')
PARTITION pRegion_4 VALUES IN('Uppvidinge' 'Alvesta' 'Växjo')
);2.6 子分区
子分区是分区表中每个分区的进一步分区，MySQL数据库允许在RANGE和 LIST分区上再进行HASH或KEY分区。
示例：
# a.建表
CREATE TABLE ts (id INT purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990)
PARTITION p1 VALUES LESS THAN (2000)
PARTITION p2 VALUES LESS THAN MAXVALUE
);
表ts有3个RANGE 分区。这些分区中的每个分区p0，p1和p2进一步分为2个子分区。实际上，整个表分为多个 3 * 2 = 6分区。
示例：
# a.建表
CREATE TABLE ts (id INT purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0
SUBPARTITION s1
)
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2
SUBPARTITION s3
)
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4
SUBPARTITION s5
)
);
使用SUBPARTITION子句明确定义子分区， 以指定各个子分区的选项。
