Métodos para optimizar la base de datos MYSQL
Concepto 1, la base de datos opcional, que a menudo se denomina valor base.
Antes de generar varios planes de ejecución, el optimizador de consultas debe obtener datos relevantes de la información estadística para estimar el número de registros involucrados en cada operación. Este dato relevante es la cardinalidad. En pocas palabras, es el estado de distribución de valor único de cada valor en cada campo.
Por ejemplo, la tabla t1 tiene 100 filas de registros, uno de los cuales es f1. El número de valores únicos en f1 puede ser 100, 1 o cualquier número entre 1 y 100. El único valor aquí es la cardinalidad opcional de la columna.
Al ver esto, podemos entender por qué es necesario crear índices en campos de alta cardinalidad, mientras que los de baja cardinalidad no son tan rápidos como los escaneos completos de tablas. Por supuesto, este es sólo un aspecto, y una mayor discusión está más allá del alcance de mi discusión.
Concepto 2, sobre el uso de pistas.
Aquí hablaré sobre qué son las sugerencias y cuándo usarlas.
HINT simplemente significa ayudar manualmente al optimizador de MySQL a generar el plan de ejecución óptimo en algunos escenarios específicos. En términos generales, el plan de ejecución del optimizador es óptimo, pero en algunos escenarios específicos, el plan de ejecución puede no ser óptimo.
Por ejemplo, la tabla t1 ha experimentado muchas operaciones de actualización frecuentes (actualizar, eliminar, insertar) y la cardinalidad ya es muy inexacta. En este momento, se acaba de ejecutar un SQL, por lo que es posible que el plan de ejecución de este SQL no sea óptimo. ¿Por qué es posible?
Veamos una demostración específica.
Por ejemplo, los siguientes dos SQL,
Respuesta:
seleccione * de t1 donde f 1 = 20;
B:
seleccione * de t1 donde f 1 = 30
Si el valor de f1 solo se actualiza con frecuencia a 30 y no alcanza el valor crítico de MySQL actualiza automáticamente el valor base, o el usuario establece actualizaciones manuales, o el usuario reduce las páginas de muestra, etc. , entonces ambas afirmaciones pueden no ser exactas.
Por cierto, MySQL proporciona métodos para actualizar automáticamente y actualizar manualmente el valor base de una tabla. Debido al espacio limitado, consulte el manual si es necesario.
Volviendo al tema, MySQL 8.0 trae varios consejos, por lo que hoy daré un ejemplo de index_merge.
Estructura de tabla de muestra:
mysql & gtdesc t 1; + - + - + - + - + - +|campo? | Tipo | Nulo | Clave | Predeterminado | |+-+--+-+-+| identificación | int(11)? |¿No? | PRI | NULL | incremento_automático | rango 1? |int(11)? |¿En serio? | MUL | NULO || |int(11)? |¿En serio? | MUL | NULO | || tiempo_registro? |FechaHora|¿Sí? | MUL | NULL | | prefijo_uid | | | NULO || desc1? |文|¿Sí? | | NULO || |int(11)? |¿En serio? | MUL | NULL | + - + - + - + - + 7 filas en la colección (0,00 segundos)
Número de registros de la tabla:
mysql & gt select from t1 count( *);+-+| count(*)|+-+| 32768 |+-+65438+0 filas en la colección (0,01 segundos)
Aquí tenemos dos SQL clásicos:
SQL C:
seleccione * de t1 donde rango1 = 1 o rango2 = 2 o rango 3 = 2;
SQL D:
seleccione * desde t1 donde rango1 = 100? ¿Y rango2 = 100? Y rango 3 = 100;
La tabla t1 en realidad tiene índices auxiliares para tres columnas: Rango1, Rango2 y Rango3.
Entonces echemos un vistazo al plan de consulta de SQL C.
Obviamente, no se utiliza ningún índice, el número de filas escaneadas es 32034 y la sobrecarga es 3243,65.
¿Explicar mysql y gt? format=json seleccionar * de t1? Donde rango1 = 1 o rango2 = 2 o rango 3 = 2 \G * * * * * * * * * * * * * * * * * * * * * * * 1.
Línea * * * * * * * * * * * * * * * * * * * * * * * * * * * Explicación: {? "query_block":{"select_id":1,"cost_info":{? "query_cost": "3243.65" }, "tabla": {? "nombre_tabla": "t1", "tipo_acceso": "TODOS",? "possible_keys":["idx_rank1","idx_rank2","idx_rank3"? ], ?" filas_examined_per_scan": 32034,? "rows_produced_per_join":115,? "Filtro": "0,36",? "cost_info":{"read_cost":"3232.07","eval_cost": "11.58","prefix_cost": "3243.65","data_read_per_join": "49K"? }, ?" used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3"? ], ?" adjunto _ condición ":"(` ytt `.` t 1 `.` rango 1 `= 1 ` .)o(` ytt `.` t 1 `.` rango 3 ` = 2))" } ? }} 1 fila en la colección, 1 advertencia (0,00 segundos)
Agreguemos una pista a la misma consulta y veamos el plan de consulta nuevamente.
En este momento, use index_merge, union union y use tres columnas. El número de líneas escaneadas es 1103 y el costo es 441,09, lo que obviamente es varias veces más rápido que antes.
¿Explicar mysql y gt? formato = JSON select/*+index _ merge(t 1)*/* de t 1? Donde rango1 = 1 o rango2 = 2 o rango 3 = 2 \G * * * * * * * * * * * * * * * * * * * * * * * 1.
Línea * * * * * * * * * * * * * * * * * * * * * * * * * * * Explicación: {? "query_block":{"select_id":1,"cost_info":{? "query_cost": "441.09" }, "tabla": {? "table_name": "t1", "access_type": "index_merge", "possible_keys": ["idx_rank1", "idx_rank2", "idx_rank3"? ], ?" clave": "unión(idx_rank1, idx_rank2, idx_rank3)",? "key_length": "5,5,5","rows_examined_per_scan":1103,? "rows_produced_per_join":1103,? "Filtrado": "100.00",? "cost_info":{"read_cost":"330.79","eval_cost": "110.30","prefix_cost": "441.09","data_read_per_join": "473K"? }, ?" used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3"? ], ?" adjunto _ condición ":"(` ytt `.` t 1 `.` rango 1 `= 1 ` .)o(` ytt `.` t 1 `.` rango 3 ` = 2))" } ? }} 1 fila en la colección, 1 advertencia (0,00 segundos)
Veamos el plan de SQL D nuevamente:
Sin mensaje,
formato mysql y gtexplain = JSON seleccione * de t 1 donde rango1 =100, rango2 =100, rango 3 = 100 \G * * * * * * * * * * * * * * * * * * * * * 1.
Línea * * * * * * * * * * * * * * * * * * * * * * * * * * * Explicación: {? "query_block":{"select_id":1,"cost_info":{? "query_cost": "534.34" }, "tabla": {? "nombre_tabla": "t1", "tipo_acceso": "ref",? "possible_keys":["idx_rank1","idx_rank2","idx_rank3"? ], ?" clave": "idx_rank1 ",? " used_key_parts": [ "rango1 "? ], ?" key_length": "5", "ref": [ "const"? ], ?" filas_examined_per_scan": 555,? "rows_produced_per_join": 0,? "Filtrado": "0,07",? "cost_info":{"read_cost":"478.84","eval_cost": "0.04","prefix_cost": "534.34","data_read_per_join": "176"? }, ?" used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3"? ], ?" adjunto _ condición ":"(` ytt `.` t 1 `.` rango 3 ` = 100) y (` ytt `.` t 1 `.` rango 2 ` = 100))" }? }} 1 fila en la colección, 1 advertencia (0,00 segundos)
Con mensaje,
formato mysql y gtexplain = JSON select/*+index _ merge(t 1)* /* de t 1 donde rango1 =100, rango2 =100, rango 3 = 100 \ G * * * * * * * * * * * * * * * * * * * * * * * * * * 1.
Línea * * * * * * * * * * * * * * * * * * * * * * * * * * * Explicación: {? "query_block":{"select_id":1,"cost_info":{? "query_cost": "5.23" }, "tabla": {? "table_name": "t1", "access_type": "index_merge", "possible_keys": ["idx_rank1", "idx_rank2", "idx_rank3"? ], ?" clave ":" intersect(idx_rank1,idx_rank2,idx_rank3)",? "key_length": "5,5,5","rows_examined_per_scan":1,? "rows_produced_per_join":1,? "Filtrado": "100.00",? "cost_info":{"read_cost":"5.13","eval_cost": "0.10","prefix_cost": "5.23","data_read_per_join": "440"? }, ?" used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3"? ], ?" adjunto _ condición ":"(` ytt `.` t 1 `.` rango 3 ` = 100)y(` ytt `.` t 1 `.` rango 1 `= 100))" }? }}1 fila en la colección, 1 advertencia (0,00 segundos)
Comparando los dos anteriores, el costo con un aviso es 100 veces menor que sin un aviso.
En definitiva, el valor de cardinalidad de la tabla afecta al plan de consulta. Si el valor no se actualiza correctamente, deberá agregar el mensaje manualmente. Creo que las futuras versiones de MySQL traerán más consejos.