PLAN DE CONSULTA DE Sqlite EXPLAIN

Advertencia: Los datos devueltos por el comando EXPLAIN QUERY PLAN están destinados únicamente a la depuración interactiva. El formato de salida puede cambiar entre versiones de SQLite. Las aplicaciones no deben depender del formato de salida del comando EXPLAIN QUERY PLAN.

Alerta: Como se advirtió anteriormente, el formato de salida EXPLAIN QUERY PLAN cambió sustancialmente con la versión 3.24.0 (2018-06-04). Es posible realizar más cambios en versiones posteriores.

El comando EXPLAIN QUERY PLAN SQL se utiliza para obtener una descripción de alto nivel de la estrategia o plan que utiliza SQLite para implementar una consulta SQL específica. Lo más significativo es que EXPLAIN QUERY PLAN informa sobre la forma en que la consulta utiliza los índices de la base de datos. Este documento es una guía para comprender e interpretar el resultado EXPLICAR EL PLAN DE CONSULTA. La información de antecedentes está disponible por separado:

• Una introducción a cómo funciona SQLite.
• Notas sobre el optimizador de consultas.
• Cómo funciona la indexación.
• El planificador de consultas de próxima generación.

Un plan de consulta se representa como un árbol. En forma sin procesar, como lo devuelve sqlite3_step (), cada nodo del árbol consta de cuatro campos: un ID de nodo entero, un ID padre entero, un campo entero auxiliar que no se usa actualmente y una descripción del nodo. Por tanto, todo el árbol es una tabla con cuatro columnas y cero o más filas. El shell de la línea de comandos generalmente interceptará esta tabla y la representará como un gráfico de arte ASCII para una visualización más conveniente. Para deshabilitar la representación gráfica automática de shells y mostrar la salida EXPLAIN QUERY PLAN en su formato tabular, ejecute el comando “.explain off” para desactivar el “modo de formato EXPLAIN”. Para restaurar la representación gráfica automática, ejecute “.explain auto”. Puede ver la configuración actual “EXPLICAR el modo de formato” utilizando el comando “.show”.

También se puede configurar la CLI en modo EXPLICAR PLAN DE CONSULTA automático mediante el comando “.eqp on”:

sqlite>.eqp on

En el modo EXPLAIN QUERY PLAN automático, el shell ejecuta automáticamente una consulta EXPLAIN QUERY PLAN por cada declaración que ingresa y muestra el resultado antes de ejecutar la consulta. Utilice el comando “.eqp off” para desactivar el modo EXPLICAR PLAN DE CONSULTA automático.

EXPLAIN QUERY PLAN es más útil en una instrucción SELECT, pero también puede aparecer con otras instrucciones que leen datos de tablas de la base de datos (por ejemplo, UPDATE, DELETE, INSERT INTO … SELECT).

1.1. Escaneos de tablas e índices

Al procesar una declaración SELECT (u otra), SQLite puede recuperar datos de las tablas de la base de datos de diversas formas. Puede escanear todos los registros en una tabla (un escaneo de tabla completa), escanear un subconjunto contiguo de los registros en una tabla según el índice rowid, escanear un subconjunto contiguo de las entradas en un índice de base de datos o usar una combinación de las estrategias anteriores en un solo escaneo. Las diversas formas en que SQLite puede recuperar datos de una tabla o índice se describen en detalle aquí.

Para cada tabla leída por la consulta, la salida de EXPLAIN QUERY PLAN incluye un registro para el cual el valor en la columna “detalle” comienza con “SCAN” o “SEARCH”. “SCAN” se utiliza para un análisis de tabla completa, incluidos los casos en los que SQLite recorre todos los registros de una tabla en un orden definido por un índice. “BÚSQUEDA” indica que solo se visita un subconjunto de las filas de la tabla. Cada registro de EXPLORACIÓN o BÚSQUEDA incluye la siguiente información:

• Se lee el nombre de los datos de la tabla.
• Si se utiliza o no un índice o un índice automático.
• Si se aplica o no la optimización del índice de cobertura.
• Qué términos de la cláusula WHERE se utilizan para la indexación.

Por ejemplo, el siguiente comando EXPLAIN QUERY PLAN opera en una instrucción SELECT que se implementa al realizar un escaneo de tabla completa en la tabla t1:

sqlite>EXPLAIN QUERY PLANSELECT a, b FROM t1 WHERE a=1;
QUERY PLAN`--SCAN TABLE t1

El ejemplo anterior muestra que SQLite seleccionando un escaneo de tabla completa que visitará todas las filas de la tabla. Si la consulta pudiera utilizar un índice, entonces el registro SCAN / SEARCH incluiría el nombre del índice y, para un registro SEARCH, una indicación de cómo se identifica el subconjunto de filas visitadas. Por ejemplo:

sqlite>CREATEINDEX i1 ON t1(a);
sqlite>EXPLAIN QUERY PLANSELECT a, b FROM t1 WHERE a=1;
QUERY PLAN`--SEARCH TABLE t1 USING INDEX i1 (a=?)

