Mysql分区表 介绍和使用

SOASPX 5457 0

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、分区表中无法使用外键

 

标签: mysql

发表评论 (已有0条评论)

还木有评论哦,快来抢沙发吧~