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://myzitong.com/article/gcoscs.html