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

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,

 PARTITION pmax VALUES LESS THAN MAXVALUEENGINE = InnoDB) */ |

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

1 row in set (0.00 sec)

若想将存储分散到其他地方，则需要用到分区选项 DATA_DIRECTORY和INDEX_DIRECTORY。