Tras las pruebas, se realizó una consulta condicional en una tabla que contenía más de 4 millones de registros, y el tiempo de consulta llegó a ser de hasta 40 segundos. Por lo tanto, es muy importante mejorar la eficiencia de la consulta de sentencias SQL. A continuación, se presentan varios métodos de optimización de sentencias de consulta que están ampliamente difundidos en Internet:
En primer lugar, cuando el volumen de datos es grande, deberías intentar evitar escanear la tabla completa y considerar construir índices sobre las columnas implicadas en dónde y ordenar, lo que puede acelerar mucho la recuperación de datos. Sin embargo, hay algunas situaciones en las que la indexación no funciona:
1. Intente evitar usar operadores != o <> en la cláusula where, de lo contrario el motor abandonará el uso de índices y realizará un escaneo completo de la tabla.
2. Intentar evitar el juicio de valor nulo en los campos de la cláusula where, de lo contrario el motor abandonará el uso de índices y realizará un escaneo completo de tablas, tales como:
Selecciona id desde t donde num es nulo
Puedes establecer el valor predeterminado de 0 en el num, asegurarte de que no hay ningún valor nulo en la columna número de la tabla, y luego consultar así:
Seleccione id de t donde num=0
3. Intenta evitar usar OR en las condiciones de la cláusula where to join, de lo contrario el motor abandonará el uso del índice y realizará un escaneo completo de la tabla, como:
Selecciona ID desde T, donde num=10 o num=20
Puedes consultar así:
Selecciona ID de T, donde num=10
Sindicato todos
Selecciona ID de T, donde num=20
4. La siguiente consulta también dará lugar a un escaneo completo de la tabla:
Selecciona ID desde t donde el nombre es '%abc%'
Para mejorar la eficiencia, considera la búsqueda en texto completo.
5. En y no en dentro también deben usarse con precaución, de lo contrario conducirá a un escaneo completo de la mesa, como:
Selecciona ID desde t donde num en(1,2,3)
Para valores continuos, si puedes usar entre ellos, no uses en:
Selecciona ID desde t donde num está entre 1 y 3
6. Si usas el parámetro en la cláusula where, también hará que se escanee la tabla completa. Porque SQL solo resuelve variables locales en tiempo de ejecución, pero el optimizador no puede posponer la selección de planes de acceso a tiempo de ejecución; Debe seleccionarse en tiempo de compilación. Sin embargo, si se establece un plan de acceso en tiempo de compilación, el valor de la variable sigue siendo desconocido y, por tanto, no puede usarse como elemento de entrada para la selección de índices. Las siguientes declaraciones serán escaneadas en su totalidad:
Selecciona ID desde t donde num=@num
Puedes forzar que la consulta use un índice en su lugar:
Selecciona ID desde t con(index(index(index name)) donde num=@num
7. Intenta evitar expresar campos en la cláusula where, lo que hará que el motor abandone el uso del índice y realice un escaneo completo de la tabla. Por ejemplo:
Selecciona ID de t donde numero/2=100
Debería cambiarse a:
Selecciona ID de T, donde num=100*2
8. Intenta evitar realizar operaciones de función sobre campos de la cláusula where, lo que hará que el motor abandone el uso de índices y realice un escaneo completo de tablas. Por ejemplo:
Selecciona ID desde t donde substring(name,1,3)='abc' – ID de nombre que empieza por abc
Selecciona ID desde t donde DateDiff(Day,Createdate,'2005-11-30′)=0–'2005-11-30′ generó ID
Debería cambiarse a:
Selecciona ID desde T donde el nombre es 'ABC%'
selecciona ID desde t donde se crea>='2005-11-30′ y se crea<'2005-12-1′
9. No realices funciones, operaciones aritméticas u otras operaciones de expresión a la izquierda del "=" en la cláusula where, de lo contrario el sistema puede no ser capaz de usar correctamente el índice.
10. Al usar un campo índice como condición, si el índice es un índice compuesto, entonces el primer campo del índice debe usarse como condición para asegurar que el sistema use el índice, de lo contrario no se utilizará el índice, y el orden de los campos debe ser lo más coherente posible con el orden del índice.
11. No escribas algunas consultas sin sentido, como generar una estructura de tabla vacía:
Selecciona Col1,col2 en #t desde T, donde 1=0
Este tipo de código no devuelve ningún conjunto de resultados, pero consume recursos del sistema, así que debería cambiarse a algo así:
crear tabla #t(...)
12. Muchas veces es una buena opción usar existe en lugar de en:
Seleccione Num de A donde Num In(Seleccione Num de B)
Sustituye por la siguiente afirmación:
Seleccione num de A donde existe (seleccione 1 de B donde num=A.num)
Aspectos a tener en cuenta al construir un índice:
1. No todos los índices son válidos para consultas, SQL se basa en los datos de la tabla para optimizar la consulta; cuando la columna índice tiene una gran cantidad de duplicación de datos, las consultas SQL pueden no usar el índice, por ejemplo una tabla tiene campos sexo, masculino, hembra casi la mitad cada uno, y aunque el índice se construya sobre sexo, no influirá en la eficiencia de las consultas.
2. Cuantos más índices no haya, mejor; el índice ciertamente puede mejorar la eficiencia de la selección correspondiente, pero también reduce la eficiencia de insertar y actualizar, porque el índice puede reconstruirse al insertar o actualizar, por lo que la forma de construir un índice debe considerarse cuidadosamente, dependiendo de la situación específica. Es mejor no tener más de 6 índices en una tabla y, si hay demasiados, considerar si es necesario construir índices en algunas columnas poco usadas.
3. Evitar actualizar las columnas de datos indexados agrupadas tanto como sea posible, porque el orden de las columnas de datos indexadas agrupadas es el orden físico de almacenamiento de los registros de tabla, y una vez que cambia el valor de la columna, se ajustará el orden de los registros completos de la tabla, lo que consumirá recursos considerables. Si el sistema de aplicaciones necesita actualizar frecuentemente las columnas de índice agrupadas, debe considerar si el índice debe construirse como un índice agrupado.
Otros puntos a tener en cuenta:
1. Intenta usar campos numéricos y trata de no diseñar campos que contengan solo información numérica como caracteres, lo que reducirá el rendimiento de consultas y conexiones, y aumentará la sobrecarga de almacenamiento. Esto se debe a que el motor compara cada carácter de la cadena uno por uno al procesar consultas y uniones, mientras que para los tipos numéricos solo es necesario compararlo una vez.
2. No use select * de t en ningún sitio, sustituya "*" por una lista de campos específica y no devuelva ningún campo que no se haya utilizado.
3. Intenta usar variables de tabla en lugar de tablas temporales. Si la variable de la tabla contiene una gran cantidad de datos, hay que tener en cuenta que el índice es muy limitado (solo el índice de clave primaria).
4. Evitar crear y eliminar frecuentemente tablas temporales para reducir el consumo de recursos de tablas del sistema.
5. Las tablas temporales no son inutilizables, y usarlas adecuadamente puede hacer que ciertas rutinas sean más efectivas, por ejemplo, cuando necesitas referenciar repetidamente una tabla grande o un conjunto de datos en una tabla de uso común. Sin embargo, para eventos puntuales, lo mejor es usar una tabla de exportación.
6. Al crear una tabla temporal, si la cantidad de datos insertados en un momento es grande, puedes usar select into en lugar de crear tabla para evitar que un gran número de registros mejore la velocidad; Si la cantidad de datos no es grande, para facilitar los recursos de la tabla del sistema, deberías crear primero la tabla y luego insertarla.
7. Si se utiliza una tabla temporal, asegúrese de eliminar explícitamente todas las tablas temporales al final del procedimiento almacenado, trunque primero la tabla y luego la tabla de descarte, para evitar un bloqueo largo de la tabla del sistema.
8. Intenta evitar usar el cursor, porque la eficiencia del cursor es pobre; si los datos operados por el cursor superan las 10.000 líneas, deberías considerar reescribir.
9. Antes de usar el método basado en cursor o el método temporal de tablas, primero debes buscar soluciones basadas en conjuntos para resolver el problema, y el método basado en conjuntos suele ser más eficaz.
10. Como en las tablas temporales, el cursor no es inutilizable. Usar FAST_FORWARD cursores para conjuntos de datos pequeños suele ser mejor que otros métodos de procesamiento fila por fila, especialmente si tienes que consultar varias tablas para obtener los datos que necesitas. Las rutinas que incluyen "total" en el conjunto de resultados suelen ser más rápidas que las que se ejecutan con el cursor. Si el tiempo de desarrollo lo permite, se pueden probar tanto métodos basados en cursor como en conjuntos para ver cuál funciona mejor.
11. Establecer SET NOCOUNT ON al principio de todos los procedimientos y triggers almacenados, y SET NOCOUNT OFF al final. No es necesario enviar mensajes DONE_IN_PROC al cliente después de ejecutar cada sentencia del procedimiento almacenado y el disparo.
12. Intenta evitar devolver grandes cantidades de datos al cliente; si el volumen de datos es demasiado grande, deberías considerar si la demanda correspondiente es razonable.
13. Intentar evitar operaciones de transacciones grandes y mejorar la capacidad de concurrencia del sistema.
|
Publicado en 11/5/2018 13:57:06
|
|
|
Publicado en 17/5/2018 10:12:27