mysql range自动分区管理,MySQL RANGE分区表

最新推荐文章于 2023-02-20 20:08:29 发布

脱衣有肉

最新推荐文章于 2023-02-20 20:08:29 发布

阅读量355

点赞数

文章标签： mysql range自动分区管理

696 total views, 4 views today

一、开启分区表功能

MySQL想要使用分区表，必须安装partition插件。

查看是否安装了partition插件。

mysql> show plugins;

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

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

...........

...........

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

42 rows in set (0.00 sec)

SELECT

PLUGIN_NAME as Name,

PLUGIN_VERSION as Version,

PLUGIN_STATUS as Status

FROM INFORMATION_SCHEMA.PLUGINS

WHERE PLUGIN_TYPE='STORAGE ENGINE';

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

| Name | Version | Status |

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

| binlog | 1.0 | ACTIVE |

| MRG_MYISAM | 1.0 | ACTIVE |

| MEMORY | 1.0 | ACTIVE |

| CSV | 1.0 | ACTIVE |

| MyISAM | 1.0 | ACTIVE |

| PERFORMANCE_SCHEMA | 0.1 | ACTIVE |

| InnoDB | 5.6 | ACTIVE |

| BLACKHOLE | 1.0 | ACTIVE |

| FEDERATED | 1.0 | DISABLED |

| ARCHIVE | 3.0 | ACTIVE |

| partition | 1.0 | ACTIVE |

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

11 rows in set (0.00 sec)

mysql>showplugins;

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

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

...........

...........

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

42rowsinset(0.00sec)

SELECT

PLUGIN_NAMEasName,

PLUGIN_VERSIONasVersion,

PLUGIN_STATUSasStatus

FROMINFORMATION_SCHEMA.PLUGINS

WHEREPLUGIN_TYPE='STORAGE ENGINE';

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

|Name|Version|Status|

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

|binlog|1.0|ACTIVE|

|MRG_MYISAM|1.0|ACTIVE|

|MEMORY|1.0|ACTIVE|

|CSV|1.0|ACTIVE|

|MyISAM|1.0|ACTIVE|

|PERFORMANCE_SCHEMA|0.1|ACTIVE|

|InnoDB|5.6|ACTIVE|

|BLACKHOLE|1.0|ACTIVE|

|FEDERATED|1.0|DISABLED|

|ARCHIVE|3.0|ACTIVE|

|partition|1.0|ACTIVE|

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

11rowsinset(0.00sec)

MySQL 5.6二进制版本默认提供分区功能；如果是编译安装，那么编译时请加上 -DWITH_PARTITION_STORAGE_ENGINE 选项。

如果不想使用分区，启动MySQL服务请加上–skip-partition 选项。

分区表中的分区必须为同一种存储引擎。

分区不能使用 MERGE, CSV, or FEDERATED 存储引擎。

创建hash分区表ti，并分配6个分区。

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)

ENGINE=INNODB

PARTITION BY HASH( MONTH(tr_date) )

PARTITIONS 6;

CREATETABLEti(idINT,amountDECIMAL(7,2),tr_dateDATE)

ENGINE=INNODB

PARTITIONBYHASH(MONTH(tr_date))

PARTITIONS6;

可以看到，每个分区都有单独的.idb文件

# ll -th

总用量 596K

-rw-rw---- 1 mysql mysql 96K 2月 6 14:05 ti#P#p1.ibd

-rw-rw---- 1 mysql mysql 96K 2月 6 14:05 ti#P#p3.ibd

-rw-rw---- 1 mysql mysql 96K 2月 6 14:05 ti#P#p5.ibd

-rw-rw---- 1 mysql mysql 96K 2月 6 14:05 ti#P#p0.ibd

-rw-rw---- 1 mysql mysql 96K 2月 6 14:05 ti#P#p2.ibd

-rw-rw---- 1 mysql mysql 96K 2月 6 14:05 ti#P#p4.ibd

-rw-rw---- 1 mysql mysql 44 2月 6 14:05 ti.par

-rw-rw---- 1 mysql mysql 8.5K 2月 6 14:05 ti.frm

-rw-rw---- 1 mysql mysql 61 2月 6 14:05 db.opt

# ll -th

总用量596K

