Pregunta ¿Cuál es el problema de consulta N + 1 SELECT?


SELECCIONAR N + 1 generalmente se establece como un problema en las discusiones de mapeo relacional de objetos (ORM), y entiendo que tiene algo que ver con tener que realizar muchas consultas de bases de datos para algo que parece simple en el mundo de los objetos.

¿Alguien tiene una explicación más detallada del problema?


1297
2017-09-18 21:30


origen


Respuestas:


Digamos que tienes una colección de Car objetos (filas de la base de datos), y cada Car tiene una colección de Wheel objetos (también filas). En otras palabras, Car -> Wheel es una relación de 1 a muchos.

Ahora, digamos que necesita recorrer todos los autos, y para cada uno, imprima una lista de las ruedas. La implementación ingenua de O / R haría lo siguiente:

SELECT * FROM Cars;

Y entonces para cada Car:

SELECT * FROM Wheel WHERE CarId = ?

En otras palabras, tiene una selección para los automóviles, y luego N selecciones adicionales, donde N es el número total de automóviles.

Alternativamente, uno podría obtener todas las ruedas y realizar las búsquedas en la memoria:

SELECT * FROM Wheel

Esto reduce el número de viajes de ida y vuelta a la base de datos de N + 1 a 2. La mayoría de las herramientas ORM le brindan varias formas de evitar que N + 1 seleccione.

Referencia: Persistencia de Java con Hibernate, capítulo 13.


767
2017-09-18 21:36



SELECT 
table1.*
, table2.*
INNER JOIN table2 ON table2.SomeFkId = table1.SomeId

Eso le proporciona un conjunto de resultados donde las filas secundarias en la tabla 2 causan duplicación al devolver los resultados de la tabla1 para cada fila secundaria en la tabla2. Los correlacionadores de O / R deben diferenciar las instancias de tabla1 basadas en un campo de clave único, luego usar todas las columnas de tabla2 para completar las instancias secundarias.

SELECT table1.*

SELECT table2.* WHERE SomeFkId = #

El N + 1 es donde la primera consulta rellena el objeto primario y la segunda consulta rellena todos los objetos secundarios para cada uno de los objetos primarios únicos devueltos.

Considerar:

class House
{
    int Id { get; set; }
    string Address { get; set; }
    Person[] Inhabitants { get; set; }
}

class Person
{
    string Name { get; set; }
    int HouseId { get; set; }
}

y tablas con una estructura similar. Una única consulta para la dirección "22 Valley St" puede regresar:

Id Address      Name HouseId
1  22 Valley St Dave 1
1  22 Valley St John 1
1  22 Valley St Mike 1

El O / RM debe llenar una instancia de Inicio con ID = 1, Dirección = "22 Valley St" y luego llenar el conjunto de habitantes con instancias People para Dave, John y Mike con solo una consulta.

Una consulta N + 1 para la misma dirección utilizada anteriormente daría como resultado:

Id Address
1  22 Valley St

con una consulta separada como

SELECT * FROM Person WHERE HouseId = 1

y como resultado un conjunto de datos por separado como

Name    HouseId
Dave    1
John    1
Mike    1

y el resultado final es el mismo que el anterior con la consulta única.

Las ventajas de Single Select es que obtienes todos los datos por adelantado que pueden ser lo que finalmente deseas. Las ventajas de N + 1 es que la complejidad de la consulta se reduce y puede usar la carga diferida donde los conjuntos de resultados secundarios solo se cargan cuando se solicita por primera vez.


98
2017-09-18 21:43



Proveedor con una relación de uno a muchos con el Producto. Un proveedor tiene (suministra) muchos productos.

***** Table: Supplier *****
+-----+-------------------+
| ID  |       NAME        |
+-----+-------------------+
|  1  |  Supplier Name 1  |
|  2  |  Supplier Name 2  |
|  3  |  Supplier Name 3  |
|  4  |  Supplier Name 4  |
+-----+-------------------+

