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.

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.

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).

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