de Martin LE TARNEC.

Indexar Group By


Las bases de datos SQL usan dos algoritmos totalmente distintos para group by. El primero, un algoritmo de hash que agrega los registros de entrada en una tabla hash temporal. Una vez que todos los registros de entrada están procesados, es la tabla hash la que en esencia se entrega como resultado. El segundo, el algoritmo ordena/agrupa, que primero ordena todas los datos de entrada vía la clave de agrupación, de forma que todas las filas de cada grupo están a continuación de las otras, de forma encadenada. Después, la base de datos solamente necesita unirlos. En general, ambos algoritmos necesitan materializar un estado intermedio, así que no se puede ejecutar en pipeline. Sin embargo, el algoritmo ordena/agrupa puede utilizar un índice para evitar una operación de ordenación, habilitando un group by en pipeline.

Nota

MySQL 5.7 no utiliza el algoritmo de hash. Sin embargo, la optimi­zación del algoritmo ordena/agrupa funciona como se describe a continuación.

Consideramos la siguiente sentencia: obtener los ingresos del día de ayer agrupados por PRODUCT_ID:

SELECT product_id, sum(eur_value)
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 GROUP BY product_id

Como sabemos desde la sección anterior, que existe el índice sobre SALE_DATE y PRODUCT_ID, el algoritmo ordena/agrupa es el más apropiado porque INDEX RANGE SCAN entrega automáticamente las filas en el orden requerido. Eso significa que la base de datos se libra de la materialización porque no necesita una operación de ordenación explícita; group by se ejecuta en pipeline.

DB2
Explain Plan
------------------------------------------------------------
ID | Operation             |                     Rows | Cost
 1 | RETURN                |                          |  675
 2 |  GRPBY (COMPLETE)     |      25 of 387 (  6.46%) |  675
 3 |   FETCH SALES         |     387 of 387 (100.00%) |  675
 4 |    IXSCAN SALES_DT_PR | 387 of 1009326 (   .04%) |   24

Predicate Information
 4 - START (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))
      STOP (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))
Oracle
---------------------------------------------------------------
|Id |Operation                    | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT             |             |   17 |  192 |
| 1 | SORT GROUP BY NOSORT        |             |   17 |  192 |
| 2 |  TABLE ACCESS BY INDEX ROWID| SALES       |  321 |  192 |
|*3 |   INDEX RANGE SCAN          | SALES_DT_PR |  321 |    3 |
---------------------------------------------------------------

El plan de ejecución de Oracle señala una operación SORT GROUP BY en pipeline agregando NOSORT en el plan. El plan de ejecución de otras bases de datos no mencionan ninguna operación de ordenación.

Group by en pipeline tiene los mismos prerequisitos que order by en pipe­line, salvo que no existen los modificadores ASC y DESC. Eso significa que si se define un índice con los modificadores ASC/DESC, no afectaría la eje­cu­ción del group by en pipeline. Lo mismo ocurre para los NULLS FIRST/LAST. Sin embargo, existen bases de datos que no pueden utilizar correctamente un índice ASC/DESC para un group by en pipeline.

Aviso

Para PostgreSQL, se debe agregar una cláusula order by para crear un índice ordenado con NULLS LAST y utilizable por un group by en pipeline.

La base de datos Oracle no puede leer un índice hacia atrás con la finalidad de ejecutar un group by en pipeline que esté seguido por un order by.

Hay más información disponible en los apéndices respectivos: PostgreSQL, Oracle.

Si se extiende la sentencia para considerar todas las ventas desde ayer, como se hizo en el ejemplo con order by en pipeline, se evita un group by en pipeline por las mismas razones que antes: INDEX RANGE SCAN no entrega las filas ordenadas por la clave de agrupación (comparando con la Figura 6.1).

SELECT product_id, sum(eur_value)
  FROM sales
 WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
 GROUP BY product_id

DB2
Explain Plan
--------------------------------------------------------------------
ID | Operation                   |                      Rows |  Cost
 1 | RETURN                      |                           | 12527
 2 |  GRPBY (FINAL)              |        25 of 25 (100.00%) | 12527
 3 |   TBSCAN                    |        25 of 25 (100.00%) | 12527
 4 |    SORT (INTERMEDIATE)      |        25 of 25 (100.00%) | 12527
 5 |     GRPBY (HASHED PARTIAL)  |      25 of 8050 (   .31%) | 12527
 6 |      FETCH SALES            |    8050 of 8050 (100.00%) | 12526
 7 |       RIDSCN                |    8050 of 8050 (100.00%) |   375
 8 |        SORT (UNQIUE)        |    8050 of 8050 (100.00%) |   375
 9 |         IXSCAN SALES_DT_PR  | 8050 of 1009326 (   .80%) |   372

El siguiente filtro where no se ha utilizado para obtener este resultado: WHERE sale_date >= CURRENT_DATE - 1 MONTH.

Al contrario de lo que sucede con el plan de ejecución de Oracle, este parece bastante complejo. Se debe a dos cosas:

  • DB2 muestra explícitamente una operación de ordenación de la posición física de almacenamiento entre el acceso al índice y el acceso a la tabla (operaciones 7 y 8).

  • DB2 lleva a cabo dos fases de agregación: primero se hace una agrupación parcial para reducir la cantidad de datos para ser ordenadas lo antes posible (operación 5) y después se hace un SORT + GRPBY habitual.

Oracle
---------------------------------------------------------------
|Id |Operation                    | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT             |             |   24 |  356 |
| 1 | HASH GROUP BY               |             |   24 |  356 |
| 2 |  TABLE ACCESS BY INDEX ROWID| SALES       |  596 |  355 |
|*3 |   INDEX RANGE SCAN          | SALES_DT_PR |  596 |    4 |
---------------------------------------------------------------

En cambio, la base de datos Oracle usa el algoritmo de hash. La ventaja del algoritmo de hash es que solamente necesita almacenar en memoria el resultado agregado, mientras que el algoritmo ordena/agrupa materializa el conjunto completo de entrada. En otras palabras, el algoritmo hash necesita menos memoria.

Como con order by en pipeline, una ejecución rápida no es el aspecto más importante de la ejecución del group by en pipeline. Es más importante que la base de datos lo ejecute de una manera pipeline y entregue el primer resultado antes de haber leído la entrada entera. Ése es el prerequisito para los métodos de optimización avanzados que se explican en el siguiente capítulo.

Si te gusta mi manera de explicar, te encantará mi libro.

Piénsalo

¿Debe asumirse que ninguna operación de la base de datos (con excepción de la ordenación y de la agrupación) pueda utilizar un índice para evitar un ordenación?

Acerca del autor

Foto de Markus Winand

Markus Winand enseña eficientemente SQL, en casa y online. Minimiza el tiempo de desarrollo utilizando moderno SQL y optimiza el tiempo de ejecución con indexación inteligente. Para ello también ha publicado el libro SQL Performance Explained.

“Use The Index, Luke” de Markus Winand se halla bajo licencia Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Aspectos legales | Contacto | SIN GARANTÍA | Marcas | Privacy | CC-BY-NC-ND 3.0 licencia