记一次MySQL表分区操作

发表于 | 分类于 | | 阅读次数:
一次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