Cómo optimizar sentencias SQL (texto completo)
Primero: elija el orden de nombre de tabla más efectivo (solo válido en el optimizador SEO/' target='_blank'> basado en reglas): el analizador de ORACLE está en orden de derecha a izquierda al procesar nombres de tabla en En la cláusula FROM, la última tabla escrita en la cláusula FROM (la tabla del controlador de la tabla base) se procesará primero. Si la cláusula FROM contiene varias tablas, se debe seleccionar la tabla con el menor número de filas como tabla base. Si la consulta conecta más de 3 tablas, debe seleccionar una tabla de intersección como tabla base. Las tablas cruzadas son tablas a las que hacen referencia otras tablas.
Segundo: cláusula WHERE en la orden de unión. ORACLE analiza las cláusulas WHERE en orden ascendente. Según este principio, las uniones entre tablas deben escribirse antes de otras condiciones WHERE, y las condiciones que filtran el número máximo de filas deben escribirse al final de la cláusula WHERE.
Tercero: Evite el uso de "*" en la cláusula SELECT: durante el proceso de análisis, ORACLE convertirá "*" a todos los nombres de columnas en orden consultando el diccionario de datos, lo que significa que llevará más tiempo. Varias veces
Cuarto: Reducir el acceso a la base de datos get='_blank'>. white'>:
Quinto: En SQL*Plus, SQL*Forms y Pro*C Restablecer ARRAYSIZE El parámetro in puede aumentar el número de accesos a la base de datos get='_blank'> cada vez. en blanco'>La cantidad de datos recuperados mediante el acceso a la base de datos, el valor recomendado es 200
Sexto: use la función DECODE para reducir el tiempo de procesamiento: use la función DECODE para evitar escanear repetidamente los mismos registros o conectarse repetidamente a la misma mesa.
Séptimo: Consolide el acceso a la base de datos get='_blank'> simple y no relacionado: si tiene varias declaraciones de consulta de base de datos get='_blank'> simples, puede consolidarlas en una sola consulta (incluso si no hay relación entre ellas)
Octavo: Eliminar filas duplicadas: La forma más efectiva de eliminar filas duplicadas (porque se usa ROWID)
Ejemplo DELETE FROM EMP E WHERE E .ROWID > (SELECT MIN(X.ROWID) DESDE EMP eliminar. Cuando se eliminan registros de una tabla, los segmentos de reversión generalmente se usan para almacenar información recuperable. Si no hay una transacción COMMIT, ORACLE restaurará los datos al estado anterior a la eliminación (para ser precisos, al estado anterior a la ejecución del comando de eliminación). Mientras se ejecuta el comando, los datos no se pueden recuperar. Por lo tanto, se llaman menos recursos y el tiempo de ejecución es corto. (Nota del traductor: TRUNCATE solo se aplica para eliminar toda la tabla, TRUNCATE es DDL, no DML)
Décimo: intente usar COMMIT: cuando sea posible, intente usar COMMIT en el programa, para que el rendimiento del programa se mejorará y la demanda de COMMIT también se liberará debido a la reducción de los recursos de COMMIT: recursos liberados por COMMIT a. Información utilizada para restaurar datos en el segmento de reversión b. Bloqueos adquiridos por declaraciones del programa c. Los costos internos incurridos por ORACLE para administrar los tres recursos anteriores
Séptimo: Reemplace la cláusula HAVING con la cláusula WHERE: Evite el uso de la cláusula HAVING porque esta cláusula solo se usa después de que se completa la recuperación. establecer después de todas las filas. Este proceso requiere operaciones como clasificar y totalizar.
Puede minimizar esta situación limitando el número de registros en la cláusula WHERE. (No Oracle) On, donde y have se pueden agregar a la cláusula condicional. On se implementa primero, seguido de cerca por donde y por último, porque on se implementa primero después de filtrar los registros que no cumplen con las condiciones. realizar estadísticas, puede reducir las operaciones intermedias de procesamiento de datos, y debería ser el más rápido, donde también debería ser más rápido que tener, porque primero filtra los datos y luego los suma, y usa on antes de conectar las dos tablas, por lo que en. uno En la tabla, solo se comparan dónde y tener. En este caso de estadísticas de consulta de tabla única, si las condiciones a filtrar no involucran los campos a calcular, entonces sus resultados son los mismos, excepto donde se puede usar la tecnología Rushmore, pero no se puede, y esta última es más lenta en velocidad. , si el campo a calcular involucra el campo a calcular, significa que el valor de este campo es incierto antes del cálculo, siga el flujo de trabajo escrito anteriormente. Donde opera antes del cálculo, mientras que opera después del cálculo, por lo que en este caso los resultados serán diferentes. Si se trata de una consulta de conexión de varias tablas, el tiempo de operación de on es anterior a dónde. El sistema primero combina varias tablas en una tabla temporal según las condiciones de conexión entre las tablas, luego filtra según dónde, luego calcula y luego filtra según el cálculo posterior. Se puede ver que para que las condiciones del filtro desempeñen un papel correcto, primero debe comprender cuándo deben entrar en vigor las condiciones y luego decidir colocarlas allí
Doce: restaurar la consulta a la tabla : incluye la subconsulta en la declaración SQL, preste especial atención a restaurar la consulta en la tabla. Por ejemplo, SELECT TAB_NAME FROM TABS WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
Decimotercero: Utilice funciones internas para mejorar la eficiencia de SQL. Lo más importante para mejorar la eficiencia de SQL es utilizar funciones intrínsecas para resolver problemas. En la práctica, es muy significativo poder dominar el método mencionado anteriormente de usar funciones para resolver problemas
Catorce: use alias de tabla: cuando conecte varias tablas en una declaración SQL, use alias de tabla y cree un alias de tabla para cada tabla. Agregue un prefijo de alias a cada columna. Esto mejorará el tiempo de análisis y minimizará los errores de sintaxis causados por columnas ambiguas.
Decimoquinto: Utilice EXISTS en lugar de IN, utilice NOT EXISTS en lugar de NOT IN: En muchas consultas basadas en tablas base, para cumplir una determinada condición, muchas veces es necesario conectarse a otra tabla. En este caso, utilizar EXISTE (o NO EXISTE) normalmente hará que la consulta sea más eficiente. En una subconsulta, la cláusula NOT IN realiza una clasificación y fusión internas. En cualquier caso, NOT IN es el menos eficiente (porque realiza un recorrido completo de la tabla en la subconsulta). Para evitar usar NOT IN, podemos reescribirlo como una combinación externa o NOT EXISTS.
Ejemplo:
(Eficiente) SELECCIONE * DESDE EMP (tabla base) DONDE EMPNO > 0 Y EXISTE (SELECCIONE 'X' DESDE DEPT DONDE DEPT.DEPTNO = EMP.DEPTNO AND LOC = EMP.DEPTNO)DEPTNO AND LOC = 'MELB') (ineficiente) SELECT * FROM EMP (tabla base) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB')
Decimosexto: Identificar sentencias SQL "ineficientes ejecución": Aunque varias herramientas gráficas para SQLseo/' target='_blank'>optimización surgen sin cesar, escribir sus propias herramientas SQL para resolver el problema es siempre la mejor manera: SELECT EXECUTIONS, DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS )/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Lee _per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS> 0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 OR BYDER 4 DESC;
Decimoséptimo: Utilice índices para mejorar la eficiencia: Los índices son la parte conceptual de la tabla y se utilizan para mejorar la eficiencia en la recuperación de datos. ORACLE utiliza una compleja estructura de árbol B autoequilibrada. En términos generales, consultar datos a través de índices es más rápido que el escaneo completo de la tabla. El optimizador ORACLEseo/' target='_blank'> utiliza índices cuando ORACLE calcula la mejor ruta para ejecutar consultas y actualizar declaraciones. Asimismo, los índices se pueden utilizar para mejorar la eficiencia al unir varias tablas. Otro beneficio de utilizar un índice es que verifica la unicidad de la clave principal. Casi cualquier columna se puede indexar utilizando el tipo de datos LONG o LONG RAW. En general, los índices son particularmente efectivos en tablas grandes. Por supuesto, también encontrará que los índices se pueden utilizar para mejorar la eficiencia al escanear tablas más pequeñas. Aunque el uso de índices puede mejorar la eficiencia de las consultas, cabe señalar que esto tiene un costo. Los índices requieren espacio de almacenamiento y mantenimiento regular, y cada vez que se agregan o restan registros de la tabla o se modifican columnas de índice, el índice en sí se modifica. Esto significa que cada INSERT, DELETE y UPDATE grabado agrega 4 o 5 E/S de disco. Los índices innecesarios pueden ralentizar los tiempos de respuesta de las consultas porque los índices requieren espacio de almacenamiento y procesamiento adicionales. Por lo tanto, es necesario reconstruir los índices periódicamente: evite usar DISTINCT en la cláusula SELECT cuando envíe consultas que contengan información de tablas de uno a varios, como departamentos y empleados. En general, considere usar EXIST en lugar de DISTINCT, lo que resultará en consultas más rápidas porque el módulo del kernel RDBMS creará un nuevo índice usando una subclase del índice. Una vez que se cumplen las condiciones de la subconsulta, el módulo principal RDBMS devuelve los resultados.
Ejemplo: (eficiente): SELECCIONE DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D, EMP E WHERE D.DEPT_NO = E.DEPT_NO (eficiente): seleccione DEPT_NO,DEPT_NAME FROM DEPT, EMP E WHERE D.DEPT_NO = E.DEPT_NODEPT_NO);< / p>
Decimonoveno: la declaración server/' target='_blank'>sql usa letras mayúsculas; debido a que Oracle siempre analiza la declaración server/' target='_blank'>sql primero, las letras minúsculas se convertirán en letras mayúsculas y luego ejecutar
Veinte: ¡Use el conector "+" lo menos posible para conectar cadenas en código Java!
No. 21: Evite el uso de NOT en columnas indexadas. Generalmente, evitaremos el uso de NOT en columnas indexadas porque NOT puede producir los mismos resultados que usar funciones en columnas indexadas. Cuando ORACLE encuentra "NOT", deja de usar el índice y realiza un escaneo completo de la tabla.
22: Evite utilizar cálculos en columnas indexadas. Si la columna indexada es parte de una función en la cláusula WHERE, el optimizador utiliza un escaneo completo de la tabla en lugar del índice. Ejemplo: Ineficiente: SELECCIONAR ?DEL DEPARTAMENTO DONDE SAL * 12 > 25000; Eficiente: SELECCIONAR ? DEL DEPARTAMENTO DONDE SAL > 25000/12;
No 23: Utilice >= en lugar de > Eficiente: SELECCIONAR * DESDE. EMP WHERE DEPTNO >=4 Ineficiente: SELECT * FROM EMP WHERE DEPTNO >3 La diferencia entre los dos es que en el primer caso, el sistema de gestión de base de datos saltará a la primera tabla. La diferencia entre los dos es que en el primer caso, el sistema de gestión de base de datos saltará directamente a la primera tabla con DEPT igual a 4, mientras que en el segundo caso, primero encontrará la fila con DEPTNO=3 y luego escaneará a la primera tabla con DEPT mayor que 3. ====================================== Generador de código integrado SpringMVC_mybatis o hibernate+ehcache L2 cache_shiro_druid_bootstrap_HTML5 java Enterprise pestaña framework versión no maven /u/2347562/blog/400728 ================================= === =====
Vigésimo cuarto: Reemplazar OR con UNION (para columnas indexadas) Generalmente, será mejor reemplazar OR en la cláusula WHERE con UNION. El uso de OR en columnas indexadas da como resultado un escaneo completo de la tabla. Tenga en cuenta que las reglas anteriores solo se aplican a varias columnas de índice. Si hay columnas no indexadas, no seleccionar O puede reducir la eficiencia de la consulta. En el siguiente ejemplo, tanto LOC_ID como REGION tienen índices.
Eficiente: SELECCIONE LOC_ID, LOC_DESC, REGIÓN DESDE LA UBICACIÓN DONDE LOC_ID = 10 UNION SELECCIONE LOC_ID, LOC_DESC, REGIÓN DESDE LA UBICACIÓN DONDE REGIÓN = "MELBOURNE" Ineficiente: SELECCIONE LOC_ID, LOC_DESC, REGIÓN DESDE LA UBICACIÓN DONDE LOC_ID = 10 O REGIÓN = "MELBOURNE" " Si insiste en usar OR, debe devolver la columna de índice superior con la menor cantidad de registros.
Artículo 25: Utilice IN para reemplazar OR. Esta es una regla simple y fácil de recordar, pero el efecto de ejecución real debe probarse. En ORACLE8i, las rutas de ejecución de los dos parecen ser las mismas. mismo. No válido: ¿SELECCIONAR? desde la ubicación donde loc_id = 10 o loc_id = 20 o loc_id = 30 ¿selección eficiente? DESDE LA UBICACIÓN DONDE LOC_IN IN (10,20,30);
26: Evite el uso de IS en columnas de índice NULL y NO ES NULO evite usar cualquier columna en el índice que pueda ser nula, de lo contrario ORACLE no podrá usar el índice. Para un índice de una sola columna, si una columna contiene valores nulos, el registro no existirá en el índice. Para un índice compuesto, si cada columna tiene un valor nulo, el registro no existirá en el índice. Existe un registro en el índice si al menos una columna no es nula. Por ejemplo, si se establece un índice único en las columnas A y B de la tabla, y hay un registro en la tabla con el valor A, B (123, nulo), ORACLE no aceptará el siguiente registro con el mismo A, Registro de valor B (123, nulo) (inserción). Sin embargo, si todas las columnas del índice son nulas, ORACLE considerará que toda la clave es nula y nulo no es igual a nulo. Entonces podrías insertar 1000 registros con el mismo valor clave y, por supuesto, ¡todos estarían vacíos! Dado que los valores NULL no existen en la columna indexada, una comparación NULL en la columna indexada en la cláusula WHERE hará que ORACLE deshabilite el índice. No válido: (el índice falló) SELECT ?DROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL Válido: (el índice es válido) SELECT ?DROM DEPARTMENT WHERE DEPT_CODE >=0;
27: Utilice siempre la primera columna del índice : si cuando se crea un índice en varias columnas, el optimizador no usará la primera columna del índice a menos que la cláusula where haga referencia a la columna, seo/' target='_blank'& El optimizador elegirá usar. el índice. También hay una regla simple pero importante, es decir, si solo se hace referencia a la segunda columna del índice, el optimizador utilizará un escaneo completo de la tabla e ignorará el índice
28: Si es posible, reemplace UNION con UNION-ALL: cuando la declaración SQL requiere UNION dos conjuntos de resultados de la consulta, los dos conjuntos de resultados están representados por UNION-ALL. Cuando una declaración SQL requiere UNION dos conjuntos de resultados de consulta, se combinan en forma de UNION-ALL y luego se ordenan antes de generar el resultado final. Si se utiliza UNION ALL en lugar de UNION, no se requiere clasificación. De este modo se mejora la eficiencia. Tenga en cuenta que UNION ALL duplicará los mismos registros en ambos conjuntos de resultados. Por lo tanto, se debe analizar la viabilidad de utilizar UNION ALL en función de las necesidades del negocio. UNION ordena el conjunto de resultados, que utiliza la memoria SORT_AREA_SIZE. Optimizar la memoria para SEO/' target='_blank'> es muy importante.
El siguiente SQL se puede utilizar para consultar el consumo de clasificación Ineficiente: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DIC-95' UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DIC-95' Eficiente: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DIC-95' UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DIC-95'
Página 29: Reemplace ORDER BY con WHERE: ORDER Cláusula BY Utilice índices sólo bajo dos condiciones estrictas. Todas las columnas de ORDER BY deben estar contenidas en el mismo índice y permanecer en el mismo orden dentro del índice. Todas las columnas de ORDER BY deben definirse como no nulas. Los índices utilizados en la cláusula WHERE y los índices utilizados en la cláusula ORDER BY no deben estar concatenados. La tabla de ejemplo DEPT contiene lo siguiente: DEPT_CODE PK NOT NULL DEPT_DESC NOT NULL DEPT_TYPE NULL Ineficiente: (sin usar índice) SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE Eficiente: (usando índice) SELECT DEPT_CODE FROM DEPT _CODE FROM DEPT WHERE DEPT_TYPE > 0
Treinta: evite cambiar el tipo de columnas de índice: al comparar datos de diferentes tipos, ORACLE realizará automáticamente conversiones de tipos simples en las columnas. Supongamos que EMPNO es una columna de índice de tipo numérico. SELECT ?FROM EMP WHERE EMPNO = '123' De hecho, después de la conversión de tipo ORACLE, esta declaración se convertirá a SELECT ?FROM EMP WHERE EMPNO = TO_NUMBER('123') Afortunadamente, la conversión de tipo no ocurre en la columna de índice, el El propósito del índice no ha cambiado. Ahora, supongamos que EMP_TYPE es una columna indexada de tipo carácter. SELECT ?FROM EMP WHERE EMP_TYPE = 123 ORACLE Convierta esta declaración a SELECT ?FROM EMP WHERE EMP_TYPE = 123 ORACLE Convierta esta declaración a SELECT ?FROM EMP WHERE EMP_TYPE = 123 Esta declaración ha sido convertida por ORACLE a: SELECT ?SELECT ?Para evitar ORACLE convierte implícitamente su SQL, es mejor realizar la conversión de tipos explícitamente. Tenga en cuenta que al comparar caracteres y valores numéricos, ORACLE dará prioridad a la conversión de tipos numéricos en tipos de caracteres
31: Cláusulas WHERE a tener en cuenta: algunas cláusulas WHERE de la instrucción SELECT no utilizan índices. A continuación se muestran algunos ejemplos. En el siguiente ejemplo, (1) '! =' no se indexará. Recuerde, un índice sólo le dice lo que está en la tabla, no lo que no está en la tabla. (2) '||' es una función de concatenación de caracteres. Al igual que otras funciones, la indexación está deshabilitada. (3) '+' es una función matemática. Al igual que con otras funciones matemáticas, la indexación está deshabilitada. (4) Las mismas columnas de índice no se pueden comparar entre sí, lo que dará como resultado un escaneo completo de la tabla.
Treinta y dos: a. Si la cantidad de datos recuperados supera el 30% del número de registros de la tabla.
b. En algunos casos, el uso de un índice puede ser más lento que un escaneo completo de la tabla, pero esto es solo una diferencia de un orden de magnitud. En términos generales, la eficiencia del uso de índices es varias veces o incluso miles de veces mayor que la del escaneo completo de la tabla.
Treinta y tres: Evite operaciones que consuman recursos: las declaraciones SQL que usan DISTINCT, UNION, MINUS, INTERSECT y ORDER BY iniciarán el motor SQL para realizar funciones de clasificación que consumen recursos. DISTINCT requiere solo una clasificación, mientras que otras operaciones requieren al menos dos clasificaciones. En general, las sentencias SQL que utilizan UNION, MINUS e INTERSECT se pueden reescribir de otras formas. Si el SORT_AREA_SIZE de tu base de datos get='_blank'> está bien ajustado, considera usar UNION, MINUS e INTERSECT, después de todo, son muy legibles
34: seo/' target='_blank '> Optimizar Agrupar por. Mejore la eficiencia de las declaraciones GROUP BY filtrando registros innecesarios antes de GROUP BY. Las dos consultas siguientes devuelven los mismos resultados, pero la segunda consulta es significativamente más rápida. Baja eficiencia: SELECCIONE TRABAJO, PROMEDIO(SAL) DEL GRUPO EMP POR TRABAJO QUE TIENE TRABAJO = 'PRESIDENTE' O TRABAJO = 'GERENTE' Alta eficiencia: SELECCIONE TRABAJO, PROMEDIO(SAL) DE EMP DONDE TRABAJO = 'PRESIDENTE' O TRABAJO = 'ADMINISTRADOR ' GRUPO por TRABAJO
Los treinta y cuatro puntos resumidos anteriormente resumen cómo optimizar las declaraciones SQL. Espero que les guste.
Artículos que te pueden interesar: Cómo optimizar sentencias SQL 30 métodos comúnmente utilizados en consultas MySQL Optimización de sentencias SQL usando EXISTS en lugar de IN, NOT EXISTS en lugar de NOT IN 30 ejemplos de métodos de optimización de sentencias SQL ( recomendado) Información práctica sobre la optimización de declaraciones SQL. 30 ejemplos de métodos de optimización de declaraciones SQL. Optimice el análisis de los planes de ejecución de declaraciones SQL. continúa) Explicación detallada de los pasos generales de optimización de sentencias SQL