Consejos:
1. ¿Cómo detectar declaraciones ineficientes?
En MySQL, al configurar --log-slow-queries=[nombre de archivo] en los parámetros de inicio, puede registrar declaraciones SQL cuyo tiempo de ejecución exceda long_query_time (el valor predeterminado es 10 segundos) en el archivo de registro especificado. También puede modificar el tiempo de consulta largo en el archivo de configuración de inicio, como por ejemplo:
# Establecer el tiempo de consulta largo en 8 segundos
long_query_time=8
2. ¿Cómo consultar el índice de una tabla?
Puede utilizar la declaración SHOW INDEX, como por ejemplo:
SHOW INDEX FROM [nombre de la tabla]
3. ¿Cómo consultar el uso del índice de una determinada declaración?
Puede utilizar la declaración EXPLAIN para ver el uso del índice de una determinada declaración SELECT. Si es una declaración ACTUALIZAR o ELIMINAR, primero debe convertirse en una declaración SELECT.
4. ¿Cómo exportar el contenido del motor INNODB al archivo de registro de errores?
Podemos usar el comando SHOW INNODB STATUS para ver mucha información útil sobre el motor INNODB, como el proceso actual, transacciones, errores de clave externa, interbloqueos. problemas y otras estadísticas. ¿Cómo permitir que esta información se registre en el archivo de registro? Siempre que cree la tabla innodb_monitor usando la siguiente declaración, MySQL escribirá el sistema en el archivo de registro de errores cada 15 segundos:
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB
Si ya no necesita exportar al archivo de registro de errores
;, simplemente elimine Esta tabla puede ser:
DROP TABLE innodb_monitor
5. ¿Cómo eliminar archivos de registro enormes con regularidad?
Simplemente configure el tiempo de vencimiento del registro en el archivo de configuración de inicio:
expire_logs_days=10
Notas:
1. Concéntrese en el índice
. A continuación se utiliza la tabla TSK_TASK como ejemplo para ilustrar el proceso de optimización de la declaración SQL. La tabla TSK_TASK se utiliza para guardar las tareas de monitoreo del sistema. Los campos e índices relevantes son los siguientes:
ID: clave principal;
MON_TIME: tiempo de monitoreo creado;
STATUS_ID: relación de clave externa establecida con SYS_HIER_INFO.ID.
Nota: MySQL creará automáticamente índices para claves externas. Durante este proceso de optimización, se descubrió que estos índices de clave externa creados automáticamente causarán interferencias innecesarias en la eficiencia de las declaraciones SQL.
Primero, encontramos en el archivo de registro que la ejecución de la siguiente declaración fue relativamente lenta, más de 10 segundos:
# Query_time: 18 Lock_time: 0 Rows_sent: 295 Rows_examined: 88143
select * from TSK_TASK WHERE STATUS_ID = 1064 and MON_TIME >= ' 2007-11 -22' y MON_TIME < '2007-11-23';
Resulta que es necesario encontrar 295 registros que cumplan las condiciones entre 88143 registros, lo cual por supuesto es lento. Utilice rápidamente la instrucción EXPLAIN para comprobar el uso del índice:
+----+-------------+----------+------+- ---------
| id | seleccionar_tipo |tipo
| claves_posibles | ref |
----------+------+-----------
|SIMPLE
| TSK_TASK | FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME |
+-------------+----------+------+--------- --Se
puede ver que hay Hay dos índices disponibles: FK_task_status_id_TO_SYS_HIER_INFO, TSK_TASK_KEY_MON_TIME, y el índice de clave externa en STATUS_ID se utiliza cuando finalmente se ejecuta la declaración.
Echemos otro vistazo al índice de la tabla TSK_TASK:
+----------+-------------------------- --------
| Tabla | Nombre_clave | Nombre_columna |
+----------+------------+------ ---------------
| TSK_TASK | PRIMARIO | ID |
999149
|
-------------------------Bajo Oracle u otras bases de datos relacionales, condiciones WHERE El orden de los campos en el índice juega un papel importante en la selección de el índice. Ajustemos el orden de los campos, coloquemos STATUS_ID al final y EXPLIQUEMOS nuevamente:
EXPLICAR seleccione * de TSK_TASK WHERE MON_TIME >= '2007-11-22' y MON_TIME < '2007-11-23' y STATUS_ID = 1064
; sin efecto, MySQL todavía usa el índice de clave externa STATUS_ID creado por el sistema.
Después de un análisis cuidadoso, parece que el atributo Cardinalidad (es decir, el número de valores únicos en el índice) juega un papel extremadamente importante en la selección del índice. MySQL selecciona el índice con el menor número de valores únicos. en el índice como índice de la declaración completa.
Para esta declaración, si usa FK_task_status_id_TO_SYS_HIER_INFO como índice y la tabla TSK_TASK almacena datos durante muchos días, la cantidad de registros escaneados será grande y la velocidad será lenta. Hay varias soluciones de optimización disponibles:
si no hay muchas tareas en un día, eliminamos el índice FK_task_status_id_TO_SYS_HIER_INFO, luego MySQL usará el índice TSK_TASK_KEY_MON_TIME y luego escaneará los registros con STATUS_ID 1064 en los datos de ese día, lo cual no es lento. Si
hay muchas tareas en un día, debemos eliminar los índices FK_task_status_id_TO_SYS_HIER_INFO y TSK_TASK_KEY_MON_TIME, y luego crear un índice conjunto de STATUS_ID, MON_TIME, que definitivamente será muy eficiente.
Por lo tanto, se recomienda no utilizar claves externas para tablas con una gran cantidad de registros para evitar una reducción importante en la eficiencia del rendimiento.
2. Intente controlar la cantidad de registros en cada tabla.
Cuando la cantidad de registros en una tabla es grande, la administración y el mantenimiento serán muy problemáticos. Por ejemplo, el mantenimiento del índice llevará mucho tiempo, lo que provocará una gran interrupción. interferencias en el funcionamiento normal del sistema.
Para las tablas cuyo volumen de datos continúa creciendo con el tiempo, podemos distinguir datos en tiempo real y datos históricos según el tiempo. Podemos utilizar el programa de servicio en segundo plano para mover periódicamente los datos de la tabla en tiempo real a la tabla histórica, controlando así. el número de registros en la tabla en tiempo real y mejorar el rendimiento de las consultas y la eficiencia operativa. Pero tenga en cuenta que el tiempo de cada movimiento debe ser lo suficientemente corto como para no afectar la escritura de datos del programa normal. Si tarda demasiado, puede provocar un problema de interbloqueo.
3. Estrategia de hash (partición) de datos:
cuando el número de clientes alcanza una determinada escala, una sola base de datos no podrá admitir un mayor acceso simultáneo. En este momento, puede considerar la posibilidad de hacer hash (particionar) los datos del cliente en varias bases de datos. compartir la carga. Mejorar el rendimiento general y la eficiencia del sistema.