java

mysql 亿级数据优化 - 使用分区

本文主要讲述 mysql range 分区的使用，以及使用 range 分区需要注意的事项，所谓分区，是指 mysql 底层实现的水平分表功能，但是对程序却是透明的，通过使用分区你可以做到水平的拆分你的数据库，从而提高各种性能，比如插入速度、查询速度等。

至于 mysql 分区到底是啥网上资料够多了，此处略去，我只补充几点，一是，如果数据量少于 1000 万条，那么不要分区了，分区也没啥用，你用牛刀杀鸡，不见得就能提高效率，少于 1000 万，建个索引就好了，查询速度不会差，二是，分区要求分区键要么是唯一主键，要么是联合主键里的一个，三是，mysql 最好不要用 timestamp 类型，网上有太多教程建议您使用它取代 dateTime，但是它有范围限制，2038年啊，有生之年你的程序将不支持继续运行，想一下，如果一个普通公司，有个程序员设计的数据库用的是 timestamp，然后公司一直活的好好的，到了 2038-01-19 03:14:07 ，突然系统崩溃了，那会是多么惨烈的场景啊，一代大神，晚节不保啊！！！而且，这个错误你会很容易忽略它。关于 dateTime 和 timestamp 你可以参考：https://dev.mysql.com/doc/refman/8.0/en/datetime.html

好了，进入主题，mysql 分区可能有多种，比如 hash 分区，list 分区等，本文主要说 range 分区，range 分区是指将一定范围的数据存在同一个分区，这就非常有用了，比如按年将数据归档，这样子你可以非常容易实现水平拆分，而且查询时还可以并发查询，不过 count 和 sum 这两个函数目前应该依旧不支持并发，并发也是底层实现，不需要你操作它。

我个人建议，最好按时间去水平拆分，不要按自增的id，或者说不要按唯一索引去分区，在我的测试中，我发现用唯一索引分区，查询时，即使已经指定了范围，但 explain partition 依旧显示是查找了所有分区，这点我后面会再验证，然后补充，如果真是这样，那么，你一定不能使用唯一主键去分区，那样子查询等于没分区，只有插入速度优化了而已。

下面，我们看一个使用 range 分区的例子，我使用 id 和 create_time 做联合主键，并使用 create_time 做分区键，如果之前 id 是唯一主键，那么，你可以修改下：

ALTER TABLE xxx DROP PRIMARY KEY ,ADD PRIMARY KEY (`id`, `create_time`);

然后修改表，并添加分区：

ALTER TABLE xxx
PARTITION BY RANGE (to_days(create_time))
(
PARTITION p201901 VALUES LESS THAN (to_days('2000-01-01')),
PARTITION p201907 VALUES LESS THAN (to_days('2005-01-01')),
PARTITION p202001 VALUES LESS THAN (to_days('2010-01-01')),
PARTITION p202007 VALUES LESS THAN (to_days('2015-01-01')),
PARTITION p202101 VALUES LESS THAN (to_days('2020-01-01')),
PARTITION p202107 VALUES LESS THAN (to_days('2025-01-01')),
PARTITION p202201 VALUES LESS THAN (to_days('2030-01-01')),
PARTITION p202207 VALUES LESS THAN (to_days('2035-01-01')),
PARTITION p202301 VALUES LESS THAN (to_days('2040-01-01')),
PARTITION p202307 VALUES LESS THAN (to_days('2045-01-01')),
PARTITION p202401 VALUES LESS THAN (to_days('2050-01-01')),
PARTITION p202407 VALUES LESS THAN (to_days('2055-01-01')),
   PARTITION pmax VALUES LESS THAN MAXVALUE
);

这里，假设我们就以 5 年分一个区，且分到 2055 结束，那时候，我也差不多退休啦。。。哈哈哈哈哈哈。。。

就这样，这就好了，然后你就可以正常去使用了，但是查询的时候做好限定 create_time 这样子将在指定的分区查找。具体你可以通过 explain partition select * from xxx where create_time > xxx and create_time < xxx; 去验证。