MySQL 表查询异常缓慢(使用 Matlab 的 Connector/J)

Question

编辑:感谢大家的评论。我已经尝试了你的大部分建议，但没有帮助。我需要补充一点，我正在使用 Connector/J 5.1.26 通过 Matlab 运行此查询(抱歉之前没有提及)。最后，我认为这是执行时间增加的原因，因为当我“直接”运行查询时需要 0.2 秒。然而，我从来没有遇到过使用 Connector/J 时如此巨大的性能损失。鉴于这些新信息，您有什么建议吗？我很抱歉没有早点披露这一点，但我再次重申，我从未体验过 Connector/J 对性能的影响。

---

我在 mySQL 中有下表(CREATE 代码取自 HeidiSQL):

CREATE TABLE `data` (
    `PRIMARY` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ID` VARCHAR(5) NULL DEFAULT NULL,
    `DATE` DATE NULL DEFAULT NULL,
    `PRICE` DECIMAL(14,4) NULL DEFAULT NULL,
    `QUANT` INT(10) NULL DEFAULT NULL,
    `TIME` TIME NULL DEFAULT NULL,
    INDEX `DATE` (`DATE`),
    INDEX `ID` (`SYMBOL`),
    INDEX `PRICE` (`PRICE`),
    INDEX `QUANT` (`SIZE`),
    INDEX `TIME` (`TIME`),
    PRIMARY KEY (`PRIMARY`)
)

它填充了大约 360,000 行数据。

执行以下查询需要 10 多秒:

Select ID, DATE, PRICE, QUANT, TIME FROM database.data WHERE DATE 
>= "2007-01-01" AND DATE <= "2010-12-31" ORDER BY ID, DATE, TIME ASC; 

我还有其他包含数百万行的表，其中类似的查询只需几分之一秒。我不知道是什么原因导致这个速度如此之慢。有什么想法/提示吗？

解释:

id = 1
select_type = SIMPLE
table = data
type = ALL
possible_keys = DATE
key = (NULL)
key_len = (NULL)
ref = (NULL)
rows = 361161
Extra = Using where; Using filesort

Answer 1

您要求的数据范围很广。时间可能花在对结果进行排序上。

在更小的日期范围内查询是否更快？例如，

WHERE DATE >= '2007-01-01' AND DATE < '2007-02-01'

一种可能是优化器可能正在使用 id 上的索引进行排序，并进行全表扫描以过滤掉日期范围。使用索引进行排序通常不是最优的。您可以将查询尝试为:

select t.*
from (Select ID, DATE, PRICE, QUANT, TIME
      FROM database.data
      WHERE DATE >= "2007-01-01" AND DATE <= "2010-12-31"
     ) t
ORDER BY ID, DATE, TIME ASC; 

我认为这将强制优化器使用 date 索引进行选择，然后使用文件排序进行排序——但是有派生表的成本。如果您没有大型结果集，这可能会显着提高性能。