La consulta es la operación más utilizada en la tecnología de bases de datos. El proceso de operación de la consulta es relativamente simple. Primero, la declaración SQL de la consulta se emite desde el cliente. Después de recibir la declaración SQL enviada por el cliente, el servidor de la base de datos ejecuta la declaración SQL y luego devuelve los resultados de la consulta al cliente. Aunque el proceso es muy simple, los diferentes métodos de consulta y configuraciones de la base de datos tendrán un gran impacto en el rendimiento de la consulta.
Por lo tanto, este artículo analiza las técnicas de optimización de consultas comúnmente utilizadas en MySQL. Las discusiones incluyen: mejorar la velocidad de las consultas a través del almacenamiento en búfer de consultas; optimización automática de las consultas por parte de MySQL; detección de consultas inalcanzables y uso de varias opciones de consulta para mejorar el rendimiento;
1. Mejorar la velocidad de las consultas mediante el almacenamiento en búfer de consultas
Generalmente, cuando usamos declaraciones SQL para realizar consultas, el servidor de la base de datos ejecutará esta declaración SQL cada vez que reciba SQL del cliente. Pero cuando se recibe exactamente la misma instrucción SQL dentro de un intervalo determinado (por ejemplo, dentro de 1 minuto), se ejecutará de la misma manera. Aunque esto puede garantizar la naturaleza en tiempo real de los datos, la mayoría de las veces, los datos no requieren un tiempo real completo, lo que significa que puede haber un cierto retraso. Si este es el caso, no vale la pena ejecutar exactamente el mismo SQL en un corto período de tiempo.
Afortunadamente, MySQL nos proporciona la función de almacenamiento en búfer de consultas (el almacenamiento en búfer de consultas solo se puede utilizar en MySQL 4.0.1 y superior). Podemos mejorar el rendimiento de las consultas hasta cierto punto mediante el almacenamiento en caché de consultas.
Podemos configurar el búfer de consulta a través del archivo my.ini en el directorio de instalación de MySQL. La configuración también es muy simple, simplemente establezca query_cache_type en 1. Después de configurar este atributo, antes de ejecutar cualquier instrucción SELECT, MySQL verificará en su búfer si se ha ejecutado la misma instrucción SELECT. Si es así y el resultado de la ejecución no ha caducado, el resultado de la consulta se devolverá directamente al cliente. Pero al escribir declaraciones SQL, tenga en cuenta que el búfer de consultas de MySQL distingue entre mayúsculas y minúsculas. Las siguientes dos declaraciones SELECT son las siguientes: SELECT * de TABLE1
SELECCIONAR * DE LA TABLA 1
Las dos declaraciones SQL anteriores son SELECT completamente diferentes para el almacenamiento en búfer de consultas. Además, el búfer de consultas no maneja espacios automáticamente. Por lo tanto, al escribir declaraciones SQL, debe intentar reducir el uso de espacios, especialmente los espacios al principio y al final de SQL (porque el caché de consultas no intercepta automáticamente los espacios en). el principio y el final).
Aunque no configurar un búfer de consulta a veces puede causar pérdidas de rendimiento, hay algunas declaraciones SQL que necesitan consultar datos en tiempo real o no se usan con frecuencia (tal vez se ejecutan una o dos veces al día). Esto requiere desactivar el almacenamiento en búfer. Por supuesto, puede desactivar la caché de consultas estableciendo el valor de query_cache_type, pero esto desactivará permanentemente la caché de consultas. MySQL 5.0 proporciona un método para desactivar temporalmente el caché de consultas: SELECT SQL_NO_CACHE campo1, campo2 DE TABLA1
Dado que la declaración SQL anterior usa SQL_NO_CACHE, independientemente de si esta declaración SQL se ha ejecutado antes, el servidor no buscará en el búfer y la ejecutará cada vez.
También podemos configurar query_cache_type en my.ini en 2, de modo que la caché de consultas se use solo después de usar SQL_CACHE. SELECCIONE SQL_CALHE * DE LA TABLA1
2. Optimización automática de consultas de MySQL
Los índices son muy importantes para las bases de datos. Los índices se pueden utilizar para mejorar el rendimiento durante las consultas. Pero a veces el uso de índices puede reducir el rendimiento. Podemos fijarnos en la siguiente tabla VENTAS: CREAR TABLA VENTAS
(
ID INT(10) SIN FIRMAR NO NULO AUTO_INCREMENT,
NOMBRE VARCHAR(100) NO NULO,
EL PRECIO FLOTANTE NO ES NULO,
SALE_COUNT INT NO NULO,
SALE_DATE FECHA NO NULA,
CLAVE PRIMARIA (ID)，
ÍNDICE (NOMBRE),
ÍNDICE (FECHA_VENTA)
)
Supongamos que hay millones de datos almacenados en esta tabla y queremos consultar el precio promedio del producto número 1000 en 2004 y 2005. Podemos escribir la siguiente sentencia SQL: SELECCIONAR PROMEDIO(PRECIO) DE VENTAS
DONDE ID = 1000 Y FECHA_VENTA ENTRE '2004-01-01' Y '2005-12-31';
Si la cantidad de este producto es muy grande, representa casi el 50% o más de los registros en la tabla VENTAS. Luego, usar el índice en el campo SALE_DATE para calcular el promedio es un poco lento. Porque si usa un índice, debe ordenar el índice. Cuando hay muchos registros que cumplen las condiciones (como representar el 50% o más de los registros en toda la tabla), la velocidad disminuirá, por lo que es mejor escanear toda la tabla. Por lo tanto, MySQL decidirá automáticamente si utilizar el índice para la consulta en función de la proporción de datos que cumplen las condiciones en toda la tabla.
Para MySQL, el índice no se utiliza cuando la proporción de los resultados de la consulta anterior con respecto a los registros en toda la tabla es aproximadamente del 30%. Esta proporción la obtienen los desarrolladores de MySQL según su experiencia. Sin embargo, el valor de escala real variará según el motor de base de datos utilizado.
3. Clasificación basada en índices
Una de las debilidades de MySQL es su clasificación. Aunque MySQL puede consultar aproximadamente 15.000 registros en 1 segundo, MySQL solo puede utilizar como máximo un índice al realizar consultas. Por lo tanto, si la condición WHERE ya ocupa el índice, el índice no se utilizará en la clasificación, lo que reducirá en gran medida la velocidad de la consulta. Podemos observar la siguiente declaración SQL: SELECT * FROM SALES WHERE NAME = “nombre” ORDER BY SALE_DATE DESC;
El índice del campo NOMBRE se ha utilizado en la cláusula WHERE del SQL anterior, por lo que ya no se utilizará al ordenar SALE_DATE. Para resolver este problema, podemos crear un índice compuesto en la tabla VENTAS: ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)
De esta manera, la velocidad mejorará enormemente al utilizar la instrucción SELECT anterior para realizar consultas. Pero tenga cuidado, cuando utilice este método, asegúrese de que no haya ningún campo de clasificación en la cláusula WHERE. En el ejemplo anterior, no puede utilizar SALE_DATE para realizar consultas. De lo contrario, aunque la clasificación es más rápida, no hay un índice separado en el campo SALE_DATE. , por lo que la consulta se ralentizará.
4. Detección de consultas inalcanzables
Al ejecutar sentencias SQL, inevitablemente encontrará algunas condiciones que deben ser falsas. La llamada condición debe ser falsa es que no importa cómo cambien los datos de la tabla, esta condición es falsa. Como DONDE valor <100 Y valor> 200. Nunca podremos encontrar un número que sea a la vez menor que 100 y mayor que 200.
Si encuentra tales condiciones de consulta, no es necesario ejecutar dichas declaraciones SQL. Afortunadamente, MySQL puede detectar esta situación automáticamente. Por ejemplo, podemos observar la siguiente declaración SQL: SELECT * FROM SALES WHERE NAME = “nombre1” AND NAME = “nombre2”
La declaración de consulta anterior busca registros cuyo NOMBRE sea igual a nombre1 y nombre2. Obviamente, esta es una consulta inalcanzable y la condición WHERE debe ser falsa. Antes de que MySQL ejecute la declaración SQL, primero analizará si la condición WHERE es una consulta inalcanzable. Si es así, la declaración SQL ya no se ejecutará. Para verificar esto. Primero usamos EXPLAIN para probar el siguiente SQL: EXPLAIN SELECT * FROM SALES WHERE NAME = "nombre1"
La consulta anterior es una consulta normal. Podemos ver que el elemento de la tabla en los datos de información de ejecución devueltos por EXPLAIN es VENTAS. Esto muestra que MySQL opera VENTAS. Mire las siguientes declaraciones nuevamente: EXPLICAR SELECT * FROM SALES WHERE NAME = “nombre1” AND NAME = “nombre2”
Podemos ver que el elemento de la tabla está vacío, lo que significa que MySQL no ha operado en la tabla SALES.
5. Utilice varias selecciones de consultas para mejorar el rendimiento.
Además del uso normal de la instrucción SELECT, MySQL también nos proporciona muchas opciones que pueden mejorar el rendimiento de las consultas. Como se mencionó anteriormente, SQL_NO_CACHE y SQL_CACHE, que se utilizan para controlar el almacenamiento en búfer de consultas, son dos de las opciones. En esta sección, presentaré algunas opciones de consulta de uso común.
1. STRAIGHT_JOIN: Forzar orden de conexión
Cuando conectamos dos o más tablas para realizar consultas, no necesitamos preocuparnos de qué tabla MySQL se conecta primero y cuál se conecta en último lugar. Todo esto está determinado por una secuencia de conexión determinada por MySQL a través de una serie de cálculos y evaluaciones internas. En las siguientes declaraciones SQL, TABLE1 y TABLE2 no están necesariamente conectados entre sí: SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1, TABLE2 WHERE…
Si los desarrolladores necesitan intervenir manualmente en el orden de las conexiones, deben usar la palabra clave STRAIGHT_JOIN, como la siguiente declaración SQL: SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE...
Como se puede ver en la declaración SQL anterior, STRAIGHT_JOIN se usa para forzar a MySQL a unir tablas en el orden de TABLE1 y TABLE2. Si cree que es más eficiente unirse en su propio orden que el orden recomendado por MySQL, puede usar STRAIGHT_JOIN para determinar el orden de conexión.
2. Intervenir en el uso del índice y mejorar el rendimiento.
El uso de índices se ha mencionado anteriormente. En circunstancias normales, MySQL decidirá si utilizar un índice y qué índice utilizar al realizar consultas. Pero en algunos casos especiales, queremos que MySQL use solo uno o algunos índices, o no queremos usar un índice determinado. Esto requiere el uso de algunas opciones de consulta de MySQL para controlar el índice.
Limitar el alcance del uso de índices.
A veces creamos muchos índices en la tabla de datos. Cuando MySQL selecciona el índice, se consideran todos estos índices. Pero a veces queremos que MySQL solo considere unos pocos índices en lugar de todos los índices. Esto requiere usar USE INDEX para configurar la declaración de consulta. SELECCIONAR * DE LA TABLA 1 UTILIZAR ÍNDICE (CAMPO1, CAMPO2)…
Como se puede ver en la declaración SQL anterior, no importa cuántos índices se hayan establecido en la TABLA1, MySQL solo considera los índices establecidos en CAMPO1 y CAMPO2 al seleccionar índices.
Limitar el rango de índices que no se utilizan.
Si tenemos muchos índices para considerar y pocos índices no utilizados, podemos usar IGNORE INDEX para la selección inversa. En el ejemplo anterior se selecciona el índice que se considera, mientras que usar IGNORE INDEX es para seleccionar el índice que no se considera. SELECCIONAR * DE LA TABLA 1 IGNORAR ÍNDICE (CAMPO1, CAMPO2)…
En la declaración SQL anterior, solo no se utilizan los índices de CAMPO1 y CAMPO2 en la tabla TABLA1.
Forzar el uso de un índice.
Los dos ejemplos anteriores brindan a MySQL una opción, lo que significa que MySQL no tiene que usar estos índices. A veces esperamos que MySQL deba usar un determinado índice (dado que MySQL solo puede usar un índice al realizar consultas, solo puede obligar a MySQL a usar un índice). Esto requiere el uso de FORCE INDEX para completar esta función. SELECCIONE * DE LA TABLA 1 ÍNDICE DE FUERZA (CAMPO1)…
La declaración SQL anterior solo utiliza el índice creado en FIELD1, no los índices de otros campos.
3. Utilice tablas temporales para mejorar el rendimiento de las consultas.
Cuando hay muchos datos en el conjunto de resultados de nuestra consulta, podemos forzar el conjunto de resultados en una tabla temporal a través de la opción SQL_BUFFER_RESULT, para que el bloqueo de la tabla MySQL se pueda liberar rápidamente (para que otras declaraciones SQL puedan consultarlos). records) ) y puede ofrecer grandes conjuntos de registros a los clientes durante largos períodos de tiempo. SELECCIONE SQL_BUFFER_RESULT * DE LA TABLA 1 DONDE…
Similar a la opción SQL_BUFFER_RESULT, existe SQL_BIG_RESULT. Esta opción generalmente se usa para agrupar o palabras clave DISTINCT. Esta opción notifica a MySQL que, si es necesario, los resultados de la consulta se colocarán en una tabla temporal, o incluso se ordenarán en la tabla temporal. SELECCIONE SQL_BUFFER_RESULT CAMPO1, CONTAR(*) DEL GRUPO TABLA1 POR CAMPO1
6. Conclusión
También existe un "principio 20/80" en programación, es decir, el 20% del código ocupa el 80% del tiempo. Lo mismo ocurre con el desarrollo de aplicaciones de bases de datos. La optimización de aplicaciones de bases de datos se centra en la eficiencia de ejecución de SQL. El objetivo de la optimización de la consulta de datos es hacer que el servidor de la base de datos lea menos datos del disco y lea las páginas de forma secuencial en lugar de no secuencialmente.