记一次MySQL表分区操作

最近一次日常迭代中,业务线需要对一张大表进行联合查询,查询性能可想而知,测试过程中服务接口直接响应超时,导致服务不可用,最后临时对该表进行分区操作,暂时缓解性能问题。由于是第一次操作表分区,姑且记录一下整个操作过程。

测试表结构

1
2
3
4
5
6
7
8
CREATE TABLE `tb_partition_test` (
`user_id` bigint(20) NOT NULL ,
`city_id` bigint(20) NOT NULL DEFAULT '0',
`record_type` smallint(6) NOT NULL DEFAULT '0',
`record` smallint(6) NOT NULL DEFAULT '0' ,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`user_id`,`record_type`),
ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分区测试';

表应用场景为:存储用户的成绩明细数据,成绩计算有多种不同的维度,每个用户的每个成绩维度只有一个分值。所以整个表按照record_type分组后,每个成绩对应的数据量是一致的,都为用户数量。最后对record_type进行hash分区。

为了不影响生产环境的业务使用,而恰好该表又不会有业务更新操作,故新建一个分区表,将原有表数据导入,采取分块导入的方式,避免产生大量的临时文件。

创建分区表

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `tb_partition_test_part` (
`user_id` bigint(20) NOT NULL ,
`city_id` bigint(20) NOT NULL DEFAULT '0',
`record_type` smallint(6) NOT NULL DEFAULT '0',
`record` smallint(6) NOT NULL DEFAULT '0' ,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`user_id`,`record_type`),
ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分区测试'
PARTITION BY HASH(record_type)
PARTITIONS 100;

数据导入

1
2
3
4
5
for i in {1..100};do
"INSERT IGNORE INTO tb_partition_test_part
SELECT * from tb_partition_test WHERE score_type=$i;"
sleep 5
done

导入完成后修改表名,会有短暂时间的锁表

1
2
3
4
5
6
set AUTOCOMMIT = 0;
BEGIN ;
RENAME TABLE tb_partition_test to tb_partition_test_20170916;
RENAME TABLE tb_partition_test_part to tb_partition_test;
COMMIT ;
set AUTOCOMMIT = 1;

查询性能比较:

通过explain partitions查看执行计划,对于指定record_type的查询,只会在指定的分区中查找,数据按照record_type均匀分区了,前后执行时间比较

1
2
3
4
5
6
+----------+
| count(0) |
+----------+
| 361795 |
+----------+
1 row in set (42.89 sec)
1
2
3
4
5
6
+----------+
| count(0) |
+----------+
| 361795 |
+----------+
1 row in set (3.04 sec)

显然3.04 sec的性能仍是不可接受的,作为相对低频的业务,这只是临时的处理方案,而且恰好因为目前的需求在每次查询都会附带record_type条件,所以可以很好的利用分区提升性能,但如果出现不按照record_type条件的查询,仍然会出现性能瓶颈,需要后端人员以及来进行优化。

参考

  1. https://dev.mysql.com/doc/refman/5.6/en/partitioning-hash.html
  2. http://www.cnblogs.com/chenmh/p/5623474.html
  3. http://blog.csdn.net/zzy7075/article/details/70054818
  4.http://blog.csdn.net/yongchao940/article/details/55266603