¿Qué debo hacer si la base de datos se convierte en el mayor problema en condiciones de alta concurrencia?

Para garantizar la coherencia y la integridad de la base de datos, a menudo se diseñan demasiadas asociaciones de tablas en un diseño lógico para reducir la redundancia de datos tanto como sea posible. (Por ejemplo, una región en una tabla de usuario se puede almacenar en otra tabla de región). Si la redundancia de datos es baja, la integridad de los datos se garantiza fácilmente, lo que mejora la velocidad de procesamiento de los datos, garantiza la integridad de los datos y expresa claramente la relación entre los elementos de los datos. No utilice campos de atributos de incremento automático como claves principales asociadas con tablas secundarias. No es conveniente para la migración del sistema y la recuperación de datos. Se pierde la relación cartográfica del sistema estadístico externo.

Cuestiones a las que se debe prestar atención en el diseño de tablas:

1. La longitud de la línea de datos no debe exceder los 8020 bytes. Si excede esta longitud, los datos ocuparán dos filas de la página física, lo que provocará la fragmentación del almacenamiento y reducirá la eficiencia de las consultas.

2. Para los campos numéricos, puede elegir el tipo numérico en lugar del tipo de cadena (número de teléfono). Esto reducirá el rendimiento de la consulta y la conexión y aumentará la sobrecarga de almacenamiento. Esto se debe a que el motor compara cada carácter de la cadena uno por uno a medida que procesa la consulta y los concatena nuevamente, mientras que para los tipos numéricos solo es necesario compararlos una vez.

3. Para el tipo de carácter inmutable char y el tipo de carácter variable varchar, ambos tienen 8000 bytes. Las consultas de caracteres son rápidas pero consumen espacio de almacenamiento. Las consultas Varchar son relativamente lentas pero ahorran espacio de almacenamiento. Al diseñar campos, tiene flexibilidad en sus elecciones. Por ejemplo, puede seleccionar CHAR para campos que varían ligeramente en longitud, como nombres de usuario y contraseñas, y VARCHAR para campos que varían en longitud, como comentarios.

4. Partiendo de la premisa de satisfacer al máximo las posibles necesidades, la longitud del campo debe establecerse lo más corta posible, lo que puede mejorar la eficiencia de la consulta y reducir el consumo de recursos al crear un índice. .

2. Optimización de consultas

Cuando utilice SQL en la ventana de datos, intente colocar el índice utilizado en la primera columna de la selección, la estructura del algoritmo debe ser tan simple como; posible;

No utilice excesivos caracteres comodín, como instrucciones SELECT* FROM T1 al realizar consultas. Si desea utilizar varias columnas, seleccione varias columnas como: seleccione col1, col 2 de 1; intente limitar el número de filas en el conjunto de resultados, como por ejemplo: seleccione las 300 primeras col1, col2, col 3 de 1, porque en algunos casos, el usuario no necesita tantos datos.

Sin un índice, la base de datos debe escanear toda la tabla para encontrar un determinado dato y recorrer todos los datos una vez para encontrar registros que cumplan con las condiciones. Cuando la cantidad de datos es pequeña, puede que no haya una diferencia significativa, pero cuando la cantidad de datos es grande, la situación es extremadamente mala.

¿Cómo se ejecutan las sentencias SQL en SQL SERVER? Les preocupa que SQLSERVER malinterprete sus declaraciones SQL. Por ejemplo:

seleccione * de la tabla1 donde nombre = 'zhangsan' y tID gt10000 y ejecute:

seleccione * de la tabla1 donde tID gt10000 y nombre= 'Zhang San'

Algunas personas no saben si la eficiencia de ejecución de las dos declaraciones anteriores es la misma, porque si simplemente observa el orden de las declaraciones, las dos declaraciones son realmente diferentes. Si tID es un índice agrupado, entonces la última oración solo se puede encontrar en los registros posteriores a 10000 en la tabla. La oración anterior primero debe buscar varios nombres = 'Zhang San' en toda la tabla y luego presentar los resultados de la consulta de acuerdo con la condición restrictiva tid> 10000.

