mysql分区

2012-10-11 10:04 轩脉刃阅读(5617) 评论(2) 编辑收藏举报

mysql的分区概念

在mysql 5.1.3之后引入和partition（分区）的概念。这个是个好东东。以前遇到一个大表的时候，我们会手动将其分为几个小表（就是分表操作）。但是分表操作有几个缺点：1 麻烦，这里指的麻烦是不仅对于调用分表的sql语句的处理上，也是分表的具体操作步骤上。2 当一个表从单表变为分表的时候，上层sql逻辑也需要进行修改，而且是大范围的修改!

引入分区的概念就简单多了。强调一点，一个表分区了，它还是一个表！一个表分表了，它就不是一个表了！这实际上是个会让你不得不使用分区的理由，当你对一个表分区的时候，上层的代码逻辑是不用变的。

分区就是一个表分割在不同的磁盘文件中。

你可以使用mysql> SHOW VARIABLES LIKE '%partition%'; 确定mysql是否支持partition

分区类型

分区的关键词就是partition，它分为Range, List, Hash, Key这几种分割方法

Range partition: 按照某个字段的范围进行分区。这种常用在时间字段上。

比如使用场景是论坛的文章表，论坛的文章最经常进行的查询也不过就是在最近几个月的论坛文章中进行操作。所以这样的文章内容使用时间进行分区是最好不过的了。

CREATE TABLE article (

article_id int NOT NULL AUTO_INCREMENT,

article_content varchar(1000),

article_time DATE NOT NULL DEFAULT '1970-01-01',

key(article_id)

)

ENGINE=INNODB

PARTITION BY RANGE (YEAR(article_time)) (

PARTITION p0 VALUES LESS THAN (1991),

PARTITION p1 VALUES LESS THAN (1996),

PARTITION p2 VALUES LESS THAN (2001),

PARTITION p3 VALUES LESS THAN MAXVALUE

) ;

上面的语句就是按照年份将文章分为4个分区

List partition:按照某个字段有哪些值分在一个分区内

直接拿官网的一个sql语句做例子

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)

);

Hash partition: 按照表中任意个int类型的字段进行分割

CREATE TABLE part_table (

id int NOT NULL AUTO_INCREMENT,

name varchar(30),

store_id int,

key(id)

)

ENGINE=INNODB

PARTITION BY HASH(store_id) 

PARTITIONS 4;

Key partition： Hash partition的进阶版，将主键作为分割的标准

比如

CREATE TABLE part_table2 (

id int NOT NULL AUTO_INCREMENT PRIMARY KEY,

name varchar(30),

store_id int

)

ENGINE=INNODB

PARTITION BY KEY() 

PARTITIONS 4;

恰当的分区当然比单表是好多的，不仅利于管理，也利于提高查询效率。分区由于代价非常小，所以在一般的使用上应该先考虑使用它。但是它也不是万能钥匙，分区说到底还是在一个mysql上进行的操作，如果数据量达到一定程度，还是会需要分库分表来进行操作的。