我有一个mysql表,其中包含字段:rangeFrom
和rangeTo
。
我想请求具有以下条件的行:在连接内rangeFrom >= ? AND rangeTo <=?
。
EXPLAIN SELECT *
FROM Version
JOIN Contract FORCE INDEX FOR JOIN (versionRangeFrom)
ON Version.id >= Contract.versionRangeFrom
AND Version.id <= Contract.versionRangeTo
WHERE Version.completedAt = '2016-06-06 10:00:01';
mysql 是这样解释的:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Version ref PRIMARY,completedAt completedAt 6 const 1 NULL
1 SIMPLE Contract ALL versionRangeFrom NULL NULL NULL 640744 Range checked for each record (index map: 0x8)
因此,它必须通过大约需要 1-2 秒的640744行工作。
但是,在查询中插入版本 ID 效果很好
EXPLAIN SELECT *
FROM Contract
WHERE 5 >= Contract.versionRangeFrom AND 5 <= Contract.versionRangeTo;
然后这样解释:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Contract range versionRangeFrom versionRangeFrom 4 NULL 534 Using index condition; Using where
所以在这种情况下,mysql 只经过 534 行,只需要大约 30 毫秒。
那么我如何正确准备这样的范围检查。在这些情况下,mysql 似乎无法使用索引。我可以使用 2 个查询来解决它,但我宁愿有一个。
这里有更多架构:
CREATE TABLE `Version` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`completedAt` datetime DEFAULT NULL,
`createdAt` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `completedAt` (`completedAt`)
)
CREATE TABLE `Contract` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`contractId` bigint(20) unsigned NOT NULL,
`startAt` bigint(20) NOT NULL DEFAULT '0',
`endAt` bigint(20) NOT NULL DEFAULT '0',
`tradeStartAt` bigint(20) NOT NULL DEFAULT '0',
`tradeEndAt` bigint(20) NOT NULL DEFAULT '0',
`latestAiId` bigint(20) NOT NULL DEFAULT '0',
`type` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
`daPreis` int(11) NOT NULL DEFAULT '0',
`lastTradePreis` int(11) NOT NULL DEFAULT '0',
`lastTradeVol` int(11) NOT NULL DEFAULT '0',
`VWAID` double NOT NULL DEFAULT '0',
`versionRangeFrom` int(10) unsigned NOT NULL,
`versionRangeTo` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `tradeStartAt` (`tradeStartAt`),
KEY `contractId` (`contractId`),
KEY `versionRangeFrom` (`versionRangeFrom`)
)
与"5"不同的值不需要只查看 534 行。
这个问题(x < from AND x > to
)不是平凡的,没有简单的答案。
缩小桌子大小会有所帮助。 当一些较小的数据类型就足够时,不要使用 BIGINT
(8 个字节)。
也许唯一真正的解决方案涉及对架构和代码的重大修改。 查看我的博客。
编辑
在某些情况下,可以有效地使用此子查询来查找有问题的行:
( SELECT Contract.id FROM ...
WHERE Version.id >= Contract.versionRangeFrom
ORDER BY versionRangeFrom
LIMIT 1 )