De hecho, esas preocupaciones son innecesarias. Hay un optimizador de análisis de consultas en SQLSERVER que puede calcular las condiciones de búsqueda en la cláusula donde y determinar qué índice puede reducir el espacio de búsqueda para el escaneo de la tabla, es decir, se puede lograr una optimización automática. Aunque el optimizador de consultas puede optimizar automáticamente las consultas en función de las cláusulas Where, a veces el optimizador de consultas no realiza las consultas tan rápido como se desea.

Entonces, lo más importante para optimizar la consulta es hacer que la declaración cumpla con las reglas del optimizador de consultas tanto como sea posible, evitar el escaneo completo de la tabla y utilizar la consulta de índice.

Se debe prestar especial atención a:

1. Trate de evitar juzgar el valor nulo del campo en la cláusula donde; de ​​lo contrario, el motor dejará de usar el índice y escaneará el tabla completa, por ejemplo:

Seleccione id de t donde num es nulo

Puede establecer el valor predeterminado de num en 0 para asegurarse de que no haya valores nulos para la num columna en la tabla y luego consúltela de esta manera:

Seleccione id de t, donde num=0

2. =o

3. Intente evitar el uso de la condición de conexión o en la cláusula donde; de ​​lo contrario, el motor dejará de usar el índice y escaneará toda la tabla, por ejemplo:

p>Seleccione id de t, donde num=10 o num=20

Puede consultar así:

seleccione id de t donde num=10

Union all

Seleccionar id de t, donde num=20s

4.in y no in también deben usarse con precaución, porque in hará que el sistema no pueda usar el índice y Solo puede buscar directamente los datos en la tabla. Por ejemplo:

seleccione id de t donde num en (1, 2, 3)

Para valores continuos, puede usar entre en lugar de en:

Desde t Seleccione id en, donde num está entre 1 y 3

6 Si es necesario, fuerce al optimizador de consultas a usar el índice, como usar parámetros en la cláusula donde, lo que también generará una tabla completa. escanear. Debido a que SQL solo resuelve variables locales en tiempo de ejecución, el optimizador no puede posponer la selección del plan de acceso hasta el momento de ejecución; debe seleccionarse en tiempo de compilación; Sin embargo, si el plan de acceso se establece en tiempo de compilación, el valor de la variable aún se desconoce, por lo que no se puede utilizar como entrada para la selección del índice. La siguiente declaración escaneará toda la tabla:

seleccione id de t donde num=@num

Puede forzar que la consulta use un índice:

Seleccionar id de t con( índice) donde num=@num.

7. Intente evitar realizar operaciones de expresión en campos en la cláusula donde, lo que hará que el motor deje de usar el índice y escanee toda la tabla. Por ejemplo:

SELECT * FROM T1 donde F1/2=100

debe cambiarse a:

SELECT * FROM T1 donde F1=100*2

SELECT * FROM RECORD donde subcadena(CARD _ NO, 1, 4)='5378 '

Debe cambiarse a:

SELECT * FROM RECORD WHERE CARD _ NO LIKE ' 5378 '

Seleccione el número de miembro, nombre, apellido de los miembros

Entre ellos DATEDIFF(yy, datofbirth, GETDATE()) gt;

debe cambiarse para:

Seleccione el número de miembro, nombre y apellido de los miembros

Donde la fecha de nacimiento ltDATEADD(yy, -21, GETDATE())

Es decir, cualquier par de operaciones de columnas dará como resultado escaneos de tablas, que incluyen funciones de bases de datos, expresiones de cálculo, etc. Al realizar la consulta, intente mover la operación a la derecha del signo igual.

8. Las operaciones de funciones en campos en la cláusula donde deben evitarse tanto como sea posible, lo que hará que el motor deje de usar el índice y escanee toda la tabla.

Por ejemplo:

seleccione id desde donde subcadena(nombre, 1, 3) = 'abc'-nombre ID que comienza con ABC.

Seleccione id desde donde fechado (día, fecha de creación, ' 2005-11-30 ') = 0-' 2005-11-30 ' id generado.

Debe cambiarse a:

Seleccione la identificación de t donde el nombre es "abc"

