MySQL - 为什么这两个查询中时间戳的性能如此不同？

Question

我写了一个 Perl 脚本，它在一个超过 140000 行并正在扩展的表中进行一些 SQL 查询。

我想比较日期并获取一些行，但我意识到仅通过更改一个 SQL 查询，我就会获得如此不同的执行速度。

查看以下执行 100 个 $sql 查询的测试结果。我在不同执行之间更改脚本的唯一行是 $sql 行。

我跑了很多次测试，我总是得到类似的结果，所以我猜它与缓存问题无关。

my $sql = "SELECT `mem_used`, `swap_used`, `mem_total` 
FROM `$config{db}{data_table}` 
WHERE  `host_id` = $host_id 
AND date >= '$date' 
AND TIMESTAMPDIFF( MINUTE , `date`, '$date' ) <= $interval;"; # VERY SLOW

time ./data_smoothing.pl

real    1m28.818s
user    1m6.516s
sys     0m0.256s

---

my $sql = "SELECT `mem_used`, `swap_used`, `mem_total` 
FROM `$config{db}{data_table}` 
WHERE  `host_id` = $host_id 
AND date >= '$date' 
AND (UNIX_TIMESTAMP(`date`) - UNIX_TIMESTAMP('$date')) <= ($interval * 60);"; #SLOW

$ time ./data_smoothing.pl

real    0m10.005s
user    0m0.108s
sys     0m0.028s

---

my $sql = "SELECT `mem_used`, `swap_used`, `mem_total` 
FROM `$config{db}{data_table}` 
WHERE  `host_id` = $host_id 
AND (`date` BETWEEN '$date' 
AND DATE_ADD('$date', INTERVAL $interval MINUTE));"; #FAST

$ time ./data_smoothing.pl

real    0m0.190s
user    0m0.084s
sys     0m0.016s

---

表是如何创建的(取自 mysqldump)

CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `host_id` smallint(6) NOT NULL,
  `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `mem_total` double(10,3) DEFAULT NULL,
  `mem_used` double(10,3) DEFAULT NULL,
  `swap_total` double(10,3) DEFAULT NULL,
  `swap_used` double(10,3) DEFAULT NULL,
  `CPU_count` smallint(6) DEFAULT NULL,
  `load_avg_1` float DEFAULT NULL,
  `load_avg_5` float DEFAULT NULL,
  `load_avg_15` float DEFAULT NULL,
  `uptime` double(10,3) DEFAULT NULL,
  `cpuIdlingTime` double(10,3) DEFAULT NULL,
  `rxBytesTotal` bigint(20) DEFAULT NULL,
  `txBytesTotal` bigint(20) DEFAULT NULL,
  `rxPacketsTotal` bigint(20) DEFAULT NULL,
  `txPacketsTotal` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`,`host_id`),
  KEY `fk_data_hosts` (`host_id`),
  KEY `date_memtot_hosts` (`date`,`mem_total`,`host_id`),
  CONSTRAINT `fk_data_hosts` FOREIGN KEY (`host_id`) REFERENCES `hosts` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=145300 DEFAULT CHARSET=utf8;

Answer 1

最后一个是最快的，因为您的比较非常适合索引。其他的，没那么多。

看，当您在测试之前使用列的值调用函数(或执行任何其他操作)时，几乎不可能使用索引来快速找到匹配的行。引擎基本上必须遍历整个表，抓取一个日期，用它做一些数学运算，然后然后检查条件是否为真。

与此同时，如果您只是说 BETWEEN this_value AND that_value，MySQL 根本不需要做太多——它可以查询索引并找到范围的两个端点，即快得多。

调用 DATE_ADD('$date', INTERVAL $interval MINUTE) 对运行时间没有太大影响，因为 MySQL 通常足够聪明，可以缓存它知道不会的值更改，这样就不必每次都重新计算它们。

至于前两者不同的原因，我不能告诉你。也许 TIMESTAMPDIFF 就是那么慢。也许转换和数学时间戳更简单，特别是考虑到 UNIX_TIMESTAMP('$date') 不需要每次都重新计算。但这一切都只是猜测。