mysql的range分区案例
1、实验案例mysql5.5 关于 range分区2.要求，有一个员工表有（money,date）,现在根据date字段进行分区
3.创建分区表
CREATE TABLE staff(
money INT NOT NULL DEFAULT 1000,
date_timeDATE
)
PARTITION BY RANGE COLUMNS (date_time)
(
PARTITION p01 VALUES LESS THAN ('2009-01-01'),
PARTITION p02 VALUES LESS THAN ('2010-01-01'),
PARTITION p03 VALUES LESS THAN ('2011-01-01'),
PARTITION p04 VALUES LESS THAN (maxvalue)
);
4.添加数据
insert into staff (money,date_time) values (3000,'2008-06-07');
insert into staff (money,date_time) values (5000,'2009-06-07');
insert into staff (money,date_time) values (8000,'2010-06-07');
insert into staff (money,date_time) values (9000,'2011-06-07');
insert into staff (money,date_time) values (13000,date(now()));
5.查询分区数据（自动进行分区修剪,优化器会自动搜索p02分区）
5.1查询2009年的的所有薪水
mysql> explain partitions select * from staff where date_time>='2009-01-01' and date_time= '2009-01-01' AND date_time < '2010-01-01'
方法2：>
在大量历史记录的情况下，直接用方法2，会非常好用。
7.查看test数据下staff具体形式
# ll
total 636
-rw-rw---- 1 mysql mysql 61 Jun 26 09:32 db.opt
-rw-rw---- 1 mysql mysql 8749 Jun 26 11:00 order1.frm
-rw-rw---- 1 mysql mysql 114688 Jun 26 11:16 order1.ibd
-rw-rw---- 1 mysql mysql 8602 Sep 11 12:40 staff.frm
-rw-rw---- 1 mysql mysql 36 Sep 11 12:40 staff.par
-rw-rw---- 1 mysql mysql98304 Sep 11 12:40 staff#P#p01.ibd
-rw-rw---- 1 mysql mysql98304 Sep 11 12:40 staff#P#p02.ibd
-rw-rw---- 1 mysql mysql98304 Sep 11 12:40 staff#P#p03.ibd
-rw-rw---- 1 mysql mysql98304 Sep 11 12:40 staff#P#p04.ibd
-rw-rw---- 1 mysql mysql 8645 Jun 26 09:49 user.frm
-rw-rw---- 1 mysql mysql98304 Jun 26 11:16 user.ibd
#
8.显示创建表结构
mysql> show create table staff\G;
*************************** 1. row ***************************
Table: staff
Create Table: CREATE TABLE `staff` (
`money` int(11) NOT NULL DEFAULT '1000',
`date_time` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGECOLUMNS(date_time)
(PARTITION p01 VALUES LESS THAN ('2009-01-01') ENGINE = InnoDB,
PARTITION p02 VALUES LESS THAN ('2010-01-01') ENGINE = InnoDB,
PARTITION p03 VALUES LESS THAN ('2011-01-01') ENGINE = InnoDB,
PARTITION p04 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
1 row in set (0.00 sec)
页:
[1]