mysql - mysql表不再优化了吗？

Question

我认为我已经对以下表格结构进行了优化:

   CREATE TABLE `sal_forwarding` (
  `sid` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `f_shop` INT(11) NOT NULL,
  `f_offer` INT(11) DEFAULT NULL,  
  .
  .
  .
  .
  .
  `f_affiliateId` TINYINT(3) UNSIGNED NOT NULL,  
  `forwardDate` DATE NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `f_partner` (`f_partner`,`forwardDate`),
  KEY `forwardDate` (`forwardDate`,`cid`),
  KEY `forwardDate_2` (`forwardDate`,`f_shop`),
  KEY `forwardDate_3` (`forwardDate`,`f_shop`,`f_partner`),
  KEY `forwardDate_4` (`forwardDate`,`f_partner`,`cid`),
  KEY `forwardDate_5` (`forwardDate`,`f_affiliateId`),
  KEY `forwardDate_6` (`forwardDate`,`f_shop`,`sid`),
  KEY `forwardDate_7` (`forwardDate`,`f_shop`,`cid`),
  KEY `forwardDate_8` (`forwardDate`,`f_affiliateId`,`cid`)
) ENGINE=INNODB AUTO_INCREMENT=10946560 DEFAULT CHARSET=latin1

这是解释语句:

id  select_type  table           type   possible_keys                                                                                                   key             key_len ref     rows    Extra
1   SIMPLE       sal_forwarding  range  forwardDate,forwardDate_2,forwardDate_3,forwardDate_4,forwardDate_5,forwardDate_6,forwardDate_7,forwardDate_8   forwardDate_7   3       (NULL)  1221784 Using where; Using index; Using filesort

以下查询需要 23 秒才能读取 2300 行:

SELECT COUNT(sid),f_shop, COUNT(DISTINCT(cid)) 
FROM sal_forwarding  
WHERE forwardDate BETWEEN "2011-01-01" AND "2011-11-01" 
GROUP BY f_shop

我可以做些什么来提高性能？非常感谢。

Answer 1

对您所拥有的内容进行轻微修改...使用 count(*) 而不是实际字段。对于 DISTINCT，您不需要 () 围绕它。它可能会对您拥有的所有索引感到困惑。删除forwardDate 上的所有其他索引，但基于 (forwardDate, f_shop, cid )(您当前的 key7 索引)的索引除外

SELECT 
      COUNT(*),
      f_shop, 
      COUNT(DISTINCT cid ) 
   FROM 
      sal_forwarding  
   WHERE 
      forwardDate BETWEEN "2011-01-01" AND "2011-11-01" 
   GROUP BY 
      f_shop

然后，为了一笑，由于似乎没有其他方法对您有用，请尝试在记录上放入预子查询，然后从中求和，这样它就不会依赖于基于您的近 1100 万条记录的任何其他索引页记录(根据自动增量值隐含)...

SELECT 
      f_shop, 
      sum( PreQuery.Presum) totalCnt,
      COUNT(*) dist_cid 
   FROM 
      ( select f_shop, cid, count(*) presum
           from sal_forwarding
           WHERE forwardDate BETWEEN "2011-01-01" AND "2011-11-01"
           group by f_shop, cid ) PreQuery
   GROUP BY 
      f_shop

由于内部预查询正在对记录进行简单计数并按 F_Shop 和 C_ID 进行分组(可通过索引优化)，因此您现在将通过简单计数来汇总不同的记录...然后执行 SUM内部计数的“presum”列的 ()。再说一次，这只是尝试扭转局面的另一种选择，希望它对您有用。