Problemas de rendimiento de SQL debido a "trabajo innecesario pero requerido"

Nivel de conocimiento requerido para comprender el artículo: comprensión general de bases de datos y SQL, cierta experiencia práctica con DBMS. Probablemente lo más importante que puede aprender para escribir consultas SQL efectivas es la indexación. Sin embargo, en segundo lugar, muy de cerca, está el conocimiento de que muchos clientes SQL requieren que la base de datos realice una gran cantidad de "trabajo innecesario pero necesario" . Repite después de mi: ¿Qué es “trabajo innecesario pero obligatorio”? Como nos dice el Capitán Obvio, ella:

Innecesario

Dejemos que nuestra aplicación cliente necesite los siguientes datos: Nada inusual. Estamos trabajando con una base de datos de películas (como la base de datos Sakila ) y queremos mostrar el título y la calificación de todas las películas a los usuarios. La siguiente consulta puede dar el resultado que necesitamos:

SELECT title, rating
FROM film

Sin embargo, nuestra aplicación (o nuestro ORM) ejecuta esta consulta:

SELECT *
FROM film

¿Qué obtenemos como resultado? Adivinar. Recibimos mucha información inútil: A la derecha incluso puedes ver que se está cargando un JSON complejo:

• desde el disco
• almacenar en caché
• por cable
• en memoria del cliente
• y finalmente desechado [por ser innecesario]

Sí, desechamos la mayor parte de esta información. Todas las acciones tomadas para extraer esta información resultaron completamente inútiles. ¿Es verdad? Es verdad.

Obligatorio

Y ahora... la peor parte. Aunque los optimizadores ahora pueden hacer mucho, estas acciones son obligatorias para la base de datos. La base de datos no tiene forma de saber que la aplicación cliente no necesita el 95% de estos datos. Y este es sólo el ejemplo más simple. Imagínese conectar varias tablas... ¿Y qué, dices, pero las bases de datos son rápidas? Déjame iluminarte sobre algunas cosas en las que probablemente no hayas pensado. Por supuesto, el tiempo de ejecución de una solicitud individual realmente no afecta en nada. Vale, funcionó una vez y media más lento, pero lo superaremos, ¿verdad? ¿Por conveniencia? A veces esto es cierto. Pero si siempre sacrificas el rendimiento por la comodidad , estas pequeñas cosas empezarán a acumularse. Ya no hablaremos de rendimiento (la velocidad de ejecución de solicitudes individuales), sino de rendimiento (tiempo de respuesta del sistema), y luego comenzarán problemas serios que no son tan fáciles de resolver. Ahí es cuando se pierde escalabilidad. Veamos los planes de ejecución, en este caso el DBMS de Oracle:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |   166K|
|   1 |  TABLE ACCESS FULL| FILM |  1000 |   166K|
--------------------------------------------------

comparado con:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 | 20000 |
|   1 |  TABLE ACCESS FULL| FILM |  1000 | 20000 |
--------------------------------------------------

Al ejecutar una consulta SELECT * en lugar de SELECT title, la calificación utiliza 8 veces más memoria en la base de datos. Nada inesperado, ¿verdad? Sabíamos que esto pasaría. Pero aun así aceptamos esto para muchas de nuestras solicitudes en las que simplemente no necesitamos todos estos datos. Creamos trabajo innecesario pero obligatorio para la base de datos , que se sigue acumulando y acumulando. Usamos 8 veces más memoria de la necesaria (el multiplicador cambiará, claro). Mientras tanto, en todas las demás etapas (E/S de disco, transferencia de datos a través de la red, consumo de memoria por parte del cliente) los problemas son exactamente los mismos, pero los omitiré y en su lugar analizaré...

Usando índices

La mayoría de las bases de datos actuales ya han apreciado el concepto de índices de cobertura . Un índice de cobertura no es en sí mismo un tipo especial de índice. Pero puede resultar ser un "índice especial" para una consulta particular, ya sea "por accidente" o porque así estaba previsto. Considere la siguiente consulta:

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

No hay nada inesperado en cuanto a su implementación. Esta es una petición sencilla. Vea el rango por índice, acceda a la tabla y listo:

-------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR               |     8 |
|*  2 |   INDEX RANGE SCAN          | IDX_ACTOR_LAST_NAME |     8 |
-------------------------------------------------------------------

Buen plan, ¿no? Bueno, si realmente necesitáramos esto, entonces no: Evidentemente estamos desperdiciando memoria, etc. Consideremos esta consulta como alternativa:

SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'

Su plan es este:

----------------------------------------------------
| Id  | Operation        | Name            | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT |                 |     8 |
|*  1 |  INDEX RANGE SCAN| IDX_ACTOR_NAMES |     8 |
----------------------------------------------------

