Mysql Optimización en el diseño de BD

De MediaWiki
Ir a la navegación Ir a la búsqueda

Introdución

  • 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



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

  • Lo que indica:
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

  • Nota Importante: El uso de la caché está 'deprecated' a partir de MySQL 5.7.20, y será eliminada en MySQL 8.0.
Esto ya está comentado en esta propia WIKI.


  • 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. Debemos de tener en cuenta que si se producen modificación en los datos, la consulta vuelve a cachearse, por lo que el valor de esta variable se incrementará, no así la variable de estado Qcache_queries_in_cache que veremos a continuación.
  • 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.


  • Tener en cuenta también que si se ejecutan sentencias que modifiquen los datos, la caché deja de ser válida y es necesario volver a guardar los resultados en memoria.



Ejercicio propuesto uso caché

Sobre la base de datos employees.


  • Activa la caché en el servidor Mysql y comprueba que está activa.
  • Obtén el tamaño máximo que puede tener la consulta para que pueda ser guardada en la cache.
  • Realiza una consulta sobre la tabla departments y comprueba que se ha guardado en la caché.
Comprueba que haciendo la misma consulta se devuelve el resultado de la caché.
  • Añade una nueva fila a la tabla departments y comprueba como al ejecutar la misma consulta de SELECT se tuvo que añadir a la caché de nuevo y por tanto no se sirvió de la caché.



Solución Ejercicio propuesto uso caché

  • Activa la caché en el servidor Mysql y comprueba que está activa.
Necesitamos modificar el archivo de configuración de Mysql y añadir la línea: query_cache_type=1


  • Obtén el tamaño máximo que puede tener la consulta para que pueda ser guardada en la cache.
Ejecutar la orden SQL: select @@query_cache_limit


  • Realiza una consulta sobre la tabla departments y comprueba que se ha guardado en la caché.
1 show status like 'qc%';
2 select * 
3 from departments;
4 show status like 'qc%';
Podemos comprobar como Qcache_inserts y Qcache_queries_in_cache se han incrementado.
La variable de estado Qcache_hits sigue en 0 ya que no se ha realizado la misma consulta desde que ha guardado en la caché.


Comprueba que haciendo la misma consulta se devuelve el resultado de la caché.
Debemos de realizar la misma consulta y comprobar como Qcache_hits aumenta: 
1 select * 
2 from departments;
3 show status like 'qc%';


  • Añade una nueva fila a la tabla departments y comprueba como al ejecutar la misma consulta de SELECT se tuvo que añadir a la caché de nuevo y por tanto no se sirvió de la caché.
Al realizar el insert podemos comprobar como Qcache_inserts aumenta, no así Qcache_queries_in_cache.
Al mismo tiempo, Qcache_hits no ha aumentado, por lo que se demuestra que el select no fue servidor por la caché ya que tuvo que guardarse nuevamente.



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

  • Explain se puede aplicar a una tabla para obtener información sobre una ella (como hace el comando DESCRIBE, o el comando SHOW COLUMNS).


  • Aplicado a la optimización, lo que vamos a conseguir haciendo uso de este comando es determinar como Mysql ejecuta la consulta y que recursos emplea (plan de ejecución).
Se puede aplicar a sentencias SQL: SELECT, UPDATE, DELETE, INSERT y REPLACE.
En versiones anteriores a 5.7 se podía utilizar la opción EXTENDED para obtener más información, pero ahora dicha opción ya está activada por defecto por lo que no es necesario ponerla.


Su sintaxis básisa es: EXPLAIN SELECT consulta
Podéis consultar en este enlace la información que suministra explain en estos casos.


Veamos el resultado sobre una consulta simple en la base de datos employees: EXPLAIN SELECT * FROM departments ORDER BY dept_no;
Mysql explain 1.jpg
Entre la información que devuelve:
  • id: Es el número identificar de cada consulta que tenga que realizar. Imaginemos un SELECT con UNION entre dos tablas. En este caso, tendría que hacer un select de la primera tabla (identificar 1), un select de la segunda (identificador 2) y unirlas.
  • select_type: El tipo de consulta SELECT que se está realizando. Tenéis en este enlace los diferentes tipos. En el ejemplo indica SIMPLE ya que es una consulta que no tiene uniones ni subconsultas.
  • table: Nombre de la tabla de donde se obtienen los registros.
  • partitions: En caso de que la tabla esté particionada, indicaría las particiones donde tendría que ir a buscar los registros. Más información en este enlace.
  • type: Indica qué tipo de valores se usan en la consulta. Podéis consultar los diferentes tipos en este enlace. En nuestro ejemplo indica INDEX. Esto indica que el acceso a los datos se ha realizado utilizando un índice (no aparece nada en la columna 'Extra').
  • posible_keys: Posibles índices que se podrían utilizar para buscar los datos.
  • key: El nombre del índice que Mysql ha decidido utilizar entre todos los posibles. En nuestro ejemplo el índice asociado a la clave primaria de la tabla.
  • key_len: Indica el tamaño del índice que utilza Mysql. En nuestro ejemplo, la clave primaria tiene un tamaño de 4 caracteres y está haciendo uso de todo su tamaño.
  • ref: Son las columnas o valores que se hacen coincidir con la clave. En nuestro ejemplo no hay. Si pusiéramos en la consulta: where dept_no='d001' , en la columna ref indicaría 'const'.
  • rows: Indica el número de filas que Mysql cree que deben examinarse. Es una estimación.
  • Extra: Muestra información adicional, como el uso de ciertas cláusulas o el uso de índices cuando son utilizados. En nuestro ejemplo aparece NULL por utilizar el índice primario, pero si usáramos otro índice aparecería el nombre del mismo.


  • Mysql va a determinar cuando debe de hacer uso de los índices.
Nosotros podemos 'forzarlo' a utilizarlos.
Podremos poner después de la cláusula FROM tabla lo indicado en este enlace.
  • Con USE indicamos a Mysql que haga uso únicamente' del índice indicado.
  • Con IGNORE indicamos que no haga uso del índice.
  • Con FORCE indica a Mysql que siempre haga uso del índice indicado.
  • Con FOR indicamos que haga uso del índice sólo para consultas del tipo combinación (JOIN), ordenación (ORDER BY) y agrupación (GROUP BY).


  • Ejemplo para evitar el uso de un índice

  • Sobre la tabla 'departments' creamos un índice sobre la columna 'detp_name'.

  • Al ejecutar la consulta: SELECT * FROM departments ORDER BY dept_name podemos ver como se hace uso del índice creado.

  • Al ejecutar la consulta: explain select * from departments ignore index (IDX_nombre) order by dept_name; vemos que ya no hace uso del índice.









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

Obtenido de «https://wiki.cifprodolfoucha.es/index.php?title=Mysql_Optimización_en_el_diseño_de_BD&oldid=5948»