29.笔记 MySQL学习——使用分区表

来源：互联网发布：电子数据交换名词解释编辑：程序博客网时间：2024/05/24 04:02

29.笔记 MySQL学习——使用分区表

MySQL支持表分区，可以让表的内容分散存储在不同的物理存储位置。

表分区可以有如下好处：

l 表存储分布在多个设备上

l 优化器可以把本地化搜索限定在某个特定的分区

创建分区，要指定PARTITION BY子句

分区函数可以根据范围、值列表或散列值来分配各行

例如：

create table log_partition

( dtdatetime not null,

infovarchar(100) not null,

index(dt)) partition by range(year(dt))

(partition p0 values less than ( 2010),

partitionp1 values less than (2011),

partition p2 values less than (2012),

partitionp3 values less than (2013),

partitionpmax values less than maxvalue

);

Query OK, 0 rows affected (0.03 sec)

当2014年可以继续进行划分

如下：

mysql> alter table log_partition reorganizepartition pmax

-> into(

->partition p4 values less than (2014),

->partition pmax values less than maxvalue

-> );

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table log_partition;

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

| log_partition | CREATE TABLE`log_partition` (

`dt` datetime NOT NULL,

`info` varchar(100) NOT NULL,

KEY`dt` (`dt`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY RANGE (year(dt))

(PARTITION p0 VALUES LESS THAN (2010)ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (2011) ENGINE =InnoDB,

PARTITION p2 VALUES LESS THAN (2012) ENGINE =InnoDB,

PARTITION p3 VALUES LESS THAN (2013) ENGINE =InnoDB,

PARTITION p4 VALUES LESS THAN (2014) ENGINE =InnoDB,