En el ejemplo anterior, SQLite usa el índice “i1” para optimizar un término de cláusula WHERE de la forma (a =?) – en este caso “a = 1”. El ejemplo anterior no podría usar un índice de cobertura, pero el siguiente ejemplo sí, y ese hecho se refleja en el resultado:

sqlite>CREATEINDEX i2 ON t1(a, b);
sqlite>EXPLAIN QUERY PLANSELECT a, b FROM t1 WHERE a=1; 
QUERY PLAN`--SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)

Todas las uniones en SQLite se implementan mediante exploraciones anidadas. Cuando se analiza una consulta SELECT que presenta una combinación utilizando EXPLAIN QUERY PLAN, se genera un registro de EXPLORACIÓN o BÚSQUEDA para cada bucle anidado. Por ejemplo:

sqlite>EXPLAIN QUERY PLANSELECT t1.*, t2.*FROM t1, t2 WHERE t1.a=1AND t1.b>2;
QUERY PLAN|--SEARCH TABLE t1 USING INDEX i2 (a=? AND b>?)`--SCAN TABLE t2

El orden de las entradas indica el orden de anidamiento. En este caso, el escaneo de la tabla t1 usando el índice i2 es el ciclo externo (ya que aparece primero) y el escaneo de la tabla completa de la tabla t2 es el ciclo interno (ya que aparece el último). En el siguiente ejemplo, las posiciones de t1 y t2 en la cláusula FROM de SELECT están invertidas. La estrategia de consulta sigue siendo la misma. La salida de EXPLAIN QUERY PLAN muestra cómo se evalúa realmente la consulta, no cómo se especifica en la declaración SQL.

sqlite>EXPLAIN QUERY PLANSELECT t1.*, t2.*FROM t2, t1 WHERE t1.a=1AND t1.b>2;
QUERY PLAN|--SEARCH TABLE t1 USING INDEX i2 (a=? AND b>?)`--SCAN TABLE t2

Si la cláusula WHERE de una consulta contiene una expresión OR, entonces SQLite podría usar la estrategia “OR por unión” (también conocida como optimización OR). En este caso, habrá un solo registro de nivel superior para la búsqueda, con dos subregistros, uno para cada índice:

sqlite>CREATEINDEX i3 ON t1(b);
sqlite>EXPLAIN QUERY PLANSELECT*FROM t1 WHERE a=1OR b=2;
QUERY PLAN`--MULTI-INDEX OR|--SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)`--SEARCH TABLE t1 USING INDEX i3 (b=?)

1.2. Clasificación temporal de árboles B

Si una consulta SELECT contiene una cláusula ORDER BY, GROUP BY o DISTINCT, es posible que SQLite necesite usar una estructura de árbol b temporal para ordenar las filas de salida. O podría usar un índice. Usar un índice es casi siempre mucho más eficiente que realizar una ordenación. Si se requiere un árbol b temporal, se agrega un registro a la salida EXPLAIN QUERY PLAN con el campo “detalle” establecido en un valor de cadena de la forma “USE TEMP B-TREE FOR xxx”, donde xxx es uno de “ORDER POR “,” AGRUPAR POR “o” DISTINTO “. Por ejemplo:

sqlite>EXPLAIN QUERY PLANSELECT c, d FROM t2 ORDERBY c;
QUERY PLAN|--SCAN TABLE t2`--USE TEMP B-TREE FOR ORDER BY

En este caso, se puede evitar el uso del árbol b temporal creando un índice en t2 (c), de la siguiente manera:

sqlite>CREATEINDEX i4 ON t2(c);
sqlite>EXPLAIN QUERY PLANSELECT c, d FROM t2 ORDERBY c; 
QUERY PLAN`--SCAN TABLE t2 USING INDEX i4

1.3. Subconsultas

En todos los ejemplos anteriores, solo ha habido una única instrucción SELECT. Si una consulta contiene sub-selecciones, estas se muestran como elementos secundarios del SELECT externo. Por ejemplo:

sqlite>EXPLAIN QUERY PLANSELECT(SELECT b FROM t1 WHERE a=0),(SELECT a FROM t1 WHERE b=t2.c)FROM t2;|--SCAN TABLE t2 USING COVERING INDEX i4|--SCALAR SUBQUERY|`--SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)`--CORRELATED SCALAR SUBQUERY`--SEARCH TABLE t1 USING INDEX i3 (b=?)

