Acelerar ORDER BY / GROUP BY con índices

Elimina la ordenación temporal con un índice

Cuando ordenas con ORDER BY y la columna objetivo no tiene índice, la base de datos tiene que sacar cada fila y luego ordenarlas.

El plan muestra esta ordenación temporal como USE TEMP B-TREE FOR ORDER BY (construye una estructura de trabajo temporal solo para hacer la reordenación).

Las cosas cambian en cuanto creas un índice sobre la columna por la que estás ordenando.

Un índice ya está en el orden de esa columna desde el momento en que se construye.

Recorre el orden del índice y el resultado sale de inmediato.

La ordenación temporal ya no es necesaria, y USE TEMP B-TREE desaparece del plan.

Una ordenación temporal reorganiza las 50 000 filas en una estructura de trabajo, así que el coste crece con el número de filas.

Incluso si LIMIT 10 devuelve solo las 10 primeras filas, elegir esas 10 primeras sigue significando reordenar cada fila primero — la carga no se reduce.

En este artículo probarás "eliminar la ordenación con un índice" en la práctica.

-- Ejemplo: tomar las 10 primeras filas ordenadas por amount
-- Con un índice, la base de datos solo recorre su orden
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

EXPLAIN QUERY PLAN
SELECT sale_id, amount
FROM perf_sales
ORDER BY amount
LIMIT 10;

Imagina el requisito: "Queremos las ventas ordenadas por fecha para ver las primeras." Toma la consulta que ordena por sale_date y compara su plan con y sin índice, observando si aparece USE TEMP B-TREE FOR ORDER BY. Elimina y reconstruye el índice dentro de esta única ejecución de la consola para que el plan sea totalmente autocontenido. (Ejecútalo correctamente para revelar la explicación.)

① Primero, usa DROP INDEX IF EXISTS para eliminar el índice sobre sale_date, luego muestra EXPLAIN QUERY PLAN para las 10 primeras filas ordenadas por sale_date (el estado sin índice).

② Luego crea un índice de una sola columna sobre sale_date.

③ Muestra el plan para la misma consulta de ordenación de nuevo, y compáralo con ① para ver si la ordenación temporal desaparece.

GROUP BY también puede apoyarse en el orden del índice

GROUP BY también necesita que las filas con el mismo valor se sitúen unas junto a otras para poder agruparlas.

Sin un índice, eso significa una ordenación temporal y USE TEMP B-TREE FOR GROUP BY en el plan.

Crea un índice sobre la columna de agrupación y las filas con el mismo valor ya son adyacentes en el orden del índice, así que la base de datos puede recortar los grupos solo recorriéndolo.

Por ejemplo, GROUP BY emp_id emparejado con un índice sobre emp_id significa que las filas de cada emp_id ya son consecutivas en el orden del índice, así que la agregación se ejecuta sin ordenación temporal.

Incluye también la columna agregada en el índice y evitarás además volver a la tabla base (el Index-Only Scan (índice cubriente) del artículo anterior).

-- Ejemplo: conteo por region
-- Con un índice sobre region, GROUP BY no necesita ordenación temporal
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT region, COUNT(*)
FROM perf_sales
GROUP BY region;

Imagina el requisito: "Queremos el conteo de ventas por comercial." Toma la consulta que agrupa por emp_id y compara su plan con y sin índice, observando si aparece USE TEMP B-TREE FOR GROUP BY.

① Primero, usa DROP INDEX IF EXISTS para eliminar el índice, luego muestra EXPLAIN QUERY PLAN para la consulta que cuenta filas por emp_id (el estado sin índice).

② Luego crea un índice de una sola columna sobre emp_id.

③ Muestra el plan para la misma consulta agregada de nuevo, y compáralo con ① para ver si la ordenación temporal desaparece.

DISTINCT elimina su ordenación temporal de la misma manera

DISTINCT (que descarta duplicados y devuelve solo valores únicos) también necesita los valores alineados para detectar los duplicados, así que sin un índice provoca una ordenación temporal con USE TEMP B-TREE FOR DISTINCT.

El mecanismo es el mismo que el de GROUP BY: con un índice sobre la columna objetivo, los valores idénticos ya son adyacentes en el orden, así que eliminar duplicados es solo cuestión de comparar cada vecino.

Para SELECT DISTINCT product FROM perf_sales, donde quieres los valores únicos de una sola columna, un índice sobre product elimina la necesidad de una ordenación temporal.

Ordenar, agrupar y deduplicar comparten todos la característica de ser "operaciones que necesitan valores alineados" — y un índice sobre la columna objetivo te permite esquivar la ordenación temporal en cualquiera de ellas.

-- Ejemplo: obtener los valores únicos de region
-- Un índice sobre region evita la ordenación temporal de DISTINCT
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT DISTINCT region
FROM perf_sales;

Imagina el requisito: "Queremos la lista de códigos de producto que hemos manejado, sin duplicados." Toma la consulta que devuelve los valores únicos de product y compara su plan con y sin índice, observando si aparece USE TEMP B-TREE FOR DISTINCT.

① Primero, usa DROP INDEX IF EXISTS para eliminar el índice, luego muestra EXPLAIN QUERY PLAN para la consulta que devuelve los valores únicos de product (el estado sin índice).

② Luego crea un índice de una sola columna sobre product.

③ Muestra el plan para la misma consulta de deduplicación de nuevo, y compáralo con ① para ver si la ordenación temporal desaparece.