mysql 子查询 性能_mysql子查询性能

我的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

)以下是运行结果EXPLAIN：

+----+--------------------+----------+-------+---------------+------+---------+------+--------+-------------+

| 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 |

+----+--------------------+----------+-------+---------------+------+---------+------+--------+-------------+