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;

(0)

相关推荐

  • MySQL中表分区技术详细解析

    MySQL 分区技术(是mysql 5.1以版本后开始用->是甲骨文mysql技术团队维护人员以插件形式插入到mysql里面的技术) 1.概述 数据库单表到达一定量后,性能会有衰减,像mysql\sql server等犹为明显,所以需要把这些数据进行分区处理.同时有时候可能出现数据剥离什么的,分区表就更有用处了! MySQL 5.1 中新增的分区(Partition)功能就开始增加,优势也越来越明显了: 与单个磁盘或文件系统分区相比,可以存储更多的数据 很容易就能删除不用或者过时的数据 一些查询

  • 创建mysql表分区的方法

    表分区是最近才知道的哦 ,以前自己做都是分表来实现上亿级别的数据了,下面我来给大家介绍一下mysql表分区创建与使用吧,希望对各位同学会有所帮助.表分区的测试使用,主要内容来自于其他博客文章以及mysql5.1的参考手册mysql测试版本:mysql5.5.28mysql物理存储文件(有mysql配置的datadir决定存储路径)格式简介数据库engine为MYISAM frm表结构文件,myd表数据文件,myi表索引文件.INNODB engine对应的表物理存储文件innodb的数据库的物理

  • MySQL的表分区详解

    一.什么是表分区通俗地讲表分区是将一大表,根据条件分割成若干个小表.mysql5.1开始支持数据表分区了.如:某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地将表分区.当然也可根据其他的条件分区. 二.为什么要对表进行分区为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率.分区的一些优点包括:      1).与单个磁盘或文件系统分区相比,可以存储更多的数据.      2).对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的

  • MySQL数据库表分区注意事项大全【推荐】

    表分区与数据库分区是不一样的那么碰到表分区使用时我们要注意一些什么事情呢,今天我们来看一篇关于MySQL数据库表分区注意事项的细节. 1.分区列索引约束 若表有primary key或unique key,则分区表的分区列必须包含在primary key或unique key列表里,这是为了确保主键的效率,否则同一主键区的东西一个在A分区,一个在B分区,显然会比较麻烦. 2.各分区类型条件 range 每个分区包含那些分区表达式的值位于一个给定的连续区间内的行.这些区间要连续且不能相互重叠 li

  • Mysql数据表分区技术PARTITION浅析

    在这一章节里, 我们来了解下 Mysql 中的分区技术 (RANGE, LIST, HASH)   Mysql 的分区技术与水平分表有点类似, 但是它是在逻辑层进行的水平分表, 对于应用而言它还是一张表, 换句话说: 分区不是实际真正的对一张表进行拆分,分区之后表还是一个表,它是把存储文件进行拆分. 在 Mysql 5.1(后) 有了几种分区类型:   RANGE分区: 基于属于一个给定连续区间的列值, 把多行分配给分区 LIST分区: 类似于按 RANGE 分区, 区别在于 LIST 分区是基

  • MySQL数据表分区策略及优缺点分析

    为什么需要分区? 当面对巨大的数据表的时候,至少有一件事情是确定的,表太大了以至于每次查询的时候我们没法做全表扫描.而这个时候也没法使用索引,或者说索引意义不大,更不用说索引的维护代价和空间占用非常高.如果是依赖索引,会导致大量的碎片和低聚集度的数据,这会导致查询的时候有上千次的随机 I/O 访问而导致宕机.这种情况下一般只会使用1-2个索引,而不会更多.这种情况下,有两个可行的选项:查询必须从数据表的指定的部分顺序查找或者是期望的部分数据及其索引与服务器的内存匹配. 需要再次重申:在存储空间过

  • MySQL高级特性——数据表分区的概念及机制详解

    MySQL 的分区的实现方式是对数据表进行一层包装,这意味着索引实际是基于每个分区定义的,而不是整张表.这个特性和 Oracle 是不同的,在 Oracle 中的索引和数据表可以使用更灵活和更复杂的方式进行分区.​ MySQL 的分区通过定义 PATITION BY 子句的条件来决定数据行所属分区的归属.在执行查询的时候,查询优化器会区分所在分区,这意味着查询不会检查全部分区,而仅仅是那些包含索要查询数据所在的分区.​ 分区的主要目的是对数据表进行大致形式的索引和聚集.这样可以减少数据表的过大范

  • Oracle数据表分区的策略

    正在看的ORACLE教程是:Oracle数据表分区的策略.本文描述通过统计分析出医院信息系统需分区的表,对需分区的表选择分区键,即找出包括在你的分区键中的列(表的属性),对大型数据的管理比较有意义, 本文的工作在Oracle8.1.6下实现. Oracle虽然是一个大型的DBMS,但如果不对记录比较多的表进行处理,仍然发挥不了Oracle管理大型数据的强大功能,因此对某些表进行分区,具有如下优点: 分区表中每个分区可以在逻辑上认为是一个独立的对象: 可以在一个表中的一个或多个分区上进行如删除.移

  • MySQL数据表使用的SQL语句整理

    目录 EXPLAIN 语句 SHOW INDEX 语句 ANALYZE TABLE 语句 EXPLAIN 语句 分析SQL索引使用,关键词EXPLAIN: SQL举例: CREATE TABLE `my_user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL DEFAULT '' COMMENT '名字', `sex` enum('0','1') NOT NULL COMMENT '性别

  • Oracle 数据表分区的策略

    正在看的ORACLE教程是:Oracle 数据表分区的策略.本文描述通过统计分析出医院信息系统需分区的表,对需分区的表选择分区键,即找出包括在你的分区键中的列(表的属性),对大型数据的管理比较有意义, 本文的工作在Oracle8.1.6下实现. Oracle虽然是一个大型的DBMS,但如果不对记录比较多的表进行处理,仍然发挥不了Oracle管理大型数据的强大功能,因此对某些表进行分区,具有如下优点: 分区表中每个分区可以在逻辑上认为是一个独立的对象: 可以在一个表中的一个或多个分区上进行如删除.

  • MySQL数据表损坏的正确修复方案

    于断电或非正常关机而导致MySQL(和PHP搭配之最佳组合)数据库出现错误是非常常见的问题.有两种方法,一种方法使用MySQL(和PHP搭配之最佳组合)的check table和repair table 的sql语句,另一种方法是使用MySQL(和PHP搭配之最佳组合)提供的多个myisamchk, isamchk数据检测恢复工具.前者使用起来比较简便.推荐使用. 1. check table 和 repair table 登陆MySQL(和PHP搭配之最佳组合) 终端: MySQL(和PHP搭

  • MySQL数据表基本操作实例详解

    本文实例讲述了MySQL数据表基本操作.分享给大家供大家参考,具体如下: 数据表的基本操作 1.主键约束要求主键列的数据唯一,并且不允许为空.主键能够唯一地识别表中的一条记录,可以结合外键来定义不同数据表之间的关系,并且可以加快数据库查询的速度.主键和记录之间的关系如同身份证和人之间的关系. 2.字表的外键必须关联父表的主键,且关联字段的数据类型必须匹配.如果类型不一样.创建子表时,就会出现错误:ERROR 1005(HY000):can't create table 'databases.ta

随机推荐