mysql - GROUP BY 查询 - 为什么这么慢

Question

我正在尝试在一个大表(超过 800 万行)上生成一个组查询。但是我可以减少按日期对所有数据进行分组的需要。我有一个 View 可以捕获我需要的日期，这限制了查询，但它并没有好多少。最后我需要加入另一个表来获取一个字段。

我正在展示查询，在主表上创建，下面解释查询。

主要查询:

SELECT  pgi_raw_data.wsp_channel,
        'IOM' AS wsp,
        pgi_raw_data.dated,
        pgi_accounts.`master`,
        pgi_raw_data.event_id,
        pgi_raw_data.breed,
        Sum(pgi_raw_data.handle),
        Sum(pgi_raw_data.payout),
        Sum(pgi_raw_data.rebate),
        Sum(pgi_raw_data.profit)
FROM    pgi_raw_data
        INNER JOIN summary_max
                ON pgi_raw_data.wsp_channel = summary_max.wsp_channel
                        AND pgi_raw_data.dated > summary_max.race_date
        INNER JOIN pgi_accounts
                ON pgi_raw_data.account = pgi_accounts.account
GROUP   BY pgi_raw_data.event_id
ORDER   BY NULL

创建表:

 CREATE TABLE `pgi_raw_data` (
  `event_id` char(25) NOT NULL DEFAULT '',
  `wsp_channel` varchar(5) NOT NULL,
  `dated` date NOT NULL,
  `time` time DEFAULT NULL,
  `program` varchar(5) NOT NULL,
  `track` varchar(25) NOT NULL,
  `raceno` tinyint(2) NOT NULL,
  `detail` varchar(30) DEFAULT NULL,
  `ticket` varchar(20) NOT NULL DEFAULT '',
  `breed` varchar(12) NOT NULL,
  `pool` varchar(10) NOT NULL,
  `gross` decimal(11,2) NOT NULL,
  `refunds` decimal(11,2) NOT NULL,
  `handle` decimal(11,2) NOT NULL,
  `payout` decimal(11,4) NOT NULL,
  `rebate` decimal(11,4) NOT NULL,
  `profit` decimal(11,4) NOT NULL,
  `account` mediumint(10) NOT NULL,
  PRIMARY KEY (`event_id`,`ticket`),
  KEY `idx_account` (`account`),
  KEY `idx_wspchannel` (`wsp_channel`,`dated`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

这是我对 summary_max 的看法:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW 
 `summary_max` AS select `pgi_summary_tbl`.`wsp_channel` AS 
 `wsp_channel`,max(`pgi_summary_tbl`.`race_date`) AS `race_date` 
from `pgi_summary_tbl` group by `pgi_summary_tbl`.`wsp

还有评估的查询:

1   PRIMARY <derived2>  ALL                 6   Using temporary

1   PRIMARY pgi_raw_data    ref idx_account,idx_wspchannel  idx_wspchannel  
7   summary_max.wsp_channel 470690  Using where

1   PRIMARY pgi_accounts    ref PRIMARY PRIMARY 3   gf3data_momutech.pgi_raw_data.account   29  Using index

2   DERIVED pgi_summary_tbl ALL                 42282   Using temporary; Using filesort

索引方面的任何帮助都会有所帮助。

Answer 1

至少您需要在这些字段上建立索引:

pgi_raw_data.wsp_channel,
pgi_raw_data.dated,  
pgi_raw_data.account
pgi_raw_data.event_id,
summary_max.wsp_channel, 
summary_max.race_date, 
pgi_accounts.account

一般(并非总是)规则是您要排序、分组、过滤或加入的任何内容都应该有一个索引。

另外:pgi_summary_tbl.wsp

此外，为什么order by null？