tool - MYSQL O vs rendimiento IN
query performance mysql (10)
Me pregunto si hay alguna diferencia en lo que respecta al rendimiento entre los siguientes
SELECT ... FROM ... WHERE someFIELD IN(1,2,3,4)
SELECT ... FROM ... WHERE someFIELD between 0 AND 5
SELECT ... FROM ... WHERE someFIELD = 1 OR someFIELD = 2 OR someFIELD = 3 ...
¿O MySQL optimizará el SQL de la misma manera que los compiladores optimizarán el código?
EDITAR: Cambió el AND a OR por el motivo indicado en los comentarios.
A continuación se detallan las 6 consultas con MySQL 5.6 @SQLFiddle
En resumen, las 6 consultas cubren columnas indizadas independientemente y se usaron 2 consultas por tipo de datos. Todas las consultas dieron como resultado el uso de un índice independientemente de IN () o de las OR que se usaban.
| ORs | IN()
integer | uses index | uses index
date | uses index | uses index
varchar | uses index | uses index
Realmente solo quería desacreditar las declaraciones hechas de que OR significa que no se puede usar ningún índice. Esto no es verdad Los índices se pueden usar en consultas usando OR ya que se muestran las 6 consultas en los ejemplos siguientes.
También me parece que muchos han ignorado el hecho de que IN () es un atajo de sintaxis para un conjunto de OR. A pequeña escala, las diferencias de rendimiento entre el uso de IN () -v- OR son extremadamente (infinitessinalmente) marginales.
Mientras que a mayor escala, IN () es ciertamente más conveniente, pero equivale a un conjunto de condiciones OR lógicamente. Cambio de circunstancia para cada consulta, por lo que siempre será mejor probar tu consulta en las tablas.
Resumen de los 6 planes de explicación, todos "Uso de la condición de índice" (desplazamiento hacia la derecha)
Query select_type table type possible_keys key key_len ref rows filtered Extra
------------- --------- ------- --------------- ----------- --------- ----- ------ ---------- -----------------------
Integers using OR SIMPLE mytable range aNum_idx aNum_idx 4 10 100.00 Using index condition
Integers using IN SIMPLE mytable range aNum_idx aNum_idx 4 10 100.00 Using index condition
Dates using OR SIMPLE mytable range aDate_idx aDate_idx 6 7 100.00 Using index condition
Dates using IN SIMPLE mytable range aDate_idx aDate_idx 6 7 100.00 Using index condition
Varchar using OR SIMPLE mytable range aName_idx aName_idx 768 10 100.00 Using index condition
Varchar using IN SIMPLE mytable range aName_idx aName_idx 768 10 100.00 Using index condition
Configuración de esquema de MySQL 5.6 :
CREATE TABLE `myTable` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`aName` varchar(255) default NULL,
`aDate` datetime,
`aNum` mediumint(8),
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;
ALTER TABLE `myTable` ADD INDEX `aName_idx` (`aName`);
ALTER TABLE `myTable` ADD INDEX `aDate_idx` (`aDate`);
ALTER TABLE `myTable` ADD INDEX `aNum_idx` (`aNum`);
INSERT INTO `myTable` (`aName`,`aDate`)
VALUES
("Daniel","2017-09-19 01:22:31")
,("Quentin","2017-06-03 01:06:45")
,("Chester","2017-06-14 17:49:36")
,("Lev","2017-08-30 06:27:59")
,("Garrett","2018-10-04 02:40:37")
,("Lane","2017-01-22 17:11:21")
,("Chaim","2017-09-20 11:13:46")
,("Kieran","2018-03-10 18:37:26")
,("Cedric","2017-05-20 16:25:10")
,("Conan","2018-07-10 06:29:39")
,("Rudyard","2017-07-14 00:04:00")
,("Chadwick","2018-08-18 08:54:08")
,("Darius","2018-10-02 06:55:56")
,("Joseph","2017-06-19 13:20:33")
,("Wayne","2017-04-02 23:20:25")
,("Hall","2017-10-13 00:17:24")
,("Craig","2016-12-04 08:15:22")
,("Keane","2018-03-12 04:21:46")
,("Russell","2017-07-14 17:21:58")
,("Seth","2018-07-25 05:51:30")
,("Cole","2018-06-09 15:32:53")
,("Donovan","2017-08-12 05:21:35")
,("Damon","2017-06-27 03:44:19")
,("Brian","2017-02-01 23:35:20")
,("Harper","2017-08-25 04:29:27")
,("Chandler","2017-09-30 23:54:06")
,("Edward","2018-07-30 12:18:07")
,("Curran","2018-05-23 09:31:53")
,("Uriel","2017-05-08 03:31:43")
,("Honorato","2018-04-07 14:57:53")
,("Griffin","2017-01-07 23:35:31")
,("Hasad","2017-05-15 05:32:41")
,("Burke","2017-07-04 01:11:19")
,("Hyatt","2017-03-14 17:12:28")
,("Brenden","2017-10-17 05:16:14")
,("Ryan","2018-10-10 08:07:55")
,("Giacomo","2018-10-06 14:21:21")
,("James","2018-02-06 02:45:59")
,("Colt","2017-10-10 08:11:26")
,("Kermit","2017-09-18 16:57:16")
,("Drake","2018-05-20 22:08:36")
,("Berk","2017-04-16 17:39:32")
,("Alan","2018-09-01 05:33:05")
,("Deacon","2017-04-20 07:03:05")
,("Omar","2018-03-02 15:04:32")
,("Thaddeus","2017-09-19 04:07:54")
,("Troy","2016-12-13 04:24:08")
,("Rogan","2017-11-02 00:03:25")
,("Grant","2017-08-21 01:45:16")
,("Walker","2016-11-26 15:54:52")
,("Clarke","2017-07-20 02:26:56")
,("Clayton","2018-08-16 05:09:29")
,("Denton","2018-08-11 05:26:05")
,("Nicholas","2018-07-19 09:29:55")
,("Hashim","2018-08-10 20:38:06")
,("Todd","2016-10-25 01:01:36")
,("Xenos","2017-05-11 22:50:35")
,("Bert","2017-06-17 18:08:21")
,("Oleg","2018-01-03 13:10:32")
,("Hall","2018-06-04 01:53:45")
,("Evan","2017-01-16 01:04:25")
,("Mohammad","2016-11-18 05:42:52")
,("Armand","2016-12-18 06:57:57")
,("Kaseem","2018-06-12 23:09:57")
,("Colin","2017-06-29 05:25:52")
,("Arthur","2016-12-29 04:38:13")
,("Xander","2016-11-14 19:35:32")
,("Dante","2016-12-01 09:01:04")
,("Zahir","2018-02-17 14:44:53")
,("Raymond","2017-03-09 05:33:06")
,("Giacomo","2017-04-17 06:12:52")
,("Fulton","2017-06-04 00:41:57")
,("Chase","2018-01-14 03:03:57")
,("William","2017-05-08 09:44:59")
,("Fuller","2017-03-31 20:35:20")
,("Jarrod","2017-02-15 02:45:29")
,("Nissim","2018-03-11 14:19:25")
,("Chester","2017-11-05 00:14:27")
,("Perry","2017-12-24 11:58:04")
,("Theodore","2017-06-26 12:34:12")
,("Mason","2017-10-02 03:53:49")
,("Brenden","2018-10-08 10:09:47")
,("Jerome","2017-11-05 20:34:25")
,("Keaton","2018-08-18 00:55:56")
,("Tiger","2017-05-21 16:59:07")
,("Benjamin","2018-04-10 14:46:36")
,("John","2018-09-05 18:53:03")
,("Jakeem","2018-10-11 00:17:38")
,("Kenyon","2017-12-18 22:19:29")
,("Ferris","2017-03-29 06:59:13")
,("Hoyt","2017-01-03 03:48:56")
,("Fitzgerald","2017-07-27 11:27:52")
,("Forrest","2017-10-05 23:14:21")
,("Jordan","2017-01-11 03:48:09")
,("Lev","2017-05-25 08:03:39")
,("Chase","2017-06-18 19:09:23")
,("Ryder","2016-12-13 12:50:50")
,("Malik","2017-11-19 15:15:55")
,("Zeph","2018-04-04 11:22:12")
,("Amala","2017-01-29 07:52:17")
;
.
update MyTable
set aNum = id
;
Consulta 1 :
select ''aNum by OR'' q, mytable.*
from mytable
where aNum = 12
OR aNum = 22
OR aNum = 27
OR aNum = 32
OR aNum = 42
OR aNum = 52
OR aNum = 62
OR aNum = 65
OR aNum = 72
OR aNum = 82
Results :
| q | id | aName | aDate | aNum |
|------------|----|----------|----------------------|------|
| aNum by OR | 12 | Chadwick | 2018-08-18T08:54:08Z | 12 |
| aNum by OR | 22 | Donovan | 2017-08-12T05:21:35Z | 22 |
| aNum by OR | 27 | Edward | 2018-07-30T12:18:07Z | 27 |
| aNum by OR | 32 | Hasad | 2017-05-15T05:32:41Z | 32 |
| aNum by OR | 42 | Berk | 2017-04-16T17:39:32Z | 42 |
| aNum by OR | 52 | Clayton | 2018-08-16T05:09:29Z | 52 |
| aNum by OR | 62 | Mohammad | 2016-11-18T05:42:52Z | 62 |
| aNum by OR | 65 | Colin | 2017-06-29T05:25:52Z | 65 |
| aNum by OR | 72 | Fulton | 2017-06-04T00:41:57Z | 72 |
| aNum by OR | 82 | Brenden | 2018-10-08T10:09:47Z | 82 |
Consulta 2 :
select ''aNum by IN'' q, mytable.*
from mytable
where aNum IN (
12
, 22
, 27
, 32
, 42
, 52
, 62
, 65
, 72
, 82
)
Results :
| q | id | aName | aDate | aNum |
|------------|----|----------|----------------------|------|
| aNum by IN | 12 | Chadwick | 2018-08-18T08:54:08Z | 12 |
| aNum by IN | 22 | Donovan | 2017-08-12T05:21:35Z | 22 |
| aNum by IN | 27 | Edward | 2018-07-30T12:18:07Z | 27 |
| aNum by IN | 32 | Hasad | 2017-05-15T05:32:41Z | 32 |
| aNum by IN | 42 | Berk | 2017-04-16T17:39:32Z | 42 |
| aNum by IN | 52 | Clayton | 2018-08-16T05:09:29Z | 52 |
| aNum by IN | 62 | Mohammad | 2016-11-18T05:42:52Z | 62 |
| aNum by IN | 65 | Colin | 2017-06-29T05:25:52Z | 65 |
| aNum by IN | 72 | Fulton | 2017-06-04T00:41:57Z | 72 |
| aNum by IN | 82 | Brenden | 2018-10-08T10:09:47Z | 82 |
Consulta 3 :
select ''adate by OR'' q, mytable.*
from mytable
where aDate= str_to_date("2017-02-15 02:45:29",''%Y-%m-%d %h:%i:%s'')
OR aDate = str_to_date("2018-03-10 18:37:26",''%Y-%m-%d %h:%i:%s'')
OR aDate = str_to_date("2017-05-20 16:25:10",''%Y-%m-%d %h:%i:%s'')
OR aDate = str_to_date("2018-07-10 06:29:39",''%Y-%m-%d %h:%i:%s'')
OR aDate = str_to_date("2017-07-14 00:04:00",''%Y-%m-%d %h:%i:%s'')
OR aDate = str_to_date("2018-08-18 08:54:08",''%Y-%m-%d %h:%i:%s'')
OR aDate = str_to_date("2018-10-02 06:55:56",''%Y-%m-%d %h:%i:%s'')
OR aDate = str_to_date("2017-04-20 07:03:05",''%Y-%m-%d %h:%i:%s'')
OR aDate = str_to_date("2018-03-02 15:04:32",''%Y-%m-%d %h:%i:%s'')
OR aDate = str_to_date("2017-09-19 04:07:54",''%Y-%m-%d %h:%i:%s'')
OR aDate = str_to_date("2016-12-13 04:24:08",''%Y-%m-%d %h:%i:%s'')
Results :
| q | id | aName | aDate | aNum |
|-------------|----|----------|----------------------|------|
| adate by OR | 47 | Troy | 2016-12-13T04:24:08Z | 47 |
| adate by OR | 76 | Jarrod | 2017-02-15T02:45:29Z | 76 |
| adate by OR | 44 | Deacon | 2017-04-20T07:03:05Z | 44 |
| adate by OR | 46 | Thaddeus | 2017-09-19T04:07:54Z | 46 |
| adate by OR | 10 | Conan | 2018-07-10T06:29:39Z | 10 |
| adate by OR | 12 | Chadwick | 2018-08-18T08:54:08Z | 12 |
| adate by OR | 13 | Darius | 2018-10-02T06:55:56Z | 13 |
Pregunta 4 :
select ''adate by IN'' q, mytable.*
from mytable
where aDate IN (
str_to_date("2017-02-15 02:45:29",''%Y-%m-%d %h:%i:%s'')
, str_to_date("2018-03-10 18:37:26",''%Y-%m-%d %h:%i:%s'')
, str_to_date("2017-05-20 16:25:10",''%Y-%m-%d %h:%i:%s'')
, str_to_date("2018-07-10 06:29:39",''%Y-%m-%d %h:%i:%s'')
, str_to_date("2017-07-14 00:04:00",''%Y-%m-%d %h:%i:%s'')
, str_to_date("2018-08-18 08:54:08",''%Y-%m-%d %h:%i:%s'')
, str_to_date("2018-10-02 06:55:56",''%Y-%m-%d %h:%i:%s'')
, str_to_date("2017-04-20 07:03:05",''%Y-%m-%d %h:%i:%s'')
, str_to_date("2018-03-02 15:04:32",''%Y-%m-%d %h:%i:%s'')
, str_to_date("2017-09-19 04:07:54",''%Y-%m-%d %h:%i:%s'')
, str_to_date("2016-12-13 04:24:08",''%Y-%m-%d %h:%i:%s'')
)
Results :
| q | id | aName | aDate | aNum |
|-------------|----|----------|----------------------|------|
| adate by IN | 47 | Troy | 2016-12-13T04:24:08Z | 47 |
| adate by IN | 76 | Jarrod | 2017-02-15T02:45:29Z | 76 |
| adate by IN | 44 | Deacon | 2017-04-20T07:03:05Z | 44 |
| adate by IN | 46 | Thaddeus | 2017-09-19T04:07:54Z | 46 |
| adate by IN | 10 | Conan | 2018-07-10T06:29:39Z | 10 |
| adate by IN | 12 | Chadwick | 2018-08-18T08:54:08Z | 12 |
| adate by IN | 13 | Darius | 2018-10-02T06:55:56Z | 13 |
Consulta 5 :
select ''name by OR'' q, mytable.*
from mytable
where aname = ''Alan''
OR aname = ''Brian''
OR aname = ''Chandler''
OR aname = ''Darius''
OR aname = ''Evan''
OR aname = ''Ferris''
OR aname = ''Giacomo''
OR aname = ''Hall''
OR aname = ''James''
OR aname = ''Jarrod''
Results :
| q | id | aName | aDate | aNum |
|-------------|----|----------|----------------------|------|
| name by OR | 43 | Alan | 2018-09-01T05:33:05Z | 43 |
| name by OR | 24 | Brian | 2017-02-01T23:35:20Z | 24 |
| name by OR | 26 | Chandler | 2017-09-30T23:54:06Z | 26 |
| name by OR | 13 | Darius | 2018-10-02T06:55:56Z | 13 |
| name by OR | 61 | Evan | 2017-01-16T01:04:25Z | 61 |
| name by OR | 90 | Ferris | 2017-03-29T06:59:13Z | 90 |
| name by OR | 37 | Giacomo | 2018-10-06T14:21:21Z | 37 |
| name by OR | 71 | Giacomo | 2017-04-17T06:12:52Z | 71 |
| name by OR | 16 | Hall | 2017-10-13T00:17:24Z | 16 |
| name by OR | 60 | Hall | 2018-06-04T01:53:45Z | 60 |
| name by OR | 38 | James | 2018-02-06T02:45:59Z | 38 |
| name by OR | 76 | Jarrod | 2017-02-15T02:45:29Z | 76 |
Consulta 6 :
select ''name by IN'' q, mytable.*
from mytable
where aname IN (
''Alan''
,''Brian''
,''Chandler''
, ''Darius''
, ''Evan''
, ''Ferris''
, ''Giacomo''
, ''Hall''
, ''James''
, ''Jarrod''
)
Results :
| q | id | aName | aDate | aNum |
|------------|----|----------|----------------------|------|
| name by IN | 43 | Alan | 2018-09-01T05:33:05Z | 43 |
| name by IN | 24 | Brian | 2017-02-01T23:35:20Z | 24 |
| name by IN | 26 | Chandler | 2017-09-30T23:54:06Z | 26 |
| name by IN | 13 | Darius | 2018-10-02T06:55:56Z | 13 |
| name by IN | 61 | Evan | 2017-01-16T01:04:25Z | 61 |
| name by IN | 90 | Ferris | 2017-03-29T06:59:13Z | 90 |
| name by IN | 37 | Giacomo | 2018-10-06T14:21:21Z | 37 |
| name by IN | 71 | Giacomo | 2017-04-17T06:12:52Z | 71 |
| name by IN | 16 | Hall | 2017-10-13T00:17:24Z | 16 |
| name by IN | 60 | Hall | 2018-06-04T01:53:45Z | 60 |
| name by IN | 38 | James | 2018-02-06T02:45:59Z | 38 |
| name by IN | 76 | Jarrod | 2017-02-15T02:45:29Z | 76 |
Apuesto a que son lo mismo, puedes ejecutar una prueba haciendo lo siguiente:
recorra el "en (1,2,3,4)" 500 veces y vea cuánto tiempo lleva. bucle sobre la versión "= 1 o = 2 o = 3 ..." 500 veces y viendo cuánto tiempo se ejecuta.
también podrías probar una forma de unión, si algún campo es un índice y tu tabla es grande podría ser más rápido ...
SELECT ...
FROM ...
INNER JOIN (SELECT 1 as newField UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) dt ON someFIELD =newField
Intenté el método de unión anterior en mi servidor SQL y es casi el mismo que en (1,2,3,4), y ambos dan como resultado una búsqueda de índice agrupado. No estoy seguro de cómo MySQL los manejará.
Creo que el BETWEEN será más rápido ya que se debe convertir en:
Field >= 0 AND Field <= 5
Tengo entendido que un IN se convertirá en un grupo de declaraciones OR de todos modos. El valor de IN es la facilidad de uso. (Ahorrando al tener que escribir el nombre de cada columna varias veces y también facilita su uso con la lógica existente - no tiene que preocuparse por Y / O la precedencia porque el IN es una declaración. Con un montón de instrucciones OR, tiene para asegurarse de rodearlos con paréntesis para asegurarse de que se evalúen como una condición).
La única respuesta real a su pregunta es PROFILE YOUR QUERIES . Entonces sabrá qué funciona mejor en su situación particular.
Creo que una explicación para la observación de sunseeker es que MySQL ordena los valores en la declaración IN si son todos valores estáticos y usa la búsqueda binaria, que es más eficiente que la alternativa O simple. No recuerdo dónde lo leí, pero el resultado de sunseeker parece ser una prueba.
Depende de lo que está haciendo; qué tan amplio es el rango, cuál es el tipo de datos (sé que su ejemplo utiliza un tipo de datos numéricos, pero su pregunta también puede aplicarse a muchos tipos de datos diferentes).
Esta es una instancia en la que desea escribir la consulta en ambos sentidos; hazlo funcionar y luego usa EXPLAIN para descubrir las diferencias de ejecución.
Estoy seguro de que hay una respuesta concreta a esto, pero así es como, prácticamente, encontraría la respuesta para mi pregunta.
Esto podría ser de alguna ayuda: http://forge.mysql.com/wiki/Top10SQLPerformanceTips
Saludos,
Franco
Necesitaba saber esto con certeza, así que comparé ambos métodos. Constantemente encontré que IN es mucho más rápido que usar OR .
No le creas a las personas que dan su "opinión", la ciencia se trata de pruebas y evidencia.
Ejecuté un bucle de 1000x las consultas equivalentes (por coherencia, utilicé sql_no_cache ):
IN : 2.34969592094s
OR : 5.83781504631s
Actualizar:
(No tengo el código fuente para la prueba original, como lo fue hace 6 años, aunque devuelve un resultado en el mismo rango que esta prueba)
En solicitud de algún código de muestra para probar esto, este es el caso de uso más simple posible. Usando Eloquent para la simplicidad de la sintaxis, el equivalente de SQL sin formato ejecuta el mismo.
$t = microtime(true);
for($i=0; $i<10000; $i++):
$q = DB::table(''users'')->where(''id'',1)
->orWhere(''id'',2)
->orWhere(''id'',3)
->orWhere(''id'',4)
->orWhere(''id'',5)
->orWhere(''id'',6)
->orWhere(''id'',7)
->orWhere(''id'',8)
->orWhere(''id'',9)
->orWhere(''id'',10)
->orWhere(''id'',11)
->orWhere(''id'',12)
->orWhere(''id'',13)
->orWhere(''id'',14)
->orWhere(''id'',15)
->orWhere(''id'',16)
->orWhere(''id'',17)
->orWhere(''id'',18)
->orWhere(''id'',19)
->orWhere(''id'',20)->get();
endfor;
$t2 = microtime(true);
echo $t."/n".$t2."/n".($t2-$t)."/n";
1482080514.3635
1482080517.3713
3.0078368186951
$t = microtime(true);
for($i=0; $i<10000; $i++):
$q = DB::table(''users'')->whereIn(''id'',[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20])->get();
endfor;
$t2 = microtime(true);
echo $t."/n".$t2."/n".($t2-$t)."/n";
1482080534.0185
1482080536.178
2.1595389842987
O será más lento. Si IN o BETWEEN es más rápido dependerá de sus datos, pero esperaría ENTRE SER más rápido normalmente ya que puede tomar un rango de un índice (suponiendo que algún campo esté indexado).
Por lo que entiendo acerca de la forma en que el compilador optimiza este tipo de consultas, usar la cláusula IN es más eficiente que múltiples cláusulas OR. Si tiene valores en los que se puede usar la cláusula BETWEEN, eso es aún más eficiente.
Sé que, siempre que tengas un índice en el campo, BETWEEN lo usará para encontrar rápidamente un extremo y luego pasar al otro. Esto es más eficiente.
Cada EXPLAIN que he visto muestra "EN (...)" y "... O ..." para que sean intercambiables e igualmente (en) eficientes. Lo que esperaría, ya que el optimizador no tiene forma de saber si comprenden o no un intervalo. También es equivalente a UNION ALL SELECT en los valores individuales.
También hice una prueba para futuros Googlers. La cuenta total de resultados devueltos es 7264 de 10000
SELECT * FROM item WHERE id = 1 OR id = 2 ... id = 10000
Esta consulta tomó 0.1239 segundos
SELECT * FROM item WHERE id IN (1,2,3,...10000)
Esta consulta tardó 0.0433 segundos
IN es 3 veces más rápido que OR