欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL分区之RANGE分区_MySQL

程序员文章站 2022-06-04 13:25:28
...
bitsCN.com


MySQL分区之RANGE分区

环境:

[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

而只需删除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

*************************** 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

㈡ 常见相关问题

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

[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

*************************** 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

*************************** 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)

bitsCN.com
相关标签: mysql