seleccione la identificación de t donde la fecha de creación gt;='2005- 11-30 'andcreatedate lt'2005-12-1'

9. No realice funciones, operaciones aritméticas u otras operaciones de expresión en el lado izquierdo de "=" en la cláusula donde, de lo contrario el sistema puede no podrá utilizar los índices correctamente.

10. Cuando se utiliza un campo de índice como condición, si el índice es un índice compuesto, el primer campo del índice debe usarse como condición para garantizar que el sistema use el índice; de ​​lo contrario, el índice. no se utilizará y el orden de los campos debe ser coherente con el orden del índice tanto como sea posible.

11. Muchas veces usar existe es una buena opción:

seleccione num de a donde num in (seleccione num de b)

Reemplace con la siguiente declaración :

seleccione num de a donde existe (seleccione 1 de b donde num = a . num)

Pero este último es significativamente más eficiente que el primero. Porque este último no genera una gran cantidad de escaneos de tablas de bloqueo o escaneos de índices.

Si desea comprobar si hay registros en la tabla, no utilice count(*). Esto es ineficiente y desperdicia recursos del servidor. En su lugar se puede utilizar EXISTS. Por ejemplo:

IF(SELECT COUNT(*)FROM tabla _ nombre WHERE columna _ nombre = ' XXX ')

Se puede escribir como:

Si existe (SELECT * FROM table_name, donde column_name = 'xxx ')

12. Intente utilizar variables de tabla en lugar de tablas temporales. Si la variable de la tabla contiene una gran cantidad de datos, tenga en cuenta que el índice es muy limitado (solo el índice de clave principal).

13. Evite crear y eliminar tablas temporales con frecuencia para reducir el consumo de recursos de las tablas del sistema.

14. Las tablas temporales no están disponibles. Usarlos correctamente puede hacer que algunas rutinas sean más eficientes, por ejemplo, cuando necesita hacer referencia repetidamente a una tabla grande o a un conjunto de datos en una tabla común. Sin embargo, para eventos puntuales, es mejor utilizar una tabla de exportación.

15. Al crear una tabla temporal, si se inserta una gran cantidad de datos a la vez, puede usar seleccionar en lugar de crear tabla para evitar generar una gran cantidad de registros y mejorar la velocidad; la cantidad de datos no es grande, para reducir los recursos de la tabla del sistema, primero debe crear la tabla y luego insertarla.

16. Si usa tablas temporales, debe eliminar explícitamente todas las tablas temporales al final del procedimiento almacenado, primero truncar la tabla y luego eliminarla para evitar bloquear la tabla del sistema durante mucho tiempo. .

17. Establezca SET NOCOUNT ON al principio de todos los procedimientos almacenados y activadores, y establezca SET NOCOUNT OFF al final. Después de ejecutar cada declaración del procedimiento almacenado y el activador, no es necesario enviar un mensaje DONE_IN_PROC al cliente.

18. Intente evitar grandes operaciones de transacciones y mejorar la concurrencia del sistema.

19. Intenta evitar devolver grandes cantidades de datos al cliente.

Si la cantidad de datos es demasiado grande, se debe considerar si los requisitos correspondientes son razonables.

20. Evite el uso de tipos de datos incompatibles. Por ejemplo, float e int, char y varchar, binario y varbinary son incompatibles. Los tipos de datos incompatibles pueden impedir que el optimizador realice algunas operaciones de optimización que de otro modo podrían realizarse. Por ejemplo:

Seleccione nombres de empleados con salario>; 60000

En esta declaración, si el tipo de campo de salario es dinero, es difícil para el optimizador optimizarlo porque 60000 es un número entero. Deberíamos convertir números enteros en monedas mientras programamos en lugar de esperar la conversión en tiempo de ejecución.

23. Si se puede utilizar DISTINCT, no es necesario GROUP BY.

Seleccione OrderID en los detalles, donde el precio unitario es gt10. La agrupación se basa en OrderID.

Se puede cambiar a:

Seleccione un OrderID diferente del. detalles, donde el precio unitario es gt10

24. Si puede usar UNION ALL, no use UNION.

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

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

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