Pudimos eliminar completamente el acceso a la tabla, gracias a la presencia de un índice que satisface todas las necesidades de nuestra consulta... un índice de cobertura. ¿Es importante? ¡Y cómo! Este enfoque le permite acelerar algunas consultas en un orden de magnitud (o ralentizarlas en un orden de magnitud cuando el índice ya no cubre después de algunos cambios). No siempre se pueden utilizar índices de cobertura. Tienes que pagar por los índices y no debes agregar demasiados. Pero en este caso todo es obvio. Evaluamos el rendimiento:

SET SERVEROUTPUT ON
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT NUMBER := 100000;
BEGIN
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Наихудший вариант pedidoа: перерасход памяти ПЛЮС доступ к таблице
      SELECT *
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 1 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Улучшенный pedido, но все равно с доступом к таблице
      SELECT /*+INDEX(actor(last_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 2 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Оптимальный pedido: покрывающий индекс
      SELECT /*+INDEX(actor(last_name, first_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 3 : ' || (SYSTIMESTAMP - v_ts));
END;
/

Tenga en cuenta que la tabla de actores solo tiene 4 columnas, por lo que la diferencia de rendimiento entre las declaraciones 1 y 2 no es tan grande, pero sigue siendo significativa. También señalaré que utilicé las sugerencias del optimizador de Oracle para que el optimizador seleccionara uno u otro índice específico para la consulta. El Operador 3 es el ganador indiscutible de nuestra carrera. Su rendimiento es mucho mejor y estamos hablando de una consulta sumamente sencilla. Nuevamente, cuando escribimos SELECT *, creamos trabajo innecesario pero obligatorio para la base de datos que no puede optimizar. No seleccionará el índice de cobertura porque tiene una sobrecarga ligeramente mayor que el índice LAST_NAME que eligió y, entre otras cosas, todavía tiene que acceder a la tabla para recuperar una columna LAST_UPDATE inútil, por ejemplo. Pero cuanto más analizamos SELECT *, peores resultan las cosas. Vamos a hablar acerca de...

Conversiones SQL

Los optimizadores funcionan muy bien porque transforman las consultas SQL ( hablé sobre cómo funciona esto en mi reciente charla en Voxxed Days en Zurich ). Por ejemplo, existe una transformación de "excepción JOIN" extremadamente poderosa. Considere la siguiente vista auxiliar que tuvimos que crear para evitar unir todas estas tablas manualmente cada vez:

CREATE VIEW v_customer AS
SELECT
  c.first_name, c.last_name,
  a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)

Esta vista simplemente realiza todas las uniones "...a uno" entre la tabla de clientes CLIENTE y varias tablas para partes de su dirección. Gracias, normalización. Imaginemos que, después de trabajar un poco con esta vista, nos acostumbramos y nos olvidamos de las tablas subyacentes. Y ahora ejecutamos la siguiente consulta:

SELECT *
FROM v_customer

Como resultado, obtenemos un plan muy impresionante:

----------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost |
----------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   599 | 47920 |   14 |
|*  1 |  HASH JOIN           |          |   599 | 47920 |   14 |
|   2 |   TABLE ACCESS FULL  | COUNTRY  |   109 |  1526 |    2 |
|*  3 |   HASH JOIN          |          |   599 | 39534 |   11 |
|   4 |    TABLE ACCESS FULL | CITY     |   600 | 10800 |    3 |
|*  5 |    HASH JOIN         |          |   599 | 28752 |    8 |
|   6 |     TABLE ACCESS FULL| CUSTOMER |   599 | 11381 |    4 |
|   7 |     TABLE ACCESS FULL| ADDRESS  |   603 | 17487 |    3 |
----------------------------------------------------------------

Bueno, por supuesto. La base de datos está haciendo todas estas uniones y escaneos completos de tablas porque eso es lo que le dijimos que hiciera: recuperar todos estos datos. Ahora, de nuevo, imagina que todo lo que realmente necesitábamos era esto: Qué, en serio, ¿verdad? Ahora estás empezando a entender de qué estoy hablando. Pero imagina que aprendimos algo de errores pasados ​​y ejecutamos esta consulta más óptima:

SELECT first_name, last_name
FROM v_customer

¡Ahora veamos qué pasó!

------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   599 | 16173 |     4 |
|   1 |  NESTED LOOPS      |             |   599 | 16173 |     4 |
|   2 |   TABLE ACCESS FULL| CUSTOMER    |   599 | 11381 |     4 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C007120 |     1 |     8 |     0 |
------------------------------------------------------------------

Cambios drásticos para mejor en términos de ejecución. Las uniones se han eliminado porque el optimizador ahora puede ver que son inútiles , y si puede ver eso (y no ha hecho que este trabajo sea obligatorio al seleccionar *), entonces simplemente no puede hacer todo ese trabajo. ¿Por qué es así en este caso? La clave externa CUSTOMER.ADDRESS_ID a la clave primaria ADDRESS.ADDRESS_ID garantiza exactamente un valor de esta última, lo que significa que la operación JOIN será una unión "...a uno" que no aumenta ni disminuye el número de filas. . Y como no seleccionamos ni solicitamos ninguna fila, no tiene sentido cargarlas. Eliminar JOIN probablemente no afectará en absoluto el resultado de la consulta. Las bases de datos hacen esto todo el tiempo. Puede ejecutar la siguiente consulta en casi cualquier base de datos:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (SELECT 1 / 0)

En este caso, es posible que se produzca una excepción aritmética, como cuando se ejecuta la siguiente consulta:

SELECT 1 / 0 FROM dual

Pero esto no sucede. El optimizador (o incluso el analizador) puede garantizar que ningún elemento de la lista de selección en el predicado EXISTS (SELECT ..) cambie el resultado de la consulta, por lo que no es necesario ejecutarla. ¡Como esto!

Mientras tanto...

Uno de los problemas más molestos con los ORM es que son muy fáciles de escribir consultas SELECT *. De hecho, por ejemplo, en HQL/JPQL generalmente se utilizan por defecto. Podemos omitir la cláusula SELECT por completo, porque vamos a recuperar la entidad completa, ¿verdad? Por ejemplo:

FROM v_customer

Por ejemplo, Vlad Mihalcea, un experto y defensor del desarrollo con Hibernate , recomienda utilizar consultas [calificadas] casi siempre cuando esté seguro de que no desea guardar ningún cambio después de finalizar la compra. Los ORM facilitan enormemente la solución del problema de la persistencia de los gráficos de objetos. Nota: Persistencia. Las tareas de modificar gráficos de objetos y guardar cambios están indisolublemente ligadas. Pero si no vas a hacer eso, ¿por qué molestarte en extraer la esencia? ¿Por qué no escribir una solicitud [refinada]? Seamos claros: desde el punto de vista del rendimiento, escribir una consulta específicamente adaptada a su caso de uso específico es obviamente mejor que cualquier otra opción. Puede que no le importe, ya que su conjunto de datos es pequeño y no importa. Excelente. Pero cuando eventualmente necesite escalabilidad, rediseñar sus aplicaciones para usar consultas en lugar del recorrido imperativo del gráfico de entidades será todo un desafío. Y tendrás algo que hacer sin él.

Contar líneas para saber si hay algo presente

Uno de los peores desperdicios de recursos es ejecutar consultas COUNT(*) sólo para ver si hay algo en la base de datos. Por ejemplo, necesitamos saber si un usuario determinado tiene algún pedido. Y ejecutamos la solicitud:

SELECT count(*)
FROM orders
WHERE user_id = :user_id

Elemental. Si COUNT = 0, entonces no hay órdenes. De lo contrario, sí. El rendimiento no será tan malo ya que probablemente tengamos un índice en la columna ORDERS.USER_ID. Pero, ¿cómo crees que se comparará el rendimiento de la consulta anterior con la siguiente opción?

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
)

No hace falta ser un genio para darse cuenta de que un predicado de existencia verdadero dejará de buscar cadenas adicionales tan pronto como encuentre la primera . Entonces, si el resultado es “no hay pedidos”, entonces la velocidad será comparable. Sin embargo, si el resultado es "sí, hay pedidos", entonces, en el caso de que no sea necesario contar la cantidad exacta, la respuesta se recibirá mucho más rápido. Después de todo, no nos interesa el número exacto. Sin embargo, le dijimos a la base de datos que lo calculara ( trabajo innecesario ) y la base de datos no sabe que estamos ignorando todos los resultados mayores que 1 ( trabajo requerido ). Por supuesto, sería mucho peor si invocáramos list.size() en una colección respaldada por JPA para lograr los mismos resultados. Ya escribí sobre esto en mi blog anteriormente y realicé pruebas comparativas de ambas opciones en...

Conclusión

Este artículo afirma lo obvio. No fuerce la base de datos a realizar trabajos innecesarios pero necesarios . Es innecesario porque, dados los requisitos, sabes que no es necesario realizar algún trabajo específico. Sin embargo, usted le dice a la base de datos que lo haga. Es necesario porque no hay forma de que la base de datos garantice que este trabajo sea innecesario . Esta información solo está disponible para el cliente y no está disponible para el servidor. Entonces la base de datos tiene que ejecutarlo. El artículo se centró en SELECT *, principalmente porque es un objeto muy conveniente de mirar. Pero esto no sólo se aplica a las bases de datos. Esto se aplica a todos los algoritmos distribuidos en los que el cliente le dice al servidor que haga un trabajo innecesario pero requerido . ¿Cuántas tareas N+1 hay en su aplicación AngularJS promedio donde la interfaz de usuario recorre el resultado del servicio A y llama al servicio B varias veces, en lugar de agrupar todas las llamadas a B en una sola llamada? Esto es algo muy común. La solución es siempre la misma. Cuanta más información proporcione a la entidad que ejecuta sus comandos, más rápido (teóricamente) ejecutará esos comandos. Escribe consultas óptimas. Siempre. Todo tu sistema te lo agradecerá. Artículo original