mysql数据库中的分区技术_MySQL之-数据表分区技术PARTITION的代码示例浅析

最新推荐文章于 2023-02-18 23:58:02 发布

为禽兽而匿

最新推荐文章于 2023-02-18 23:58:02 发布

阅读量158

点赞数

文章标签： mysql数据库中的分区技术

本文链接：https://blog.csdn.net/weixin_30869777/article/details/114862849

版权

这篇文章主要介绍了Mysql数据表分区技术PARTITION浅析,分别介绍了 Mysql 中的分区技术 RANGE、LIST、 HASH,需要的朋友可以参考下。

在这一章节里，我们来了解下 Mysql 中的分区技术 (RANGE, LIST, HASH)

Mysql 的分区技术与水平分表有点类似，但是它是在逻辑层进行的水平分表，对于应用而言它还是一张表，换句话说: 分区不是实际真正的对一张表进行拆分，分区之后表还是一个表，它是把存储文件进行拆分。

在 Mysql 5.1(后) 有了几种分区类型:

RANGE分区: 基于属于一个给定连续区间的列值，把多行分配给分区

LIST分区: 类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择

HASH分区: 基于用户定义的表达式的返回值来进行选择分区，该表达式使用将要插入到表中的这些行的列值进行计算，这个函数可以包含 Mysql 中有效的、产生非负整数值的任何表达式

KEY分区: 累世于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 Mysql 服务器提供其自身的哈希函数

分区应该注意的事项：

1、做分区时，要么不定义主键，要么把分区字段加入到主键中

2、分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量 NOT NULL

首先你可以查看下你的 Mysql 版本是否支持 PARTITIONmysql> show plugins;

或者:mysql> show variables like "%part%";

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

| Variable_name | Value |

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

| have_partitioning | YES |

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

RANGE 分区

假定你创建了一个如下的表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。如果你想将其分成4个小分区，那么你可以采用RANGE分区，创建的数据库表如下: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

-> ) ENGINE=Myisam DEFAULT CHARSET=utf8

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

-> );

如果你想把不同时期离职的员工进行分别存储，那么你可以将日期字段 separated (即离职时间) 作为一个 key, 创建的 SQL 语句如下: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

-> ) ENGINE=Myisam DEFAULT CHARSET=utf8

-> PARTITION BY RANGE (YEAR(separated)) (

-> PARTITION P0 VALUES LESS THAN (2001),

-> PARTITION P1 VALUES LESS THAN (2011),

-> PARTITION P2 VALUES LESS THAN (2021),

-> PARTITION P3 VALUES LESS THAN MAXVALUE

-> );

List 分区

同样的例子，如果这20家影像店分布在4个有经销权的地区，+------------------+--------------------------------------+

| 地区 | 音像店 ID 号 |

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

| 北区 | 3, 5, 6, 9, 17 |

| 东区 | 1, 2, 10, 11, 19, 20 |

| 西区 | 4, 12, 13, 14, 18 |

| 中心区 | 7, 8, 15, 16 |

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

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

-> ) ENGINE=Myisam DEFAULT CHARSET=utf8

-> PARTITION BY LIST (store_id) (

-> PARTITION pNorth VALUES IN (3, 5, 6, 9, 17),

-> PARTITION pEast VALUES IN (1, 2, 10, 11, 19, 20),

-> PARTITION pWest VALUES IN (4, 12, 13, 14, 18),

-> PARTITION pCentral VALUES IN (7, 8, 15, 16)

-> );

当你创建完之后，你可以进入 Mysql 数据储存文件，该文件夹位置定义在 Mysql 配置文件中shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;

[mysqld]

datadir = /var/lib/mysql

shawn@Shawn:~$ cd /var/lib/mysql/dbName

shawn@Shawn:/var/lib/mysql/dbName$ ll

显示如下:

8768 Jun 7 22:01 employees.frm

48 Jun 7 22:01 employees.par

0 Jun 7 22:01 employees#P#pCentral.MYD

1024 Jun 7 22:01 employees#P#pCentral.MYI

0 Jun 7 22:01 employees#P#pEast.MYD

1024 Jun 7 22:01 employees#P#pEast.MYI

0 Jun 7 22:01 employees#P#pNorth.MYD

1024 Jun 7 22:01 employees#P#pNorth.MYI

0 Jun 7 22:01 employees#P#pWest.MYD

1024 Jun 7 22:01 employees#P#pWest.MYI

从这里可以看出，它是把存储文件根据我们的定义进行了拆分employees.frm = 表结构

employees.par = partition, 申明是一个分区表

.MYD = 数据文件

.MYI = 索引文件

HASH 分区

HASH 分区主要用来确保数据在预先确定数目的分区中平均分布

如果你想把不同时期加入的员工进行分别存储，那么你可以将日期字段 hired 作为一个 keymysql-> 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

-> ) ENGINE=Myisam DEFAULT CHARSET=utf8

-> PARTITION BY HASH (YEAR(hired)) (

-> PARTITIONS 4

-> );

#这里注意的是 PARTITIONS，多了一个 s

这里要提一下的就是，如上的例子都是使用的是 Myisam 存储引擎，它默认使用独立表空间，所以你可以在上面的磁盘空间里看到不同的分区

而 InnoDB 引擎则默认使用共享表空间, 此时就算你对 InnoDB 表进行分区，你查看下会发现，它并没有像 Myisam 那么样进行物理上的分区，所以你需要修改下 Mysql 配置文件:shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;

#添加:

innodb_file_per_table=1

#重启 mysql

shawn@Shawn:~$ sudo /etc/init.d/mysql restart

此时你再对 InooDB 进行分区，则会有如下效果:8768 Jun 7 22:54 employees.frm

48 Jun 7 22:54 employees.par

98304 Jun 7 22:54 employees#P#pCentral.ibd

98304 Jun 7 22:54 employees#P#pEast.ibd

98304 Jun 7 22:54 employees#P#pNorth.ibd

98304 Jun 7 22:54 employees#P#pWest.ibd

分区管理

删除分区mysql> alter table employees drop partition pWest;

新增分区#range添加新分区

mysql> alter table employees add partition ( partition p4 values less than (26) );

#list添加新分区

mysql> alter table employees add partition( partition pSouth values in (21, 22, 23) );

#hash重新分区

mysql> alter table employees add partition partitions 5;

为禽兽而匿

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库中的分区技术_MySQL之-数据表分区技术PARTITION的代码示例浅析

这篇文章主要介绍了Mysql数据表分区技术PARTITION浅析,分别介绍了 Mysql 中的分区技术 RANGE、LIST、 HASH,需要的朋友可以参考下。在这一章节里，我们来了解下 Mysql 中的分区技术 (RANGE, LIST, HASH)Mysql 的分区技术与水平分表有点类似，但是它是在逻辑层进行的水平分表，对于应用而言它还是一张表，换句话说: 分区不是实际真正的对一张表进...
复制链接

扫一扫