-rw-rw----1mysqlmysql96K2月614:05ti#P#p1.ibd

-rw-rw----1mysqlmysql96K2月614:05ti#P#p3.ibd

-rw-rw----1mysqlmysql96K2月614:05ti#P#p5.ibd

-rw-rw----1mysqlmysql96K2月614:05ti#P#p0.ibd

-rw-rw----1mysqlmysql96K2月614:05ti#P#p2.ibd

-rw-rw----1mysqlmysql96K2月614:05ti#P#p4.ibd

-rw-rw----1mysqlmysql442月614:05ti.par

-rw-rw----1mysqlmysql8.5K2月614:05ti.frm

-rw-rw----1mysqlmysql612月614:05db.opt

分区操作针对一张表的数据和所有索引，你不能单独对数据分区，而不对索引分区。

二、分区表的优点

●分区表可以存储更多数据，不同的分区可以存放在单独磁盘或者系统分区上。

●便于清理不需要的数据，使用drop partition即可。当然，也能为新数据创建分区。

●优化SQL查询，自适应where条件，只扫描where条件过滤后数据对应的分区，排除了其他不需要的分区。另外，可以根据日常的SQL，将热数据重组到一个分区，能够提高不少性能。从5.6开始，查询支持指定分区。如 SELECT * FROM t PARTITION (p0,p1) WHERE c < 5，该SQL只扫描p0和p1分区，然后使用where条件过滤。大大提升了SQL查询速度。分区表支持 DELETE, INSERT, REPLACE, UPDATE, LOAD DATA, LOAD XML等命令。

●聚合函数默认并行化执行，例如 SUM() 和 COUNT()。 SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id; 这条SQL，在每个分区同时并行执行，最后将结果合并得出最终结果。

●如果将数据分布到不同磁盘上，可以大大提高吞吐量。

三、分区表类型

1、范围分区(RANGE partitioning)

顾名思义，每个分区中存放的数据都有指定的范围，数据符合这个范围就存放到对应的分区中。范围界定使用 VALUES LESS THAN 关键字。

##1、普通数值类型的分区，如INT类型

创建range分区表，以store_id列作为分区列，store_id<6落在p0分区，6

这里没有指定存储引起，它以MySQL默认为准。

CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT NOT NULL,

store_id INT NOT NULL

)

PARTITION BY RANGE (store_id) (

PARTITION p0 VALUES LESS THAN (6),

PARTITION p1 VALUES LESS THAN (11),

PARTITION p2 VALUES LESS THAN (16),

PARTITION p3 VALUES LESS THAN (21)

);

CREATETABLEemployees(

idINTNOTNULL,

fnameVARCHAR(30),

lnameVARCHAR(30),

hiredDATENOTNULLDEFAULT'1970-01-01',

separatedDATENOTNULLDEFAULT'9999-12-31',

job_codeINTNOTNULL,

store_idINTNOTNULL

)

PARTITIONBYRANGE(store_id)(

PARTITIONp0VALUESLESSTHAN(6),

PARTITIONp1VALUESLESSTHAN(11),

PARTITIONp2VALUESLESSTHAN(16),

PARTITIONp3VALUESLESSTHAN(21)

);

分区表的详细信息，查询 information_schema.PARTITIONS 可以得到。

字段解释

TABLE_SCHEMA：表属于的database名字。

PARTITION_NAME：分区名。

PARTITION_ORDINAL_POSITION：分区序号。

PARTITION_METHOD：分区类型，如RANGE、LIST、HASH等。

PARTITION_DESCRIPTION：用于RANGE和LIST分区，分区范围的值。

select TABLE_SCHEMA,

TABLE_NAME,

PARTITION_NAME,

PARTITION_METHOD,

PARTITION_EXPRESSION,

PARTITION_DESCRIPTION,

TABLE_ROWS,

PARTITION_ORDINAL_POSITION,

CREATE_TIME,

UPDATE_TIME,

TABLESPACE_NAME

from information_schema.PARTITIONS

where TABLE_NAME = 'employees'

order by PARTITION_ORDINAL_POSITION;

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

| szlsd | employees | p0 | RANGE | store_id | 6 | 0 | 1 | 2017-02-07 11:05:19 | NULL | NULL |

