≡
  • 网络编程
  • 数据库
  • CMS技巧
  • 软件编程
  • PHP笔记
  • JavaScript
  • MySQL
位置：首页 > 数据库 > MySQL

MySQL数据表分区技术PARTITION浅析

人气：393 时间：2020-07-09

这篇文章主要为大家详细介绍了MySQL数据表分区技术PARTITION浅析，具有一定的参考价值，可以用来参考一下。

感兴趣的小伙伴，下面一起跟随四海网的小编小韵来看看吧！

在这一章节里， 我们来了解下 Mysql 中的分区技术 (RANGE, LIST, HASH)
 
Mysql 的分区技术与水平分表有点类似， 但是它是在逻辑层进行的水平分表， 对于应用而言它还是一张表， 换句话说: 分区不是实际真正的对一张表进行拆分，分区之后表还是一个表，它是把存储文件进行拆分。

在 Mysql 5.1(后) 有了几种分区类型:
 
RANGE分区: 基于属于一个给定连续区间的列值， 把多行分配给分区

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

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

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

1、 做分区时，要么不定义主键，要么把分区字段加入到主键中
2、 分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量 NOT NULL
 
首先你可以查看下你的 Mysql 版本是否支持 PARTITION

代码如下:



mysql> show plugins;

 

| partition    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |

或者:

代码如下:

 

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 作为一个 key

代码如下:




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

 

本文来自：http://www.q1010.com/177/9391-0.html

注：关于MySQL数据表分区技术PARTITION浅析的内容就先介绍到这里，更多相关文章的可以留意四海网的其他信息。

关键词：MYSQL

您可能感兴趣的文章

  • MySQL事务处理与应用简析
  • windows7下启动MySQL服务出现服务名无效的原因及解决方法
  • MySQL错误TIMESTAMP column with CURRENT_TIMESTAMP的解决方法
  • MySQL中字段名和保留字冲突的解决办法
  • MySQL清除log-bin日志的方法
  • MySQL错误Cannot find or open table x/x from the internal问题解决方法
  • MySQL主从复制(master-slave)实际操作案例
  • MySQL查询今天、昨天、近7天、近30天、本月、上一月的SQL语句
  • MySQL数据库防止人为误操作的实例讲解
  • MySQL数据库表修复 MyISAM
上一篇：MySQL5.6基本优化配置
下一篇：MySQL事务处理与应用简析
热门文章
  • mysql 修改character_set_server为utf-8的简单示例
  • 解决MySQL丢失文件localhost.localdomain.pid、mysql.sock的示例
  • MySQL 数据类型binary和varbinary的简单示例
  • MySQL:reading initial communication packet问题解决方法
  • MySql 表类型MYISAM、InnoDB区别
  • bash: mysql: command not found 的解决方法
  • MYSQL默认用户名ROOT修改方法
  • MySQL 常用命令菜鸟教程
  • MySQL 使用命令行新建用户并授予权限
  • MySql 数据库物理文件存放位置查看示例
  • 最新文章
    • MySQL查看死锁与解除死锁的简单示例
    • MySQL 慢查询的功能实例
    • MySQL查看死锁与去除死锁的简单示例
    • MySQL找出未提交事务的SQL的简单示例
    • MySQL锁阻塞的的简单示例
    • MySQL中的binary类型使用操作的示例
    • SQL优化教程之in与range查询的简单示例
    • MySQL 的 21 个规范、优化最佳实践！
    • MySQL 字符类型大小写敏感的简单示例
    • 解决mybatis-plus分页传入参数后sql where条件没有limit分页信息的问题

四海网收集整理一些常用的php代码,JS代码,数据库mysql等技术文章。