>我必须有 6550 万行的表: 1)
CREATE TABLE RawData1 (
cdasite varchar(45) COLLATE utf8_unicode_ci NOT NULL,
id int(20) NOT NULL DEFAULT '0',
timedate datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
type int(11) NOT NULL DEFAULT '0',
status int(11) NOT NULL DEFAULT '0',
branch_id int(20) DEFAULT NULL,
branch_idString varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (id,cdasite,timedate),
KEY idx_timedate (timedate,cdasite)
) ENGINE=InnoDB;
2) 与分区相同的表(称为 RawData2)
PARTITION BY RANGE ( TO_DAYS(timedate))
(PARTITION p20140101 VALUES LESS THAN (735599) ENGINE = InnoDB,
PARTITION p20140401 VALUES LESS THAN (735689) ENGINE = InnoDB,
.
.
PARTITION p20201001 VALUES LESS THAN (738064) ENGINE = InnoDB,
PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
我使用相同的查询:
SELECT count(id) FROM RawData1
where timedate BETWEEN DATE_FORMAT(date_sub(now(),INTERVAL 2 YEAR),'%Y-%m-01') AND now();
2个问题: 1. 为什么分区表比常规表运行时间长? 2.常规表返回 17.094 秒36380217正常吗,所有研发负责人都认为不够快,需要在 ~2 秒内返回。
我需要检查/执行/更改什么? 在不到 3-4 秒的时间内扫描35732495行并检索36380217是否现实?
- 您已经找到了一个例子来说明为什么
PARTITIONing
不是性能灵丹妙药。 id
从何而来?cdasite
有多少个不同的值? 如果数千,而不是数百万,则构建一个映射 cdasite <=> id 的表,并从笨重的VARCHAR(45)
切换到MEDIUMINT UNSIGNED
(或任何合适的)。 这个项目可能最有帮助,但也许还不够。- 同上
status
,但可能使用TINYINT UNSIGNED
。 或者想想ENUM。 要么是 1 个字节,而不是 4。 INT(20)
上的(20)
毫无意义。 你会得到一个限制约为 20 亿的 4 字节整数。- 您确定没有重复
timedates
吗? branch_id
和branch_idString
- 这闻起来像一对需要在另一个表中,只留下ID在这里?- 更小 ->更快。
COUNT(*)
与COUNT(id)
相同,因为id
是NOT NULL
。- 在需要之前不要包括将来的分区;这会减慢速度。 (并且根本不使用分区。
若要更快地获取该查询,请生成并维护摘要表。 它将在PRIMARY KEY
中至少有一个DATE
,并且至少COUNT(*)
作为列。 然后,查询将从该表中获取。 详细了解汇总表:http://mysql.rjweb.org/doc.php/summarytables