【测试环境】
【 OB 4.2.1】
现在有一张表，表结构如下：
CREATE TABLE xxx (
id bigint(20) NOT NULL COMMENT ‘ۼWD?’,
oaid_md5 varchar(64) DEFAULT NULL COMMENT ‘idfa_md5’,
xxx varchar(64) DEFAULT NULL COMMENT ‘xxx’,
xxx varchar(64) DEFAULT NULL COMMENT ‘xxx’,
ts bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘ts’,
xxx varchar(1024) DEFAULT NULL COMMENT ‘xxx’,
xxx varchar(128) DEFAULT NULL COMMENT ‘xxx’,
xxx varchar(128) DEFAULT NULL COMMENT ‘xxx’,
xxx varchar(1024) DEFAULT NULL COMMENT ‘xxx’,
xxx varchar(1024) DEFAULT NULL COMMENT ‘xxx’,
xxx varchar(1024) DEFAULT NULL COMMENT ‘xxx’,
xxx varchar(128) DEFAULT NULL COMMENT ‘xxx’,
xxx varchar(128) DEFAULT NULL COMMENT ‘xxx’,
xxx datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘???’,
xxx datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘s??’,
PRIMARY KEY (id),
KEY I_oaid (oaid_md5) BLOCK_SIZE 16384 LOCAL,
KEY I_ts (ts) BLOCK_SIZE 16384 LOCAL
) AUTO_INCREMENT = 731895827 AUTO_INCREMENT_MODE = ‘ORDER’ DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by hash(id)
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4,
partition p5,
partition p6,
partition p7,
partition p8,
partition p9,
partition p10,
partition p11,
partition p12,
partition p13,
partition p14,
partition p15,
partition p16,
partition p17,
partition p18,
partition p19,
partition p20,
partition p21,
partition p22,
partition p23,
partition p24,
partition p25,
partition p26,
partition p27,
partition p28,
partition p29)
in的范围为200条数据
SQL1
select max(id) from xxx where oaid_md5 in
（
‘xxx’
‘xxx’
）
SQL2
select max(ts) from dwd_ads_mis_channel_show_dd where oaid_md5 in
（
‘xxx’
‘xxx’
）
SQL1只需要80ms ， SQL2却需要3s
