una - MySQL: ejecución de consultas dramáticamente más lenta si se usa LIMIT 1 en lugar de LIMIT 5

select*from table limit 5 10

Question

noté una disminución drástica de la velocidad si LIMITÓ una consulta a 1 en lugar de 5.

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

en lugar de

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

Mi tabla contiene aproximadamente 12,000,000 filas con la siguiente estructura:

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 ;

Estas son explicaciones de una medición de velocidad para 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

Estas son explicaciones de una medición de velocidad para el LÍMITE 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

¿Alguien puede explicarme este comportamiento por favor? Menciono que es el resultado del índice diferente que se usa con LIMIT 1. ¿Por qué mysql usa claves diferentes para diferentes valores de LIMIT?

Answer 1

Con LIMIT 1, supongo que el analizador de consultas comprime la clave primaria y encuentra el último registro cuyo homematic_devices_id =30 , presumiblemente porque el analizador sabe que la operación de "ordenar" será más costosa.

Cuando LIMITES 5, supongo que el analizador de consultas decide buscar los registros primero y luego ordenarlos. Si desea acelerar esa operación, puede crear un índice tanto en homematic_devices_id como en ID de la siguiente manera: ALTER TABLE homematic_events_test ADD INDEX ( homematic_devices_id, id ) - al colocar primero el ID del dispositivo ALTER TABLE homematic_events_test ADD INDEX ( homematic_devices_id, id ) cláusula "Where" y el La columna ID ayuda al SORT

Answer 2

Mi suposición es que cuando tiene un order by combinado con un limit 1 , la solicitud se trata internamente como un max() (o mínimo), que se puede alcanzar inmediatamente con el índice, mientras que cuando solicita un limit 5 , el el orden debe hacerse completamente primero.

Answer 3

Por alguna razón, de alguna manera es más rápido para MySQL usar la clave principal, ID , para acceder a estas filas, en lugar del índice. A pesar de que las consultas has utilizado específicamente el campo para el que se construyó el índice homematic_devices_id . También me parece extraño que MySQL en el segundo caso solo tenga homematic_devices_id en possible_keys , pero luego elige PRIMARY lugar. Por lo general, MySQL mostrará PRIMARY y otros posibles índices en esa columna.

¿Es posible que sea un problema dependiente de los datos? ¿Has probado tu consulta con otros device_ids?

Intente usar FORCE INDEX en ambos casos y vea si puede solucionar el problema.