Mysql Optimización en el diseño de BD

Introdución

• Más información en este enlace.

• En este punto vamos a indicar aspectos en el diseño físico de las bases de datos, formas de realizar las consultas,estructura de tablas,... que harán que las consultas se ejecuten de forma más rápida.

Optimizando la estructura de las bases de datos

• Podemos consultar las opciones a tener en cuenta en este enlace.

Optimizando el diseño de las tablas

• Columnas de las tablas:

• Intentar escoger el tipo de dato más pequeño posible. Así, por ejemplo. MEDIUMINT ocupa un 25% menos de espacio que un INT.
• Mejor declarar columnas con la propieadad NOT NULL ya que va a hacer que los índices funcionen más rápidos ya que no tienen que chequear si el valor es NULL en las comparaciones.

• ROW FORMAT: Podemos consultar las características de los diferentes formatos de filas en este enlace. Lo que representa este parámetro es la forma en como Mysql guarda cada fila de datos. Lo que hace Mysql por defecto es utilizar el tipo COMPACT. Si queremos que la fila 'ocupe' menos deberemos escoger la opción COMPRESSED , 'comprimiendo' los datos por lo que aumenta el uso de la CPU. Una explicación en este enlace.

• Indices:

• Las columnas que conforman los índices deben ser de un tamaño lo más pequeño posible. Recordar como ya vimos en la sección de índices que los índices secundarios guardan el valor del índice primario.
• Crea el número de índices que necesites. teniendo en cuenta que estos son adecuados para consultas pero que dejan de ser adecuados si realizamos operaciones de inserción y actualización.
• Si se realizan consultas en los que se busca por varios campos, se debe de crear un índice multicolumna mejor que varios índices parciales La primera parte del índice debería ser siempre la columna más usada. En el caso de usar siempre varias columnas es mejor que la primera columna sea la que tenga más duplicados.
• En el caso de utilizar un índice sobre una columna de tipo cadena de un tamaño grande, es mejor hacer un índice parcial.

• JOINS:

• Podemos declarar columnas que se referencian mucho en tablas diferentes para aumentar el rendimiento.
• Intentar que los nombres de las columnas sean simples. Por ejemplo, en una tabla Clientes, la columna nombre es adecuada, mejor que cliente_nombre. Por motivos de compatibilidad con otros gestores es mejor que los nombres de las columnas tengan menos de 18 caracteres.

Optimizando los tipos de datos

• Siempre es preferible valores numéricos para identificar las filas que valores de cadena.

• Si la información a guardar puede ser guardada en formato numérico o cadena, es preferible que sea numérica.

• Siempre que se pueda es mejor utilizar la ordenación y comparación haciendo uso del operador BINARY.

• Siempre que se comparan columnas es mejor que dichas columnas tengan la misma codificación (collation) y juego de caracteres.

• Con columnas inferiores en tamaño a 8KB es mejor hacer uso del tipo de datos varchar que blob.

• Si habitualmente en una tabla se disponen de columnas a las cuales raramente se va a acceder, es mejor mover dichas columnas a otra tabla y relacionarlas con una clave primaria-foránea con la tabla original.

Optimizando cuando el número de tablas abiertas es alta

• Podéis consultar este enlace

• Lo que indica:

• El valor de la variable del sistema table_open_cache debe de ser al menos el resultado de multiplicar el valor de la variable del sistema max_connections por N siendo N el número de tablas a las que puede acceder cualquier consulta SQL que vayamos a utilizar. A mayores se debe incrementar para reservar el uso de tablas temporales y ficheros. Este número debe ser inferior al número máximo de ficheros abiertos que puede soportar el Sistema Operativo. Podéis consultar como hacerlo en este enlace.

El valor por defecto de la variable del sistema table_open_cache es de 400.

• El valor de la variable table_open_cache será demasiado pequeño cuando la variable de estado Opened_tables es demasiado alto o este se incrementa muy rápidamente. Dicha variable informa de cuantas tablas están abiertas en operaciones. La orden SQL sería para obtener el valor de dicha variable es: SHOW GLOBAL STATUS LIKE 'Opened_tables';

Su valor debe de ser lo más próximo posible al valor de table_open_cache.

Podéis consultar un ejemplo de optimización en este enlace. La base teórica en la que se apoya no lo tengo muy claro, ya que determina el número máximo de tablas de la base de datos y la multiplica por el número de threads concurrentes y de ahí aplica un 50%. Me parece muy exagerado pensar que cada uno de las conexiones a la base de datos acceda a todas las tablas y aplicar un 50% porque sí ???? Yo creo que está mejor el método comentado anteriormente del número máximo de conexiones simultáneas por el número de tablas máximo que pueda acceder una consulta.

Procesamiento de consultas

• Cuando el gestor ejecuta una consulta pasa por las siguiente fases:

• Parseo.
• Análisis.
• Optimización.

Antes de hacer pasar por estas etapas, el servidor consulta la caché para no tener que repetir el proceso.

Caché de consultas