Cuarto, crear índices eficientes

La creación de índices generalmente tiene dos propósitos: mantener la singularidad de las columnas del índice y proporcionar una estrategia para un acceso rápido a los datos de la tabla.

Hay dos tipos de índices en bases de datos grandes: índices agrupados e índices no agrupados. Las tablas sin índices agrupados almacenan datos en una estructura de montón, y todos los datos se agregan al final de la tabla. Sin embargo, los datos de una tabla con un índice agrupado se almacenarán en el orden de las claves del índice agrupado y solo se permite un índice agrupado en una tabla. Por lo tanto, de acuerdo con la estructura del árbol B, se puede entender que agregar cualquier índice puede mejorar la velocidad de consulta de la columna de índice, pero reducirá el rendimiento de las operaciones de inserción, actualización y eliminación. Por lo tanto, realice operaciones frecuentes de inserción, actualización y eliminación en tablas con muchos índices y establezca un factor de relleno más pequeño al establecer tablas e índices para dejar más espacio libre en cada página de datos y reducir las divisiones de páginas y el trabajo de reorganización.

Los índices son una de las formas más eficientes de obtener datos de una base de datos. El 95% de los problemas de rendimiento de las bases de datos se pueden resolver mediante tecnología de indexación. En general, suelo utilizar un índice agrupado único en la clave primaria lógica, un índice único no agrupado en la clave del sistema (como un procedimiento almacenado) y un índice no agrupado en cualquier columna [campo] de clave externa. Sin embargo, los exponentes son como la sal. Demasiada comida puede volverla salada. Hay que considerar cuánto espacio hay en la base de datos, cómo se accede a la tabla y si estos accesos son principalmente de lectura y escritura.

De hecho, puedes pensar en un índice como un tipo especial de directorio. SQL SERVER de Microsoft proporciona dos índices: clusteredindex y nonclusteredindex.

La diferencia entre índice agrupado e índice no agrupado:

De hecho, el texto de nuestro diccionario chino en sí es un índice agrupado. Por ejemplo, si queremos buscar la palabra "an", naturalmente abriremos las primeras páginas del diccionario, porque el pinyin de "An" es "An", y el diccionario de caracteres chinos ordenados por pinyin comienza con la letra inglesa "A" y termina en "Z", por lo que la palabra "An" se encuentra naturalmente en la parte superior del diccionario. Si busca todas las palabras que comienzan con "A" y no puede encontrar la palabra, significa que la palabra no está en su diccionario. Del mismo modo, si buscas la palabra "张", también irás a la última parte de tu diccionario, porque el pinyin de "张" es "张". En otras palabras, el cuerpo principal del diccionario en sí es un directorio y no es necesario buscar en otros directorios para encontrar lo que busca.

A este directorio donde el contenido del texto se organiza según ciertas reglas lo llamamos "índice agrupado".

Si conoces una palabra, puedes consultarla rápidamente de forma automática.

Pero es posible que te encuentres con una palabra que no reconoces y no sabes cómo pronunciarla. En este momento, no puede encontrar la palabra que acaba de buscar. Debe buscar la palabra que desea según el radical, luego pasar directamente a una página y encontrar la palabra que desea según el número de página después de la palabra. . Sin embargo, la clasificación de palabras que se encuentran combinando el catálogo radical y el diccionario no es un verdadero método de clasificación de texto. Por ejemplo, si busca la palabra "Zhang", vemos que el número de página de la palabra "Zhang" en el diccionario después de la búsqueda radical es 672, y la palabra "Chi" está encima de la palabra "Zhang" en el diccionario. diccionario, pero el número de página es 63, debajo. Obviamente, estos personajes no están realmente por encima ni por debajo del personaje "Zhang". Las palabras consecutivas "Chi, Zhang, Nu" que ve ahora son en realidad su orden en el índice no agrupado, que es el mapeo de las palabras en el diccionario en el índice no agrupado. De esta manera, podemos encontrar la palabra que necesitamos, pero requiere dos procesos: primero, busque el resultado en la tabla de contenido y luego pase al número de página que necesita.