MySQL分区之RANGE分区_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方技术论坛-国内领先的Weblogic、Tuxedo中间件技术论坛
联动北方-国内领先的云技术服务提供商
»  游客             当前位置：  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2247 | 回复: 0   主题： MySQL分区之RANGE分区        下一篇 
薯片
注册用户
等级：中士
经验：237
发帖：75
精华：0
注册：2012-2-10
状态：离线
发送短消息息给薯片 加好友    发送短消息息给薯片 发消息
发表于： IP：您无权察看 2014-12-18 16:07:44 | [全部帖] [楼主帖] 楼主

[sql]
mysql> select version()\G;
*************************** 1. row ***************************
version(): 5.5.28


         ㈠ 主要应用场景

         RANGE分区主要用于日期列的分区
         例如销售类的表，可以根据年份来分区存储销售记录
         如下是对sales表进行分区

[sql]
mysql> create table sales(money int unsigned not null,
-> date datetime
-> )engine=innodb
-> partition by range (year(date)) (
-> partition p2008 values less than (2009),
-> partition p2009 values less than (2010),
-> partition p2010 values less than (2011)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into sales SELECT 100,'2008-01-01';
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into sales SELECT 100,'2008-02-01';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into sales SELECT 200,'2008-01-02';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into sales SELECT 100,'2008-03-01';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into sales SELECT 100,'2009-03-01';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into sales SELECT 200,'2010-03-01';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from sales;
+-------+---------------------+
| money | date                |
+-------+---------------------+
|   100 | 2008-01-01 00:00:00 |
|   100 | 2008-02-01 00:00:00 |
|   200 | 2008-01-02 00:00:00 |
|   100 | 2008-03-01 00:00:00 |
|   100 | 2009-03-01 00:00:00 |
|   200 | 2010-03-01 00:00:00 |
+-------+---------------------+
6 rows in set (0.00 sec)


          ① 便于对sales表管理，如果要删除2008年的数据，我们就不需要执行：

 delete from sales where date>= '2008-01-01' and date<'2009-01-01'


             而只需删除2008年数据所在的分区即可

[sql]
mysql> alter table sales drop partition p2008;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from sales;
+-------+---------------------+
| money | date                |
+-------+---------------------+
|   100 | 2009-03-01 00:00:00 |
|   200 | 2010-03-01 00:00:00 |
+-------+---------------------+
2 rows in set (0.00 sec)


          ② 另一个好处是加快某些查询操作，例如，我们只需要查询2009年整年的销售额

[sql]
mysql> explain partitions
-> select * from sales
-> where date>='2009-01-01' and date<='2009-12-31'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
partitions: p2009
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)


          SQL优化器会进行分区修剪，即只搜索p2009
          也请注意分区的边界，如date<'2010-01-01',那么优化器会连带搜索p2010分区

          ㈡ 常见相关问题

          ① 插入了一个不在分区中定义的值

[sql]
mysql> insert into sales select 200,'2012-12-3';
ERROR 1526 (HY000): Table has no partition for value 2012
mysql> show create table sales \G;
*************************** 1. row ***************************
Table: sales
Create Table: CREATE TABLE `sales` (
`money` int(10) unsigned NOT NULL,
`date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (year(date))
(PARTITION p2009 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p2010 VALUES LESS THAN (2011) ENGINE = InnoDB) */
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> alter table sales add partition(
-> partition p2012 values less than maxvalue);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> insert into sales select 200,'2012-12-3';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from sales where date='2012-12-3';
+-------+---------------------+
| money | date                |
+-------+---------------------+
|   200 | 2012-12-03 00:00:00 |
+-------+---------------------+
1 row in set (0.00 sec)


          ② 对RANGE分区的查询，优化器只能对year(),to_days(),to_seconds()和unix_timestamp()这类函数进行优化选择

[sql]
mysql> create table t (date datetime)
-> engine=innodb
-> partition by range (year(date)*100+month(date)) (
-> partition p201201 values less than (201202),
-> partition p201202 values less than (201203),
-> partition p201203 values less than (201204)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t select '2012-01-01';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t select '2012-01-06';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t select '2012-02-06';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t select '2012-01-06';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t select '2012-03-06';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t select '2012-02-01';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from t;
+---------------------+
| date                |
+---------------------+
| 2012-01-01 00:00:00 |
| 2012-01-06 00:00:00 |
| 2012-01-06 00:00:00 |
| 2012-02-06 00:00:00 |
| 2012-02-01 00:00:00 |
| 2012-03-06 00:00:00 |
+---------------------+
6 rows in set (0.00 sec)
mysql> explain partitions
-> select * from t
-> where date>='2012-01-01' and date<='2012-01-31'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: p201201,p201202,p201203
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 6
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> drop table t;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t (date datetime)
-> engine=innodb
-> partition by range (to_days(date)) (
-> partition p201201 values less than (to_days('2012-02-01')),
-> partition p201201 values less than (to_days('2012-03-01')),
-> partition p201201 values less than (to_days('2012-04-01'))
-> );
mysql> insert into t select '2012-01-02';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t select '2012-01-03';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t select '2012-01-08';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t select '2012-02-08';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> insert into t select '2012-03-08';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from t;
+---------------------+
| date                |
+---------------------+
| 2012-01-02 00:00:00 |
| 2012-01-03 00:00:00 |
| 2012-01-08 00:00:00 |
| 2012-02-08 00:00:00 |
| 2012-03-08 00:00:00 |
+---------------------+
5 rows in set (0.00 sec)
mysql> explain partitions
-> select * from t
-> where date>='2012-01-01' and date<='2012-01-31'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: p1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.00 sec)


--转自 北京联动北方科技有限公司


赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码： 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论

Weblogic中间件技术论坛

 |

Tuxedo中间件技术论坛

 |

数据库论坛

 |

Java论坛

 |

Linux/unix论坛

 |

网站地图


Powered by LandingBJ     @ 2008-2018     联动北方   
京ICP证号090597    京ICP备案09017624号    京公网安备110112000093号    
400-810-2327     Email：service@landingbj.com   

Powered by landingbj @ 2008-2015 联动北方
京ICP证号090597 京ICP备案09017624号 京公网安备110112000093号
86-27-87056338 Email：service@landingbj.com