MySQL: значительно медленнее выполнение запроса, если использовать LIMIT 1 вместо LIMIT 5

Я заметил резкое снижение скорости, если я ограничу запрос 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 ;

Это объяснение измерения скорости для предела 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

Это объяснение измерения скорости для предела 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
Может ли кто-нибудь объяснить мне это поведение, пожалуйста? Я упоминаю, что это результат другого индекса, который является UD с пределом 1. Но почему mysql использует разные ключи для разных предельных значений?
3 15
mysql performance indexing limit

3 ответа:

По какой-то причине для MySQL быстрее использовать первичный ключ ID, чтобы получить доступ к этим строкам, а не к индексу. Даже несмотря на то, что в запросах вы специально используете поле, для которого был построен индекс homematic_devices_id. Я также нахожу странным, что MySQL во втором случае имеет только homematic_devices_id под possible_keys, но затем выбирает PRIMARY вместо этого. Обычно MySQL показывает и PRIMARY, и другие возможные индексы в этом столбце.

Возможно ли, что это проблема, зависящая от данных? А вы пробовали ваш запрос с другими device_ids?

Попробуйте использовать FORCE INDEX в обоих случаях и посмотрите, сможете ли вы решить проблему.

3

С ограничением 1, я предполагаю, что анализатор запросов пролистывает первичный ключ и находит последнюю запись, чья homematic_devices_id =30 - предположительно потому, что анализатор знает, что операция "сортировка" будет более дорогой.

Когда вы ограничиваете 5, я предполагаю, что анализатор запросов решает сначала найти записи, а затем отсортировать их. Если вы хотите ускорить эту операцию, вы можете создать индекс для homematic_devices_id и ID следующим образом: ALTER TABLE homematic_events_test ADD INDEX ( homematic_devices_id, id ) - поместив сначала идентификатор устройства, вы размещаете "где" предложение, а столбец ID помогает сортировке

8

Мое предположение состоит в том, что когда вы имеете и order by в сочетании с limit 1, запрос внутренне обрабатывается как max() (или min), который может быть достигнут немедленно с индексом, тогда как когда вы просите limit 5, заказ должен быть выполнен полностью первым.

1