Comparta varios consejos para optimizar comandos de sentencias SQL

Este artículo comparte varias técnicas de optimización de comandos de declaraciones SQL. Los estudiantes que necesiten saber más pueden consultarlas.

1. En la aplicación, asegúrese de que el número de accesos a la tabla se minimice en función de la realización de las funciones a través de

parámetros de búsqueda, intente reducir el número de filas; acceder a la tabla y minimizar el conjunto de resultados, reduciendo así la carga en la red, las operaciones que se pueden separar deben procesarse por separado para mejorar la velocidad de respuesta cada vez que se utiliza SQL en la ventana de datos;

El índice se coloca en la primera columna de la selección; la estructura del algoritmo es lo más simple posible; al realizar consultas, no utilice demasiados comodines como la instrucción SELECT * FROM T1. tantas columnas como desee utilizar, como: SELECT COL1, COL2 FROM

T1; intente limitar el número de filas en el conjunto de resultados si es posible, como: SELECT TOP 300

COL1, COL2, COL3 DE T1, porque algunos En este caso, el usuario no necesita tantos datos. No utilice cursores de bases de datos en

aplicaciones. Los cursores son herramientas muy útiles, pero requieren más sobrecarga que usar sentencias SQL regulares orientadas a conjuntos

extraer en un orden específico Búsqueda de datos; .

2. Evite el uso de tipos de datos incompatibles. Por ejemplo, float e int, char y varchar, binario y

varbinary son incompatibles. La incompatibilidad de tipos de datos puede impedir que el optimizador realice algunas operaciones de optimización que de otro modo podrían realizarse. Por ejemplo:

SELECCIONE el nombre DEL empleado DONDE salario > 60000

En esta declaración, si el campo de salario es de tipo dinero, es difícil para el optimizador optimizarlo porque 60000

Es un número entero. Deberíamos convertir el tipo entero al tipo de moneda durante la programación, en lugar de esperar la conversión en tiempo de ejecución.

3. Intente evitar realizar operaciones de función o expresión en campos en la cláusula WHERE, lo que hará que el motor deje de usar el índice y realice un escaneo completo de la tabla. Por ejemplo:

SELECCIONAR * DE T1 DONDE F1/2=100

Debe cambiarse a:

SELECCIONAR * DE T1 DONDE F1=100*2

SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=?5378?

Debe cambiarse a:

SELECT * FROM RECORD WHERE CARD_NO LIKE ? 5378%

SELECT número_miembro, nombre, apellido DE los miembros

DONDE DATEDIFF(yy,datofbirth,GETDATE()) > 21

Debe cambiarse a:

SELECCIONE número_miembro, nombre, apellido DE los miembros

DONDE fechadenacimiento Es decir: cualquier operación en la columna dará como resultado un escaneo de la tabla, que incluye funciones de base de datos, expresiones de cálculo, etc., consulta

< Cuando p>, mueva la operación al lado derecho del signo igual tanto como sea posible.

4. Evite el uso de operadores como != o ＜＞, IS NULL o IS NOT NULL, IN, NOT IN, etc.

Porque esto hará que el sistema no pueda usar el índice. En cambio, solo puede buscar datos en la tabla directamente. Por ejemplo:

SELECT id FROM Employee WHERE id != 'B%'

El optimizador no podrá determinar el número de filas que alcanzará en el índice, por lo que Tendrá que buscar en todas las filas de la tabla.

5. Intente utilizar campos numéricos. A algunos desarrolladores y administradores de bases de datos les gusta diseñar campos que contengan información numérica

como tipos de caracteres. Esto reduce el rendimiento de consultas y uniones y aumenta la sobrecarga de almacenamiento. . Esto se debe a que el motor procesa la consulta y la concatena para comparar cada carácter de la cadena uno por uno, mientras que para los tipos numéricos solo una comparación es suficiente.

6. Utilice las cláusulas EXISTS y NOT EXISTS de forma adecuada. Como se muestra a continuación:

