【 测试环境 】
【 OB 】
【 4.2.0 】
【问题描述】
建表语句：
CREATE TABLE test_partition01 (
record_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘id’,
did bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘did’,
task_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘任务id’,
activity_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘活动id’,
status tinyint(3) NOT NULL DEFAULT ‘-1’ COMMENT ‘状态’,
created datetime NOT NULL DEFAULT ‘1970-01-01 00:00:00’ COMMENT ‘创建时间’,
modified datetime NOT NULL DEFAULT ‘1970-01-01 00:00:00’ COMMENT ‘修改时间’,
task_receive_time datetime NOT NULL DEFAULT ‘2018-01-01 00:00:00’ COMMENT ‘任务接收时间’,
task_completion_time datetime NOT NULL DEFAULT ‘2018-01-01 00:00:00’ COMMENT ‘任务完成时间’,
task_expire_time datetime NOT NULL DEFAULT ‘2018-01-01 00:00:00’ COMMENT ‘任务过期时间’,
PRIMARY KEY (record_id),
UNIQUE KEY un_did_task_id (did,task_id),
KEY created (created),
KEY modified (modified)
) ENGINE=InnoDB AUTO_INCREMENT=3381 DEFAULT CHARSET=utf8mb4;
CREATE TABLE test_partition02 (
record_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘id’,
did bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘did’,
task_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘任务id’,
activity_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘活动id’,
status tinyint(3) NOT NULL DEFAULT ‘-1’ COMMENT ‘状态’,
created datetime NOT NULL DEFAULT ‘1970-01-01 00:00:00’ COMMENT ‘创建时间’,
modified datetime NOT NULL DEFAULT ‘1970-01-01 00:00:00’ COMMENT ‘修改时间’,
task_receive_time datetime NOT NULL DEFAULT ‘2018-01-01 00:00:00’ COMMENT ‘任务接收时间’,
task_completion_time datetime NOT NULL DEFAULT ‘2018-01-01 00:00:00’ COMMENT ‘任务完成时间’,
task_expire_time datetime NOT NULL DEFAULT ‘2018-01-01 00:00:00’ COMMENT ‘任务过期时间’,
PRIMARY KEY (record_id),
UNIQUE KEY un_did_task_id (did,task_id,record_id),
KEY created (created),
KEY modified (modified)
) ENGINE=InnoDB AUTO_INCREMENT=3381 DEFAULT CHARSET=utf8mb4
PARTITION BY HASH(record_id) PARTITIONS 30;
测试sql:
select * from test_partition01 WHERE did = 172695112519474677
select * from test_partition02 WHERE did = xx
读表2的QPS性能比表1要低很多，像这种有主键、唯一键的表结构应该怎么设置分区键呢，