mysql - 使用 "WHERE UNIX_TIMESTAMP(date)"的 SQL 查询的性能

Question

我正在运行一个 MySQL 服务器 (5.5)，它有一个大表(上面有大约 1000 万条记录)。该表是某种日志，在 2 列上有一个主键:

id <- integer,
date <- datetime

连接到此数据库的应用程序正在发送一个查询，内容如下:

SELECT * FROM bigtable  
INNER JOIN other_table
ON ....
WHERE UNIX_TIMESTAMP(date) BETWEEN #somevalue# AND #somevalue2#;

我发现执行此查询需要很长时间。我知道有些函数可以阻止 MySQL 使用索引并使其执行全表扫描。

问题:如图所示，在主键的列上使用函数 UNIX_TIMESTAMP 而不是 "... WHERE date BETWEEN '2012:01:01 00:00:00' AND '2012: 是否会影响性能？ 02:01 00:00:00' " ?

查询:

SELECT r.f_registro, r.latitud, r.longitud, r.velocidad, r.status, r.odometro, r.heading, r.sensor, a.nombre FROM registros r INNER JOIN activos a ON a.id_tracker = r.id_tracker WHERE a.id_activo = 2366 AND r.satelites > '3' AND UNIX_TIMESTAMP(r.f_registro) BETWEEN 1342159200 AND 1342760400 ORDER BY r.f_registro

执行需要几秒甚至几分钟!运行解释返回:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,a,const,PRIMARY,PRIMARY,4,const,1,"Using filesort"
1,SIMPLE,r,range,"id_tracker,satelites",satelites,4,NULL,1,"Using index condition; Using where"

Answer 1

您是正确的，在日期列上使用函数会阻止 MySQL 使用该列上的索引。

相反，将范围计算为两个日期常量并使用 BETWEEN。

另外请注意，您没有指明日期列上有索引。索引是最左边的前缀，所以以 id 开头的复合索引不能用于只要求日期的查询。

查询的 ON 部分(您已排除)可能对查询的性能同样重要，您应该评估它是否也能够使用索引。