| szlsd | employees | p1 | RANGE | store_id | 11 | 0 | 2 | 2017-02-07 11:05:19 | NULL | NULL |

| szlsd | employees | p2 | RANGE | store_id | 16 | 0 | 3 | 2017-02-07 11:05:19 | NULL | NULL |

| szlsd | employees | p3 | RANGE | store_id | 21 | 0 | 4 | 2017-02-07 11:05:19 | NULL | NULL |

4 rows in set (0.00 sec)

selectTABLE_SCHEMA,

TABLE_NAME,

PARTITION_NAME,

PARTITION_METHOD,

PARTITION_EXPRESSION,

PARTITION_DESCRIPTION,

TABLE_ROWS,

PARTITION_ORDINAL_POSITION,

CREATE_TIME,

UPDATE_TIME,

TABLESPACE_NAME

frominformation_schema.PARTITIONS

whereTABLE_NAME='employees'

orderbyPARTITION_ORDINAL_POSITION;

|szlsd|employees|p0|RANGE|store_id|6|0|1|2017-02-0711:05:19|NULL|NULL|

|szlsd|employees|p1|RANGE|store_id|11|0|2|2017-02-0711:05:19|NULL|NULL|

|szlsd|employees|p2|RANGE|store_id|16|0|3|2017-02-0711:05:19|NULL|NULL|

|szlsd|employees|p3|RANGE|store_id|21|0|4|2017-02-0711:05:19|NULL|NULL|

4rowsinset(0.00sec)

竖排版更利于阅读

select TABLE_SCHEMA,

TABLE_NAME,

PARTITION_NAME,

PARTITION_METHOD,

PARTITION_EXPRESSION,

PARTITION_DESCRIPTION,

TABLE_ROWS,

PARTITION_ORDINAL_POSITION,

CREATE_TIME,

UPDATE_TIME,

TABLESPACE_NAME

from information_schema.PARTITIONS

where TABLE_NAME = 'employees'

order by PARTITION_ORDINAL_POSITION\G

*************************** 1. row ***************************

TABLE_SCHEMA: szlsd

TABLE_NAME: employees

PARTITION_NAME: p0

PARTITION_METHOD: RANGE

PARTITION_EXPRESSION: store_id

PARTITION_DESCRIPTION: 6

TABLE_ROWS: 0

PARTITION_ORDINAL_POSITION: 1

CREATE_TIME: 2017-02-07 11:05:19

UPDATE_TIME: NULL

TABLESPACE_NAME: NULL

*************************** 2. row ***************************

TABLE_SCHEMA: szlsd

TABLE_NAME: employees

PARTITION_NAME: p1

PARTITION_METHOD: RANGE

PARTITION_EXPRESSION: store_id

PARTITION_DESCRIPTION: 11

TABLE_ROWS: 0

PARTITION_ORDINAL_POSITION: 2

CREATE_TIME: 2017-02-07 11:05:19

UPDATE_TIME: NULL

TABLESPACE_NAME: NULL

*************************** 3. row ***************************

TABLE_SCHEMA: szlsd

TABLE_NAME: employees

PARTITION_NAME: p2

PARTITION_METHOD: RANGE

PARTITION_EXPRESSION: store_id

PARTITION_DESCRIPTION: 16

TABLE_ROWS: 0

PARTITION_ORDINAL_POSITION: 3

CREATE_TIME: 2017-02-07 11:05:19

UPDATE_TIME: NULL

TABLESPACE_NAME: NULL

*************************** 4. row ***************************

TABLE_SCHEMA: szlsd

TABLE_NAME: employees

PARTITION_NAME: p3

PARTITION_METHOD: RANGE

PARTITION_EXPRESSION: store_id

PARTITION_DESCRIPTION: 21

TABLE_ROWS: 0

PARTITION_ORDINAL_POSITION: 4

CREATE_TIME: 2017-02-07 11:05:19

UPDATE_TIME: NULL

TABLESPACE_NAME: NULL

4 rows in set (0.00 sec)

selectTABLE_SCHEMA,

TABLE_NAME,

PARTITION_NAME,

PARTITION_METHOD,

PARTITION_EXPRESSION,

PARTITION_DESCRIPTION,

TABLE_ROWS,

