1.mysql ：RANGE/LIST/HASH/KEY
RANGE: ， 。
LIST: RANGE ， LIST 。
HASH: ， 。 MySQL 、 。
KEY: HASH ， KEY ， MySQL 。 。
//
range ： ,mysql5.5 range columns 。
list ： ，mysql5.5 list columns。
hash ： ， 。
key ： MYSQL 。
： ， ， 0 ；
RANGE LIST , HASH KEY 。
2.
CREATE [TEMPORARY] 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] ...)]
3.
(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;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
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,col3,col4)) partition by hash(col3) partitions 4;
(2) ， ， 。
create table part2
(col1 int not null,
col2 varchar(50) not null,
col3 int not null,
col4 int,
unique key(col1,col2,col3,col4))
partition by hash(col3)
partitions 5;
(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 5;
4.
1)RANGE
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 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 where from_date < '1985-12-31';
2)LIST
drop table titles;
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 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 where title='manger';
3)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;
4)KEY
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 key(emp_no)
partitions 4;
//
explain partitions select * from titles where emp_no = 499999;