1.SELECCIONE SUMA(T1.C1)DE T1 DONDE(

(SELECCIONE CONTADOR(*)DE T2 DONDE T2.C2=T1.C2>0)

2.SELECT SUM(T1.C1) FROM T1WHERE EXISTS(

SELECT * FROM T2 WHERE T2.C2=T1.C2)

Ambos se generan El mismo resultado, pero este último es obviamente más eficiente que el primero, porque este último no genera muchos bloqueos

Si desea calibrar el escaneo de la tabla o el escaneo del índice para verificar si un determinado registro. existe en la tabla, no use (*), lo cual es ineficiente y desperdicia recursos del servidor. Puede usar EXISTS en su lugar

IF (SELECT(*) FROM table_name WHERE column_name = 'xxx'. )

Se puede escribir como:

IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

A menudo es necesario escribir una declaración T_SQL para comparar un conjunto de resultados principal y un conjunto de resultados secundario para averiguar si hay registros en el conjunto de resultados principal pero no en el conjunto de resultados secundario, como por ejemplo:

1.SELECT a.hdr_key FROM hdr_tbl a ---- tbl a significa que tbl se reemplaza por el alias a

WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)

2.SELECT a.hdr_key DESDE hdr_tbl a

UNIRSE A LA IZQUIERDA dtl_tbl b EN a.hdr_key = b.hdr_key DONDE b.hdr_key ES NULO

3.SELECCIONE hdr_key DESDE hdr_tbl

DONDE hdr_key NO ESTÁ (SELECCIONE hdr_key DESDE dtl_tbl)

Los tres métodos de escritura pueden obtener los mismos resultados correctos, pero la eficiencia disminuirá en orden

7. mejor Evite el uso de letras que no sean iniciales en los datos de caracteres indexados. Esto también evita que el motor use el índice

Vea el siguiente ejemplo:

SELECT FROM T1 WHERE NAME LIKE. ?%L%?

SELECCIONE * DE T1 DONDE SUBSTING(NOMBRE,2,1)=?L?

SELECCIONE * DE T1 DONDE NOMBRE COMO ?L %?

Incluso si el campo NOMBRE tiene un índice, las dos primeras consultas aún no pueden usar el índice para completar la operación acelerada. El motor tiene que

operar con todos los datos de la tabla completa, uno por uno. uno para completar la tarea. La tercera consulta puede utilizar índices para acelerar la operación.

8. Aproveche al máximo las condiciones de unión. En algunos casos, puede haber más de una condición de unión entre las dos tablas. En este caso, complete la condición de unión en la cláusula WHERE.

Al escribir, es posible mejorar enormemente la velocidad de consulta.

Ejemplo:

SELECCIONE SUMA(A.CANTIDAD) DE LA CUENTA A, TARJETA B DONDE A.CARD_NO = B.CARD_NO

SELECCIONE SUMA(A.CANTIDAD) ) DESDE LA CUENTA A, TARJETA B DONDE A.CARD_NO = B.CARD_NO

AND A.ACCOUNT_NO=B.ACCOUNT_NO

La segunda oración se ejecutará mucho más rápido que la primera.

9. Elimine el acceso secuencial a datos de filas de tablas grandes

Aunque hay índices en todas las columnas de verificación, algunas formas de cláusulas WHERE obligan al optimizador a usar

Acceso secuencial. Por ejemplo:

SELECCIONAR * DE pedidos DONDE (núm_cliente=104 Y núm_pedido>1001) O

núm_pedido=1008

La solución puede ser usar unión para evitar pedido Acceso:

SELECT * FROM pedidos DONDE client_num=104 AND order_num>1001

UNION

SELECT * FROM pedidos DONDE order_num=1008

Esto permite utilizar la ruta del índice para procesar consultas.

10. Evite expresiones regulares difíciles

La palabra clave LIKE admite la coincidencia de comodines, lo que técnicamente se denomina expresión regular. Pero este tipo de coincidencia requiere mucho tiempo

. Por ejemplo: SELECCIONAR * DEL cliente DONDE código postal COMO ?98_ _ _?

Incluso si se crea un índice en el campo del código postal, en este caso se sigue utilizando el escaneo secuencial. Si

cambia la declaración a SELECT * FROM customer WHERE zipcode >?98000?, el índice se utilizará para consultar al ejecutar la consulta

, lo que obviamente mejorará enormemente la velocidad.

11. Utilice vistas para acelerar las consultas

Ordenar un subconjunto de la tabla y crear una vista a veces puede acelerar las consultas. Ayuda a evitar múltiples operaciones de clasificación y simplifica el trabajo del optimizador de otras maneras.