PARTITION_ORDINAL_POSITION,

CREATE_TIME,

UPDATE_TIME,

TABLESPACE_NAME

frominformation_schema.PARTITIONS

whereTABLE_NAME='employees'

orderbyPARTITION_ORDINAL_POSITION\G

***************************1.row ***************************

TABLE_SCHEMA:szlsd

TABLE_NAME:employees

PARTITION_NAME:p0

PARTITION_METHOD:RANGE

PARTITION_EXPRESSION:store_id

PARTITION_DESCRIPTION:6

TABLE_ROWS:0

PARTITION_ORDINAL_POSITION:1

CREATE_TIME:2017-02-0711:05:19

UPDATE_TIME:NULL

TABLESPACE_NAME:NULL

***************************2.row ***************************

TABLE_SCHEMA:szlsd

TABLE_NAME:employees

PARTITION_NAME:p1

PARTITION_METHOD:RANGE

PARTITION_EXPRESSION:store_id

PARTITION_DESCRIPTION:11

TABLE_ROWS:0

PARTITION_ORDINAL_POSITION:2

CREATE_TIME:2017-02-0711:05:19

UPDATE_TIME:NULL

TABLESPACE_NAME:NULL

***************************3.row ***************************

TABLE_SCHEMA:szlsd

TABLE_NAME:employees

PARTITION_NAME:p2

PARTITION_METHOD:RANGE

PARTITION_EXPRESSION:store_id

PARTITION_DESCRIPTION:16

TABLE_ROWS:0

PARTITION_ORDINAL_POSITION:3

CREATE_TIME:2017-02-0711:05:19

UPDATE_TIME:NULL

TABLESPACE_NAME:NULL

***************************4.row ***************************

TABLE_SCHEMA:szlsd

TABLE_NAME:employees

PARTITION_NAME:p3

PARTITION_METHOD:RANGE

PARTITION_EXPRESSION:store_id

PARTITION_DESCRIPTION:21

TABLE_ROWS:0

PARTITION_ORDINAL_POSITION:4

CREATE_TIME:2017-02-0711:05:19

UPDATE_TIME:NULL

TABLESPACE_NAME:NULL

4rowsinset(0.00sec)

熟悉Oracle分区表的朋友，应该会发现上面分区表创建的有问题。没错，就是没有MAXVALUE分区，MAXVALUE 分区用于存放大于现有分区范围的数据。一是避免忘记分配分区，造成数据无法插入；二是插入的数据存在很大不确定性，用于存储这些不确定数据。

RANGE分区表必须含有MAXVALUE分区，语法 LESS THAN MAXVALUE 。大于16的数据都会存储在p3分区红。

CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT NOT NULL,

store_id INT NOT NULL

)

PARTITION BY RANGE (store_id) (

PARTITION p0 VALUES LESS THAN (6),

PARTITION p1 VALUES LESS THAN (11),

PARTITION p2 VALUES LESS THAN (16),

PARTITION p3 VALUES LESS THAN MAXVALUE

);

CREATETABLEemployees(

idINTNOTNULL,

fnameVARCHAR(30),

lnameVARCHAR(30),

hiredDATENOTNULLDEFAULT'1970-01-01',

separatedDATENOTNULLDEFAULT'9999-12-31',

job_codeINTNOTNULL,

store_idINTNOTNULL

)

PARTITIONBYRANGE(store_id)(

PARTITIONp0VALUESLESSTHAN(6),

PARTITIONp1VALUESLESSTHAN(11),

PARTITIONp2VALUESLESSTHAN(16),

PARTITIONp3VALUESLESSTHANMAXVALUE

);

MySQL官方文档有一个很有意思的例子，按照工号(job_code)位数来分区。

2位工号代表普通工人；3位工号代表办公室人员和供应人员；4位工号代表管理人员。

这样工人的数据存放在p0分区，办公室人员和供应人员的数据存放在p1分区，而管理人员的数据存放在p2分区。

由于工号的位数是固定的，所以无需考虑创建MAXVALUE分区。

CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT NOT NULL,

store_id INT NOT NULL

)

PARTITION BY RANGE (job_code) (

PARTITION p0 VALUES LESS THAN (100),

PARTITION p1 VALUES LESS THAN (1000),

PARTITION p2 VALUES LESS THAN (10000)

);

