导航：首页 > 数据库 >

MySQL分区学习

发表于：2024-09-22 作者：千家信息网编辑

千家信息网最后更新 2024年09月22日，https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html一、分区概述分区是一种表设计模式，自5.1版

千家信息网最后更新 2024年09月22日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表中，交换是双向的。

很赞哦！

支持数据不同函数数据库物理分配文件用户索引缺点存储查询管理优点信息区间双向只是可用性数据库的安全要保护哪些东西数据库安全各自的含义是什么生产安全数据库录入数据库的安全性及管理数据库安全策略包含哪些海淀数据库安全审计系统建立农村房屋安全信息数据库易用的数据库客户端支持安全管理连接数据库失败ssl安全错误数据库的锁怎样保障安全宜兴智能软件开发项目信息广州华多网络技术公司联通网络安全分析路由器dns服务器地址怎么填写数据库系统概论的答案企业网络安全致胜宝典软件开发的特殊过程服务器需要做双网卡设置吗网络安全期末考试题目培英小学网络安全小课堂速达5000在哪打开数据库如何在现实生活中维护网络安全 idea复制创建数据库表增加大学生网络安全意识网络安全发手则数据库文件查看器手机电脑网络安全数据库技术资源小程序注册后需要购买服务器吗非遗物质文化数据库应用场景 c数据库连接sql 服务器本地机ip怎么查 java查看数据库连接的数计算机网络技术期中考试题湖南物联网时钟同步服务器网络技术股吧电子表格中重复的数据库网络安全知识题及答案入侵服务器视频教程 rac和ha数据库的区别

千家信息网

千家信息网

MySQL分区学习

一、分区概述

1. 分区种类

2. 分区优、缺点

二、分区类型详解

1. Range分区

2. List分区

3. Hash分区

4. Key分区

5. Columns分区

6. 子分区

三、分区维护管理

1. 增加分区

2. 删除分区

3. 查看分区

4. 清除分区数据

5. 解析分区

6. 交换分区

关系型数据库之MySQL基于SSL主从复制及SSL远程访问

MySQL和PostgreSQL在多表连接算法上的差异有哪些

相关文章

MySQL分区学习

一、 分区概述

1. 分区种类

2. 分区优、缺点

二、 分区类型详解

1. Range分区

2. List分区

3. Hash分区

4. Key分区

5. Columns分区

6. 子分区

三、 分区维护管理

1. 增加分区

2. 删除分区

3. 查看分区

4. 清除分区数据

5. 解析分区

6. 交换分区

关系型数据库之MySQL基于SSL主从复制及SSL远程访问

MySQL和PostgreSQL在多表连接算法上的差异有哪些

相关文章

一、分区概述

二、分区类型详解

三、分区维护管理