MySQL的：如果慢得多的查询执行使用LIMIT 1而不是LIMIT 5

Question

我注意到一个显着降低的速度如果I LIMIT查询以1代替5.

SELECT he. * 
FROM homematic_events he 
WHERE he.homematic_devices_id =30 
ORDER BY id DESC 
LIMIT 1 

代替

SELECT he. * 
FROM homematic_events he 
WHERE he.homematic_devices_id =30 
ORDER BY id DESC 
LIMIT 5 

我的表格包含大约12,000,000行，结构如下：

CREATE TABLE IF NOT EXISTS `homematic_events` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `homematic_devices_id` int(11) DEFAULT NULL, 
    `address` char(16) COLLATE utf8_unicode_ci NOT NULL, 
    `interface_id` char(16) COLLATE utf8_unicode_ci NOT NULL, 
    `key` char(32) COLLATE utf8_unicode_ci NOT NULL, 
    `value` float(12,2) NOT NULL, 
    `timestamp` datetime NOT NULL, 
    PRIMARY KEY (`id`), 
    KEY `timestamp` (`timestamp`), 
    KEY `address` (`address`), 
    KEY `key` (`key`), 
    KEY `homematic_devices_id` (`homematic_devices_id`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=12637557 ; 

这些是解释加速我asurment为LIMIT 5：

mysql> EXPLAIN SELECT he. * FROM homematic_events he WHERE he.homematic_devices_id =30 ORDER BY id DESC LIMIT 5; 
    +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------------+ 
    | id | select_type | table | type | possible_keys  | key     | key_len | ref | rows | Extra      | 
    +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------------+ 
    | 1 | SIMPLE  | he | ref | homematic_devices_id | homematic_devices_id | 5  | const | 4171 | Using where; Using filesort | 
    +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-----------------------------+ 


starting       0.000010 
checking query cache for query 0.000030 
Opening tables     0.000007 
System lock      0.000004 
Table lock      0.000015 
init        0.000019 
optimizing      0.000007 
statistics      0.000098 
preparing       0.000012 
executing       0.000002 
Sorting result     0.022965 
Sending data      0.000047 
end        0.000004 
query end       0.000002 
freeing items      0.000302 
storing result in query cache  0.000009 
logging slow query    0.000002 
cleaning up      0.000003 

这些交代了LIMIT 1的速度measurment：

mysql> EXPLAIN SELECT he. * FROM homematic_events he WHERE he.homematic_devices_id =30 ORDER BY id DESC LIMIT 1; 
+----+-------------+-------+-------+----------------------+---------+---------+------+------+-------------+ 
| id | select_type | table | type | possible_keys  | key  | key_len | ref | rows | Extra  | 
+----+-------------+-------+-------+----------------------+---------+---------+------+------+-------------+ 
| 1 | SIMPLE  | he | index | homematic_devices_id | PRIMARY | 4  | NULL | 3029 | Using where | 
+----+-------------+-------+-------+----------------------+---------+---------+------+------+-------------+ 

starting        0.000010 
checking query cache for query  0.000034 
Opening tables      0.000009 
System lock       0.000004 
Table lock       0.000015 
init         0.000020 
optimizing       0.000008 
statistics       0.000069 
preparing        0.000016 
executing        0.000002 
Sorting result      0.000005 
Sending data      502.290180 
end         0.000010 
query end        0.000003 
freeing items       0.000293 
logging slow query     0.000004 
logging slow query     0.000002 
cleaning up       0.000003 

任何人都可以解释这种现象给我好吗？我提到它是不同索引的结果，它与极限1相同。但为什么mysql对不同的LIMIT值使用不同的键？

Answer 1

由于某种原因，MySQL以某种方式更快地使用主键ID来访问这些行，而不是索引。即使您有特别使用homematic_devices_id索引的字段的查询。我还发现奇怪的是，在第二种情况下，MySQL只有homematic_devices_id在possible_keys下，但随后选择了PRIMARY。通常，MySQL将在该列中显示PRIMARY和其他可能的索引。

它可能是一个数据相关的问题？您是否尝试过使用其他device_ids查询？

尝试在两种情况下都使用FORCE INDEX，并查看是否可以解决该问题。

Answer 2

我的假设是，当你有和order by与limit 1相结合，要求在内部为max()（或分），可立即与指数，而当你问一个limit 5，订货到达，处理必须首先完成。

Answer 3

利用极限1，我猜查询分析器拉链下来的主键，发现最后一个记录，其homematic_devices_id =30 - 大概是因为分析器知道“之类的”操作会更贵。

当你限制5，我猜测查询分析器决定先找到记录，然后对它们进行排序。如果你想加快这个操作，你可以像这样在homematic_devices_id和ID上创建索引：ALTER TABLE homematic_events_test ADD INDEX ( homematic_devices_id, id) - 通过首先放置设备ID，容纳“Where”子句，并且ID列帮助排序