优化mysql标量子查询

mysql的优化方法有很多与oracle类似，而且mysql对子查询的支持更差.

下面是一个mysql的标量子查询

SELECT DISTINCT
	t.act_id,
	(
		SELECT
			count(0)
		FROM
			t
		WHERE
			to_days(now()) - to_days(time_stamp) = 1
	) yesterday,
	(
		SELECT
			count(0)
		FROM
			t
		WHERE
			to_days(time_stamp) = to_days(now())
	) today,
	(
		SELECT
			count(0)
		FROM
			t
		WHERE
			act_id = t.act_id
	) total
FROM
	t t
WHERE
	EXISTS (
		SELECT
			1
		FROM
			a a
		WHERE
			a.shop_id = 'one_shop_id'
		AND a.id = t.act_id
	);

里面用了不必要的标量子查询，前两个无关联条件，第三个根本不需要用子查询，所以效率很低，需要15s

改后如下：

SELECT a.act_id, a.total, y.yesterday, t.today
  FROM (SELECT t.act_id, COUNT(*) AS total
          FROM t
         INNER JOIN a
            ON (a.id = t.act_id)
         WHERE a.shop_id = 'one_shop_id'
         GROUP BY act_id) a,
       (SELECT COUNT(0) AS yesterday
          FROM t
         WHERE to_days(now()) - to_days(time_stamp) = 1) y,
       (SELECT COUNT(0) AS today
          FROM t
         WHERE to_days(time_stamp) = to_days(now())) t;

查询只需要0.1s