php - MySQL "NOT IN"查询优化

Question

优化 MySQL 查询不是我的专长，所以我想知道是否有人可以帮助我在这里制定最佳查询(和索引)。

作为背景，我试图在具有特定 where 条件(日期范围，而不是特定产品等)的交易表中找到不同的访问者 ID，如您在下面的查询中所见。交易和访问者是一对多的关系，因此一个访问者可以有多个交易。

对结果的另一个要求是，如果在结果中找到 visitor_id，它必须是整个表中 visitor_id(按日期时间)的第一个实例。换句话说，visitor_id 应该只存在于主查询中设置的日期范围内，而不能提前。

这是我到目前为止整理的内容。它使用 NOT IN 和一个子查询，但这似乎并不理想，因为查询需要 2-3 秒，因为该表有超过 500k 的记录。我尝试了几种索引变体，但似乎没有任何效果。

这是查询。

SELECT DISTINCT visitor_id, date_time
FROM pt_transactions
WHERE visitor_id NOT IN (SELECT visitor_id FROM pt_transactions WHERE date_time <     '$this->_date_time_start')
AND campaign_id = $this->_campaign_id
AND a_aid = '$a_aid'
AND date_time >= '$this->_date_time_start'
AND date_time <= '$this->_date_time_end'                      
AND product_id != 65

这是完整的表结构。

CREATE TABLE IF NOT EXISTS `pt_transactions` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `type` varchar(2) NOT NULL COMMENT 'New Lead (NL), Raw Optin (RO), Base Sale (BS), Upsell Sale (US), Recurring Sale (RS), Base Refund (BR), Upsell Refund (UR), Recurring Refund (RR), Unknown Refund (XR),  or Chargeback (C)',
  `date_time` datetime NOT NULL,
  `amount` varchar(255) NOT NULL,
  `a_aid` varchar(255) NOT NULL,
  `subid1` varchar(255) NOT NULL,
  `subid2` varchar(255) NOT NULL,
  `subid3` varchar(255) NOT NULL,
  `product_id` int(16) NOT NULL,
  `visitor_id` int(32) NOT NULL,
  `campaign_id` int(16) NOT NULL,
  `last_click_id` int(16) NOT NULL,
  `trackback_type` varchar(255) NOT NULL COMMENT 'Shows if the transaction is tracked back to the original visitor via cookie or via IP.  Usually only applies to sales via pixel.',
  `original_transaction_id` int(32) NOT NULL COMMENT 'Reference to original transaction id, in this table, if type is RS, R, or C',
  `recurring_transaction_id` varchar(32) NOT NULL COMMENT 'Reference to existing RecurringTransaction if type is RS',
  PRIMARY KEY (`id`),
  KEY `visitor_id` (`visitor_id`),
  KEY `campaign_id` (`visitor_id`,`campaign_id`,`amount`,`product_id`),
  KEY `transaction_retrieval_group` (`campaign_id`,`date_time`,`a_aid`),
  KEY `type` (`type`),
  KEY `date_time` (`date_time`),
  KEY `original_source` (`campaign_id`,`a_aid`,`date_time`,`product_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=574636 

Answer 1

你可以试试NOT EXISTS

SELECT DISTINCT visitor_id, date_time
  FROM pt_transactions t
 WHERE campaign_id = $this->_campaign_id
   AND a_aid = '$a_aid'
   AND date_time >= '$this->_date_time_start'
   AND date_time <= '$this->_date_time_end'                      
   AND product_id != 65
   AND NOT EXISTS 
(
  SELECT * 
    FROM pt_transactions 
   WHERE visitor_id = t.visitor_id
     AND date_time < '$this->_date_time_start'
)

做 EXPLAIN <query>并查看您的索引是如何使用的。如果您愿意，可以以文本形式在您的问题中发布结果。