MySql ， JOIN 和 Group By 查询正在使用临时和 Filesort

Question

我有如下 2 个表:

CREATE TABLE `ox_campaigns` (                                                       
    `campaignid` mediumint(9) NOT NULL auto_increment,                                
    `campaignname` varchar(255) NOT NULL default '',                                  
    `clientid` mediumint(9) NOT NULL default '0',                                                                                                    
    `is_deleted` tinyint(1) NOT NULL default '0',                                        
    PRIMARY KEY  (`campaignid`),                                                      
    KEY `ox_campaigns_clientid` (`clientid`)                                          
) ENGINE=MyISAM DEFAULT CHARSET=utf8  

CREATE TABLE `ox_clients` (                                   
  `clientid` mediumint(9) NOT NULL auto_increment,            
  `agencyid` mediumint(9) NOT NULL default '0',               
  `clientname` varchar(255) NOT NULL default '',                                                                
  `is_deleted` tinyint(4) NOT NULL,                           
  PRIMARY KEY  (`clientid`),                                  
  UNIQUE KEY `ox_clients_account_id` (`account_id`),          
  KEY `ox_clients_agencyid` (`agencyid`)                      
) ENGINE=MyISAM DEFAULT CHARSET=utf8              

一个客户可以关联多个事件。

我有一个 campaignids 列表，我想要的是这些事件的不同 clientids 列表。

我正在使用的查询是:

SELECT clients.* 
    FROM clients  
    JOIN campaigns ON clients.clientid = campaigns.clientid
    WHERE campaigns.is_deleted=0 
        AND campaignid in (2325,2395)
        AND clients.is_deleted=0 
    GROUP BY clients.clientid

它给出的 EXPLAIN 输出是:

+----+-------------+-----------+-------+-------------------------------+---------+--------+-------------------------------------------------------------------------------+
| id | select_type | table     | type  | possible_keys                 | key     | key_len| ref                      | rows| Extra                                        |
+----+-------------+-----------+-------+-------------------------------+---------+--------+-------------------------------------------------------------------------------+
| 1  |    SIMPLE   | campaigns | range | PRIMARY,ox_campaigns_clientid | PRIMARY | 3      |             NULL         | 2   | Using where; Using temporary; Using filesort |
| 1  |    SIMPLE   | clients   | eq_ref| PRIMARY                       | PRIMARY | 3      | openx.campaigns.clientid | 1   | Using where

为什么它为此查询使用临时和文件排序？

Answer 1

由于 group by，它正在使用文件排序。您可以通过对您正在做的事情使用 exists 子句来防止这种情况:

SELECT c.* 
FROM clients c 
WHERE EXISTS (SELECT 1
              FROM campaigns ca
              WHERE ca.clientid = c.clientid AND
                    ca.is_deleted = 0 AND
                    ca.campaignid IN (2325,2395)
             )
      AND c.is_deleted = 0;

您在 campaigns_clientid(clientid) 上有一个索引，所以这应该使用索引。更好的索引是 campaigns_clientid(clientid, is_deleted, campaign_id)。该索引“覆盖”了子查询。换句话说，引擎将只使用索引而不必读入该表的数据页。