>规格:
- MySQL 版本: 5.6.19 (Ubuntu(
- 还尝试了MariaDB,并遇到了同样的问题
桌子:
CREATE TABLE `x` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`a` INT(10) UNSIGNED NOT NULL,
`time` DECIMAL(16,6) NOT NULL,
PRIMARY KEY (`id`),
INDEX `a` (`a`),
INDEX `time` (`time`),
INDEX `time_a` (`time`, `a`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=298846
;
查询:
SELECT COUNT(DISTINCT `a`) c
FROM `x`
WHERE `time` >= (UNIX_TIMESTAMP()- (60 * 24));
如果在给定范围内有很多行带有time
,则此查询非常慢。另请注意,虽然可能有很多匹配的行(数千或数万或更多(,但DISTINCT
a
的数量总是相当小(几百(。
查询速度快(基本上是即时的(,无论表的大小如何,在以下情况下:
- 只有几行在给定范围内或何时
time
- 没有
WHERE
部分(因为索引在a
(
这让我认为在计数时无法在a
上使用索引,即使EXPLAIN
在possibly_keys
中提到了所有三个索引。
即使出现以下情况,问题仍然存在:
-
time
的类型为BIGINT
或DATETIME
(对查询进行了相应的更改( -
ENGINE=MyISAM
有什么建议吗?
SELECT COUNT(DISTINCT `a`)
FROM `x`;
将跨越INDEX(a)
. 请参阅EXPLAIN FORMAT=JSON SELECT ...
并查找"using_index_for_group_by": true
。 当只有少量不同的a
值时,这使得它非常快。
我怀疑使用 WHERE
子句会说"using_index_for_group_by": "scanning"
,这意味着它效率较低。 我怀疑实现者做了单键案例,但没有做多键案例。
这是整个表定义吗? 我看到AUTO_INCREMENT
没有任何索引。 怎么了? 关于MyISAM和InnoDB之间与此讨论相关的唯一区别是PRIMARY KEY
的处理。
time
的数据类型可能并不重要。
如果我没有满足你的"任何建议?"问题,请改写这个问题。
尝试使用索引提示来强制查询使用您希望它使用的索引。
SELECT COUNT(DISTINCT `a`) c
FROM `x` FORCE INDEX (the_index_you_want_to_use)
WHERE `time` >= (UNIX_TIMESTAMP()- (60 * 24));
最好不要在这样的 where 子句中进行任何计算。
var unixtime = UNIX_TIMESTAMP()- (60 * 24)
SELECT COUNT(DISTINCT `a`) c
FROM `x` FORCE INDEX (the_index_you_want_to_use)
WHERE `time` >= unixtime
如果我不得不猜测,问题是类型。 UNIX_TIMESTAMP()
返回一个无符号整数。 您的time
变量是 decimal
。 这些不是一回事。 而且,类型不匹配可能会使优化器感到困惑。
听起来表很大,因此更改类型是不可行的(但是,如果可以的话,您可能希望通过选择具有正确类型的新表来测试这一点(。
以下内容可能会有所帮助:
WHERE `time` >= cast(UNIX_TIMESTAMP() - (60 * 24) as unsigned);
您还可以声明一个局部无符号变量并将"常量"存储在变量中,以查看是否可以解决性能问题。
最后,如果未使用time, a
上的索引,请尝试以下查询变体:
SELECT COUNT(*) as c
FROM (SELECT DISTINCT a
FROM `x`
WHERE `time` >= CAST(unixtime - 24 * 60 as unsigned)
) ax
我已经看到这种重组提高了其他数据库的性能,尽管在 MySQL 上没有。