Por ejemplo:

SELECT nombre.cliente,rcvbles.balance,?otras columnas

DESDE cliente,rcvbles

DONDE cust.customer_id = rcvlbes.customer_id

AND rcvblls.balance>0

AND cust.postcode>?98000

ORDER BY cust.name

Si esta consulta es para ejecutarse varias veces pero no solo una vez, puede encontrar todos los clientes no pagos en una

vista y ordenarlos por nombre de cliente:

CREATE VIEW DBO.V_CUST_RCVLBES

p>

COMO

SELECCIONE nombre.cliente,rcvbles.balance,?otras columnas

DESDE cliente,rcvbles

DONDE cust.customer_id = rcvlbes <. /p>

AND rcvblls.balance>0

ORDER BY cust.name

Luego consulta en la vista de la siguiente manera:

SELECT * DESDE V_CUST_RCVLBES

¿DÓNDE código postal>?98000?

Hay menos filas en la vista que en la tabla principal y el orden físico es el requerido, lo que reduce el disco

E/S, por lo que la carga de trabajo de consultas se puede reducir considerablemente.

12. Si puedes usar BETWEEN, no uses IN

SELECT * FROM T1 WHERE ID IN (10,11,12,13,14)

Cambiar a:

SELECCIONAR * DE T1 DONDE ID ENTRE 10 Y 14

Porque IN hará que el sistema no pueda usar el índice y solo podrá buscar directamente los datos en el mesa.

13. DISTINCT no requiere GROUP BY

SELECCIONAR OrderID FROM Detalles DONDE UnitPrice > 10 GROUP BY OrderID

Se puede cambiar a:

SELECCIONE DISTINTO ID de pedido DESDE Detalles DONDE Precio unitario > 10

14 Uso parcial de índices

1.SELECCIONE ID de empleado, nombre, apellido

DESDE nombres

DONDE departamento = 'prod' o ciudad = 'Orlando' o división = 'comida'

2.SELECCIONE ID de empleado, nombre, apellido DE nombres DONDE departamento =

'prod '

UNION ALL

SELECT ID de empleado, nombre, apellido FROM nombres DONDE ciudad = 'Orlando'

UNION ALL

SELECT empleadoID, nombre, apellido DE nombres DONDE división =

'comida'

Si la columna del departamento tiene un índice, la consulta 2 puede usar parcialmente el índice, pero la consulta 1 no.

15. Si puede usar UNION ALL, no use UNION

UNION ALL no ejecuta la función SELECT DISTINCT, lo que reducirá muchos recursos innecesarios

16. No escriba consultas que no hagan nada

Por ejemplo: SELECCIONE COL1 DE T1 DONDE 1=0

SELECCIONE COL1 DE T1 DONDE COL1=1 Y COL1=2

Este tipo de código inactivo no devolverá ningún conjunto de resultados, pero consumirá recursos del sistema.

17. Intente no utilizar la instrucción SELECT INTO.

La instrucción SELECT INTO hace que la tabla se bloquee, impidiendo que otros usuarios accedan a ella.

18. Obligar al optimizador de consultas a utilizar un índice si es necesario

SELECT * FROM T1 WHERE nextprocess = 1 AND Processid IN (8,32,45)

Cambiar a:

SELECT * FROM T1 (INDEX = IX_ProcessID) DONDE nextprocess = 1 AND

ID de proceso IN (8,32,45)

Luego consulta El optimizador forzará la ejecución de la consulta utilizando el índice IX_ProcessID.

19. Aunque los métodos de escritura de las declaraciones ACTUALIZAR y ELIMINAR son básicamente fijos, todavía damos algunas sugerencias para las declaraciones ACTUALIZAR:

a) Intente no modificar el campo de clave principal.

b) Al modificar campos VARCHAR, intente utilizar valores con el mismo contenido de longitud.

c) Intente minimizar las operaciones de ACTUALIZACIÓN en tablas con activadores de ACTUALIZACIÓN.

d) Evite columnas ACTUALIZAR que se copiarán a otras bases de datos.

e) Evite columnas ACTUALIZAR con muchos índices.

f) Evite las columnas ACTUALIZAR en las condiciones de la cláusula WHERE.