mysql表分区的使用与底层原理详解

-- 创建分区必须包含所有主键
create table user_11(
  id bigint(20) not null ,
  name varchar(20) ,
  age int(3),
	PRIMARY KEY (`id`,`age`)
)
-- 创建分区
partition by range columns(id,age)(
  partition p00 values less than(6,30), -- 小于6的值在P0分区
  partition p11 values less than(11,40), -- 小于11的值在p1分区
  partition p22 values less than(16,50), -- 小于16的值在p2分区
  partition p33 values less than (9999,9999) -- 大于21的值在p3分区，或者用一个更大的值
);

-- 创建分区必须包含所有唯一键
create table user_22(
  id bigint(20)  not null,
  name varchar(20) ,
  age int(3) not null ,
	unique key only_one_1(age,id )
)
-- 创建分区
partition by range columns(id,age)(
  partition p000 values less than(6,30), -- 小于6的值在P0分区
  partition p111 values less than(11,40), -- 小于11的值在p1分区
  partition p222 values less than(16,50), -- 小于16的值在p2分区
  partition p333 values less than (9999,9999) -- 大于21的值在p3分区，或者用一个更大的值
);

create table employees(
id bigint(20) not null,
age int(3) not null,
name varchar(20)
)
-- 创建分区
partition by range (age)(
  partition p0 values less than(6), -- 小于6的值在P0分区
  partition p1 values less than(11), -- 小于11的值在p1分区
  partition p2 values less than(16), -- 小于16的值在p2分区
  partition p3 values less than(21) -- 小于21的值在p3分区
);

[root@VM_0_5_Centos test]# pwd
/var/lib/mysql/test
[root@VM_0_5_centos test]# ll
总用量 8741504
-rw-rw---- 1 mysql mysql         61 10月 31 2018 db.opt
-rw-rw---- 1 mysql mysql       8614 8月   1 21:30 employees.frm
-rw-rw---- 1 mysql mysql         32 8月   1 21:30 employees.par
-rw-rw---- 1 mysql mysql      98304 8月   1 21:30 employees#P#p0.ibd
-rw-rw---- 1 mysql mysql      98304 8月   1 21:30 employees#P#p1.ibd
-rw-rw---- 1 mysql mysql      98304 8月   1 21:30 employees#P#p2.ibd
-rw-rw---- 1 mysql mysql      98304 8月   1 21:30 employees#P#p3.ibd

mysql> insert employees (id,name,age) values(1,'yexindong',21);
ERROR 1526 (HY000): Table has no partition for value 21

create table employees(
id bigint(20) not null,
age int(3) not null,
name varchar(20)
)
-- 创建分区
partition by range (age)(
  partition p0 values less than(6), -- 小于6的值在P0分区
  partition p1 values less than(11), -- 小于11的值在p1分区
  partition p2 values less than(16), -- 小于16的值在p2分区
  partition p3 values less than maxvalue -- 大于16的值在p3分区，或者用一个更大的值
);

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,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991), -- 1991年之前的数据在P0分区
    PARTITION p1 VALUES LESS THAN (1996),-- 1996年之前的数据在P1分区
    PARTITION p2 VALUES LESS THAN (2001),-- 2001年之前的数据在P2分区
    PARTITION p3 VALUES LESS THAN MAXVALUE -- 2001年制后的数据在P3分区
);

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

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,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17), -- 3,5,6,9,17的值放在pNorth分区
    PARTITION pEast VALUES IN (1,2,10,11,19,20),-- 1,2,10,11,19,20的值放在pEast分区
    PARTITION pWest VALUES IN (4,12,13,14,18),-- 4,12,13,14,18的值放在pWest分区
    PARTITION pCentral VALUES IN (7,8,15,16)-- 7,8,15,16的值放在pCentral分区
);

-- 普通字段的分区
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,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 5;--创建5个分区，分别是0，1，2，3，4

-- 创建时间类型的分区
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,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4; -- 创建四个分区，分别为0，1，2，3

-- 以主键进行分区
CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2; -- 创建2个分区分别为P0和P1，这里是hash分区的变种，存储方式和hash分区一样


-- 以唯一键进行分区
CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 3;-- 创建三个分区，分别是p0，p1，p2

-- 指定主键字段进行分区
CREATE TABLE tm1 (
    s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10; -- 创建10个分区

 -- 表中有3个分区，每个分区上有2个子分区，所以加起来一共有6个分区
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
    );

-rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p0#SP#p0sp0.ibd
-rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p0#SP#p0sp1.ibd
-rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p1#SP#p1sp0.ibd
-rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p1#SP#p1sp1.ibd
-rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p2#SP#p2sp0.ibd
-rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p2#SP#p2sp1.ibd

-- 添加列表分区
alter table titles add partition(partition p7 values in('CEO'));