• Mysql almacena cada consulta en la caché a no ser que se ejecute con la opción SQL_NO_CACHE.

Para que funcione la caché esta tiene que estar activa y podemos activarla o consultar su estado en la variable del sistema query_cache_type.

Sólo las consultas que se repiten muchas veces deben ser cacheadas.

Nunca serán cacheadas las consultas que forman parte de una subconsulta, las consultas en el interior de procedientos almacenados, función, trigger o evento.

• A partir de la versión 5.7 la caché está desactivada.

Para activarla debemos modificar el valor de la variable del sistema query_cache_type.

Otra variable relacionada con la anterior es la que indica el tamaño de la caché. Esta viene especificada por el variable del sistema query_cache_size.

Recordar que lo que estamos reservando es espacio de memoria para guardar los resultados de consultas.

Por lo tanto tendríamos que analizar las sentencias SQL que se ejecutan en el servidor y ver cuales de ellas son ejecutadas más veces y cual es su tamaño medio (cuantos bytes ocupan el resultado de la ejecución).

A partir de estos datos y en función de la memoria disponible en el ordenador donde se ejecuta el Mysql podremos activar la caché e indicar el tamaño de la misma.

Para limitar las consultas que serán guardadas en la caché en base al tamaño del resultado de una consulta, disponemos de la variable del sistema query_cache_limit.

• Para determinar si la caché está funcionando adecuadamente, podremos hacer una consulta al gestor, para que muestre los valores del las variables de estado relacionadas con la caché: show status like 'qc%';

Entre la información que obtendremos estará:

• Qcache_free_memory: Es la memoria que queda libre para nuevas consultas en la caché.
• Qcache_hits: Indica cuantas consultas fueron 'servidas' por la caché. Cuanto más grande su valor mejor.
• Qcache_inserts: Consultas que han sido cacheadas desde que se inició el servidor Mysql.
• Qcache_lowmem_prunes: Indica cuantas consultas fueron descartadas por la caché al no poder añadirlas por falta de espacio.
• Qcache_queries_in_cache: Número de consultas en la caché.

• Podéis consultar una explicación de estos parámetros en este enlace.

• Recordar que se puede hacer que una consulta se ejecute sin que haga uso de la caché con la opción: SELECT SQL_NO_CACHE col1,col2,....

De todas formas el propio S.O. tiene su propia caché de acceso a disco y el propio Mysql hace uso de una caché de índices y páginas de disco, por lo que puede suceder que al ejecutar una consulta que haga uso de la caché, al volver a ejecutarla desactivando dicha caché, siga devolviendo los datos en un tiempo muy pequeño.

Lo mejor es mirar los parámetros anteriores para ver si está bien configurada.

Parseo, análisis y optimización

• Una vez que se comprueba que la consulta no está en la caché, el gestor transforma la sentencia en partes (parseo) identificando:

• Tipo de consulta: INSERT, UPDATE, DELETE, SELECT, GRANT,...
• Tablas que se van a utilizar.
• Contenido de la clásula WHERE (si existe)
• Otras cláusulas como GROUP, HAVING, ORDER BY,...

• Una vez tiene identificada las partes, intenta establecer cual es la forma más óptima de ejecutar la consulta para recuperar las filas de la forma más rápida posible (siendo este un proceso complejo).

Optimización de consultas e índices

• Los índices se utilizan en las siguientes situaciones:

• Consultar con WHERE en la que se usan columnas con índices.
• Para descargar filas en una columna, ya que si hay varios índices sobre una misma columna, Mysql utilizará el que menos filas tenga.
• En combinaciones de tablas cuando la relación entre ellas (clave primaria / foránea) se realiza con columnas que tengan un índice asociado.
• Para encontrar el valor de una función de agregado sobre campos indexados (no necesitamos acceder a los datos).
• Para ordenar o agrupar sobre columnas con índices asociados (si es múltiple, empezando por la izquierda).
• Para casos en los que sólo queremos acceder a los datos de las columnas que tengan un índice.

• Recordar que podemos aplicar un índice sobre varias columnas.

Siempre que tengamos una consulta SQL en la que la parte where afecte a más de una columna, será mejor hacer un único índice sobre todas las columnas del where que el realizar múltiples índices sobre cada columna individual, y crearlo en el mismo orden que el la sentencia WHERE.

Así, si tengo un índice multicolumna sobre las columnas pais y edad de una tabla 'personal', las siguientes consultas harían uso del índice:

• select nombre from personal where pais='España' and edad=50;
• select nombre from personal where pais='Francia' and edad>50;
• select nombre from personal where pais='Irlanda' and (edad=5 or edad=60);

Sin embargo estas otras no:

• select nombre from personal where edad=50;
• select nombre from personal where pais='España' or edad=50;

• Si queremos saber cuando Mysql hace uso de un índice o no debemos hacer uso del comando explain.

Comando Explain

• Podéis consultar su sintaxis en este enlace.

• Más información en este enlace.

-- Ángel D. Fernández González -- (2017).