首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql分区表 介绍和使用(转)

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

作者头像
wuweixiang
发布2019-03-12 14:16:33
1.5K0
发布2019-03-12 14:16:33
举报
文章被收录于专栏:吴伟祥吴伟祥

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、分区表中无法使用外键
本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • What?(分区表是什么)
  • How?(怎么使用它)
  • 更多使用方式
  • 分区表的操作逻辑
  • 分区的优势
  • 分区表的陷阱
  • 分区列的必须作为查询条件:
  • 其他限制:
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档