我有两张表-profile
有1500000条记录,indicator_trades
有12000000条记录。以下查询返回0个结果,大约需要10秒才能完成。
SELECT `trd`.`symbol`, `p`.`type`
FROM `indicator_trades` AS `trd`
INNER JOIN `profile` AS `p` ON `p`.`symbol` = `trd`.`symbol`
WHERE `start_date` >= '2014-09-17' AND `p`.`type` = 2
描述结果:
+----+-------------+-------+--------+-----------------------+------------+---------+--------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------+------------+---------+--------------------------+------+-------------+
| 1 | SIMPLE | trd | range | IDX_symbol,start_date | start_date | 4 | NULL | 3662 | Using where |
| 1 | SIMPLE | p | eq_ref | IDX_symbol,type | IDX_symbol | 34 | barchart_data.trd.symbol | 1 | Using where |
+----+-------------+-------+--------+-----------------------+------------+---------+--------------------------+------+-------------+
对于使用键的查询来说,10秒似乎是一个异常长的时间,但是是否需要那么长的时间才能从
indicator_trades
表中的12000000条记录中减去3662条记录呢?在两个表上显示CREATE:
CREATE TABLE `indicator_trades` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`symbol` varchar(32) NOT NULL,
`indicator_code` varchar(10) NOT NULL,
`start_date` date DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `IDX_symbol` (`symbol`) USING BTREE,
KEY `start_date` (`start_date`),
KEY `indicator_code` (`indicator_code`)
) ENGINE=InnoDB AUTO_INCREMENT=12582721 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT |
CREATE TABLE `profile` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`symbol` varchar(32) NOT NULL,
`type` int(11) DEFAULT NULL,
`lastupdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
UNIQUE KEY `IDX_symbol` (`symbol`) USING BTREE,
KEY `type` (`type`),
KEY `exchange` (`exchange`)
) ENGINE=InnoDB AUTO_INCREMENT=320948248 DEFAULT CHARSET=latin1
最佳答案
乍一看,您的查询应该很快,也由EXPLAIN输出描述。
您无法避免磁盘读取,因为您需要获取一个不在所用索引中的字段(例如:使用indexstart_date
,但需要reedsymbol
字段;使用symbol
索引但读取type
字段)。
此外,还需要确保所有索引都加载到内存中(缓冲池设置允许您增加它)。
您的查询可以这样重写(我认为应该有相同的性能):
SELECT `p`.`symbol`,
2 AS `type`
FROM `profile` AS `p`
WHERE `p`.`symbol` IN (SELECT DISTINCT( `trd`.`symbol` )
FROM `indicator_trades` AS `trd`
WHERE `trd`.`start_date` >= '2014-09-17')
AND `p`.`type` = 2
但是您可以运行子查询并让我们知道它的速度是多少:
SELECT DISTINCT( `trd`.`symbol` )
FROM `indicator_trades` AS `trd`
WHERE `trd`.`start_date` >= '2014-09-17'
关于mysql - MySQL查询未正确使用索引?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/26263844/