***** Table: Product *****
+-----+-----------+--------------------+-------+------------+
| ID  |   NAME    |     DESCRIPTION    | PRICE | SUPPLIERID |
+-----+-----------+--------------------+-------+------------+
|1    | Product 1 | Name for Product 1 |  2.0  |     1      |
|2    | Product 2 | Name for Product 2 | 22.0  |     1      |
|3    | Product 3 | Name for Product 3 | 30.0  |     2      |
|4    | Product 4 | Name for Product 4 |  7.0  |     3      |
+-----+-----------+--------------------+-------+------------+

Factores:

  • Modo diferido para Proveedor establecido en "verdadero" (predeterminado)

  • El modo de búsqueda utilizado para consultar el producto es Seleccionar

  • Modo Fetch (por defecto): se accede a la información del proveedor

  • El almacenamiento en caché no juega un papel por primera vez

  • Se accede al proveedor

El modo de captura es Seleccionar captura (predeterminado)

// It takes Select fetch mode as a default
Query query = session.createQuery( "from Product p");
List list = query.list();
// Supplier is being accessed
displayProductsListWithSupplierName(results);

select ... various field names ... from PRODUCT
select ... various field names ... from SUPPLIER where SUPPLIER.id=?
select ... various field names ... from SUPPLIER where SUPPLIER.id=?
select ... various field names ... from SUPPLIER where SUPPLIER.id=?

Resultado:

  • 1 declaración de selección para el producto
  • N seleccionar declaraciones para el proveedor

¡Este es un problema de selección N + 1!


58
2017-12-01 13:35



No puedo comentar directamente sobre otras respuestas, porque no tengo suficiente reputación. Pero vale la pena señalar que el problema esencialmente solo surge porque, históricamente, muchos dbms han sido bastante pobres cuando se trata de manejar uniones (MySQL es un ejemplo particularmente notable). Así que n + 1 ha sido, a menudo, notablemente más rápido que una unión. Y luego hay formas de mejorar n + 1 pero aún sin necesidad de unir, que es lo que se relaciona con el problema original.

Sin embargo, MySQL ahora es mucho mejor de lo que solía ser en lo que respecta a las uniones. Cuando aprendí MySQL por primera vez, solía unir mucho. Luego descubrí lo lento que son, y cambio a n + 1 en el código. Pero, recientemente, he estado retrocediendo a las uniones, porque ahora MySQL es muchísimo mejor en su manejo de lo que era cuando comencé a usarlo.

En estos días, una simple combinación en un conjunto de tablas correctamente indexadas rara vez es un problema, en términos de rendimiento. Y si da un golpe de rendimiento, entonces el uso de pistas de índice a menudo los resuelve.

Esto es discutido aquí por uno de los miembros del equipo de desarrollo de MySQL:

http://jorgenloland.blogspot.co.uk/2013/02/dbt-3-q3-6-x-performance-in-mysql-5610.html

Así que el resumen es: si ha estado evitando uniones en el pasado debido al desempeño abismal de MySQL con ellos, intente de nuevo en las últimas versiones. Probablemente te sorprenderá gratamente.


33
2018-01-08 12:49



Nos alejamos del ORM en Django debido a este problema. Básicamente, si intentas y haces

for p in person:
    print p.car.colour

El ORM devolverá felizmente a todas las personas (generalmente como instancias de un objeto Persona), pero luego tendrá que consultar la tabla del automóvil para cada Persona.

Un enfoque simple y muy efectivo para esto es algo que yo llamo "plegado en abanico", lo que evita la absurda idea de que los resultados de las consultas de una base de datos relacional deben correlacionarse con las tablas originales de las que se compone la consulta.

Paso 1: selección amplia

  select * from people_car_colour; # this is a view or sql function

