mysql partition分区

2019独角兽企业重金招聘Python工程师标准>>>

1.mysql支持的分区
range分区：行数据基于属于一个给定连续区间的列值被放入分区,mysql5.5开始支持range columns的分区。
list分区：分区面向的是离散的值，mysql5.5支持list columns。
hash分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。
key分区：根据MYSQL数据库提供的哈希函数来进行分区。


2.基本语法

CREATE TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options];

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

range和list支持子分区，但子分区只支持hash和key分区


3.分区限制

//提示：使用innodb创建分区表时，需要在配置文件添加innodb_file_per_table=1

(1)创建任何类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。
mysql> create table part1
(col1 int not null,
col2 varchar(50) not null,
col3 int not null,
col4 int not null,
unique key(col1,col2))
partition by hash(col3)可以是列和自定义的表达式 整形数值
partitions 4;
错误： A PRIMARY KEY must include all columns in the table's partitioning     


(2)唯一索引可以为NULL值，并且分区列只要是唯一索引的一个组成部分，不需要整个唯一索引都是分区列。

create table part3
(col1 int not null,
col2 varchar(50) not null,
col3 int not null,
col4 int not null,
unique key(col1,col2,col3,col4))
partition by hash(col3)
partitions 10;


(3)如果表中没有指定主键，唯一索引，可以指定任何一列为分区列。


create table part3
(col1 int not null,
col2 varchar(50) not null,
col3 int not null,
col4 int not null)
partition by hash(col3)
partitions 10;


4.创建各种分区 5.5版本

###range分区

 CREATE TABLE titles_range (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by range columns(from_date)
(partition p01 values less than ('1985-12-31'),
partition p02 values less than ('1986-12-31'),
partition p03 values less than ('1987-12-31'),
partition p04 values less than ('1988-12-31'),
partition p05 values less than ('1989-12-31'),
partition p06 values less than ('1990-12-31'),
partition p07 values less than ('1991-12-31'),
partition p08 values less than ('1992-12-31'),
partition p09 values less than ('1993-12-31'),
partition p10 values less than ('1994-12-31'),
partition p11 values less than ('1995-12-31'),
partition p12 values less than ('1996-12-31'),
partition p13 values less than ('1997-12-31'),
partition p14 values less than ('1998-12-31'),
partition p15 values less than ('1999-12-31'),
partition p16 values less than ('2000-12-31'),
partition p17 values less than ('2001-12-31'),
partition p18 values less than ('2002-12-31'),
partition p19 values less than ('3000-12-31')
);
explain partitions select * from titles_range where from_date < '1985-12-31';


###list分区


CREATE TABLE titles_list (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by list columns(title)
(
partition p0 values in ('Assistant Engineer'),
partition p1 values in ('Engineer'),
partition p2 values in ('Manager'),
partition p3 values in ('Senior Engineer'),
partition p4 values in ('Senior Staff'),
partition p5 values in ('Staff'),
partition p6 values in ('Technique Leader')
);

//查看执行计划
explain partitions select * from titles_list where title = 'Manager';


###hash分区

CREATE TABLE titles (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by hash(emp_no)
partitions 4;

//查看执行计划
explain partitions select * from titles where emp_no = 499999;


+----+-------------+-------------+------------+------+----------------+---------+---------+-------+------+-------+
| id | select_type | table       | partitions | type | possible_keys  | key     | key_len | ref   | rows | Extra |
+----+-------------+-------------+------------+------+----------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | titles_hash | p3         | ref  | PRIMARY,emp_no | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------------+------------+------+----------------+---------+---------+-------+------+-------+

###key分区

---passwrod()

CREATE TABLE titles_key (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by key(emp_no)
partitions 4;

//查看执行计划
explain partitions select * from titles_key where emp_no = 499999;
+----+-------------+------------+------------+------+----------------+---------+---------+-------+------+-------+
| id | select_type | table      | partitions | type | possible_keys  | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+------------+------+----------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | titles_key | p0         | ref  | PRIMARY,emp_no | PRIMARY | 4       | const |    1 |       |
+----+-------------+------------+------------+------+----------------+---------+---------+-------+------+-------+

转载于:https://my.oschina.net/u/1458120/blog/203983