El ejemplo anterior contiene dos subconsultas “ESCALAR”. Las subconsultas son ESCALARES en el sentido de que devuelven un valor único: una tabla de una fila y una columna. Si la consulta real devuelve más que eso, solo se usa la primera columna de la primera fila.

La primera subconsulta anterior es constante con respecto a la consulta externa. El valor de la primera subconsulta puede calcularse una vez y luego reutilizarse para cada fila del SELECT externo. La segunda subconsulta, sin embargo, está “CORRELADA”. El valor de la segunda subconsulta cambia según los valores de la fila actual de la consulta externa. Por lo tanto, la segunda subconsulta debe ejecutarse una vez para cada fila de salida en el SELECT externo.

A menos que se aplique la optimización de aplanamiento, si aparece una subconsulta en la cláusula FROM de una instrucción SELECT, SQLite puede ejecutar la subconsulta y almacenar los resultados en una tabla temporal, o puede ejecutar la subconsulta como una co-rutina. La siguiente consulta es un ejemplo de esto último. La subconsulta la ejecuta una co-rutina. La consulta externa se bloquea siempre que necesita otra fila de entrada de la subconsulta. El control cambia a la co-rutina que produce la fila de salida deseada, luego el control vuelve a la rutina principal que continúa procesando.

sqlite>EXPLAIN QUERY PLANSELECTcount(*)FROM(SELECTmax(b)AS x FROM t1 GROUPBY a)GROUPBY x;
QUERY PLAN|--CO-ROUTINE 0x20FC3E0|`--SCAN TABLE t1 USING COVERING INDEX i2|--SCAN SUBQUERY 0x20FC3E0`--USE TEMP B-TREE FOR GROUP BY

Si la optimización de acoplado se usa en una subconsulta en la cláusula FROM de una instrucción SELECT, eso fusiona efectivamente la subconsulta en la consulta externa. El resultado de EXPLAIN QUERY PLAN refleja esto, como en el siguiente ejemplo:

sqlite>EXPLAIN QUERY PLANSELECT*FROM(SELECT*FROM t2 WHERE c=1), t1;
QUERY PLAN|--SEARCH TABLE t2 USING INDEX i4 (c=?)`--SCAN TABLE t1

Si es posible que sea necesario visitar el contenido de una subconsulta más de una vez, entonces el uso de una co-rutina no es deseable, ya que la co-rutina tendría que calcular los datos más de una vez. Y si la subconsulta no se puede acoplar, eso significa que la subconsulta debe manifestarse en una tabla transitoria.

sqlite>SELECT*FROM>(SELECT*FROM t1 WHERE a=1ORDERBY b LIMIT2)AS x,>(SELECT*FROM t2 WHERE c=1ORDERBY d LIMIT2)AS y;
QUERY PLAN|--MATERIALIZE 0x18F06F0|`--SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)|--MATERIALIZE 0x18F80D0||--SEARCH TABLE t2 USING INDEX i4 (c=?)|`--USE TEMP B-TREE FOR ORDER BY|--SCAN SUBQUERY 0x18F06F0 AS x`--SCAN SUBQUERY 0x18F80D0 AS y

1.4. Consultas compuestas

Cada consulta de componente de una consulta compuesta (UNION, UNION ALL, EXCEPT o INTERSECT) se asigna calculada por separado y se le da su propia línea en la salida EXPLAIN QUERY PLAN.

sqlite>EXPLAIN QUERY PLANSELECT a FROM t1 UNIONSELECT c FROM t2;
QUERY PLAN`--COMPOUND QUERY|--LEFT-MOST SUBQUERY|`--SCAN TABLE t1 USING COVERING INDEX i1`--UNION USING TEMP B-TREE`--SCAN TABLE t2 USING COVERING INDEX i4

La cláusula “USING TEMP B-TREE” en la salida anterior indica que se usa una estructura de árbol b temporal para implementar la UNIÓN de los resultados de las dos sub-selecciones. Un método alternativo de calcular un compuesto es ejecutar cada subconsulta como una co-rutina, organizar para que sus salidas aparezcan en orden ordenado y fusionar los resultados. Cuando el planificador de consultas elige este último enfoque, la salida EXPLAIN QUERY PLAN tiene este aspecto:

sqlite>EXPLAIN QUERY PLANSELECT a FROM t1 EXCEPTSELECT d FROM t2 ORDERBY1;
QUERY PLAN`--MERGE (EXCEPT)|--LEFT|`--SCAN TABLE t1 USING COVERING INDEX i1`--RIGHT|--SCAN TABLE t2`--USE TEMP B-TREE FOR ORDER BY