Esto devolverá algo así como

  p.id | p.name | p.telno | car.id | car.type | car.colour
  -----+--------+---------+--------+----------+-----------
  2    | jones  | 2145    | 77     | ford     | red
  2    | jones  | 2145    | 1012   | toyota   | blue
  16   | ashby  | 124     | 99     | bmw      | yellow

Paso 2: Objectify

Suck los resultados en un creador genérico de objetos con un argumento para dividir después del tercer elemento. Esto significa que el objeto "jones" no se creará más de una vez.

Paso 3: Render

for p in people:
    print p.car.colour # no more car queries

Ver esta página web para una implementación de plegado en abanico para Python.


25
2018-06-09 21:18



Supongamos que tiene EMPRESA y EMPLEADO. LA EMPRESA tiene muchos EMPLEADOS (es decir, EMPLEADO tiene un campo ID EMPRESA).

En algunas configuraciones O / R, cuando tiene un objeto Company asignado y accede a sus objetos Employee, la herramienta O / R hará una selección para cada empleado, mientras que si simplemente estuviera haciendo cosas en SQL directo, podría select * from employees where company_id = XX. Por lo tanto, N (número de empleados) más 1 (empresa)

Así es como funcionaron las versiones iniciales de EJB Entity Beans. Creo que cosas como Hibernate han acabado con esto, pero no estoy muy seguro. La mayoría de las herramientas generalmente incluyen información sobre su estrategia de mapeo.


16
2017-09-18 21:33



Aquí hay una buena descripción del problema: http://www.realsolve.co.uk/site/tech/hib-tip-pitfall.php?name=why-lazy

Ahora que comprende el problema, normalmente se puede evitar haciendo una búsqueda conjunta en su consulta. Esto básicamente obliga a la búsqueda del objeto cargado de forma diferida, por lo que los datos se recuperan en una consulta en lugar de n + 1 consultas. Espero que esto ayude.


14
2017-09-18 21:43



Consulte la publicación de Ayende sobre el tema: Combatir el problema Select N + 1 en NHibernate

Básicamente, al usar un ORM como NHibernate o EntityFramework, si tiene una relación de uno a varios (detalle principal) y desea enumerar todos los detalles por cada registro maestro, debe realizar llamadas de consulta N + 1 al base de datos, "N" es el número de registros maestros: 1 consulta para obtener todos los registros maestros y N consultas, uno por registro maestro, para obtener todos los detalles por registro maestro.

Más llamadas de consulta a la base de datos -> más tiempo de latencia -> disminución del rendimiento de la aplicación / base de datos.

Sin embargo, los ORM tienen opciones para evitar este problema, principalmente mediante el uso de "uniones".


12
2018-06-05 22:21



En mi opinión, el artículo escrito en Hibernate Pitfall: Por qué las relaciones deberían ser perezosas es exactamente lo opuesto al verdadero problema de N + 1 es.

Si necesita una explicación correcta, consulte Hibernate - Capítulo 19: Mejora del rendimiento - Estrategias de captación

Seleccionar búsqueda (el valor predeterminado) es   extremadamente vulnerable a N + 1 selecciona   problemas, por lo que es posible que desee habilitar   unirse a la búsqueda


11
2017-07-21 11:55



El enlace suministrado tiene un ejemplo muy simple del problema n + 1. Si lo aplica a Hibernate, básicamente está hablando de lo mismo. Cuando consulta un objeto, la entidad se carga pero cualquier asociación (a menos que se configure de otra manera) se cargará de forma diferida. De ahí una consulta para los objetos raíz y otra consulta para cargar las asociaciones para cada uno de estos. 100 objetos devueltos significan una consulta inicial y luego 100 consultas adicionales para obtener la asociación para cada uno, n + 1.

http://pramatr.com/2009/02/05/sql-n-1-selects-explained/


9
2018-02-20 08:33



Es mucho más rápido emitir 1 consulta que devuelve 100 resultados que emitir 100 consultas que devuelven 1 resultado.


7
2017-11-07 10:30