Cómo mejorar la velocidad de consulta de la base de datos MySQL con millones de registros
Recientemente, debido a necesidades de trabajo, comencé a prestar atención al método de optimización de la declaración de consulta de selección de la base de datos Mysql.
En proyectos reales, se descubrió que cuando el volumen de datos de la tabla MySQL alcanza millones, la eficiencia de la consulta del SQL ordinario disminuye linealmente, y si hay muchas condiciones de consulta para dónde, la velocidad de la consulta es simplemente inaguantable. Una vez probé una consulta condicional en una tabla que contenía más de 4 millones de registros (con índices) y el tiempo de consulta llegó a 40 segundos. Creo que cualquier usuario estaría loco por una latencia de consulta tan alta. Por lo tanto, es muy importante cómo mejorar la eficiencia de las consultas de las declaraciones SQL. Los siguientes son 30 métodos ampliamente difundidos para optimizar declaraciones de consulta SQL en Internet:
1. =o
2. Para optimizar la consulta, debemos evitar escanear toda la tabla tanto como sea posible. Primero, 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, el motor dejará de usar el índice y escaneará toda la tabla, por ejemplo:
Seleccionar. 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 columna num en la tabla y luego consultarlo de esta manera:
Seleccione id de t, donde num=0
4. 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 escanee toda la tabla, por ejemplo:
Seleccione id de t , donde num=10 o num=20
Puede consultar así:
seleccione id de t donde num=10
Unir todo
Seleccione id de t, donde num=20
5. escaneo de tabla: (no puede ir precedido de un signo de porcentaje)
Seleccione entre t id, donde el nombre es similar a "c"
Para mejorar la eficiencia, se puede realizar la búsqueda de texto completo consideró.
6. In y not in también deben usarse con precaución; de lo contrario, se realizará un escaneo completo de la tabla, como por ejemplo:
seleccione id de t donde num en (1, 2, 3)
p>Para valores continuos, puedes usar entre en lugar de en:
Selecciona id de t donde num está entre 1 y 3
7. Si está en donde El uso de parámetros en la cláusula también dará como resultado un escaneo completo de la tabla. 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.
8. 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:
seleccione id de t donde num/2=100
debe cambiarse a:
seleccione id de t donde num = 100 * 2< /p >
9. 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 de donde subcadena (nombre, 1, 3) = 'abc' – ID de nombre que comienza con ABC.
Se debe cambiar el ID generado por el ID seleccionado desde donde fechado(día, fecha de creación, '2005-11-30') = 0-"2005-11-30"
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' y la fecha de creación lt'2005 - 12-1′
10. 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, es posible que el sistema no pueda utilizar el índice correctamente. .
11. 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.
12. No escriba consultas sin sentido. Si necesita generar una estructura de tabla vacía:
seleccione col1, col2 en #t desde t donde 1=0
.Este tipo de código no devolverá ningún conjunto de resultados, pero consumirá recursos del sistema. Debería cambiarse a esto:
Crear tabla #t(…)
13 Muchas veces es una buena opción usar existe en lugar de en:
Reemplace con la siguiente declaración:
seleccione num de a donde existe (seleccione 1 de b donde num = a . num)
14. No todos los índices son válidos para consultas. SQL optimiza las consultas en función de los datos de la tabla. Es posible que las consultas SQL no utilicen el índice cuando hay muchos datos duplicados en la columna indexada. Por ejemplo, si hay casi la mitad de los campos en una tabla, masculino y femenino, incluso si el índice se basa en el género, no tendrá ningún efecto en la eficiencia de la consulta.
15, cuantos más índices, mejor. Aunque los índices pueden mejorar la eficiencia de las selecciones correspondientes, también pueden reducir la eficiencia de las inserciones y actualizaciones. Debido a que los índices pueden reconstruirse durante inserciones o actualizaciones, la forma de crearlos requiere una consideración cuidadosa caso por caso. El número de índices de una tabla no debe exceder de 6. Si hay demasiados índices, considere si es necesario crear índices en algunas columnas que se utilizan con poca frecuencia.
16. Debe evitar actualizar las columnas de datos de índice agrupado tanto como sea posible, porque el orden de las columnas de datos de índice agrupado es el orden de almacenamiento físico de los registros de la tabla. Una vez que cambie el valor de la columna, se ajustará el orden de todos los registros de la tabla, lo que consumirá recursos considerables. Si el sistema de aplicación requiere actualizaciones frecuentes de las columnas de datos del índice agrupado, es necesario considerar si el índice debe crearse como un índice agrupado.
17. Prueba a 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. Esto se debe a que el motor compara cada carácter de la cadena uno por uno al procesar consultas y conexiones, pero solo una comparación es suficiente para el tipo de número.
18. Utilice varchar/nvarchar en lugar de char/nchar tanto como sea posible, porque en primer lugar, el espacio de almacenamiento de los campos de longitud variable es pequeño, lo que puede ahorrar espacio de almacenamiento. En segundo lugar, para las consultas, la eficiencia de la búsqueda. en campos relativamente pequeños mejora significativamente.
19. No utilice select * from t en ningún lugar, reemplace "*" con una lista de campos específica y no devuelva ningún campo innecesario.
20. 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 los índices son muy limitados (solo índices de clave primaria).
21. Evite crear y eliminar tablas temporales con frecuencia para reducir el consumo de recursos de las tablas del sistema.
22. 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.
23. 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 crear 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.
24. Si se utilizan tablas temporales, todas las tablas temporales deben eliminarse explícitamente al final del procedimiento almacenado. Primero trunque la tabla y luego elimínela para evitar el bloqueo a largo plazo de las tablas del sistema.
25. Intente evitar el uso de cursores porque los cursores son muy ineficientes. Si los datos operados por el cursor superan los 654,38 millones de filas, se debe considerar la reescritura.
26. Antes de utilizar el método basado en cursor o el método de tabla temporal, primero debe encontrar un método basado en conjuntos para resolver el problema.
27. Al igual que las tablas temporales, los cursores no están disponibles. El uso de cursores FAST_FORWARD para conjuntos de datos pequeños suele ser superior a otros métodos de procesamiento fila por fila, especialmente cuando se debe hacer referencia a varias tablas para obtener los datos necesarios. Las rutinas que incluyen "Total" en el conjunto de resultados suelen ser más rápidas que usar un cursor. Si el tiempo de desarrollo lo permite, se pueden probar tanto el enfoque basado en cursor como el enfoque basado en colección para ver cuál funciona mejor.
28. 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.
29. 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.
30. Intente evitar grandes operaciones de transacciones y mejorar la concurrencia del sistema.