MySQL分区学习

https://dev.MySQL.com/doc/refman/5.7/en/alter-table-partition-operations.html

公司主营业务：成都网站建设、成都网站制作、移动网站开发等业务。帮助企业客户真正实现互联网宣传，提高企业的竞争能力。创新互联是一支青春激扬、勤奋敬业、活力青春激扬、勤奋敬业、活力澎湃、和谐高效的团队。公司秉承以“开放、自由、严谨、自律”为核心的企业文化，感谢他们对我们的高要求，感谢他们从不同领域给我们带来的挑战，让我们激情的团队有机会用头脑与智慧不断的给客户带来惊喜。创新互联推出墨江免费做网站回馈大家。

一、分区概述

分区是一种表设计模式，自5.1版本开始支持分区，逻辑上是一张表，物理上可能是多个对象。其中MyISAM、INNODB、NDB等存储引擎都支持分区，但CSV、MERGE、FEDORATED不支持分区。

1. 分区种类

水平分区：对表记录进行拆分，同一表的不同行记录分配到不同的物理文件中。

Range分区：行根据基于属于一个给定连续区间的列值放入分区，自MYSQL5.5开始支持列范围分区，最常用的分区。

List分区：和Range分区一样，只是List分区是面对离散值，自MYSQL5.5开支支持列List分区。

Hash分区：根据用户自定义的表达式的返回值进行分区，返回值不能为负数。

Key分区：根据MYSQL数据库提供的散列函数来进行分区。

垂直分区：对表字段进程拆分（MYSQL暂不支持），同一表中不同的列分配在不同的物理文件中。

2. 分区优、缺点

优点：

可极大提高查询效率；

主要用于数据库的高可用性，方便管理；

缺点：

无论何种分区，如果表中存在主键或者唯一键索引，分区列必须是唯一索引的一个组成部分。

二、分区类型详解

1. Range分区

create table t_range(id int)

partition by range(id)

(partition p0 values less than (100),

partition p1 values less than(500),

partition p2 values less than maxvalue );

insert into t_range values(10),(120),(600);

(root:localhost:Sat Jul 8 20:05:12 2017)[dbtest]> \! ls -lnrth /home/mysql/dbtest

total 320K

-rw-rw---- 1 500 500 61 Mar 17 15:58 db.opt

-rw-rw---- 1 500 500 32 Jul 8 20:04 t_range.par ##存储分区信息

-rw-rw---- 1 500 500 8.4K Jul 8 20:04 t_range.frm

-rw-rw---- 1 500 500 96K Jul 8 20:05 t_range#P#p2.ibd

-rw-rw---- 1 500 500 96K Jul 8 20:05 t_range#P#p1.ibd

-rw-rw---- 1 500 500 96K Jul 8 20:05 t_range#P#p0.ibd

(root:localhost:Sat Jul 8 20:14:13 2017)[(none)]> select * from information_schema.partitions where table_name='t_range' \G

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

TABLE_CATALOG: def

TABLE_SCHEMA: dbtest

TABLE_NAME: t_range

PARTITION_NAME: p0

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 1

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 100

TABLE_ROWS: 1

AVG_ROW_LENGTH: 16384

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

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

TABLE_CATALOG: def

TABLE_SCHEMA: dbtest

TABLE_NAME: t_range

PARTITION_NAME: p1

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 2

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 500

TABLE_ROWS: 1

AVG_ROW_LENGTH: 16384

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

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

TABLE_CATALOG: def

TABLE_SCHEMA: dbtest

TABLE_NAME: t_range

PARTITION_NAME: p2

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 3

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: MAXVALUE

TABLE_ROWS: 1

AVG_ROW_LENGTH: 16384

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

3 rows in set (0.00 sec)

2. List分区

-rw-rw---- 1 500 500 28 Jul 8 21:39 t_list.par

-rw-rw---- 1 500 500 8.4K Jul 8 21:39 t_list.frm

-rw-rw---- 1 500 500 96K Jul 8 21:39 t_list#P#p1.ibd

-rw-rw---- 1 500 500 96K Jul 8 21:39 t_list#P#p0.ibd

