专栏首页吴伟祥Mysql分区表 介绍和使用(转)

Mysql分区表 介绍和使用(转)

What?(分区表是什么)

分区表可以用一张表存储大量数据,达到和物理分表同样的效果,但操作起来更简单,对于使用者来说和普通表无差别

How?(怎么使用它)

Mysql在创建表时使用PARTITION BY字句定义每个分区,例子如下:

CREATE TABLE goods (
    create_date DATETIME NOT NULL,
    ........
) ENGINE=InnoDB PARTITION BY RANGE(YEAR(create_date))(
    PARTITION p_2014 VALUES LESS THAN (2015) ENGINE=InnoDB,
    PARTITION p_2015 VALUES LESS THAN (2016) ENGINE=InnoDB,
    PARTITION p_2016 VALUES LESS THAN (2017) ENGINE=InnoDB,
    PARTITION p_others values LESS THAN MAXVALUE ENGINE=InnoDB);  

上面的建表语句中,我们创建了一个商品表goods,其中定义了创建时间(create_date)字段,
我们使用范围分区方式建立分区表,然后我们使用该字段的年份作为分区条件,
分别将时间在2015年之前的数据存放在了p_2014分区,
将时间在2016年之前(也就是2015年整年的数据)存放在了p_2015分区。
将时间在2017年之前(也就是2016年整年的数据)存放在了p_2016分区。
然后将2017年以及之后的数据都放在了最后一个分区p_others。

更多使用方式

分区表不仅可以根据字段范围分区,也支持通过键值、哈希和列表分区,不过我们最常用的就是根据范围进行分区。  可以使用数学模函数进行分区,也可以根据时间范围进行分区,  甚至我们可以自行定义一个分区列,将想要落在相同分区的数据的该列都设为相同值。

分区表的操作逻辑

SELECT:
    读锁不会影响同时发生的其他读操作,不必担心。
INSERT:
    分区层先打开并锁住所有分区表,确定由哪个分区接收这条记录,再释放全表锁并锁住对应分区,将记录写入对应底层表
DELETE:
    类似于INSERT
UPDATE:
    分区层先打开并锁住所有底层表,然后确定要更新的数据在哪个分区,取出该数据并更新,再判断更新后的数据应该
    存储到哪个分区,最后对新分区进行写入操作,然后对老分区做删除操作。

虽然每个操作都会“打开并锁住所有分区表”,但这并不表示分区表在处理过程中是锁住全表的, 分区表的锁机制取决于我们所选择的存储引擎,如果我们使用InnoDB构建分区表, 那么会在分区层(通过分区条件定位到分区后)释放表锁,之后的锁机制会按照InnoDB方式进行。

分区的优势

1、使得一张表能存储更多的数据

2、让开发者更加专心于业务逻辑,而不是繁琐的sql条件匹配

3、让你在使用ORM框架时,更加的简单方便,无需修改ORM框架,和操作普通表完全相同

4、对于表的维护更加方便,当你需要修改字段或者调整索引时,无需同时操作300张表

5、当某些数据不再有价值时,可以直接清空一个分区,降低删除的代价(普通的删除需要根据where条件匹配后再回表删除),例如删除2015年之前的记录,可以直接清空p_2014分区,因为每个分区在底层是单独的子表,所以无需根据时间字段筛选

分区表的陷阱

分区设计上的陷阱:
上面例子中,按照时间分区的方式,会带来一个问题: 
随着时间的增长,我们也需要新增分区,否则所有的数据都会落到最后的分区中,成为一个大分区, 
当然,新增一个分区的代价是非常小的,完全不用太担心,但如果你已经懒到不想改表了,可以使用 
自增id取模进行hash来避免这个问题,例如建立100个分区,然后以id取模100的方式作为分区条件。 
这样做的好处是增长的数据都可以完全的均分到所有分区,不会造成大分区的存在, 
但坏处是每个分区的数据量都会一直增长,并且在进行范围操作时会锁住大量分区。

分区列的必须作为查询条件:

因为需要根据分区列来确定数据所在分区,所以分区列必须作为查询条件, 
如果不使用分区列的查询条件,那么就无法进行分区过滤,Mysql最终会扫描所有分区,这就和我们的初衷相违背了。

其他限制:

1、所有分区都必须使用相同的存储引擎

2、某些存储引擎不支持分区(MERGE、CSV、FEDERATED)

3、一张表最多只能有1024个分区

4、分区表中无法对非分区列建立唯一索引(Unique Index)

5、分区表中无法使用外键

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Linux Partition scheme 分区方案(一)

    根分区包含Linux系统所有的目录。如果在安装系统时只分配了/分区,那么上面的/boot、/usr和/var将都包含在根分区中,也就是这些分区将占用根分区的空间...

    wuweixiang
  • MySql数据库分表分区实践(转)

    海量设备通过物联网服务接入云端,设备每30s上报一次自身数据(以下称为动态数据)。 物联网服务将设备上报的数据转发给数据处理网关,由数据入库网关执行批量入库操作...

    wuweixiang
  • Linux 格式化和挂载数据盘 转

    本文描述如何用一个新的数据盘创建一个单分区数据盘并挂载文件系统。本文仅适用于使用 fdisk 命令对一个不大于 2 TB 的数据盘执行分区操作。如果需要分区的数...

    wuweixiang
  • Linux-fdisk磁盘分区命令(16)

    名称: fdisk 使用: fdisk [块设备磁盘] 说明: 将一个块设备(磁盘)分成若干个块设备(磁盘),并将分区的信息写进分区表。  fdisk命令菜单常...

    张诺谦
  • 一文带你搞懂 MySQL 中的分区!

    首先要先介绍一下InnoDB逻辑存储结构和区的概念,它的所有数据都被逻辑地存放在表空间,表空间又由段,区,页组成。

    良月柒
  • 数据分区------《Designing Data-Intensive Applications》读书笔记9

    分区与副本是很容易混淆的概念,我们这里离清一下两者。 数据分区的每个副本可以存储在多个节点上。这意味着,即使每个记录恰好属于一个分区,它仍然可以存储在几个不同...

    HappenLee
  • Linux Partition scheme 分区方案(一)

    根分区包含Linux系统所有的目录。如果在安装系统时只分配了/分区,那么上面的/boot、/usr和/var将都包含在根分区中,也就是这些分区将占用根分区的空间...

    wuweixiang
  • linux学习第十四篇:查看磁盘,文件大小命令:df,du;磁盘分区

    df命令 df:查看已挂载磁盘的总容量、使用容量、剩余容量等,可以不加任何参数,默认是按k为单位显示的。 ? 带有tmpfs的都是临时的文件系统,所以在对应的挂...

    用户1215343
  • MySQL支持哪几类分区表?

    Q 题目 MySQL支持哪几类分区表? A 答案 表分区是指根据一定规则,将数据库中的一张表分解成多个更小的,容易管理的部分。从逻辑上看,只有一张表,但是底层却...

    企鹅号小编
  • oracle表空间表分区详解及oracle表分区查询使用方法(转+整理)

    此文从以下几个方面来整理关于分区表的概念及操作: 1.表空间及分区表的概念 2.表分区的具体作用 3.表分区的优缺点 4.表分区的几种类型及操作方法 5...

    用户1221057

扫码关注云+社区

领取腾讯云代金券