MySQL Partition 小结

常见的提升 MySQL 性能的方式有很多种:主从复制实现读写分离,水平方向分表以及对表分区等。后两种是针对单个大表提升性能的方式,采取何种方式取决于数据库的使用场景和查询类型。

一般使用分表的情形:

  • 查询的条件不固定,并且目的在于分析数据
  • 拆分后数量大于 1024 (分区的数目 Mysql 限制为小于1024)

使用分区的情形:

  • 查询比较固定,数据库作为应用的后端
  • 不需要外键支持 (分区无法提供外键支持)

Use cases

比较常用到 Partition 的场景:

  1. 定期删除/归档过期数据
  2. 分区后可根据分区缩小查询范围提高查询性能。

Limitations

Partition 并不能解决所有性能问题,相反,盲目使用 Partition 而且不做相应的优化,分区反而会引入性能问题。举个例子,没有分区前,完成一次请求仅需要一次 I/O,而将一个表分成 4 个区后,完成一次请求反而增大到 4 次 I/O。

PARTITIONing splits up one table into several smaller tables. But table size is rarely a performance issue. Instead, I/O time and indexes are the issues.

此外,Partition 本身还存在一些限制:

  • 不支持外键
  • 在创建分区时不支持存储过程/变量等
  • SQL mode 不能改变,等等

这些需要在使用分区之前,考虑清楚。

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE tbl (
dt DATETIME NOT NULL, -- or DATE
...
PRIMARY KEY (..., dt),
UNIQUE KEY (..., dt),
...
)
PARTITION BY RANGE (TO_DAYS(dt)) (
start VALUES LESS THAN (0),
from20160315 VALUES LESS THAN (TO_DAYS('2016-03-16')),
from20160316 VALUES LESS THAN (TO_DAYS('2016-03-17')),
...
from20160414 VALUES LESS THAN (TO_DAYS('2016-04-15')),
from20160415 VALUES LESS THAN (TO_DAYS('2016-04-16')),
future VALUES LESS THAN MAXVALUE
);

SQL to change partition

1
2
3
4
5
ALTER TABLE tbl DROP PARTITION from20160316;
ALTER TABLE tbl REORGANIZE PARTITION future INTO (
PARTITION from20160417 VALUES LESS THAN (TO_DAYS('2016-04-17')),
PARTITION future VALUES LESS THAN MAXVALUE
);

References

  1. 19.5 Restrictions and Limitations on Partitioning
  2. PARTITION Maintenance in MySQL
  3. MySQL Large Table: Split OR Partitioning???