1, no se necesita ningún cursor.
El uso de cursores no solo ocupa memoria, sino que también bloquea la tabla de una manera increíble, lo que puede hacer que todas las optimizaciones de rendimiento que el DBA puede hacer sean iguales a cero. Cada búsqueda en el cursor equivale a una selección.
2. Cree índices apropiados
Siempre que agregue un índice a una tabla, seleccionar será más rápido, pero insertar y eliminar será mucho más lento porque crear y mantener el índice requiere mucho tiempo. de trabajo extra.
(1) Los campos procesados por funciones no pueden utilizar índices.
(2) Cuando la condición incluye varios campos en la tabla, no se puede indexar.
3. Utilice transacciones
Para algunas operaciones que requieren mucho tiempo, el uso de transacciones puede lograr buenos resultados de optimización.
4. Cuidado con los bloqueos
Accede a tu reloj en un orden determinado. Si bloquea la tabla A primero y luego la tabla B, debe bloquearlas en este orden en todos los procedimientos almacenados. Si el procedimiento almacenado primero bloquea la tabla B y luego bloquea la tabla A, esto puede causar un punto muerto.
5. No abra grandes conjuntos de datos.
6. No utilice cursores del lado del servidor.
En comparación con los cursores del lado del servidor, los cursores del lado del cliente pueden reducir la sobrecarga del servidor y del sistema de red y también pueden reducir el tiempo de bloqueo.
7. No ignores el problema de modificar el mismo registro al mismo tiempo.
En ocasiones, dos usuarios modificarán el mismo registro al mismo tiempo, de esta forma, algunas actualizaciones se perderán cuando el último revisor modifique al revisor anterior. Para manejar esta situación, cree un campo de marca de tiempo, verifíquelo antes de escribir, combine los cambios si está permitido y avise al usuario si hay un conflicto.
8. Intenta no utilizar tipos de datos de texto.
No utilices texto a menos que lo utilices para procesar grandes cantidades de datos. Debido a que no es fácil de consultar, la velocidad es lenta y se desperdiciará mucho espacio si no se usa correctamente. En general, varchar maneja mejor los datos.
9. Evite utilizar cálculos en columnas de índice.
En la cláusula donde, si la columna indexada es parte de la función, el optimizador utilizará un escaneo completo de la tabla en lugar del índice. Por ejemplo:
(Ineficiente) Seleccionar...desde[depto]donde[sal]* 12 > 25000; (Eficiente) Seleccionar...desde[depto]donde[sal]> 25000/12; 1210. Los diferentes tipos de índices tienen un rendimiento diferente, por lo que debes usar primero el más eficiente.
La eficiencia de búsqueda de índice del tipo numérico es mayor que la del tipo cadena. La eficiencia de indexación de las cadenas de longitud fija char y nchar es mayor que la de las cadenas de longitud variable varchar y nvarchar.
(Ineficiente) Seleccione... de nombre de tabla donde nombre de usuario = 'Zhang San' y edad > = 21 (válido) seleccione... de nombre de tabla donde edad > = 21, nombre de usuario = 'Zhang San' 12 segundos, optimización de declaraciones SQL
1, no use select *
Los beneficios de especificar las columnas requeridas en select son los siguientes:
(1) reduce la memoria consumo y ancho de banda de la red.
(2) Más seguro
(3) Le da al optimizador de consultas la oportunidad de leer todas las columnas requeridas del índice.
2. Utilice la consulta de parámetros
Principalmente para evitar la inyección de SQL y mejorar la seguridad.
3. El uso existe o no existe en lugar de en o no en.
(Válido) seleccione * de [EMP] donde [empno] > 0 y existe (seleccione 'X' de [depto] donde [depto]). [nºdepto]=[emp].
[deptno] y [loc] = ' MELB '); (ineficiente) seleccione * de [EMP] donde [EMPNO] > 0 y [deptno]in (seleccione [deptno] de [dept] donde [loc] = ' MELB '); ); 124, operación nula o no nula
Para determinar si un campo es nulo, generalmente no se utilizan índices porque los índices no indexan valores nulos. No puede utilizar nulo como índice; cualquier columna con un valor nulo no se incluirá en el índice. Es decir, si una columna tiene valores nulos, no habrá ninguna mejora en el rendimiento incluso si está indexada. El optimizador no permitirá que cualquier declaración que use es nula o no nula en una cláusula donde use un índice.
Solución recomendada: utilizar otras operaciones con la misma función, por ejemplo: si a no es nulo, cambiarlo a a & gt0 o a & gt? etc.
5, & lt y >Operación
Si es más grande o más pequeño que la situación general, no es necesario ajustarlo, porque tiene un índice y se utilizará la búsqueda por índice. , pero en algunos casos se puede optimizar. Si una tabla tiene 654,38+0 millones de registros, los efectos de ejecutar >:2 y > =3 son muy diferentes.
(Ineficiente) seleccione * de[EMP]donde[deptno]> 2; (Eficiente) seleccione * de [EMP]donde[deptno]> = 3, como la operación
<; La operación p>Like puede aplicar consultas con comodines. Las combinaciones de comodines que contiene pueden llegar a casi cualquier consulta. Sin embargo, si se usa incorrectamente, causará problemas de rendimiento, como diapositivas. %5400%?Este tipo de consulta no se refiere al índice, pero ¿me gusta? ¿X5400%? Índice de rango de referencia.7.where afecta el orden de las condiciones después de
El orden de las condiciones después de la cláusula Where tendrá un impacto directo en la consulta de la tabla de big data. Por ejemplo:
Seleccione * de ZL_yhjbqk donde dy_DJ = 'por debajo de 1 kV', xh_BZ = 1;
Seleccione * de ZL_yhjbqk donde dy_DJ = 1 y dy_dj = 'por debajo de 1 kV'; dos consultas anteriores, 123, ningún campo está indexado, por lo que ambos campos se escanean en la tabla. ¿El primer SQL DY _ DJ =' por debajo de 1 kV? La tasa de condiciones en el conjunto de registros es del 99%, mientras que la tasa de xh_bz=1 es solo del 0,5%. En el primer SQL, el 99% de los registros se comparan con dy_dj y xh_bz. En el segundo SQL, el 0,5% de los registros se comparan con dy_dj y xh_bz. Se puede concluir que la utilización de CPU del segundo SQL es significativamente menor que la del primer SQL.
8. Reemplazar o con unión (aplicable a columnas de índice)
En términos generales, es efectivo reemplazar o en la cláusula donde con unión. El uso de o en una columna indexada dará como resultado un escaneo completo de la tabla. Nota: Esta regla solo es válida para varias columnas de índice. Si una columna no está indexada, la eficiencia de la consulta puede verse reducida porque no tiene select o . En el siguiente ejemplo, hay índices tanto en loc_id como en región.
(Ineficiente) seleccione loc_id,loc_desc,begion desde la ubicación donde loc_id = 10 o begion = 'Melbourne'; (Eficiente) seleccione loc_id,loc_desc,Begion desde la ubicación donde loc_id = 10 union seleccione loc _ id, loc _ desc _ Begion desde la ubicación donde Begion = 'Melbourne'; 129, optimizar la base de agrupación
Para mejorar la eficiencia de la declaración agrupar por, podemos filtrar registros innecesarios antes de agrupar por.
(Ineficiente) seleccione [trabajo], avg([sal])del grupo [EMP] por [trabajo] teniendo trabajo = 'presidente' o trabajo = 'gerente' (Eficiente) seleccione [trabajo]; , promedio ([sal]) de [EMP] donde [trabajo] = 'presidente' o trabajo = 'gerente' grupo por [trabajo], use procedimientos almacenados
Puede considerar usar procedimientos almacenados para; encapsular esas declaraciones SQL complejas o lógica de negocios, esto tiene varias ventajas:
(1) El plan de ejecución del procedimiento almacenado se puede almacenar en caché en la memoria durante mucho tiempo, lo que reduce el tiempo de recompilación.
(2) Los procedimientos almacenados reducen las interacciones complejas entre clientes y servidores.
(3) Si necesita realizar algunas modificaciones después de lanzar el programa, puede modificar directamente el procedimiento almacenado sin modificar el programa para evitar reinstalar el programa de implementación.
11. ¿Utiliza sp_configure 'para consultar el límite de sobrecarga del controlador? O configure QUERY_GOVERNOR_COST_LIMIT para limitar los recursos consumidos por la consulta. Cuando los recursos consumidos por una consulta de evaluación exceden el límite, el servidor cancela automáticamente la consulta y la elimina antes de la consulta. Establecer tiempo de bloqueo Configure el tiempo de bloqueo.
12. Utilice seleccionar parte superior o establezca el número de filas para limitar el número de filas operadas.
13. Si la consulta no está indexada en o o etc. Utilice una declaración explícita para especificar el índice: seleccione * de miembro persona (índice = IX _ título) dónde procesar la identificación (? ¿Hombre?,? ¿Mujer?).
14. Si desea insertar un valor binario grande en la columna Imagen, utilice un procedimiento almacenado. No utilice inserción incrustada (no sé si es JAVA). Porque de esta manera, la aplicación primero convierte el valor binario en una cadena (el doble de su tamaño) y luego el servidor convierte el carácter en un valor binario después de recibirlo. No existe tal acción en el procedimiento almacenado: Método: cree el proceso p_insert como insertar en valores de tabla (imagen f) (@ imagen). Llame a este procedimiento almacenado en primer plano para pasar parámetros binarios, lo que mejora significativamente la velocidad de procesamiento.
15, seleccione EMP _ Nombre Formulario Empleado Dónde Salario > Análisis 3000 En esta declaración, si el salario es de tipo flotante, el optimizador lo optimizará para la conversión (Float, 3000). Debido a que 3000 es un número entero, deberíamos usar 3000.0 en la programación en lugar de dejar que el DBMS realice la conversión en tiempo de ejecución. Conversión de datos de caracteres idénticos y enteros.
3. Métodos para procesar más de un millón de datos para mejorar la velocidad de consulta.
1. ¡Intenta evitar usarlo en la cláusula donde! =o
2. Deberíamos considerar la creación de índices en las columnas involucradas en dónde y ordenar por.
3. Intente evitar juzgar el valor nulo del campo en la cláusula donde; de lo contrario, se realizará un escaneo completo de la tabla.
4. Evite utilizar la condición de conexión o en la cláusula donde; de lo contrario, se realizará un escaneo completo de la tabla.
Select id desde donde num = 10 o num = 20 se reescribe como select id desde donde num = 10 union all select id desde donde num = 20 1235. Trate de evitar el uso de signos de porcentaje iniciales.
Seleccione id desde donde nombres como "% ABC %" 16, in y not in también deben usarse con precaución. existe y no existe se puede utilizar en muchos casos; de lo contrario, se escaneará toda la tabla.
7. Si se utilizan parámetros en la cláusula donde, también provocará un escaneo completo de la tabla.
Se puede cambiar la identificación desde donde num = @num para forzar que la consulta use el índice.
Seleccione ID de t con(índice)donde num=@num 1238. Intente evitar operaciones de expresión en campos en la cláusula donde; de lo contrario, se escaneará toda la tabla.
Seleccione id de t donde num/2 = 100 1 debe cambiarse a:
seleccione id de t donde num = 100 * 2 19. Intente evitar realizar operaciones funcionales en los campos de la cláusula donde; de lo contrario, se escaneará toda la tabla.
Seleccione ID de subcadena (nombre, 1, 3) = 'ABC' 1 debe cambiarse a:
Seleccione ID donde el nombre sea algo así como "ABC %" 110. No todos los índices son válidos para consultas. SQL optimiza las consultas en función de los datos de la tabla. Cuando hay muchos datos duplicados en la columna indexada, es posible que la consulta SQL no utilice el índice.
11. Cuantos más índices, mejor. El envío de índices mejora la eficiencia de la selección, pero reduce la eficiencia de la inserción y la actualización. El número de índices de una tabla no debe exceder de 6.
12. Intente utilizar campos numéricos. Si el campo solo contiene información numérica, intente no diseñarlo como caracteres. Esto reducirá el rendimiento de las consultas y conexiones y aumentará la sobrecarga de almacenamiento. Debido a que el motor compara cada carácter de la cadena uno por uno al procesar la consulta y la unión, solo se requiere una comparación para los tipos numéricos.
13. Utilice varchar/nvarchar en lugar de char/nchar tanto como sea posible, porque el espacio de almacenamiento de los campos de longitud variable es muy pequeño al principio, lo que puede ahorrar espacio de almacenamiento para consultas; en un campo relativamente pequeño, la búsqueda dentro del campo es obviamente más eficiente.
14. No utilices select en ningún lugar, en su lugar utiliza una lista específica de campos y no devuelvas campos no utilizados.
15. Intente evitar el uso de cursores porque los cursores son ineficientes. Si los datos operados por el cursor superan las 10,000 filas, considere reescribirlos.
16. Intente evitar grandes operaciones de transacciones y mejorar la concurrencia del sistema.
17. Utilice set rowcount para lograr una paginación de alto rendimiento.
4. Selección de la clave principal de la base de datos Los métodos comunes de selección de la clave principal de la base de datos son:
●¿Crecer campos automáticamente?¿Identificador único? ¿Combinar? Tipo 1231, ventajas del campo de crecimiento automático:
(1) Simple y eficiente. 1 Desventajas:
(1) El incremento automático generalmente usa el tipo int, que está limitado por la cantidad de datos. (2) Fusionar datos en la base de datos será problemático. 122. Ventajas de GUID:
(1) Seguro y único. (2) No habrá problemas de combinación de datos como los campos autoagregados. 12 Desventajas:
(1) Su longitud es de 16 bytes, lo que ocupa mucho espacio de almacenamiento. (2) El tipo de datos es irregular, lleva mucho tiempo crear un índice sobre él y la eficiencia es menor que usar campos de incremento automático. Declaración de derechos de autor: ¿Este artículo es de un blogger de CSDN? Buenas y malas son todas experiencias. El texto original cumple con el acuerdo de derechos de autor CC 4.0 BY-SA.
Adjunte el enlace de la fuente original y esta declaración al reimprimir. Enlace original:/Li tangyuan/article/details/96867580
Cómo optimizar la base de datos
Etiqueta: ¿Cómo proteger los tipos de datos? Reemplazo del cálculo del comodín csdn de escaneo completo de la tabla.