php - 如何避免在 MySQL 请求中连接表

Question

我正在创建一个包含 3 列的报告表:当天的总计数、pt_scanid 的计数 对于表来说是完全唯一的 以及日期一天。

这是表架构:

CREATE TABLE IF NOT EXISTS `partners_scanstracking` (
`pt_id` int(11) NOT NULL AUTO_INCREMENT,
`pt_partner_id` int(5) NOT NULL,
`pt_ip` varchar(30) NOT NULL,
`pt_scanid` varchar(50) NOT NULL,
`pt_fn` varchar(20) DEFAULT NULL,
`pt_url` mediumtext,
`pt_created` datetime NOT NULL,
PRIMARY KEY (`pt_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

这是我创建的请求:

SELECT COUNT(DISTINCT `c`.`pt_created`) AS `count`,
       COUNT(DISTINCT `x`.`pt_scanid`) AS `uniq`,
       `c`.`pt_created`
FROM `partners_scanstracking` c
LEFT JOIN
  (SELECT `x`.*
   FROM
     (SELECT *
      FROM `partners_scanstracking`
      WHERE `pt_partner_id` = '0'
      ORDER BY `pt_created` ASC) x
   GROUP BY `pt_scanid`) x ON DATE(`c`.`pt_created`)=DATE(`x`.`pt_created`)
WHERE (DATE(`c`.`pt_created`) >= '2016-10-06'
       AND DATE(`c`.`pt_created`) <= '2016-12-14')
  AND `c`.`pt_partner_id` = '0'
GROUP BY DATE(`c`.`pt_created`)
ORDER BY `c`.`pt_created` ASC

Explain

之前一切都很好，但现在表有 500.000 条记录，请求速度太慢。我想主要原因是我要加入两张大 table 。

我不知道如何优化查询或避免此处加入。

有人可以帮我吗？

PS:如果没有什么可以优化的，我想我需要为计算结果创建表并使用 crons 计划。

Answer 1

1. 不要选择不需要的字段。 MySQL 可能 足够聪明，可以忽略子查询中的 SELECT *，但我不会打赌。

2. 避免对标准使用函数调用；如果 pt_created 被索引，DATE() 将使查询忽略索引；如果 pt_created 没有被编入索引，它应该被编入索引。

   地点(日期(c.pt_created)>='2016-10-06'和日期(c.pt_created)<='2016-12-14')

会更好，更高效:

c.pt_created BETWEEN '2016-10-06 00:00:00' AND '2016-12-14 23:59:59'

与此相关的是，目前还不太清楚这应该做什么:

ON DATE(`c`.`pt_created`)=DATE(`x`.`pt_created`)

---

是什么特殊要求导致了该查询而不是像这样的查询？

SELECT COUNT(DISTINCT `pst`.`pt_created`) AS `count`,
       COUNT(DISTINCT `pst`.`pt_scanid`) AS `uniq`,
       DATE(`c`.`pt_created`) AS theDate
FROM `partners_scanstracking` AS pst
WHERE pst.pt_created BETWEEN '2016-10-06 00:00:00' AND '2016-12-14 23:59:59'
  AND pst.`pt_partner_id` = '0'
GROUP BY theDate
ORDER BY theDate ASC