MySQL 作为开源数据库已经支持分区表，而 PostgreSQL 在分区表方面本身做得不是很好，虽然可以通过继承实现表分区功能。接下来打算了解 MySQL 分区表相关的内容。
MySQL 分区表有 RANGE, LIST, HASH, KEY 四种类型, 本文介绍 RANGE 分区类型。
一 查看是否支持分区
首先查看所安装的 MySQL 数据库是否分区，有两种方法，第一种是通过 “show plugins;” 命令查看，另一种是通过以下 SQL 查看：
1 2 3 4
root @localhost:(none)>use information_schema select plugin_name,plugin_version,plugin_status ,plugin_type from plugins where plugin_type ='STORAGE ENGINE' ;
如图
备注：如果以上结果没有 partition 为 active 的信息，则当前 MySQL 数据库不支持分区。
二 RANGE 分区举例: 通过 RANGE COLUMNS 分区方式
建表
1 2 3 4 5 6 7 8 9 10 11
create table tbl_access_log ( id bigint , name varchar (64 ), create_time datetime ) PARTITION BY RANGE COLUMNS (create_time) ( PARTITION p201410 values less than ('2014-11-01 00:00:00' ), PARTITION p201411 values less than ('2014-12-01 00:00:00' ), PARTITION p201412 values less than ('2015-01-01 00:00:00' ), PARTITION pmax values less than maxvalue );
查看分区
1 2 3 4 5 6 7 8 9 10
root@localhost:information_schema>select table_schema,table_name,partition_name,PARTITION_METHOD from information_schema.partitions where table_name='tbl_access_log'; +--------------+----------------+----------------+------------------+ | table_schema | table_name | partition_name | PARTITION_METHOD | +--------------+----------------+----------------+------------------+ | francs | tbl_access_log | p201410 | RANGE COLUMNS | | francs | tbl_access_log | p201411 | RANGE COLUMNS | | francs | tbl_access_log | p201412 | RANGE COLUMNS | | francs | tbl_access_log | pmax | RANGE COLUMNS | +--------------+----------------+----------------+------------------+ 4 rows in set (0.01 sec)
插入数据测试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
root@localhost:francs>insert into tbl_access_log values(1 ,'a' ,'2014-10-01 12:00:00' ); Query OK, 1 row affected (0 .06 sec) root@localhost:francs>insert into tbl_access_log values(1 ,'a' ,'2014-10-01 13:00:00' ); Query OK, 1 row affected (0 .06 sec) root@localhost:francs>insert into tbl_access_log values(1 ,'a' ,'2014-11-01 13:00:00' ); Query OK, 1 row affected (0 .00 sec) root@localhost:francs>insert into tbl_access_log values(1 ,'a' ,'2015-01-01 13:00:00' ); Query OK, 1 row affected (0 .00 sec) root@localhost:francs>select * from tbl_access_log; +------+------+---------------------+ | id | name | create_time | +------+------+---------------------+ | 1 | a | 2014-10-01 12:00:00 | | 1 | a | 2014-10-01 13:00:00 | | 1 | a | 2014-11-01 13:00:00 | | 1 | a | 2015-01-01 13:00:00 | +------+------+---------------------+ 4 rows in set (0 .00 sec)
查询指定分区
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
root@localhost:francs>select * from tbl_access_log PARTITION ( p201410); +------+------+---------------------+ | id | name | create_time | +------+------+---------------------+ | 1 | a | 2014 -10 -01 12 :00 :00 | | 1 | a | 2014 -10 -01 13 :00 :00 | +------+------+---------------------+ 2 rows in set (0.00 sec) root@localhost:francs>select * from tbl_access_log PARTITION (p201411); +------+------+---------------------+ | id | name | create_time | +------+------+---------------------+ | 1 | a | 2014 -11 -01 13 :00 :00 | +------+------+---------------------+ 1 row in set (0.00 sec) root@localhost:francs>select * from tbl_access_log PARTITION (pmax); +------+------+---------------------+ | id | name | create_time | +------+------+---------------------+ | 1 | a | 2015 -01 -01 13 :00 :00 | +------+------+---------------------+ 1 row in set (0.00 sec)
三 RANGE 分区举例: 通过 RANGE 分区方式
建表
1 2 3 4 5 6 7 8 9 10 11
create table tbl_access_log_unix ( id bigint , name varchar (64 ), create_time TIMESTAMP ) PARTITION BY RANGE ( UNIX_TIMESTAMP (create_time) ) ( PARTITION p201410 values less than (UNIX_TIMESTAMP ('2014-11-01 00:00:00' )), PARTITION p201411 values less than (UNIX_TIMESTAMP ('2014-12-01 00:00:00' )), PARTITION p201412 values less than (UNIX_TIMESTAMP ('2015-01-01 00:00:00' )), PARTITION pmax values less than (maxvalue) );
查看分区类型
1 2 3 4 5 6 7 8 9 10
root@localhost:information_schema>select table_schema,table_name,partition_name,PARTITION_METHOD from partitions where table_name='tbl_access_log_unix'; +--------------+---------------------+----------------+------------------+ | table_schema | table_name | partition_name | PARTITION_METHOD | +--------------+---------------------+----------------+------------------+ | francs | tbl_access_log_unix | p201410 | RANGE | | francs | tbl_access_log_unix | p201411 | RANGE | | francs | tbl_access_log_unix | p201412 | RANGE | | francs | tbl_access_log_unix | pmax | RANGE | +--------------+---------------------+----------------+------------------+ 4 rows in set (0.02 sec)
备注：以上介绍了两种 RANGE 分区例子。第一种是 RANGE COLUMNS partitioning 方式，第二种是 RANGE partitioning 方式，两种方式不同，主要区别是RANGE COLUMNS partitioning 方式可以基于多个字段分区，同时支持整型以外的字段分区。
四 参考
原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/239604.html