CREATE TABLE `t_list` (

`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY LIST (id)

(PARTITION p0 VALUES IN (1,3,5,7,9) ENGINE = InnoDB,

PARTITION p1 VALUES IN (2,4,6,8,10) ENGINE = InnoDB) */;

3. Hash分区

HASH分区将数据均匀的分布到预先定义的各个分区中，保障各个分区的数据数量大致一样的。在range和list分区定义时，必须明确指定分区的列值或列值集合保存在哪个分区中，而hash分区自动完成列值的分配，平均的将数据放在不同的分区。

CREATE TABLE `t_hash` (

`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY HASH (id)

PARTITIONS 4 */

-rw-rw---- 1 500 500 32 Jul 8 21:53 t_hash.par

-rw-rw---- 1 500 500 8.4K Jul 8 21:53 t_hash.frm

-rw-rw---- 1 500 500 96K Jul 8 21:54 t_hash#P#p3.ibd

-rw-rw---- 1 500 500 96K Jul 8 21:54 t_hash#P#p2.ibd

-rw-rw---- 1 500 500 96K Jul 8 21:54 t_hash#P#p1.ibd

-rw-rw---- 1 500 500 96K Jul 8 21:54 t_hash#P#p0.ibd

4. Key分区

Hash分区根据用户自定义的函数进行分区，key使用MYSQL数据库提供的函数进行分区。

CREATE TABLE `t_key` (

`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY KEY (id)

PARTITIONS 4 */;

5. Columns分区

Range、List、Hash和Key分区都是针对整型分区，如果不是整型分区，则需要通过相关函数转换。但通过columns分区，不需要转换。

Columns支持int/small int/tinyint/bigint/date/datetime/char/varchar/binary支持，对float/decimal/blob/text不支持

create table t_range_columns(dtime datetime)

partition by range columns (dtime)

(partition p0 values less than ('2016-01-01'),

partition p1 values less than('2017-01-01'),

partition p2 values less than maxvalue );

6. 子分区

在分区的基础上再进行分区，也称之为复合分区。

三、分区维护管理

Alter table table_name

| ADD PARTITION (partition_definition)

| DROP PARTITION partition_names

| TRUNCATE PARTITION {partition_names | ALL }

| COALESCE PARTITION number

| REORGANIZE PARTITION partition_names INTO (partition_definitions)

| ANALYZE PARTITION {partition_names | ALL }

| CHECK PARTITION {partition_names | ALL }

| OPTIMIZE PARTITION {partition_names | ALL }

| REBUILD PARTITION {partition_names | ALL }

| REPAIR PARTITION {partition_names | ALL }

| REMOVE PARTITIONING

1. 增加分区

alter table t_range add partition ( partition p2 values less than maxvalue) ;

2. 删除分区

alter table t_range drop partition p2;

alter table t_list remove partitioning;

3. 查看分区

information_schema.partitions

4. 清除分区数据

alter table t_range truncate partition p2;

5. 解析分区

(root:localhost:Sat Jul 8 21:30:03 2017)[dbtest]> explain partitions select * from t_range \G

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

id: 1

select_type: SIMPLE

table: t_range

partitions: p0,p1,p2

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 3

Extra: NULL

1 row in set (0.00 sec)

(root:localhost:Sat Jul 8 21:30:18 2017)[dbtest]> explain partitions select * from t_range where id=800\G

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

id: 1

select_type: SIMPLE

table: t_range

partitions: p2

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 2

Extra: Using where

1 row in set (0.00 sec)

（只查询指定的分区）

6. 交换分区

MYSQL5.6支持了交换分区，具体语法如下：

alter table t_range exchange partition p0 with table t;

将分区表t_range的P0分区的数据交换到t表中，而t表的数据也会交换到t_range表中，交换是双向的。

文章题目：MySQL分区学习
网页路径：http://azwzsj.com/article/gcoscs.html

MySQL分区学习

一、 分区概述

1. 分区种类

2. 分区优、缺点

二、 分区类型详解

1. Range分区

2. List分区

3. Hash分区

4. Key分区

5. Columns分区

6. 子分区

三、 分区维护管理

1. 增加分区

2. 删除分区

3. 查看分区

4. 清除分区数据

5. 解析分区

6. 交换分区

其他资讯

一、分区概述

二、分区类型详解

三、分区维护管理