你的 SQL 没有用到任何索引,对 a b 两个表都是全表扫描,在数据量小的时候是没有问题的,但是如果数据量超过 100 万,性能问题就会突显出来。
这里不清楚你的 created_at 字段是什么类型,不过从你的代码 DATE_FORMAT(created_at, '%Y-%m-%d') 看来,应该是 DATETIME 或者 TIMESTAMP,这个字段对你的场景来说并不是很友好,如果是我的话,可能会新增一个 DATE 类型的字段 created_at_date 来解决这个问题:
ALTER TABLE `a` ADD COLUMN `created_at_date` DATE;
ALTER TABLE `b` ADD COLUMN `created_at_date` DATE;
ALTER TABLE `a` ADD INDEX `created_at_date` (`created_at_date`);
ALTER TABLE `b` ADD INDEX `created_at_date` (`created_at_date`);
对表中现有的数据,将 created_at_date 设置为 DATE(created_at):
UPDATE `a` SET `created_at_date` = DATE(`created_at`);
UPDATE `b` SET `created_at_date` = DATE(`created_at`);
如果不想改应用层代码,可以创建一个 TRIGGER,自动设置该字段。
其次,不太清楚你的具体需求,不知道是日期出现一次就统计一次,还是只要表中存在该日期,就算一次?如果是情况二,楼上的回答其实就可以了,可以用到 DATE 列的索引:
select c.t,count(*) from (
select a.t,count(*) from a a group by a.t
union all
select b.t,count(*) from b b group by b.t
) c
group by c.t;
如果是情况一,从你写的 SQL 判断可能是这种情况吧。这时要把两个临时表的 DATE 列拿出来做 FULL OUTER JOIN 了,然后计算 a.count + b.count。但是 MYSQL 不支持 FULL OUTER JOIN,可以 用 LEFT OUTER JOIN UNION RIGHT OUTER JOIN 小技巧 来解决,完整的 SQL 如下(仅供参考):
SELECT ta.`created_at_date`, ta.c + IFNULL(tb.c,0) cnt FROM
(
SELECT `created_at_date`, COUNT(*) c FROM a GROUP BY `created_at_date`
) AS ta LEFT OUTER JOIN
(
SELECT `created_at_date`, COUNT(*) c FROM b GROUP BY `created_at_date`
) AS tb ON ta.`created_at_date` = tb.`created_at_date`
UNION
SELECT tb.`created_at_date`, tb.c cnt FROM
(
SELECT `created_at_date`, COUNT(*) c FROM a GROUP BY `created_at_date`
) AS ta RIGHT OUTER JOIN
(
SELECT `created_at_date`, COUNT(*) c FROM b GROUP BY `created_at_date`
) AS tb ON ta.`created_at_date` = tb.`created_at_date`
WHERE ta.c IS NULL