Mysql第八天分区与分表

2022-01-25 08:59:30

分区表

主要提供如下的特性，或者适合如此场景：

数据量非常大，或者只有表中最后的部分有热点数据，其他均为历史数据
分区表数据更容易维护，可以对独立的分区删除等操作
分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
可以避免一些特殊瓶颈，比如InnoDB的单个索引的互斥访问
可以备份和恢复独立的分区

创建分区表

通常有这么几种分法，因为主键或者是唯一约束键必须有一部分包含在分区键中，所以一般要不无主键，要不就按照自增主键的id进行范围分区，要不就把分区字段和主键一起作为联合主键。
还有一些其他的限制，比如分区键的运算结果必须为整数

Range分区

CREATE TABLE biz_order(
id bigint(20) NOT NULL AUTO_INCREMENT,
created DATETIME NOT NULL COMMENT '创建时间',
PRIMARY KEY (id, created)) ENGINE=InnoDB PARTITION BY RANGE(YEAR(created))(
  PARTITION p_2010 VALUES LESS THAN (2015),
  PARTITION p_latest VALUES LESS THAN MAXVALUE);

这种分区，最新的那个区显然会有最多的热点数据。可以再使用Hash子分区来减少竞争
- 除了使用YEAR, TO_DAY等日期函数外，还可以使用其数学函数，比如取模，按7取模是周几等

List分区

是用IN来做列值匹配的集合。比如可以按照地区来分为东西南北几个区：

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

这种如果插入语句不在IN中，则会插入失败

Hash分区

PARTITIONS为分区的数量，即会根据分区键的值计算出一个hash值，然后以4为模进行存储，好处是，不用再重新建分区了。

PARTITION BY HASH(store_id)
PARTITIONS 4;

还有Key分区，用的太少，不说了

操作分区表

增加删除分区等语句看这里

分区表由多个底层表构成，底层表跟普通表没什么区别，其索引也是分别在各个表中的索引。分区表只是会在一个很粗的粒度上决定一下去哪个底层表继续查询。

SELECT 锁住底层表，优化器先判断可以过滤部分的分期，然后再调用存储引擎接口访问各个分区
INSERT DELETE同行
UPDATE 操作会需要设计更新后判断在哪个分区，如果插入到了新分区，那么则删除原分区中的数据。
使用WHERE语句最好能够明确用到分区的关键字，这样可以很好的命中分区
锁住底层表不一定是表锁，会用到存储引擎自己的行级锁

如何使用

使用分区表肯定是因为数据量非常大，这个时候索引已经不能很好的起作用了。
可以不使用索引，而用粗粒度的命中分区表，然后全表扫描。

或者是针对热点数据，单独使用一个区让这个区都能够放到缓存中，这样就会有一个热点的很小的分区，可以对其使用索引。

另外一些可能的问题：

NULL值，因为TO_DAY等方法NULL值为无效入参，会把值放在第一个分区，这个时候SELECT的时候可能会需要查找第一个和命中的分区这样两个分区，这样可能会有很多的性能损耗，解决办法是使第一个分区尽可能的小，第二个办法是直接使用RANGE COLUMNS()而不使用函数
分区列和索引列应该用同一个列，如果不是，会导致无法过滤的问题
寻找分区的成本可能会比较高
维护分区的成本，比如alter等语句改变分区个数，或者其他涉及数据迁移的操作

分区表的查询

要在WHERE后面带分区列，且不能是表达式
使用EXPLAIN PARTITIONS SELECT来判断是否进行了分区过滤

分表

分区表还是一张表，是一种逻辑上的实现，主要解决的是单表数据过大，索引效率低的问题，很适合大量历史数据，少量活跃数据的场景。把数据保存在不同的区域。

分表是真的有多张表，基于分表还可以做分库，可以提升并发性能，以及磁盘I/O的性能。

二者可以配合使用。

使用集群的方式

要配合复制使用，仅仅是把查询请求进行了分摊。
但是这样不会影响代码层。

使用业务逻辑划分

可一个根据用户id来分，每个用户一张表，这样需要每有新的用户都建表了。

还有常用的做法是预先设计好比如100张表，然后对数据的一个字段做hash，然后对100取模。

又或者根据时间来进行分割，这种的好处是，如果根据时间做统计的时候可以不用UNION

上面的分表方式都不能解决根据服务器压力进行选择的问你，并且也不能比较均匀的保存数据。

分表之后要考虑这样几个操作以后可能会带来的问题：

分页，主要看分页情况下排序的字段是什么，如果是时间，那么按照时间段分表是比较好的，如果会涉及到多个表的UNION，那么就会比较耗费性能。
插入，更新。主要是更新的时候的主键的问题，因为分表之后主键不唯一了，因此需要用分表列和自增列做联合主键。
分组，统计。这个跟分页考虑的情况差不多，也是主要涉及排序的问题。比如如果每次都是需要按照用户统计信息的话，那么按照用户分表的选择是没错的。
表的分发跟业务很比较大的关系。要尽量考虑比较多的因素和场景。
通用一些的解决办法是，对分页字段使用搜索引擎
或者对分页和排序字段单独列一张表不分，作为查询的索引。

使用merge存储引擎

基本表:
CREATE TABLE TEST_MERGE_1(
ID INT(5) NOT NULL,
VALUE VARCHAR(100) NOT NULL,
PRIMARY KEY(ID)
);
CREATE TABLE TEST_MERGE_2(
ID INT(5) NOT NULL,
VALUE VARCHAR(100) NOT NULL,
PRIMARY KEY(ID)
);
MERGE表：
CREATE TABLE TEST_MERGE(
ID INT(5) NOT NULL,
VALUE VARCHAR(100) NOT NULL,
PRIMARY KEY(ID)
) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(TEST_MERGE_1,TEST_MERGE_2);

基本表必须是MYISAM类型的。
基本表的数据结构必须一致。
order by等语句，我想的是因为Merge表里有基本表共同的索引，所以，排序的时候应该是，都先比较第一个，然后再。。。有点像常用的大文件分成多个小文件，然后分别排序，最后merge的过程。

主要是能够提供比较好的编码界面。

码农公寓

分区表