Cómo optimizar la velocidad de consulta de sqlite
1. Factores que afectan el rendimiento de la consulta:
1. menos Cuanto mejor
2. Si se debe 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, pero si no hay índice, la eficiencia de ejecución de la declaración OR 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, (agt;=b) AND (alt;=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 será 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: xgt; ='abc' AND xlt.'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 sobre LIKE, pero si el índice no existe, entonces LIKE todavía no es tan eficiente como un índice. conversión.
3. Procesamiento de varias declaraciones de consulta (consulta compuesta)
1. Ordenar por lt;
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.
Record
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: SELECT a FROM (SELECT x y AS a FROM t1 WHERE zlt; 100) WHERE agt 5
Ejecución El enfoque predeterminado Esta declaración SQL normalmente consiste en realizar una consulta interna, colocar los resultados en una tabla temporal y luego realizar una 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 FROM t1 WHERE zlt; 100 AND agt; 5, el resultado es obviamente el mismo que el anterior, pero en este momento basta con consultar los datos una vez
Además, si hay un índice en la tabla t1, también 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 utiliza DISTINCT, o la consulta externa no utiliza una función agregada
6. Subconsulta La consulta no utiliza 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
10. La subconsulta no usa una función agregada, o la consulta externa no usa LIMIT
11. La subconsulta y la consulta externa no usan la cláusula ORDER BY al mismo tiempo
12. La subconsulta y la consulta externa La consulta de capa no usa LIMIT al mismo tiempo
13 La subconsulta no usa OFFSET
14. La consulta externa no es parte de la 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 ni 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 un 1 DE (
SELECCIONAR x DE la pestaña
UNIONAR TODO
SELECCIONAR y DE la pestaña
UNION TODO
SELECCIONAR abs(z*2) DESDE tab2
)DONDE a!=5 ORDENAR POR 1
Convertir a:
SELECCIONA x 1 DESDE la pestaña DONDE x 1!=5
UNIONA TODOS
SELECCIONA y 1 DESDE la pestaña DONDE y 1!=5
UNA TODOS
SELECCIONE abs(z*2) 1 DE tab2 DONDE abs(z*2) 1!=5
ORDEN POR 1
17. Para consultas compuestas, todas las declaraciones ORDER BY de la consulta principal deben ser referencias simples a las columnas de la subconsulta
18. Si no se usa 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, /* parsing context*/
Select *p , /* Consulta principal o instrucción SELECT externa */
int iFrom, /* p-gt; pSrc-gt; índice en a[] */
int isAgg, /* si es externo Si la capa SELECT usa una función agregada, es Verdadero */
int subqueryIsAgg /* Si la subconsulta usa una función agregada, es Verdadero */
)
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. Porque, para cada fila seleccionada frente a la tabla, si hay un índice, la velocidad para encontrar la fila correspondiente más adelante será muy rápida, pero si no hay un índice, se debe atravesar toda la tabla, lo cual es muy ineficiente. pero esto se ha implementado en la nueva versión One de optimización.
El proceso de optimización es el siguiente:
Para cada tabla que se va 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, entonces se estima que todos los registros calificados son un tercio del total de registros, se estima que el número total de registros es 1.000.000 y el número total de registros se estima en 1.000.000. El costo estimado es también 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 tiene la forma Xlt; opgt; exprgt;, analice la cláusula nuevamente.
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 la operación 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-gt; aiRowEst[i] * inMultiplier; /* Calcula el número de filas */
4. * Calcular costo*/
5.
6. De manera similar, si esta consulta requiere clasificación, agregue NlogN al costo total anterior.
7. el costo obtenido es menor que el costo total, se actualiza el costo total, 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. Luego se puede obtener el orden de anidamiento de todo el bucle anidado. es la implementación óptima. Es el óptimo el que logra el propósito 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.
VI.Índice
En SQLite, existen los siguientes tipos de índice:
1) Índice de una sola columna
2) Varias columnas Í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 no esté 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: =, gt;, lt; , EN et al.
2) Los operadores ENTRE, ME GUSTA y O no se pueden utilizar con índices,
Por ejemplo, ENTRE: SELECT * FROM mytable WHERE myfield BETWEEN 10 and 20
;Esto debe convertirse a:
SELECT * FROM mytable WHERE myfield ENTRE 10 y 20
2:
SELECT * FROM mytable WHERE myfield gt; ; = 10 AND myfield <= 20;
En este punto, si existe un índice en myfield, puede utilizar ese índice, lo que mejora enormemente la velocidad
Otro ejemplo es LIKE: SELECT * FROM mytable WHERE myfield LIKE 'sql';
En este punto se debe convertir a:
SELECT * FROM mytable WHERE myfield gt = 'sql' AND myfield lt; sqm' ;
En este momento, si hay un índice en myfield, se puede utilizar, mejorando así en gran medida la velocidad
Entonces O: SELECT * FROM mytable WHERE myfield = ' abc' OR myfield = 'xyz';
En este punto 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)
SELECCIONAR * DESDE mitabla DONDE micampo 2 = 1;
SELECCIONAR * DESDE mitabla DONDE substr(micampo, 0, 1) = 'w'; mitabla DONDE longitud(micampo) lt;