目录

29 MYSQL 分区表

要不要使用分区表

1. 分区表

为了说明分区表的组织形式,先创建一个表 t:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE `t` (
  `ftime` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);

表 t 将包含了一个.frm 文件和 4 个.ibd 文件,每个分区对应一个.ibd 文件。也就是说:

  1. 对于引擎层来说,这是 4 个表;
  2. 对于 Server 层来说,这是 1 个表。

1.1 分区表的引擎层行为

innodb 分区表

/images/mysql/MySQL45%E8%AE%B2/split_innodb.png

从上面的实验效果可以看出,session B 的第一个 insert 语句是可以执行成功的。这是因为,对于引擎来说,p_2018 和 p_2019 是两个不同的表,也就是说 2017-4-1 的下一个记录并不是 2018-4-1,而是 p_2018 分区的 supremum。所以 T1 时刻,在表 t 的 ftime 索引上,间隙和加锁的状态其实是图 4 这样的:

/images/mysql/MySQL45%E8%AE%B2/split_innodb_lock.jpg

myisam 分区表

首先用 alter table t engine=myisam,把表 t 改成 MyISAM 表,然后执行下面这个操作

/images/mysql/MySQL45%E8%AE%B2/split_myisam.png

由于 MyISAM 引擎只支持表锁,所以这条 update 语句会锁住整个表 t 上的读。正是因为 MyISAM 的表锁是在引擎层实现的,session A 加的表锁,其实是锁在分区 p_2018 上。因此,只会堵住在这个分区上执行的查询,落到其他分区的查询是不受影响的。

分区策略

每当第一次访问一个分区表的时候,MySQL 需要把所有的分区都访问一遍。

一个典型的报错情况是这样的:如果一个分区表的分区很多,比如超过了 1000 个,而 MySQL 启动的时候,open_files_limit 参数使用的是默认值 1024,那么就会在访问这个表的时候,由于需要打开所有的文件,导致打开表文件的个数超过了上限而报错。

MyISAM 分区表使用的分区策略,我们称为通用分区策略(generic partitioning),每次访问分区都由 server 层控制。通用分区策略,是 MySQL 一开始支持分区表的时候就存在的代码,在文件管理、表管理的实现上很粗糙,因此有比较严重的性能问题。

从 MySQL 5.7.9 开始,InnoDB 引擎引入了本地分区策略(native partitioning)。这个策略是在 InnoDB 内部自己管理打开分区的行为。

从 MySQL 8.0 版本开始,就不允许创建 MyISAM 分区表了,只允许创建已经实现了本地分区策略的引擎。目前来看,只有 InnoDB 和 NDB 这两个引擎支持了本地分区策略。

1.2 分区表的 server 层行为

如果从 server 层看的话,一个分区表就只是一个表。

/images/mysql/MySQL45%E8%AE%B2/split_server.png

分区表,在做 DDL 的时候,影响会更大。虽然 session B 只需要操作 p_2107 这个分区,但是由于 session A 持有整个表 t 的 MDL 锁,就导致了 session B 的 alter 语句被堵住。

到这里我们小结一下:

  1. MySQL 在第一次打开分区表的时候,需要访问所有的分区;
  2. 在 server 层,认为这是同一张表,因此所有分区共用同一个 MDL 锁;
  3. 在引擎层,认为这是不同的表,因此 MDL 锁之后的执行过程,会根据分区表规则,只访问必要的分区。

而关于“必要的分区”的判断,就是根据 SQL 语句中的 where 条件,结合分区规则来实现的。

2. 分区表的适用场景

2.1 分区表的优点

分区表的一个显而易见的优势是对业务透明,相对于用户分表来说,使用分区表的业务代码更简洁。

还有,分区表可以很方便的清理历史数据。如果一项业务跑的时间足够长,往往就会有根据时间删除历史数据的需求。这时候,按照时间分区的分区表,就可以直接通过 alter table t drop partition …这个语法删掉分区,从而删掉过期的历史数据。

这个 alter table t drop partition …操作是直接删除分区文件,效果跟 drop 普通表类似。与使用 delete 语句删除数据相比,优势是速度快、对系统影响小。

2.2 分区表的劣势

实际使用时,分区表跟用户分表比起来,有两个绕不开的问题:

  1. 一个是第一次访问的时候需要访问所有分区
  2. 另一个是共用 MDL 锁。

因此,如果要使用分区表,就不要创建太多的分区。这里有两个问题需要注意:

  1. 分区并不是越细越好。实际上,单表或者单分区的数据一千万行,只要没有特别大的索引,对于现在的硬件能力来说都已经是小表了
  2. 分区也不要提前预留太多,在使用之前预先创建即可。对于没有数据的历史分区,要及时的 drop 掉。

至于分区表的其他问题,比如查询需要跨多个分区取数据,查询性能就会比较慢,基本上就不是分区表本身的问题,而是数据量的问题或者说是使用方式的问题了。当然,如果你的团队已经维护了成熟的分库分表中间件,用业务分表,对业务开发同学没有额外的复杂性,对 DBA 也更直观,自然是更好的。