CREATETABLEemployees(

idINTNOTNULL,

fnameVARCHAR(30),

lnameVARCHAR(30),

hiredDATENOTNULLDEFAULT'1970-01-01',

separatedDATENOTNULLDEFAULT'9999-12-31',

job_codeINTNOTNULL,

store_idINTNOTNULL

)

PARTITIONBYRANGE(job_code)(

PARTITIONp0VALUESLESSTHAN(100),

PARTITIONp1VALUESLESSTHAN(1000),

PARTITIONp2VALUESLESSTHAN(10000)

);

2、date类型的列做RANGE分区

例子中，separated列是DATE类型。

CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT,

store_id INT

)

PARTITION BY RANGE ( YEAR(separated) ) (

PARTITION p0 VALUES LESS THAN (1991),

PARTITION p1 VALUES LESS THAN (1996),

PARTITION p2 VALUES LESS THAN (2001),

PARTITION p3 VALUES LESS THAN MAXVALUE

);

CREATETABLEemployees(

idINTNOTNULL,

fnameVARCHAR(30),

lnameVARCHAR(30),

hiredDATENOTNULLDEFAULT'1970-01-01',

separatedDATENOTNULLDEFAULT'9999-12-31',

job_codeINT,

store_idINT

)

PARTITIONBYRANGE(YEAR(separated))(

PARTITIONp0VALUESLESSTHAN(1991),

PARTITIONp1VALUESLESSTHAN(1996),

PARTITIONp2VALUESLESSTHAN(2001),

PARTITIONp3VALUESLESSTHANMAXVALUE

);

3、TIMESTAMP类型的列做RANGE分区

report_updated列为TIMESTAMP时间戳类型。如果我们直接使用时间戳分区，还要把时间计算成时间戳，然后分区，麻烦而且容易出错。

而UNIX_TIMESTAMP()函数可以把TIMESTAMP类型转换成时间，就容易很多，不容易出错。

CREATE TABLE quarterly_report_status (

report_id INT NOT NULL,

report_status VARCHAR(20) NOT NULL,

report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

)

PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (

PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),

PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),

PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),

PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),

PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),

PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),

PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),

PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),

PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),

PARTITION p9 VALUES LESS THAN (MAXVALUE)

);

CREATETABLEquarterly_report_status(

report_idINTNOTNULL,

report_statusVARCHAR(20)NOTNULL,

report_updatedTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP

)

PARTITIONBYRANGE(UNIX_TIMESTAMP(report_updated))(

PARTITIONp0VALUESLESSTHAN(UNIX_TIMESTAMP('2008-01-01 00:00:00')),

PARTITIONp1VALUESLESSTHAN(UNIX_TIMESTAMP('2008-04-01 00:00:00')),

PARTITIONp2VALUESLESSTHAN(UNIX_TIMESTAMP('2008-07-01 00:00:00')),

PARTITIONp3VALUESLESSTHAN(UNIX_TIMESTAMP('2008-10-01 00:00:00')),

PARTITIONp4VALUESLESSTHAN(UNIX_TIMESTAMP('2009-01-01 00:00:00')),

PARTITIONp5VALUESLESSTHAN(UNIX_TIMESTAMP('2009-04-01 00:00:00')),

PARTITIONp6VALUESLESSTHAN(UNIX_TIMESTAMP('2009-07-01 00:00:00')),

PARTITIONp7VALUESLESSTHAN(UNIX_TIMESTAMP('2009-10-01 00:00:00')),

PARTITIONp8VALUESLESSTHAN(UNIX_TIMESTAMP('2010-01-01 00:00:00')),

PARTITIONp9VALUESLESSTHAN(MAXVALUE)

);

4、RANGE分区适用场景

●可以按照分区删除老数据，ALTER TABLE employees DROP PARTITION p0。比delete高效的多，而且影响小。

●表中有日期或者时间类型的列

●经常基于分区列查询，MySQL能够快速根据where条件扫描数据所在的分区。EXPLAIN PARTITIONS SELECT COUNT(*) FROM employees WHERE separated BETWEEN ‘2000-01-01’ AND ‘2000-12-31’ GROUP BY store_id;