MySQL:如果使用 LIMIT 1 而不是 LIMIT 5，查询执行速度会大大降低

Question

我注意到如果我将查询限制为 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 ;

这些是 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 的速度测量解释:

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

有人可以向我解释一下这种行为吗？我提到这是与 LIMIT 1 不同的索引的结果。但是为什么 mysql 对不同的 LIMIT 值使用不同的键？

Answer 1

对于 LIMIT 1，我猜测查询分析器压缩主键并找到其 homematic_devices_id =30 的最后一条记录 - 大概是因为分析器知道“排序”操作会更昂贵.

当您限制 5 时，我猜查询分析器决定先查找记录，然后对它们进行排序。如果你想加快该操作，你可以像这样在 homematic_devices_id 和 ID 上创建一个索引: “Where”子句和 ID 列有助于排序