mysql 子查询性能

Question

我的 SQL（带有子查询）花了很长时间（接近 24 小时）。使用子查询对性能不利吗？

我的表如下

mysql> show create table eventnew;
CREATE TABLE `eventnew` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `date` datetime DEFAULT NULL,
  `src_ip` int(10) unsigned DEFAULT NULL,
  `src_port` int(10) unsigned DEFAULT NULL,
  `dst_ip` int(10) unsigned DEFAULT NULL,
  `dst_port` int(10) unsigned DEFAULT NULL,
  `repo_ip` varchar(50) DEFAULT NULL,
  `link` varchar(50) DEFAULT NULL,
  `binary_hash` varchar(50) DEFAULT NULL,
  `sensor_id` varchar(50) DEFAULT NULL,
  `repox_ip` int(10) unsigned DEFAULT NULL,
  `flags` varchar(50) DEFAULT NULL,
  `shellcode` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `date` (`date`),
  KEY `sensor_id` (`sensor_id`),
  KEY `src_ip` (`src_ip`)
) ENGINE=MyISAM AUTO_INCREMENT=883278 DEFAULT CHARSET=latin1

我的 SQL 如下：

SELECT COUNT( DISTINCT binary_hash ) AS cnt
FROM eventnew
WHERE DATE >=  '2010-10-16'
AND DATE <  '2010-10-17'
AND binary_hash NOT 
IN (

SELECT DISTINCT binary_hash
FROM eventnew
WHERE DATE <  '2010-10-16'
AND binary_hash IS NOT NULL
)

下面是运行结果解释：

+----+--------------------+----------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type        | table    | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+--------------------+----------+-------+---------------+------+---------+------+--------+-------------+
|  1 | PRIMARY            | eventnew | range | date          | date | 9       | NULL |  14296 | Using where |
|  2 | DEPENDENT SUBQUERY | eventnew | range | date          | date | 9       | NULL | 384974 | Using where |
+----+--------------------+----------+-------+---------------+------+---------+------+--------+-------------+

Answer 1

使用子查询肯定会影响您的性能。例如，假设表 T1 有 'n' 条记录，T2 有 'm' 条记录。当您对 T1 和 T2 进行联接时，它将获取 n*m 条记录，然后根据您的条件对它们进行排序。同样的情况也适用于 in 关键字。如果子查询中有另一个约束，则会进一步降低效率。然而，在实践中使用子查询是不可避免的。

Answer 2

我建议您使用 NOT EXISTS 而不是 NOT IN。

Answer 3

试试这个

SELECT COUNT( DISTINCT a.binary_hash ) AS cnt
FROM eventnew a left join eventnew b on (a.binary_hash=b.binary_hash AND b.binary_hash IS NOT NULL AND b.DATE <  '2010-10-16')
WHERE a.DATE >=  '2010-10-16'
AND a.DATE <  '2010-10-17'
and  b.date is null