浅谈mysql分区表

write by donaldhan, 2020-01-31 20:22

Mysql

引言

在业务不断迭代，进化的过程，相关数据在数据库表中沉淀下来，少则千万，多级亿级，针对面向C端的业务数据，我们一般使用数据库中间件来解决大数据量的问题，比如360的Atlas, 及阿里的Mycat, Mycat现在开源自愿者在维护，Atlas文档方更全面一些。针对大表，Mysql提供一个解决方案，即分区表。

Mysql分区表使用限制

Mysql分区表的使用有一定的限制，在大型的互联网公司很少使用，具体原因如下：

分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
旦数据量并发量上来，如果在分区表实施关联，就是一个灾难
自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控
运维的坑，嘿嘿

参考：互联网公司为啥不使用mysql分区表

针对非业务数据表，可以使用分区表，因为分区表创建是有一定的限制

比如如下建表SQL

CREATE TABLE `task` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `partition_key` int(11) NOT NULL,
  `task_no` varchar(64) DEFAULT NULL COMMENT '任务编号',
  `task_date` datetime DEFAULT NULL,
  `task_time` bigint(20) DEFAULT NULL,
  `is_delete` tinyint(4) DEFAULT '0' COMMENT '1:删除；0:正常',
  `remark` varchar(6) DEFAULT NULL COMMENT '备注',
  `version` bigint(20) DEFAULT NULL COMMENT '更新版本',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`,`partition_key`),
  KEY `idx_task_time` (`task_time`),
  KEY `idx_partition_key` (`partition_key`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH (partition_key)
PARTITIONS 32 ;

针对这张表，如果task_no是唯一主键，我们就不能对分区字段partition_key，进行分区，否则会报如下错误：

[SQL]ALTER TABLE task ADD UNIQUE  `idx_task_no` (`task_no`);
[Err] 1503 - A UNIQUE INDEX must include all columns in the table's partitioning function

原因为，创建分区表时，分区字段必须包含联合主键中；同时分区表中不能有其他唯一索引，否则回报上面的错误，这个可能可能是由于分区同时保证唯一性比较麻烦；

针对非业务数据表，一般数据都有一定的时间性，可以根据时间日期（20200131），进行分区。注意分区key必须包含在联合主键中，否则会报如下错误：

[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function

出现上面的原因，是因为分区字段不在联合主键中，可能分区很难保证唯一性；

跟分区表的用法可以参考：MySQL · 最佳实践 · 分区表基本类型, 这里不再造轮子。

0xTTEPX

Just do it, deeply...

浅谈mysql分区表

引言

Mysql分区表使用限制