part 1: 需求
很多时候,如果一个表的数据足够大的时候,操作(query,insert) 的效率就会降低。此时将一个表分区就变得必要
part 2: 帮助信息
url 1: http://dev.mysql.com/doc/refman/5.1/en/partitioning.html
# mysql 官方文档,讲解mysql partition
url 2: http://dev.mysql.com/doc/refman/5.1/en/alter-table-partition-operations.html
# mysql官方文档,讲解mysqll 修改分区
url 3: http://www.jzxue.com/shujuku/mysql/200912/24-3297.html
or http://www.jz123.cn/text/0826274.html
# 如果英文或是耐心不是太差,看url 1 和 url 2 的英文文档再加上测试就足够了。这里的中文文档也挺好的,可以看一下。
part 3: 概要讲解 & 应用举例
1:mysql partition 的类型主要有 range, list, hash, key 四种, 具体的语法结构和示例如下:
type 1: range
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),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
type 2: list
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),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
type 3: hash
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;
type 4: key
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
2:mysql 子分区
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
);
3: mysql 添加分区
mysql>alter table T_LOCATION add partition (partition p2012_09_01 values less than (TO_DAYS('2012-09-1')));
4: mysql 删除分区
mysql>alter table T_LOCATION_days drop partition p2012_09_01 ;
#NOTE: hash 分区中有 coalesce 分区
5: mysql 修改分区
mysql> alter table T_LOCATION reorganize partition p2012_09_01 into (partition p2012_10_01 values less than (TO_DAYS('2012-10-1')));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
7: 查看分区信息
from INFORMATION_SCHEMA.PARTITIONS
where TABLE_SCHEMA = 'LN_PABB' ;
8: 显示query 涉及的分区
mysql> explain partitions select count(*) from T_LOCATION_days where timestamp = '2010-05-05' \G; -- and timestamp < '2010-07-27' \G;
part 4: 常见问题
#todo
part 5: 规律总结
#todo