Mi información de contacto
Correo[email protected]
2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
El contenido de los registros de MySQL es muy importante y a menudo se pregunta durante las entrevistas. Al mismo tiempo, dominar el conocimiento relacionado con los registros también nos ayudará a comprender los principios subyacentes de MySQL y nos ayudará a solucionar problemas cuando sea necesario.
Los tipos de registros comunes en MySQL incluyen principalmente las siguientes categorías (para el motor de almacenamiento InnoDB):
El registro binario (binlog) y el registro de transacciones (rehacer registro y deshacer registro) son más importantes y requieren nuestra atención.
El registro de consultas lentas registra todas las declaraciones de consulta cuyo tiempo de ejecución excede long_query_time (el valor predeterminado es 10 segundos, generalmente establecido en 1 segundos). A menudo se usa para resolver problemas de consultas lentas de SQL (el tiempo de ejecución de SQL es demasiado largo).
Encontrar SQL lento es el primer paso para optimizar el rendimiento de las declaraciones SQL. Luego use el comando EXPLAIN para analizar el SQL lento y obtener información sobre el plan de ejecución.
Puede utilizar el comando show variables como "slow_query_log" para comprobar si el registro de consultas lentas está activado. Está desactivado de forma predeterminada.
Se puede activar mediante SET GLOBAL slow_query_log=ON
El parámetro long_query_time define cuánto tiempo tarda una consulta antes de poder definirse como una consulta lenta. El valor predeterminado es 10 segundos. Puede verlo mediante el comando MOSTRAR VARIABLES LIKE'%long_query_time%';
También se puede modificar: establecer global long_query_time = 12
En proyectos reales, el registro de consultas lentas puede ser relativamente grande y es inconveniente analizarlo directamente. Podemos utilizar la herramienta oficial de ajuste y análisis de consultas lentas de MySQL. MySQLdumps lento . Mi blog también está simplemente conectado a la herramienta mysqldumpslow:
[MySQL] Herramienta mysqldumpslow: resumen de archivos de registro de consultas lentas-CSDN Blog
Hay una variable en MySQL que registra el número actual de declaraciones de consulta lenta. Puede usar mostrar estado global como '%slo.
w_queries%'; comando para ver.
MySQL nos proporcionaEXPLICARcomando para obtener información sobre el plan de ejecución.
El plan de ejecución se refiere al método de ejecución específico de una declaración SQL después de haber sido optimizada por el optimizador de consultas MySQL. Los planes de ejecución se utilizan generalmente en escenarios como el análisis y la optimización del rendimiento de SQL. A través de los resultados de EXPLAIN, puede obtener información como la secuencia de consulta de la tabla de datos, el tipo de operación de la operación de consulta de datos, qué índices se pueden alcanzar, qué índices se verán realmente afectados y cuántas filas de registros hay en cada dato. Se consulta la tabla y otra información. Específicamente, podemos optimizar SQL mediante algunos métodos comunes a continuación:
1. Evite usar SELECT *
SELECT * consume más CPU.
SELECT *Los campos inútiles aumentan el consumo de recursos de ancho de banda de la red y el tiempo de transmisión de datos, especialmente los campos grandes (como varchar,
blob, texto).
SELECT * no puede utilizar el optimizador MySQL para cubrir la optimización del índice (la estrategia de "índice de cobertura" basada en el optimizador MySQL es extremadamente rápida y eficiente, y es un método de optimización de consultas altamente recomendado en la industria)
SELECT <lista de campos> puede reducir el impacto de los cambios en la estructura de la tabla.
2. Optimización de la paginación
La paginación ordinaria lleva un tiempo relativamente corto cuando la cantidad de datos es pequeña.
Si la cantidad de datos aumenta, alcanzando millones o incluso decenas de millones, la paginación normal llevará mucho tiempo.
¿Cómo optimizar? Puede modificar la declaración SQL anterior en una subconsulta.
Primero consultamos el valor de la clave principal correspondiente al primer parámetro de límite, y luego filtramos y limitamos en función de este valor de clave principal, para que la eficiencia sea más rápida. Sin embargo, este método sólo funciona si los identificadores están en orden positivo.
Sin embargo, el resultado de la subconsulta generará una nueva tabla, lo que afectará al rendimiento. Se debe evitar el uso extensivo de subconsultas. Además, este método sólo es aplicable cuando la identificación está en secuencia positiva. En escenarios de paginación complejos, a menudo es necesario filtrar los ID que cumplen las condiciones mediante condiciones de filtrado. En este momento, los ID son discretos y discontinuos.
3. Haz menos uniones
Manual de desarrollo de Alibaba:
Puedes leer la discusión sobre Zhihu:
https://www.zhihu.com/pregunta/68258877
https://www.zhihu.com/pregunta/682588774. Se recomienda no utilizar claves foráneas ni cascadas.
Manual de desarrollo de Java de Alibaba:
5. Elija el tipo de campo apropiado
6. Intente utilizar UNION ALL en lugar de UNION
UNION colocará todos los datos de los dos conjuntos de resultados en una tabla temporal y luego realizará la operación de deduplicación, que lleva más tiempo y consume más recursos de CPU.
UNION ALL ya no deduplicará el conjunto de resultados y los datos obtenidos contienen elementos duplicados.
Sin embargo, si no se permiten datos duplicados en el escenario empresarial real, aún se puede utilizar UNION.
7. Operaciones por lotes
Para las actualizaciones de datos en la base de datos, si se pueden utilizar operaciones por lotes, utilícelas tanto como sea posible para reducir la cantidad de solicitudes de la base de datos y mejorar el rendimiento.
8. Utilice los índices correctamente
Hay mucho contenido en esta sección, que se presentará en un blog separado más adelante.
binlog (el registro binario es un archivo de registro binario) registra principalmente todas las operaciones que se han modificado en la base de datos MSQL (todas las declaraciones DDL y DML ejecutadas por la base de datos), incluidos los cambios en la estructura de la tabla (CREATE, ALTER, DROP TABLE), los datos de la tabla. modificaciones (INSERT.UPDATE, DELETE..), pero no incluye SELECT, SHOW y otras operaciones que no causan cambios en la base de datos.
Puede utilizar el comando show binario logs para ver una lista de todos los registros binarios:
Hay 3 tipos de métodos de grabación binaria:
En comparación con el modo Fila, el archivo de registro en el modo declaración es más pequeño, la presión de E/S del disco también es menor y el rendimiento es mejor. Sin embargo, su precisión es peor que la del modo Fila.
Antes de MySQL 5.1.5, el formato de binlog era solo STATEMENT. 5.1.5 comenzó a admitir binlog en formato ROW. A partir de la versión 5.1.8, MySQL comenzó a admitir binlog en formato MIXTO. Antes de MySQL 5.7.7, el modo Declaración se usaba de forma predeterminada. MySQL5.7.7 usa el modo Fila de forma predeterminada.
Puede utilizar mostrar variables como'%binlog format%' para ver el formato utilizado por binlog;
El escenario de aplicación principal de binlog es la replicación maestro-esclavo, maestro-esclavo y maestro-esclavo son inseparables de binlog. Se debe confiar en Binlog para sincronizar datos y garantizar la coherencia de los datos.
El principio de replicación maestro-esclavo se muestra en la siguiente figura:
1. La biblioteca principal escribe los cambios en los datos en la base de datos en binlog.
2. Conecte la biblioteca esclava a la biblioteca principal.
3. La biblioteca esclava creará un subproceso I0 para solicitar el binlog actualizado de la biblioteca principal.
4. La biblioteca principal creará un hilo de volcado de binlog para enviar el binlog, y el hilo de I/0 en la biblioteca esclava es responsable de recibirlo. 5. El hilo de I/0 de la biblioteca esclava escribirá el binlog recibido en el relé. registro.
6. Lea el registro de retransmisión del hilo SQL de la biblioteca y sincronice los datos localmente (es decir, ejecute SQL nuevamente)
Para el motor de almacenamiento InnoDB, durante la ejecución de una transacción, el registro se escribirá primero en binlogcache. Solo cuando se envíe la transacción, el registro en binlogcache se conservará en el archivo binlog en el disco. Escribir en la memoria es más rápido y esto también se hace por razones de eficiencia.
Debido a que el binlog de una transacción no se puede dividir, no importa cuán grande sea la transacción, debe escribirse una vez, por lo que el sistema asignará un bloque de memoria a cada subproceso como caché binlog. Podemos controlar el tamaño de binlogcache de un solo subproceso a través del parámetro binlog_cache_size. Si el contenido del almacenamiento excede este parámetro, debe almacenarse temporalmente en el disco (intercambio).
Entonces, ¿cuándo se descarga el binlog en el disco? Puede controlar el tiempo de descarga del biglog a través del parámetro sync_binlog. El rango de valores es 0-N y el valor predeterminado es 0:
·0: No es obligatorio, el sistema decidirá cuándo escribir en el disco.
·1: Cada vez que se envía una transacción, el binlog debe escribirse en el disco:
·N: Binlog se escribirá en el disco cada N transacciones.Riesgo de pérdida
Antes de MySQL5.7, el valor predeterminado de sync_binlog era 0. Después de MySQL5.7, el valor predeterminado de sync_binlog es 1. Generalmente, no se recomienda establecer el valor de sync_binlog en 0. Si los requisitos de rendimiento son relativamente altos o se produce un cuello de botella de E/S en el disco, el valor de sync_binlog se puede aumentar adecuadamente. Sin embargo, esto aumentará el riesgo de pérdida de datos.
Cuando se encuentre con las siguientes tres situaciones, MySQL regenerará un nuevo archivo de registro y se incrementará el número de serie del archivo.
Sabemos que el motor de almacenamiento InnoD8 administra el espacio de almacenamiento en unidades de páginas. Los datos que insertamos en MySQL finalmente existen en la página. Para ser precisos, es el tipo de página de datos. Para reducir la sobrecarga de E/S del disco, también existe un área llamada Buffer Pool, que existe en la memoria. Cuando la página correspondiente a nuestros datos no existe en el Buffer Pool, MSQL primero almacenará en caché la página en el disco en el Buffer Pool, para que luego operemos directamente la página en el Buffer Pool, lo que mejora enormemente el rendimiento de lectura y escritura. .
Después de confirmar una transacción, es posible que nuestras modificaciones en la página correspondiente en el grupo de búfer no se conserven en el disco. En este momento, si MySQL falla repentinamente, ¿desaparecerán directamente los cambios en esta transacción?
Obviamente no, de ser así obviamente violaría la durabilidad de la transacción.
El motor MySQLInnoDB utiliza el registro de rehacer para garantizar la durabilidad de las transacciones. Lo principal que hace el redo log es registrar las modificaciones de la página, como cuántos bytes se han modificado en un determinado desplazamiento en una determinada página y cuál es el contenido modificado específico. Cada registro en el registro de rehacer contiene el número de espacio de tabla, el número de página de datos, el desplazamiento, los datos modificados específicos e incluso puede registrar la longitud de los datos modificados (según el tipo de registro de rehacer).
Cuando se confirma la transacción, vaciaremos el registro de rehacer en el disco de acuerdo con la estrategia de vaciado. De esta manera, incluso si MySQL falla, los datos que no se pudieron escribir en el disco se pueden recuperar después del reinicio, asegurando así la durabilidad. de la transacción. En otras palabras, rehacer el registro brinda capacidades de recuperación de fallas de MySQL.
Redo Log registra todas las operaciones de modificación en la base de datos. Cuando la base de datos realiza operaciones de escritura (INSERT, UPDATE, DELETE), estas operaciones primero se registrarán en el Redo Log y luego se aplicarán al archivo de datos. De esta manera, incluso si el sistema falla antes de que la operación de modificación de datos se escriba completamente en el disco, Redo Log puede garantizar que los datos no se pierdan. Durante la recuperación, la base de datos rehacerá estas operaciones de modificación no finalizadas desde el Registro de rehacer para garantizar la coherencia de los datos.
Redo Log ayuda a que la base de datos se recupere a un estado consistente después de una falla del sistema o un corte de energía inesperado. Durante el proceso de recuperación, la base de datos verificará los registros en el Registro de rehacer y volverá a aplicar todas las modificaciones de datos enviadas pero no persistentes a los archivos de datos para recuperar los datos.
Para mejorar el rendimiento de las operaciones de escritura, las bases de datos suelen utilizar mecanismos de almacenamiento en caché (como grupos de búfer) para almacenar temporalmente las operaciones de modificación en la memoria en lugar de escribirlas inmediatamente en el disco. La existencia de Redo Log hace posible este mecanismo de almacenamiento en caché, porque siempre que se garantice que el Redo Log persista, no hay riesgo de pérdida de datos incluso si los datos en el caché no se han escrito en el disco.
Al confirmar una transacción, el registro de rehacer en el búfer de registro se descargará en el disco. Puede usar innodb_flush_log_at.
confirmar el control de parámetros. Debemos prestar atención a configurar la política de descarga correcta innodb_flush_log_at_trx_commit. Dependiendo de la estrategia de descarga configurada en MySQL, puede haber problemas menores de pérdida de datos después de que MySQL deja de funcionar.
innodb_flush_log_at_trx_commit Es un parámetro de configuración importante en el motor de almacenamiento MySQL InnoDB. Determina las estrategias de vaciado y escritura del registro cuando se envía una transacción, lo que afecta la durabilidad y el rendimiento de los datos. Tiene tres valores, a saber, 0, 1 y 2. Cada valor representa una estrategia de cepillado diferente.
innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_trx_commit = 2
Resumen de la estrategia del pincel.
1. El registro de rehacer se escribe en el búfer de registro pero aún no se ha escrito en el caché de la página. En este momento, la base de datos falla y se pierden datos (esta pérdida de datos puede ocurrir cuando el valor de la política de descarga innodb_flush log_at trx_commit es. 0);
2. El registro de rehacer se escribió en el caché de la página pero aún no se escribió en el disco. El sistema operativo fallará y puede ocurrir una pérdida de datos (esta pérdida de datos puede ocurrir cuando el valor de la política de descarga innodb2 flush log_at trx_commit es. 2).
Deshacer registro (registro de reversión) es un registro utilizado en el sistema de base de datos para registrar operaciones de modificación de datos. Registra las operaciones inversas (es decir, operaciones de deshacer) de todas las operaciones de modificación de datos durante la ejecución de la transacción. Deshacer registro juega un papel clave en la reversión de transacciones. A través de Deshacer registro, los datos se pueden restaurar al estado anterior al inicio de la transacción, garantizando así la atomicidad de la transacción.
La atomicidad de una transacción significa que todas las operaciones de la transacción se ejecutan todas o no se ejecuta ninguna. Undo Log garantiza la atomicidad de las transacciones a través de los siguientes mecanismos:
Grabar operaciones de deshacer Durante la ejecución de la transacción, cualquier operación de modificación de los datos registrará la operación de deshacer correspondiente en el Registro de deshacer antes de la modificación real. Por ejemplo, si una transacción actualiza el valor de una fila de registros, el valor anterior se registrará en el Registro de deshacer antes de la actualización.
Transacción de reversión Si la transacción falla por algún motivo (como un error o una reversión explícita), el sistema de base de datos lee el registro de deshacer y restaura los datos al estado anterior a que comenzara la transacción de acuerdo con la operación de deshacer registrada. De esta manera, puede asegurarse de que la transacción fallida no tenga impacto en la base de datos, asegurando así la atomicidad de la transacción.
Cita:
Se ha dedicado a la investigación de tecnología durante más de 30 años y domina varios lenguajes como java, linux, javascript, php, css, etc. Ha realizado muchas contribuciones en el campo del código abierto. Estación de documentación para desarrolladores para compartir algunos problemas en el desarrollo de tecnología para referencia futura.
Correo[email protected]