Cómo optimizar las consultas sqlite
1. Factores que afectan el rendimiento de la consulta:
1. El número de filas recuperadas de la tabla, menor. cuanto mejor
2. Ya sea para ordenar.
3. Si se utiliza el índice.
4. Formulario de declaración de consulta
Varias transformaciones de optimización de consultas
1. Para una sola columna de una sola tabla, si hay una cláusula como T.C = expr, y están conectados con el operador OR, por ejemplo: x = expr1 OR expr2 = x OR x = expr3 En este momento, debido a OR, en SQLite, las filas de la tabla no se pueden recuperar usando el índice. En este momento, dado que SQLite no puede usar el índice para la optimización para OR, se puede convertir en una cláusula con el operador IN: x IN (expr1, expr2, expr3) De esta manera, el índice se puede usar para la optimización y el El efecto es muy obvio, sin embargo, si la declaración OR se ejecuta sin un índice, la eficiencia será ligeramente mejor que la de la declaración IN.
2. Si un operador de cláusula es ENTRE, los índices no se pueden usar para la optimización en SQLite, por lo que la conversión equivalente correspondiente es: como por ejemplo: a ENTRE b Y c se puede convertir a: (a ENTRE b Y c) Y (a & gt;=b) Y (a & lt;=c). En la cláusula anterior, (a>=b) AND (a<=c) se establecerá como dinámica y es una cláusula de (a BETWEEN b AND c), si la declaración BETWEEN ha sido codificada, la cláusula se ignorará. si hay un índice disponible para el cual la cláusula ya cumple la condición, se ignorará la cláusula principal.
3. Si el operador de la unidad es LIKE, se realizará la siguiente conversión: x LIKE 'abc%', convertido a: x>='abc' AND x<.'abd'. Debido a que LIKE en SQLite no se puede optimizar junto con un índice, una conversión está muy alejada de una conversión si el índice existe, ya que no tiene ningún efecto en LIKE, pero si el índice no existe, entonces LIKE aún no es tan eficiente como un conversión.
3. Procesamiento de varias declaraciones de consulta (consulta compuesta)
1. Declaración de consulta:
Método de ejecución: uno de UNION ALL, UNION, EXCEPT o INTERSECT. El proceso de ejecución de esta declaración consiste en ejecutar primero selectA y selectB y ordenarlos, y luego escanear y procesar los dos resultados. Los procesos de ejecución de las cuatro operaciones anteriores son diferentes y el proceso de ejecución se divide en siete subprocesos:<. /p>
outA: coloca el resultado de selectA en una línea. outA: coloca la fila de resultados de selectA en el conjunto de resultados final
outB: coloca la fila de resultados de selectA en el conjunto de resultados final (solo para las operaciones UNION y UNION ALL, otras operaciones no se colocan en el conjunto final conjunto de resultados)
AltB: cuando el registro actual de selectA es más pequeño que el registro actual de selectB, escanea y ordena los resultados de selectB.
Registro
AeqB: Cuando el registro actual de selectA es igual al registro actual de selectB
AgtB: Cuando el registro actual de selectA es mayor que el registro actual de selectB
EofA: al recorrer los resultados de selectA
EofB: cuando se completa el recorrido de los resultados de selectB
A continuación se explica cómo se realizan estas cuatro operaciones:
Orden de ejecución
p>UNION TODO
UNION
EXCEPTO
INTERSECT
AltB:
outA, siguienteA
outA, nextA
outA, nextA
siguienteA
AeqB:
outA, siguienteA
siguienteA
siguienteA
outA, siguienteA
AgtB:
outB, siguienteB
outB, siguienteB
siguienteB
siguienteB
siguienteB
EofA:
outB, nextB
outB, nextB
detener
detener
EofB:
outA, nextA
outA, nextA
outA,nextA
halt
2. Si es posible, puede convertir la declaración usando la consulta GROUP BY en una. Consulta DISTINCT, porque GROUP BY a veces puede usarse índices, mientras que DISTINCT no usa índices.
Cuarto, aplanamiento de subconsultas
Ejemplo: SELECCIONAR a FROM (SELECCIONAR x+y COMO a FROM t1 WHERE z<100) WHERE a>5
El valor predeterminado El método para ejecutar esta declaración SQL generalmente es ejecutar la consulta interna, colocar los resultados en una tabla temporal y luego realizar la consulta externa en esta tabla. La tabla se utiliza para consultas externas, lo que requiere que los datos se procesen dos veces. Además, esta tabla temporal no tiene índice, por lo que las consultas externas no se pueden optimizar. Si se procesa el SQL anterior, se puede obtener la siguiente declaración SQL: SELECT. x + y COMO a DESDE t1 DONDE z<100 Y a>5, el resultado es obviamente el mismo que el anterior, pero en este momento es suficiente consultar los datos solo una vez
Además, si hay es un índice en la tabla t1, también se puede evitar atravesar toda la tabla.
Condiciones para utilizar el método de aplanamiento para optimizar SQL:
1. La subconsulta y la consulta externa no utilizan funciones agregadas al mismo tiempo
2. La subconsulta no utiliza funciones agregadas o la consulta externa no es una combinación de la tabla
3. La subconsulta no es el operando derecho de la combinación externa izquierda
4. la subconsulta no usa DISTINCT, o la consulta externa no es una combinación de la tabla
5. La subconsulta no es una combinación de la tabla
6. de la tabla
7. La subconsulta no es una unión de la tabla
8. La subconsulta no es una unión de tablas
9. unión de tablas
10. La subconsulta no es una unión de tablas
11. La subconsulta no es una unión de tabla
12. /p>
5. La subconsulta no usa DISTINCT, o la consulta externa no usa una función agregada
6. Subconsulta La consulta no usa una función agregada, o la consulta externa no. use la palabra clave DISTINCT
7. La subconsulta tiene una declaración FROM
8. La subconsulta no usa LIMIT o la consulta externa no es una combinación de tabla.
9. La subconsulta no es una combinación externa izquierda o la consulta externa no es una combinación externa derecha
10 La subconsulta no usa LIMIT o la consulta externa no usa una función agregada
p>
10. La subconsulta no utiliza una función agregada o la consulta externa no utiliza LIMIT
11. La subconsulta y la consulta externa no utilizan la cláusula ORDER BY al mismo tiempo.
12. La subconsulta y la consulta externa no usan LIMIT al mismo tiempo
13. La subconsulta no usa OFFSET
14. no es parte de una consulta compuesta, o la subconsulta no usa las palabras clave ORDER BY y LIMIT al mismo tiempo
15. La consulta externa no usa funciones agregadas y la subconsulta no contiene ORDER. BY
16. La subconsulta compuesta está aplanada: la subconsulta no es una consulta compuesta o es una consulta compuesta UNION ALL. O es una consulta compuesta UNION ALL, pero se compone de múltiples consultas de funciones no agregadas. Su consulta principal no es una consulta compuesta de la subconsulta, ni utiliza funciones agregadas o consultas DISTINCT, y no hay otras tablas en. la declaración FROM. O subconsulta, la consulta principal y la subconsulta pueden contener declaraciones WHERE, que estarán sujetas a las condiciones 11, 12 y 13 anteriores.
Ejemplo SELECCIONAR a+1 DESDE (
SELECCIONAR x DESDE la pestaña
UNIONAR TODO
SELECCIONAR y DESDE la pestaña
UNIÓN TODO
SELECCIONAR abs(z*2) DESDE tab2
)DONDE a!=5 ORDENAR POR 1
Convertir a:
SELECCIONE x+1 DE la pestaña DONDE x+1!=5
UNIÓN TODO
SELECCIONE y+1 DE la pestaña DONDE y+1!=5
UNIÓN TODO
SELECCIONAR abs(z*2)+1 DE tab2 DONDE abs(z*2)+1!=5
ORDENAR POR 1
17. Si la subconsulta es una consulta compuesta, todas las declaraciones ORDER BY de la consulta principal deben ser referencias simples a las columnas de la subconsulta
18. No se utiliza LIMIT o la consulta externa no tiene una declaración WHERE.
El aplanamiento de subconsultas se logra especializando la función:
static int flattenSubquery(
Parse *pParse, /* Contexto de análisis*/
Seleccione *p, /* Consulta principal o instrucción SELECT externa */
int iFrom, /* p->pSrc-> , /* Verdadero si el SELECT externo usa una función agregada */
int subqueryIsAgg /* Verdadero si la subconsulta usa una función agregada */
)
Se implementa en el archivo Select.c. archivo c. Obviamente, para consultas complejas, si se cumplen las condiciones anteriores, la consulta se puede optimizar aplanando la declaración de la consulta. Si tiene un índice, ¡el efecto será aún mejor!
Quinto, unir consulta
Al devolver los resultados de la consulta, la tabla relevante debe estar conectada a cada fila. En SQLite, esto se lograba a través de bucles anidados, en los primeros días. En la versión, el más a la izquierda es el bucle más externo y el más a la derecha es el bucle más interno. Al conectar dos o más tablas, si hay un índice, se colocará en el bucle interno. nuevo En esta versión, se ha implementado esta optimización.
En la nueva versión se ha implementado esta optimización.
El proceso de optimización es el siguiente:
Para cada tabla a consultar, cuente la información del índice de la tabla y primero asigne el costo a SQLITE_BIG_DBL (constante definida por el sistema):
p>
1) Si no hay índice, verifique si el ID de fila de la tabla tiene alguna condición de consulta:
1. Si hay Rowid=EXPR, devuelva el costo estimado de esta tabla, y la tabla de costos es 0, el número de registros obtenidos por la consulta es 1, y el costo estimado de completar esta tabla,
2. pero hay rowid IN (...), y IN es una lista, devuelve El número de registros es el número de elementos en la lista IN y el costo estimado es NlogN,
3. no es una lista, sino el resultado de una subconsulta, dado que no se puede determinar la subconsulta específica, solo se estima un valor, la cantidad de registros devueltos es 100 y el costo es 200.
4. Si el ID de fila de la consulta es un rango de consulta, se estima que todos los registros elegibles son un tercio del total de registros, se estima que el número total de registros es 1.000.000 y se estima. El costo también es el número de registros.
5.
6. Si el costo obtenido en este momento es menor que el costo total, actualice el costo total, de lo contrario no actualice.
2) Si hay operadores OR en la cláusula WHERE, separe todas las cláusulas conectadas por estos OR y analícelas nuevamente.
1. Si hay cláusulas compuestas por conectores AND, las cláusulas conectadas por AND se vuelven a analizar por separado.
2. Si la cláusula de conexión es de la forma X
3. El siguiente paso es calcular el coste total de todo el par de operaciones OR. Si el costo obtenido en este momento es menor que el costo total, se actualiza el costo total, en caso contrario no se actualiza.
3) Si hay un índice, cuente la información del índice de cada tabla. Para cada índice:
1. número de columna que se puede utilizar para La cláusula WHERE correspondiente del índice (el operador debe ser = o IN (...)), si no se encuentra, salga del bucle para cada índice, si se encuentra, determine el funcionamiento de la cláusula. Si el operador de la cláusula es =, no hay costo extra; si es IN (subselección), el costo extra estimado en el multiplicador es 25 si es IN (lista), el costo extra es N (N es número; de columnas de la lista).
2. Luego calcule el costo total y el número total de registros de resultados de la consulta y el costo.
3. nRow = pProbe->aiRowEst[i] * inMultiplier;/* Calcula el número de filas*/
4. Calcule el costo */
5.
6. De manera similar, si esta consulta requiere clasificación, agregue NlogN al costo total anterior
7. tiempo Si el costo obtenido es menor que el costo total, entonces el costo total se actualiza, de lo contrario no se actualiza.
4) A través del proceso de optimización anterior, puede obtener el costo total de consulta de una tabla (es decir, la suma de los costos anteriores) y luego realizar la misma operación en la segunda tabla, y así adelante, hasta que la cláusula FROM Todas las tablas hayan calculado sus costos respectivos, y finalmente tome la más pequeña y úsela como la capa más interna del bucle anidado, y luego se puede obtener el orden de anidamiento de todo el bucle anidado. , es el óptimo a lograr con fines de optimización.
5) A través del proceso de optimización anterior, puede obtener el costo total de consulta de una tabla (es decir, la suma de los costos anteriores) y luego realizar la misma operación en la segunda tabla, y así adelante, hasta que la cláusula FROM Todas las tablas hayan calculado sus costos respectivos, y finalmente tome la más pequeña y úsela como la capa más interna del bucle anidado, y luego se puede obtener el orden de anidamiento de todo el bucle anidado. , es el óptimo a lograr con fines de optimización.
5) Por lo tanto, el orden de anidamiento de los bucles no es necesariamente el mismo que el orden en la cláusula FROM, porque durante la ejecución, se utilizará la optimización del índice para reorganizar el orden.
Índice VI.
En SQLite, existen los siguientes tipos de índice:
1) Índice de una sola columna
2) Multi- índice de columna Índice
3) Índice único
4) Para clave primaria declarada: Para una clave primaria declarada como CLAVE PRIMARIA INTEGER, la columna se ordenará de forma predeterminada, por lo que aunque sea no en el diccionario de datos Genera un índice pero tampoco crea un índice sobre él, sino que funciona como un índice. Por lo tanto, si crea un índice separado en esta clave principal, desperdiciará espacio y no proporcionará ningún beneficio.
Notas sobre el uso de índices:
1) Para tablas muy pequeñas, no es necesario crear índices
2) Si las inserciones y actualizaciones se realizan con frecuencia en la tabla Para operar, los índices deben usarse moderadamente
3) No se deben crear demasiados índices en la tabla. Si se crean demasiados índices, es posible que SQLite no seleccione el mejor índice para ejecutar la consulta. Si no elige el mejor índice para ejecutar la consulta, la solución es construir índices anidados
Tiempo para usar índices:
1) Operadores: =, >, <, IN , etc. .
SELECCIONE * DE mytable WHERE myfield >= 10 AND myfield <= 20;
En este momento, si hay un índice en myfield, puede usar el índice, lo que mejora enormemente la velocidad
Otro ejemplo es LIKE: SELECT * FROM mytable WHERE myfield LIKE 'sql%';
Esto debe convertirse a:
SELECT * FROM mytable WHERE myfield >= 'sql' AND myfield < 'sqm';
En este punto, si hay un índice en myfield, puedes usarlo, mejorando así enormemente la velocidad
Luego OR ：SELECT * FROM mytable WHERE myfield = 'abc' OR myfield = 'xyz';
En este momento se debe convertir a:
SELECT * FROM mytable WHERE myfield IN ('abc ', 'xyz');
En este punto, si hay un índice en myfield, se puede mejorar mucho
3) A veces no hay índice y se debe recorrer toda la tabla (demostración del programa)
SELECT * FROM mytable WHERE myfield % 2 = 1
SELECT * FROM mytable WHERE substr(myfield, 0, 1) = ' w';
SELECCIONAR * DESDE mitabla DONDE longitud(micampo) < 5;